database.py

import sqlite3
import os

DB_NAME = "vqa_stem_education.db"

def get_connection():
    db_path = os.path.abspath(DB_NAME)
    print(f"📍 Database: {db_path}")
    conn = sqlite3.connect(DB_NAME)
    conn.execute("PRAGMA foreign_keys = ON")  # ✅ Enable foreign key constraints
    return conn

def create_database():
    conn = get_connection()
    cursor = conn.cursor()
    
    # Create all tables with authentication support
    cursor.executescript("""
    CREATE TABLE IF NOT EXISTS Student (
        studentID INTEGER PRIMARY KEY AUTOINCREMENT,
        fullName VARCHAR NOT NULL,
        gender VARCHAR,
        dateOfBirth TEXT,
        class VARCHAR,
        guardianCtcNo VARCHAR,
        guardianEmail VARCHAR,
        username VARCHAR UNIQUE NOT NULL,
        password VARCHAR NOT NULL
    );
    
    CREATE TABLE IF NOT EXISTS Teacher (
        teacherID INTEGER PRIMARY KEY AUTOINCREMENT,
        fullName VARCHAR NOT NULL,
        gender VARCHAR,
        CtcNumber VARCHAR,
        email VARCHAR,
        AssignedSubject VARCHAR,
        username VARCHAR UNIQUE NOT NULL,
        password VARCHAR NOT NULL
    );
    
    CREATE TABLE IF NOT EXISTS ImageQuestion (
        questionID INTEGER PRIMARY KEY AUTOINCREMENT,
        studentID INTEGER,
        imagePath TEXT,
        questionText TEXT,
        submissionDate TEXT,
        FOREIGN KEY (studentID) REFERENCES Student(studentID)
    );
    
    CREATE TABLE IF NOT EXISTS AIAnswer (
        answerID INTEGER PRIMARY KEY AUTOINCREMENT,
        questionID INTEGER,
        answerText TEXT,
        generatedDate TEXT,
        processingTime REAL,
        FOREIGN KEY (questionID) REFERENCES ImageQuestion(questionID)
    );
    
    CREATE TABLE IF NOT EXISTS ContentLibrary (
        contentID INTEGER PRIMARY KEY AUTOINCREMENT,
        teacherID INTEGER,
        title VARCHAR NOT NULL,
        description TEXT,
        filePath VARCHAR,
        uploadDate TEXT,
        FOREIGN KEY (teacherID) REFERENCES Teacher(teacherID)
    );
    
    CREATE TABLE IF NOT EXISTS Bookmark (
        bookmarkID INTEGER PRIMARY KEY AUTOINCREMENT,
        studentID INTEGER,
        contentType VARCHAR,
        contentTypeID INTEGER,
        dateSaved TEXT,
        FOREIGN KEY (studentID) REFERENCES Student(studentID)
    );
    
    CREATE TABLE IF NOT EXISTS Challenge (
        challengeID INTEGER PRIMARY KEY AUTOINCREMENT,
        teacherID INTEGER,
        title VARCHAR NOT NULL,
        challengePath VARCHAR,
        description TEXT,
        challengeQuestion TEXT,
        datePosted TEXT,
        FOREIGN KEY (teacherID) REFERENCES Teacher(teacherID)
    );
    
    CREATE TABLE IF NOT EXISTS ChallengeSubmission (
        submissionID INTEGER PRIMARY KEY AUTOINCREMENT,
        challengeID INTEGER,
        studentID INTEGER,
        answerText TEXT,
        submissionDate TEXT,
        score INTEGER,
        FOREIGN KEY (challengeID) REFERENCES Challenge(challengeID),
        FOREIGN KEY (studentID) REFERENCES Student(studentID)
    );
    
    CREATE TABLE IF NOT EXISTS HelpTutorial (
        helpId INTEGER PRIMARY KEY AUTOINCREMENT,
        teacherId INTEGER,
        title VARCHAR NOT NULL,
        description TEXT,
        filePath VARCHAR,
        uploadDate TEXT,
        FOREIGN KEY (teacherId) REFERENCES Teacher(teacherID)
    );
    
    CREATE TABLE IF NOT EXISTS Feedback (
        feedbackID INTEGER PRIMARY KEY AUTOINCREMENT,
        userType VARCHAR,
        userID INTEGER,
        category VARCHAR,
        feedbackText TEXT,
        rating INTEGER,
        dateSubmitted TEXT
    );
    
    CREATE TABLE IF NOT EXISTS ProgressReport (
        reportID INTEGER PRIMARY KEY AUTOINCREMENT,
        studentID INTEGER NOT NULL,
        submissionID INTEGER,
        answerID INTEGER,
        summaryScore REAL NOT NULL,
        comment TEXT,
        dateSubmitted TEXT NOT NULL,
        semester VARCHAR(10) NOT NULL,
        FOREIGN KEY (studentID) REFERENCES Student(studentID),
        FOREIGN KEY (submissionID) REFERENCES ChallengeSubmission(submissionID),
        FOREIGN KEY (answerID) REFERENCES AIAnswer(answerID)
    );
    """)
    
    conn.commit()
    conn.close()
    print("✅ Database and tables created successfully!")

