// backend/db/sql.js
const sql = require('mssql');

const config = {
  server: process.env.SQL_HOST,
  port: parseInt(process.env.SQL_PORT),
  user: process.env.SQL_USER,
  password: process.env.SQL_PASSWORD,
  database: process.env.SQL_DATABASE,
  options: {
    encrypt: true,
    trustServerCertificate: true,
  },
};

let pool = null;

async function getConnection() {
  if (pool) return pool;
  pool = await sql.connect(config);
  console.log('✅ Connected to SAP SQL Server');
  return pool;
}

async function query(sqlQuery, params = []) {
  const conn = await getConnection();
  const request = conn.request();
  // Assuming params is an array of values, but mssql uses named params or input()
  // For simplicity, if params are positional, need to adjust
  // But in current code, params are passed as array, so perhaps use input with placeholders
  // For now, assume sqlQuery has ? and params array
  params.forEach((param, index) => {
    request.input(`param${index}`, param);
  });
  const result = await request.query(sqlQuery.replace(/\?/g, (match, offset) => `@param${offset}`));
  return result.recordset;
}

module.exports = { getConnection, query };