// services/itemGroupClassStore.js
// Admin-configured mapping: SAP Item Group (OITB.ItmsGrpCod) → which Work
// Order table its items should land in — RAW / PACK / COMPONENT. Lets the
// portal replace the hardcoded Solution-name/PK-code heuristics in
// work-order.html with an explicit, editable rule per item group.
const sql = require('mssql');

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

let _conn = null;

async function getConn() {
  if (_conn) return _conn;
  const config = {
    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 },
  };
  _conn = await sql.connect(config);
  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, (match, offset, string) => {
    const paramIndex = (string.slice(0, offset).match(/\?/g) || []).length;
    return `@param${paramIndex}`;
  });
  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('[ITEM-GROUP-CLASS-STORE] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ITMSGRPCOD     INT PRIMARY KEY,
      ITMSGRPNAM     NVARCHAR(100),
      CLASSIFICATION NVARCHAR(20),
      UPDATED_BY     NVARCHAR(50),
      UPDATED_AT     DATETIME2
    )
  `).catch(e => {
    if (isAlreadyExists(e)) { console.log('[ITEM-GROUP-CLASS-STORE] Table exists — OK'); }
    else throw e;
  });
  console.log('[ITEM-GROUP-CLASS-STORE] ✅ Ready');
}

// { ITMSGRPCOD: 'RAW'|'PACK'|'COMPONENT' } for groups that have a classification set
async function getMap() {
  const rows = await exec(`SELECT ITMSGRPCOD, CLASSIFICATION FROM ${TABLE} WHERE CLASSIFICATION IS NOT NULL AND CLASSIFICATION <> ''`);
  const map = {};
  rows.forEach(r => { map[r.ITMSGRPCOD] = r.CLASSIFICATION; });
  return map;
}

async function getAll() {
  const rows = await exec(`SELECT ITMSGRPCOD, ITMSGRPNAM, CLASSIFICATION FROM ${TABLE}`);
  const map = {};
  rows.forEach(r => { map[r.ITMSGRPCOD] = r.CLASSIFICATION || ''; });
  return map;
}

// CLASSIFICATION is stored as a comma-separated set of tags — a group can be
// tagged BOTH 'RAW' and 'PACK' at once (its items then land in BOTH the Raw
// Material and Packing Material tables), or just 'COMPONENT' (mutually
// exclusive with RAW/PACK — the Components table is the "none of the above"
// bucket, so RAW/PACK always win if COMPONENT is also present).
// 'SFG' (Semi-Finished Good) is a separate, orthogonal axis — it marks a
// group's items as SFG PRODUCTS in their own right (used by the Batch
// Issuance SFG workflow to decide which items may be intimated without a
// Requirement), independent of how those same items route when they appear
// as an INGREDIENT inside another product's BOM — so it can coexist with
// RAW/PACK/COMPONENT rather than being mutually exclusive with them.
const VALID_TOKENS = new Set(['RAW', 'PACK', 'COMPONENT', 'SFG']);
function normalizeClassification(raw) {
  const tokens = [...new Set((raw || '').split(',').map(s => s.trim().toUpperCase()).filter(t => VALID_TOKENS.has(t)))];
  if (tokens.includes('COMPONENT') && (tokens.includes('RAW') || tokens.includes('PACK')))
    return tokens.filter(t => t !== 'COMPONENT').join(',');
  return tokens.join(',');
}

async function setClassification(itmsGrpCod, itmsGrpNam, classification, updatedBy) {
  const cls = normalizeClassification(classification);
  const idRows = await exec(`
    MERGE ${TABLE} AS tgt
    USING (SELECT ?  AS ITMSGRPCOD) AS src
    ON tgt.ITMSGRPCOD = src.ITMSGRPCOD
    WHEN MATCHED THEN UPDATE SET
      ITMSGRPNAM = ?, CLASSIFICATION = ?, UPDATED_BY = ?, UPDATED_AT = SYSDATETIME()
    WHEN NOT MATCHED THEN INSERT (ITMSGRPCOD, ITMSGRPNAM, CLASSIFICATION, UPDATED_BY, UPDATED_AT)
      VALUES (?, ?, ?, ?, SYSDATETIME());
    SELECT ? AS ITMSGRPCOD;
  `, [itmsGrpCod, itmsGrpNam || '', cls, updatedBy || '', itmsGrpCod, itmsGrpNam || '', cls, updatedBy || '', itmsGrpCod]);
  return idRows[0]?.ITMSGRPCOD;
}

// SAP Item Group codes tagged 'SFG' — used server-side to verify a Batch
// Issuance SFG intimation's products are actually SFG-classified, rather
// than trusting the client's own tab/mode selection.
async function getSfgGroupCodes() {
  const map = await getMap();
  return Object.keys(map).filter(code => (map[code] || '').split(',').includes('SFG')).map(Number);
}

module.exports = { bootstrap, getMap, getAll, setClassification, getSfgGroupCodes, VALID_TOKENS };