-- Migration 001: Presentation Practice Feature
-- Adds generated_questions and practice_answers tables.
-- Existing documents and ai_results tables are untouched.
--
-- Idempotent — safe to run multiple times (uses CREATE TABLE IF NOT EXISTS).
-- Does not drop or modify any existing tables or rows.
--
-- Run against your database:
--   mysql -u root -p prep < database/migration_001_practice_feature.sql

-- ------------------------------------------------------------------
-- 1. generated_questions
--    Each row is one audience question extracted from ai_results JSON.
--    Questions can be answered individually via the practice feature.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS generated_questions (
    id                              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id                     INT UNSIGNED    NOT NULL,
    ai_result_id                    INT UNSIGNED    NULL,
    category                        VARCHAR(100)    NOT NULL,
    question                        TEXT            NOT NULL,
    why_this_might_be_asked         TEXT            NULL,
    suggested_answer_direction      TEXT            NULL,
    supporting_document_reference   TEXT            NULL,
    sort_order                      INT UNSIGNED    NOT NULL DEFAULT 0,
    created_at                      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_gq_document_id  (document_id),
    INDEX idx_gq_ai_result    (ai_result_id),
    INDEX idx_gq_category     (category),
    INDEX idx_gq_created_at   (created_at),

    CONSTRAINT fk_gq_document
        FOREIGN KEY (document_id) REFERENCES documents (id)
        ON DELETE CASCADE,

    CONSTRAINT fk_gq_ai_result
        FOREIGN KEY (ai_result_id) REFERENCES ai_results (id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------------
-- 2. practice_answers
--    Each row is one user attempt at answering a generated_question.
--    Multiple rows with the same question_id = multiple retries.
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS practice_answers (
    id                              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id                     INT UNSIGNED    NOT NULL,
    question_id                     INT UNSIGNED    NOT NULL,
    user_answer                     LONGTEXT        NOT NULL,
    feedback_json                   LONGTEXT        NULL,
    overall_score                   TINYINT UNSIGNED NULL,
    accuracy_score                  TINYINT UNSIGNED NULL,
    completeness_score              TINYINT UNSIGNED NULL,
    clarity_score                   TINYINT UNSIGNED NULL,
    document_grounding_score        TINYINT UNSIGNED NULL,
    presentation_readiness_score    TINYINT UNSIGNED NULL,
    status                          ENUM('submitted','graded','failed')
                                                NOT NULL DEFAULT 'submitted',
    error_message                   TEXT            NULL,
    attempt_number                  INT UNSIGNED    NOT NULL DEFAULT 1,
    created_at                      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_pa_document_id  (document_id),
    INDEX idx_pa_question_id  (question_id),
    INDEX idx_pa_status       (status),
    INDEX idx_pa_created_at   (created_at),

    CONSTRAINT fk_pa_document
        FOREIGN KEY (document_id) REFERENCES documents (id)
        ON DELETE CASCADE,

    CONSTRAINT fk_pa_question
        FOREIGN KEY (question_id) REFERENCES generated_questions (id)
        ON DELETE CASCADE,

    CONSTRAINT uq_pa_question_attempt
        UNIQUE (question_id, attempt_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;