# student.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 re
import pandas as pd
import streamlit as st


# Function to add data analysis to the student table
def analyze_student_data(cursor):
    # Fetch distinct program codes
    cursor.execute("SELECT DISTINCT prog_code FROM program")
    program_codes = [code[0] for code in cursor.fetchall()]
    
    # Select a program code
    selected_program = st.selectbox("Select Program Code", program_codes)

    # Update the SQL query to fetch the year along with the class section
    cursor.execute(f"""SELECT class.class_section, class.class_year, COUNT(student.stud_id) 
                FROM class 
                LEFT JOIN student ON class.class_id = student.class_id 
                WHERE class.prog_id = (SELECT prog_id FROM program WHERE prog_code = '{selected_program}') 
                GROUP BY class.class_section, class.class_year""")
    analysis_data = cursor.fetchall()

    # Create a DataFrame to display the updated analysis
    df = pd.DataFrame(analysis_data, columns=['Section', 'Year', 'Number of Students'])

    # Display the updated analysis in a table
    st.write(f"Data Analysis - Number of Students per Section in Program ({selected_program})")
    st.dataframe(df)

# Your code for connecting to the database and obtaining the cursor

def student(table_name):
    stud_id = generate_unique_4(cursor, "stud_id", table_name)
    stud_name = st.text_input("Student Name", key="stud_name").upper()
    stud_username = st.text_input("Student Username", key="stud_username")
    stud_password = st.text_input("Student Password", type="password", key="stud_password")

    # Fetch data from the "class" and "program" tables to populate the selectboxes
    cursor.execute("SELECT program.prog_code FROM program")
    program_data = cursor.fetchall()
    program_options = [code[0] for code in program_data]
    
    selected_program = st.selectbox("Select Program Code", program_options, key="selected_program")

    # Fetch class data based on the selected program
    cursor.execute(f"""SELECT class.class_id, class.class_year, class.class_section, program.prog_code 
                   FROM class 
                   JOIN program ON class.prog_id = program.prog_id 
                   WHERE program.prog_code = '{selected_program}'""")

    class_data = cursor.fetchall()
    class_options = [f"{year}{section} - ID:{class_id}" for class_id, year, section, prog_code in class_data]

    selected_class = st.selectbox("Select Class", sorted(class_options), key="selected_class")

    if st.button("Insert Student Record"):
        # Check if all fields are filled
        if not stud_name or not stud_username or not stud_password or not selected_class:
            st.error("All fields are required. Please provide values for all fields.")
        else:
            try:
                # Extract class_id from the selected option
                selected_class_id = re.search(r'ID:(\d+)', selected_class).group(1)

                # Check if the username is already taken
                cursor.execute(f"SELECT stud_username FROM student WHERE stud_username = {stud_username}")
                result = cursor.fetchone()
                if result:
                    st.error("Username already taken. Please choose a different username.")
                else:
                    # Insert a record into the student table
                    cursor.execute(f"""INSERT INTO student (stud_id, stud_name, stud_username, stud_password, class_id)
                                   VALUES ({stud_id}, {stud_name}, {stud_username}, {stud_password}, {selected_class_id})""")
                    db_connection.commit()
                    st.success("Record inserted successfully.")
            except Exception as e:
                st.error(f"An error occurred: {str(e)}")

    # Call the data analysis function to display student data analysis
    analyze_student_data(cursor)

    # Function to display the table with an additional column for concatenation
    def display_student_table(cursor, table_name):
        selected_program = st.selectbox("Select Program Code", program_options, key="display_student_table")
        cursor.execute(f"SELECT s.*, c.class_year, c.class_section, p.prog_code, "
                    "p.prog_code || c.class_year || c.class_section AS custom_column "
                    f"FROM {table_name} s "
                    "JOIN class c ON s.class_id = c.class_id "
                    "JOIN program p ON c.prog_id = p.prog_id")

        data = cursor.fetchall()
        df = pd.DataFrame(data, columns=['stud_id', 'stud_name', 'stud_username', 'stud_password', 'class_id',
                                        'is_eval', 'user_type', 'class_year', 'class_section', 'prog_code', 'custom_column'])
        df = df[df['prog_code'] == selected_program]
        st.dataframe(df.style.set_properties(**{'text-align': 'center'}))

    # Call the function to display the table with the concatenated column
    display_student_table(cursor, table_name)