// backend/services/hanaItemStore.js
// SQL persistence for item creation approval requests.
'use strict';
const { getPool } = require('./appSqlPool');
const { DEFAULT_COMPANY } = require('./companyConfig');

const tbl = () => `[dbo].[ZITEM_PORTAL]`;

async function exec(sqlQuery, params = []) {
  const pool    = await getPool();
  const request = pool.request();
  let idx = 0;
  const replaced = sqlQuery.replace(/\?/g, () => {
    const name = `p${idx}`;
    request.input(name, params[idx]);
    idx++;
    return `@${name}`;
  });
  const result = await request.query(replaced);
  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 STORE] Checking table...');
  let tableExisted = false;
  await exec(`
    CREATE TABLE ${tbl()} (
      [ID]                  INT IDENTITY(1,1) PRIMARY KEY,
      [STATUS]              NVARCHAR(30)  DEFAULT 'PENDING',
      [ITEM_CODE]           NVARCHAR(50),
      [ITEM_NAME]           NVARCHAR(200),
      [ITEM_DATA]           NVARCHAR(MAX),
      [SUBMITTED_BY]        NVARCHAR(60),
      [SUBMITTED_BY_NAME]   NVARCHAR(100),
      [SUBMITTED_AT]        DATETIME2     DEFAULT GETDATE(),
      [ADMIN_NOTES]         NVARCHAR(MAX),
      [ADMIN_EDITED_DATA]   NVARCHAR(MAX),
      [REVIEWED_BY]         NVARCHAR(60),
      [REVIEWED_AT]         DATETIME2,
      [APPROVED_BY]         NVARCHAR(60),
      [APPROVED_AT]         DATETIME2,
      [REJECTED_BY]         NVARCHAR(60),
      [REJECTED_AT]         DATETIME2,
      [SAP_ITEM_CODE]       NVARCHAR(50),
      [APPROVAL_LOG]        NVARCHAR(MAX),
      [COMPANY]             NVARCHAR(50)  DEFAULT '${DEFAULT_COMPANY}'
    )
  `).catch(e => {
    if (!isAlreadyExists(e)) throw e;
    tableExisted = true;
    console.log('[ITEM STORE] Table already exists — checking for missing columns...');
  });
  if (tableExisted) {
    const cols = [
      `IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZITEM_PORTAL' AND COLUMN_NAME='COMPANY') ALTER TABLE ${tbl()} ADD [COMPANY] NVARCHAR(50) DEFAULT '${DEFAULT_COMPANY}'`,
      `IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZITEM_PORTAL' AND COLUMN_NAME='ADMIN_EDITED_DATA') ALTER TABLE ${tbl()} ADD [ADMIN_EDITED_DATA] NVARCHAR(MAX)`,
      `IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZITEM_PORTAL' AND COLUMN_NAME='APPROVAL_LOG') ALTER TABLE ${tbl()} ADD [APPROVAL_LOG] NVARCHAR(MAX)`,
    ];
    for (const sql of cols) await exec(sql).catch(() => {});
    console.log('[ITEM STORE] ✅ Column migration check complete');
  }
  console.log('[ITEM STORE] ✅ Table ready');
}

function rowToItem(row) {
  if (!row) return null;
  let itemData = {}, adminEditedData = null, approvalLog = [];
  try { itemData = JSON.parse(row.ITEM_DATA || '{}'); } catch (_) {}
  try { adminEditedData = row.ADMIN_EDITED_DATA ? JSON.parse(row.ADMIN_EDITED_DATA) : null; } catch (_) {}
  try { approvalLog = JSON.parse(row.APPROVAL_LOG || '[]'); } catch (_) {}
  return {
    id:              row.ID,
    status:          row.STATUS,
    itemCode:        row.ITEM_CODE  || '',
    itemName:        row.ITEM_NAME  || '',
    itemData,
    adminEditedData,
    submittedBy:     row.SUBMITTED_BY      || '',
    submittedByName: row.SUBMITTED_BY_NAME || '',
    submittedAt:     row.SUBMITTED_AT ? new Date(row.SUBMITTED_AT).toISOString() : null,
    adminNotes:      row.ADMIN_NOTES       || '',
    reviewedBy:      row.REVIEWED_BY       || null,
    reviewedAt:      row.REVIEWED_AT ? new Date(row.REVIEWED_AT).toISOString() : null,
    approvedBy:      row.APPROVED_BY       || null,
    approvedAt:      row.APPROVED_AT ? new Date(row.APPROVED_AT).toISOString() : null,
    rejectedBy:      row.REJECTED_BY       || null,
    rejectedAt:      row.REJECTED_AT ? new Date(row.REJECTED_AT).toISOString() : null,
    sapItemCode:     row.SAP_ITEM_CODE     || null,
    approvalLog,
    company:         row.COMPANY           || '',
  };
}

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

