# import sqlite3
# import pandas as pd
# import gradio as gr
# from database import initialize_database

# # Initialize DB once at startup
# initialize_database()

# def query_students(department, year):
#     conn = sqlite3.connect("college.db")
#     query = "SELECT * FROM student WHERE 1=1"
    
#     if department != "All":
#         query += f" AND major = '{department}'"
#     if year != "All":
#         query += f" AND year_of_enrollment = {year}"
        
#     df = pd.read_sql_query(query, conn)
#     conn.close()
#     return df

# def query_courses(department):
#     conn = sqlite3.connect("college.db")
#     query = "SELECT * FROM course"
#     if department != "All":
#         query += f" WHERE department = '{department}'"
#     df = pd.read_sql_query(query, conn)
#     conn.close()
#     return df

# # Get list of unique years from DB to populate dropdown dynamically (optional)
# def get_years():
#     conn = sqlite3.connect("college.db")
#     df = pd.read_sql_query("SELECT DISTINCT year_of_enrollment FROM student ORDER BY year_of_enrollment", conn)
#     conn.close()
#     years = df["year_of_enrollment"].tolist()
#     years = [str(y) for y in years]
#     years.insert(0, "All")
#     return years

# departments = ["All", "Computer Science", "Mechanical Engineering", "ECE", "Civil", "IT", "CSE", "AI"]

# with gr.Blocks() as demo:
#     gr.Markdown("# College Database Dashboard")
    
#     with gr.Tab("Students"):
#         with gr.Row():
#             dept_input = gr.Dropdown(choices=departments, label="Department", value="All")
#             year_input = gr.Dropdown(choices=get_years(), label="Year of Enrollment", value="All")
#         student_table = gr.Dataframe()
#         query_btn = gr.Button("Show Students")
        
#         query_btn.click(fn=query_students, inputs=[dept_input, year_input], outputs=student_table)
    
#     with gr.Tab("Courses"):
#         course_dept_input = gr.Dropdown(choices=departments, label="Department", value="All")
#         course_table = gr.Dataframe()
#         course_btn = gr.Button("Show Courses")
        
#         course_btn.click(fn=query_courses, inputs=course_dept_input, outputs=course_table)

# if __name__ == "__main__":
#     demo.launch()

import streamlit as st
import pandas as pd
import sqlite3
from database import initialize_database

# Initialize DB on start
initialize_database()

# Function to fetch filtered students
def get_students(department, year):
    conn = sqlite3.connect("college.db")
    query = "SELECT * FROM student WHERE 1=1"
    if department != "All":
        query += f" AND major = '{department}'"
    if year != "All":
        query += f" AND year_of_enrollment = {year}"
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df

# Function to fetch filtered courses
def get_courses(department):
    conn = sqlite3.connect("college.db")
    query = "SELECT * FROM course"
    if department != "All":
        query += f" WHERE department = '{department}'"
    df = pd.read_sql_query(query, conn)
    conn.close()
    return df

# Prepare filter options
departments = [
    "All",
    "Computer Science",
    "Mechanical Engineering",
    "ECE",
    "Civil",
    "IT",
    "CSE",
    "AI"
]

def get_years():
    conn = sqlite3.connect("college.db")
    df = pd.read_sql_query("SELECT DISTINCT year_of_enrollment FROM student ORDER BY year_of_enrollment", conn)
    conn.close()
    years = df["year_of_enrollment"].astype(str).tolist()
    years.insert(0, "All")
    return years

# Streamlit app UI

st.title("College Database Dashboard")

tab1, tab2 = st.tabs(["Students", "Courses"])

with tab1:
    st.header("Student Records")
    dept_filter = st.selectbox("Select Department", departments)
    year_filter = st.selectbox("Select Year of Enrollment", get_years())
    
    if st.button("Show Students"):
        students_df = get_students(dept_filter, year_filter)
        if students_df.empty:
            st.info("No students found with the selected filters.")
        else:
            st.dataframe(students_df)

with tab2:
    st.header("Course Records")
    course_dept_filter = st.selectbox("Select Department", departments, key="course_dept")
    
    if st.button("Show Courses"):
        courses_df = get_courses(course_dept_filter)
        if courses_df.empty:
            st.info("No courses found with the selected department.")
        else:
            st.dataframe(courses_df)