// services/requirementStore.js
// Stores logistics "Requirements Management" entries in SQL: ZREQUIREMENTS
// Ref No format: REQ-{MM}-{YY}-{NNNN}  (NNNN = per month-year running number)
const sql = require('mssql');

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

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('[REQ-STORE] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID            INT IDENTITY(1,1) PRIMARY KEY,
      REF_NO        NVARCHAR(30) NOT NULL,
      PERIOD_FROM   DATE,
      PERIOD_TO     DATE,
      LINES         NVARCHAR(MAX),
      STATUS        NVARCHAR(30) DEFAULT 'OPEN',
      REMARKS       NVARCHAR(MAX),
      CREATED_BY    NVARCHAR(50),
      CREATED_NAME  NVARCHAR(100),
      CREATED_AT    DATETIME2,
      UPDATED_AT    DATETIME2,
      IS_DELETED    BIT DEFAULT 0,
      COMPANY       NVARCHAR(60)
    )
  `).catch(e => {
    if (isAlreadyExists(e)) { console.log('[REQ-STORE] Table exists — OK'); }
    else throw e;
  });
  // Migrations for existing tables
  await exec(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZREQUIREMENTS' AND COLUMN_NAME='IS_DELETED')
    ALTER TABLE ${TABLE} ADD IS_DELETED BIT DEFAULT 0
  `).catch(e => console.log('[REQ-STORE] IS_DELETED migration:', e.message));
  await exec(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZREQUIREMENTS' AND COLUMN_NAME='UPDATED_AT')
    ALTER TABLE ${TABLE} ADD UPDATED_AT DATETIME2
  `).catch(e => console.log('[REQ-STORE] UPDATED_AT migration:', e.message));
  // Production ↔ Store review loop (Admin → System Settings → "Requirement
  // — Store Review Workflow", optional/off by default): Production shares a
  // Requirement with Store, Store cross-checks it against SAP's own MRP
  // Wizard output and edits the line items accordingly, then reverts it back
  // to Production — who then raises the Batch Intimation as normal.
  // REVIEW_STAGE: 0 = not shared yet (or feature unused), 1 = shared with
  // Store (pending their review), 2 = Store reverted — done, single round
  // trip only (no back-and-forth). ORIGINAL_LINES snapshots what LINES
  // looked like at the moment it was shared, so Production can see exactly
  // what Store changed ("before/after") once it comes back.
  const addCol = (col, ddl) => exec(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZREQUIREMENTS' AND COLUMN_NAME='${col}')
    ALTER TABLE ${TABLE} ADD ${ddl}
  `).catch(e => console.log(`[REQ-STORE] ${col} migration:`, e.message));
  await addCol('REVIEW_STAGE', 'REVIEW_STAGE INT DEFAULT 0');
  await addCol('ORIGINAL_LINES', 'ORIGINAL_LINES NVARCHAR(MAX)');
  await addCol('SHARED_BY', 'SHARED_BY NVARCHAR(50)');
  await addCol('SHARED_NAME', 'SHARED_NAME NVARCHAR(100)');
  await addCol('SHARED_AT', 'SHARED_AT DATETIME2');
  await addCol('REVIEWED_BY', 'REVIEWED_BY NVARCHAR(50)');
  await addCol('REVIEWED_NAME', 'REVIEWED_NAME NVARCHAR(100)');
  await addCol('REVIEWED_AT', 'REVIEWED_AT DATETIME2');
  await addCol('REVIEW_REMARKS', 'REVIEW_REMARKS NVARCHAR(MAX)');
  console.log('[REQ-STORE] ✅ Ready');
}

function toTs(isoStr) {
  if (!isoStr) return null;
  return isoStr.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,
    refNo:       row.REF_NO || '',
    periodFrom:  row.PERIOD_FROM ? new Date(row.PERIOD_FROM).toISOString().slice(0, 10) : null,
    periodTo:    row.PERIOD_TO ? new Date(row.PERIOD_TO).toISOString().slice(0, 10) : null,
    lines:       safeJson(row.LINES, []),
    status:      row.STATUS || 'OPEN',
    remarks:     row.REMARKS || '',
    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,
    isDeleted:   !!row.IS_DELETED,
    company:     row.COMPANY || '',
    reviewStage:    row.REVIEW_STAGE != null ? row.REVIEW_STAGE : 0,
    originalLines:  safeJson(row.ORIGINAL_LINES, null),
    sharedBy:       row.SHARED_BY || '',
    sharedByName:   row.SHARED_NAME || '',
    sharedAt:       row.SHARED_AT ? new Date(row.SHARED_AT).toISOString() : null,
    reviewedBy:     row.REVIEWED_BY || '',
    reviewedByName: row.REVIEWED_NAME || '',
    reviewedAt:     row.REVIEWED_AT ? new Date(row.REVIEWED_AT).toISOString() : null,
    reviewRemarks:  row.REVIEW_REMARKS || '',
  };
}

