lingyue-zhibao/定稿/init_mock.sql

-- ============================================================
-- 灵越智报 2.0 - 数据库初始化脚本（定稿）
-- 版本: v1.0-final
-- 设计: 简化单层项目结构（前端只关心"项目"概念）
-- 场景: 成都院-安全生产标准化复审报告 Mock 数据
-- 定稿时间: 2026-02-12
-- ============================================================

BEGIN;

-- ============================================================
-- 0. PostgreSQL扩展
-- ============================================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================
-- 0.1 前端业务支撑表（sys_* 系列）
-- ============================================================

-- 用户表
CREATE TABLE IF NOT EXISTS sys_users (
    id BIGSERIAL PRIMARY KEY,
    node_id BIGINT,  -- 关联 nodes 表，延迟约束（nodes 表在后面创建）
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    salt VARCHAR(50),
    email VARCHAR(200),
    phone VARCHAR(20),
    avatar VARCHAR(500),
    real_name VARCHAR(100),
    department VARCHAR(200),
    position VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active',
    last_login_at TIMESTAMP,
    last_login_ip VARCHAR(50),
    login_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_users_username ON sys_users(username);
CREATE INDEX IF NOT EXISTS idx_sys_users_node ON sys_users(node_id);

-- 会话表
CREATE TABLE IF NOT EXISTS sys_sessions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES sys_users(id) ON DELETE CASCADE,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    refresh_token VARCHAR(255),
    device_type VARCHAR(50),
    device_info TEXT,
    ip_address VARCHAR(50),
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_sessions_user ON sys_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_sessions_token ON sys_sessions(session_token);

-- 登录日志表
CREATE TABLE IF NOT EXISTS sys_login_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES sys_users(id) ON DELETE SET NULL,
    username VARCHAR(100),
    login_type VARCHAR(20),
    ip_address VARCHAR(50),
    user_agent TEXT,
    device_type VARCHAR(50),
    location VARCHAR(200),
    status VARCHAR(20) NOT NULL,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_login_logs_user ON sys_login_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_login_logs_created ON sys_login_logs(created_at DESC);

-- 角色表
CREATE TABLE IF NOT EXISTS sys_roles (
    id SERIAL PRIMARY KEY,
    role_code VARCHAR(50) NOT NULL UNIQUE,
    role_name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_roles_code ON sys_roles(role_code);

-- 权限表（菜单+按钮）
CREATE TABLE IF NOT EXISTS sys_permissions (
    id SERIAL PRIMARY KEY,
    perm_code VARCHAR(100) NOT NULL UNIQUE,
    perm_name VARCHAR(200) NOT NULL,
    perm_type VARCHAR(20) NOT NULL,
    parent_id INT REFERENCES sys_permissions(id) ON DELETE CASCADE,
    path VARCHAR(500),
    icon VARCHAR(100),
    component VARCHAR(200),
    sort_order INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_permissions_parent ON sys_permissions(parent_id);
CREATE INDEX IF NOT EXISTS idx_sys_permissions_type ON sys_permissions(perm_type);

-- 用户角色关联表
CREATE TABLE IF NOT EXISTS sys_user_roles (
    id SERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES sys_users(id) ON DELETE CASCADE,
    role_id INT NOT NULL REFERENCES sys_roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, role_id)
);
CREATE INDEX IF NOT EXISTS idx_sys_user_roles_user ON sys_user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_user_roles_role ON sys_user_roles(role_id);

-- 角色权限关联表
CREATE TABLE IF NOT EXISTS sys_role_permissions (
    id SERIAL PRIMARY KEY,
    role_id INT NOT NULL REFERENCES sys_roles(id) ON DELETE CASCADE,
    permission_id INT NOT NULL REFERENCES sys_permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(role_id, permission_id)
);
CREATE INDEX IF NOT EXISTS idx_sys_role_permissions_role ON sys_role_permissions(role_id);
CREATE INDEX IF NOT EXISTS idx_sys_role_permissions_perm ON sys_role_permissions(permission_id);

-- 系统配置表
CREATE TABLE IF NOT EXISTS sys_configs (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    config_json JSONB,
    config_type VARCHAR(50),
    description TEXT,
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_configs_key ON sys_configs(config_key);

-- 数据字典类型表
CREATE TABLE IF NOT EXISTS sys_dict_types (
    id SERIAL PRIMARY KEY,
    dict_code VARCHAR(100) NOT NULL UNIQUE,
    dict_name VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_dict_types_code ON sys_dict_types(dict_code);

-- 数据字典项表
CREATE TABLE IF NOT EXISTS sys_dict_items (
    id SERIAL PRIMARY KEY,
    dict_type_id INT NOT NULL REFERENCES sys_dict_types(id) ON DELETE CASCADE,
    item_code VARCHAR(100) NOT NULL,
    item_name VARCHAR(200) NOT NULL,
    item_value TEXT,
    sort_order INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    extra JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(dict_type_id, item_code)
);
CREATE INDEX IF NOT EXISTS idx_sys_dict_items_type ON sys_dict_items(dict_type_id);

-- 操作日志表
CREATE TABLE IF NOT EXISTS sys_operation_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(100),
    module VARCHAR(100),
    action VARCHAR(100),
    method VARCHAR(10),
    url VARCHAR(500),
    params TEXT,
    result TEXT,
    ip_address VARCHAR(50),
    user_agent TEXT,
    duration_ms INT,
    status VARCHAR(20),
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_operation_logs_user ON sys_operation_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_operation_logs_created ON sys_operation_logs(created_at DESC);

-- 文件存储表
CREATE TABLE IF NOT EXISTS sys_files (
    id BIGSERIAL PRIMARY KEY,
    file_key VARCHAR(100) NOT NULL UNIQUE,
    original_name VARCHAR(500) NOT NULL,
    storage_name VARCHAR(200) NOT NULL,
    storage_path VARCHAR(500) NOT NULL,
    file_type VARCHAR(100),
    file_size BIGINT,
    md5_hash VARCHAR(32),
    storage_type VARCHAR(20) DEFAULT 'local',
    bucket VARCHAR(100),
    url VARCHAR(1000),
    thumbnail_url VARCHAR(1000),
    uploaded_by BIGINT,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_files_key ON sys_files(file_key);
CREATE INDEX IF NOT EXISTS idx_sys_files_uploaded ON sys_files(uploaded_by);

-- 异步任务队列表
CREATE TABLE IF NOT EXISTS sys_tasks (
    id BIGSERIAL PRIMARY KEY,
    task_type VARCHAR(50) NOT NULL,
    task_key VARCHAR(100),
    payload JSONB,
    status VARCHAR(20) DEFAULT 'pending',
    priority INT DEFAULT 0,
    retry_count INT DEFAULT 0,
    max_retries INT DEFAULT 3,
    result JSONB,
    error_msg TEXT,
    started_at TIMESTAMP,
    finished_at TIMESTAMP,
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sys_tasks_type ON sys_tasks(task_type);
CREATE INDEX IF NOT EXISTS idx_sys_tasks_status ON sys_tasks(status);
CREATE INDEX IF NOT EXISTS idx_sys_tasks_created ON sys_tasks(created_at DESC);

-- ============================================================
-- 1. 核心图结构表
-- ============================================================

-- 1.1 节点类型定义表
CREATE TABLE IF NOT EXISTS node_types (
    id            SERIAL       PRIMARY KEY,
    type_code     VARCHAR(50)  NOT NULL UNIQUE,
    type_name     VARCHAR(100) NOT NULL,
    description   TEXT,
    icon          VARCHAR(100),
    color         VARCHAR(20),
    created_at    TIMESTAMP    DEFAULT NOW()
);

-- 1.2 关系类型定义表
CREATE TABLE IF NOT EXISTS edge_types (
    id             SERIAL       PRIMARY KEY,
    type_code      VARCHAR(50)  NOT NULL,
    type_name      VARCHAR(100) NOT NULL,
    from_node_type VARCHAR(50),
    to_node_type   VARCHAR(50),
    description    TEXT,
    created_at     TIMESTAMP    DEFAULT NOW(),
    UNIQUE(type_code, from_node_type, to_node_type)
);

-- 1.3 节点表（核心）
CREATE TABLE IF NOT EXISTS nodes (
    id            BIGSERIAL    PRIMARY KEY,
    node_type     VARCHAR(50)  NOT NULL,
    node_key      VARCHAR(200),
    name          VARCHAR(500) NOT NULL,
    status        VARCHAR(50)  DEFAULT 'active',
    created_by    BIGINT,
    created_at    TIMESTAMP    DEFAULT NOW(),
    updated_at    TIMESTAMP    DEFAULT NOW()
);

-- 1.4 关系表（核心）
CREATE TABLE IF NOT EXISTS edges (
    id            BIGSERIAL    PRIMARY KEY,
    edge_type     VARCHAR(50)  NOT NULL,
    from_node_id  BIGINT       NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    to_node_id    BIGINT       NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    sort_order    INT          DEFAULT 0,
    created_at    TIMESTAMP    DEFAULT NOW()
);

-- 1.5 节点属性表
CREATE TABLE IF NOT EXISTS node_properties (
    id            BIGSERIAL    PRIMARY KEY,
    node_id       BIGINT       NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    prop_key      VARCHAR(100) NOT NULL,
    prop_value    TEXT,
    prop_json     JSONB,
    prop_number   DECIMAL(20,4),
    prop_date     TIMESTAMP,
    created_at    TIMESTAMP    DEFAULT NOW(),
    updated_at    TIMESTAMP    DEFAULT NOW()
);

-- 1.6 关系属性表
CREATE TABLE IF NOT EXISTS edge_properties (
    id            BIGSERIAL    PRIMARY KEY,
    edge_id       BIGINT       NOT NULL REFERENCES edges(id) ON DELETE CASCADE,
    prop_key      VARCHAR(100) NOT NULL,
    prop_value    TEXT,
    prop_json     JSONB,
    created_at    TIMESTAMP    DEFAULT NOW()
);

-- 1.7 属性定义表
CREATE TABLE IF NOT EXISTS property_definitions (
    id            SERIAL       PRIMARY KEY,
    owner_type    VARCHAR(20)  NOT NULL,
    target_type   VARCHAR(50)  NOT NULL,
    prop_key      VARCHAR(100) NOT NULL,
    prop_name     VARCHAR(200) NOT NULL,
    data_type     VARCHAR(50)  NOT NULL,
    required      BOOLEAN      DEFAULT false,
    default_value TEXT,
    description   TEXT,
    created_at    TIMESTAMP    DEFAULT NOW()
);

-- ============================================================
-- 2. 索引设计
-- ============================================================

-- nodes 表索引
CREATE INDEX IF NOT EXISTS idx_nodes_type ON nodes(node_type);
CREATE INDEX IF NOT EXISTS idx_nodes_type_status ON nodes(node_type, status);
CREATE INDEX IF NOT EXISTS idx_nodes_key ON nodes(node_key);
CREATE UNIQUE INDEX IF NOT EXISTS idx_nodes_type_key ON nodes(node_type, node_key) WHERE node_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_nodes_created_by ON nodes(created_by);
CREATE INDEX IF NOT EXISTS idx_nodes_created_at ON nodes(created_at DESC);

-- edges 表索引
CREATE INDEX IF NOT EXISTS idx_edges_type ON edges(edge_type);
CREATE INDEX IF NOT EXISTS idx_edges_from ON edges(from_node_id);
CREATE INDEX IF NOT EXISTS idx_edges_to ON edges(to_node_id);
CREATE INDEX IF NOT EXISTS idx_edges_type_from ON edges(edge_type, from_node_id);
CREATE INDEX IF NOT EXISTS idx_edges_type_to ON edges(edge_type, to_node_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_edges_unique ON edges(edge_type, from_node_id, to_node_id);

-- node_properties 表索引
CREATE INDEX IF NOT EXISTS idx_node_props_node ON node_properties(node_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_node_props_unique ON node_properties(node_id, prop_key);
CREATE INDEX IF NOT EXISTS idx_node_props_key ON node_properties(prop_key);

-- edge_properties 表索引
CREATE INDEX IF NOT EXISTS idx_edge_props_edge ON edge_properties(edge_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_edge_props_unique ON edge_properties(edge_id, prop_key);

-- ============================================================
-- 3. 视图定义（简化设计：只保留PROJECT相关视图）
-- ============================================================

-- 3.1 项目视图（合并了原模板和报告的功能）
CREATE OR REPLACE VIEW v_projects AS
SELECT 
    n.id,
    n.name AS title,
    n.node_key AS project_code,
    n.status,
    n.created_at,
    n.updated_at,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'description') AS description,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'template_type') AS template_type,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'content_html') AS content_html,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'content_markdown') AS content_markdown,
    (SELECT COUNT(*) FROM edges e WHERE e.from_node_id = n.id AND e.edge_type = 'HAS_ELEMENT') AS element_count,
    (SELECT COUNT(*) FROM edges e WHERE e.from_node_id = n.id AND e.edge_type = 'HAS_ATTACHMENT') AS attachment_count,
    (SELECT COUNT(*) FROM edges e WHERE e.from_node_id = n.id AND e.edge_type = 'HAS_RULE') AS rule_count,
    (SELECT COUNT(*) FROM edges e 
     JOIN nodes v ON v.id = e.to_node_id 
     WHERE e.from_node_id = n.id AND e.edge_type = 'HAS_VALUE' 
     AND EXISTS (SELECT 1 FROM node_properties WHERE node_id = v.id AND prop_key = 'is_filled' AND prop_value = 'true')
    ) AS filled_count,
    (SELECT n2.id FROM edges e JOIN nodes n2 ON n2.id = e.to_node_id 
     WHERE e.from_node_id = n.id AND e.edge_type = 'COPIED_FROM' LIMIT 1) AS source_project_id,
    n.created_by,
    (SELECT username FROM sys_users WHERE node_id = n.created_by) AS created_by_name
FROM nodes n
WHERE n.node_type = 'PROJECT';

-- 3.2 项目要素视图
CREATE OR REPLACE VIEW v_project_elements AS
SELECT 
    n.id,
    n.name AS element_name,
    n.node_key AS element_key,
    n.created_at,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'element_type') AS element_type,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'namespace') AS namespace,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'field_name') AS field_name,
    (SELECT prop_json FROM node_properties WHERE node_id = n.id AND prop_key = 'table_columns') AS table_columns,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'required')::boolean AS required,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'default_value') AS default_value,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'description') AS description,
    (SELECT e.from_node_id FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_ELEMENT' LIMIT 1) AS project_id,
    (SELECT e.sort_order FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_ELEMENT' LIMIT 1) AS sort_order
FROM nodes n
WHERE n.node_type = 'ELEMENT';

-- 3.3 项目要素值视图
CREATE OR REPLACE VIEW v_project_values AS
SELECT 
    n.id AS value_id,
    n.node_key AS element_key,
    n.created_at,
    n.updated_at,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'value_text') AS value_text,
    (SELECT prop_json FROM node_properties WHERE node_id = n.id AND prop_key = 'value_json') AS value_json,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'is_filled')::boolean AS is_filled,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'fill_source') AS fill_source,
    (SELECT e.from_node_id FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_VALUE' LIMIT 1) AS project_id
FROM nodes n
WHERE n.node_type = 'VALUE';

-- 3.4 附件视图
CREATE OR REPLACE VIEW v_attachments AS
SELECT 
    n.id,
    n.name AS display_name,
    n.node_key AS file_key,
    n.created_at,
    n.updated_at,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'file_name') AS file_name,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'file_path') AS file_path,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'file_type') AS file_type,
    (SELECT prop_number FROM node_properties WHERE node_id = n.id AND prop_key = 'file_size')::bigint AS file_size,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'parse_status') AS parse_status,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'parsed_text') AS parsed_text,
    (SELECT prop_date FROM node_properties WHERE node_id = n.id AND prop_key = 'parsed_at') AS parsed_at,
    (SELECT COUNT(*) FROM edges e WHERE e.from_node_id = n.id AND e.edge_type = 'HAS_ENTITY') AS entity_count,
    (SELECT e.from_node_id FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_ATTACHMENT' LIMIT 1) AS project_id,
    (SELECT e.sort_order FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_ATTACHMENT' LIMIT 1) AS sort_order
FROM nodes n
WHERE n.node_type = 'ATTACHMENT';

-- 3.5 实体视图
CREATE OR REPLACE VIEW v_entities AS
SELECT 
    n.id,
    n.name AS entity_text,
    n.node_key AS entity_key,
    n.created_at,
    n.updated_at,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'entity_type') AS entity_type,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'business_label') AS business_label,
    (SELECT prop_number FROM node_properties WHERE node_id = n.id AND prop_key = 'confidence') AS confidence,
    (SELECT prop_number FROM node_properties WHERE node_id = n.id AND prop_key = 'occurrence_count')::int AS occurrence_count,
    (SELECT e.from_node_id FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_ENTITY' LIMIT 1) AS attachment_id
FROM nodes n
WHERE n.node_type = 'ENTITY';

-- 3.6 规则视图
CREATE OR REPLACE VIEW v_rules AS
SELECT 
    n.id,
    n.name AS rule_name,
    n.node_key AS element_key,
    n.status,
    n.created_at,
    n.updated_at,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'description') AS description,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'rule_type') AS rule_type,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'action_type') AS action_type,
    (SELECT prop_json FROM node_properties WHERE node_id = n.id AND prop_key = 'action_config') AS action_config,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'dsl_content') AS dsl_content,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'last_output_text') AS last_output_text,
    (SELECT prop_json FROM node_properties WHERE node_id = n.id AND prop_key = 'last_output_json') AS last_output_json,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'last_run_status') AS last_run_status,
    (SELECT prop_date FROM node_properties WHERE node_id = n.id AND prop_key = 'last_run_time') AS last_run_time,
    (SELECT prop_value FROM node_properties WHERE node_id = n.id AND prop_key = 'last_run_error') AS last_run_error,
    (SELECT e.from_node_id FROM edges e WHERE e.to_node_id = n.id AND e.edge_type = 'HAS_RULE' LIMIT 1) AS project_id
