CREATE DATABASE IF NOT EXISTS imscopyc_mis_classwork
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE imscopyc_mis_classwork;

CREATE TABLE IF NOT EXISTS quizzes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiz_slug VARCHAR(80) NOT NULL,
    title VARCHAR(255) NOT NULL,
    is_open TINYINT(1) NOT NULL DEFAULT 1,
    opened_at DATETIME NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_quiz_slug (quiz_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS submissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiz_slug VARCHAR(80) NOT NULL DEFAULT 'class-work-1',
    student_name VARCHAR(255) NOT NULL,
    index_number VARCHAR(100) NOT NULL,
    submitted_at DATETIME NOT NULL,
    responses LONGTEXT NOT NULL,
    score INT UNSIGNED NULL,
    max_score INT UNSIGNED NULL,
    UNIQUE KEY uq_quiz_index (quiz_slug, index_number),
    INDEX idx_submitted_at (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS questions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiz_slug VARCHAR(80) NOT NULL DEFAULT 'class-work-1',
    question_key VARCHAR(20) NOT NULL,
    label TEXT NOT NULL,
    option_a TEXT NOT NULL,
    option_b TEXT NOT NULL,
    option_c TEXT NOT NULL,
    option_d TEXT NOT NULL,
    answer ENUM('a', 'b', 'c', 'd') NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_quiz_question (quiz_slug, question_key),
    INDEX idx_quiz_slug (quiz_slug),
    INDEX idx_sort_order (sort_order),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;