lingyue-zhibao/0211docs/init_mock.sql

-- ============================================================
-- 灵越智报 2.0 - 数据库初始化 & Mock数据
-- 场景: 成都院-安全生产标准化复审报告
-- 生成时间: 2026-02-12
-- ============================================================

-- 使用事务确保原子性
BEGIN;

-- ============================================================
-- 0. 扩展（可选）
-- ============================================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name = 'vector') THEN
        EXECUTE 'CREATE EXTENSION IF NOT EXISTS vector';
    ELSE
        RAISE NOTICE 'pgvector extension not installed, skip vector features';
    END IF;
END $$;

-- ============================================================
-- 0.x 基础业务表（auth/document/parse/ai/extract）
-- ============================================================

-- 用户与会话
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 TEXT 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 sessions (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255),
    expires_at TIMESTAMP,
    ip_address VARCHAR(100),
    user_agent TEXT,
    last_used_at TIMESTAMP,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);

-- 文档与解析
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);

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 document_blocks (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    parent_id VARCHAR(36),
    children JSONB DEFAULT '[]',
    block_index INT,
    block_type VARCHAR(50),
    text_elements JSONB DEFAULT '[]',
    table_data JSONB,
    image_path VARCHAR(500),
    image_url VARCHAR(500),
    metadata JSONB DEFAULT '{}',
    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_blocks_document ON document_blocks(document_id);

-- AI 相关
CREATE TABLE IF NOT EXISTS elements (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) REFERENCES documents(id) ON DELETE CASCADE,
    user_id VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
    type VARCHAR(50),
    label VARCHAR(200),
    value TEXT,
    position JSONB DEFAULT '{}',
    confidence DECIMAL(10,4),
    extraction_method VARCHAR(50),
    graph_node_id VARCHAR(36),
    metadata JSONB DEFAULT '{}',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_elements_document ON elements(document_id);

CREATE TABLE IF NOT EXISTS annotations (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) REFERENCES documents(id) ON DELETE CASCADE,
    user_id VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
    text TEXT,
    position JSONB DEFAULT '{}',
    type VARCHAR(50),
    suggestion TEXT,
    ai_generated BOOLEAN DEFAULT FALSE,
    confidence DECIMAL(10,4),
    status VARCHAR(20) DEFAULT 'pending',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_annotations_document ON annotations(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 extract_projects (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(50) DEFAULT 'draft',
    config JSONB DEFAULT '{}',
    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_extract_projects_user ON extract_projects(user_id);

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 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 reports (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    project_id VARCHAR(36),
    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),
    source_document_id VARCHAR(36),
    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_status ON reports(status);

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);

-- ============================================================
-- 1. 建表 (DDL)
-- ============================================================

-- 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 UNIQUE,
    type_name      VARCHAR(100) NOT NULL,
    from_node_type VARCHAR(50),
    to_node_type   VARCHAR(50),
    description    TEXT,
    created_at     TIMESTAMP    DEFAULT NOW()
);

-- 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),
    to_node_id    BIGINT       NOT NULL REFERENCES nodes(id),
    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),
    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),
    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()
);

-- ============================================================
-- 1.x 兼容字段与触发器（兼容后端实体字段命名）
-- ============================================================

-- node_properties: 兼容 graph-service 使用 prop_text 字段
ALTER TABLE node_properties ADD COLUMN IF NOT EXISTS prop_text TEXT;

CREATE OR REPLACE FUNCTION sync_node_properties_text() RETURNS trigger AS $$
BEGIN
    IF NEW.prop_text IS NULL AND NEW.prop_value IS NOT NULL THEN
        NEW.prop_text := NEW.prop_value;
    ELSIF NEW.prop_value IS NULL AND NEW.prop_text IS NOT NULL THEN
        NEW.prop_value := NEW.prop_text;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_node_properties_text ON node_properties;
CREATE TRIGGER trg_sync_node_properties_text
BEFORE INSERT OR UPDATE ON node_properties
FOR EACH ROW EXECUTE FUNCTION sync_node_properties_text();

-- edge_properties: 兼容 graph-service 使用 prop_text 字段
ALTER TABLE edge_properties ADD COLUMN IF NOT EXISTS prop_text TEXT;

CREATE OR REPLACE FUNCTION sync_edge_properties_text() RETURNS trigger AS $$
BEGIN
    IF NEW.prop_text IS NULL AND NEW.prop_value IS NOT NULL THEN
        NEW.prop_text := NEW.prop_value;
    ELSIF NEW.prop_value IS NULL AND NEW.prop_text IS NOT NULL THEN
        NEW.prop_value := NEW.prop_text;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_edge_properties_text ON edge_properties;
CREATE TRIGGER trg_sync_edge_properties_text
BEFORE INSERT OR UPDATE ON edge_properties
FOR EACH ROW EXECUTE FUNCTION sync_edge_properties_text();

-- property_definitions: 兼容 graph-service 使用 type_code/is_required 字段
ALTER TABLE property_definitions ADD COLUMN IF NOT EXISTS type_code VARCHAR(50);
ALTER TABLE property_definitions ADD COLUMN IF NOT EXISTS is_required BOOLEAN;

CREATE OR REPLACE FUNCTION sync_property_definitions_fields() RETURNS trigger AS $$
BEGIN
    IF NEW.type_code IS NULL AND NEW.target_type IS NOT NULL THEN
        NEW.type_code := NEW.target_type;
    ELSIF NEW.target_type IS NULL AND NEW.type_code IS NOT NULL THEN
        NEW.target_type := NEW.type_code;
    END IF;

    IF NEW.is_required IS NULL AND NEW.required IS NOT NULL THEN
        NEW.is_required := NEW.required;
    ELSIF NEW.required IS NULL AND NEW.is_required IS NOT NULL THEN
        NEW.required := NEW.is_required;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_property_definitions_fields ON property_definitions;
CREATE TRIGGER trg_sync_property_definitions_fields
BEFORE INSERT OR UPDATE ON property_definitions
FOR EACH ROW EXECUTE FUNCTION sync_property_definitions_fields();

-- ============================================================

-- ============================================================
-- 初始化管理员账号
-- ============================================================
INSERT INTO users (
    id, 
    username, 
    email, 
    password_hash, 
    display_name, 
    role, 
    is_active
) VALUES (
    '1',
    'admin',
    'admin@lingyue.com',
    '$2a$10$0AUCG2mG7a6JXErOTI.Pg.Q/R04plOXvc.TDMeWzwwZQ23ZmrtJxC',
    '管理员',
    'admin',
    true
) ON CONFLICT (username) DO UPDATE SET
    password_hash = EXCLUDED.password_hash,
    email = EXCLUDED.email,
    display_name = EXCLUDED.display_name,
    role = EXCLUDED.role;

-- ============================================================
-- 提交事务
-- ============================================================
COMMIT;

-- ============================================================
-- 验证数据
-- ============================================================
SELECT '数据库初始化完成' as message;
SELECT COUNT(*) as node_count FROM nodes;
SELECT COUNT(*) as template_count FROM templates;
SELECT COUNT(*) as user_count FROM users;