FROM nodes n
WHERE n.node_type = 'RULE';

-- 3.9 规则输入视图
CREATE OR REPLACE VIEW v_rule_inputs AS
SELECT 
    e.id AS input_id,
    e.from_node_id AS rule_id,
    e.to_node_id AS source_node_id,
    e.sort_order,
    (SELECT prop_value FROM edge_properties WHERE edge_id = e.id AND prop_key = 'input_key') AS input_key,
    (SELECT prop_value FROM edge_properties WHERE edge_id = e.id AND prop_key = 'input_name') AS input_name,
    (SELECT prop_value FROM edge_properties WHERE edge_id = e.id AND prop_key = 'input_type') AS input_type,
    (SELECT prop_value FROM edge_properties WHERE edge_id = e.id AND prop_key = 'fixed_value') AS fixed_value,
    n.node_type AS source_type,
    n.name AS source_name
FROM edges e
JOIN nodes n ON n.id = e.to_node_id
WHERE e.edge_type = 'INPUT_FROM';

-- ============================================================
-- 4. 初始化节点类型和关系类型
-- ============================================================

INSERT INTO node_types (type_code, type_name, description, icon, color) VALUES
('PROJECT', '项目', '智报项目（包含要素定义和内容）', 'folder', '#1890ff'),
('ELEMENT', '要素', '项目要素定义', 'tag', '#13c2c2'),
('VALUE', '要素值', '要素的具体值', 'edit', '#eb2f96'),
('ATTACHMENT', '附件', '项目附件', 'paperclip', '#faad14'),
('ENTITY', '实体', 'NER识别的实体', 'user', '#2f54eb'),
('RULE', '规则', '数据提取规则', 'branches', '#f5222d')
ON CONFLICT (type_code) DO NOTHING;

