// services/warehouseTranTypeStore.js
// Admin-managed mapping of Warehouse → Transaction Type (Complete='C' /
// Reject='R') for Receipt from Production. When a warehouse is mapped, the
// receipt page shows its transaction type automatically (the user can't pick
// it). Unmapped warehouses default to Complete. Company-scoped. App DB only.
'use strict';
const sql = require('mssql');

const TABLE = `[dbo].[ZWAREHOUSE_TRANTYPE]`;

let _conn = null;
async function getConn() {
  if (_conn) return _conn;
  _conn = await sql.connect({
    server: process.env.APP_SQL_HOST,
    port: parseInt(process.env.APP_SQL_PORT),
    user: process.env.APP_SQL_USER,
    password: process.env.APP_SQL_PASSWORD,
    database: process.env.APP_SQL_DATABASE,
    options: { encrypt: true, trustServerCertificate: true },
  });
  return _conn;
}
async function exec(sqlQuery, params = []) {
  const conn = await getConn();
  const request = conn.request();
  params.forEach((param, index) => { request.input(`param${index}`, param); });
  const replacedSql = sqlQuery.replace(/\?/g, (m, offset, string) => {
    const i = (string.slice(0, offset).match(/\?/g) || []).length;
    return `@param${i}`;
  });
  const result = await request.query(replacedSql);
  return result.recordset || [];
}
function isAlreadyExists(e) {
  const m = (e.message || '').toLowerCase();
  return m.includes('already exists') || m.includes('duplicate') || m.includes('existing object') || m.includes('there is already an object');
}

async function bootstrap() {
  console.log('[WH-TRANTYPE-STORE] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID         INT IDENTITY(1,1) PRIMARY KEY,
      COMPANY    NVARCHAR(60) NOT NULL,
      WHS_CODE   NVARCHAR(20) NOT NULL,
      TRAN_TYPE  CHAR(1)      NOT NULL,   -- 'C' Complete | 'R' Reject
      UPDATED_AT DATETIME2
    )
  `).catch(e => { if (isAlreadyExists(e)) console.log('[WH-TRANTYPE-STORE] Table exists — OK'); else throw e; });
  await exec(`CREATE UNIQUE INDEX IDX_ZWH_TT_UN ON ${TABLE} ([COMPANY],[WHS_CODE])`).catch(() => {});
  console.log('[WH-TRANTYPE-STORE] ✅ Ready');
}

// { whCode: 'C'|'R', ... } for a company
async function getMap(company) {
  const rows = await exec(`SELECT WHS_CODE, TRAN_TYPE FROM ${TABLE} WHERE COMPANY = ?`, [company || '']);
  const map = {};
  rows.forEach(r => { map[r.WHS_CODE] = (r.TRAN_TYPE === 'R' ? 'R' : 'C'); });
  return map;
}

// Replace the whole mapping for a company with the given { whCode: 'C'|'R' }.
async function setMap(company, map) {
  const co = company || '';
  const now = new Date().toISOString().replace('T', ' ').replace('Z', '').substring(0, 23);
  await exec(`DELETE FROM ${TABLE} WHERE COMPANY = ?`, [co]);
  const entries = Object.entries(map || {}).filter(([wh, t]) => wh && (t === 'C' || t === 'R'));
  for (const [wh, t] of entries) {
    await exec(`INSERT INTO ${TABLE} (COMPANY, WHS_CODE, TRAN_TYPE, UPDATED_AT) VALUES (?, ?, ?, ?)`,
      [co, String(wh).trim(), t, now]);
  }
  return { count: entries.length };
}

module.exports = { bootstrap, getMap, setMap };