import type { Database } from 'bun:sqlite';
import { mkdirSync, appendFileSync } from 'fs';
import { join } from 'path';
import { migrations, type Migration } from './migrations';
import { toIsoSqlUTC } from '../utils/datetime';

const MIGRATIONS_LOG_LEVEL = (process.env.MIGRATIONS_LOG_LEVEL || '').toLowerCase();
const MIGRATIONS_QUIET = process.env.NODE_ENV === 'test' || MIGRATIONS_LOG_LEVEL === 'silent';

function nowIso(): string {
  return toIsoSqlUTC(new Date());
}

function logEvent(level: 'info' | 'error', event: string, data: any = {}) {
  // En modo test o nivel 'silent', no registrar eventos para evitar ruido
  if (MIGRATIONS_QUIET) return;
  try {
    mkdirSync('data', { recursive: true });
  } catch {}
  try {
    const line = JSON.stringify({ ts: nowIso(), level, event, ...data });
    appendFileSync(join('data', 'migrations.log'), line + '\n');
  } catch {}
}

function ensureMigrationsTable(db: Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      checksum TEXT NOT NULL,
      applied_at TEXT NOT NULL
    );
  `);
}

function getAppliedVersions(db: Database): Map<number, { name: string; checksum: string; applied_at: string }> {
  const rows = db.query(`SELECT version, name, checksum, applied_at FROM schema_migrations ORDER BY version`).all() as Array<{ version: number; name: string; checksum: string; applied_at: string }>;
  const map = new Map<number, { name: string; checksum: string; applied_at: string }>();
  for (const r of rows) {
    map.set(Number(r.version), { name: String(r.name), checksum: String(r.checksum), applied_at: String(r.applied_at) });
  }
  return map;
}

function tableExists(db: Database, table: string): boolean {
  const row = db.query(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`).get(table) as { name?: string } | undefined;
  return !!row;
}

function detectExistingSchema(db: Database): boolean {
  // Consideramos esquema "existente" si ya hay tablas clave
  const coreTables = ['users', 'tasks', 'response_queue'];
  return coreTables.every(t => tableExists(db, t));
}

function insertMigrationRow(db: Database, mig: Migration) {
  db.prepare(
    `INSERT INTO schema_migrations (version, name, checksum, applied_at) VALUES (?, ?, ?, ?)`
  ).run(mig.version, mig.name, mig.checksum, nowIso());
}

function backupDatabaseIfNeeded(db: Database): string | null {
  if (process.env.NODE_ENV === 'test') return null;
  try {
    mkdirSync('data', { recursive: true });
  } catch {}
  const stamp = new Date().toISOString().replace(/[-:T.Z]/g, '').slice(0, 14);
  const backupPath = join('data', `tasks.db.bak-${stamp}.sqlite`);
  try {
    // VACUUM INTO hace copia consistente del estado actual
    db.exec(`VACUUM INTO '${backupPath.replace(/'/g, "''")}'`);
    if (!MIGRATIONS_QUIET) console.log(`ℹ️ Backup de base de datos creado en: ${backupPath}`);
    return backupPath;
  } catch (e) {
    if (!MIGRATIONS_QUIET) console.warn('⚠️ No se pudo crear el backup con VACUUM INTO (continuando de todos modos):', e);
    return null;
  }
}

// ---------------------------------------------------------------------------
// Migration runner helpers
// ---------------------------------------------------------------------------

function validateMigrationsChecksums(
  applied: Map<number, { name: string; checksum: string; applied_at: string }>,
): void {
  const strict = (process.env.MIGRATOR_CHECKSUM_STRICT ?? 'true').toLowerCase() !== 'false';
  for (const [version, info] of applied) {
    const codeMig = migrations.find(m => m.version === version);
    if (!codeMig || codeMig.checksum === info.checksum) continue;

    const msg = `❌ Checksum mismatch en migración v${version}: aplicado=${info.checksum}, código=${codeMig.checksum}`;
    console.error(msg);
    try { logEvent('error', 'checksum_mismatch', { version, applied_checksum: info.checksum, code_checksum: codeMig.checksum }); } catch {}
    if (strict) throw new Error(msg);
  }
}

