lingyue-zhibao/database/init_0211_full.sql

-- ============================================================
-- 灵越智报 2.0 数据库完整脚本
-- 基于 2026-02-11 会议讨论设计
-- 
-- 核心概念：
-- - 静态要素：模板中固定不变的文本
-- - 动态要素：需要通过规则计算填充的内容
-- - 占位符：动态要素在模板中的标识符
-- ============================================================

-- ============================================================
-- 1. 基础表
-- ============================================================

-- ------------------------------------------------------------
-- 1.1 users - 用户表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(200),
    phone VARCHAR(20),
    real_name VARCHAR(100),
    avatar_url VARCHAR(500),
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.status IS '状态: active/disabled';

-- ------------------------------------------------------------
-- 1.2 projects - 项目表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    code VARCHAR(100),
    description TEXT,
    status VARCHAR(50) DEFAULT 'active',
    owner_id BIGINT REFERENCES users(id),
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE projects IS '项目表';

-- ============================================================
-- 2. 模板相关表
-- ============================================================

-- ------------------------------------------------------------
-- 2.1 report_templates - 报告模板表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_templates (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    description TEXT,
    content_html TEXT,
    content_json JSONB,
    dynamic_element_count INT DEFAULT 0,
    table_element_count INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'active',
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE report_templates IS '报告模板表';
COMMENT ON COLUMN report_templates.dynamic_element_count IS '动态要素数量';
COMMENT ON COLUMN report_templates.table_element_count IS '表格型动态要素数量';

-- ------------------------------------------------------------
-- 2.2 template_dynamic_elements - 模板动态要素定义表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS template_dynamic_elements (
    id BIGSERIAL PRIMARY KEY,
    template_id BIGINT NOT NULL REFERENCES report_templates(id) ON DELETE CASCADE,
    element_key VARCHAR(100) NOT NULL,
    element_name VARCHAR(200) NOT NULL,
    element_type VARCHAR(50) NOT NULL,
    namespace VARCHAR(100),
    field_name VARCHAR(100),
    table_columns JSONB,
    required BOOLEAN DEFAULT false,
    default_value TEXT,
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(template_id, element_key)
);

COMMENT ON TABLE template_dynamic_elements IS '模板动态要素定义表';
COMMENT ON COLUMN template_dynamic_elements.element_key IS '占位符key，如 basicInfo.projectCode 或 +review_status';
COMMENT ON COLUMN template_dynamic_elements.element_type IS '类型: text/paragraph/table';
COMMENT ON COLUMN template_dynamic_elements.table_columns IS '表格列定义JSON数组';

-- ============================================================
-- 3. 报告相关表
-- ============================================================

-- ------------------------------------------------------------
-- 3.1 reports - 报告表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    report_type VARCHAR(100),
    template_id BIGINT REFERENCES report_templates(id),
    project_id BIGINT REFERENCES projects(id),
    content_html TEXT,
    status VARCHAR(50) DEFAULT 'draft',
    source_report_id BIGINT REFERENCES reports(id),
    copy_time TIMESTAMP,
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE reports IS '报告表';
COMMENT ON COLUMN reports.status IS '状态: draft/published/archived';
COMMENT ON COLUMN reports.source_report_id IS '源报告ID（复制来源）';

-- ------------------------------------------------------------
-- 3.2 element_values - 动态要素值表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS element_values (
    id BIGSERIAL PRIMARY KEY,
    report_id BIGINT NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    element_key VARCHAR(100) NOT NULL,
    value_text TEXT,
    value_json JSONB,
    is_filled BOOLEAN DEFAULT false,
    fill_source VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(report_id, element_key)
);

COMMENT ON TABLE element_values IS '动态要素值表';
COMMENT ON COLUMN element_values.element_key IS '占位符key';
COMMENT ON COLUMN element_values.value_text IS '文本值（text/paragraph类型）';
COMMENT ON COLUMN element_values.value_json IS 'JSON值（table类型）';
COMMENT ON COLUMN element_values.fill_source IS '填充来源: manual/rule/import';

-- ------------------------------------------------------------
-- 3.3 element_rules - 动态要素规则表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS element_rules (
    id BIGSERIAL PRIMARY KEY,
    report_id BIGINT NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    element_key VARCHAR(100) NOT NULL,
    rule_name VARCHAR(200),
    description TEXT,
    rule_type VARCHAR(50) NOT NULL,
    last_output_text TEXT,
    last_output_json JSONB,
    last_run_status VARCHAR(50),
    last_run_time TIMESTAMP,
    last_run_error TEXT,
    dsl_content TEXT,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(report_id, element_key)
);

COMMENT ON TABLE element_rules IS '动态要素规则表';
COMMENT ON COLUMN element_rules.rule_type IS '类型: extraction/generation/calculation';
COMMENT ON COLUMN element_rules.dsl_content IS 'DSL语法内容';

-- ------------------------------------------------------------
-- 3.4 rule_inputs - 规则输入表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rule_inputs (
    id BIGSERIAL PRIMARY KEY,
    rule_id BIGINT NOT NULL REFERENCES element_rules(id) ON DELETE CASCADE,
    input_key VARCHAR(50) NOT NULL,
    input_name VARCHAR(200) NOT NULL,
    input_type VARCHAR(50) NOT NULL,
    ref_attachment_id BIGINT,
    ref_entity_id BIGINT,
    ref_element_key VARCHAR(100),
    fixed_value TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE rule_inputs IS '规则输入表';
COMMENT ON COLUMN rule_inputs.input_type IS '类型: attachment_ref/entity_ref/element_ref/text/number';
COMMENT ON COLUMN rule_inputs.ref_element_key IS '引用其他动态要素的key';

-- ------------------------------------------------------------
-- 3.5 rule_action_config - 规则动作配置表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rule_action_config (
    id BIGSERIAL PRIMARY KEY,
    rule_id BIGINT NOT NULL REFERENCES element_rules(id) ON DELETE CASCADE UNIQUE,
    action_type VARCHAR(50) NOT NULL,
    config JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE rule_action_config IS '规则动作配置表';
COMMENT ON COLUMN rule_action_config.action_type IS '类型: extract_entity/extract_pattern/llm_generate/extract_table';

-- ============================================================
-- 4. 附件相关表
-- ============================================================

-- ------------------------------------------------------------
-- 4.1 report_attachments - 报告附件表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_attachments (
    id BIGSERIAL PRIMARY KEY,
    report_id BIGINT NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    display_name VARCHAR(200) NOT NULL,
    file_name VARCHAR(500) NOT NULL,
    file_path VARCHAR(1000) NOT NULL,
    file_type VARCHAR(50),
    file_size BIGINT,
    parse_status VARCHAR(50) DEFAULT 'pending',
    parsed_text TEXT,
    entity_count INT DEFAULT 0,
    parse_error TEXT,
    parsed_at TIMESTAMP,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE report_attachments IS '报告附件表';
COMMENT ON COLUMN report_attachments.parse_status IS '解析状态: pending/processing/completed/failed';

-- ------------------------------------------------------------
-- 4.2 attachment_entities - 附件实体表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attachment_entities (
    id BIGSERIAL PRIMARY KEY,
    attachment_id BIGINT NOT NULL REFERENCES report_attachments(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL,
    entity_text VARCHAR(500) NOT NULL,
    business_label VARCHAR(200),
    confidence DECIMAL(5,4) DEFAULT 1.0,
    occurrence_count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE attachment_entities IS '附件实体表';
COMMENT ON COLUMN attachment_entities.entity_type IS '类型: ORG/PERSON/DATE/NUMBER/LOCATION';
COMMENT ON COLUMN attachment_entities.business_label IS '业务标签';

-- ============================================================
-- 5. 其他表
-- ============================================================

-- ------------------------------------------------------------
-- 5.1 report_copy_records - 报告复制记录表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_copy_records (
    id BIGSERIAL PRIMARY KEY,
    source_report_id BIGINT NOT NULL REFERENCES reports(id),
    target_report_id BIGINT NOT NULL REFERENCES reports(id),
    copied_by BIGINT REFERENCES users(id),
    copy_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    elements_copied INT DEFAULT 0,
    rules_copied INT DEFAULT 0,
    notes TEXT
);

COMMENT ON TABLE report_copy_records IS '报告复制记录表';

-- ============================================================
-- 6. 视图
-- ============================================================

-- ------------------------------------------------------------
-- 6.1 v_report_dynamic_elements - 报告动态要素完整视图
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW v_report_dynamic_elements AS
SELECT 
    r.id AS report_id,
    r.title AS report_title,
    te.element_key,
    te.element_name,
    te.element_type,
    te.namespace,
    te.field_name,
    te.table_columns,
    te.required,
    ev.value_text,
    ev.value_json,
    ev.is_filled,
    ev.fill_source,
    er.id AS rule_id,
    er.rule_type,
    er.last_run_status,
    er.last_run_time,
    CASE WHEN er.id IS NOT NULL THEN true ELSE false END AS has_rule
FROM reports r
JOIN report_templates rt ON rt.id = r.template_id
JOIN template_dynamic_elements te ON te.template_id = rt.id
LEFT JOIN element_values ev ON ev.report_id = r.id AND ev.element_key = te.element_key
LEFT JOIN element_rules er ON er.report_id = r.id AND er.element_key = te.element_key;

COMMENT ON VIEW v_report_dynamic_elements IS '报告动态要素完整视图';

-- ------------------------------------------------------------
-- 6.2 v_rule_full_config - 规则完整配置视图
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW v_rule_full_config AS
SELECT 
    er.id AS rule_id,
    er.report_id,
    er.element_key,
    er.rule_name,
    er.rule_type,
    er.last_output_text,
    er.last_output_json,
    er.last_run_status,
    er.dsl_content,
    er.status,
    ac.action_type,
    ac.config AS action_config,
    (
        SELECT json_agg(json_build_object(
            'inputKey', ri.input_key,
            'inputName', ri.input_name,
            'inputType', ri.input_type,
            'refAttachmentId', ri.ref_attachment_id,
            'refEntityId', ri.ref_entity_id,
            'refElementKey', ri.ref_element_key,
            'fixedValue', ri.fixed_value
        ) ORDER BY ri.sort_order)
        FROM rule_inputs ri
        WHERE ri.rule_id = er.id
    ) AS inputs
FROM element_rules er
LEFT JOIN rule_action_config ac ON ac.rule_id = er.id;

COMMENT ON VIEW v_rule_full_config IS '规则完整配置视图';

-- ------------------------------------------------------------
-- 6.3 v_report_summary - 报告摘要视图
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW v_report_summary AS
SELECT 
    r.id,
    r.title,
    r.report_type,
    r.status,
    r.created_at,
    r.updated_at,
    rt.name AS template_name,
    p.name AS project_name,
    u.real_name AS created_by_name,
    (SELECT COUNT(*) FROM element_values ev WHERE ev.report_id = r.id AND ev.is_filled = true) AS filled_count,
    (SELECT COUNT(*) FROM template_dynamic_elements te WHERE te.template_id = r.template_id) AS total_elements,
    (SELECT COUNT(*) FROM report_attachments ra WHERE ra.report_id = r.id) AS attachment_count
FROM reports r
LEFT JOIN report_templates rt ON rt.id = r.template_id
LEFT JOIN projects p ON p.id = r.project_id
LEFT JOIN users u ON u.id = r.created_by;

COMMENT ON VIEW v_report_summary IS '报告摘要视图';

-- ============================================================
-- 7. 索引
-- ============================================================

-- users
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);

-- projects
CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_id);
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);

-- report_templates
CREATE INDEX IF NOT EXISTS idx_templates_category ON report_templates(category);
CREATE INDEX IF NOT EXISTS idx_templates_status ON report_templates(status);

-- template_dynamic_elements
CREATE INDEX IF NOT EXISTS idx_template_elements_template ON template_dynamic_elements(template_id);
CREATE INDEX IF NOT EXISTS idx_template_elements_type ON template_dynamic_elements(element_type);
CREATE INDEX IF NOT EXISTS idx_template_elements_namespace ON template_dynamic_elements(namespace);

-- reports
CREATE INDEX IF NOT EXISTS idx_reports_template ON reports(template_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_by ON reports(created_by);
CREATE INDEX IF NOT EXISTS idx_reports_source ON reports(source_report_id);

-- element_values
CREATE INDEX IF NOT EXISTS idx_element_values_report ON element_values(report_id);
CREATE INDEX IF NOT EXISTS idx_element_values_key ON element_values(element_key);
CREATE INDEX IF NOT EXISTS idx_element_values_filled ON element_values(is_filled);

-- element_rules
CREATE INDEX IF NOT EXISTS idx_element_rules_report ON element_rules(report_id);
CREATE INDEX IF NOT EXISTS idx_element_rules_key ON element_rules(element_key);
CREATE INDEX IF NOT EXISTS idx_element_rules_status ON element_rules(status);

-- rule_inputs
CREATE INDEX IF NOT EXISTS idx_rule_inputs_rule ON rule_inputs(rule_id);

-- report_attachments
CREATE INDEX IF NOT EXISTS idx_attachments_report ON report_attachments(report_id);
CREATE INDEX IF NOT EXISTS idx_attachments_parse_status ON report_attachments(parse_status);

-- attachment_entities
CREATE INDEX IF NOT EXISTS idx_attachment_entities_attachment ON attachment_entities(attachment_id);
CREATE INDEX IF NOT EXISTS idx_attachment_entities_type ON attachment_entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_attachment_entities_label ON attachment_entities(business_label);

-- report_copy_records
CREATE INDEX IF NOT EXISTS idx_copy_records_source ON report_copy_records(source_report_id);
CREATE INDEX IF NOT EXISTS idx_copy_records_target ON report_copy_records(target_report_id);

-- ============================================================
-- 8. Mock 数据
-- ============================================================

-- ------------------------------------------------------------
-- 8.1 用户数据
-- ------------------------------------------------------------
INSERT INTO users (id, username, password_hash, real_name, email, status)
VALUES (1, 'admin', '$2a$10$xxxxx', '管理员', 'admin@lingyue.com', 'active')
ON CONFLICT (id) DO NOTHING;

SELECT setval('users_id_seq', GREATEST((SELECT MAX(id) FROM users), 1));

-- ------------------------------------------------------------
-- 8.2 项目数据
-- ------------------------------------------------------------
INSERT INTO projects (id, name, code, description, owner_id, created_by)
VALUES (1, '电力安全评审项目', 'DLAQ-2026', '电力建设企业安全生产标准化评审项目', 1, 1)
ON CONFLICT (id) DO NOTHING;

SELECT setval('projects_id_seq', GREATEST((SELECT MAX(id) FROM projects), 1));

-- ------------------------------------------------------------
-- 8.3 模板数据
-- ------------------------------------------------------------
INSERT INTO report_templates (id, name, category, description, dynamic_element_count, table_element_count, status, created_by)
VALUES (1, '电力安全生产标准化复审报告', '安全评审', '电力建设企业安全生产标准化达标评级评审报告模板', 45, 4, 'active', 1)
ON CONFLICT (id) DO NOTHING;

SELECT setval('report_templates_id_seq', GREATEST((SELECT MAX(id) FROM report_templates), 1));

-- ------------------------------------------------------------
-- 8.4 模板动态要素数据
-- ------------------------------------------------------------

-- 文本型动态要素
INSERT INTO template_dynamic_elements (id, template_id, element_key, element_name, element_type, namespace, field_name, required, sort_order)
VALUES 
(1, 1, 'basicInfo.projectCode', '项目编号', 'text', 'basicInfo', 'projectCode', true, 1),
(2, 1, 'project.reviewObject', '评审对象', 'text', 'project', 'reviewObject', true, 2),
(3, 1, 'project.reviewObjectAlias', '评审对象简称', 'text', 'project', 'reviewObjectAlias', true, 3),
(4, 1, 'basicInfo.requestLevel', '申请级别', 'text', 'basicInfo', 'requestLevel', true, 4),
(5, 1, 'project.resultScore', '评审得分', 'text', 'project', 'resultScore', true, 5),
(6, 1, 'project.resultLevel', '评审级别', 'text', 'project', 'resultLevel', true, 6),
(7, 1, 'project.workStartAt', '评审开始日期', 'text', 'project', 'workStartAt', true, 7),
(8, 1, 'project.workEndAt', '评审结束日期', 'text', 'project', 'workEndAt', true, 8),
(9, 1, 'project.createdAt', '报告日期', 'text', 'project', 'createdAt', true, 9),
(10, 1, 'basicInfo.applyAt', '申请日期', 'text', 'basicInfo', 'applyAt', false, 10),
(11, 1, 'project.reviewPeriod', '评审期', 'text', 'project', 'reviewPeriod', false, 11),
(12, 1, 'basicInfo.reviewObjectCertificateGetAt', '证书获取日期', 'text', 'basicInfo', 'reviewObjectCertificateGetAt', false, 12),
(13, 1, 'basicInfo.reviewObjectCertificate2GetAt', '二次证书获取日期', 'text', 'basicInfo', 'reviewObjectCertificate2GetAt', false, 13)
ON CONFLICT (template_id, element_key) DO NOTHING;

-- 段落型动态要素
INSERT INTO template_dynamic_elements (id, template_id, element_key, element_name, element_type, namespace, field_name, required, sort_order)
VALUES 
(20, 1, 'project.reviewObjectSummary', '评审对象概述', 'paragraph', 'project', 'reviewObjectSummary', true, 20),
(21, 1, 'project.reviewObjectDescription', '企业简介', 'paragraph', 'project', 'reviewObjectDescription', true, 21),
(22, 1, 'project.target', '目标', 'paragraph', 'project', 'target', true, 22),
(23, 1, 'project.duty', '组织机构和职责', 'paragraph', 'project', 'duty', true, 23),
(24, 1, 'project.fullParticipation', '全员参与', 'paragraph', 'project', 'fullParticipation', false, 24),
(25, 1, 'project.safetyInvestment', '安全生产投入', 'paragraph', 'project', 'safetyInvestment', false, 25),
(26, 1, 'project.safetyCulture', '安全文化建设', 'paragraph', 'project', 'safetyCulture', false, 26),
(27, 1, 'project.systematicManagement', '制度化管理', 'paragraph', 'project', 'systematicManagement', false, 27),
(28, 1, 'project.employeeTraining', '人员教育培训', 'paragraph', 'project', 'employeeTraining', false, 28),
(29, 1, 'project.assetManagement', '设备设施管理', 'paragraph', 'project', 'assetManagement', false, 29),
(30, 1, 'project.jobSafety', '作业安全', 'paragraph', 'project', 'jobSafety', false, 30),
(31, 1, 'project.safetyStandardizationStatus', '安全生产标准化建设和持续运行情况', 'paragraph', 'project', 'safetyStandardizationStatus', false, 31)
ON CONFLICT (template_id, element_key) DO NOTHING;

-- 表格型动态要素
INSERT INTO template_dynamic_elements (id, template_id, element_key, element_name, element_type, namespace, field_name, required, table_columns, sort_order)
VALUES 
(40, 1, '+review_status', '现场复审情况表', 'table', NULL, 'review_status', true, '["序号", "项目", "存在的问题", "扣分标准"]', 40),
(41, 1, '+target_responsibility', '目标职责评审表', 'table', NULL, 'target_responsibility', true, '["序号", "项目", "存在的问题", "扣分标准"]', 41),
(42, 1, '+institutionalized_management', '制度化管理评审表', 'table', NULL, 'institutionalized_management', true, '["序号", "项目", "存在的问题", "扣分标准"]', 42),
(43, 1, '+education_and_training', '教育培训评审表', 'table', NULL, 'education_and_training', true, '["序号", "项目", "存在的问题", "扣分标准"]', 43)
ON CONFLICT (template_id, element_key) DO NOTHING;

SELECT setval('template_dynamic_elements_id_seq', GREATEST((SELECT MAX(id) FROM template_dynamic_elements), 43));

-- ------------------------------------------------------------
-- 8.5 报告数据
-- ------------------------------------------------------------

-- 成都院复审报告（原始报告）
INSERT INTO reports (id, title, report_type, status, template_id, project_id, created_by, created_at)
VALUES (1001, '成都院复审报告', '电力安全生产标准化复审报告', 'draft', 1, 1, 1, '2026-02-10 10:30:00')
ON CONFLICT (id) DO NOTHING;

-- 华东院复审报告（从成都院复制）
INSERT INTO reports (id, title, report_type, status, template_id, project_id, created_by, source_report_id, copy_time, created_at)
VALUES (1002, '华东院复审报告', '电力安全生产标准化复审报告', 'draft', 1, 1, 1, 1001, '2026-02-11 14:00:00', '2026-02-11 14:00:00')
ON CONFLICT (id) DO NOTHING;

SELECT setval('reports_id_seq', GREATEST((SELECT MAX(id) FROM reports), 1002));

-- ------------------------------------------------------------
-- 8.6 附件数据
-- ------------------------------------------------------------
INSERT INTO report_attachments (id, report_id, display_name, file_name, file_path, file_type, file_size, parse_status, entity_count, parsed_at, sort_order)
VALUES 
(1, 1001, '01-复审通知', '复审通知.docx', '/uploads/2026/02/复审通知.docx', 'docx', 102400, 'completed', 15, '2026-02-10 10:35:00', 1),
(2, 1001, '02-评审报告原文', '评审报告.pdf', '/uploads/2026/02/评审报告.pdf', 'pdf', 512000, 'completed', 25, '2026-02-10 10:40:00', 2),
(3, 1001, '03-安全标准化达标证书', '达标证书.pdf', '/uploads/2026/02/达标证书.pdf', 'pdf', 256000, 'completed', 8, '2026-02-10 10:45:00', 3)
ON CONFLICT (id) DO NOTHING;

SELECT setval('report_attachments_id_seq', GREATEST((SELECT MAX(id) FROM report_attachments), 3));

-- ------------------------------------------------------------
-- 8.7 附件实体数据
-- ------------------------------------------------------------
INSERT INTO attachment_entities (id, attachment_id, entity_type, entity_text, business_label, confidence, occurrence_count)
VALUES 
-- 复审通知中的实体
(1, 1, 'ORG', '中国电建集团成都勘测设计研究院有限公司', '评审对象', 0.95, 15),
(2, 1, 'ORG', '湖北安源安全环保科技有限公司', '评审单位', 0.92, 8),
(3, 1, 'PERSON', '何彦锋', '董事长', 0.88, 3),
(4, 1, 'PERSON', '张世殊', '总经理', 0.88, 2),
(5, 1, 'DATE', '2024年7月13日', '评审开始日期', 0.99, 2),
(6, 1, 'DATE', '2024年10月17日', '评审结束日期', 0.99, 2),
(7, 1, 'DATE', '2024年7月8日', '申请日期', 0.99, 1),
(8, 1, 'DATE', '2019年12月9日', '证书获取日期', 0.99, 1),
(9, 1, 'DATE', '2015年4月7日', '二次证书获取日期', 0.99, 1),
-- 评审报告中的实体
(10, 2, 'NUMBER', '93.33', '评审得分', 0.99, 3),
(11, 2, 'NUMBER', '50', '注册资本（亿）', 0.95, 2),
(12, 2, 'NUMBER', '2887', '员工人数', 0.95, 1),
(13, 2, 'LOCATION', '成都市温江区政和街8号', '公司地址', 0.90, 1),
-- 达标证书中的实体
(14, 3, 'ORG', '中国电建集团成都勘测设计研究院有限公司', NULL, 0.95, 2),
(15, 3, 'DATE', '2019年12月9日', '发证日期', 0.99, 1)
ON CONFLICT (id) DO NOTHING;

SELECT setval('attachment_entities_id_seq', GREATEST((SELECT MAX(id) FROM attachment_entities), 15));

-- ------------------------------------------------------------
-- 8.8 动态要素值数据（成都院）
-- ------------------------------------------------------------

-- 文本型
INSERT INTO element_values (id, report_id, element_key, value_text, is_filled, fill_source)
VALUES 
(1, 1001, 'basicInfo.projectCode', 'BZ-0092-2024', true, 'rule'),
(2, 1001, 'project.reviewObject', '中国电建集团成都勘测设计研究院有限公司', true, 'rule'),
(3, 1001, 'project.reviewObjectAlias', '成都院', true, 'rule'),
(4, 1001, 'basicInfo.requestLevel', '一级', true, 'rule'),
(5, 1001, 'project.resultScore', '93.33', true, 'rule'),
(6, 1001, 'project.resultLevel', '一级', true, 'rule'),
(7, 1001, 'project.workStartAt', '2024年7月13日', true, 'rule'),
(8, 1001, 'project.workEndAt', '2024年10月17日', true, 'rule'),
(9, 1001, 'project.createdAt', '2026年02月', true, 'manual'),
(10, 1001, 'basicInfo.applyAt', '2024年7月8日', true, 'rule'),
(11, 1001, 'project.reviewPeriod', '2023年7月8日-2024年7月8日', true, 'rule'),
(12, 1001, 'basicInfo.reviewObjectCertificateGetAt', '2019年12月9日', true, 'rule'),
(13, 1001, 'basicInfo.reviewObjectCertificate2GetAt', '2015年4月7日', true, 'rule')
ON CONFLICT (report_id, element_key) DO NOTHING;

-- 段落型
INSERT INTO element_values (id, report_id, element_key, value_text, is_filled, fill_source)
VALUES 
(20, 1001, 'project.reviewObjectSummary', '中国电建集团成都勘测设计研究院有限公司（简称：成都院）成立于2005年，是世界500强企业中国电力建设集团的核心成员企业。作为国家级高新技术企业，成都院以水利水电、新能源和基础设施为核心业务，在水电工程领域承担了金沙江、大渡河、雅砻江等重大河流的规划勘测设计工作，创造了多项"中国第一"和"世界之最"。成都院拥有注册资本50亿元人民币，专业技术人员规模近3000人，累计获得4443项专利和100项行业资质认证，展现了雄厚的技术研发实力。', true, 'rule'),
(21, 1001, 'project.reviewObjectDescription', '中国电建集团成都勘测设计研究院有限公司（简称：成都院）是中国电力建设集团核心成员企业，作为国内领先的清洁能源工程服务商，成都院在水利水电、新能源及基础设施领域具有突出优势。成都院深度参与了国家西部大开发和"西电东送"战略，承担了包括两河口水电站、杨房沟水电站、乌东德水电站等重大工程的勘测设计工作，其中两河口水电站拥有世界最高土石坝的纪录。', true, 'rule'),
(22, 1001, 'project.target', '成都院制定并发布《QHSE"十四五"规划》（安质〔2023〕1号），明确了总体安全生产目标，包括7项安全管理及事故控制目标，总体目标为公司"十四五"规划的子规划，安全生产与职业健康工作目标纳入总体生产经营目标。成都院发布《关于印发<成都院2024年安全生产与职业健康、能源节约与生态环境保护工作目标和重点>的通知》（蓉设安质〔2024〕18号)，明确了2024年安全生产与职业健康目标，包括9项安全管理目标和10项事故控制目标。', true, 'rule')
ON CONFLICT (report_id, element_key) DO NOTHING;

-- 表格型
INSERT INTO element_values (id, report_id, element_key, value_json, is_filled, fill_source)
VALUES 
(40, 1001, '+target_responsibility', '[
  {"序号": 1, "项目": "5.1.1.1 目标制定", "存在的问题": "大邑地勘项目部制定的2024年度安全生产目标，缺少设备设施方面的事故控制目标。", "扣分标准": "依据评估标准②:目标内容有缺失，扣1分/项,共扣1分。"},
  {"序号": 2, "项目": "5.1.1.2 目标落实", "存在的问题": "双江口设计项目部项目经理与各专业部门负责人签订的《2024年度安全责任书》中，目标分解不明确，不满足要求。", "扣分标准": "依据评估标准②:签订责任书、目标分解不满足要求，扣1分/单位,共扣1分。"},
  {"序号": 3, "项目": "5.1.1.3 目标考核", "存在的问题": "云阳监理项目未对二季度安全生产目标及工作计划完成情况进行考核、监督检查。", "扣分标准": "依据评估标准①:未对目标的完成情况进行评估和考核，扣2分/次,共扣2分。"}
]', true, 'rule'),
(41, 1001, '+institutionalized_management', '[
  {"序号": 1, "项目": "5.2.2 规章制度", "存在的问题": "勘测设计分公司未按成都院《安全风险管控管理办法》的要求制定安全风险管控制度。", "扣分标准": "依据评估标准①：制度不全，扣2分/项，共扣2分"},
  {"序号": 2, "项目": "5.2.3 操作规程", "存在的问题": "云阳监理项目1#引水洞汽车吊和下库进出水口挖掘机未张贴安全操作规程。", "扣分标准": "依据评估标准③:设备未悬挂安全操作规程，扣1分/处，共扣2分。"}
]', true, 'rule'),
(42, 1001, '+education_and_training', '[
  {"序号": 1, "项目": "5.3.1 教育培训管理", "存在的问题": "大邑地勘项目部《2024年安全生产教育和培训计划》中，缺少有限空间作业等专项培训内容。", "扣分标准": "依据评估标准③:培训计划未包含职业健康、应急管理及上级要求的培训等内容，内容不全，扣2分。"}
]', true, 'rule')
ON CONFLICT (report_id, element_key) DO NOTHING;

SELECT setval('element_values_id_seq', GREATEST((SELECT MAX(id) FROM element_values), 42));

-- ------------------------------------------------------------
-- 8.9 动态要素规则数据（成都院）
-- ------------------------------------------------------------
INSERT INTO element_rules (id, report_id, element_key, rule_name, description, rule_type, last_output_text, last_run_status, last_run_time, status)
VALUES 
(1, 1001, 'basicInfo.projectCode', '项目编号提取', '从复审通知中提取项目编号', 'extraction', 'BZ-0092-2024', 'success', '2026-02-11 14:00:00', 'active'),
(2, 1001, 'project.reviewObject', '评审对象提取', '从复审通知中提取评审对象', 'extraction', '中国电建集团成都勘测设计研究院有限公司', 'success', '2026-02-11 14:01:00', 'active'),
(3, 1001, 'project.reviewObjectAlias', '评审对象简称提取', '从复审通知中提取评审对象简称', 'extraction', '成都院', 'success', '2026-02-11 14:02:00', 'active'),
(4, 1001, 'project.resultScore', '评审得分提取', '从评审报告中提取评审得分', 'extraction', '93.33', 'success', '2026-02-11 14:03:00', 'active'),
(5, 1001, 'project.workStartAt', '评审开始日期提取', '从复审通知中提取评审开始日期', 'extraction', '2024年7月13日', 'success', '2026-02-11 14:04:00', 'active'),
(6, 1001, 'project.workEndAt', '评审结束日期提取', '从复审通知中提取评审结束日期', 'extraction', '2024年10月17日', 'success', '2026-02-11 14:05:00', 'active'),
(7, 1001, 'project.reviewObjectSummary', '评审对象概述生成', '基于企业简介生成评审对象概述', 'generation', NULL, 'success', '2026-02-11 14:06:00', 'active'),
(8, 1001, 'project.reviewObjectDescription', '企业简介生成', '基于附件内容生成企业简介', 'generation', NULL, 'success', '2026-02-11 14:07:00', 'active'),
(9, 1001, 'project.target', '目标内容生成', '基于附件内容生成目标描述', 'generation', NULL, 'success', '2026-02-11 14:08:00', 'active'),
(10, 1001, '+target_responsibility', '目标职责评审表提取', '从评审报告中提取目标职责评审表', 'extraction', NULL, 'success', '2026-02-11 14:09:00', 'active')
ON CONFLICT (report_id, element_key) DO NOTHING;

SELECT setval('element_rules_id_seq', GREATEST((SELECT MAX(id) FROM element_rules), 10));

-- ------------------------------------------------------------
-- 8.10 规则输入数据
-- ------------------------------------------------------------
INSERT INTO rule_inputs (id, rule_id, input_key, input_name, input_type, ref_attachment_id, ref_entity_id, fixed_value, sort_order)
VALUES 
(1, 1, 'input1', '复审通知', 'attachment_ref', 1, NULL, NULL, 1),
(2, 2, 'input1', '复审通知', 'attachment_ref', 1, NULL, NULL, 1),
(3, 3, 'input1', '评审对象', 'entity_ref', NULL, 1, NULL, 1),
(4, 4, 'input1', '评审报告', 'attachment_ref', 2, NULL, NULL, 1),
(5, 5, 'input1', '复审通知', 'attachment_ref', 1, NULL, NULL, 1),
(6, 6, 'input1', '复审通知', 'attachment_ref', 1, NULL, NULL, 1),
(7, 7, 'input1', '评审报告', 'attachment_ref', 2, NULL, NULL, 1),
(8, 7, 'prompt', '提示词', 'text', NULL, NULL, '请根据以下企业信息，生成一段简洁的企业概述。', 2),
(9, 8, 'input1', '评审报告', 'attachment_ref', 2, NULL, NULL, 1),
(10, 8, 'prompt', '提示词', 'text', NULL, NULL, '请根据以下内容，生成详细的企业简介。', 2),
(11, 9, 'input1', '评审报告', 'attachment_ref', 2, NULL, NULL, 1),
(12, 9, 'prompt', '提示词', 'text', NULL, NULL, '请根据以下内容，提取并整理企业的安全生产目标相关内容。', 2),
(13, 10, 'input1', '评审报告', 'attachment_ref', 2, NULL, NULL, 1)
ON CONFLICT (id) DO NOTHING;

SELECT setval('rule_inputs_id_seq', GREATEST((SELECT MAX(id) FROM rule_inputs), 13));

-- ------------------------------------------------------------
-- 8.11 规则动作配置数据
-- ------------------------------------------------------------
INSERT INTO rule_action_config (id, rule_id, action_type, config)
VALUES 
(1, 1, 'extract_pattern', '{"pattern": "项目编号[：:]\\s*(\\S+)", "group": 1}'),
(2, 2, 'extract_entity', '{"entityType": "ORG", "businessLabel": "评审对象", "selectStrategy": "first"}'),
(3, 3, 'extract_pattern', '{"pattern": "（简称[：:](.+?)）", "group": 1}'),
(4, 4, 'extract_entity', '{"entityType": "NUMBER", "businessLabel": "评审得分", "selectStrategy": "first"}'),
(5, 5, 'extract_entity', '{"entityType": "DATE", "businessLabel": "评审开始日期", "selectStrategy": "first"}'),
(6, 6, 'extract_entity', '{"entityType": "DATE", "businessLabel": "评审结束日期", "selectStrategy": "first"}'),
(7, 7, 'llm_generate', '{"model": "qwen-max", "temperature": 0.7, "maxTokens": 1000}'),
(8, 8, 'llm_generate', '{"model": "qwen-max", "temperature": 0.7, "maxTokens": 2000}'),
(9, 9, 'llm_generate', '{"model": "qwen-max", "temperature": 0.7, "maxTokens": 2000}'),
(10, 10, 'extract_table', '{"tablePattern": "序号.*项目.*问题", "columns": ["序号", "项目", "存在的问题", "扣分标准"]}')
ON CONFLICT (rule_id) DO NOTHING;

SELECT setval('rule_action_config_id_seq', GREATEST((SELECT MAX(id) FROM rule_action_config), 10));

-- ------------------------------------------------------------
-- 8.12 报告复制记录
-- ------------------------------------------------------------
INSERT INTO report_copy_records (id, source_report_id, target_report_id, copied_by, copy_time, elements_copied, rules_copied, notes)
VALUES (1, 1001, 1002, 1, '2026-02-11 14:00:00', 45, 10, '从成都院复制到华东院')
ON CONFLICT (id) DO NOTHING;

SELECT setval('report_copy_records_id_seq', GREATEST((SELECT MAX(id) FROM report_copy_records), 1));

-- ============================================================
-- 完成
-- ============================================================
SELECT '灵越智报 2.0 数据库完整脚本执行完成' AS result;