-- 2.2 插入关系类型（简化设计：PROJECT直接关联所有子节点）
INSERT INTO edge_types (type_code, type_name, from_node_type, to_node_type, description) VALUES
('HAS_ELEMENT', '包含要素', 'PROJECT', 'ELEMENT', '项目包含要素定义'),
('HAS_VALUE', '包含值', 'PROJECT', 'VALUE', '项目有要素值'),
('FOR_ELEMENT', '对应要素', 'VALUE', 'ELEMENT', '值对应的要素'),
('HAS_ATTACHMENT', '包含附件', 'PROJECT', 'ATTACHMENT', '项目有附件'),
('HAS_ENTITY', '包含实体', 'ATTACHMENT', 'ENTITY', '附件有实体'),
('HAS_RULE', '包含规则', 'PROJECT', 'RULE', '项目有规则'),
('FOR_ELEMENT', '针对要素', 'RULE', 'ELEMENT', '规则对应的要素'),
('INPUT_FROM', '输入来源', 'RULE', 'ENTITY', '规则输入来源（实体）'),
('INPUT_FROM', '输入来源', 'RULE', 'ATTACHMENT', '规则输入来源（附件）'),
('INPUT_FROM', '输入来源', 'RULE', 'VALUE', '规则输入来源（其他值）'),
('COPIED_FROM', '复制来源', 'PROJECT', 'PROJECT', '项目复制来源')
ON CONFLICT (type_code, from_node_type, to_node_type) DO NOTHING;

