lingyue-zhibao/database/migrations/V2026_01_21__add_document_blocks_and_entities.sql

-- 文档块表：存储文档的结构化内容（参考飞书Block设计）
-- 核心设计：块内容由 elements 数组（TextElement）组成，实体作为元素嵌入
CREATE TABLE IF NOT EXISTS document_blocks (
    id VARCHAR(64) PRIMARY KEY,
    document_id VARCHAR(64) NOT NULL,
    parent_id VARCHAR(64),
    children JSONB,
    block_index INTEGER NOT NULL,
    block_type VARCHAR(32) NOT NULL,
    elements JSONB,
    style JSONB,
    metadata JSONB,
    create_by VARCHAR(64),
    create_by_name VARCHAR(128),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64),
    update_by_name VARCHAR(128),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX IF NOT EXISTS idx_document_blocks_document_id ON document_blocks(document_id);
CREATE INDEX IF NOT EXISTS idx_document_blocks_parent_id ON document_blocks(parent_id);
CREATE INDEX IF NOT EXISTS idx_document_blocks_block_type ON document_blocks(block_type);
-- 全文搜索索引（对elements中的文本内容）
CREATE INDEX IF NOT EXISTS idx_document_blocks_elements_gin ON document_blocks USING GIN (elements jsonb_path_ops);

-- 文档实体标注表：存储文档中的实体/要素标记
CREATE TABLE IF NOT EXISTS document_entities (
    id VARCHAR(64) PRIMARY KEY,
    document_id VARCHAR(64) NOT NULL,
    block_id VARCHAR(64),
    name VARCHAR(512) NOT NULL,
    entity_type VARCHAR(32) NOT NULL,
    value TEXT,
    block_char_start INTEGER,
    block_char_end INTEGER,
    global_char_start INTEGER,
    global_char_end INTEGER,
    anchor_before VARCHAR(100),
    anchor_after VARCHAR(100),
    source VARCHAR(16) DEFAULT 'auto',
    confidence DECIMAL(5,4),
    confirmed BOOLEAN DEFAULT FALSE,
    graph_node_id VARCHAR(64),
    metadata JSONB,
    create_by VARCHAR(64),
    create_by_name VARCHAR(128),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64),
    update_by_name VARCHAR(128),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX IF NOT EXISTS idx_document_entities_document_id ON document_entities(document_id);
CREATE INDEX IF NOT EXISTS idx_document_entities_block_id ON document_entities(block_id);
CREATE INDEX IF NOT EXISTS idx_document_entities_type ON document_entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_document_entities_name ON document_entities(name);
CREATE INDEX IF NOT EXISTS idx_document_entities_global_char ON document_entities(document_id, global_char_start, global_char_end);
CREATE INDEX IF NOT EXISTS idx_document_entities_graph_node ON document_entities(graph_node_id);

-- 注释
COMMENT ON TABLE document_blocks IS '文档块 - 存储文档的结构化内容块';
COMMENT ON TABLE document_entities IS '文档实体 - 存储文档中标记的实体/要素';

COMMENT ON COLUMN document_blocks.block_type IS '块类型: heading1/heading2/heading3/paragraph/table/list/image/quote';
COMMENT ON COLUMN document_entities.entity_type IS '实体类型: PERSON/ORG/LOC/DATE/NUMBER/MONEY/CONCEPT/DATA/DEVICE/TERM';
COMMENT ON COLUMN document_entities.source IS '来源: auto=自动识别, manual=手动标注';