lingyue-zhibao/backend/sql/template_tables.sql

-- =====================================================
-- 报告模板系统表结构 v2.0
-- 「示例文档驱动」的模板生成系统
-- PostgreSQL 15+
-- 
-- 表列表：
--   1. templates - 报告模板
--   2. source_files - 来源文件定义
--   3. variables - 模板变量
--   4. generations - 生成任务
-- 
-- 创建时间: 2026-01-23
-- =====================================================

-- ============================================
-- 1. 报告模板表（templates）
-- ============================================
-- 注意: 如果 supplement_tables.sql 中已定义旧版 templates 表，
-- 需要先删除或重命名
DROP TABLE IF EXISTS templates CASCADE;

CREATE TABLE templates (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    base_document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE RESTRICT,
    status VARCHAR(32) DEFAULT 'draft',
    config JSONB DEFAULT '{}',
    is_public BOOLEAN DEFAULT FALSE,
    use_count INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    update_by VARCHAR(36),
    update_by_name VARCHAR(100)
);

CREATE INDEX idx_templates_user_id ON templates(user_id);
CREATE INDEX idx_templates_status ON templates(status);
CREATE INDEX idx_templates_is_public ON templates(is_public);
CREATE INDEX idx_templates_base_document ON templates(base_document_id);

COMMENT ON TABLE templates IS '报告模板';
COMMENT ON COLUMN templates.base_document_id IS '示例报告文档ID，关联 documents 表';
COMMENT ON COLUMN templates.status IS 'draft-草稿, published-已发布, archived-已归档';
COMMENT ON COLUMN templates.config IS '模板配置，如默认AI模型等';
COMMENT ON COLUMN templates.is_public IS '是否公开给其他用户使用';
COMMENT ON COLUMN templates.use_count IS '被使用生成报告的次数';

-- ============================================
-- 2. 来源文件定义表（source_files）
-- ============================================
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 '["pdf", "docx"]',
    required BOOLEAN DEFAULT TRUE,
    example_document_id VARCHAR(36) REFERENCES documents(id) ON DELETE SET NULL,
    display_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT uk_source_files_alias UNIQUE (template_id, alias)
);

CREATE INDEX idx_source_files_template ON source_files(template_id);

COMMENT ON TABLE source_files IS '来源文件定义';
COMMENT ON COLUMN source_files.alias IS '用户自定义的别名，用于引用，如"可研批复"';
COMMENT ON COLUMN source_files.file_types IS '允许上传的文件类型列表';
COMMENT ON COLUMN source_files.required IS '是否为必须提供的文件';
COMMENT ON COLUMN source_files.example_document_id IS '创建模板时使用的示例文件，用于预览';

-- ============================================
-- 3. 模板变量表（variables）
-- ============================================
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) NOT NULL,
    variable_group VARCHAR(100),
    
    -- 在示例报告中的位置
    location JSONB NOT NULL,
    
    -- 示例值
    example_value TEXT,
    value_type VARCHAR(32) DEFAULT 'text',
    
    -- 数据来源
    source_file_alias VARCHAR(100),
    source_type VARCHAR(32) NOT NULL,
    source_config JSONB,
    
    -- 提取方式
    extract_type VARCHAR(32),
    extract_config JSONB,
    
    display_order INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT uk_variables_name UNIQUE (template_id, name)
);

CREATE INDEX idx_variables_template ON variables(template_id);
CREATE INDEX idx_variables_source_alias ON variables(source_file_alias);
CREATE INDEX idx_variables_source_type ON variables(source_type);

COMMENT ON TABLE variables IS '模板变量';
COMMENT ON COLUMN variables.name IS '变量名，模板内唯一，用于程序引用';
COMMENT ON COLUMN variables.display_name IS '显示名称，用于用户界面';
COMMENT ON COLUMN variables.location IS '变量在文档中的位置，包含 element_id、偏移量等';
COMMENT ON COLUMN variables.value_type IS 'text-文本, date-日期, number-数字, table-表格';
COMMENT ON COLUMN variables.source_type IS 'document-从来源文件提取, manual-手动输入, reference-引用其他变量, fixed-固定值';
COMMENT ON COLUMN variables.extract_type IS 'direct-直接提取, ai_extract-AI字段提取, ai_summarize-AI总结';

-- ============================================
-- 4. 生成任务表（generations）
-- ============================================
CREATE TABLE IF NOT EXISTS generations (
    id VARCHAR(36) PRIMARY KEY,
    template_id VARCHAR(36) NOT NULL REFERENCES templates(id) ON DELETE RESTRICT,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    name VARCHAR(255),
    
    -- 来源文件映射：别名 → 文档ID
    source_file_map JSONB NOT NULL,
    
    -- 变量提取结果
    variable_values JSONB,
    
    -- 生成的文档
    output_document_id VARCHAR(36) REFERENCES documents(id) ON DELETE SET NULL,
    output_file_path VARCHAR(500),
    
    status VARCHAR(32) DEFAULT 'pending',
    error_message TEXT,
    progress INT DEFAULT 0,
    
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE INDEX idx_generations_template ON generations(template_id);
CREATE INDEX idx_generations_user ON generations(user_id);
CREATE INDEX idx_generations_status ON generations(status);
CREATE INDEX idx_generations_create_time ON generations(create_time DESC);

COMMENT ON TABLE generations IS '报告生成任务';
COMMENT ON COLUMN generations.source_file_map IS '来源文件映射，如 {"可研批复": "doc_123"}';
COMMENT ON COLUMN generations.variable_values IS '变量提取结果，包含值、置信度、状态等';
COMMENT ON COLUMN generations.status IS 'pending-待执行, extracting-提取中, review-待确认, completed-已完成, error-错误';
COMMENT ON COLUMN generations.progress IS '进度百分比 0-100';

-- ============================================
-- 5. 更新时间触发器
-- ============================================
-- 确保 update_update_time_column 函数存在（在 init.sql 中定义）
-- 如果不存在，创建一个
CREATE OR REPLACE FUNCTION update_update_time_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_templates_update_time ON templates;
CREATE TRIGGER trigger_templates_update_time
    BEFORE UPDATE ON templates
    FOR EACH ROW
    EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS trigger_variables_update_time ON variables;
CREATE TRIGGER trigger_variables_update_time
    BEFORE UPDATE ON variables
    FOR EACH ROW
    EXECUTE FUNCTION update_update_time_column();

-- ============================================
-- 6. 旧版 extract 表（v1.x 兼容）
-- ============================================
-- 以下表将在未来版本中删除，仅保留兼容性
-- extract_projects, extract_source_documents, extract_rules, 
-- extract_results, extract_rule_templates

-- 如需要删除旧表，取消以下注释：
-- DROP TABLE IF EXISTS extract_rule_templates CASCADE;
-- DROP TABLE IF EXISTS extract_results CASCADE;
-- DROP TABLE IF EXISTS extract_rules CASCADE;
-- DROP TABLE IF EXISTS extract_source_documents CASCADE;
-- DROP TABLE IF EXISTS extract_projects CASCADE;