-- Migration 002: "Add More Questions" Feature
-- Adds generation tracking columns to generated_questions and
-- a new question_generation_runs table for batch metadata.
--
-- Idempotent — uses ALTER TABLE with column-existence guards where
-- possible (MySQL), and CREATE TABLE IF NOT EXISTS.
-- Does not drop or modify any existing data.
--
-- Run against your database:
--   mysql -u root -p prep < database/migration_002_add_more_questions.sql

-- ------------------------------------------------------------------
-- 1. generated_questions — new columns
-- ------------------------------------------------------------------

-- generation_batch
ALTER TABLE generated_questions
    ADD COLUMN IF NOT EXISTS generation_batch INT UNSIGNED NOT NULL DEFAULT 1;

-- generation_focus
ALTER TABLE generated_questions
    ADD COLUMN IF NOT EXISTS generation_focus VARCHAR(50) NOT NULL DEFAULT 'initial';

-- question_hash
ALTER TABLE generated_questions
    ADD COLUMN IF NOT EXISTS question_hash CHAR(64) NULL;

-- ------------------------------------------------------------------
-- 2. generated_questions — new indexes
-- ------------------------------------------------------------------

ALTER TABLE generated_questions
    ADD INDEX IF NOT EXISTS idx_gq_doc_batch (document_id, generation_batch);

ALTER TABLE generated_questions
    ADD INDEX IF NOT EXISTS idx_gq_doc_focus (document_id, generation_focus);

ALTER TABLE generated_questions
    ADD INDEX IF NOT EXISTS idx_gq_question_hash (question_hash);

-- ------------------------------------------------------------------
-- 3. Backfill existing rows
--    generation_batch and generation_focus already default to 1 and
--    'initial' via the column defaults, so they are set automatically.
--    question_hash is computed from the normalized question text.
-- ------------------------------------------------------------------

UPDATE generated_questions
   SET question_hash = SHA2(LOWER(TRIM(question)), 256)
 WHERE question_hash IS NULL;

-- ------------------------------------------------------------------
-- 4. question_generation_runs (batch metadata)
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS question_generation_runs (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id                 INT UNSIGNED    NOT NULL,
    ai_result_id                INT UNSIGNED    NULL,
    generation_batch            INT UNSIGNED    NOT NULL,
    generation_focus            VARCHAR(50)     NOT NULL,
    requested_count             INT UNSIGNED    NOT NULL DEFAULT 5,
    inserted_count              INT UNSIGNED    NOT NULL DEFAULT 0,
    skipped_duplicate_count     INT UNSIGNED    NOT NULL DEFAULT 0,
    status                      ENUM('requested','completed','partial','failed')
                                                NOT NULL DEFAULT 'requested',
    error_message               TEXT            NULL,
    created_at                  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_qgr_document_id (document_id),
    INDEX idx_qgr_batch       (generation_batch),
    INDEX idx_qgr_focus       (generation_focus),
    INDEX idx_qgr_status      (status),
    INDEX idx_qgr_created_at  (created_at),

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

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