// Generate REQ-MM-YY-NNNN — NNNN is a running number within the current month/year.
async function generateRefNo(company) {
  const now = new Date();
  const mm = String(now.getMonth() + 1).padStart(2, '0');
  const yy = String(now.getFullYear()).slice(-2);
  const prefix = `REQ-${mm}-${yy}-`;
  const rows = await exec(
    `SELECT REF_NO FROM ${TABLE} WHERE REF_NO LIKE ? ${company ? 'AND COMPANY = ?' : ''}`,
    company ? [prefix + '%', company] : [prefix + '%']
  );
  let max = 0;
  rows.forEach(r => {
    const n = parseInt((r.REF_NO || '').slice(prefix.length), 10);
    if (!isNaN(n) && n > max) max = n;
  });
  const next = String(max + 1).padStart(4, '0');
  return prefix + next;
}

async function insertRequirement(r) {
  const now = toTs(new Date().toISOString());
  const refNo = await generateRefNo(r.company);
  // 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} (
      REF_NO, PERIOD_FROM, PERIOD_TO, LINES, STATUS, REMARKS,
      CREATED_BY, CREATED_NAME, CREATED_AT, COMPANY
    ) VALUES (?,?,?,?,?,?,?,?,?,?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [
    refNo,
    r.periodFrom || null,
    r.periodTo || null,
    JSON.stringify(r.lines || []),
    'OPEN',
    r.remarks || '',
    r.createdBy,
    r.createdByName || r.createdBy,
    now,
    r.company || '',
  ]);
  const id = idRows[0].ID;
  return { id, refNo, ...r, status: 'OPEN', createdAt: new Date().toISOString() };
}

async function listRequirements({ mine, company, status, includeDeleted } = {}) {
  const all = await exec(`SELECT * FROM ${TABLE} ORDER BY CREATED_AT DESC`);
  return all.map(fromRow).filter(r => {
    if (!includeDeleted && r.isDeleted) return false;
    if (status && status !== 'ALL' && r.status !== status.toUpperCase()) return false;
    if (mine && r.createdBy !== 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 updateRequirement(id, patch) {
  const now = toTs(new Date().toISOString());
  await exec(`
    UPDATE ${TABLE}
    SET PERIOD_FROM = ?, PERIOD_TO = ?, LINES = ?, REMARKS = ?, UPDATED_AT = ?
    WHERE ID = ? AND (IS_DELETED = 0 OR IS_DELETED IS NULL)
  `, [
    patch.periodFrom || null,
    patch.periodTo || null,
    JSON.stringify(patch.lines || []),
    patch.remarks || '',
    now,
    parseInt(id),
  ]);
  return findById(id);
}

async function updateStatus(id, status) {
  await exec(`UPDATE ${TABLE} SET STATUS = ? WHERE ID = ?`, [String(status).toUpperCase(), parseInt(id)]);
}

async function softDelete(id) {
  await exec(`UPDATE ${TABLE} SET IS_DELETED = 1, UPDATED_AT = ? WHERE ID = ?`,
    [toTs(new Date().toISOString()), parseInt(id)]);
}

// Production → Store: snapshots the CURRENT lines into ORIGINAL_LINES (the
// "before" for the eventual before/after view) and moves to stage 1.
async function shareWithStore(id, { by, byName }) {
  const now = toTs(new Date().toISOString());
  const req = await findById(id);
  if (!req) throw new Error('Requirement not found');
  await exec(`
    UPDATE ${TABLE} SET REVIEW_STAGE = 1, ORIGINAL_LINES = ?, SHARED_BY = ?, SHARED_NAME = ?, SHARED_AT = ?, UPDATED_AT = ?
    WHERE ID = ?
  `, [JSON.stringify(req.lines || []), by || '', byName || by || '', now, now, parseInt(id)]);
  return findById(id);
}

// Store → Production: overwrites LINES with Store's edited version and moves
// to stage 2 (done — single round trip, no further back-and-forth).
async function revertToProduction(id, { by, byName, lines, remarks }) {
  const now = toTs(new Date().toISOString());
  await exec(`
    UPDATE ${TABLE} SET REVIEW_STAGE = 2, LINES = ?, REVIEWED_BY = ?, REVIEWED_NAME = ?, REVIEWED_AT = ?, REVIEW_REMARKS = ?, UPDATED_AT = ?
    WHERE ID = ?
  `, [JSON.stringify(lines || []), by || '', byName || by || '', now, remarks || '', now, parseInt(id)]);
  return findById(id);
}

module.exports = {
  bootstrap, generateRefNo, insertRequirement,
  listRequirements, findById, updateRequirement, updateStatus, softDelete,
  shareWithStore, revertToProduction,
};