ai-LiaoWangweb-app/migrate.sql

-- ============================================
-- AI LiaoWang Web App 数据库迁移脚本
-- 数据库: PostgreSQL
-- 生成时间: 2026-05-18
-- 说明: 包含所有表的完整建表语句，含 SSO 新增字段
-- ============================================

-- 1. 用户表 (User) — 已新增 SSO 字段
CREATE TABLE IF NOT EXISTS "user" (
    id SERIAL PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    password_hash VARCHAR(256),
    sso_sub VARCHAR(256) UNIQUE,
    real_name VARCHAR(100),
    roles TEXT,
    email VARCHAR(120),
    phone VARCHAR(30),
    avatar_url VARCHAR(500)
);

CREATE INDEX IF NOT EXISTS ix_user_username ON "user" (username);

-- 插入默认管理员账号 (密码: admin)
INSERT INTO "user" (username, password_hash, sso_sub, real_name, roles)
SELECT 'admin',
       'scrypt:32768:8:1$A9gQwDOkl3PLfi5f$fd967bd171fe965bbb1530b036a5003d83ee61b6463dc273479538584ce39c2e778be3e2e8d17b26b8890df31724eda49edb5cd91096ee3a7093c64a135b519e',
       NULL, '管理员', '["super_admin"]'
WHERE NOT EXISTS (SELECT 1 FROM "user" WHERE username = 'admin');

-- 2. 爬虫源表 (SpiderSource)
CREATE TABLE IF NOT EXISTS spider_source (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    code_identifier VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW(),
    type VARCHAR(20) DEFAULT 'script',
    url VARCHAR(500),
    method VARCHAR(10) DEFAULT 'GET',
    headers TEXT,
    params TEXT,
    search_param_key VARCHAR(50) DEFAULT 'q',
    selectors TEXT,
    has_pagination BOOLEAN DEFAULT FALSE,
    pagination_param VARCHAR(50),
    pagination_step INTEGER DEFAULT 10,
    pagination_start INTEGER DEFAULT 0
);

-- 3. 采集任务表 (SpiderTask)
CREATE TABLE IF NOT EXISTS collection_task (
    id SERIAL PRIMARY KEY,
    keyword VARCHAR(100) NOT NULL,
    spider_source_id INTEGER NOT NULL REFERENCES spider_source(id),
    pages INTEGER DEFAULT 1,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    finished_at TIMESTAMP
);

-- 4. 采集结果表 (SpiderResult)
CREATE TABLE IF NOT EXISTS spider_result (
    id SERIAL PRIMARY KEY,
    task_id INTEGER REFERENCES collection_task(id),
    title VARCHAR(500),
    abstract TEXT,
    source VARCHAR(200),
    cover VARCHAR(500),
    link VARCHAR(500),
    published_at VARCHAR(50),
    has_deep_collection BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 5. 深度采集表 (DeepCollection)
CREATE TABLE IF NOT EXISTS deep_collection (
    id SERIAL PRIMARY KEY,
    title VARCHAR(500),
    url VARCHAR(500) NOT NULL UNIQUE,
    content TEXT,
    summary TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    progress INTEGER DEFAULT 0,
    progress_msg VARCHAR(200) DEFAULT '',
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 6. AI 模型表 (AIModel)
CREATE TABLE IF NOT EXISTS ai_model (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    provider VARCHAR(50) DEFAULT 'openai',
    api_base VARCHAR(500) NOT NULL,
    api_key VARCHAR(500) NOT NULL,
    model_name VARCHAR(200) NOT NULL,
    system_prompt TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    total_tokens BIGINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 7. Token 使用日志表 (TokenUsageLog)
CREATE TABLE IF NOT EXISTS token_usage_log (
    id SERIAL PRIMARY KEY,
    model_id INTEGER REFERENCES ai_model(id),
    prompt_tokens INTEGER DEFAULT 0,
    completion_tokens INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    request_type VARCHAR(50) DEFAULT 'test',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 8. AI 对话表 (AIConversation)
CREATE TABLE IF NOT EXISTS ai_conversation (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) DEFAULT 'New Chat',
    user_id INTEGER REFERENCES "user"(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 9. AI 消息表 (AIMessage)
CREATE TABLE IF NOT EXISTS ai_message (
    id SERIAL PRIMARY KEY,
    conversation_id INTEGER NOT NULL REFERENCES ai_conversation(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL,
    content TEXT,
    meta_data TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);