// services/reqCalcGroupStore.js
// Saved "Item Groups" for the Requirement Calculator (public/requirements.html
// Calculator tab) — a user-defined preset list of item codes (e.g. "All Blood
// Bag Items") that can be picked to fetch all its items into the calculator
// worksheet at once, instead of searching and adding items one by one.
// Shared company-wide: every user with Requirements access can see and use
// any group. Only the creator or an admin may edit/delete one.
'use strict';
const sql = require('mssql');

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

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('[REQCALC-GROUPS] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID           INT IDENTITY(1,1) PRIMARY KEY,
      NAME         NVARCHAR(150) NOT NULL,
      ITEMS        NVARCHAR(MAX),
      COMPANY      NVARCHAR(60),
      CREATED_BY   NVARCHAR(50),
      CREATED_NAME NVARCHAR(100),
      CREATED_AT   DATETIME2,
      UPDATED_AT   DATETIME2
    )
  `).catch(e => { if (isAlreadyExists(e)) { console.log('[REQCALC-GROUPS] Table exists — OK'); } else throw e; });
  console.log('[REQCALC-GROUPS] ✅ Ready');
}

function toTs(d) { return d.toISOString().replace('T', ' ').replace('Z', '').substring(0, 23); }
function safeJson(val, fallback) { if (!val) return fallback; try { return JSON.parse(val); } catch { return fallback; } }

function fromRow(row) {
  if (!row) return null;
  return {
    id:            row.ID,
    name:          row.NAME || '',
    items:         safeJson(row.ITEMS, []),   // [{itemCode,itemName}]
    company:       row.COMPANY || '',
    createdBy:     row.CREATED_BY || '',
    createdByName: row.CREATED_NAME || '',
    createdAt:     row.CREATED_AT ? new Date(row.CREATED_AT).toISOString() : null,
    updatedAt:     row.UPDATED_AT ? new Date(row.UPDATED_AT).toISOString() : null,
  };
}

// Shared company-wide — every group for the given company, regardless of creator.
async function listGroups(company) {
  const rows = await exec(
    `SELECT * FROM ${TABLE} ${company ? 'WHERE COMPANY = ?' : ''} ORDER BY NAME ASC`,
    company ? [company] : []
  );
  return rows.map(fromRow);
}

async function getGroup(id) {
  const rows = await exec(`SELECT * FROM ${TABLE} WHERE ID = ?`, [id]);
  return fromRow(rows[0]);
}

async function createGroup(g) {
  const now = toTs(new Date());
  const rows = await exec(
    `INSERT INTO ${TABLE} (NAME, ITEMS, COMPANY, CREATED_BY, CREATED_NAME, CREATED_AT, UPDATED_AT)
     OUTPUT INSERTED.*
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [g.name, JSON.stringify(g.items || []), g.company || '', g.createdBy || '', g.createdByName || '', now, now]
  );
  return fromRow(rows[0]);
}

async function updateGroup(id, g) {
  const now = toTs(new Date());
  const rows = await exec(
    `UPDATE ${TABLE} SET NAME = ?, ITEMS = ?, UPDATED_AT = ? OUTPUT INSERTED.* WHERE ID = ?`,
    [g.name, JSON.stringify(g.items || []), now, id]
  );
  return fromRow(rows[0]);
}

async function deleteGroup(id) {
  await exec(`DELETE FROM ${TABLE} WHERE ID = ?`, [id]);
}

module.exports = { bootstrap, listGroups, getGroup, createGroup, updateGroup, deleteGroup };