-- Prep Application Schema
-- Run against your MySQL/MariaDB database:
--   mysql -u root -p your_db_name < database/schema.sql

CREATE TABLE IF NOT EXISTS documents (
    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,
    status            ENUM('uploaded', 'text_extracted', 'analyzed', 'failed')
                                      NOT NULL DEFAULT 'uploaded',
    error_message     TEXT            NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_status      (status),
    INDEX idx_created_at  (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS ai_results (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id   INT UNSIGNED    NOT NULL,
    task_type     VARCHAR(50)     NOT NULL COMMENT 'e.g. questions, suggestions',
    result_json   LONGTEXT        NOT NULL COMMENT 'AI response as JSON string',
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_document_id (document_id),
    INDEX idx_task_type   (task_type),

    CONSTRAINT fk_ai_results_document
        FOREIGN KEY (document_id) REFERENCES documents (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------------
-- Generated Questions (presentation practice feature)
-- 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,
    generation_batch                INT UNSIGNED    NOT NULL DEFAULT 1,
    generation_focus                VARCHAR(50)     NOT NULL DEFAULT 'initial',
    question_hash                   CHAR(64)        NULL,
    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),
    INDEX idx_gq_doc_batch    (document_id, generation_batch),
    INDEX idx_gq_doc_focus    (document_id, generation_focus),
    INDEX idx_gq_question_hash (question_hash),

    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;


-- ------------------------------------------------------------------
-- Question Generation Runs (batch metadata for "Add More Questions")
-- Each row records one generation request — how many questions were
-- asked for, how many inserted, how many duplicates skipped.
-- ------------------------------------------------------------------

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;


-- ------------------------------------------------------------------
-- Practice Answers (presentation practice feature)
-- Each row is one user attempt at answering a generated_question.
-- Multiple rows per question = 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;