-- ============================================================
-- 5. Mock数据 - 成都院复审报告场景（简化设计）
-- ============================================================

-- 5.1 创建项目节点（合并了原模板和报告的功能）
INSERT INTO nodes (id, node_type, node_key, name, status, created_by) VALUES
(10, 'PROJECT', 'PRJ-2024-001', '成都院2024年复审报告', 'draft', 1);

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(10, 'title', '成都院2024年复审报告'),
(10, 'description', '成都院电力安全生产标准化复审报告'),
(10, 'status', 'draft'),
(10, 'template_type', '电力安全生产标准化复审报告'),
(10, 'content_html', '<h1>成都院2024年复审报告</h1><p>报告内容...</p>'),
(10, 'content_markdown', '# 成都院2024年复审报告\n\n报告内容...');

-- 5.5 创建要素定义节点
INSERT INTO nodes (id, node_type, node_key, name, created_by) VALUES
(201, 'ELEMENT', 'basicInfo.projectCode', '项目编号', 1),
(202, 'ELEMENT', 'basicInfo.reviewObject', '评审对象', 1),
(203, 'ELEMENT', 'basicInfo.reviewType', '评审类型', 1),
(204, 'ELEMENT', 'schedule.startDate', '评审开始日期', 1),
(205, 'ELEMENT', 'schedule.endDate', '评审结束日期', 1),
(206, 'ELEMENT', 'result.score', '评审得分', 1),
(207, 'ELEMENT', 'result.conclusion', '评审结论', 1),
(208, 'ELEMENT', 'experts.list', '专家组成员', 1);

