import { Database } from 'bun:sqlite';
import { normalizeWhatsAppId } from './utils/whatsapp';
import { mkdirSync } from 'fs';
import { dirname } from 'path';
import { Migrator } from './db/migrator';
import { migrations } from './db/migrations';
import { resolveDbAbsolutePath } from './env/db-path';

function applyDefaultPragmas(instance: Database): void {
  try {
    instance.exec(`PRAGMA busy_timeout = 5000;`);
    // Intentar activar WAL (si no es soportado, SQLite devolverá 'memory' u otro modo)
    instance.query(`PRAGMA journal_mode = WAL`).get();
    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('[db] No se pudieron aplicar PRAGMAs (WAL, busy_timeout...):', e);
  }
}

 // Function to get a database instance. Defaults to 'tmp/tasks.db' in dev and '/app/data/tasks.db' in prod (overridable via DB_PATH/DATA_DIR)
function getDb(filename: string = 'tasks.db'): Database {
  const absolutePath = resolveDbAbsolutePath(filename);

  // Asegurar directorio padre
  try {
    mkdirSync(dirname(absolutePath), { recursive: true });
  } catch (err) {
    const code = (err && typeof err === 'object') ? (err as { code?: string }).code : undefined;
    if (code !== 'EEXIST') throw err;
  }

  const instance = new Database(absolutePath);
  applyDefaultPragmas(instance);
  return instance;
}

// Default export for the main application database
export const db = getDb();

 // Initialize function now accepts a database instance
export function initializeDatabase(instance: Database) {
  // Aplicar PRAGMAs por defecto (WAL, busy_timeout, FK, etc.)
  applyDefaultPragmas(instance);

  // Ejecutar migraciones con el Migrator; si no deja el esquema listo, aplicar fallback.
  let migratorError: unknown = null;
  try {
    Migrator.migrateToLatest(instance, { withBackup: false, allowBaseline: false });
  } catch (e) {
    migratorError = e;
    console.error('[initializeDatabase] Error al aplicar migraciones con Migrator:', e);
  }

  // Verificación mínima: si las tablas base no existen, aplicar fallback secuencial.
  const tableExists = (name: string): boolean => {
    try {
      const row = instance
        .query(`SELECT name FROM sqlite_master WHERE type='table' AND name = ?`)
        .get(name) as { name?: string } | undefined;
      return Boolean(row && row.name === name);
    } catch {
      return false;
    }
  };

  const needsFallback =
    !tableExists('users') ||
    !tableExists('tasks') ||
    !tableExists('response_queue');

  if (needsFallback) {
    console.warn('[initializeDatabase] Migrator no dejó el esquema listo; aplicando fallback de migraciones secuenciales');
    try {
      instance.transaction(() => {
        try { instance.exec(`PRAGMA foreign_keys = ON;`); } catch {}
        for (const m of migrations) {
          m.up(instance);
        }
      })();
    } catch (fallbackErr) {
      console.error('[initializeDatabase] Fallback de migraciones falló:', fallbackErr);
      throw fallbackErr;
    }
  } else if (migratorError) {
    // Si el Migrator falló pero el esquema ya está correcto, sólo loggeamos.
    console.warn('[initializeDatabase] Migrator reportó error, pero el esquema parece estar correcto. Continuando.');
  }
}

/**
 * Ensures a user exists in the database based on their raw WhatsApp ID.
 * If the user exists, updates their last_seen timestamp.
 * If the user does not exist, creates them.
 * Uses the normalizeWhatsAppId utility.
 * Stores timestamps with millisecond precision.
 * 
 * @param rawUserId The raw WhatsApp ID (e.g., '12345@s.whatsapp.net').
 * @param instance The database instance to use (defaults to the main db).
 * @returns The normalized user ID if successful, otherwise null.
 */
export function ensureUserExists(rawUserId: string | null | undefined, instance: Database = db): string | null {
  const normalizedId = normalizeWhatsAppId(rawUserId);

  if (!normalizedId) {
    console.error(`[ensureUserExists] Could not normalize or invalid user ID provided: ${rawUserId}`);
    return null;
  }

  try {
    // Use strftime for millisecond precision timestamps
    const insertStmt = instance.prepare(`
      INSERT INTO users (id, first_seen, last_seen) 
      VALUES (?, strftime('%Y-%m-%d %H:%M:%f', 'now'), strftime('%Y-%m-%d %H:%M:%f', 'now'))
      ON CONFLICT(id) DO NOTHING;
    `);
    
    const updateStmt = instance.prepare(`
      UPDATE users 
      SET last_seen = strftime('%Y-%m-%d %H:%M:%f', 'now')
      WHERE id = ?;
    `);

    // Run as transaction for atomicity
    instance.transaction(() => {
      insertStmt.run(normalizedId);
      // Update last_seen even if the user was just inserted or already existed
      updateStmt.run(normalizedId); 
    })(); // Immediately invoke the transaction

    return normalizedId;

  } catch (error) {
    console.error(`[ensureUserExists] Database error for user ID ${normalizedId}:`, error);
    return null;
  }
}
