// services/bomRequestStore.js
// Stores BOM Create/Update approval requests in SQL: ZBOM_REQUESTS
const sql = require('mssql');
const { DEFAULT_COMPANY } = require('./companyConfig');

const DB_SCHEMA = DEFAULT_COMPANY;
const TABLE     = `[dbo].[ZBOM_REQUESTS]`;
const SEQ       = `[dbo].[ZBOM_REQUESTS_SEQ]`;

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('[BOM-STORE] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID             INT IDENTITY(1,1) PRIMARY KEY,
      TYPE           NVARCHAR(10) NOT NULL,
      ITEM_CODE      NVARCHAR(50) NOT NULL,
      ITEM_NAME      NVARCHAR(200),
      QTY            DECIMAL(18,4) DEFAULT 1,
      BOM_TYPE       NVARCHAR(30) DEFAULT 'Production',
      WAREHOUSE      NVARCHAR(20),
      DISTR_RULE     NVARCHAR(50),
      PROJECT        NVARCHAR(50),
      COMPONENTS     NVARCHAR(MAX),
      ORIGINAL_DATA  NVARCHAR(MAX),
      STATUS         NVARCHAR(30) DEFAULT 'PENDING',
      SUBMITTED_BY   NVARCHAR(50),
      SUBMITTED_NAME NVARCHAR(100),
      SUBMITTED_AT   DATETIME2,
      APPROVAL_LOG   NVARCHAR(MAX),
      REJECTED_BY    NVARCHAR(50),
      REJECTED_AT    DATETIME2,
      SAP_PUSHED_AT  DATETIME2,
      SAP_PUSHED_BY  NVARCHAR(50),
      SAP_RESULT     NVARCHAR(MAX),
      COMPANY        NVARCHAR(60)
    )
  `).catch(e => {
    if (isAlreadyExists(e)) { console.log('[BOM-STORE] Table exists — OK'); }
    else throw e;
  });
  // Migration: add COMPANY column if missing
await exec(`
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME='ZBOM_REQUESTS' AND COLUMN_NAME='COMPANY'
  )
  ALTER TABLE [dbo].[ZBOM_REQUESTS] ADD [COMPANY] NVARCHAR(50) DEFAULT '${DB_SCHEMA}'
`).catch(e => {
  console.log('[BOM-STORE] COMPANY column migration:', e.message);
});
  console.log('[BOM-STORE] ✅ Ready');
}

function toTs(isoStr) {
  if (!isoStr) return null;
  return isoStr.replace('T', ' ').replace('Z', '').substring(0, 23);
}

function fromRow(row) {
  if (!row) return null;
  return {
    id:            row.ID,
    type:          row.TYPE,
    itemCode:      row.ITEM_CODE || '',
    itemName:      row.ITEM_NAME || '',
    qty:           Number(row.QTY) || 1,
    bomType:       row.BOM_TYPE || 'Production',
    warehouse:     row.WAREHOUSE || '',
    distrRule:     row.DISTR_RULE || '',
    project:       row.PROJECT || '',
    components:    safeJson(row.COMPONENTS, []),
    originalData:  safeJson(row.ORIGINAL_DATA, null),
    status:        row.STATUS || 'PENDING',
    submittedBy:   row.SUBMITTED_BY || '',
    submittedByName: row.SUBMITTED_NAME || '',
    submittedAt:   row.SUBMITTED_AT ? new Date(row.SUBMITTED_AT).toISOString() : null,
    approvalLog:   safeJson(row.APPROVAL_LOG, []),
    rejectedBy:    row.REJECTED_BY || null,
    rejectedAt:    row.REJECTED_AT ? new Date(row.REJECTED_AT).toISOString() : null,
    sapPushedAt:   row.SAP_PUSHED_AT ? new Date(row.SAP_PUSHED_AT).toISOString() : null,
    sapPushedBy:   row.SAP_PUSHED_BY || null,
    sapResult:     safeJson(row.SAP_RESULT, null),
    company:       row.COMPANY || '',
  };
}

function safeJson(val, fallback) {
  if (!val) return fallback;
  try { return JSON.parse(val); } catch { return fallback; }
}

async function insertBomRequest(b) {
  const now  = toTs(new Date().toISOString());

  // INSERT and SELECT SCOPE_IDENTITY() must be one batch — as two separate
  // exec() calls, a pooled connection can route the second one to a
  // DIFFERENT physical connection than the one that just inserted, where
  // SCOPE_IDENTITY() correctly returns NULL (see workOrderStore.js's
  // insertWorkOrder() for the full write-up of this bug class).
  const idRows = await exec(`
    INSERT INTO ${TABLE} (
      TYPE, ITEM_CODE, ITEM_NAME, QTY, BOM_TYPE, WAREHOUSE, DISTR_RULE, PROJECT,
      COMPONENTS, ORIGINAL_DATA, STATUS, SUBMITTED_BY, SUBMITTED_NAME, SUBMITTED_AT,
      APPROVAL_LOG, COMPANY
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [
    b.type, b.itemCode, b.itemName || '', Number(b.qty) || 1,
    b.bomType || 'Production', b.warehouse || '', b.distrRule || '', b.project || '',
    JSON.stringify(b.components || []),
    b.originalData ? JSON.stringify(b.originalData) : null,
    'PENDING', b.submittedBy, b.submittedByName || b.submittedBy, now,
    JSON.stringify([]),
    b.company || '',
  ]);
  const id = idRows[0].ID;
  return { id, ...b, status: 'PENDING', submittedAt: new Date().toISOString(), approvalLog: [] };
}