-- 添加要素定义属性
INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(201, 'element_type', 'text'),
(201, 'namespace', 'basicInfo'),
(201, 'field_name', 'projectCode'),
(201, 'required', 'true'),
(201, 'description', '项目编号，格式如 BZ-0092-2024'),

(202, 'element_type', 'text'),
(202, 'namespace', 'basicInfo'),
(202, 'field_name', 'reviewObject'),
(202, 'required', 'true'),
(202, 'description', '被评审的企业或单位名称'),

(203, 'element_type', 'text'),
(203, 'namespace', 'basicInfo'),
(203, 'field_name', 'reviewType'),
(203, 'required', 'true'),
(203, 'default_value', '复审'),

(204, 'element_type', 'text'),
(204, 'namespace', 'schedule'),
(204, 'field_name', 'startDate'),
(204, 'required', 'true'),

(205, 'element_type', 'text'),
(205, 'namespace', 'schedule'),
(205, 'field_name', 'endDate'),
(205, 'required', 'true'),

(206, 'element_type', 'text'),
(206, 'namespace', 'result'),
(206, 'field_name', 'score'),
(206, 'required', 'true'),

(207, 'element_type', 'paragraph'),
(207, 'namespace', 'result'),
(207, 'field_name', 'conclusion'),
(207, 'required', 'true'),

(208, 'element_type', 'table'),
(208, 'namespace', 'experts'),
(208, 'field_name', 'list');

INSERT INTO node_properties (node_id, prop_key, prop_json) VALUES
(208, 'table_columns', '["姓名", "单位", "职称", "专业"]');

-- 5.2 创建 PROJECT --HAS_ELEMENT--> ELEMENT 关系
INSERT INTO edges (edge_type, from_node_id, to_node_id, sort_order) VALUES
('HAS_ELEMENT', 10, 201, 1),
('HAS_ELEMENT', 10, 202, 2),
('HAS_ELEMENT', 10, 203, 3),
('HAS_ELEMENT', 10, 204, 4),
('HAS_ELEMENT', 10, 205, 5),
('HAS_ELEMENT', 10, 206, 6),
('HAS_ELEMENT', 10, 207, 7),
('HAS_ELEMENT', 10, 208, 8);

