SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS api_logs;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS quiz_results;
DROP TABLE IF EXISTS lesson_history;
DROP TABLE IF EXISTS teacher_feedback;
DROP TABLE IF EXISTS conversation;
DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS student_progress;
DROP TABLE IF EXISTS student_memory;
DROP TABLE IF EXISTS class_sessions;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS teachers;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Teachers Table
CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    total_practice_hours DECIMAL(8,2) DEFAULT 0.00,
    global_teaching_score DECIMAL(5,2) DEFAULT 0.00,
    global_engagement_score DECIMAL(5,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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;

-- 4. Student Realtime Session Progress
CREATE TABLE student_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    student_id INT NOT NULL,
    current_confidence DECIMAL(3,2) NOT NULL,
    current_curiosity DECIMAL(3,2) NOT NULL,
    current_attention DECIMAL(3,2) NOT NULL,
    current_state ENUM('Listening', 'Speaking', 'Thinking', 'Distracted', 'Hand_Raised') DEFAULT 'Listening',
    mute_status TINYINT(1) DEFAULT 0,
    score_delta INT DEFAULT 0,
    UNIQUE KEY uq_session_student (session_id, student_id),
    FOREIGN KEY (session_id) REFERENCES class_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(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;

-- 6. Attendance Table
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    student_id INT NOT NULL,
    is_present TINYINT(1) DEFAULT 1,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES class_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Live Unified Conversation Ledger
CREATE TABLE conversation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    speaker_type ENUM('Teacher', 'Student', 'System') NOT NULL,
    speaker_id INT NULL, -- NULL if Teacher or System
    speaker_name VARCHAR(100) NOT NULL,
    utterance TEXT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES class_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Post-Class Teacher Feedback / Comprehensive Evaluations
CREATE TABLE teacher_feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL UNIQUE,
    overall_score DECIMAL(5,2) NOT NULL,
    voice_clarity_score DECIMAL(5,2) NOT NULL,
    explanation_quality_score DECIMAL(5,2) NOT NULL,
    engagement_score DECIMAL(5,2) NOT NULL,
    pace_speed_score DECIMAL(5,2) NOT NULL,
    interaction_score DECIMAL(5,2) NOT NULL,
    doubts_solved INT DEFAULT 0,
    strengths TEXT NOT NULL,
    weaknesses TEXT NOT NULL,
    improvement_suggestions TEXT NOT NULL,
    detailed_ai_report LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES class_sessions(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;

-- 10. Quiz Session Performance Records
CREATE TABLE quiz_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    student_id INT NOT NULL,
    question_asked TEXT NOT NULL,
    student_response TEXT NOT NULL,
    is_correct TINYINT(1) NOT NULL,
    points_awarded INT DEFAULT 0,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES class_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(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;

-- 12. Complete API Call / Prompt Evaluation Logger
CREATE TABLE api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NULL,
    endpoint VARCHAR(255) NOT NULL,
    request_payload LONGTEXT NOT NULL,
    response_payload LONGTEXT NOT NULL,
    execution_time_ms INT NOT NULL,
    http_status INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- SEED DATA: SEEDING DEFAULT TEACHER & 20 DISTINCT INDIAN STUDENTS
-- ==========================================

-- Seed default teacher (Password: AdminClassroom2026!)
INSERT INTO teachers (id, name, email, password_hash, total_practice_hours, global_teaching_score, global_engagement_score)
VALUES (1, 'Aditya Verma', 'teacher@classroom.in', '$2y$10$vO9R/B88zL7W3S8qR67X0uF9rNfE26YxRve4L7fP3T1G1v0W/Dqy6', 14.50, 84.50, 79.00);

-- 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');