/**
 * 清理 P1-Cleanup 后遗留的孤儿表和残留列。
 *
 * 删除内容：
 *   - 孤儿表：teams, team_members（0 行数据）
 *   - sessions 残留列：ext_user_id, ext_display_name（3 行 H5 测试数据）
 *   - workspaces 残留列：team_id, visibility（代码已不使用）
 *   - 依赖残留列的索引：idx_workspaces_team_visibility, idx_sessions_ext_user
 *
 * 用法：
 *   npx tsx scripts/cleanup-legacy-schema.ts          # 执行清理
 *   npx tsx scripts/cleanup-legacy-schema.ts --dry-run # 只打印不执行
 */
import Database from 'better-sqlite3'
import path from 'path'
import os from 'os'
import fs from 'fs'

const dryRun = process.argv.includes('--dry-run')

const dbPath = path.join(os.homedir(), '.forge', 'forge.db')

if (!fs.existsSync(dbPath)) {
  console.log('DB not found at', dbPath)
  process.exit(0)
}

// 自动备份
const backupPath = dbPath + '.backup-' + Date.now()
if (!dryRun) {
  fs.copyFileSync(dbPath, backupPath)
  console.log(`备份: ${backupPath}`)
}

const db = new Database(dbPath)

function exec(sql: string) {
  if (dryRun) {
    console.log(`[DRY-RUN] ${sql}`)
    return
  }
  db.exec(sql)
  console.log(`[OK] ${sql}`)
}

// 1. 删除依赖残留列的索引（DROP COLUMN 前必须先删索引）
exec('DROP INDEX IF EXISTS idx_workspaces_team_visibility')
exec('DROP INDEX IF EXISTS idx_sessions_ext_user')

// 2. 删除孤儿表
exec('DROP TABLE IF EXISTS team_members')
exec('DROP TABLE IF EXISTS teams')

// 3. 删除 sessions 残留列（SQLite 3.35+）
exec('ALTER TABLE sessions DROP COLUMN ext_user_id')
exec('ALTER TABLE sessions DROP COLUMN ext_display_name')

// 4. 删除 workspaces 残留列
exec('ALTER TABLE workspaces DROP COLUMN team_id')
exec('ALTER TABLE workspaces DROP COLUMN visibility')

// 验证
console.log('\n=== 清理后验证 ===')
const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE '_cf_%' ORDER BY name").all() as { name: string }[]
console.log('剩余表数:', tables.length)
console.log('teams/team_members 存在?', tables.some(t => t.name === 'teams' || t.name === 'team_members') ? '⚠️ 仍在' : '✅ 已删除')

const sessionCols = db.prepare('PRAGMA table_info(sessions)').all() as { name: string }[]
console.log('sessions 列:', sessionCols.map(c => c.name).join(', '))

const wsCols = db.prepare('PRAGMA table_info(workspaces)').all() as { name: string }[]
console.log('workspaces 列:', wsCols.map(c => c.name).join(', '))

db.close()
console.log('\n清理完成')