import gradio as gr
import pandas as pd
import sqlite3
from datetime import datetime

# Database Setup
db_file = "outputs/attendance_records.db"

# Helper Functions
def get_db_connection():
    """Create a new database connection."""
    conn = sqlite3.connect(db_file)
    return conn

def log_attendance(name, day, date, status):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS attendance (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        day TEXT,
        date TEXT,
        status TEXT
    )
    """)
    conn.commit()

    cursor.execute("""
    INSERT INTO attendance (name, day, date, status)
    VALUES (?, ?, ?, ?)
    """, (name, day, date, status))
    conn.commit()
    conn.close()
    return "Attendance logged successfully!"

def calculate_fees():
    conn = get_db_connection()
    cursor = conn.cursor()

    # Calculate attendance fees
    cursor.execute("""
    SELECT name, COUNT(*) * (1000 / 12) AS fees
    FROM attendance
    WHERE status = 'Present'
    GROUP BY name
    """)
    fees_data = cursor.fetchall()
    fees_dict = {row[0]: row[1] for row in fees_data}
    conn.close()
    return fees_dict

def create_end_of_month_table():
    today = datetime.now()
    if today.day != pd.Period(today.strftime("%Y-%m")).days_in_month:
        return "It's not the end of the month yet."

    conn = get_db_connection()
    cursor = conn.cursor()

    # Create end-of-month table
    month = today.strftime("%Y-%m")
    table_name = f"fees_{month.replace('-', '_')}"
    cursor.execute(f"""
    CREATE TABLE IF NOT EXISTS {table_name} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        email TEXT,
        fees REAL
    )
    """)

    # Load participant data
    participant_file = "participants_form.xlsx"
    participants = pd.read_excel(participant_file)

    # Calculate fees
    fees_dict = calculate_fees()

    # Populate table
    for _, row in participants.iterrows():
        name = row["Name"]
        email = row["Email"]
        fees = fees_dict.get(name, 0)

        cursor.execute(f"""
        INSERT INTO {table_name} (name, email, fees)
        VALUES (?, ?, ?)
        """, (name, email, fees))

    conn.commit()
    conn.close()
    return f"End-of-month table '{table_name}' created successfully!"

def submit_attendance(name, day, date, status):
    return log_attendance(name, day, date, status)

def is_month_end():
    today = datetime.now()
    return today.day == pd.Period(today.strftime("%Y-%m")).days_in_month


# Gradio Interface
def get_dropdown_options(file_path, column_name):
    df = pd.read_excel(file_path)
    options = df["Name"].dropna().unique().tolist()
    options.sort()
    return options


with gr.Blocks() as app:
    gr.Markdown("# Attendance Tracker")

    with gr.Row():
        file_path = "participants_form.xlsx"
        column_name = "Name"
        options = get_dropdown_options(file_path, column_name)
        name = gr.Dropdown(choices=options, label="Select an Option")
        day = gr.Textbox(label="Day")
        date = gr.Textbox(label="Date (YYYY-MM-DD)")
        status = gr.Radio(["Present", "Absent"], label="Status")

    submit_button = gr.Button("Submit Attendance")
    submit_message = gr.Textbox(label="Message", interactive=False)

    submit_button.click(submit_attendance, inputs=[name, day, date, status], outputs=[submit_message])

    def update_end_of_month():
        return create_end_of_month_table()

    app.load(update_end_of_month)

app.launch()