import plotly.express as px
from shiny.express import input, render, ui
from shinywidgets import render_widget
import sqlite3
import pandas as pd
from datetime import datetime
from faicons import icon_svg as icon
from shiny import reactive


ui.page_opts(title="Bienvenue à la page RH", fillable=True)
with ui.navset_tab():
    with ui.nav_panel("Création de compte employé"):
        conn = sqlite3.connect('C:/Users/neiro/Documents/GitHub/sgbd-usine/donnees/company.db')
        cursor = conn.cursor()
        with ui.layout_columns():
            with ui.card(full_screen=True):
                ui.h4("Veillez remplir les informations ci-dessous:")
                conn = sqlite3.connect('C:/Users/neiro/Documents/GitHub/sgbd-usine/donnees/company.db')
                cursor = conn.cursor()

                ui.input_text("NOM","Nom")
                ui.input_text("PRENOM","Prénom")
                ui.input_select("TYPE", "Type de contrat", choices=["Journalier", "Production"])
                
                @render.ui
                def journee_ou_production():
                    conn = sqlite3.connect('C:/Users/neiro/Documents/GitHub/sgbd-usine/donnees/company.db')
                    cursor = conn.cursor()
                    res1= cursor.execute("SELECT * FROM GRILLE_SALAIRE_HORAIRE")
                    data1 = pd.DataFrame(res1.fetchall(), columns=[x[0] for x in cursor.description])
                    journee = data1['CS_HORAIRE'].unique().tolist()
                    res2= cursor.execute("SELECT * FROM GRILLE_SALAIRE_PROD")
                    data2 = pd.DataFrame(res2.fetchall(), columns=[x[0] for x in cursor.description])
                    production = data2['CS_PRODUCTION'].unique().tolist()
                    typ=input.TYPE()
                    conn.close()
                    if typ== "Journalier":
                        return ui.input_select("CS_JOURNEE","Code salarial journalier",  choices=journee)
                    else:
                        return ui.input_select("CS_PRODUCTION","Code salarial de production",  choices=production)
                ui.input_date("date_debut", "Date de début" )

                ui.input_action_button("action_button", "Créer un compte")  
                @render.text()
                @reactive.event(input.action_button)
                def counter():
                    if int(input.action_button()) == 1:
                            NOM = input.NOM()
                            PRENOM = input.PRENOM()
                            TYPE = 1 if input.TYPE() == "Journalier" else 2
                            CS_JOURNEE = input.CS_JOURNEE() if TYPE == 1 else None
                            CS_PRODUCTION = input.CS_PRODUCTION() if TYPE == 2 else None
                            date_debut = input.date_debut()
                            conn = sqlite3.connect('C:/Users/neiro/Documents/GitHub/sgbd-usine/donnees/company.db')
                            cursor = conn.cursor()
                            restest=cursor.execute("""
                                SELECT NUMERO_EMPLOYE FROM EMPLOYE 
                                WHERE NOM = ? AND PRENOM = ? AND DATE_DEBUT = ?     
                            """, (NOM, PRENOM, date_debut))
                            existing_employee = restest.fetchone()
                            
                            if existing_employee is not None:
                                return f"Cet employé est déjà enregistré avec le numéro employé: {existing_employee[0]}"
                            else: 
                                cursor.execute("""
                                    SELECT MAX(NUMERO_EMPLOYE) FROM EMPLOYE
                                """)
                                max_numero_employe = cursor.fetchone()[0]
                                NUMERO_EMPLOYE = max_numero_employe + 1 if max_numero_employe is not None else 1

                                cursor.execute("""
                                    INSERT INTO EMPLOYE (NUMERO_EMPLOYE, TYPE, NOM, PRENOM, CS_PRODUCTION, CS_JOURNEE, DATE_DEBUT)
                                    VALUES (?, ?, ?, ?, ?, ?, ?)
                                """, (NUMERO_EMPLOYE, TYPE, NOM, PRENOM, CS_PRODUCTION, CS_JOURNEE, date_debut))

                                conn.commit()
                                conn.close()

                            return f"Nouvel employé enregistré!\n\n" + f"Numéro employé: {NUMERO_EMPLOYE}" + f"\nType de contrat: {'Journalier' if TYPE == 1 else 'Production'}" + f"\nNom: {NOM}" + f"\nPrénom: {PRENOM}" + (f"\nCode salarial de production: {CS_PRODUCTION}" if TYPE == 2 else f"\nCode salarial journalier: {CS_JOURNEE}") + f"\nDate du début du contrat: {date_debut}"
                    elif int(input.action_button())>1:
                        return "Vous avez déjà créé un compte employé, merci de rafraîchir la page pour en créer un autre."

    with ui.nav_panel("Suivi des employés"):
        @render.data_frame
        def suivi():
            conn1 = sqlite3.connect('C:/Users/neiro/Documents/GitHub/sgbd-usine/donnees/company.db')
            cursor = conn1.cursor()
            res= cursor.execute("SELECT * FROM EMPLOYE")
            data = pd.DataFrame(res.fetchall(), columns=[x[0] for x in cursor.description])
            conn1.close()
            return data

