-- CourseBook + QuestionBank 统一数据模型 SQL DDL
-- 可在 draw.io 中通过 Arrange > Insert > SQL 自动生成 ER 图

CREATE TABLE CourseBook (
    id VARCHAR(36) PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    edition VARCHAR(50),
    version VARCHAR(50),
    status VARCHAR(20),
    publisher_info TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    created_by BIGINT,
    updated_by BIGINT
);

CREATE TABLE StructureNode (
    id VARCHAR(36) PRIMARY KEY,
    course_book_id VARCHAR(36) NOT NULL REFERENCES CourseBook(id),
    parent_id VARCHAR(36) REFERENCES StructureNode(id),
    level INT NOT NULL,
    node_type VARCHAR(50) NOT NULL,
    title VARCHAR(500),
    seq INT NOT NULL,
    metadata JSON,
    created_at TIMESTAMP
);

CREATE TABLE Material (
    id VARCHAR(36) PRIMARY KEY,
    genre VARCHAR(50),
    modality JSON,
    content TEXT,
    resources_url JSON,
    source VARCHAR(20),
    owner_type VARCHAR(20) NOT NULL,
    owner_id VARCHAR(36) NOT NULL,
    review_status VARCHAR(20),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    created_by BIGINT
);

CREATE TABLE QuestionBlock (
    id VARCHAR(36) PRIMARY KEY,
    block_type VARCHAR(50) NOT NULL,
    primary_skill VARCHAR(10),
    secondary_skills JSON,
    instruction TEXT,
    difficulty INT,
    owner_type VARCHAR(20) NOT NULL,
    owner_id VARCHAR(36) NOT NULL,
    review_status VARCHAR(20),
    public_tag JSON,
    tags JSON,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    created_by BIGINT
);

CREATE TABLE QuestionBlock_Material (
    question_block_id VARCHAR(36) NOT NULL REFERENCES QuestionBlock(id),
    material_id VARCHAR(36) NOT NULL REFERENCES Material(id),
    role_in_block VARCHAR(50),
    PRIMARY KEY (question_block_id, material_id)
);

CREATE TABLE Question (
    id VARCHAR(36) PRIMARY KEY,
    question_block_id VARCHAR(36) NOT NULL REFERENCES QuestionBlock(id),
    seq INT NOT NULL,
    question_type VARCHAR(50) NOT NULL,
    content JSON,
    translation_direction VARCHAR(20),
    source VARCHAR(20),
    created_at TIMESTAMP
);

CREATE TABLE Point (
    id VARCHAR(36) PRIMARY KEY,
    question_id VARCHAR(36) NOT NULL REFERENCES Question(id),
    seq INT NOT NULL,
    position VARCHAR(100),
    answer TEXT,
    hint JSON,
    match_rule JSON,
    scoring VARCHAR(20),
    created_at TIMESTAMP
);

CREATE TABLE ContentBlock (
    id VARCHAR(36) PRIMARY KEY,
    structure_node_id VARCHAR(36) NOT NULL REFERENCES StructureNode(id),
    seq INT NOT NULL,
    block_type VARCHAR(50) NOT NULL,
    title VARCHAR(500),
    is_assessed BOOLEAN,
    visible_to VARCHAR(20),
    material_id VARCHAR(36) REFERENCES Material(id),
    question_block_id VARCHAR(36) REFERENCES QuestionBlock(id),
    payload JSON,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    created_by BIGINT
);
