import { mkdirSync } from 'fs';
import { dirname } from 'path';
import { resolveDbAbsolutePath, isDev, DEV_AUTOSEED_DB, DEV_DEFAULT_USER } from './env';

function applyDefaultPragmas(instance: any): void {
  try {
    instance.exec(`PRAGMA busy_timeout = 5000;`);
    // Intentar activar WAL (si no es soportado, SQLite devolverá 'memory' u otro modo)
    try {
      if (typeof instance.query === 'function') {
        instance.query(`PRAGMA journal_mode = WAL`)?.get?.();
      } else {
        instance.prepare?.(`PRAGMA journal_mode = WAL`)?.get?.();
      }
    } catch {}
    instance.exec(`PRAGMA synchronous = NORMAL;`);
    instance.exec(`PRAGMA wal_autocheckpoint = 1000;`);
    // Asegurar claves foráneas siempre activas
    instance.exec(`PRAGMA foreign_keys = ON;`);
  } catch (e) {
    console.warn('[web/db] No se pudieron aplicar PRAGMAs (WAL, busy_timeout...):', e);
  }
}

/**
 * Intenta cargar un constructor de Database compatible:
 * - En Bun (SSR nativo): bun:sqlite
 * - En Node (Vite dev SSR): better-sqlite3
 */
async function importSqliteDatabase(): Promise<any> {
  // En tests, forzar bun:sqlite para evitar mezclar engines con la conexión de tests
  const nodeEnv = String(process.env.NODE_ENV || '').toLowerCase();
  if (nodeEnv === 'test') {
    const mod: any = await import('bun:sqlite');
    return (mod as any).Database || (mod as any).default || mod;
  }

  // En desarrollo (Vite SSR), cargar better-sqlite3 vía require de Node para mantener el contexto CJS
  if (typeof import.meta !== 'undefined' && (import.meta as any).env?.DEV) {
    const modModule: any = await import('node:module');
    const require = modModule.createRequire(import.meta.url);
    const mod = require('better-sqlite3');
    return (mod as any).default || (mod as any).Database || mod;
  }

  // En producción (Bun en runtime), usar bun:sqlite nativo
  const mod: any = await import('bun:sqlite');
  return (mod as any).Database || (mod as any).default || mod;
}

// ---------------------------------------------------------------------------
// DB open helpers
// ---------------------------------------------------------------------------

function ensureParentDir(absolutePath: string): void {
  try {
    mkdirSync(dirname(absolutePath), { recursive: true });
  } catch (err: any) {
    if (err?.code !== 'EEXIST') throw err;
  }
}

async function createSqliteInstance(absolutePath: string): Promise<any> {
  const DatabaseCtor = await importSqliteDatabase();
  const instance = new DatabaseCtor(absolutePath);
  applyDefaultPragmas(instance);
  return instance;
}

function hasTable(instance: any, table: string): boolean {
  try {
    instance.prepare(`SELECT 1 FROM ${table} LIMIT 1`).get();
    return true;
  } catch {
    return false;
  }
}

async function runDevMigrationsBun(instance: any): Promise<void> {
  try {
    const dbModule = await import('../../../../../src/db');
    if (typeof (dbModule as any).initializeDatabase === 'function') {
      (dbModule as any).initializeDatabase(instance);
      console.info('[web/db] DEV: esquema inicializado (Bun initializeDatabase).');
    }
  } catch (e) {
    console.warn('[web/db] No se pudo ejecutar initializeDatabase en dev (Bun):', e);
  }
}

async function runDevMigrationsNode(instance: any): Promise<void> {
  try {
    const mod = await import('../../../../../src/db/migrations/index');
    const list = (mod as any).migrations as any[];
    const compat: any = instance;
    if (typeof compat.query !== 'function') {
      compat.query = (sql: string) => ({
        all: () => compat.prepare(sql).all(),
        get: () => compat.prepare(sql).get(),
      });
    }
    try { compat.exec?.('PRAGMA foreign_keys = ON;'); } catch {}
    for (const m of list) {
      try {
        await (m.up as any)(compat);
      } catch (e) {
        console.warn('[web/db] Error aplicando migración en dev (Node):', (m as any)?.name ?? '(sin nombre)', e);
      }
    }
    try {
      compat.prepare('SELECT 1 FROM tasks LIMIT 1').get();
      console.info('[web/db] DEV: esquema inicializado (migraciones aplicadas en Node).');
    } catch {}
  } catch (e) {
    console.warn('[web/db] No se pudieron aplicar migraciones en dev (Node):', e);
  }
}

async function initializeDevSchema(instance: any): Promise<void> {
  if (hasTable(instance, 'tasks')) return;

  const isBun = typeof (globalThis as any).Bun !== 'undefined';
  if (isBun) {
    await runDevMigrationsBun(instance);
  } else {
    await runDevMigrationsNode(instance);
  }
}

async function seedDevData(instance: any): Promise<void> {
  try {
    let count = 0;
    try {
      const row = instance.prepare('SELECT COUNT(1) AS c FROM tasks').get() as any;
      count = Number(row?.c ?? 0);
    } catch {
      return; // table doesn't exist yet — skip seeding
    }

    const shouldSeed = typeof DEV_AUTOSEED_DB === 'boolean' ? DEV_AUTOSEED_DB : true;
    if (count !== 0 || !shouldSeed) {
      console.info(`[web/db] DEV: seed no aplicado (count=${count}, DEV_AUTOSEED_DB=${shouldSeed}).`);
      return;
    }

    console.info('[web/db] DEV: tabla tasks vacía; iniciando seed de demo...');
    try {
      const seed = await import('./dev-seed');
      if (typeof (seed as any).seedDev === 'function') {
        await (seed as any).seedDev(instance, DEV_DEFAULT_USER);
        console.info('[web/db] DEV: seed de demo completado.');
      } else {
        console.warn('[web/db] DEV: módulo dev-seed sin función seedDev; omitiendo seed.');
      }
    } catch (e) {
      console.warn('[web/db] DEV: no se pudo cargar/ejecutar dev-seed; omitiendo seed. Error:', e);
    }
  } catch (e) {
    console.warn('[web/db] DEV: error al evaluar seed de demo:', e);
  }
}

// ---------------------------------------------------------------------------
// DB open
// ---------------------------------------------------------------------------

/**
 * Abre la BD compartida. En desarrollo, si el archivo no existe y DEV_AUTOSEED_DB=true,
 * inicializa el esquema (migraciones) y siembra datos de demo.
 * Nota: usa bun:sqlite si está disponible; en SSR Node usa better-sqlite3.
 */
async function openDb(filename: string = 'tasks.db'): Promise<any> {
  const absolutePath = resolveDbAbsolutePath(filename);

  ensureParentDir(absolutePath);
  const instance = await createSqliteInstance(absolutePath);

  if (isDev()) {
    await initializeDevSchema(instance);
    await seedDevData(instance);
  }

  return instance;
}

let _db: any | null = null;
let _dbPath: string | null = null;

/**
 * Devuelve una única instancia compartida (lazy) de la BD.
 * Si cambia el path, cierra la anterior y abre una nueva conexión.
 */
export async function getDb(filename: string = 'tasks.db'): Promise<any> {
  const resolved = resolveDbAbsolutePath(filename);
  if (_db && _dbPath === resolved) return _db;
  // Path changed or first call — close previous and reconnect
  try {
    if (_db && typeof _db.close === 'function') _db.close();
  } catch {}
  _db = await openDb(filename);
  _dbPath = resolved;
  return _db;
}
