-- ==========================================================
-- EasyResult Online Exam
-- Exam Template Module - Part 1
-- MariaDB 10.6+ / MySQL 8+
-- ==========================================================
--
-- Purpose:
--   Stores reusable Exam Builder templates.
--
-- Architecture:
--   Exam Template -> Create Exam -> exams
--
-- Important:
--   This module does NOT modify the existing exams table.
--   Existing Exam Builder / Teacher / Admin authentication remains
--   untouched.
-- ==========================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS exam_templates (
    template_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Public/reference identifiers
    template_uuid CHAR(36) NOT NULL,
    template_code VARCHAR(30) NOT NULL,

    -- Template information
    template_title VARCHAR(255) NOT NULL,
    template_title_bn VARCHAR(255) NULL,
    description TEXT NULL,
    instructions TEXT NULL,

    -- Exam defaults
    exam_type VARCHAR(30) NOT NULL DEFAULT 'PRACTICE',
    duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30,

    total_questions SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    total_marks DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    pass_marks DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    -- Marking configuration
    negative_marking TINYINT(1) NOT NULL DEFAULT 0,
    default_negative_marks DECIMAL(8,2) NOT NULL DEFAULT 0.00,

    -- Question presentation defaults
    shuffle_questions TINYINT(1) NOT NULL DEFAULT 1,
    shuffle_options TINYINT(1) NOT NULL DEFAULT 1,

    -- Exam attempt/result defaults
    attempt_limit SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    show_result TINYINT(1) NOT NULL DEFAULT 1,
    show_correct_answer TINYINT(1) NOT NULL DEFAULT 0,

    -- Template lifecycle
    -- 0 = DRAFT
    -- 1 = ACTIVE
    -- 2 = ARCHIVED
    -- 3 = DELETED
    status TINYINT UNSIGNED NOT NULL DEFAULT 0,

    -- Audit
    created_by_user_id BIGINT UNSIGNED NULL,
    updated_by_user_id BIGINT UNSIGNED NULL,
    archived_by_user_id BIGINT UNSIGNED NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    archived_at DATETIME NULL,

    PRIMARY KEY (template_id),

    UNIQUE KEY uq_exam_templates_uuid (template_uuid),
    UNIQUE KEY uq_exam_templates_code (template_code),

    KEY idx_exam_templates_status (status),
    KEY idx_exam_templates_type (exam_type),
    KEY idx_exam_templates_created_by (created_by_user_id),
    KEY idx_exam_templates_updated_by (updated_by_user_id),
    KEY idx_exam_templates_archived_by (archived_by_user_id),
    KEY idx_exam_templates_created_at (created_at),
    KEY idx_exam_templates_status_created (status, created_at),

    CONSTRAINT chk_exam_templates_duration
        CHECK (duration_minutes BETWEEN 1 AND 1440),

    CONSTRAINT chk_exam_templates_questions
        CHECK (total_questions >= 0),

    CONSTRAINT chk_exam_templates_marks
        CHECK (total_marks >= 0),

    CONSTRAINT chk_exam_templates_pass_marks
        CHECK (pass_marks >= 0),

    CONSTRAINT chk_exam_templates_negative_marks
        CHECK (default_negative_marks >= 0),

    CONSTRAINT chk_exam_templates_attempt_limit
        CHECK (attempt_limit BETWEEN 1 AND 100),

    CONSTRAINT chk_exam_templates_status
        CHECK (status BETWEEN 0 AND 3)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


-- ==========================================================
-- Template Sections
-- ==========================================================
--
-- Example:
--   Section A = Bangla
--   Section B = English
--   Section C = Mathematics
--
-- A template can contain multiple sections.
-- These sections will later be copied into exam_sections
-- when an actual exam is created from the template.
-- ==========================================================

CREATE TABLE IF NOT EXISTS exam_template_sections (
    template_section_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    template_id BIGINT UNSIGNED NOT NULL,

    section_title VARCHAR(255) NOT NULL,
    section_title_bn VARCHAR(255) NULL,
    section_code VARCHAR(50) NULL,

    section_order SMALLINT UNSIGNED NOT NULL DEFAULT 1,

    question_limit SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    section_marks DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    instructions TEXT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (template_section_id),

    UNIQUE KEY uq_template_section_code (
        template_id,
        section_code
    ),

    UNIQUE KEY uq_template_section_order (
        template_id,
        section_order
    ),

    KEY idx_template_sections_template (
        template_id
    ),

    CONSTRAINT fk_template_sections_template
        FOREIGN KEY (template_id)
        REFERENCES exam_templates (template_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT chk_template_section_order
        CHECK (section_order >= 1),

    CONSTRAINT chk_template_section_marks
        CHECK (section_marks >= 0)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


-- ==========================================================
-- Optional verification
-- ==========================================================
-- SELECT * FROM exam_templates ORDER BY template_id DESC;
-- SELECT * FROM exam_template_sections
-- ORDER BY template_id, section_order;