lingyue-zhibao/database/init.sql

-- =====================================================
-- 灵越智报 2.0 数据库初始化脚本
-- PostgreSQL 15+ / pgvector
-- 设计参考: a_docs/数据库设计文档.md
-- =====================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS vector;

-- ==================== 用户表 ====================
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    avatar_url VARCHAR(500),
    role VARCHAR(20) DEFAULT 'user',
    preferences JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- ==================== 文档表 ====================
CREATE TABLE IF NOT EXISTS documents (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    file_name VARCHAR(500) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'uploaded',
    parsed_text TEXT,
    page_count INT,
    word_count INT,
    entity_count INT DEFAULT 0,
    relation_count INT DEFAULT 0,
    rule_count INT DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_documents_user ON documents(user_id);
CREATE INDEX IF NOT EXISTS idx_documents_type ON documents(file_type);
CREATE INDEX IF NOT EXISTS idx_documents_status ON documents(status);

-- ==================== 文档章节结构表 ====================
CREATE TABLE IF NOT EXISTS document_sections (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    parent_id VARCHAR(36) REFERENCES document_sections(id) ON DELETE CASCADE,
    section_index INT NOT NULL,
    level INT NOT NULL DEFAULT 1,
    title VARCHAR(500),
    content TEXT,
    start_page INT,
    end_page INT,
    start_char INT,
    end_char INT,
    section_type VARCHAR(32) DEFAULT 'heading',
    table_data JSONB,
    image_path VARCHAR(500),
    image_caption VARCHAR(500),
    metadata JSONB DEFAULT '{}',
    sort_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_doc_sections_document ON document_sections(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_sections_parent ON document_sections(parent_id);
CREATE INDEX IF NOT EXISTS idx_doc_sections_level ON document_sections(level);
CREATE INDEX IF NOT EXISTS idx_doc_sections_type ON document_sections(section_type);

-- ==================== 文档分块表 ====================
CREATE TABLE IF NOT EXISTS document_chunks (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    section_id VARCHAR(36) REFERENCES document_sections(id) ON DELETE SET NULL,
    chunk_index INT NOT NULL,
    content TEXT NOT NULL,
    start_char INT,
    end_char INT,
    page_number INT,
    token_count INT,
    metadata JSONB DEFAULT '{}',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_doc_chunks_document ON document_chunks(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_chunks_section ON document_chunks(section_id);

-- ==================== 解析任务表（parse-service 依赖） ====================
CREATE TABLE IF NOT EXISTS parse_tasks (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending',
    progress INT DEFAULT 0,
    current_step VARCHAR(32),
    error_message TEXT,
    options JSONB DEFAULT '{}',
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    parse_status VARCHAR(20) DEFAULT 'pending',
    parse_progress INT DEFAULT 0,
    rag_status VARCHAR(20) DEFAULT 'pending',
    rag_progress INT DEFAULT 0,
    structured_status VARCHAR(20) DEFAULT 'pending',
    structured_progress INT DEFAULT 0,
    structured_element_count INT,
    structured_image_count INT,
    structured_table_count INT,
    ner_status VARCHAR(20) DEFAULT 'pending',
    ner_progress INT DEFAULT 0,
    ner_task_id VARCHAR(100),
    ner_entity_count INT,
    ner_relation_count INT,
    ner_message TEXT,
    graph_status VARCHAR(20) DEFAULT 'pending',
    graph_progress INT DEFAULT 0,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_parse_tasks_document ON parse_tasks(document_id);

-- ==================== 文档结构化元素表（结构化解析 / 前端展示依赖） ====================
CREATE TABLE IF NOT EXISTS document_elements (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    element_index INT,
    element_type VARCHAR(50),
    content TEXT,
    style JSONB DEFAULT '{}',
    runs JSONB DEFAULT '[]',
    image_url VARCHAR(500),
    image_path VARCHAR(500),
    image_alt VARCHAR(500),
    image_width INT,
    image_height INT,
    image_format VARCHAR(20),
    table_index INT,
    table_data JSONB,
    table_row_count INT,
    table_col_count INT,
    table_text TEXT,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_document_elements_document ON document_elements(document_id);

-- ==================== 向量嵌入表 ====================
CREATE TABLE IF NOT EXISTS embeddings (
    id VARCHAR(36) PRIMARY KEY,
    chunk_id VARCHAR(36) NOT NULL REFERENCES document_chunks(id) ON DELETE CASCADE,
    embedding vector(1536),
    model_name VARCHAR(100) DEFAULT 'text-embedding-ada-002',
    model_version VARCHAR(50),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_embeddings_chunk ON embeddings(chunk_id);
CREATE INDEX IF NOT EXISTS idx_embeddings_vector ON embeddings USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- ==================== 实体类型表 ====================
CREATE TABLE IF NOT EXISTS entity_types (
    id VARCHAR(36) PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100) NOT NULL,
    category VARCHAR(32),
    color VARCHAR(20),
    icon VARCHAR(50),
    patterns JSONB DEFAULT '[]',
    examples JSONB DEFAULT '[]',
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_entity_types_code ON entity_types(type_code);

-- ==================== 实体节点表（方案 B：视图驱动）====================
CREATE TABLE IF NOT EXISTS entity_nodes (
    id VARCHAR(36) PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    name VARCHAR(500) NOT NULL,
    normalized_text VARCHAR(500),
    embedding vector(1536),
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(entity_type, normalized_text)
);
CREATE INDEX IF NOT EXISTS idx_entity_nodes_type ON entity_nodes(entity_type);
CREATE INDEX IF NOT EXISTS idx_entity_nodes_name ON entity_nodes(name);
CREATE INDEX IF NOT EXISTS idx_entity_nodes_normalized ON entity_nodes(normalized_text);

-- ==================== 实体属性表 ====================
CREATE TABLE IF NOT EXISTS entity_attributes (
    id VARCHAR(36) PRIMARY KEY,
    entity_id VARCHAR(36) NOT NULL REFERENCES entity_nodes(id) ON DELETE CASCADE,
    attribute_key VARCHAR(100) NOT NULL,
    attribute_value TEXT,
    value_type VARCHAR(32) DEFAULT 'STRING',
    sort_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(entity_id, attribute_key)
);
CREATE INDEX IF NOT EXISTS idx_entity_attributes_entity ON entity_attributes(entity_id);
CREATE INDEX IF NOT EXISTS idx_entity_attributes_key ON entity_attributes(attribute_key);

-- ==================== 实体视图 v_entities（读模型）====================
CREATE OR REPLACE VIEW v_entities AS
SELECT
    n.id,
    n.entity_type,
    n.name AS entity_text,
    n.normalized_text,
    n.embedding,
    n.del_flag,
    n.create_by,
    n.create_time,
    n.update_by,
    n.update_time,
    (SELECT attribute_value FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'aliases' LIMIT 1) AS aliases,
    (SELECT attribute_value FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'metadata' LIMIT 1) AS metadata,
    COALESCE((SELECT (attribute_value::INT) FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'occurrence_count' LIMIT 1), 1) AS occurrence_count,
    COALESCE((SELECT (attribute_value::FLOAT) FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'confidence' LIMIT 1), 1.0) AS confidence,
    COALESCE((SELECT (attribute_value::BOOLEAN) FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'is_confirmed' LIMIT 1), FALSE) AS is_confirmed,
    COALESCE((SELECT (attribute_value::BOOLEAN) FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'is_merged' LIMIT 1), FALSE) AS is_merged,
    (SELECT attribute_value FROM entity_attributes WHERE entity_id = n.id AND attribute_key = 'merged_to_id' LIMIT 1) AS merged_to_id
FROM entity_nodes n
WHERE n.del_flag = FALSE;

-- ==================== 文档–实体关联表 ====================
CREATE TABLE IF NOT EXISTS document_entities (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    entity_id VARCHAR(36) NOT NULL REFERENCES entity_nodes(id) ON DELETE CASCADE,
    business_label VARCHAR(100),
    display_text VARCHAR(500),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(document_id, entity_id, business_label)
);
CREATE INDEX IF NOT EXISTS idx_document_entities_document ON document_entities(document_id);
CREATE INDEX IF NOT EXISTS idx_document_entities_entity ON document_entities(entity_id);
CREATE INDEX IF NOT EXISTS idx_document_entities_label ON document_entities(business_label);

-- ==================== 关系类型表 ====================
CREATE TABLE IF NOT EXISTS relation_types (
    id VARCHAR(36) PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100) NOT NULL,
    source_entity_types JSONB DEFAULT '[]',
    target_entity_types JSONB DEFAULT '[]',
    is_symmetric BOOLEAN DEFAULT FALSE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_relation_types_code ON relation_types(type_code);

-- ==================== 实体关系表 ====================
CREATE TABLE IF NOT EXISTS entity_relations (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    source_entity_id VARCHAR(36) NOT NULL REFERENCES entity_nodes(id) ON DELETE CASCADE,
    target_entity_id VARCHAR(36) NOT NULL REFERENCES entity_nodes(id) ON DELETE CASCADE,
    relation_type VARCHAR(50) NOT NULL,
    relation_text VARCHAR(500),
    confidence FLOAT DEFAULT 1.0,
    extraction_method VARCHAR(32) DEFAULT 'auto',
    evidence_text TEXT,
    is_confirmed BOOLEAN DEFAULT FALSE,
    metadata JSONB DEFAULT '{}',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_relations_document ON entity_relations(document_id);
CREATE INDEX IF NOT EXISTS idx_relations_source ON entity_relations(source_entity_id);
CREATE INDEX IF NOT EXISTS idx_relations_target ON entity_relations(target_entity_id);
CREATE INDEX IF NOT EXISTS idx_relations_type ON entity_relations(relation_type);

-- ==================== 规则表 ====================
CREATE TABLE IF NOT EXISTS rules (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) REFERENCES documents(id) ON DELETE SET NULL,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    rule_type VARCHAR(32) NOT NULL,
    source VARCHAR(32) DEFAULT 'auto',
    priority INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'draft',
    embedding vector(1536),
    is_global BOOLEAN DEFAULT FALSE,
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_rules_document ON rules(document_id);
CREATE INDEX IF NOT EXISTS idx_rules_user ON rules(user_id);
CREATE INDEX IF NOT EXISTS idx_rules_type ON rules(rule_type);
CREATE INDEX IF NOT EXISTS idx_rules_status ON rules(status);
CREATE INDEX IF NOT EXISTS idx_rules_category ON rules(category);

-- ==================== 规则条件表 ====================
CREATE TABLE IF NOT EXISTS rule_conditions (
    id VARCHAR(36) PRIMARY KEY,
    rule_id VARCHAR(36) NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
    condition_index INT NOT NULL,
    condition_type VARCHAR(32) NOT NULL,
    config JSONB NOT NULL,
    logic_operator VARCHAR(10) DEFAULT 'AND',
    sort_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_rule_conditions_rule ON rule_conditions(rule_id);

-- ==================== 规则动作表 ====================
CREATE TABLE IF NOT EXISTS rule_actions (
    id VARCHAR(36) PRIMARY KEY,
    rule_id VARCHAR(36) NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
    action_index INT NOT NULL,
    action_type VARCHAR(32) NOT NULL,
    config JSONB NOT NULL,
    target_field VARCHAR(100),
    sort_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_rule_actions_rule ON rule_actions(rule_id);

-- ==================== 生成任务表 ====================
CREATE TABLE IF NOT EXISTS generations (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    document_id VARCHAR(36) REFERENCES documents(id) ON DELETE SET NULL,
    template_id VARCHAR(36),
    name VARCHAR(200),
    applied_rules JSONB DEFAULT '[]',
    source_file_map JSONB DEFAULT '{}',
    extracted_data JSONB DEFAULT '{}',
    confirmed_data JSONB DEFAULT '{}',
    output_file_path VARCHAR(500),
    output_file_name VARCHAR(255),
    output_document_id VARCHAR(36),
    progress INTEGER,
    status VARCHAR(20) DEFAULT 'pending',
    error_message TEXT,
    started_at TIMESTAMP,
    reviewed_at TIMESTAMP,
    completed_at TIMESTAMP,
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_generations_user ON generations(user_id);
CREATE INDEX IF NOT EXISTS idx_generations_document ON generations(document_id);
CREATE INDEX IF NOT EXISTS idx_generations_status ON generations(status);

-- ==================== 生成输出表 ====================
CREATE TABLE IF NOT EXISTS generation_outputs (
    id VARCHAR(36) PRIMARY KEY,
    generation_id VARCHAR(36) NOT NULL REFERENCES generations(id) ON DELETE CASCADE,
    version INT NOT NULL DEFAULT 1,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(20) DEFAULT 'docx',
    share_token VARCHAR(100),
    share_expires_at TIMESTAMP,
    download_count INT DEFAULT 0,
    is_final BOOLEAN DEFAULT FALSE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_gen_outputs_generation ON generation_outputs(generation_id);
CREATE INDEX IF NOT EXISTS idx_gen_outputs_share ON generation_outputs(share_token);

-- ==================== 模板/变量/来源文件（兼容现有模板流程） ====================
CREATE TABLE IF NOT EXISTS templates (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    name VARCHAR(200),
    description TEXT,
    base_document_id VARCHAR(36),
    status VARCHAR(20) DEFAULT 'draft',
    config JSONB DEFAULT '{}',
    is_public BOOLEAN DEFAULT FALSE,
    use_count INT DEFAULT 0,
    rating DOUBLE PRECISION,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_templates_user ON templates(user_id);

CREATE TABLE IF NOT EXISTS variables (
    id VARCHAR(36) PRIMARY KEY,
    template_id VARCHAR(36) NOT NULL REFERENCES templates(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    display_name VARCHAR(200),
    variable_group VARCHAR(100),
    category VARCHAR(50),
    location JSONB,
    example_value TEXT,
    value_type VARCHAR(20),
    source_file_alias VARCHAR(100),
    source_type VARCHAR(30),
    source_config JSONB DEFAULT '{}',
    extract_type VARCHAR(30),
    extract_config JSONB DEFAULT '{}',
    display_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_variables_template ON variables(template_id);

CREATE TABLE IF NOT EXISTS source_files (
    id VARCHAR(36) PRIMARY KEY,
    template_id VARCHAR(36) NOT NULL REFERENCES templates(id) ON DELETE CASCADE,
    alias VARCHAR(100) NOT NULL,
    description TEXT,
    file_types JSONB DEFAULT '[]',
    required BOOLEAN DEFAULT TRUE,
    example_document_id VARCHAR(36),
    display_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_source_files_template ON source_files(template_id);

-- ==================== 标准实体表 ====================
CREATE TABLE IF NOT EXISTS standard_entities (
    id VARCHAR(36) PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    name VARCHAR(500) NOT NULL,
    aliases JSONB DEFAULT '[]',
    attributes JSONB DEFAULT '{}',
    embedding vector(1536),
    occurrence_count INT DEFAULT 1,
    document_count INT DEFAULT 1,
    is_verified BOOLEAN DEFAULT FALSE,
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_std_entities_type ON standard_entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_std_entities_name ON standard_entities(name);
CREATE INDEX IF NOT EXISTS idx_std_entities_vector ON standard_entities USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- ==================== 标准关系表 ====================
CREATE TABLE IF NOT EXISTS standard_relations (
    id VARCHAR(36) PRIMARY KEY,
    source_entity_id VARCHAR(36) NOT NULL REFERENCES standard_entities(id) ON DELETE CASCADE,
    target_entity_id VARCHAR(36) NOT NULL REFERENCES standard_entities(id) ON DELETE CASCADE,
    relation_type VARCHAR(50) NOT NULL,
    attributes JSONB DEFAULT '{}',
    occurrence_count INT DEFAULT 1,
    is_verified BOOLEAN DEFAULT FALSE,
    del_flag BOOLEAN DEFAULT FALSE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_std_relations_source ON standard_relations(source_entity_id);
CREATE INDEX IF NOT EXISTS idx_std_relations_target ON standard_relations(target_entity_id);
CREATE INDEX IF NOT EXISTS idx_std_relations_type ON standard_relations(relation_type);

-- ==================== 实体合并记录表 ====================
CREATE TABLE IF NOT EXISTS entity_merge_records (
    id VARCHAR(36) PRIMARY KEY,
    source_entity_id VARCHAR(36) NOT NULL REFERENCES entity_nodes(id) ON DELETE CASCADE,
    target_entity_id VARCHAR(36) NOT NULL REFERENCES standard_entities(id) ON DELETE CASCADE,
    similarity_score FLOAT,
    merge_type VARCHAR(32) DEFAULT 'auto',
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_merge_records_source ON entity_merge_records(source_entity_id);
CREATE INDEX IF NOT EXISTS idx_merge_records_target ON entity_merge_records(target_entity_id);

-- ==================== 规则-标准实体/关系关联（实体图与拖拽搭建规则） ====================
CREATE TABLE IF NOT EXISTS rule_standard_entities (
    id VARCHAR(36) PRIMARY KEY,
    rule_id VARCHAR(36) NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
    standard_entity_id VARCHAR(36) NOT NULL REFERENCES standard_entities(id) ON DELETE CASCADE,
    role VARCHAR(32) DEFAULT 'participant',
    display_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(rule_id, standard_entity_id)
);
CREATE INDEX IF NOT EXISTS idx_rule_std_entities_rule ON rule_standard_entities(rule_id);
CREATE INDEX IF NOT EXISTS idx_rule_std_entities_entity ON rule_standard_entities(standard_entity_id);

CREATE TABLE IF NOT EXISTS rule_standard_relations (
    id VARCHAR(36) PRIMARY KEY,
    rule_id VARCHAR(36) NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
    standard_relation_id VARCHAR(36) NOT NULL REFERENCES standard_relations(id) ON DELETE CASCADE,
    display_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(rule_id, standard_relation_id)
);
CREATE INDEX IF NOT EXISTS idx_rule_std_relations_rule ON rule_standard_relations(rule_id);
CREATE INDEX IF NOT EXISTS idx_rule_std_relations_relation ON rule_standard_relations(standard_relation_id);

-- ==================== 前端业务：公司 / 项目 / 报告 / 报告附件 ====================
CREATE TABLE IF NOT EXISTS companies (
    id VARCHAR(36) PRIMARY KEY,
    parent_id VARCHAR(36) REFERENCES companies(id) ON DELETE SET NULL,
    name VARCHAR(500) NOT NULL,
    short_name VARCHAR(100),
    unified_social_credit_code VARCHAR(50),
    description TEXT,
    metadata JSONB DEFAULT '{}',
    del_flag BOOLEAN DEFAULT FALSE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_companies_parent ON companies(parent_id);
CREATE INDEX IF NOT EXISTS idx_companies_name ON companies(name);

CREATE TABLE IF NOT EXISTS projects (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) REFERENCES companies(id) ON DELETE SET NULL,
    project_code VARCHAR(100) NOT NULL,
    project_name VARCHAR(500),
    review_object VARCHAR(500),
    review_object_alias VARCHAR(200),
    review_object_summary TEXT,
    application_category VARCHAR(100),
    request_level VARCHAR(50),
    result_level VARCHAR(50),
    result_score DECIMAL(10,2),
    review_period VARCHAR(200),
    work_start_at DATE,
    work_end_at DATE,
    review_object_certificate2_get_at DATE,
    apply_at DATE,
    reviewer_company_id VARCHAR(36) REFERENCES companies(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}',
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_projects_company ON projects(company_id);
CREATE INDEX IF NOT EXISTS idx_projects_code ON projects(project_code);

CREATE TABLE IF NOT EXISTS reports (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    project_id VARCHAR(36) REFERENCES projects(id) ON DELETE SET NULL,
    title VARCHAR(500) NOT NULL,
    report_type VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    content_template TEXT,
    content_rendered TEXT,
    auto_saved_at TIMESTAMP,
    report_score DECIMAL(10,2),
    report_level VARCHAR(50),
    report_metrics JSONB DEFAULT '{}',
    generation_id VARCHAR(36) REFERENCES generations(id) ON DELETE SET NULL,
    source_document_id VARCHAR(36) REFERENCES documents(id) ON DELETE SET NULL,
    archived_at TIMESTAMP,
    published_at TIMESTAMP,
    del_flag BOOLEAN DEFAULT FALSE,
    create_by VARCHAR(36),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_reports_user ON reports(user_id);
CREATE INDEX IF NOT EXISTS idx_reports_project ON reports(project_id);
CREATE INDEX IF NOT EXISTS idx_reports_status ON reports(status);
CREATE INDEX IF NOT EXISTS idx_reports_created ON reports(create_time);

CREATE TABLE IF NOT EXISTS report_attachments (
    id VARCHAR(36) PRIMARY KEY,
    report_id VARCHAR(36) NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    display_name VARCHAR(255),
    file_name VARCHAR(500) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_type VARCHAR(50),
    file_size BIGINT,
    sort_order INT DEFAULT 0,
    saved_to_knowledge_base BOOLEAN DEFAULT FALSE,
    knowledge_base_item_id VARCHAR(36),
    uploaded_by VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_report_attachments_report ON report_attachments(report_id);

-- ==================== 报告占位符定义表 ====================
CREATE TABLE IF NOT EXISTS placeholder_definitions (
    id VARCHAR(36) PRIMARY KEY,
    report_type VARCHAR(100),
    placeholder_key VARCHAR(200) NOT NULL,
    placeholder_group VARCHAR(50),
    source_table VARCHAR(100) NOT NULL,
    source_column VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    example_value TEXT,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(placeholder_key)
);
CREATE INDEX IF NOT EXISTS idx_placeholder_definitions_group ON placeholder_definitions(placeholder_group);
CREATE INDEX IF NOT EXISTS idx_placeholder_definitions_report_type ON placeholder_definitions(report_type);

-- ==================== 预置数据 ====================
INSERT INTO entity_types (id, type_code, type_name, category, color, icon, description, sort_order) VALUES
('et-001', 'PERSON', '人名', 'basic', '#1890ff', 'user', '人物姓名，如：张经理、李总', 1),
('et-002', 'ORG', '机构', 'basic', '#faad14', 'bank', '组织机构，如：成都检测公司、环保局', 2),
('et-003', 'LOC', '地点', 'basic', '#52c41a', 'environment', '地理位置，如：成都市高新区', 3),
('et-004', 'DATE', '日期', 'basic', '#722ed1', 'calendar', '日期时间，如：2024年5月15日', 4),
('et-005', 'NUMBER', '数值', 'basic', '#13c2c2', 'number', '数值数量，如：50分贝、100万元', 5),
('et-006', 'DEVICE', '设备', 'domain', '#eb2f96', 'tool', '设备仪器，如：噪音检测设备、光谱仪', 10),
('et-007', 'PROJECT', '项目', 'domain', '#f5222d', 'project', '项目名称，如：环境监测项目', 11),
('et-008', 'TERM', '术语', 'domain', '#a0d911', 'book', '专业术语，如：COD、BOD、PM2.5', 12),
('et-009', 'STANDARD', '标准', 'domain', '#2f54eb', 'file-text', '标准规范，如：GB 3096-2008', 13),
('et-010', 'MATERIAL', '材料', 'domain', '#fa8c16', 'experiment', '材料物质，如：甲醛、苯', 14),
('et-011', 'METHOD', '方法', 'domain', '#1890ff', 'api', '检测方法，如：气相色谱法', 15)
ON CONFLICT (id) DO NOTHING;

INSERT INTO relation_types (id, type_code, type_name, source_entity_types, target_entity_types, is_symmetric, description) VALUES
('rt-001', 'LOCATED_IN', '位于', '["ORG", "PERSON", "PROJECT"]', '["LOC"]', false, '实体位于某地'),
('rt-002', 'WORKS_FOR', '任职于', '["PERSON"]', '["ORG"]', false, '人员任职于机构'),
('rt-003', 'BELONGS_TO', '属于', '["ORG", "PROJECT"]', '["ORG"]', false, '隶属关系'),
('rt-004', 'RESPONSIBLE_FOR', '负责', '["PERSON", "ORG"]', '["PROJECT", "LOC"]', false, '负责某事/某地'),
('rt-005', 'USES', '使用', '["ORG", "PERSON", "PROJECT"]', '["DEVICE", "METHOD"]', false, '使用设备/方法'),
('rt-006', 'DETECTS', '检测', '["ORG", "PERSON", "DEVICE"]', '["MATERIAL", "TERM"]', false, '检测某物质/指标'),
('rt-007', 'CONTAINS', '包含', '["PROJECT", "ORG"]', '["PROJECT", "TERM", "MATERIAL"]', false, '包含关系'),
('rt-008', 'COMPLIES_WITH', '符合', '["PROJECT", "ORG", "METHOD"]', '["STANDARD"]', false, '符合某标准'),
('rt-009', 'PRODUCES', '产生', '["ORG", "PROJECT", "DEVICE"]', '["NUMBER", "MATERIAL"]', false, '产生数据/物质'),
('rt-010', 'COOPERATES', '合作', '["ORG"]', '["ORG"]', true, '机构合作关系'),
('rt-011', 'OCCURS_AT', '发生于', '["PROJECT"]', '["DATE"]', false, '发生在某时间')
ON CONFLICT (id) DO NOTHING;