async function insertItem(data) {
  const rows = await exec(`
    INSERT INTO ${tbl()} (
      [STATUS],[ITEM_CODE],[ITEM_NAME],[ITEM_DATA],
      [SUBMITTED_BY],[SUBMITTED_BY_NAME],[SUBMITTED_AT],
      [APPROVAL_LOG],[COMPANY]
    ) OUTPUT INSERTED.ID VALUES (?,?,?,?,?,?,GETDATE(),?,?)
  `, [
    data.status || 'PENDING',
    (data.itemCode || '').substring(0, 50),
    (data.itemName || '').substring(0, 200),
    JSON.stringify(data.itemData || {}),
    (data.submittedBy     || '').substring(0, 60),
    (data.submittedByName || '').substring(0, 100),
    JSON.stringify(data.approvalLog || []),
    (data.company || '').substring(0, 50),
  ]);
  const id = rows[0].ID;
  console.log(`[ITEM STORE] ✅ Inserted ID=${id}`);
  return { id };
}

async function findById(id) {
  const rows = await exec(`SELECT * FROM ${tbl()} WHERE [ID]=?`, [parseInt(id)]);
  return rowToItem(rows[0]);
}

async function listByStatus(status, companyDB, submittedBy = null) {
  const conditions = [];
  const params = [];
  if (status !== 'ALL')              { conditions.push(`[STATUS]=?`);        params.push(status.toUpperCase()); }
  if (submittedBy)                   { conditions.push(`[SUBMITTED_BY]=?`);  params.push(submittedBy); }
  if (companyDB && companyDB !== 'ALL') { conditions.push(`[COMPANY]=?`);    params.push(companyDB); }
  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
  const rows = await exec(`SELECT * FROM ${tbl()} ${where} ORDER BY [SUBMITTED_AT] DESC`, params);
  return rows.map(rowToItem);
}

async function updateItem(id, patch) {
  const COL_MAP = {
    status:          'STATUS',
    itemCode:        'ITEM_CODE',
    itemName:        'ITEM_NAME',
    itemData:        'ITEM_DATA',
    adminNotes:      'ADMIN_NOTES',
    adminEditedData: 'ADMIN_EDITED_DATA',
    approvalLog:     'APPROVAL_LOG',
    reviewedBy:      'REVIEWED_BY',
    reviewedAt:      'REVIEWED_AT',
    approvedBy:      'APPROVED_BY',
    approvedAt:      'APPROVED_AT',
    rejectedBy:      'REJECTED_BY',
    rejectedAt:      'REJECTED_AT',
    sapItemCode:     'SAP_ITEM_CODE',
    company:         'COMPANY',
  };
  const JSON_COLS = new Set(['ITEM_DATA', 'ADMIN_EDITED_DATA', 'APPROVAL_LOG']);
  const TS_COLS   = new Set(['REVIEWED_AT', 'APPROVED_AT', 'REJECTED_AT']);

  const setClauses = [], vals = [];
  Object.entries(patch).forEach(([k, v]) => {
    const col = COL_MAP[k];
    if (!col) return;
    setClauses.push(`[${col}]=?`);
    if (JSON_COLS.has(col))       vals.push(v === null ? null : JSON.stringify(v));
    else if (TS_COLS.has(col))    vals.push(v ? ts(v) : null);
    else                           vals.push(v === null || v === undefined ? null : String(v));
  });
  if (!setClauses.length) return;
  vals.push(parseInt(id));
  await exec(`UPDATE ${tbl()} SET ${setClauses.join(',')} WHERE [ID]=?`, vals);
  console.log(`[ITEM STORE] ✅ Updated ID=${id}`);
}

module.exports = { bootstrap, insertItem, findById, listByStatus, updateItem };