lingyue-zhibao/backend/sql/init.sql

-- 灵越智报 v2.0 数据库初始化脚本
-- PostgreSQL 15+
-- 注意: ID 使用 VARCHAR(36) 以兼容 MyBatis-Plus 的 ASSIGN_UUID 策略

-- 创建数据库（如果不存在）
-- CREATE DATABASE lingyue_zhibao;

-- 连接到数据库
-- \c lingyue_zhibao;

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- 1. 用户表 (users)
-- ============================================
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    role VARCHAR(20) NOT NULL DEFAULT 'user', -- admin/user/guest
    preferences JSONB DEFAULT '{}',
    last_login_at TIMESTAMP,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL 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);

-- ============================================
-- 2. 文档表 (documents)
-- ============================================
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,
    type VARCHAR(20) NOT NULL, -- pdf/word/image/markdown/other
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending/uploading/parsing/completed/failed
    file_size BIGINT,
    file_url VARCHAR(500),
    thumbnail_url VARCHAR(500),
    parsed_text TEXT,
    parse_status VARCHAR(20), -- pending/parsing/completed/failed
    parse_progress INTEGER DEFAULT 0, -- 0-100
    parse_error TEXT,
    parse_started_at TIMESTAMP,
    parse_completed_at TIMESTAMP,
    metadata JSONB DEFAULT '{}', -- pageCount, ocrConfidence, layoutStructure等
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_documents_user_id ON documents(user_id);
CREATE INDEX IF NOT EXISTS idx_documents_status ON documents(status);
CREATE INDEX IF NOT EXISTS idx_documents_type ON documents(type);
CREATE INDEX IF NOT EXISTS idx_documents_created_at ON documents(create_time DESC);
CREATE INDEX IF NOT EXISTS idx_documents_metadata ON documents USING GIN(metadata);
CREATE INDEX IF NOT EXISTS idx_documents_parsed_text ON documents USING GIN(to_tsvector('english', parsed_text)); -- 全文搜索

-- ============================================
-- 3. 要素表 (elements)
-- ============================================
CREATE TABLE IF NOT EXISTS elements (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(20) NOT NULL, -- amount/company/person/location/date/other
    label VARCHAR(100) NOT NULL,
    value TEXT NOT NULL,
    position JSONB, -- {page, x, y, width, height}
    confidence DECIMAL(3,2), -- 0.00-1.00
    extraction_method VARCHAR(20), -- ai/regex/rule/manual
    graph_node_id VARCHAR(36), -- 关联的图节点ID
    metadata JSONB DEFAULT '{}',
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_elements_document_id ON elements(document_id);
CREATE INDEX IF NOT EXISTS idx_elements_user_id ON elements(user_id);
CREATE INDEX IF NOT EXISTS idx_elements_type ON elements(type);
CREATE INDEX IF NOT EXISTS idx_elements_graph_node_id ON elements(graph_node_id);
CREATE INDEX IF NOT EXISTS idx_elements_position ON elements USING GIN(position);

-- ============================================
-- 4. 批注表 (annotations)
-- ============================================
CREATE TABLE IF NOT EXISTS annotations (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    text TEXT NOT NULL,
    position JSONB NOT NULL, -- {page, start: {x, y}, end: {x, y}}
    type VARCHAR(20) NOT NULL, -- highlight/strikethrough/suggestion
    suggestion TEXT,
    ai_generated BOOLEAN DEFAULT FALSE,
    confidence DECIMAL(3,2),
    status VARCHAR(20) DEFAULT 'pending', -- pending/accepted/rejected
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_annotations_document_id ON annotations(document_id);
CREATE INDEX IF NOT EXISTS idx_annotations_user_id ON annotations(user_id);
CREATE INDEX IF NOT EXISTS idx_annotations_type ON annotations(type);
CREATE INDEX IF NOT EXISTS idx_annotations_status ON annotations(status);

-- ============================================
-- 5. 关系网络表 (graphs)
-- ============================================
CREATE TABLE IF NOT EXISTS graphs (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    nodes JSONB NOT NULL DEFAULT '[]', -- GraphNode数组
    edges JSONB NOT NULL DEFAULT '[]', -- GraphEdge数组
    calculation_result JSONB,
    calculation_status VARCHAR(20), -- pending/completed/failed
    metadata JSONB DEFAULT '{}',
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_graphs_document_id ON graphs(document_id);
CREATE INDEX IF NOT EXISTS idx_graphs_user_id ON graphs(user_id);
CREATE INDEX IF NOT EXISTS idx_graphs_nodes ON graphs USING GIN(nodes);
CREATE INDEX IF NOT EXISTS idx_graphs_edges ON graphs USING GIN(edges);

-- ============================================
-- 6. 解析任务表 (parse_tasks)
-- ============================================
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) NOT NULL DEFAULT 'pending', -- pending/processing/completed/failed
    progress INTEGER DEFAULT 0, -- 0-100
    current_step VARCHAR(100),
    error_message TEXT,
    options JSONB DEFAULT '{}', -- 解析选项
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_parse_tasks_document_id ON parse_tasks(document_id);
CREATE INDEX IF NOT EXISTS idx_parse_tasks_status ON parse_tasks(status);

-- ============================================
-- 7. 会话表 (sessions)
-- ============================================
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 UNIQUE,
    refresh_token_hash VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_token_hash ON sessions(token_hash);
CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);

-- ============================================
-- 创建更新时间触发器函数
-- ============================================
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 update_users_updated_at ON users;
CREATE TRIGGER update_users_update_time BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_documents_updated_at ON documents;
CREATE TRIGGER update_documents_update_time BEFORE UPDATE ON documents
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_elements_updated_at ON elements;
CREATE TRIGGER update_elements_update_time BEFORE UPDATE ON elements
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_annotations_updated_at ON annotations;
CREATE TRIGGER update_annotations_update_time BEFORE UPDATE ON annotations
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_graphs_updated_at ON graphs;
CREATE TRIGGER update_graphs_update_time BEFORE UPDATE ON graphs
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_parse_tasks_updated_at ON parse_tasks;
CREATE TRIGGER update_parse_tasks_update_time BEFORE UPDATE ON parse_tasks
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_sessions_updated_at ON sessions;
CREATE TRIGGER update_sessions_update_time BEFORE UPDATE ON sessions
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();