-- Migration 003: Exam Prep Module
-- Adds exam_materials, exam_sets, exam_questions, exam_attempts, and
-- exam_attempt_answers tables.
--
-- Idempotent — safe to run multiple times (uses CREATE TABLE IF NOT EXISTS).
-- Does not drop or modify any existing Presentation Prep tables or rows.
--
-- Run against your database:
--   mysql -u root -p prep < database/migration_003_exam_prep.sql

-- ------------------------------------------------------------------
-- 1. exam_materials
--    Uploaded study materials for the Exam Prep feature.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS exam_materials (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    original_filename   VARCHAR(255)    NOT NULL,
    stored_filename     VARCHAR(255)    NOT NULL,
    file_extension      VARCHAR(10)     NOT NULL,
    mime_type           VARCHAR(127)    NOT NULL,
    file_size           INT UNSIGNED    NOT NULL COMMENT 'Size in bytes',
    extracted_text      LONGTEXT        NULL,
    structured_content_json LONGTEXT    NULL,
    status              ENUM('uploaded', 'text_extracted', 'questions_generated', 'failed')
                                        NOT NULL DEFAULT 'uploaded',
    error_message       TEXT            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_em_status      (status),
    INDEX idx_em_created_at  (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------------
-- 2. exam_sets
--    A generated batch of exam questions for a given material.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS exam_sets (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    material_id             INT UNSIGNED    NOT NULL,
    set_number              INT UNSIGNED    NOT NULL,
    requested_count         INT UNSIGNED    NOT NULL DEFAULT 50,
    generated_count         INT UNSIGNED    NOT NULL DEFAULT 0,
    skipped_duplicate_count INT UNSIGNED    NOT NULL DEFAULT 0,
    status                  ENUM('requested', 'generated', 'partial', 'failed')
                                            NOT NULL DEFAULT 'requested',
    error_message           TEXT            NULL,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_es_material_id (material_id),
    INDEX idx_es_set_number  (set_number),
    INDEX idx_es_status      (status),

    CONSTRAINT fk_es_material
        FOREIGN KEY (material_id) REFERENCES exam_materials (id)
        ON DELETE CASCADE,

    CONSTRAINT uq_es_material_set_number
        UNIQUE (material_id, set_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------------
-- 3. exam_questions
--    Individual multiple-choice questions belonging to an exam set.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS exam_questions (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    material_id         INT UNSIGNED    NOT NULL,
    exam_set_id         INT UNSIGNED    NOT NULL,
    question            TEXT            NOT NULL,
    option_a            TEXT            NOT NULL,
    option_b            TEXT            NOT NULL,
    option_c            TEXT            NOT NULL,
    option_d            TEXT            NOT NULL,
    correct_option      ENUM('A','B','C','D') NOT NULL,
    explanation         TEXT            NULL,
    source_location     VARCHAR(255)    NOT NULL COMMENT 'e.g. Slide 4, Page 12',
    source_quote        TEXT            NULL COMMENT 'Verbatim quote from material',
    topic               VARCHAR(150)    NULL,
    difficulty          ENUM('easy','medium','hard') DEFAULT 'medium',
    question_hash       CHAR(64)        NULL,
    sort_order          INT UNSIGNED    NOT NULL DEFAULT 0,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_eq_material_id (material_id),
    INDEX idx_eq_exam_set_id (exam_set_id),
    INDEX idx_eq_question_hash (question_hash),

    CONSTRAINT fk_eq_material
        FOREIGN KEY (material_id) REFERENCES exam_materials (id)
        ON DELETE CASCADE,

    CONSTRAINT fk_eq_exam_set
        FOREIGN KEY (exam_set_id) REFERENCES exam_sets (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------------
-- 4. exam_attempts
--    One row per exam sitting. Timer state and final score are stored here.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS exam_attempts (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exam_set_id         INT UNSIGNED    NOT NULL,
    material_id         INT UNSIGNED    NOT NULL,
    started_at          DATETIME        NULL,
    expires_at          DATETIME        NULL,
    submitted_at        DATETIME        NULL,
    status              ENUM('not_started', 'in_progress', 'submitted', 'auto_submitted', 'expired')
                                        NOT NULL DEFAULT 'not_started',
    score               INT UNSIGNED    NULL,
    total_questions     INT UNSIGNED    NOT NULL DEFAULT 0,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_ea_exam_set_id (exam_set_id),
    INDEX idx_ea_material_id (material_id),
    INDEX idx_ea_status      (status),

    CONSTRAINT fk_ea_exam_set
        FOREIGN KEY (exam_set_id) REFERENCES exam_sets (id)
        ON DELETE CASCADE,

    CONSTRAINT fk_ea_material
        FOREIGN KEY (material_id) REFERENCES exam_materials (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------------
-- 5. exam_attempt_answers
--    One row per answer selected during an attempt.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS exam_attempt_answers (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    attempt_id          INT UNSIGNED    NOT NULL,
    question_id         INT UNSIGNED    NOT NULL,
    selected_option     ENUM('A','B','C','D') NULL,
    is_correct          TINYINT(1)      NULL,
    answered_at         DATETIME        NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_eaa_attempt_id  (attempt_id),
    INDEX idx_eaa_question_id (question_id),

    CONSTRAINT fk_eaa_attempt
        FOREIGN KEY (attempt_id) REFERENCES exam_attempts (id)
        ON DELETE CASCADE,

    CONSTRAINT fk_eaa_question
        FOREIGN KEY (question_id) REFERENCES exam_questions (id)
        ON DELETE CASCADE,

    CONSTRAINT uq_eaa_attempt_question
        UNIQUE (attempt_id, question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;