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

# Initialize database (set clear_data=True to reset data)
initialize_database(clear_data=False)

# Database path
db_path = os.path.abspath(os.path.join(os.path.dirname(__file__), "../college.db"))

# App layout
st.set_page_config(page_title="College Dashboard", layout="wide")
st.title("📊 College Dynamic Dashboard")

# Sidebar: select table
table = st.sidebar.selectbox("Select Table", ["student", "employee", "course"])

# Load data function
def load_data(table):
    conn = sqlite3.connect(db_path)
    df = pd.read_sql_query(f"SELECT * FROM {table}", conn)
    conn.close()
    return df

# Filter data function
def filter_data(df):
    col = st.sidebar.selectbox("Select column to filter", df.columns)
    unique_values = df[col].dropna().unique().tolist()
    selected = st.sidebar.multiselect(f"Filter {col}", unique_values, default=unique_values)
    if selected:
        return df[df[col].isin(selected)]
    return df

# Load and filter data
df = load_data(table)
filtered_df = filter_data(df)

# Display filtered data
st.subheader(f"📄 {table.capitalize()} Table")
st.dataframe(filtered_df, use_container_width=True)

# Quick insights & charts
st.markdown("### 📈 Quick Insights")
if table == "student":
    st.metric("Total Students", len(filtered_df))
    if "major" in filtered_df.columns:
        st.bar_chart(filtered_df['major'].value_counts())
elif table == "employee":
    st.metric("Total Employees", len(filtered_df))
    if "department" in filtered_df.columns:
        st.bar_chart(filtered_df['department'].value_counts())
elif table == "course":
    st.metric("Total Courses", len(filtered_df))
    if "semester" in filtered_df.columns:
        st.bar_chart(filtered_df['semester'].value_counts())

# Custom SQL query
st.markdown("### 🔍 Run Custom SQL Query")
sql_query = st.text_area("Enter your SQL Query below:", height=150)

if st.button("Execute Query"):
    # Basic SQL safety check
    restricted_keywords = ["drop", "delete", "update", "insert", "alter", "create", "pragma"]
    if any(keyword in sql_query.lower() for keyword in restricted_keywords):
        st.warning("🚫 Restricted SQL keywords detected. Only SELECT queries are allowed.")
    else:
        try:
            conn = sqlite3.connect(db_path)
            result_df = pd.read_sql_query(sql_query, conn)
            conn.close()
            st.success("✅ Query executed successfully.")
            st.dataframe(result_df, use_container_width=True)
        except Exception as e:
            st.error(f"❌ Error: {e}")