def insert_demo_data():
    conn = get_connection()
    cursor = conn.cursor()
    
    # Password hash for "123456"
    import hashlib
    demo_password = hashlib.sha256("123456".encode()).hexdigest()
    
    # ✅ FIXED: Insert demo student with CORRECT column names
    cursor.execute("SELECT COUNT(*) FROM Student")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO Student (fullName, username, password, class, guardianCtcNo, guardianEmail, dateOfBirth, gender) 
            VALUES ('Ali Bin Abu', 'ali', ?, '3A', '0123456789', 'guardian@email.com', '15/03/10', 'Lelaki')
        """, (demo_password,))
        print("👤 Demo student created - Username: ali, Password: 123456")
    
    # ✅ FIXED: Insert demo teacher with CORRECT column names
    cursor.execute("SELECT COUNT(*) FROM Teacher")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO Teacher (fullName, username, password, AssignedSubject) 
            VALUES ('Encik Rahman', 'rahman', ?, 'Sains')
        """, (demo_password,))
        print("👨‍🏫 Demo teacher created - Username: rahman, Password: 123456")
    
    # Insert demo content
    cursor.execute("SELECT COUNT(*) FROM ContentLibrary")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO ContentLibrary (teacherID, title, description, filePath, uploadDate)
            VALUES (1, 'Pengenalan STEM', 'Belajar asas STEM untuk kanak-kanak', 'stem_intro.pdf', '2024-01-01')
        """)
        print("📚 Demo content created")
    
    # Insert demo challenge
    cursor.execute("SELECT COUNT(*) FROM Challenge")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO Challenge (teacherID, title, challengePath, description, challengeQuestion, datePosted)
            VALUES (1, 'Cabaran Sains Minggu Ini', 'challenge1.png', 
                    'Cuba jawab soalan sains ini!', 
                    'Apakah tiga keadaan jirim?', 
                    '2024-01-15')
        """)
        print("🎯 Demo challenge created")
    
    # Insert demo tutorial
    cursor.execute("SELECT COUNT(*) FROM HelpTutorial")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO HelpTutorial (teacherId, title, description, filePath, uploadDate)
            VALUES (1, 'Cara Guna Sistem VQA', 'Panduan langkah demi langkah', 'tutorial1.pdf', '2024-01-01')
        """)
        print("📖 Demo tutorial created")
    
    conn.commit()
    conn.close()
    print("📚 Demo data inserted!")

def check_database_status():
    """Simple database status check"""
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [row[0] for row in cursor.fetchall()]
    
    # Show exact location
    db_path = os.path.abspath(DB_NAME)
    print(f"📍 FULL PATH: {db_path}")
    print(f"📋 Tables: {', '.join(tables)}")
    
    if os.path.exists(db_path):
        file_size = os.path.getsize(db_path)
        print(f"✅ File exists! Size: {file_size:,} bytes ({file_size/1024:.2f} KB)")
    else:
        print(f"❌ File not found!")
        conn.close()
        return
    
    # Show counts
    try:
        cursor.execute("SELECT COUNT(*) FROM Student")
        student_count = cursor.fetchone()[0]
        print(f"👥 Students: {student_count}")
        
        cursor.execute("SELECT COUNT(*) FROM Teacher")
        teacher_count = cursor.fetchone()[0]
        print(f"👨‍🏫 Teachers: {teacher_count}")
        
        cursor.execute("SELECT COUNT(*) FROM ImageQuestion")
        question_count = cursor.fetchone()[0]
        print(f"🖼️ Questions: {question_count}")
        
        cursor.execute("SELECT COUNT(*) FROM Feedback")
        feedback_count = cursor.fetchone()[0]
        print(f"💬 Feedback: {feedback_count}")
        
    except Exception as e:
        print(f"⚠️ Error counting records: {e}")
    
    conn.close()