-- 5.3 创建要素值节点（初始为空）
INSERT INTO nodes (id, node_type, node_key, name, created_by) VALUES
(301, 'VALUE', 'PRJ-2024-001:basicInfo.projectCode', '项目编号值', 1),
(302, 'VALUE', 'PRJ-2024-001:basicInfo.reviewObject', '评审对象值', 1),
(303, 'VALUE', 'PRJ-2024-001:basicInfo.reviewType', '评审类型值', 1),
(304, 'VALUE', 'PRJ-2024-001:schedule.startDate', '评审开始日期值', 1),
(305, 'VALUE', 'PRJ-2024-001:schedule.endDate', '评审结束日期值', 1),
(306, 'VALUE', 'PRJ-2024-001:result.score', '评审得分值', 1),
(307, 'VALUE', 'PRJ-2024-001:result.conclusion', '评审结论值', 1),
(308, 'VALUE', 'PRJ-2024-001:experts.list', '专家组成员值', 1);

-- 初始化要素值属性（未填充）
INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(301, 'is_filled', 'false'),
(302, 'is_filled', 'false'),
(303, 'is_filled', 'true'),
(303, 'value_text', '复审'),
(303, 'fill_source', 'default'),
(304, 'is_filled', 'false'),
(305, 'is_filled', 'false'),
(306, 'is_filled', 'false'),
(307, 'is_filled', 'false'),
(308, 'is_filled', 'false');

-- 5.4 创建 PROJECT --HAS_VALUE--> VALUE 关系
INSERT INTO edges (edge_type, from_node_id, to_node_id) VALUES
('HAS_VALUE', 10, 301),
('HAS_VALUE', 10, 302),
('HAS_VALUE', 10, 303),
('HAS_VALUE', 10, 304),
('HAS_VALUE', 10, 305),
('HAS_VALUE', 10, 306),
('HAS_VALUE', 10, 307),
('HAS_VALUE', 10, 308);

-- 创建 FOR_ELEMENT 关系
INSERT INTO edges (edge_type, from_node_id, to_node_id) VALUES
('FOR_ELEMENT', 301, 201),
('FOR_ELEMENT', 302, 202),
('FOR_ELEMENT', 303, 203),
('FOR_ELEMENT', 304, 204),
('FOR_ELEMENT', 305, 205),
('FOR_ELEMENT', 306, 206),
('FOR_ELEMENT', 307, 207),
('FOR_ELEMENT', 308, 208);

-- 5.5 创建附件节点
INSERT INTO nodes (id, node_type, node_key, name, created_by) VALUES
(400, 'ATTACHMENT', 'ATT-2024-001', '01-复审通知', 1),
(401, 'ATTACHMENT', 'ATT-2024-002', '02-评审计划', 1);

INSERT INTO node_properties (node_id, prop_key, prop_value, prop_number) VALUES
(400, 'file_name', '复审通知.docx', NULL),
(400, 'file_path', '/uploads/2024/02/attachments/复审通知.docx', NULL),
(400, 'file_type', 'docx', NULL),
(400, 'file_size', NULL, 512000),
(400, 'parse_status', 'completed', NULL),
(400, 'parsed_text', '关于开展中国电建集团成都勘测设计研究院有限公司安全生产标准化复审工作的通知...', NULL),

(401, 'file_name', '评审计划.docx', NULL),
(401, 'file_path', '/uploads/2024/02/attachments/评审计划.docx', NULL),
(401, 'file_type', 'docx', NULL),
(401, 'file_size', NULL, 768000),
(401, 'parse_status', 'completed', NULL);

-- 5.6 创建 PROJECT --HAS_ATTACHMENT--> ATTACHMENT 关系
INSERT INTO edges (edge_type, from_node_id, to_node_id, sort_order) VALUES
('HAS_ATTACHMENT', 10, 400, 1),
('HAS_ATTACHMENT', 10, 401, 2);

-- 5.9 创建实体节点（从附件中提取）
INSERT INTO nodes (id, node_type, node_key, name, created_by) VALUES
(500, 'ENTITY', 'ORG:中国电建集团成都勘测设计研究院有限公司', '中国电建集团成都勘测设计研究院有限公司', 1),
(501, 'ENTITY', 'ORG:成都院', '成都院', 1),
(502, 'ENTITY', 'CODE:BZ-0092-2024', 'BZ-0092-2024', 1),
(503, 'ENTITY', 'PERSON:何彦锋', '何彦锋', 1),
(504, 'ENTITY', 'DATE:2024年7月13日', '2024年7月13日', 1),
(505, 'ENTITY', 'DATE:2024年10月17日', '2024年10月17日', 1),
(506, 'ENTITY', 'NUMBER:93.33', '93.33', 1);

INSERT INTO node_properties (node_id, prop_key, prop_value, prop_number) VALUES
(500, 'entity_type', 'ORG', NULL),
(500, 'business_label', '评审对象', NULL),
(500, 'confidence', NULL, 0.95),
(500, 'occurrence_count', NULL, 5),