async function listRequests({ status, type, mine, company } = {}) {
  // Fetch all then filter in JS for reliability
  const all = await exec(`SELECT * FROM ${TABLE} ORDER BY SUBMITTED_AT DESC`);
  return all.map(fromRow).filter(r => {
    if (status && status !== 'ALL' && r.status !== status.toUpperCase()) return false;
    if (type && r.type !== type.toUpperCase()) return false;
    if (mine && r.submittedBy !== mine) return false;
    if (company && r.company && r.company !== company) return false;
    return true;
  });
}

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

async function updateRequest(id, patch) {
  const FIELD_MAP = {
    status:       'STATUS',
    approvalLog:  'APPROVAL_LOG',
    rejectedBy:   'REJECTED_BY',
    rejectedAt:   'REJECTED_AT',
    sapPushedAt:  'SAP_PUSHED_AT',
    sapPushedBy:  'SAP_PUSHED_BY',
    sapResult:    'SAP_RESULT',
  };

  function prepareValue(key, val) {
    if (key === 'approvalLog' || key === 'sapResult') return JSON.stringify(val || []);
    if (key === 'rejectedAt' || key === 'sapPushedAt') return toTs(val);
    return val;
  }

  const sets = []; const vals = [];
  for (const [jsKey, colName] of Object.entries(FIELD_MAP)) {
    if (patch[jsKey] !== undefined) {
      sets.push(`${colName} = ?`);
      vals.push(prepareValue(jsKey, patch[jsKey]));
    }
  }
  if (!sets.length) return;
  vals.push(parseInt(id));
  await exec(`UPDATE ${TABLE} SET ${sets.join(', ')} WHERE ID = ?`, vals);
}

async function getStats() {
  const rows = await exec(`
    SELECT STATUS, TYPE, COUNT(*) AS CNT
    FROM ${TABLE}
    GROUP BY STATUS, TYPE
    ORDER BY STATUS, TYPE
  `);
  const stats = { byStatus: {}, byType: { CREATE: 0, UPDATE: 0 }, total: 0 };
  rows.forEach(r => {
    const s = r.STATUS || r.status || r.S;
    const t = r.TYPE || r.type || r.T;
    const c = Number(r.CNT || r.cnt || r.C) || 0;
    stats.byStatus[s] = (stats.byStatus[s] || 0) + c;
    stats.byType[t] = (stats.byType[t] || 0) + c;
    stats.total += c;
  });
  return stats;
}

module.exports = { bootstrap, insertBomRequest, listRequests, findById, updateRequest, getStats };