-- Migration: Create ai_performance_reports table
-- Feature: AI Student Performance Ranking
-- Run this script once against the portal database before using the AI report endpoint.

CREATE TABLE IF NOT EXISTS ai_performance_reports (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id              INT NOT NULL,
    term_id                 INT NOT NULL,
    class                   VARCHAR(50) NOT NULL,
    section                 VARCHAR(50) NOT NULL,
    student_id              INT NOT NULL,
    exam_score              DECIMAL(5,2) NOT NULL DEFAULT 0,
    test_score              DECIMAL(5,2) NOT NULL DEFAULT 0,
    class_performance       DECIMAL(5,2) NOT NULL DEFAULT 0,
    weekly_assessment       DECIMAL(5,2) NOT NULL DEFAULT 0,
    performance_score       DECIMAL(5,2) NOT NULL DEFAULT 0,
    trend_score             DECIMAL(6,2) NOT NULL DEFAULT 0,
    ai_band                 VARCHAR(20) NOT NULL,
    risk_level              VARCHAR(20) DEFAULT NULL,
    strength_subjects       JSON DEFAULT NULL,
    improvement_subjects    JSON DEFAULT NULL,
    admin_recommendation    TEXT DEFAULT NULL,
    teacher_recommendation  TEXT DEFAULT NULL,
    parent_recommendation   TEXT DEFAULT NULL,
    generated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_ai_report (session_id, term_id, class, section, student_id),
    KEY idx_student_session_term (student_id, session_id, term_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