(501, 'entity_type', 'ORG', NULL),
(501, 'business_label', '评审对象简称', NULL),
(501, 'confidence', NULL, 0.92),

(502, 'entity_type', 'CODE', NULL),
(502, 'business_label', '项目编号', NULL),
(502, 'confidence', NULL, 0.98),

(503, 'entity_type', 'PERSON', NULL),
(503, 'business_label', '专家', NULL),
(503, 'confidence', NULL, 0.90),

(504, 'entity_type', 'DATE', NULL),
(504, 'business_label', '评审开始日期', NULL),
(504, 'confidence', NULL, 0.96),

(505, 'entity_type', 'DATE', NULL),
(505, 'business_label', '评审结束日期', NULL),
(505, 'confidence', NULL, 0.96),

(506, 'entity_type', 'NUMBER', NULL),
(506, 'business_label', '评审得分', NULL),
(506, 'confidence', NULL, 0.88);

-- 创建 HAS_ENTITY 关系（附件 → 实体）
INSERT INTO edges (edge_type, from_node_id, to_node_id) VALUES
('HAS_ENTITY', 400, 500),
('HAS_ENTITY', 400, 501),
('HAS_ENTITY', 400, 502),
('HAS_ENTITY', 400, 503),
('HAS_ENTITY', 400, 504),
('HAS_ENTITY', 400, 505),
('HAS_ENTITY', 401, 506);

-- 5.7 创建规则节点（用户从实体添加的规则）
INSERT INTO nodes (id, node_type, node_key, name, status, created_by) VALUES
(600, 'RULE', 'PRJ-2024-001:basicInfo.projectCode', '项目编号-直接引用实体', 'active', 1),
(601, 'RULE', 'PRJ-2024-001:basicInfo.reviewObject', '评审对象-直接引用实体', 'active', 1),
(602, 'RULE', 'PRJ-2024-001:schedule.startDate', '评审开始日期-直接引用实体', 'active', 1),
(603, 'RULE', 'PRJ-2024-001:schedule.endDate', '评审结束日期-直接引用实体', 'active', 1),
(604, 'RULE', 'PRJ-2024-001:result.score', '评审得分-直接引用实体', 'active', 1);

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(600, 'rule_type', 'direct_entity'),
(600, 'action_type', 'use_entity_value'),
(600, 'description', '直接使用附件实体值填充项目编号'),

(601, 'rule_type', 'direct_entity'),
(601, 'action_type', 'use_entity_value'),
(601, 'description', '直接使用附件实体值填充评审对象'),

(602, 'rule_type', 'direct_entity'),
(602, 'action_type', 'use_entity_value'),
(602, 'description', '直接使用附件实体值填充评审开始日期'),

(603, 'rule_type', 'direct_entity'),
(603, 'action_type', 'use_entity_value'),
(603, 'description', '直接使用附件实体值填充评审结束日期'),

(604, 'rule_type', 'direct_entity'),
(604, 'action_type', 'use_entity_value'),
(604, 'description', '直接使用附件实体值填充评审得分');

-- 5.8 创建 PROJECT --HAS_RULE--> RULE 关系
INSERT INTO edges (edge_type, from_node_id, to_node_id) VALUES
('HAS_RULE', 10, 600),
('HAS_RULE', 10, 601),
('HAS_RULE', 10, 602),
('HAS_RULE', 10, 603),
('HAS_RULE', 10, 604);

-- 创建 FOR_ELEMENT 关系（规则 → 要素定义）
INSERT INTO edges (edge_type, from_node_id, to_node_id) VALUES
('FOR_ELEMENT', 600, 201),
('FOR_ELEMENT', 601, 202),
('FOR_ELEMENT', 602, 204),
('FOR_ELEMENT', 603, 205),
('FOR_ELEMENT', 604, 206);

-- 创建 INPUT_FROM 关系（规则 → 实体）
INSERT INTO edges (id, edge_type, from_node_id, to_node_id, sort_order) VALUES
(1000, 'INPUT_FROM', 600, 502, 1),
(1001, 'INPUT_FROM', 601, 500, 1),
(1002, 'INPUT_FROM', 602, 504, 1),
(1003, 'INPUT_FROM', 603, 505, 1),
(1004, 'INPUT_FROM', 604, 506, 1);

-- 添加输入属性
INSERT INTO edge_properties (edge_id, prop_key, prop_value) VALUES
(1000, 'input_key', 'entity'),
(1000, 'input_type', 'entity_ref'),
(1000, 'input_name', '项目编号实体'),

(1001, 'input_key', 'entity'),
(1001, 'input_type', 'entity_ref'),
(1001, 'input_name', '评审对象实体'),

(1002, 'input_key', 'entity'),
(1002, 'input_type', 'entity_ref'),
(1002, 'input_name', '开始日期实体'),

(1003, 'input_key', 'entity'),
(1003, 'input_type', 'entity_ref'),
(1003, 'input_name', '结束日期实体'),

(1004, 'input_key', 'entity'),
(1004, 'input_type', 'entity_ref'),
(1004, 'input_name', '得分实体');