function logStartupSummary(
  db: Database,
  applied: Map<number, any>,
  pending: Migration[],
): void {
  const jmRow = db.query(`PRAGMA journal_mode`).get() as Record<string, unknown> | undefined;
  const journalMode = jmRow ? String((jmRow['journal_mode'] ?? jmRow['value'] ?? jmRow['mode'] ?? 'unknown')) : 'unknown';
  const currentVersion = applied.size ? Math.max(...Array.from(applied.keys())) : 0;
  if (!MIGRATIONS_QUIET) console.log(`ℹ️ Migrador — journal_mode=${journalMode}, versión_actual=${currentVersion}, pendientes=${pending.length}`);
  try { logEvent('info', 'startup_summary', { journal_mode: journalMode, current_version: currentVersion, pending: pending.length }); } catch {}
}

function maybeApplyBaseline(db: Database): void {
  const v1 = migrations.find(m => m.version === 1)!;
  db.transaction(() => {
    insertMigrationRow(db, v1);
  })();
  if (!MIGRATIONS_QUIET) console.log('ℹ️ Baseline aplicado: schema_migrations marcada en v1 (sin ejecutar up)');
  try { logEvent('info', 'baseline_applied', { version: 1 }); } catch {}
}

function applyMigration(db: Database, mig: Migration): void {
  if (!MIGRATIONS_QUIET) console.log(`➡️ Aplicando migración v${mig.version} - ${mig.name}`);
  try {
    try { logEvent('info', 'apply_start', { version: mig.version, name: mig.name, checksum: mig.checksum }); } catch {}
    const t0 = Date.now();
    db.transaction(() => {
      const res = mig.up(db);
      if (res instanceof Promise) {
        throw new Error('Las migraciones up no deben ser asíncronas en este migrador');
      }
      insertMigrationRow(db, mig);
    })();
    const ms = Date.now() - t0;
    if (!MIGRATIONS_QUIET) console.log(`✅ Migración v${mig.version} aplicada (${ms} ms)`);
    try { logEvent('info', 'apply_success', { version: mig.version, name: mig.name, checksum: mig.checksum, duration_ms: ms }); } catch {}
  } catch (e) {
    console.error(`❌ Error aplicando migración v${mig.version}:`, e);
    try { logEvent('error', 'apply_error', { version: mig.version, name: mig.name, checksum: mig.checksum, error: String(e) }); } catch {}
    throw e;
  }
}

function storeBackupIfNeeded(db: Database, withBackup: boolean): void {
  if (!withBackup) return;
  const backupPath = backupDatabaseIfNeeded(db);
  try { logEvent('info', 'backup', { path: backupPath }); } catch {}
}

// ---------------------------------------------------------------------------
// Migrator object
// ---------------------------------------------------------------------------

export const Migrator = {
  ensureMigrationsTable,
  getAppliedVersions,

  migrateToLatest(db: Database, options?: { withBackup?: boolean; allowBaseline?: boolean }) {
    const withBackup = options?.withBackup !== false;
    const allowBaseline = options?.allowBaseline === true;

    ensureMigrationsTable(db);
    const applied = getAppliedVersions(db);
    let pending = migrations
      .filter(m => !applied.has(m.version))
      .sort((a, b) => a.version - b.version);

    validateMigrationsChecksums(applied);
    logStartupSummary(db, applied, pending);

    if (applied.size === 0 && allowBaseline && detectExistingSchema(db)) {
      maybeApplyBaseline(db);
      pending = migrations.filter(m => m.version > 1);
    }

    if (pending.length === 0) {
      if (!MIGRATIONS_QUIET) console.log('ℹ️ No hay migraciones pendientes');
      try { logEvent('info', 'no_pending', {}); } catch {}
      return;
    }

    storeBackupIfNeeded(db, withBackup);

    for (const mig of pending) {
      applyMigration(db, mig);
    }
  },
};
