import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from './db/schema';
import fs from 'node:fs';
import path from 'node:path';

const DB_FILE = process.env.DB_FILE || './data.sqlite';

export const sqlite = new Database(DB_FILE);
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');

export const db = drizzle(sqlite, { schema });

// 启动时按文件名顺序应用 drizzle/*.sql；记录到 _migrations 表
export function applyMigrations(): void {
  const migrationsDir = path.join(process.cwd(), 'drizzle');
  if (!fs.existsSync(migrationsDir)) return;
  sqlite.exec(`CREATE TABLE IF NOT EXISTS _migrations (
    name TEXT PRIMARY KEY,
    applied_at INTEGER NOT NULL
  )`);
  const rows = sqlite.prepare('SELECT name FROM _migrations').all() as { name: string }[];
  const applied = new Set(rows.map((r) => r.name));
  const files = fs.readdirSync(migrationsDir).filter((f) => f.endsWith('.sql')).sort();
  for (const f of files) {
    if (applied.has(f)) continue;
    const sql = fs.readFileSync(path.join(migrationsDir, f), 'utf8');
    try {
      sqlite.exec(sql);
      sqlite.prepare('INSERT INTO _migrations (name, applied_at) VALUES (?, ?)').run(f, Date.now());
      console.log('[migrate] applied', f);
    } catch (err) {
      console.error('[migrate] FAILED', f, err);
      throw err;
    }
  }
}