// services/prePwoStore.js
// "Pre-PWO" staging (Admin → System Settings → "Pre-PWO — Store Review
// Before SAP", optional/off by default): when enabled, "Create Production
// Order from Work Order" no longer writes to SAP immediately. It first
// creates a portal-only staging record here — same component/BOM line
// shape the real SAP ProductionOrderLines POST would use — that Production
// may optionally "Share with Store". Store can add/remove/substitute
// component lines outright and revert it back to Production (single round
// trip, no further back-and-forth). There is NO hard gate: Production may
// push the Pre-PWO to SAP at any time, reviewed or not. Once pushed, the
// row is marked CONVERTED and locked — see markConverted().
const sql = require('mssql');
const notify = () => require('./notifyStore');

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

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('[PREPWO-STORE] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID              INT IDENTITY(1,1) PRIMARY KEY,
      WORK_ORDER_ID   INT NOT NULL,
      ITEM_CODE       NVARCHAR(60),
      ITEM_NAME       NVARCHAR(200),
      PLANNED_QTY     NVARCHAR(60),
      BATCH_NUMBER    NVARCHAR(60),
      MFG_DATE        NVARCHAR(30),
      EXP_DATE        NVARCHAR(30),
      WAREHOUSE       NVARCHAR(20),
      FG_WAREHOUSE    NVARCHAR(20),
      LINES           NVARCHAR(MAX),
      ORIGINAL_LINES  NVARCHAR(MAX),
      REVIEW_STAGE    INT DEFAULT 0,
      SHARED_BY       NVARCHAR(50),
      SHARED_NAME     NVARCHAR(100),
      SHARED_AT       DATETIME2,
      REVIEWED_BY     NVARCHAR(50),
      REVIEWED_NAME   NVARCHAR(100),
      REVIEWED_AT     DATETIME2,
      REVIEW_REMARKS  NVARCHAR(MAX),
      STATUS          NVARCHAR(20) DEFAULT 'OPEN',
      CONVERTED_PO_ID INT,
      CONVERTED_AT    DATETIME2,
      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('[PREPWO-STORE] Table exists — OK'); }
    else throw e;
  });
  console.log('[PREPWO-STORE] ✅ Ready');
}

function toTs(iso) { return iso ? iso.replace('T', ' ').replace('Z', '').substring(0, 23) : null; }
function safeJson(v, f) { if (!v) return f; try { return JSON.parse(v); } catch { return f; } }

function fromRow(row) {
  if (!row) return null;
  return {
    id:             row.ID,
    workOrderId:    row.WORK_ORDER_ID,
    itemCode:       row.ITEM_CODE || '',
    itemName:       row.ITEM_NAME || '',
    plannedQty:     row.PLANNED_QTY || '',
    batchNumber:    row.BATCH_NUMBER || '',
    mfgDate:        row.MFG_DATE || '',
    expDate:        row.EXP_DATE || '',
    warehouse:      row.WAREHOUSE || '',
    fgWarehouse:    row.FG_WAREHOUSE || '',
    lines:          safeJson(row.LINES, []),
    originalLines:  safeJson(row.ORIGINAL_LINES, null),
    reviewStage:    row.REVIEW_STAGE != null ? row.REVIEW_STAGE : 0,
    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 || '',
    status:         row.STATUS || 'OPEN',
    convertedPoId:  row.CONVERTED_PO_ID || null,
    convertedAt:    row.CONVERTED_AT ? new Date(row.CONVERTED_AT).toISOString() : null,
    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 || '',
  };
}

