-- PostgreSQL 迁移失败清理 SQL 脚本
-- 用于清理 AnythingLLM 项目中的失败迁移记录

-- 1. 首先检查当前迁移状态
\echo '当前迁移状态:'
SELECT 
    migration_name,
    started_at,
    finished_at,
    CASE 
        WHEN finished_at IS NULL THEN 'FAILED'
        ELSE 'COMPLETED'
    END as status,
    logs
FROM _prisma_migrations 
ORDER BY started_at DESC;

-- 2. 创建备份（可选但推荐）
\echo '创建备份表...'
CREATE TABLE IF NOT EXISTS _prisma_migrations_backup_20250927 AS 
SELECT * FROM _prisma_migrations 
WHERE finished_at IS NULL OR logs LIKE '%P3009%';

-- 3. 显示将要删除的记录
\echo '将要删除的失败迁移:'
SELECT migration_name, started_at 
FROM _prisma_migrations 
WHERE finished_at IS NULL OR logs LIKE '%P3009%';

-- 4. 执行删除（请确认后再执行）
-- 删除特定失败的迁移（20230921191814_init）
\echo '删除特定失败迁移...'
DELETE FROM _prisma_migrations 
WHERE migration_name = '20230921191814_init'
AND (finished_at IS NULL OR logs LIKE '%P3009%');

-- 或者删除所有失败的迁移
-- DELETE FROM _prisma_migrations WHERE finished_at IS NULL;

-- 5. 验证清理结果
\echo '清理后的迁移状态:'
SELECT 
    migration_name,
    started_at,
    finished_at,
    CASE 
        WHEN finished_at IS NULL THEN 'FAILED'
        ELSE 'COMPLETED'
    END as status
FROM _prisma_migrations 
ORDER BY started_at DESC;

-- 6. 检查表是否存在
\echo '检查业务表是否存在:'
SELECT EXISTS (
    SELECT FROM information_schema.tables 
    WHERE table_schema = 'public' 
    AND table_name = 'users'
) as users_table_exists,
EXISTS (
    SELECT FROM information_schema.tables 
    WHERE table_schema = 'public' 
    AND table_name = 'workspaces'
) as workspaces_table_exists;

-- 使用说明：
-- 1. 连接到数据库：psql -h rag-ai.e.aivencloud.com -p 15005 -U [用户名] -d [数据库名]
-- 2. 运行此脚本：\i cleanup-migrations.sql
-- 3. 清理完成后，可以重新运行迁移：npx prisma migrate dev