import os
import sqlite3
import pandas as pd
import numpy as np
import gradio as gr
from datetime import date

# -------------------- STEP 1: Safe DB Path --------------------
if os.path.exists("D:"):  # Local Windows setup
    DB_PATH = r"D:\attendance_space\attendance.db"
else:  # Hugging Face or Linux
    DB_PATH = os.path.join(os.path.dirname(__file__), "attendance.db")

print(f"Using database at: {DB_PATH}")

# -------------------- STEP 2: Initialize Database --------------------
def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS attendance (
            StudentID TEXT,
            Subject TEXT,
            Date TEXT,
            Status TEXT
        )
    """)
    conn.commit()
    conn.close()

init_db()

# -------------------- STEP 3: Core Functions --------------------
def add_attendance(student_id, subject, att_date, status):
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO attendance VALUES (?, ?, ?, ?)",
                   (student_id, subject, att_date, status))
    conn.commit()
    conn.close()
    return f"✅ Attendance added for Student {student_id}"

def get_attendance_data():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    df = pd.read_sql("SELECT * FROM attendance", conn)
    conn.close()
    return df

def generate_summary(threshold=75):
    df = get_attendance_data()
    if df.empty:
        return pd.DataFrame(), pd.DataFrame()

    summary = (
        df.groupby(['StudentID', 'Subject'])['Status']
        .apply(lambda x: (x == 'Present').sum() / len(x) * 100)
        .reset_index(name='Attendance %')
    )

    below_threshold = summary[summary['Attendance %'] < threshold]
    return summary, below_threshold

def import_csv(file):
    try:
        df = pd.read_csv(file.name)
        conn = sqlite3.connect(DB_PATH, check_same_thread=False)
        df.to_sql("attendance", conn, if_exists="append", index=False)
        conn.close()
        return f"✅ Imported {len(df)} records."
    except Exception as e:
        return f"❌ Error: {e}"

def export_csv():
    df = get_attendance_data()
    export_path = os.path.join(os.path.dirname(DB_PATH), "attendance_export.csv")
    df.to_csv(export_path, index=False)
    return export_path

def clear_db():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.execute("DELETE FROM attendance")
    conn.commit()
    conn.close()
    return "🗑 Database cleared."

# -------------------- STEP 4: Gradio UI --------------------
with gr.Blocks() as demo:
    gr.Markdown("## 📚 Student Attendance Manager")

    with gr.Tab("➕ Add Attendance"):
        student_id = gr.Textbox(label="Student ID")
        subject = gr.Textbox(label="Subject")
        att_date = gr.Textbox(label="Date (YYYY-MM-DD)", value=str(date.today()))
        status = gr.Dropdown(["Present", "Absent"], label="Status")
        threshold = gr.Slider(0, 100, value=75, label="Threshold (%)")
        output_msg = gr.Textbox(label="Message")
        raw_df = gr.Dataframe(label="Raw Attendance")
        summary_df = gr.Dataframe(label="Summary")
        below_df = gr.Dataframe(label="Below Threshold")
        btn = gr.Button("Add & Refresh")
        btn.click(
            lambda sid, sub, dt, st, th: (
                add_attendance(sid, sub, dt, st),
                get_attendance_data(),
                *generate_summary(th)
            ),
            [student_id, subject, att_date, status, threshold],
            [output_msg, raw_df, summary_df, below_df]
        )

    with gr.Tab("📊 Reports"):
        threshold2 = gr.Slider(0, 100, value=75, label="Threshold (%)")
        summary_out = gr.Dataframe(label="Summary")
        below_out = gr.Dataframe(label="Below Threshold")
        btn_report = gr.Button("Generate Report")
        btn_report.click(
            lambda th: generate_summary(th),
            [threshold2],
            [summary_out, below_out]
        )

    with gr.Tab("📤 Export Data"):
        export_btn = gr.Button("Export to CSV")
        export_msg = gr.Textbox(label="Export Path")
        export_btn.click(lambda: export_csv(), outputs=export_msg)

    with gr.Tab("📥 Import Data"):
        file_in = gr.File(label="Upload CSV", file_types=[".csv"])
        import_msg = gr.Textbox(label="Message")
        file_in.upload(lambda f: import_csv(f), file_in, import_msg)

    with gr.Tab("⚠ Admin"):
        clear_btn = gr.Button("Clear Database")
        clear_msg = gr.Textbox(label="Message")
        clear_btn.click(lambda: clear_db(), outputs=clear_msg)

# -------------------- STEP 5: Launch App --------------------
if __name__ == "__main__":
    demo.launch(
        server_name="0.0.0.0",  # Required for Hugging Face Spaces
        server_port=int(os.environ.get("PORT", 7860))
    )