async function insertPrePwo(p) {
  const now = toTs(new Date().toISOString());
  // INSERT and SELECT SCOPE_IDENTITY() must be one batch — see
  // workOrderStore.js's insertWorkOrder() for the full write-up of why a
  // separate exec() for SCOPE_IDENTITY() can return NULL on a pooled conn.
  const idRows = await exec(`
    INSERT INTO ${TABLE} (
      WORK_ORDER_ID, ITEM_CODE, ITEM_NAME, PLANNED_QTY, BATCH_NUMBER, MFG_DATE, EXP_DATE,
      WAREHOUSE, FG_WAREHOUSE, LINES, REVIEW_STAGE, STATUS, REMARKS,
      CREATED_BY, CREATED_NAME, CREATED_AT, COMPANY
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [
    parseInt(p.workOrderId),
    p.itemCode || '', p.itemName || '', p.plannedQty || '', p.batchNumber || '',
    p.mfgDate || '', p.expDate || '', p.warehouse || '', p.fgWarehouse || '',
    JSON.stringify(p.lines || []), 0, 'OPEN', p.remarks || '',
    p.createdBy, p.createdByName || p.createdBy, now, p.company || '',
  ]);
  const created = await findById(idRows[0].ID);
  notify().notify({
    stepFullKey: 'production_order:prepwo_share',
    title: `Pre-PWO staged for ${created.itemName || created.itemCode} — optional Store review`,
    lines: [['Item', created.itemName || created.itemCode], ['Created By', created.createdByName]],
    url: `${process.env.APP_BASE_URL || ''}/production-order`,
    excludeUsernames: [p.createdBy],
  });
  return created;
}

async function listPrePwos({ mine, company, status, workOrderId, 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;
    if (workOrderId != null && String(r.workOrderId) !== String(workOrderId)) 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 findOpenByWorkOrderId(workOrderId) {
  const rows = await exec(
    `SELECT * FROM ${TABLE} WHERE WORK_ORDER_ID = ? AND (IS_DELETED = 0 OR IS_DELETED IS NULL) AND STATUS <> 'CONVERTED'`,
    [parseInt(workOrderId)]
  );
  return rows.length ? fromRow(rows[0]) : null;
}

// Production edits header/lines directly, any time before conversion.
async function updatePrePwo(id, patch) {
  const now = toTs(new Date().toISOString());
  await exec(`
    UPDATE ${TABLE}
    SET ITEM_CODE = ?, ITEM_NAME = ?, PLANNED_QTY = ?, BATCH_NUMBER = ?, MFG_DATE = ?, EXP_DATE = ?,
        WAREHOUSE = ?, FG_WAREHOUSE = ?, LINES = ?, REMARKS = ?, UPDATED_AT = ?
    WHERE ID = ? AND STATUS <> 'CONVERTED' AND (IS_DELETED = 0 OR IS_DELETED IS NULL)
  `, [
    patch.itemCode || '', patch.itemName || '', patch.plannedQty || '', patch.batchNumber || '',
    patch.mfgDate || '', patch.expDate || '', patch.warehouse || '', patch.fgWarehouse || '',
    JSON.stringify(patch.lines || []), patch.remarks || '', now, parseInt(id),
  ]);
  return findById(id);
}

// Production → Store: snapshots 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 pre = await findById(id);
  if (!pre) throw new Error('Pre-PWO not found');
  await exec(`
    UPDATE ${TABLE} SET REVIEW_STAGE = 1, ORIGINAL_LINES = ?, SHARED_BY = ?, SHARED_NAME = ?, SHARED_AT = ?, UPDATED_AT = ?
    WHERE ID = ?
  `, [JSON.stringify(pre.lines || []), by || '', byName || by || '', now, now, parseInt(id)]);
  notify().notify({
    stepFullKey: 'production_order:prepwo_review',
    title: `Pre-PWO ${pre.itemName || pre.itemCode} shared for Store review`,
    lines: [['Item', pre.itemName || pre.itemCode], ['Shared By', byName || by]],
    url: `${process.env.APP_BASE_URL || ''}/production-order`,
    excludeUsernames: [by],
  });
  return findById(id);
}

// Store → Production: overwrites LINES with Store's edited version (may
// add/remove/substitute lines outright) 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());
  const pre = await findById(id);
  if (!pre) throw new Error('Pre-PWO not found');
  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)]);
  notify().notify({
    stepFullKey: 'production_order:prepwo_share',
    title: `Pre-PWO ${pre.itemName || pre.itemCode} reviewed by Store — back with Production`,
    lines: [['Item', pre.itemName || pre.itemCode], ['Reviewed By', byName || by], ['Remarks', remarks || '']],
    url: `${process.env.APP_BASE_URL || ''}/production-order`,
    excludeUsernames: [by],
  });
  return findById(id);
}

// Called once the Pre-PWO's current lines have actually been pushed to SAP
// as a real Production Order — locks the row (updatePrePwo/share/revert all
// refuse once STATUS = 'CONVERTED').
async function markConverted(id, { convertedPoId }) {
  const now = toTs(new Date().toISOString());
  await exec(`UPDATE ${TABLE} SET STATUS = 'CONVERTED', CONVERTED_PO_ID = ?, CONVERTED_AT = ?, UPDATED_AT = ? WHERE ID = ?`,
    [parseInt(convertedPoId), now, now, parseInt(id)]);
  return findById(id);
}

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

module.exports = {
  bootstrap, insertPrePwo, listPrePwos, findById, findOpenByWorkOrderId,
  updatePrePwo, shareWithStore, revertToProduction, markConverted, softDelete,
};