models.py (Database Models)

"""
Database models for ThutoAI School Integration
"""
from datetime import datetime
import sqlite3
import os

class DatabaseManager:
    def __init__(self, db_path="thutoai_school.db"):
        self.db_path = db_path
        self.init_database()
    
    def get_connection(self):
        return sqlite3.connect(self.db_path)
    
    def init_database(self):
        """Initialize the database with all required tables"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # Students table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                email TEXT,
                grade_level TEXT,
                class_section TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Announcements table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS announcements (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                content TEXT NOT NULL,
                priority TEXT DEFAULT 'normal',
                target_audience TEXT DEFAULT 'all',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                expires_at TIMESTAMP,
                is_active BOOLEAN DEFAULT 1
            )
        ''')
        
        # Examinations table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS examinations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                exam_name TEXT NOT NULL,
                subject TEXT NOT NULL,
                exam_date DATE NOT NULL,
                exam_time TIME,
                duration_minutes INTEGER,
                location TEXT,
                grade_level TEXT,
                instructions TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Tests/Assignments table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tests_assignments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                subject TEXT NOT NULL,
                type TEXT NOT NULL, -- 'test', 'assignment', 'quiz', 'project'
                due_date DATE NOT NULL,
                due_time TIME,
                description TEXT,
                grade_level TEXT,
                total_marks INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Grades table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS grades (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id TEXT NOT NULL,
                subject TEXT NOT NULL,
                assessment_type TEXT NOT NULL, -- 'test', 'exam', 'assignment', 'quiz'
                assessment_name TEXT NOT NULL,
                marks_obtained REAL NOT NULL,
                total_marks REAL NOT NULL,
                percentage REAL NOT NULL,
                grade_letter TEXT,
                date_recorded DATE NOT NULL,
                teacher_comments TEXT,
                FOREIGN KEY (student_id) REFERENCES students (student_id)
            )
        ''')
        
        # School Events table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS school_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                event_name TEXT NOT NULL,
                event_date DATE NOT NULL,
                event_time TIME,
                location TEXT,
                description TEXT,
                event_type TEXT, -- 'holiday', 'sports', 'cultural', 'academic'
                is_holiday BOOLEAN DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Admin Users table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin_users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                full_name TEXT NOT NULL,
                email TEXT,
                role TEXT DEFAULT 'admin', -- 'admin', 'teacher', 'principal'
                is_active BOOLEAN DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_login TIMESTAMP
            )
        ''')
        
        # Syllabus table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS syllabus (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                subject TEXT NOT NULL,
                grade_level TEXT NOT NULL,
                chapter_number INTEGER,
                chapter_title TEXT NOT NULL,
                topics TEXT NOT NULL, -- JSON array of topics
                learning_objectives TEXT,
                duration_weeks INTEGER,
                resources TEXT, -- JSON array of resources/books
                assessment_methods TEXT,
                created_by TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_active BOOLEAN DEFAULT 1
            )
        ''')
        
        # Timetable table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS timetable (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                class_section TEXT NOT NULL, -- e.g., "10-A", "11-B"
                day_of_week TEXT NOT NULL, -- 'Monday', 'Tuesday', etc.
                period_number INTEGER NOT NULL,
                start_time TIME NOT NULL,
                end_time TIME NOT NULL,
                subject TEXT NOT NULL,
                teacher_name TEXT,
                room_number TEXT,
                created_by TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_active BOOLEAN DEFAULT 1
            )
        ''')
        
        # File Uploads table (for syllabus documents, etc.)
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS file_uploads (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                filename TEXT NOT NULL,
                original_filename TEXT NOT NULL,
                file_path TEXT NOT NULL,
                file_type TEXT NOT NULL, -- 'syllabus', 'timetable', 'announcement'
                file_size INTEGER,
                uploaded_by TEXT NOT NULL,
                related_id INTEGER, -- ID of related record (syllabus, announcement, etc.)
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        conn.commit()
        conn.close()
        print("Database initialized successfully!")
    
    def add_sample_data(self):
        """Add sample data for testing"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # Sample announcements
        announcements = [
            ("Important: Mid-term Exam Schedule Released", 
             "The mid-term examination schedule has been posted. Please check your exam dates and prepare accordingly. All exams will be held in the main examination hall.", 
             "high", "all"),
            ("Library Hours Extended", 
             "Due to upcoming exams, library hours have been extended until 8 PM on weekdays.", 
             "normal", "all"),
            ("Sports Day Registration Open", 
             "Registration for annual sports day is now open. Last date for registration: March 15th.", 
             "normal", "all")
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO announcements (title, content, priority, target_audience)
            VALUES (?, ?, ?, ?)
        ''', announcements)
        
        # Sample examinations
        examinations = [
            ("Mid-term Mathematics", "Mathematics", "2024-03-20", "09:00", 180, "Hall A", "Grade 10", "Bring calculator and geometry box"),
            ("Mid-term Physics", "Physics", "2024-03-22", "09:00", 180, "Hall B", "Grade 10", "Formula sheet will be provided"),
            ("Mid-term Chemistry", "Chemistry", "2024-03-25", "09:00", 180, "Hall A", "Grade 10", "Periodic table will be provided")
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO examinations (exam_name, subject, exam_date, exam_time, duration_minutes, location, grade_level, instructions)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', examinations)
        
        # Sample tests/assignments
        tests = [
            ("Chapter 5 Quiz", "Mathematics", "quiz", "2024-03-15", "10:00", "Quadratic equations quiz", "Grade 10", 20),
            ("Physics Lab Report", "Physics", "assignment", "2024-03-18", "23:59", "Submit pendulum experiment report", "Grade 10", 25),
            ("Chemistry Unit Test", "Chemistry", "test", "2024-03-16", "11:00", "Atomic structure and bonding", "Grade 10", 50)
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO tests_assignments (title, subject, type, due_date, due_time, description, grade_level, total_marks)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', tests)
        
        # Sample student
        cursor.execute('''
            INSERT OR IGNORE INTO students (student_id, name, email, grade_level, class_section)
            VALUES (?, ?, ?, ?, ?)
        ''', ("STU001", "John Doe", "[email protected]", "Grade 10", "10-A"))
        
        # Sample grades
        grades = [
            ("STU001", "Mathematics", "test", "Algebra Test", 85, 100, 85.0, "A", "2024-02-15", "Excellent work!"),
            ("STU001", "Physics", "quiz", "Motion Quiz", 18, 20, 90.0, "A+", "2024-02-20", "Great understanding"),
            ("STU001", "Chemistry", "assignment", "Lab Report 1", 22, 25, 88.0, "A", "2024-02-25", "Good analysis")
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO grades (student_id, subject, assessment_type, assessment_name, marks_obtained, total_marks, percentage, grade_letter, date_recorded, teacher_comments)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', grades)
        
        # Sample events
        events = [
            ("Spring Break", "2024-03-30", "00:00", "School", "Spring holidays begin", "holiday", 1),
            ("Science Fair", "2024-04-15", "09:00", "School Auditorium", "Annual science exhibition", "academic", 0),
            ("Sports Day", "2024-04-20", "08:00", "School Ground", "Annual sports competition", "sports", 0)
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO school_events (event_name, event_date, event_time, location, description, event_type, is_holiday)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', events)
        
        # Sample admin user (password: admin123)
        import hashlib
        password_hash = hashlib.sha256("admin123".encode()).hexdigest()
        cursor.execute('''
            INSERT OR IGNORE INTO admin_users (username, password_hash, full_name, email, role)
            VALUES (?, ?, ?, ?, ?)
        ''', ("admin", password_hash, "School Administrator", "[email protected]", "admin"))
        
        # Sample syllabus data
        syllabus_data = [
            ("Mathematics", "Grade 10", 1, "Algebra Fundamentals", 
             '["Linear equations", "Quadratic equations", "Polynomials", "Factoring"]',
             "Students will understand and solve algebraic equations", 4,
             '["NCERT Mathematics Grade 10", "RD Sharma", "Online Khan Academy"]',
             "Tests, Assignments, Projects", "admin"),
            ("Physics", "Grade 10", 1, "Light and Reflection",
             '["Laws of reflection", "Mirrors", "Refraction", "Lenses"]',
             "Understanding light behavior and optical instruments", 3,
             '["NCERT Physics Grade 10", "HC Verma", "Lab experiments"]',
             "Practical exams, Theory tests", "admin"),
            ("Chemistry", "Grade 10", 1, "Acids, Bases and Salts",
             '["Properties of acids", "Properties of bases", "pH scale", "Salt formation"]',
             "Chemical properties and reactions of acids and bases", 3,
             '["NCERT Chemistry Grade 10", "Lab manual"]',
             "Lab reports, Unit tests", "admin")
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO syllabus (subject, grade_level, chapter_number, chapter_title, topics, learning_objectives, duration_weeks, resources, assessment_methods, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', syllabus_data)
        
        # Sample timetable data for Grade 10-A
        timetable_data = [
            ("10-A", "Monday", 1, "09:00", "09:45", "Mathematics", "Mr. Smith", "Room 101", "admin"),
            ("10-A", "Monday", 2, "09:45", "10:30", "Physics", "Ms. Johnson", "Room 201", "admin"),
            ("10-A", "Monday", 3, "10:45", "11:30", "Chemistry", "Dr. Brown", "Lab 1", "admin"),
            ("10-A", "Monday", 4, "11:30", "12:15", "English", "Mrs. Davis", "Room 102", "admin"),
            ("10-A", "Monday", 5, "13:00", "13:45", "History", "Mr. Wilson", "Room 103", "admin"),
            ("10-A", "Monday", 6, "13:45", "14:30", "Physical Education", "Coach Miller", "Gym", "admin"),
            
            ("10-A", "Tuesday", 1, "09:00", "09:45", "Physics", "Ms. Johnson", "Room 201", "admin"),
            ("10-A", "Tuesday", 2, "09:45", "10:30", "Mathematics", "Mr. Smith", "Room 101", "admin"),
            ("10-A", "Tuesday", 3, "10:45", "11:30", "English", "Mrs. Davis", "Room 102", "admin"),
            ("10-A", "Tuesday", 4, "11:30", "12:15", "Chemistry", "Dr. Brown", "Lab 1", "admin"),
            ("10-A", "Tuesday", 5, "13:00", "13:45", "Computer Science", "Mr. Tech", "Computer Lab", "admin"),
            ("10-A", "Tuesday", 6, "13:45", "14:30", "Art", "Ms. Creative", "Art Room", "admin"),
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO timetable (class_section, day_of_week, period_number, start_time, end_time, subject, teacher_name, room_number, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', timetable_data)
        
        conn.commit()
        conn.close()
        print("Sample data added successfully!")

# Initialize database when module is imported
if __name__ == "__main__":
    db = DatabaseManager()
    db.add_sample_data()