import gradio as gr
import pandas as pd
import sqlite3
from datetime import datetime, date
import os
from utils.database import *
from utils.reports import *
from utils.auth import *

# Initialize Database
init_database()

# Attendance Functions
def mark_attendance(student_id, date, status, remarks=""):
    conn = sqlite3.connect('school_data.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT OR REPLACE INTO attendance (student_id, date, status, remarks)
        VALUES (?, ?, ?, ?)
    ''', (student_id, date, status, remarks))
    
    conn.commit()
    conn.close()
    return f"✅ उपस्थिती नोंदवली: {student_id} - {status}"

def get_attendance_summary(student_id, month, year):
    conn = sqlite3.connect('school_data.db')
    
    query = '''
        SELECT status, COUNT(*) as count 
        FROM attendance 
        WHERE student_id = ? AND strftime('%m', date) = ? AND strftime('%Y', date) = ?
        GROUP BY status
    '''
    
    df = pd.read_sql_query(query, conn, params=(student_id, f"{month:02d}", str(year)))
    conn.close()
    
    if df.empty:
        return "❌ या महिन्यासाठी उपस्थिती नाही"
    
    summary = "उपस्थिती सारांश:\n"
    for _, row in df.iterrows():
        summary += f"{row['status']}: {row['count']} दिवस\n"
    
    return summary

# Marks Entry System
def enter_marks(student_id, subject, exam_type, marks, total_marks, grade):
    conn = sqlite3.connect('school_data.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO marks (student_id, subject, exam_type, marks, total_marks, grade, entered_on)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', (student_id, subject, exam_type, marks, total_marks, grade, datetime.now()))
    
    conn.commit()
    conn.close()
    return f"✅ गुण नोंदवले: {student_id} - {subject} - {marks}/{total_marks}"

# Report Generation
def generate_udise_report():
    return generate_udise_data()

def generate_sports_report():
    return generate_sports_data()

# Login System
def login_user(username, password, user_type):
    return authenticate_user(username, password, user_type)

# Main Application with Tabs
with gr.Blocks(theme=gr.themes.Soft(), title="संपूर्ण शाळा व्यवस्थापन प्रणाली") as app:
    
    gr.Markdown("# 🏫 संपूर्ण शाळा व्यवस्थापन प्रणाली")
    
    # Login Section
    with gr.Tab("🔐 लॉगिन"):
        with gr.Row():
            with gr.Column():
                gr.Markdown("### प्रवेश करा")
                login_username = gr.Textbox(label="वापरकर्तानाव")
                login_password = gr.Textbox(label="पासवर्ड", type="password")
                user_type = gr.Radio(["विद्यार्थी", "पालक", "शिक्षक", "प्राचार्य"], label="तुमचा प्रकार")
                login_btn = gr.Button("लॉगिन करा")
                login_output = gr.Textbox(label="लॉगिन निकाल")
    
    # Student Management
    with gr.Tab("📚 विद्यार्थी व्यवस्थापन"):
        with gr.Row():
            with gr.Column():
                gr.Markdown("### नवीन विद्यार्थी नोंदणी")
                stu_name = gr.Textbox(label="पूर्ण नाव")
                stu_grade = gr.Dropdown([f"{i}वी" for i in range(1, 13)], label="इयत्ता")
                stu_rollno = gr.Number(label="रोल नंबर")
                stu_parent_name = gr.Textbox(label="पालकाचे नाव")
                stu_parent_phone = gr.Textbox(label="मोबाइल नंबर")
                stu_address = gr.Textbox(label="पत्ता")
                add_student_btn = gr.Button("विद्यार्थी नोंदवा")
            
            with gr.Column():
                student_output = gr.Textbox(label="निकाल")
                gr.Markdown("### विद्यार्थी शोधा")
                search_student_id = gr.Textbox(label="विद्यार्थी ID")
                search_student_btn = gr.Button("शोधा")
                student_search_output = gr.Textbox(label="माहिती")
    
    # Attendance System
    with gr.Tab("📅 उपस्थिती व्यवस्थापन"):
        with gr.Row():
            with gr.Column():
                gr.Markdown("### दैनिक उपस्थिती")
                att_student_id = gr.Textbox(label="विद्यार्थी ID")
                att_date = gr.Textbox(label="तारीख (YYYY-MM-DD)", value=date.today().isoformat())
                att_status = gr.Radio(["Present", "Absent", "Late", "Halfday"], label="स्थिती")
                att_remarks = gr.Textbox(label="शेरा")
                mark_attendance_btn = gr.Button("उपस्थिती नोंदवा")
            
            with gr.Column():
                attendance_output = gr.Textbox(label="निकाल")
                gr.Markdown("### उपस्थिती अहवाल")
                report_student_id = gr.Textbox(label="विद्यार्थी ID")
                report_month = gr.Number(label="महिना (1-12)", value=datetime.now().month)
                report_year = gr.Number(label="वर्ष", value=datetime.now().year)
                get_attendance_report_btn = gr.Button("अहवाल मिळवा")
                attendance_report_output = gr.Textbox(label="उपस्थिती अहवाल")
    
    # Marks Management
    with gr.Tab("📊 गुण व्यवस्थापन"):
        with gr.Row():
            with gr.Column():
                gr.Markdown("### गुण नोंदणी")
                marks_student_id = gr.Textbox(label="विद्यार्थी ID")
                marks_subject = gr.Dropdown([
                    "मराठी", "हिंदी", "इंग्रजी", "गणित", "विज्ञान", "सामाजिक शास्त्र",
                    "इतिहास", "भूगोल", "शारीरिक शिक्षण", "कला", "संगीत"
                ], label="विषय")
                marks_exam_type = gr.Dropdown(["Unit Test", "Semester", "Final", "Practical"], label="परीक्षा प्रकार")
                marks_obtained = gr.Number(label="मिळालेले गुण")
                marks_total = gr.Number(label="कमाल गुण", value=100)
                marks_grade = gr.Textbox(label="श्रेणी")
                enter_marks_btn = gr.Button("गुण नोंदवा")
            
            with gr.Column():
                marks_output = gr.Textbox(label="निकाल")
    
    # Reports Generation
    with gr.Tab("📈 अहवाल तयार करा"):
        with gr.Row():
            with gr.Column():
                gr.Markdown("### UDISE+ डेटा अहवाल")
                udise_report_btn = gr.Button("UDISE+ अहवाल तयार करा")
                udise_output = gr.Textbox(label="UDISE+ अहवाल")
            
            with gr.Column():
                gr.Markdown("### क्रीडा अहवाल")
                sports_report_btn = gr.Button("क्रीडा अहवाल तयार करा")
                sports_output = gr.Textbox(label="क्रीडा अहवाल")
        
        with gr.Row():
            with gr.Column():
                gr.Markdown("### शैक्षणिक अहवाल")
                academic_report_btn = gr.Button("शैक्षणिक अहवाल तयार करा")
                academic_output = gr.Textbox(label="शैक्षणिक अहवाल")
    
    # Data Export
    with gr.Tab("💾 डेटा निर्यात करा"):
        with gr.Row():
            with gr.Column():
                gr.Markdown("### Excel मध्ये सेव्ह करा")
                export_students_btn = gr.Button("विद्यार्थी डेटा Excel मध्ये")
                export_teachers_btn = gr.Button("शिक्षक डेटा Excel मध्ये")
                export_output = gr.Textbox(label="निर्यात निकाल")
            
            with gr.Column():
                gr.Markdown("### PDF अहवाल")
                pdf_report_btn = gr.Button("PDF अहवाल तयार करा")
                pdf_output = gr.Textbox(label="PDF निकाल")

    # Button Actions
    add_student_btn.click(add_student, [
        stu_name, stu_grade, stu_rollno, stu_parent_name, stu_parent_phone, stu_address
    ], student_output)
    
    mark_attendance_btn.click(mark_attendance, [
        att_student_id, att_date, att_status, att_remarks
    ], attendance_output)
    
    get_attendance_report_btn.click(get_attendance_summary, [
        report_student_id, report_month, report_year
    ], attendance_report_output)
    
    enter_marks_btn.click(enter_marks, [
        marks_student_id, marks_subject, marks_exam_type, marks_obtained, marks_total, marks_grade
    ], marks_output)
    
    udise_report_btn.click(generate_udise_report, [], udise_output)
    sports_report_btn.click(generate_sports_report, [], sports_output)
    
    login_btn.click(login_user, [login_username, login_password, user_type], login_output)

if __name__ == "__main__":
    app.launch(share=True)