# subj_inst.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
import time
# import ast

# db_connection.disconnect()

# Define a function to fetch and display the desired data
def display_combined_table(cursor, table_name):
    try:
        # Execute the SQL query to fetch the data with the desired columns
        cursor.execute("""
            SELECT 
                si.subj_inst_id, 
                si.sub_id_code, 
                s.sub_name,  -- Add subj_name to the query
                si.inst_id, 
                i.inst_name, 
                si.class_id, 
                p.prog_code, 
                c.class_year, 
                c.class_section
            FROM subj_inst si
            INNER JOIN subject s ON si.sub_id_code = s.sub_id_code
            INNER JOIN instructor i ON si.inst_id = i.inst_id
            INNER JOIN class c ON si.class_id = c.class_id
            INNER JOIN program p ON s.prog_id = p.prog_id
        """)
        data = cursor.fetchall()

        if not data:
            st.warning("No data found.")
        else:
            # Create a DataFrame from the fetched data and set column names
            df = pd.DataFrame(data, columns=[
                'subj_inst_id',
                'sub_id_code',
                'subj_name',  # Add subj_name to the columns
                'inst_id',
                'inst_name',
                'class_id',
                'prog_code',
                'class_year',
                'class_section'
            ])

            # Display the table with centered text
            st.header(f"{table_name} Table")

            # Create select boxes for filtering
            prog_code_filter = st.selectbox("Filter by Program Code", df['prog_code'].unique())
            class_year_filter = st.selectbox("Filter by Class Year", df['class_year'].unique())

            # Filter sections based on the selected program code and class year
            available_sections = df[(df['prog_code'] == prog_code_filter) & (df['class_year'] == class_year_filter)]['class_section'].unique()

            if len(available_sections) == 0:
                st.warning("No sections available for the selected program code and class year.")
            else:
                section_filter = st.selectbox("Filter by Section", sorted(available_sections))

                # Apply filters
                filtered_df = df[(df['prog_code'] == prog_code_filter) & (df['class_year'] == class_year_filter) & (df['class_section'] == section_filter)]
                
                if filtered_df.empty:
                    st.warning("No matching records found.")
                else:
                    print(filtered_df.style.set_properties(**{'text-align': 'center'}))
                    st.dataframe(filtered_df.style.set_properties(**{'text-align': 'center'}))
    except Exception as e:
        st.error(f"An error occurred while fetching data: {str(e)}")


def subj_inst(table_name):
    subj_inst_id = generate_unique_4(cursor, "subj_inst_id", table_name)

    # Fetch program options from the program table
    cursor.execute("SELECT prog_id, prog_code, prog_name FROM program")
    program_options = cursor.fetchall()
    prog_code = st.selectbox("Program Code", [str(prog[1]) for prog in program_options])

    # Fetch class options from the class table filtered by prog_code
    cursor.execute("""
        SELECT c.class_id, 
               c.class_year || ' - ' || c.class_section || ' (' || p.prog_code || ')' 
          FROM class c 
               INNER JOIN program p ON c.prog_id = p.prog_id 
         WHERE c.prog_id = ?
    """, (program_options[[prog[1] for prog in program_options].index(prog_code)][0],))
    class_options = cursor.fetchall()
    class_id = st.selectbox("Class Year and Section (Program)", sorted([str(cl[1]) for cl in class_options]))

    # Fetch instructor options from the instructor table
    cursor.execute("SELECT inst_id, inst_name FROM instructor")
    instructor_options = cursor.fetchall()
    
    # Filter subject_options to exclude those that already have assigned instructors in the selected class
    cursor.execute("""
        SELECT sub_id_code 
          FROM subj_inst 
         WHERE class_id = ?
    """, (class_options[[cl[1] for cl in class_options].index(class_id)][0],))
    assigned_subjects = set(row[0] for row in cursor.fetchall())
    
    cursor.execute("""
        SELECT s.sub_id_code, s.sub_name 
          FROM subject s 
         WHERE s.prog_id = ?
    """, (program_options[[prog[1] for prog in program_options].index(prog_code)][0],))
    subject_options = cursor.fetchall()
    subject_options = [(sub[0], sub[1]) for sub in subject_options if sub[0] not in assigned_subjects]

    if not subject_options:
        st.warning("All subjects in this class already have instructors.")
    else:
        sub_id_code = st.selectbox("Subject Name", [str(sub[1]) for sub in subject_options])

        # Fetch selected subject's instructor options
        cursor.execute("SELECT inst_id, inst_name FROM instructor")
        instructor_options = cursor.fetchall()
        inst_id = st.selectbox("Instructor Name", [str(inst[1]) for inst in instructor_options])

        if st.button("Insert Subject Instructor Record"):
            try:
                # Extract the selected IDs from the displayed names
                sub_id_code = subject_options[[sub[1] for sub in subject_options].index(sub_id_code)][0]
                inst_id = instructor_options[[inst[1] for inst in instructor_options].index(inst_id)][0]
                class_id = class_options[[cl[1] for cl in class_options].index(class_id)][0]

                # Check for duplicates
                cursor.execute("""
                    SELECT subj_inst_id 
                      FROM subj_inst 
                     WHERE sub_id_code = ? 
                       AND inst_id = ? 
                       AND class_id = ?
                """, (sub_id_code, inst_id, class_id))
                duplicate_check = cursor.fetchone()

                if duplicate_check:
                    st.error("This combination of Subject, Instructor, and Class already exists.")
                else:
                    # Insert a record into the subj_inst table
                    cursor.execute("""
                        INSERT INTO subj_inst (subj_inst_id, sub_id_code, inst_id, class_id) 
                        VALUES (?, ?, ?, ?)
                    """, (subj_inst_id, sub_id_code, inst_id, class_id))
                    db_connection.commit()
                    st.success("Record inserted successfully.")
                    time.sleep(0.3)
                    st.rerun()
            except Exception as e:
                st.error(f"An error occurred: {str(e)}")

    display_combined_table(cursor, table_name)

# # db_connection.reconnect()