AIclinicalresearch/docs/09-架构实施/migration-scripts/003-migrate-aia.sql

-- ========================================
-- 003-migrate-aia.sql
-- ========================================
-- 目的：迁移aia_schema（AI智能问答模块）
-- 迁移表：5个（projects, conversations, messages, general_conversations, general_messages）
-- 预计时间：30分钟
-- 作者：AI助手
-- 日期：2025-11-09
-- ========================================

-- 前置条件：
-- 1. 已执行 001-create-all-10-schemas.sql
-- 2. 已执行 002-migrate-platform.sql（因为需要引用platform_schema.users）
-- 3. public schema中的相关表存在且有数据

BEGIN;

-- ========================================
-- 第一步：创建aia_schema.projects表
-- ========================================

CREATE TABLE IF NOT EXISTS aia_schema.projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    background TEXT DEFAULT '',
    research_type VARCHAR(50) DEFAULT 'observational',
    conversation_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES platform_schema.users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_aia_projects_user_id ON aia_schema.projects(user_id);
CREATE INDEX IF NOT EXISTS idx_aia_projects_created_at ON aia_schema.projects(created_at);
CREATE INDEX IF NOT EXISTS idx_aia_projects_deleted_at ON aia_schema.projects(deleted_at);

-- ========================================
-- 第二步：创建aia_schema.conversations表
-- ========================================