-- 5.11 执行规则，填充要素值
UPDATE node_properties SET prop_value = 'BZ-0092-2024', updated_at = NOW()
WHERE node_id = 301 AND prop_key = 'value_text';

UPDATE node_properties SET prop_value = 'true', updated_at = NOW()
WHERE node_id = 301 AND prop_key = 'is_filled';

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(301, 'fill_source', 'rule')
ON CONFLICT (node_id, prop_key) DO UPDATE SET prop_value = EXCLUDED.prop_value;

UPDATE node_properties SET prop_value = '中国电建集团成都勘测设计研究院有限公司', updated_at = NOW()
WHERE node_id = 302 AND prop_key = 'value_text';

UPDATE node_properties SET prop_value = 'true', updated_at = NOW()
WHERE node_id = 302 AND prop_key = 'is_filled';

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(302, 'fill_source', 'rule')
ON CONFLICT (node_id, prop_key) DO UPDATE SET prop_value = EXCLUDED.prop_value;

UPDATE node_properties SET prop_value = '2024年7月13日', updated_at = NOW()
WHERE node_id = 304 AND prop_key = 'value_text';

UPDATE node_properties SET prop_value = 'true', updated_at = NOW()
WHERE node_id = 304 AND prop_key = 'is_filled';

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(304, 'fill_source', 'rule')
ON CONFLICT (node_id, prop_key) DO UPDATE SET prop_value = EXCLUDED.prop_value;

UPDATE node_properties SET prop_value = '2024年10月17日', updated_at = NOW()
WHERE node_id = 305 AND prop_key = 'value_text';

UPDATE node_properties SET prop_value = 'true', updated_at = NOW()
WHERE node_id = 305 AND prop_key = 'is_filled';

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(305, 'fill_source', 'rule')
ON CONFLICT (node_id, prop_key) DO UPDATE SET prop_value = EXCLUDED.prop_value;

UPDATE node_properties SET prop_value = '93.33', updated_at = NOW()
WHERE node_id = 306 AND prop_key = 'value_text';

UPDATE node_properties SET prop_value = 'true', updated_at = NOW()
WHERE node_id = 306 AND prop_key = 'is_filled';

INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(306, 'fill_source', 'rule')
ON CONFLICT (node_id, prop_key) DO UPDATE SET prop_value = EXCLUDED.prop_value;

-- 更新规则执行状态
INSERT INTO node_properties (node_id, prop_key, prop_value) VALUES
(600, 'last_output_text', 'BZ-0092-2024'),
(600, 'last_run_status', 'success'),

(601, 'last_output_text', '中国电建集团成都勘测设计研究院有限公司'),
(601, 'last_run_status', 'success'),

(602, 'last_output_text', '2024年7月13日'),
(602, 'last_run_status', 'success'),

(603, 'last_output_text', '2024年10月17日'),
(603, 'last_run_status', 'success'),

(604, 'last_output_text', '93.33'),
(604, 'last_run_status', 'success')
ON CONFLICT (node_id, prop_key) DO UPDATE SET prop_value = EXCLUDED.prop_value;

-- ============================================================
-- 6. 设置序列起始值
-- ============================================================

SELECT setval('nodes_id_seq', 1000, true);
SELECT setval('edges_id_seq', 2000, true);
SELECT setval('node_properties_id_seq', 3000, true);
SELECT setval('edge_properties_id_seq', 1000, true);

-- ============================================================
-- 7. 初始化管理员用户
-- ============================================================

INSERT INTO sys_users (id, username, password_hash, salt, real_name, status) VALUES
(1, 'admin', '$2a$10$iPQR/hRVyuHsbeawxWdf7u6ka6WmCqGB.a1EnBRfplXaGIHBvRWd6', 'random_salt', '管理员', 'active')
ON CONFLICT (username) DO NOTHING;

INSERT INTO sys_roles (id, role_code, role_name, description) VALUES
(1, 'admin', '超级管理员', '拥有所有权限')
ON CONFLICT (role_code) DO NOTHING;

INSERT INTO sys_user_roles (user_id, role_id) VALUES
(1, 1)
ON CONFLICT (user_id, role_id) DO NOTHING;

-- ============================================================
-- 提交事务
-- ============================================================

COMMIT;

-- ============================================================
-- 验证数据（事务外执行）
-- ============================================================

SELECT '=== 灵越智报 2.0 数据库初始化完成（定稿 v1.0） ===' AS message;
SELECT '节点统计:' AS category, node_type, COUNT(*) AS count FROM nodes GROUP BY node_type ORDER BY node_type;
SELECT '关系统计:' AS category, edge_type, COUNT(*) AS count FROM edges GROUP BY edge_type ORDER BY edge_type;
SELECT '项目视图:' AS category, id, title, element_count, attachment_count, filled_count FROM v_projects;
SELECT '附件视图:' AS category, id, display_name, entity_count FROM v_attachments;
SELECT '实体视图:' AS category, id, entity_text, entity_type, business_label FROM v_entities;
SELECT '规则视图:' AS category, id, rule_name, rule_type, last_run_status FROM v_rules;