# =[Modules dan Packages]========================

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
import logging

# =[Variabel Global]=============================

app   = Flask(__name__, static_url_path='/static')
model = None


# =[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 sebagai objek
            kategori_umur_obj = request.form.get('KategoriUmur')

            # Check if kategori_umur_obj has a value
            if kategori_umur_obj is not None:
                # Ubah objek JavaScript menjadi dict Python
                kategori_umur_values = json.loads(kategori_umur_obj)
            else:
                # Jika tidak ada nilai, atur sebagai dictionary kosong
                kategori_umur_values = {}
            
            # Python Flask code to access category values from formData
            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'])

            # 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])
            print("Data untuk prediksi:")
            print(df_test)
            hasil_prediksi = model.predict(df_test[0:1])[0]
            # Set up logging
            logging.basicConfig(level=logging.DEBUG)

            # Inside the apiDeteksi function, add logging statements to print values
            logging.debug("Nama: %s", nama)
            logging.debug("Kategori Umur: %s", kategori_umur_dict)

            # 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()
                    print("Data berhasil disimpan ke database.")
            except Error as e:
                print("Error while connecting to MySQL", e)
            finally:
                if connection.is_connected():
                    cursor.close()
                    connection.close()
                    print("MySQL connection is closed")

            # Return prediction result as JSON
            return jsonify({
                "prediksi": hasil_prediksi_str
            })
        except Exception as e:
            print(f"Error during prediction: {e}")
            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'] = "Sesuai Posisi Harapan" if row['kesesuaian_posisi_dg_skills'] == 1 else "Tidak Sesuai Posisi Harapan"
                row['kesesuaian_posisi_dg_pengalaman'] = "Sesuai Posisi Harapan" if row['kesesuaian_posisi_dg_pengalaman'] == 1 else "Tidak Sesuai Posisi Harapan"
            
            return render_template('view_tabel.html', data_tabel=data_tabel)
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")
            
# =[Main]========================================

if __name__ == '__main__':
	
	# Load model yang telah ditraining
	model = load('model_scratch_2.model')

	# Run Flask di localhost 
	app.run(host="localhost", port=5000, debug=True)