# subject.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

# Existing code for adding subjects
def subject(table_name):
    sub_id_code = generate_unique_4(cursor, "sub_id_code", table_name)
    sub_name = st.text_input("Subject Name", key="sub_name").upper()

    # Fetch program options from the database
    cursor.execute("SELECT prog_id, prog_name FROM program")
    program_options = cursor.fetchall()
    prog_id_options = {str(prog[0]): prog[1] for prog in program_options}

    # Fetch academic year options from the database, including sem_num
    cursor.execute("SELECT acad_id, ' - Semester ' || sem_num FROM academic_list")
    academic_options = cursor.fetchall()
    acad_id_options = {str(acad[0]): acad[1] for acad in academic_options}

    prog_id = st.selectbox("Program", list(prog_id_options.keys()), format_func=lambda x: prog_id_options[x])
    acad_id = st.selectbox("Academic Year and Semester", list(acad_id_options.keys()), format_func=lambda x: acad_id_options[x])

    if st.button("Insert Subject Record"):
        # Check if sub_name, prog_id, and acad_id are provided
        if not sub_name or not prog_id or not acad_id:
            st.error("Subject Name, Program, and Academic Year and Semester are required. Please provide values for all fields.")
        else:
            try:
                # Check for duplicates
                cursor.execute("SELECT sub_id_code FROM subject WHERE sub_name = ? AND prog_id = ? AND acad_id = ?",
                               (sub_name, prog_id, acad_id))
                duplicate_check = cursor.fetchone()
                if duplicate_check:
                    st.error("A subject with the same name, program, and academic year and semester already exists.")
                else:
                    # Insert a record into the subject table
                    cursor.execute("INSERT INTO subject (sub_id_code, sub_name, prog_id, acad_id) VALUES (?, ?, ?, ?)",
                                   (sub_id_code, sub_name, prog_id, acad_id))
                    db_connection.commit()
                    st.success("Record inserted successfully.")
            except Exception as e:
                st.error(f"An error occurred: {str(e)}")

    # New code for displaying the DataFrame
    st.header("Filtered Subjects")
    if prog_id and acad_id:
        cursor.execute("SELECT sub_id_code, sub_name FROM subject WHERE prog_id = ? AND acad_id = ?",
                    (prog_id, acad_id))
        subject_data = cursor.fetchall()
        if subject_data:
            df = pd.DataFrame(subject_data, columns=["sub_id_code", "sub_name"])
            st.dataframe(df.style.set_properties(**{'text-align': 'center'}))
        else:
            st.warning("No subjects found for the selected Program and Academic Year/Semester.")
    display_table(cursor, table_name)