-- 高中数学知识图谱数据库架构
-- 基于三层架构设计：Knowledge（知识层）、Method（方法层）、Problem（题目层）

-- 创建数据库（如果需要）
-- CREATE DATABASE math_knowledge_graph;
-- \c math_knowledge_graph;

-- 启用必要的扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ===========================================
-- 1. 元数据表（教材信息）
-- ===========================================

-- 教材表
CREATE TABLE textbooks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    publisher VARCHAR(50) DEFAULT '人教版',
    version VARCHAR(20) DEFAULT 'A版',
    volume_type VARCHAR(20) CHECK (volume_type IN ('必修', '选择性必修')),
    volume_number INTEGER,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 章节表
CREATE TABLE chapters (
    id SERIAL PRIMARY KEY,
    textbook_id INTEGER REFERENCES textbooks(id) ON DELETE CASCADE,
    chapter_number INTEGER NOT NULL,
    chapter_name VARCHAR(100) NOT NULL,
    chapter_order INTEGER NOT NULL,
    description TEXT,
    page_range VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(textbook_id, chapter_number)
);

-- 小节表
CREATE TABLE sections (
    id SERIAL PRIMARY KEY,
    chapter_id INTEGER REFERENCES chapters(id) ON DELETE CASCADE,
    section_number VARCHAR(10) NOT NULL,
    section_name VARCHAR(100) NOT NULL,
    section_order INTEGER NOT NULL,
    page_start INTEGER,
    page_end INTEGER,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(chapter_id, section_number)
);

-- ===========================================
-- 2. 知识层（Knowledge Layer）
-- ===========================================

-- 知识点主表
CREATE TABLE knowledge_points (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL, -- 如 K1-1-1-01
    name VARCHAR(100) NOT NULL,
    level VARCHAR(10) CHECK (level IN ('二级', '三级')),
    type VARCHAR(20) CHECK (type IN ('概念/定义', '定理/性质', '公式')),
    importance VARCHAR(10) CHECK (importance IN ('核心', '重要', '基础')),
    section_id INTEGER REFERENCES sections(id),

    -- 核心内容（JSON格式存储复杂结构）
    core_content JSONB,

    -- 原理说明
    principle_explanation JSONB,

    -- 适用条件
    application_conditions JSONB,

    -- 关联内容
    related_content JSONB,

    -- 考查方式
    examination_methods TEXT[],

    -- 教材位置
    textbook_location VARCHAR(100),

    -- 创建和更新时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 知识点关系表
CREATE TABLE knowledge_relations (
    id SERIAL PRIMARY KEY,
    source_knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
    target_knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
    relation_type VARCHAR(20) CHECK (relation_type IN ('前置知识', '包含关系', '关联内容')),
    strength FLOAT DEFAULT 1.0 CHECK (strength >= 0 AND strength <= 1),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_knowledge_id, target_knowledge_id, relation_type)
);

-- ===========================================
-- 3. 方法层（Method Layer）
-- ===========================================

-- 解题方法主表
CREATE TABLE methods (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL, -- 如 M1-1-1-01
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) CHECK (type IN ('解题方法', '计算技巧', '证明方法')),
    difficulty_level INTEGER CHECK (difficulty_level >= 1 AND difficulty_level <= 5),
    section_id INTEGER REFERENCES sections(id),

    -- 适用场景
    application_scenarios JSONB,

    -- 数学思想
    mathematical_thoughts TEXT[],

    -- 解题策略
    problem_solving_strategy TEXT,

    -- 教材位置
    textbook_location VARCHAR(100),

    -- 描述
    description TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 方法步骤表
CREATE TABLE method_steps (
    id SERIAL PRIMARY KEY,
    method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
    step_number INTEGER NOT NULL,
    step_description TEXT NOT NULL,
    key_points TEXT[],
    common_mistakes TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(method_id, step_number)
);