CREATE TABLE IF NOT EXISTS aia_schema.conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    project_id UUID,
    agent_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    model_name VARCHAR(50) DEFAULT 'deepseek-v3',
    message_count INT DEFAULT 0,
    total_tokens INT DEFAULT 0,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES platform_schema.users(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES aia_schema.projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_aia_conversations_user_id ON aia_schema.conversations(user_id);
CREATE INDEX IF NOT EXISTS idx_aia_conversations_project_id ON aia_schema.conversations(project_id);
CREATE INDEX IF NOT EXISTS idx_aia_conversations_agent_id ON aia_schema.conversations(agent_id);
CREATE INDEX IF NOT EXISTS idx_aia_conversations_created_at ON aia_schema.conversations(created_at);
CREATE INDEX IF NOT EXISTS idx_aia_conversations_deleted_at ON aia_schema.conversations(deleted_at);

-- ========================================
-- 第三步：创建aia_schema.messages表
-- ========================================

CREATE TABLE IF NOT EXISTS aia_schema.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL,
    role VARCHAR(20) NOT NULL,
    content TEXT NOT NULL,
    model VARCHAR(50),
    metadata JSONB,
    tokens INT,
    is_pinned BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (conversation_id) REFERENCES aia_schema.conversations(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_aia_messages_conversation_id ON aia_schema.messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_aia_messages_created_at ON aia_schema.messages(created_at);
CREATE INDEX IF NOT EXISTS idx_aia_messages_is_pinned ON aia_schema.messages(is_pinned);

-- ========================================
-- 第四步：创建aia_schema.general_conversations表
-- ========================================

CREATE TABLE IF NOT EXISTS aia_schema.general_conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    title VARCHAR(255) NOT NULL,
    model_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES platform_schema.users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_aia_general_conversations_user_id ON aia_schema.general_conversations(user_id);
CREATE INDEX IF NOT EXISTS idx_aia_general_conversations_created_at ON aia_schema.general_conversations(created_at);
CREATE INDEX IF NOT EXISTS idx_aia_general_conversations_updated_at ON aia_schema.general_conversations(updated_at);

-- ========================================
-- 第五步：创建aia_schema.general_messages表
-- ========================================

CREATE TABLE IF NOT EXISTS aia_schema.general_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL,
    role VARCHAR(20) NOT NULL,
    content TEXT NOT NULL,
    model VARCHAR(50),
    metadata JSONB,
    tokens INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (conversation_id) REFERENCES aia_schema.general_conversations(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_aia_general_messages_conversation_id ON aia_schema.general_messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_aia_general_messages_created_at ON aia_schema.general_messages(created_at);

-- ========================================
-- 第六步：迁移数据
-- ========================================

-- 6.1 迁移projects
INSERT INTO aia_schema.projects (
    id, user_id, name, background, research_type,
    conversation_count, created_at, updated_at, deleted_at
)
SELECT 
    id, user_id, name, background, research_type,
    conversation_count, created_at, updated_at, deleted_at
FROM public.projects
ON CONFLICT (id) DO NOTHING;

-- 6.2 迁移conversations
INSERT INTO aia_schema.conversations (
    id, user_id, project_id, agent_id, title,
    model_name, message_count, total_tokens, metadata,
    created_at, updated_at, deleted_at
)
SELECT 
    id, user_id, project_id, agent_id, title,
    model_name, message_count, total_tokens, metadata,
    created_at, updated_at, deleted_at
FROM public.conversations
ON CONFLICT (id) DO NOTHING;

-- 6.3 迁移messages
INSERT INTO aia_schema.messages (
    id, conversation_id, role, content,
    model, metadata, tokens, is_pinned, created_at
)
SELECT 
    id, conversation_id, role, content,
    model, metadata, tokens, is_pinned, created_at
FROM public.messages
ON CONFLICT (id) DO NOTHING;

-- 6.4 迁移general_conversations
INSERT INTO aia_schema.general_conversations (
    id, user_id, title, model_name,
    created_at, updated_at, deleted_at
)
SELECT 
    id, user_id, title, model_name,
    created_at, updated_at, deleted_at
FROM public.general_conversations
ON CONFLICT (id) DO NOTHING;

-- 6.5 迁移general_messages
INSERT INTO aia_schema.general_messages (
    id, conversation_id, role, content,
    model, metadata, tokens, created_at
)
SELECT 
    id, conversation_id, role, content,
    model, metadata, tokens, created_at
FROM public.general_messages
ON CONFLICT (id) DO NOTHING;

-- ========================================
-- 第七步：数据验证
-- ========================================

DO $$
DECLARE
    public_projects INTEGER;
    public_conversations INTEGER;
    public_messages INTEGER;
    public_general_conversations INTEGER;
    public_general_messages INTEGER;
    
    aia_projects INTEGER;
    aia_conversations INTEGER;
    aia_messages INTEGER;
    aia_general_conversations INTEGER;
    aia_general_messages INTEGER;
    
    all_match BOOLEAN := true;
BEGIN
    -- 统计原表
    SELECT COUNT(*) INTO public_projects FROM public.projects;
    SELECT COUNT(*) INTO public_conversations FROM public.conversations;
    SELECT COUNT(*) INTO public_messages FROM public.messages;
    SELECT COUNT(*) INTO public_general_conversations FROM public.general_conversations;
    SELECT COUNT(*) INTO public_general_messages FROM public.general_messages;
    
    -- 统计新表
    SELECT COUNT(*) INTO aia_projects FROM aia_schema.projects;
    SELECT COUNT(*) INTO aia_conversations FROM aia_schema.conversations;
    SELECT COUNT(*) INTO aia_messages FROM aia_schema.messages;
    SELECT COUNT(*) INTO aia_general_conversations FROM aia_schema.general_conversations;
    SELECT COUNT(*) INTO aia_general_messages FROM aia_schema.general_messages;
    
    -- 输出统计
    RAISE NOTICE '==================== 数据迁移统计 ====================';
    RAISE NOTICE 'projects:              public.% -> aia_schema.%', public_projects, aia_projects;
    RAISE NOTICE 'conversations:         public.% -> aia_schema.%', public_conversations, aia_conversations;
    RAISE NOTICE 'messages:              public.% -> aia_schema.%', public_messages, aia_messages;
    RAISE NOTICE 'general_conversations: public.% -> aia_schema.%', public_general_conversations, aia_general_conversations;
    RAISE NOTICE 'general_messages:      public.% -> aia_schema.%', public_general_messages, aia_general_messages;
    RAISE NOTICE '=====================================================';
    
    -- 验证每个表
    IF public_projects != aia_projects THEN
        RAISE WARNING '⚠️ projects 数据量不一致';
        all_match := false;
    END IF;
    
    IF public_conversations != aia_conversations THEN
        RAISE WARNING '⚠️ conversations 数据量不一致';
        all_match := false;
    END IF;
    
    IF public_messages != aia_messages THEN
        RAISE WARNING '⚠️ messages 数据量不一致';
        all_match := false;
    END IF;
    
    IF public_general_conversations != aia_general_conversations THEN
        RAISE WARNING '⚠️ general_conversations 数据量不一致';
        all_match := false;
    END IF;
    
    IF public_general_messages != aia_general_messages THEN
        RAISE WARNING '⚠️ general_messages 数据量不一致';
        all_match := false;
    END IF;
    
    IF all_match THEN
        RAISE NOTICE '✅ AIA Schema 所有表数据迁移成功！';
    ELSE
        RAISE WARNING '⚠️ 部分表数据迁移存在问题，请检查';
    END IF;
END $$;

-- ========================================
-- 第八步：外键完整性验证
-- ========================================

-- 验证所有project的user_id都存在于platform_schema.users中
DO $$
DECLARE
    invalid_users INTEGER;
BEGIN
    SELECT COUNT(*) INTO invalid_users
    FROM aia_schema.projects p
    LEFT JOIN platform_schema.users u ON p.user_id = u.id
    WHERE u.id IS NULL;
    
    IF invalid_users > 0 THEN
        RAISE WARNING '⚠️ projects表中有 % 条记录的user_id无效', invalid_users;
    ELSE
        RAISE NOTICE '✅ projects表外键完整性验证通过';
    END IF;
END $$;

COMMIT;

-- ========================================
-- 执行结果统计（可单独运行）
-- ========================================

SELECT 
    'aia_schema' AS schema_name,
    'projects' AS table_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT user_id) AS unique_users,
    MIN(created_at) AS earliest_record,
    MAX(created_at) AS latest_record
FROM aia_schema.projects

UNION ALL

SELECT 
    'aia_schema',
    'conversations',
    COUNT(*),
    COUNT(DISTINCT user_id),
    MIN(created_at),
    MAX(created_at)
FROM aia_schema.conversations

UNION ALL

SELECT 
    'aia_schema',
    'messages',
    COUNT(*),
    NULL,
    MIN(created_at),
    MAX(created_at)
FROM aia_schema.messages;

-- ========================================
-- 完成提示
-- ========================================
-- ✅ AIA Schema 迁移完成
-- 包含5个表：projects, conversations, messages, 
--           general_conversations, general_messages
-- 下一步：执行 004-migrate-pkb.sql
-- ========================================