# instructor.py
import streamlit as st
import pandas as pd
from app5_selectbox.database_con import cursor, db_connection
from app5_selectbox.app5_selectbox_func import display_table, generate_unique_4


# Function to fetch all instructors with their assigned program codes
def get_instructors_with_programs():
    cursor.execute("""
        SELECT instructor.inst_id, instructor.inst_name, program.prog_id, program.prog_code
        FROM instructor
        INNER JOIN program ON instructor.prog_id = program.prog_id
    """)
    data = cursor.fetchall()
    df = pd.DataFrame(data, columns=['Instructor ID', 'Instructor Name', 'Program ID', 'Program code'])
    return df

def instructor(table_name):
    inst_id = generate_unique_4(cursor,"inst_id", table_name)
    inst_name = st.text_input("Instructor Name", key="inst_name").upper()

    # Get the available programs from the 'program' table
    cursor.execute("SELECT prog_id, prog_name FROM program")
    programs = cursor.fetchall()
    program_names = [f"{prog_id} - {prog_name}" for (prog_id, prog_name) in programs]
    prog_id = st.selectbox("Program", program_names, key="prog_id")

    if st.button("Insert Instructor Record"):
        # Check if the inst_name and prog_id are provided
        if not inst_name or not prog_id:
            st.error("Instructor Name and Program are required. Please provide values for both fields.")
        else:
            # Extract the selected program_id from the program_names
            selected_program = programs[program_names.index(prog_id)]
            selected_prog_id = selected_program[0]

            # Check for duplicates in the 'instructor' table
            cursor.execute("SELECT inst_id FROM instructor WHERE inst_name = %s AND prog_id = %s", (inst_name, selected_prog_id))
            existing_instructor = cursor.fetchone()
            if existing_instructor:
                st.error("Instructor with the same name and program already exists.")
            else:
                try:
                    # Insert a record into the instructor table
                    cursor.execute("INSERT INTO instructor (inst_id, inst_name, prog_id) VALUES (%s, %s, %s)",
                                    (inst_id, inst_name, selected_prog_id))
                    db_connection.commit()
                    st.success("Record inserted successfully.")
                except Exception as e:
                    st.error(f"An error occurred: {str(e)}")
    # display_table(cursor, table_name)


    # Streamlit code
    st.title("Instructors with Assigned Program Codes")
    instructor_df = get_instructors_with_programs()
    st.dataframe(instructor_df.style.set_properties(**{'text-align': 'center'}))