-- 方法常见错误表
CREATE TABLE method_common_errors (
    id SERIAL PRIMARY KEY,
    method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
    error_description TEXT NOT NULL,
    cause_analysis TEXT,
    correct_approach TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 方法支撑知识点表
CREATE TABLE method_support_knowledge (
    id SERIAL PRIMARY KEY,
    method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
    knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
    support_type VARCHAR(20) CHECK (support_type IN ('直接支撑', '间接支撑')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(method_id, knowledge_id)
);

-- ===========================================
-- 4. 题目层（Problem Layer）
-- ===========================================

-- 题目主表
CREATE TABLE problems (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL, -- 如 T1-1-1-E01
    title TEXT,
    problem_type VARCHAR(20) CHECK (problem_type IN ('例题', '练习题', '习题', '思考题')),

    -- 来源信息
    source_info JSONB,

    -- 题目内容
    stem TEXT, -- 题干
    full_content TEXT, -- 完整题目
    images_info JSONB, -- 图片信息

    -- 题型分类
    problem_category JSONB,

    -- 难度评估
    difficulty_info JSONB,

    -- 教材位置
    textbook_location VARCHAR(100),

    -- 小题数量
    subquestion_count INTEGER DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 小题表
CREATE TABLE problem_subquestions (
    id SERIAL PRIMARY KEY,
    problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
    subquestion_number VARCHAR(10) NOT NULL, -- 如 (1), (2)
    content TEXT,
    difficulty_level INTEGER CHECK (difficulty_level >= 1 AND difficulty_level <= 5),
    solution TEXT,
    answer TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(problem_id, subquestion_number)
);

-- 题目知识点标注表
CREATE TABLE problem_knowledge_tags (
    id SERIAL PRIMARY KEY,
    problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
    subquestion_id INTEGER REFERENCES problem_subquestions(id) ON DELETE CASCADE,
    knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
    tag_type VARCHAR(20) CHECK (tag_type IN ('主要考查', '辅助涉及')),
    weight FLOAT DEFAULT 1.0 CHECK (weight > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(problem_id, subquestion_id, knowledge_id, tag_type)
);

-- 题目方法标注表
CREATE TABLE problem_method_tags (
    id SERIAL PRIMARY KEY,
    problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
    subquestion_id INTEGER REFERENCES problem_subquestions(id) ON DELETE CASCADE,
    method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
    usage_frequency VARCHAR(20) CHECK (usage_frequency IN ('主要方法', '辅助方法')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(problem_id, subquestion_id, method_id)
);

-- ===========================================
-- 5. 学习分析和扩展表
-- ===========================================

-- 学习路径表（为个性化推荐预留）
CREATE TABLE learning_paths (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    knowledge_sequence JSONB,
    difficulty_progression JSONB,
    estimated_hours INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 难度统计表
CREATE TABLE difficulty_stats (
    id SERIAL PRIMARY KEY,
    stat_type VARCHAR(20) NOT NULL,
    reference_type VARCHAR(20) NOT NULL, -- knowledge, method, problem
    reference_id INTEGER NOT NULL,
    avg_difficulty FLOAT,
    difficulty_distribution JSONB,
    sample_size INTEGER,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(stat_type, reference_type, reference_id)
);

-- ===========================================
-- 6. 全文搜索支持
-- ===========================================

-- 创建全文搜索配置（中文支持）
CREATE TEXT SEARCH CONFIGURATION chinese (COPY = simple);

-- 搜索索引更新函数
CREATE OR REPLACE FUNCTION update_search_vectors()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新知识点的搜索向量
    IF TG_TABLE_NAME = 'knowledge_points' THEN
        UPDATE knowledge_points
        SET search_vector =
            setweight(to_tsvector('chinese', COALESCE(name, '')), 'A') ||
            setweight(to_tsvector('chinese', COALESCE(core_content::text, '')), 'B') ||
            setweight(to_tsvector('chinese', COALESCE(principle_explanation::text, '')), 'C')
        WHERE id = NEW.id;
    END IF;

    -- 更新方法的搜索向量
    IF TG_TABLE_NAME = 'methods' THEN
        UPDATE methods
        SET search_vector =
            setweight(to_tsvector('chinese', COALESCE(name, '')), 'A') ||
            setweight(to_tsvector('chinese', COALESCE(problem_solving_strategy, '')), 'B') ||
            setweight(to_tsvector('chinese', COALESCE(description, '')), 'C')
        WHERE id = NEW.id;
    END IF;

    -- 更新题目的搜索向量
    IF TG_TABLE_NAME = 'problems' THEN
        UPDATE problems
        SET search_vector =
            setweight(to_tsvector('chinese', COALESCE(title, '')), 'A') ||
            setweight(to_tsvector('chinese', COALESCE(stem, '')), 'B') ||
            setweight(to_tsvector('chinese', COALESCE(full_content, '')), 'C')
        WHERE id = NEW.id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为主要表添加搜索向量字段
ALTER TABLE knowledge_points ADD COLUMN search_vector tsvector;
ALTER TABLE methods ADD COLUMN search_vector tsvector;
ALTER TABLE problems ADD COLUMN search_vector tsvector;

-- 创建触发器
CREATE TRIGGER update_knowledge_search_vector
    AFTER INSERT OR UPDATE ON knowledge_points
    FOR EACH ROW EXECUTE FUNCTION update_search_vectors();

CREATE TRIGGER update_method_search_vector
    AFTER INSERT OR UPDATE ON methods
    FOR EACH ROW EXECUTE FUNCTION update_search_vectors();

CREATE TRIGGER update_problem_search_vector
    AFTER INSERT OR UPDATE ON problems
    FOR EACH ROW EXECUTE FUNCTION update_search_vectors();

-- ===========================================
-- 7. 索引创建
-- ===========================================

-- 基础索引
CREATE INDEX idx_knowledge_points_code ON knowledge_points(code);
CREATE INDEX idx_knowledge_points_level ON knowledge_points(level);
CREATE INDEX idx_knowledge_points_type ON knowledge_points(type);
CREATE INDEX idx_knowledge_points_importance ON knowledge_points(importance);
CREATE INDEX idx_knowledge_points_section ON knowledge_points(section_id);

CREATE INDEX idx_methods_code ON methods(code);
CREATE INDEX idx_methods_type ON methods(type);
CREATE INDEX idx_methods_difficulty ON methods(difficulty_level);
CREATE INDEX idx_methods_section ON methods(section_id);

CREATE INDEX idx_problems_code ON problems(code);
CREATE INDEX idx_problems_type ON problems(problem_type);
CREATE INDEX idx_problems_difficulty ON problems((difficulty_info->>'整体难度')::int);

-- JSON字段索引
CREATE INDEX idx_knowledge_points_content ON knowledge_points USING GIN(core_content);
CREATE INDEX idx_methods_scenarios ON methods USING GIN(application_scenarios);
CREATE INDEX idx_problems_source ON problems USING GIN(source_info);

-- 搜索向量索引
CREATE INDEX idx_knowledge_points_search ON knowledge_points USING GIN(search_vector);
CREATE INDEX idx_methods_search ON methods USING GIN(search_vector);
CREATE INDEX idx_problems_search ON problems USING GIN(search_vector);

-- 关系表索引
CREATE INDEX idx_knowledge_relations_source ON knowledge_relations(source_knowledge_id);
CREATE INDEX idx_knowledge_relations_target ON knowledge_relations(target_knowledge_id);
CREATE INDEX idx_knowledge_relations_type ON knowledge_relations(relation_type);

CREATE INDEX idx_problem_knowledge_tags_problem ON problem_knowledge_tags(problem_id);
CREATE INDEX idx_problem_knowledge_tags_knowledge ON problem_knowledge_tags(knowledge_id);
CREATE INDEX idx_problem_method_tags_problem ON problem_method_tags(problem_id);
CREATE INDEX idx_problem_method_tags_method ON problem_method_tags(method_id);

-- ===========================================
-- 8. 约束和触发器
-- ===========================================

-- 更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为需要的表添加更新时间触发器
CREATE TRIGGER update_knowledge_points_updated_at
    BEFORE UPDATE ON knowledge_points
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_methods_updated_at
    BEFORE UPDATE ON methods
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_problems_updated_at
    BEFORE UPDATE ON problems
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 检查约束
ALTER TABLE knowledge_points ADD CONSTRAINT chk_knowledge_code_format
    CHECK (code ~ '^K\d+-\d+-\d+-\d+$');

ALTER TABLE methods ADD CONSTRAINT chk_method_code_format
    CHECK (code ~ '^M\d+-\d+-\d+-\d+$');

ALTER TABLE problems ADD CONSTRAINT chk_problem_code_format
    CHECK (code ~ '^T\d+-\d+-\d+-[A-Z]\d+$');

-- ===========================================
-- 9. 初始数据插入
-- ===========================================

-- 插入教材信息示例
INSERT INTO textbooks (name, volume_type, volume_number, description) VALUES
('高中数学必修第一册', '必修', 1, '人教版高中数学必修第一册'),
('高中数学必修第二册', '必修', 2, '人教版高中数学必修第二册'),
('高中数学选择性必修第一册', '选择性必修', 1, '人教版高中数学选择性必修第一册'),
('高中数学选择性必修第二册', '选择性必修', 2, '人教版高中数学选择性必修第二册'),
('高中数学选择性必修第三册', '选择性必修', 3, '人教版高中数学选择性必修第三册');

-- ===========================================
-- 10. 视图创建
-- ===========================================

-- 知识点详细信息视图
CREATE VIEW knowledge_detail_view AS
SELECT
    kp.id,
    kp.code,
    kp.name,
    kp.level,
    kp.type,
    kp.importance,
    s.section_name,
    c.chapter_name,
    t.name as textbook_name,
    kp.core_content,
    kp.principle_explanation,
    kp.examination_methods,
    kp.textbook_location,
    kp.created_at
FROM knowledge_points kp
LEFT JOIN sections s ON kp.section_id = s.id
LEFT JOIN chapters c ON s.chapter_id = c.id
LEFT JOIN textbooks t ON c.textbook_id = t.id;

-- 题目详细信息视图
CREATE VIEW problem_detail_view AS
SELECT
    p.id,
    p.code,
    p.problem_type,
    p.title,
    p.stem,
    p.full_content,
    p.difficulty_info,
    s.section_name,
    c.chapter_name,
    t.name as textbook_name,
    p.textbook_location,
    p.subquestion_count,
    p.created_at
FROM problems p
LEFT JOIN knowledge_points kp ON p.section_id = kp.section_id
LEFT JOIN sections s ON kp.section_id = s.id
LEFT JOIN chapters c ON s.chapter_id = c.id
LEFT JOIN textbooks t ON c.textbook_id = t.id;

-- 方法详细信息视图
CREATE VIEW method_detail_view AS
SELECT
    m.id,
    m.code,
    m.name,
    m.type,
    m.difficulty_level,
    m.mathematical_thoughts,
    m.problem_solving_strategy,
    s.section_name,
    c.chapter_name,
    t.name as textbook_name,
    m.textbook_location,
    m.created_at
FROM methods m
LEFT JOIN sections s ON m.section_id = s.id
LEFT JOIN chapters c ON s.chapter_id = c.id
LEFT JOIN textbooks t ON c.textbook_id = t.id;

COMMENT ON DATABASE math_knowledge_graph IS '高中数学知识图谱数据库';