import os
import io
import sqlite3
import gradio as gr
import pandas as pd
from datetime import datetime
from attendance_core import connect, init_db, load_csv_to_df, ingest_df, get_dataframe, compute_percentages_numpy, summary_per_subject, students_below_threshold, export_csv

DB_PATH = os.environ.get("ATTENDANCE_DB_PATH", "attendance.db")

def ensure_db():
    conn = connect(DB_PATH)
    init_db(conn)
    return conn

def upload_csv(file, start_date, end_date):
    if file is None:
        return "Please upload a CSV.", None, None
    conn = ensure_db()
    df = load_csv_to_df(file.name)
    # Optional filter by date on ingest
    if start_date:
        df = df[df["Date"] >= start_date]
    if end_date:
        df = df[df["Date"] <= end_date]
    ingest_df(conn, df)
    return f"Ingested {len(df)} rows into the database.", df.head(10), df.tail(10)

def generate_summaries(threshold, start_date, end_date, subject, student_id):
    conn = ensure_db()
    df = get_dataframe(conn, start_date or None, end_date or None, subject or None, student_id or None)
    overall = compute_percentages_numpy(df)
    sub_summary = summary_per_subject(df)
    low = students_below_threshold(df, threshold)
    return df, overall, sub_summary, low

def download_csv(which, threshold, start_date, end_date, subject, student_id):
    conn = ensure_db()
    df = get_dataframe(conn, start_date or None, end_date or None, subject or None, student_id or None)
    if which == "Raw Attendance":
        out = df
        name = "raw_attendance.csv"
    elif which == "Overall Summary":
        out = compute_percentages_numpy(df)
        name = "overall_summary.csv"
    elif which == "Subject Summary":
        out = summary_per_subject(df)
        name = "subject_summary.csv"
    else:
        out = students_below_threshold(df, threshold)
        name = "below_threshold.csv"
    buf = io.BytesIO()
    out.to_csv(buf, index=False)
    buf.seek(0)
    return (name, buf)

with gr.Blocks(title="Student Attendance Management") as demo:
    gr.Markdown("# 🎓 Student Attendance Management\nA fast, SQLite-backed attendance analyzer with NumPy + Pandas.")
    with gr.Tabs():
        with gr.Tab("Upload / Ingest"):
            with gr.Row():
                file = gr.File(file_types=[".csv"], label="Upload attendance CSV")
                with gr.Column():
                    start_date = gr.Textbox(label="Filter start date (YYYY-MM-DD)", value="")
                    end_date = gr.Textbox(label="Filter end date (YYYY-MM-DD)", value="")
                    btn_ingest = gr.Button("Ingest to Database")
            status = gr.Markdown()
            head_df = gr.Dataframe(interactive=False, label="Head (preview)")
            tail_df = gr.Dataframe(interactive=False, label="Tail (preview)")
            btn_ingest.click(upload_csv, inputs=[file, start_date, end_date], outputs=[status, head_df, tail_df])

        with gr.Tab("Summaries & Queries"):
            with gr.Row():
                threshold = gr.Slider(0, 100, value=75, step=1, label="Low attendance threshold (%)")
                subject = gr.Textbox(label="Subject (optional exact match)", value="")
                student_id = gr.Textbox(label="StudentID (optional)", value="")
                start_date_q = gr.Textbox(label="Start date (YYYY-MM-DD, optional)", value="")
                end_date_q = gr.Textbox(label="End date (YYYY-MM-DD, optional)", value="")
                btn_run = gr.Button("Run Summary")
            raw_df = gr.Dataframe(label="Filtered Raw Attendance", interactive=False)
            overall_df = gr.Dataframe(label="Overall Attendance (per student)", interactive=False)
            subject_df = gr.Dataframe(label="Per-Subject Summary", interactive=False)
            low_df = gr.Dataframe(label="Students Below Threshold", interactive=False)
            btn_run.click(generate_summaries, inputs=[threshold, start_date_q, end_date_q, subject, student_id],
                          outputs=[raw_df, overall_df, subject_df, low_df])

        with gr.Tab("Export"):
            which = gr.Dropdown(choices=["Raw Attendance", "Overall Summary", "Subject Summary", "Below Threshold"],
                                value="Overall Summary", label="Choose export")
            threshold2 = gr.Slider(0, 100, value=75, step=1, label="Threshold for 'Below Threshold' export")
            start_date_e = gr.Textbox(label="Start date (YYYY-MM-DD, optional)", value="")
            end_date_e = gr.Textbox(label="End date (YYYY-MM-DD, optional)", value="")
            subject_e = gr.Textbox(label="Subject (optional exact match)", value="")
            student_e = gr.Textbox(label="StudentID (optional)", value="")
            dl = gr.File(label="Download CSV", interactive=False)
            btn_dl = gr.Button("Prepare CSV")
            btn_dl.click(download_csv, inputs=[which, threshold2, start_date_e, end_date_e, subject_e, student_e], outputs=dl)

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