
-- 2. Students Master Table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    avatar_url VARCHAR(255) NOT NULL,
    class_level VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    personality_type ENUM('Curious', 'Shy', 'Topper', 'Naughty', 'Funny', 'Silent', 'Overconfident', 'Slow Learner', 'Fast Learner', 'Creative', 'Practical', 'Electronics Enthusiast', 'Coding Geek', 'AI Buff', 'Mechanically Inclined') NOT NULL,
    base_confidence DECIMAL(3,2) NOT NULL, -- 0.00 to 1.00
    base_curiosity DECIMAL(3,2) NOT NULL,
    base_attention_span DECIMAL(3,2) NOT NULL,
    english_proficiency ENUM('Basic', 'Intermediate', 'Fluent') NOT NULL,
    science_knowledge ENUM('Low', 'Medium', 'High') NOT NULL,
    robotics_interest DECIMAL(3,2) NOT NULL,
    voice_identifier VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Class Sessions Table
CREATE TABLE class_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    topic VARCHAR(255) NOT NULL,
    current_mode ENUM('Lecture', 'Discussion', 'Quiz', 'Practical') DEFAULT 'Lecture',
    status ENUM('Scheduled', 'Active', 'Paused', 'Completed') DEFAULT 'Scheduled',
    duration_seconds INT DEFAULT 0,
    started_at TIMESTAMP NULL DEFAULT NULL,
    ended_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Long-Term Student Memory
CREATE TABLE student_memory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    concept_key VARCHAR(150) NOT NULL,
    understanding_level DECIMAL(3,2) NOT NULL DEFAULT 0.00, -- 0.00 to 1.00
    last_tested_at TIMESTAMP NULL DEFAULT NULL,
    times_encountered INT DEFAULT 1,
    misconceptions_log TEXT DEFAULT NULL,
    UNIQUE KEY uq_student_concept (student_id, concept_key),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Lesson History Tracker
CREATE TABLE lesson_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    topic_delivered VARCHAR(255) NOT NULL,
    core_vocabulary_used TEXT NOT NULL,
    session_date DATE NOT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Configuration Engine Settings Table
CREATE TABLE settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed 20 Unique Indian STEM Student Profiles
INSERT INTO students (id, name, avatar_url, class_level, age, gender, personality_type, base_confidence, base_curiosity, base_attention_span, english_proficiency, science_knowledge, robotics_interest, voice_identifier) VALUES
(1, 'Aarav Sharma', 'assets/images/avatars/aarav.png', 'Class 8', 13, 'Male', 'Topper', 0.90, 0.85, 0.95, 'Fluent', 'High', 0.90, 'Google UK English Male'),
(2, 'Ananya Iyer', 'assets/images/avatars/ananya.png', 'Class 8', 13, 'Female', 'Curious', 0.75, 0.95, 0.80, 'Fluent', 'High', 0.95, 'Google US English Female'),
(3, 'Kabir Patel', 'assets/images/avatars/kabir.png', 'Class 8', 14, 'Male', 'Naughty', 0.85, 0.60, 0.40, 'Intermediate', 'Medium', 0.70, 'Google India English Male'),
(4, 'Diya Joshi', 'assets/images/avatars/diya.png', 'Class 8', 13, 'Female', 'Shy', 0.40, 0.70, 0.85, 'Intermediate', 'Medium', 0.65, 'Google UK English Female'),
(5, 'Vivaan Mehta', 'assets/images/avatars/vivaan.png', 'Class 8', 13, 'Male', 'Electronics Enthusiast', 0.80, 0.90, 0.85, 'Fluent', 'High', 0.98, 'Google India English Male'),
(6, 'Isha Nair', 'assets/images/avatars/isha.png', 'Class 8', 13, 'Female', 'Creative', 0.70, 0.88, 0.75, 'Fluent', 'Medium', 0.85, 'Google US English Female'),
(7, 'Arjun Rao', 'assets/images/avatars/arjun.png', 'Class 8', 14, 'Male', 'Overconfident', 0.95, 0.50, 0.55, 'Fluent', 'Medium', 0.60, 'Google UK English Male'),
(8, 'Riya Das', 'assets/images/avatars/riya.png', 'Class 8', 13, 'Female', 'Silent', 0.35, 0.65, 0.90, 'Intermediate', 'High', 0.70, 'Google India English Female'),
(9, 'Sai Reddy', 'assets/images/avatars/sai.png', 'Class 8', 14, 'Male', 'Practical', 0.78, 0.85, 0.80, 'Intermediate', 'High', 0.92, 'Google India English Male'),
(10, 'Prisha Kapoor', 'assets/images/avatars/prisha.png', 'Class 8', 13, 'Female', 'Fast Learner', 0.88, 0.82, 0.92, 'Fluent', 'High', 0.88, 'Google UK English Female'),
(11, 'Rohan Deshmukh', 'assets/images/avatars/rohan.png', 'Class 8', 14, 'Male', 'Slow Learner', 0.45, 0.55, 0.60, 'Basic', 'Low', 0.50, 'Google India English Male'),
(12, 'Meera Pillai', 'assets/images/avatars/meera.png', 'Class 8', 13, 'Female', 'Funny', 0.80, 0.70, 0.50, 'Fluent', 'Medium', 0.65, 'Google US English Female'),
(13, 'Aditya Choudhury', 'assets/images/avatars/aditya.png', 'Class 8', 13, 'Male', 'Coding Geek', 0.85, 0.92, 0.90, 'Fluent', 'High', 0.95, 'Google UK English Male'),
(14, 'Sanya Malhotra', 'assets/images/avatars/sanya.png', 'Class 8', 13, 'Female', 'AI Buff', 0.82, 0.95, 0.85, 'Fluent', 'High', 0.90, 'Google UK English Female'),
(15, 'Ishaan Dubey', 'assets/images/avatars/ishaan.png', 'Class 8', 14, 'Male', 'Mechanically Inclined', 0.75, 0.80, 0.78, 'Intermediate', 'Medium', 0.94, 'Google India English Male'),
(16, 'Tanvi Bhat', 'assets/images/avatars/tanvi.png', 'Class 8', 13, 'Female', 'Topper', 0.92, 0.88, 0.96, 'Fluent', 'High', 0.89, 'Google US English Female'),
(17, 'Tejas Gowda', 'assets/images/avatars/tejas.png', 'Class 8', 14, 'Male', 'Naughty', 0.78, 0.50, 0.45, 'Basic', 'Low', 0.55, 'Google India English Male'),
(18, 'Anika Gupta', 'assets/images/avatars/anika.png', 'Class 8', 13, 'Female', 'Shy', 0.50, 0.68, 0.80, 'Intermediate', 'Medium', 0.60, 'Google India English Female'),
(19, 'Devansh Singh', 'assets/images/avatars/devansh.png', 'Class 8', 14, 'Male', 'Overconfident', 0.90, 0.55, 0.60, 'Fluent', 'Medium', 0.65, 'Google UK English Male'),
(20, 'Kriti Saxena', 'assets/images/avatars/kriti.png', 'Class 8', 13, 'Female', 'Creative', 0.72, 0.85, 0.78, 'Fluent', 'Medium', 0.82, 'Google UK English Female');

-- Seed Admin Configuration Records
INSERT INTO settings (setting_key, setting_value) VALUES
('GEMINI_API_KEY', ''),
('GLOBAL_DIFFICULTY', 'Medium'),
('AUTOMATIC_QUESTION_FREQUENCY', 'Medium'),
('SYSTEM_SPEAKING_SPEED', '1.0');