-- Medical SBA Question Generator and Moderation System
-- Database schema recreation script
-- Updated to reflect current implemented application features

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

USE exam_questions;

-- -----------------------------------------------------
-- Table: workspaces
-- Stores browser-based workspace sessions
-- -----------------------------------------------------
DROP TABLE IF EXISTS workspaces;

CREATE TABLE workspaces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workspace_id VARCHAR(64) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: generation_jobs
-- Tracks question generation batches/jobs
-- -----------------------------------------------------
DROP TABLE IF EXISTS generation_jobs;

CREATE TABLE generation_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workspace_id VARCHAR(64) NOT NULL,
    specialty VARCHAR(255),
    condition_name VARCHAR(255),
    presentation VARCHAR(255),
    learning_outcome TEXT,
    num_questions INT NOT NULL,
    questions_completed INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'running',
    skills TEXT,
    summative_formative VARCHAR(50) NULL,
    year_level INT NULL,
    tutor_comments TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_generation_jobs_workspace_id (workspace_id),
    INDEX idx_generation_jobs_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: questions
-- Stores generated and moderated questions
-- -----------------------------------------------------
DROP TABLE IF EXISTS questions;

CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workspace_id VARCHAR(64) NOT NULL,
    source_type VARCHAR(20),
    source_question_id INT NULL,

    title TEXT,
    stem TEXT,
    lead_in TEXT,

    option_a TEXT,
    option_b TEXT,
    option_c TEXT,
    option_d TEXT,
    option_e TEXT,

    correct_answer TEXT,
    correct_answer_letter VARCHAR(2),

    justification TEXT,

    specialty VARCHAR(255),
    condition_name VARCHAR(255),
    presentation VARCHAR(255),
    skill VARCHAR(255),
    year_level INT NULL,
    summative_formative VARCHAR(50) NULL,
    tutor_comments TEXT NULL,
    tags TEXT,

    status VARCHAR(20) DEFAULT 'Draft',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL,

    INDEX idx_questions_workspace_id (workspace_id),
    INDEX idx_questions_status (status),
    INDEX idx_questions_skill (skill),
    INDEX idx_questions_condition_name (condition_name),
    INDEX idx_questions_presentation (presentation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Suggested optional seed row pattern (commented out)
-- -----------------------------------------------------
-- INSERT INTO workspaces (workspace_id) VALUES ('example_workspace_id');