import pandas as pd
import sqlite3
from datetime import datetime

def generate_udise_data():
    conn = sqlite3.connect('school_data.db')
    
    # Student Count by Grade
    student_stats = pd.read_sql_query('''
        SELECT grade, COUNT(*) as count 
        FROM students 
        GROUP BY grade
    ''', conn)
    
    # Teacher Count
    teacher_count = pd.read_sql_query('SELECT COUNT(*) as count FROM teachers', conn)['count'][0]
    
    conn.close()
    
    report = "📊 UDISE+ डेटा अहवाल\n\n"
    report += f"कुल विद्यार्थी: {student_stats['count'].sum()}\n"
    report += f"कुल शिक्षक: {teacher_count}\n\n"
    report += "इयत्तानुसार विद्यार्थी:\n"
    
    for _, row in student_stats.iterrows():
        report += f"{row['grade']}: {row['count']} विद्यार्थी\n"
    
    return report

def generate_sports_data():
    sports_achievements = [
        "क्रिकेट: जिल्हा स्पर्धा - प्रथम क्रमांक",
        "खो-खो: विभागीय स्पर्धा - द्वितीय क्रमांक",
        "कबड्डी: तालुका स्पर्धा - प्रथम क्रमांक"
    ]
    
    report = "🏆 क्रीडा अहवाल\n\n"
    report += "स्पर्धा व कामगिरी:\n"
    for achievement in sports_achievements:
        report += f"• {achievement}\n"
    
    return report