-- ═══════════════════════════════════════════════════════════════════════
-- Exam Format Profiles  (AI Exam Generator: house-style reference)
-- One reusable "how our papers look" profile per subject × class. A school
-- uploads a real past paper or template ONCE (image / PDF / Word / text);
-- the file is extracted and distilled into an editable format spec that the
-- generator conforms to on every run. Content still comes from study
-- materials — this governs structure, layout, wording and mark spread only.
--
-- Idempotent; self-heals via lib/exam_utils.php on first use.
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `exam_format_profiles` (
    `id`                 INT AUTO_INCREMENT PRIMARY KEY,
    `subject_id`         INT          NOT NULL,
    `class_name`         VARCHAR(100) NOT NULL,
    `subject_name`       VARCHAR(150) NULL,
    `source_type`        ENUM('image','pdf','word','text') NOT NULL DEFAULT 'text',
    `original_filename`  VARCHAR(255) NULL,
    `stored_filename`    VARCHAR(255) NULL,
    `file_size`          INT          NOT NULL DEFAULT 0,
    `raw_extracted_text` LONGTEXT     NULL,
    `format_spec_json`   LONGTEXT     NULL,
    `summary_text`       TEXT         NULL,
    `status`             ENUM('processing','ready','failed') NOT NULL DEFAULT 'processing',
    `status_note`        VARCHAR(255) NULL,
    `uploaded_by`        INT          NULL,
    `created_at`         TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_format_profile_subject_class` (`subject_id`, `class_name`),
    KEY `idx_format_profile_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- History of superseded profiles so a fresh upload never destroys a good one.
CREATE TABLE IF NOT EXISTS `exam_format_profile_versions` (
    `id`                 INT AUTO_INCREMENT PRIMARY KEY,
    `profile_id`         INT          NOT NULL,
    `subject_id`         INT          NOT NULL,
    `class_name`         VARCHAR(100) NOT NULL,
    `source_type`        ENUM('image','pdf','word','text') NOT NULL DEFAULT 'text',
    `original_filename`  VARCHAR(255) NULL,
    `stored_filename`    VARCHAR(255) NULL,
    `raw_extracted_text` LONGTEXT     NULL,
    `format_spec_json`   LONGTEXT     NULL,
    `summary_text`       TEXT         NULL,
    `status`             VARCHAR(20)  NULL,
    `archived_by`        INT          NULL,
    `created_at`         TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_format_profile_versions_profile` (`profile_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
