/**
 * scripts/db-init.cjs
 * 初始化数据库：建表 + 灌种子（使用 node:sqlite 内置模块）
 * 用法：node scripts/db-init.cjs
 */
const { DatabaseSync } = require('node:sqlite')
const fs = require('fs')
const path = require('path')

const DB_PATH = process.env.DB_PATH || path.join(__dirname, '../data/aipro.db')
fs.mkdirSync(path.dirname(DB_PATH), { recursive: true })

console.log('[db-init] DB:', DB_PATH)
const db = new DatabaseSync(DB_PATH)
db.exec('PRAGMA journal_mode = WAL')
db.exec('PRAGMA foreign_keys = ON')

// 迁移记录表（幂等执行）
db.exec(`CREATE TABLE IF NOT EXISTS _migrations (
  name TEXT PRIMARY KEY,
  applied_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
)`)

// 探测现有 schema，对存量数据库回填 _migrations
function tableExists(name) {
  const r = db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name)
  return !!r
}
function columnExists(table, col) {
  if (!tableExists(table)) return false
  const rows = db.prepare(`PRAGMA table_info(${table})`).all()
  return rows.some(r => r.name === col)
}
const bootstrapMark = db.prepare('INSERT OR IGNORE INTO _migrations (name) VALUES (?)')
if (tableExists('skills')) bootstrapMark.run('0001_init.sql')
if (columnExists('skills', 'download_count')) bootstrapMark.run('0002_skill_stats.sql')
if (tableExists('users')) bootstrapMark.run('0003_users.sql')
if (tableExists('api_keys') && tableExists('skill_invocations')) bootstrapMark.run('0004_skill_api.sql')
if (tableExists('llm_providers')) bootstrapMark.run('0005_llm_providers.sql')
const hasQoder = tableExists('llm_providers')
  ? db.prepare("SELECT 1 FROM llm_providers WHERE code='qoder'").get()
  : null
if (hasQoder) bootstrapMark.run('0006_qoder_provider.sql')
if (columnExists('skills', 'visibility')) bootstrapMark.run('0007_skill_visibility.sql')
const hasZhipu = tableExists('llm_providers')
  ? db.prepare("SELECT 1 FROM llm_providers WHERE code='zhipu'").get()
  : null
if (hasZhipu) bootstrapMark.run('0008_zhipu_provider.sql')
if (tableExists('skill_downloads')) bootstrapMark.run('0009_skill_downloads.sql')
if (tableExists('runtime_engines')) bootstrapMark.run('0010_runtime_engines.sql')
if (columnExists('runtime_engines', 'api_key')) bootstrapMark.run('0011_runtime_engine_api_key.sql')
if (tableExists('system_skills')) bootstrapMark.run('0012_system_skills.sql')
if (tableExists('llm_providers')) {
  const retiredRuntime = db.prepare("SELECT 1 FROM llm_providers WHERE code='qoder' OR protocol='qoder'").get()
  if (!retiredRuntime) bootstrapMark.run('0013_retire_legacy_runtime.sql')
}
if (columnExists('runtime_engines', 'local_command') && tableExists('runtime_agents')) bootstrapMark.run('0014_local_runtime_agents.sql')
if (columnExists('runtime_agents', 'skill_id')) bootstrapMark.run('0015_runtime_agent_skill_refs.sql')
if (tableExists('file_blobs')) bootstrapMark.run('0016_file_blobs.sql')
if (tableExists('runtime_engines')) {
  const hasStudentPresets = db.prepare("SELECT 1 FROM runtime_engines WHERE code='local-custom'").get()
  if (hasStudentPresets) bootstrapMark.run('0017_local_runtime_student_presets.sql')
  const hasQoderCloudAgent = db.prepare("SELECT 1 FROM runtime_engines WHERE code='qoder-cloud-agent'").get()
  if (hasQoderCloudAgent) bootstrapMark.run('0018_qoder_cloud_agent_runtime.sql')
}

const applied = new Set(
  db.prepare('SELECT name FROM _migrations').all().map(r => r.name)
)

// 建表（按顺序执行所有 migration 文件，已应用过的跳过）
const migrations = [
  '0001_init.sql',
  '0002_skill_stats.sql',
  '0003_users.sql',
  '0004_skill_api.sql',
  '0005_llm_providers.sql',
  '0006_qoder_provider.sql',
  '0007_skill_visibility.sql',
  '0008_zhipu_provider.sql',
  '0009_skill_downloads.sql',
  '0010_runtime_engines.sql',
  '0011_runtime_engine_api_key.sql',
  '0012_system_skills.sql',
  '0013_retire_legacy_runtime.sql',
  '0014_local_runtime_agents.sql',
  '0015_runtime_agent_skill_refs.sql',
  '0016_file_blobs.sql',
  '0017_local_runtime_student_presets.sql',
  '0018_qoder_cloud_agent_runtime.sql',
  '0019_direct_skill_create.sql',
  '0020_unified_runtime_streaming.sql',
]
const insertMigration = db.prepare('INSERT INTO _migrations (name) VALUES (?)')
migrations.forEach((file, idx) => {
  const filePath = path.join(__dirname, '../migrations', file)
  if (!fs.existsSync(filePath)) {
    console.log(`[1.${idx + 1}] ${file} 不存在，跳过`)
    return
  }
  if (applied.has(file)) {
    console.log(`[1.${idx + 1}] ${file} 已应用，跳过`)
    return
  }
  console.log(`[1.${idx + 1}] 执行 migrations/${file} ...`)
  const sql = fs.readFileSync(filePath, 'utf8')
  db.exec(sql)
  insertMigration.run(file)
})
console.log('    ✓ 表创建完成')

// 灌种子
const seedPath = path.join(__dirname, '../seed/categories.sql')
if (fs.existsSync(seedPath)) {
  console.log('[2] 灌种子 seed/categories.sql (315 节点) ...')
  const seedSql = fs.readFileSync(seedPath, 'utf8')
  db.exec(seedSql)
  const row = db.prepare('SELECT COUNT(*) as count FROM categories').get()
  console.log(`    ✓ categories 表共 ${row.count} 条`)
} else {
  console.log('[2] seed/categories.sql 不存在，跳过（先跑 node scripts/json-to-sql.cjs）')
}

db.close()
console.log('✅ 数据库初始化完成')
