const sql    = require('mssql');
const logger = require('./utils/logger');

function sqlDriver(authMode) {
  return authMode === 'windows' ? require('mssql/msnodesqlv8') : sql;
}

function buildConfig(overrides = {}) {
  const authMode = String(overrides.authMode || process.env.DB_AUTH || process.env.DB_AUTH_MODE || 'sql').toLowerCase();
  const config = {
    server:   overrides.server   || process.env.DB_SERVER   || 'localhost',
    database: overrides.database || process.env.DB_NAME     || 'softrestaurant11',
    options: {
      encrypt:                false,   
      trustServerCertificate: true,
      enableArithAbort:       true,
      useUTC:                false,   // respetar DATETIME local de SoftRestaurant
      connectTimeout:         parseInt(process.env.DB_CONNECT_TIMEOUT) || 8000,  // Detectar fallo rápido
      requestTimeout:         parseInt(process.env.DB_REQUEST_TIMEOUT) || 30000,
    },
    pool: {
      max:                parseInt(process.env.DB_POOL_MAX)  || 10,
      min:                parseInt(process.env.DB_POOL_MIN)  || 2,   
      idleTimeoutMillis:  parseInt(process.env.DB_POOL_IDLE) || 30000,
      acquireTimeoutMillis: 15000,
    },
  };
  if (authMode === 'windows') {
    config.options.trustedConnection = true;
    config.driver = 'msnodesqlv8';
  } else {
    config.port = parseInt(overrides.port || process.env.DB_PORT) || 1433;
    config.user = overrides.user || process.env.DB_USER || 'sa';
    config.password = overrides.password || process.env.DB_PASSWORD;
  }
  return config;
}

function currentAuthMode() {
  return String(process.env.DB_AUTH || process.env.DB_AUTH_MODE || 'sql').toLowerCase() === 'windows' ? 'windows' : 'sql';
}

let   pool            = null;
let   poolReady       = false;
let   reconnectTimer  = null;
let   healthTimer     = null;
let   reconnectAttempts = 0;
// ROB-2 FIX: promesa compartida para evitar race condition en reconexión concurrente
let   _connectingPromise = null;

const MAX_RECONNECT_ATTEMPTS = parseInt(process.env.DB_MAX_RECONNECT)   || 10;
const HEALTH_CHECK_INTERVAL  = parseInt(process.env.DB_HEALTH_INTERVAL) || 60000; 
const RECONNECT_BASE_DELAY   = 2000;  

const _metrics = {
  totalQueries:   0,
  failedQueries:  0,
  reconnects:     0,
  lastConnected:  null,
  lastError:      null,
  lastHealthCheck: null,
};

function isFatalPoolError(err) {
  const code = String(err?.code || '').toUpperCase();
  const message = String(err?.message || '').toLowerCase();
  if (['ESOCKET', 'ECONNCLOSED', 'ENOTOPEN', 'ELOGIN'].includes(code)) return true;
  return message.includes('connection is closed') ||
    message.includes('connection is closing') ||
    message.includes('connection not yet open') ||
    message === 'aborted';
}

async function connect() {
  try {
    if (pool) {
      try { await pool.close(); } catch (_) {  }
    }

    const config = buildConfig();
    pool      = new (sqlDriver(config.driver === 'msnodesqlv8' ? 'windows' : 'sql')).ConnectionPool(config);
    poolReady = false;

    
    pool.on('error', (err) => {
      if (!isFatalPoolError(err)) {
        logger.warn({ event: 'db_pool_transient_error', error: err.message, code: err.code || null });
        _metrics.lastError = { message: err.message, at: new Date().toISOString(), transient: true };
        return;
      }
      logger.error({ event: 'db_pool_error', error: err.message, code: err.code || null });
      _metrics.lastError = { message: err.message, at: new Date().toISOString() };
      poolReady = false;
      scheduleReconnect();
    });

    await pool.connect();

    const wasReconnecting  = reconnectAttempts > 0;
    poolReady              = true;
    reconnectAttempts      = 0;
    // An on-demand getPool() can recover before a previously scheduled
    // reconnect fires. Cancel that stale timer so it cannot close the new,
    // healthy pool while requests are using it.
    if (reconnectTimer) {
      clearTimeout(reconnectTimer);
      reconnectTimer = null;
    }
    if (wasReconnecting) _metrics.reconnects++;
    _metrics.lastConnected = new Date().toISOString();

    const db = pool.config.database;
    const sv = pool.config.server;
    logger.info(`✅ [DB] Conectado → ${sv}/${db} auth=${config.driver === 'msnodesqlv8' ? 'windows' : 'sql'} driver=${config.driver || 'tedious'}`);

    startHealthCheck();
    return pool;

  } catch (err) {
    logger.error(`[DB] Fallo al conectar auth=${currentAuthMode()}: ${err.message}`);
    _metrics.lastError = { message: err.message, at: new Date().toISOString() };
    poolReady = false;
    scheduleReconnect();
    return null;
  }
}

