import logging
import traceback
from flask import Flask, render_template, request, jsonify
import pandas as pd
import json
from joblib import load
import mysql.connector
from mysql.connector import Error

# =[Modules dan Packages]========================
from c45 import c45  # Ensure this is correctly imported and available

# =[Variabel Global]=============================
app = Flask(__name__, static_url_path='/static')
model = None
model = load('/code/model_scratch_2.model')

# Setup logging
logging.basicConfig(level=logging.DEBUG)

# =[Routing]=====================================

# [Routing untuk Halaman Utama atau Home]
@app.route("/")
def beranda():
    return render_template('index.html')

# [Routing untuk API]
@app.route("/deteksi", methods=['GET', 'POST'])
def apiDeteksi():
    if request.method == 'POST':
        try:
            # Extract form data
            nama = request.form['Nama']
            posisi_harapan = request.form['Posisi_Harapan']
            usia = int(request.form['Usia'])

            # Extract kategori umur as an object
            kategori_umur_obj = request.form.get('KategoriUmur')
            if kategori_umur_obj is not None:
                kategori_umur_values = json.loads(kategori_umur_obj)
            else:
                kategori_umur_values = {}

            kategori_umur_values = {f"KategoriUmur[{i}]": int(request.form.get(f"KategoriUmur[{i}]")) for i in range(1, 11)}
            kategori_umur_dict = {f"Kategori_Umur_{i}": kategori_umur_values.get(f"KategoriUmur[{i}]", 0) for i in range(1, 11)}

            pendidikan = int(request.form['Pendidikan'])
            lama_pengalaman = int(request.form['Lama_Pengalaman'])
            kesesuaian_posisi_dg_skills = int(request.form['Kesesuaian_Posisi_Skill'])
            kesesuaian_posisi_dg_pengalaman = int(request.form['Kesesuaian_Posisi_Pengalaman'])

            # Log input data
            logging.debug(f"Nama: {nama}")
            logging.debug(f"Posisi Harapan: {posisi_harapan}")
            logging.debug(f"Usia: {usia}")
            logging.debug(f"Pendidikan: {pendidikan}")
            logging.debug(f"Lama Pengalaman: {lama_pengalaman}")
            logging.debug(f"Kesesuaian Posisi Skill: {kesesuaian_posisi_dg_skills}")
            logging.debug(f"Kesesuaian Posisi Pengalaman: {kesesuaian_posisi_dg_pengalaman}")
            logging.debug(f"Kategori Umur: {kategori_umur_dict}")

            # Perform prediction
            data = {**kategori_umur_dict,
                    "Pendidikan": pendidikan,
                    "Lama_Pengalaman": lama_pengalaman,
                    "Kesesuaian_Posisi_Skill": kesesuaian_posisi_dg_skills,
                    "Kesesuaian_Posisi_Pengalaman": kesesuaian_posisi_dg_pengalaman}

            df_test = pd.DataFrame(data, index=[0])
            logging.debug(f"DataFrame for prediction:\n{df_test}")

            # Check if model is loaded
            if model is None:
                raise Exception("Model not loaded")

            # Perform prediction
            hasil_prediksi = model.predict(df_test)[0]

            # Log prediction result
            logging.debug(f"Prediction result: {hasil_prediksi}")

            # Map prediction to a string
            if hasil_prediksi == 1:
                hasil_prediksi_str = 'Recommended Candidate'
            elif hasil_prediksi == 0:
                hasil_prediksi_str = 'Not Recommended Candidate'
            else:
                hasil_prediksi_str = 'Data Tidak Sesuai Dengan Format'

            # Save data to the database
            try:
                connection = mysql.connector.connect(host='sql12.freesqldatabase.com',
                                                     database='sql12711336',
                                                     user='sql12711336',
                                                     password='ufDVxditnx')
                if connection.is_connected():
                    cursor = connection.cursor(prepared=True)
                    insert_query = ("INSERT INTO klasifikasi "
                                    "(nama, kategori_umur_1, kategori_umur_2, "
                                    "kategori_umur_3, kategori_umur_4, kategori_umur_5, kategori_umur_6, "
                                    "kategori_umur_7, kategori_umur_8, kategori_umur_9, kategori_umur_10, pendidikan, lama_pengalaman, "
                                    "kesesuaian_posisi_dg_skills, kesesuaian_posisi_dg_pengalaman, "
                                    "posisi_harapan, usia, hasil_prediksi) "
                                    "VALUES "
                                    "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")

                    cursor.execute(insert_query, (nama,) + tuple(data.values()) + (posisi_harapan, usia, hasil_prediksi_str))
                    connection.commit()
                    logging.debug("Data berhasil disimpan ke database.")
            except Error as e:
                logging.error(f"Error while connecting to MySQL: {e}")
            finally:
                if connection.is_connected():
                    cursor.close()
                    connection.close()
                    logging.debug("MySQL connection is closed")

            # Return prediction result as JSON
            return jsonify({
                "prediksi": hasil_prediksi_str
            })
        except Exception as e:
            logging.error(f"Error during prediction: {e}")
            logging.error(traceback.format_exc())
            return jsonify({
                "error": "Error during prediction"
            })

    # If the request is not POST, render the prediksi.html page
    return render_template('prediksi.html')

@app.route("/view_tabel", methods=['GET'])
def view_tabel():
    try:
        connection = mysql.connector.connect(host='sql12.freesqldatabase.com',
                                             database='sql12711336',
                                             user='sql12711336',
                                             password='ufDVxditnx')
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)
            cursor.execute("SELECT * FROM klasifikasi")
            data_tabel = cursor.fetchall()

            # Konversi nilai pendidikan, lama_pengalaman, kesesuaian_posisi_dg_skills, dan kesesuaian_posisi_dg_pengalaman
            for row in data_tabel:
                row['pendidikan'] = ">= S1" if row['pendidikan'] == "1" else "<S1"
                row['lama_pengalaman'] = ">=1 Tahun" if row['lama_pengalaman'] == "1" else "<1 Tahun"
                row['kesesuaian_posisi_dg_skills'] = "Skills Sesuai Posisi Harapan" if row['kesesuaian_posisi_dg_skills'] == "1" else "Skills Tidak Sesuai Posisi Harapan"
                row['kesesuaian_posisi_dg_pengalaman'] = "Pengalaman Sesuai Posisi Harapan" if row['kesesuaian_posisi_dg_pengalaman'] == "1" else "Pengalaman Tidak Sesuai Posisi Harapan"

            return render_template('view_tabel.html', data_tabel=data_tabel)
    except Error as e:
        logging.error(f"Error while connecting to MySQL: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            logging.debug("MySQL connection is closed")

# =[Main]========================================

if __name__ == '__main__':
    # Load model yang telah ditraining
    try:
        model = load('/code/model_scratch_2.model')
        logging.debug("Model loaded successfully")
    except Exception as e:
        logging.error(f"Error loading model: {e}")

    # Run Flask di localhost
    app.run(host="0.0.0.0", port=5000, debug=True)  # Make sure to allow connections from outside if needed