import sqlite3
import pandas as pd
from datetime import datetime

def init_database():
    conn = sqlite3.connect('school_data.db')
    cursor = conn.cursor()
    
    # Students Table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            grade TEXT NOT NULL,
            roll_no INTEGER,
            parent_name TEXT,
            parent_phone TEXT,
            address TEXT,
            created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Teachers Table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS teachers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            subject TEXT,
            qualifications TEXT,
            phone TEXT,
            email TEXT,
            created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Attendance Table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS attendance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER,
            date DATE,
            status TEXT,
            remarks TEXT,
            FOREIGN KEY (student_id) REFERENCES students (id)
        )
    ''')
    
    # Marks Table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS marks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER,
            subject TEXT,
            exam_type TEXT,
            marks REAL,
            total_marks REAL,
            grade TEXT,
            entered_on TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students (id)
        )
    ''')
    
    # Users Table for Login
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            password TEXT,
            user_type TEXT,
            student_id INTEGER,
            teacher_id INTEGER,
            FOREIGN KEY (student_id) REFERENCES students (id),
            FOREIGN KEY (teacher_id) REFERENCES teachers (id)
        )
    ''')
    
    conn.commit()
    conn.close()

def add_student(name, grade, roll_no, parent_name, parent_phone, address):
    conn = sqlite3.connect('school_data.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO students (name, grade, roll_no, parent_name, parent_phone, address)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (name, grade, roll_no, parent_name, parent_phone, address))
    
    student_id = cursor.lastrowid
    conn.commit()
    conn.close()
    
    return f"✅ विद्यार्थी नोंदणी झाली! ID: {student_id}"