function scheduleReconnect() {
  if (reconnectTimer) return;

  reconnectAttempts++;

  // FIX-STARTUP: No rendirse nunca al agotar reintentos.
  // Escenario real: el equipo arranca, ARKHON inicia antes de que SQL Server
  // termine de subir (~30-120 s según hardware), agota los 10 intentos rápidos
  // y queda "Running" en el SCM pero sin conexión activa para siempre.
  // Solución: una vez en el cap (intento > MAX), seguir reintentando cada 60 s
  // indefinidamente hasta que SQL Server esté disponible.
  if (reconnectAttempts > MAX_RECONNECT_ATTEMPTS) {
    reconnectAttempts = MAX_RECONNECT_ATTEMPTS; // mantener contador en el cap, no crecer más
    logger.warn(`⏳ [DB] SQL Server sigue sin responder — reintentando cada 60 s hasta que esté disponible...`);
    // NO hacer return — caer al setTimeout de abajo con delay máximo (60 s)
  }

  const delay = Math.min(RECONNECT_BASE_DELAY * Math.pow(2, reconnectAttempts - 1), 60000);
  logger.info(`🔄 [DB] Reintento ${reconnectAttempts}/${MAX_RECONNECT_ATTEMPTS} en ${delay / 1000}s…`);

  reconnectTimer = setTimeout(() => {
    reconnectTimer = null;
    // Usar _connectingPromise para que getPool() pueda awaitar la reconexión
    if (!_connectingPromise) {
      _connectingPromise = connect().finally(() => { _connectingPromise = null; });
    }
  }, delay);
}

function startHealthCheck() {
  if (healthTimer) clearInterval(healthTimer);

  healthTimer = setInterval(async () => {
    try {
      if (!pool || !poolReady) return;
      await pool.request().query('SELECT 1 AS ping');
      _metrics.lastHealthCheck = new Date().toISOString();
    } catch (err) {
      logger.warn('⚠️  [DB] Health check fallido — iniciando reconexión…');
      poolReady = false;
      scheduleReconnect();
    }
  }, HEALTH_CHECK_INTERVAL);

  // FIX-STARTUP: NO llamar healthTimer.unref().
  // .unref() permitiría que Node cerrara el proceso si no hay otros handles activos
  // (ej: conexión fallida + polling pausado por 0 clientes WS). El SCM lo detecta
  // como crash, reinicia el servicio, pero SQL Server tampoco está listo aún →
  // ciclo de crash/restart que nunca termina en conexión exitosa.
}

async function getPool() {
  if (pool && poolReady) return pool;

  // ROB-2 FIX: usar promesa compartida para evitar múltiples connect() simultáneos
  if (!_connectingPromise) {
    _connectingPromise = connect().finally(() => { _connectingPromise = null; });
  }
  await _connectingPromise;

  if (!poolReady) {
    throw new Error('Base de datos no disponible. Reintentando conexión en segundo plano.');
  }

  return pool;
}

function getPoolStats() {
  let poolSize = null;
  if (pool) {
    try {
      poolSize = {
        total:   (pool.pool?.size      ?? 'desconocido'),
        idle:    (pool.pool?.available ?? 'desconocido'),
        pending: (pool.pool?.pending   ?? 'desconocido'),
      };
    } catch (_) {

      poolSize = { total: 'desconocido', idle: 'desconocido', pending: 'desconocido' };
    }
  }
  return {
    connected:        poolReady,
    authMode:         currentAuthMode(),
    driver:           currentAuthMode() === 'windows' ? 'msnodesqlv8' : 'tedious',
    server:           process.env.DB_SERVER || 'localhost',
    database:         process.env.DB_NAME || 'softrestaurant11',
    reconnectAttempts,
    ..._metrics,
    poolSize,
  };
}

async function closePool() {
  if (healthTimer)    clearInterval(healthTimer);
  if (reconnectTimer) clearTimeout(reconnectTimer);
  if (pool) {
    try { await pool.close(); } catch (_) {  }
    pool      = null;
    poolReady = false;
    logger.info('🔌 [DB] Pool cerrado limpiamente.');
  }
}

async function getPoolForTenant(tenantConfig) {
  const config = buildConfig(tenantConfig);
  const tenantPool = new (sqlDriver(config.driver === 'msnodesqlv8' ? 'windows' : 'sql')).ConnectionPool(config);
  await tenantPool.connect();
  // Caller is responsible for calling close() when done:
  // const { pool, close } = await getPoolForTenant(cfg);
  // try { ... } finally { await close(); }
  return { pool: tenantPool, close: () => tenantPool.close() };
}

// Iniciar conexión al cargar el módulo; guardamos la promesa para que getPool()
// la reutilice en lugar de lanzar una segunda conexión concurrente (ROB-2)
_connectingPromise = connect().finally(() => { _connectingPromise = null; });

process.on('SIGTERM', closePool);
process.on('SIGINT',  closePool);
module.exports = {
  getPool,         
  sql,             
  buildConfig,     
  getPoolStats,    
  closePool,       
  getPoolForTenant, 
  isFatalPoolError,
};
