// services/appSqlPool.js
// Shared MSSQL connection pool for the PORTAL'S OWN tables (ZWORK_ORDERS,
// ZCUST_USERS, ZAPPROVAL_STEPS, pl_account_map, cf_items, bs_config, etc.) —
// deliberately a SEPARATE server/database from the SAP B1 SQL Server
// (services/sqlPool.js), so nothing this app creates ever touches the SAP
// database. Same shape/API as sqlPool.js (getPool/query), just pointed at
// APP_SQL_* env vars instead of SQL_*.
'use strict';
const sql = require('mssql');

let _pool       = null;
let _connecting = false;
let _waiters    = [];

function buildConfig() {
  return {
    server:            process.env.APP_SQL_HOST,
    port:              parseInt(process.env.APP_SQL_PORT) || 1433,
    user:              process.env.APP_SQL_USER,
    password:          process.env.APP_SQL_PASSWORD,
    database:          process.env.APP_SQL_DATABASE,
    connectionTimeout: 30000,
    requestTimeout:    60000,
    pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },
    options: {
      encrypt:                true,
      trustServerCertificate: true,
      enableArithAbort:       true,
      cryptoCredentialsDetails: { minVersion: 'TLSv1' },
    },
  };
}

async function getPool() {
  if (_pool && _pool.connected) return _pool;

  if (_connecting) {
    return new Promise((resolve, reject) => _waiters.push({ resolve, reject }));
  }

  _connecting = true;
  try {
    const pool = new sql.ConnectionPool(buildConfig());

    pool.on('error', err => {
      console.error('[APP-SQL-POOL] Pool error — resetting:', err.message);
      _pool = null;
    });

    await pool.connect();
    _pool = pool;
    console.log(`[APP-SQL-POOL] ✅ Connected  ${process.env.APP_SQL_DATABASE} @ ${process.env.APP_SQL_HOST}`);
    _waiters.forEach(w => w.resolve(_pool));
    return _pool;
  } catch (err) {
    _pool = null;
    console.error('[APP-SQL-POOL] ❌ Connection failed:', err.message);
    _waiters.forEach(w => w.reject(err));
    throw err;
  } finally {
    _connecting = false;
    _waiters    = [];
  }
}

async function query(sqlText, params = []) {
  const pool = await getPool();
  const req  = pool.request();
  let i = 0;
  const text = sqlText.replace(/\?/g, () => {
    const name = `p${i}`;
    req.input(name, params[i]);
    i++;
    return `@${name}`;
  });
  const result = await req.query(text);
  return result.recordset || [];
}

module.exports = { getPool, query };