const { Pool } = require('pg');
require('dotenv').config();

const pools = {};

function getPool(dbName) {
  const database = dbName || process.env.PG_DATABASE || 'an_omon_full_04052026';
  if (!pools[database]) {
    pools[database] = new Pool({
      host: process.env.PG_HOST || '192.168.50.79',
      port: parseInt(process.env.PG_PORT || '5437', 10),
      user: process.env.PG_USER || 'postgres',
      password: process.env.PG_PASSWORD || '123',
      database: database,
      max: 10,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 5000,
      statement_timeout: 15000, // 15s timeout to prevent hanging queries
    });

    pools[database].on('error', (err) => {
      console.error(`Unexpected error on idle client for db ${database}`, err);
    });
  }
  return pools[database];
}

async function query(sql, params = [], dbName = null) {
  const pool = getPool(dbName);
  const start = Date.now();
  const res = await pool.query(sql, params);
  const duration = Date.now() - start;
  // console.log(`[SQL ${duration}ms] executed on ${dbName || 'default'}`);
  return res;
}

// Clean RTF string helper
function cleanRtf(text) {
  if (!text) return '';
  if (!text.includes('{\\rtf')) return text.trim();
  // Strip RTF control codes
  let clean = text
    .replace(/\\par[d]?/g, '\n')
    .replace(/\\u([0-9]{3,5})\?/g, (match, dec) => String.fromCharCode(dec))
    .replace(/\\'([0-9a-fA-F]{2})/g, (match, hex) => String.fromCharCode(parseInt(hex, 16)))
    .replace(/\{[^{}]*\}/g, '')
    .replace(/\\[a-zA-Z0-9\-]+/g, '')
    .replace(/[\{\}]/g, '')
    .trim();
  return clean;
}

module.exports = {
  getPool,
  query,
  cleanRtf,
};