// services/productionPlanningStore.js
// Daily Production Planning — one row per (Date, Product) plan: how many
// packages × quantity per package are planned, the per-cycle quantity and
// cycles/day that implies, and whether it's actually been done yet.
const sql = require('mssql');

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

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');
}
function nowTs() { return new Date().toISOString().replace('T', ' ').substring(0, 23); }

async function bootstrap() {
  console.log('[PRODUCTION-PLANNING] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID              INT IDENTITY(1,1) PRIMARY KEY,
      PLAN_DATE       NVARCHAR(20) NOT NULL,
      PRODUCT_NAME    NVARCHAR(200) NOT NULL,
      PRODUCT_TYPE    NVARCHAR(50),
      MARKET          NVARCHAR(100),
      NO_OF_PACKAGE   FLOAT DEFAULT 0,
      QUANTITY        FLOAT DEFAULT 0,
      PROD_AS_PER_SINGLE FLOAT DEFAULT 0,
      PER_CYCLE_QTY   FLOAT DEFAULT 0,
      NO_OF_CYCLE_DAY FLOAT DEFAULT 0,
      STATUS          NVARCHAR(20) DEFAULT 'PLANNED',
      BATCH_NO        NVARCHAR(60),
      COMPANY         NVARCHAR(60),
      CREATED_BY      NVARCHAR(50),
      CREATED_NAME    NVARCHAR(100),
      CREATED_AT      DATETIME2,
      UPDATED_BY      NVARCHAR(50),
      UPDATED_NAME    NVARCHAR(100),
      UPDATED_AT      DATETIME2,
      APPROVED_BY     NVARCHAR(50),
      APPROVED_NAME   NVARCHAR(100),
      APPROVED_AT     DATETIME2,
      REJECTED_BY     NVARCHAR(50),
      REJECTED_NAME   NVARCHAR(100),
      REJECTED_AT     DATETIME2,
      REJECT_REASON   NVARCHAR(500),
      IS_DELETED      BIT DEFAULT 0
    )
  `).catch(e => { if (!isAlreadyExists(e)) throw e; });
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='PRODUCT_TYPE') ALTER TABLE ${TABLE} ADD [PRODUCT_TYPE] NVARCHAR(50)`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='MARKET') ALTER TABLE ${TABLE} ADD [MARKET] NVARCHAR(100)`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='APPROVED_BY') ALTER TABLE ${TABLE} ADD [APPROVED_BY] NVARCHAR(50)`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='APPROVED_NAME') ALTER TABLE ${TABLE} ADD [APPROVED_NAME] NVARCHAR(100)`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='APPROVED_AT') ALTER TABLE ${TABLE} ADD [APPROVED_AT] DATETIME2`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='REJECTED_BY') ALTER TABLE ${TABLE} ADD [REJECTED_BY] NVARCHAR(50)`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='REJECTED_NAME') ALTER TABLE ${TABLE} ADD [REJECTED_NAME] NVARCHAR(100)`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='REJECTED_AT') ALTER TABLE ${TABLE} ADD [REJECTED_AT] DATETIME2`).catch(() => {});
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPRODUCTION_PLANNING' AND COLUMN_NAME='REJECT_REASON') ALTER TABLE ${TABLE} ADD [REJECT_REASON] NVARCHAR(500)`).catch(() => {});
  console.log('[PRODUCTION-PLANNING] ✅ Ready');
}

function fromRow(row) {
  return {
    id: row.ID,
    planDate: row.PLAN_DATE || '',
    productName: row.PRODUCT_NAME || '',
    productType: row.PRODUCT_TYPE || '',
    market: row.MARKET || '',
    noOfPackage: row.NO_OF_PACKAGE || 0,
    quantity: row.QUANTITY || 0,
    prodAsPerSingle: row.PROD_AS_PER_SINGLE || 0,
    perCycleQty: row.PER_CYCLE_QTY || 0,
    noOfCycleDay: row.NO_OF_CYCLE_DAY || 0,
    status: row.STATUS || 'PLANNED',
    batchNo: row.BATCH_NO || '',
    company: row.COMPANY || '',
    createdBy: row.CREATED_BY || '',
    createdByName: row.CREATED_NAME || '',
    createdAt: row.CREATED_AT ? new Date(row.CREATED_AT).toISOString() : null,
    updatedBy: row.UPDATED_BY || '',
    updatedByName: row.UPDATED_NAME || '',
    updatedAt: row.UPDATED_AT ? new Date(row.UPDATED_AT).toISOString() : null,
    approvedBy: row.APPROVED_BY || '',
    approvedByName: row.APPROVED_NAME || '',
    approvedAt: row.APPROVED_AT ? new Date(row.APPROVED_AT).toISOString() : null,
    rejectedBy: row.REJECTED_BY || '',
    rejectedByName: row.REJECTED_NAME || '',
    rejectedAt: row.REJECTED_AT ? new Date(row.REJECTED_AT).toISOString() : null,
    rejectReason: row.REJECT_REASON || '',
    isDeleted: !!row.IS_DELETED,
  };
}

async function listPlans({ company, from, to, status, productType, market, q, includeDeleted } = {}) {
  const rows = await exec(`SELECT * FROM ${TABLE} ORDER BY PLAN_DATE DESC, ID DESC`);
  const qLower = q ? String(q).trim().toLowerCase() : '';
  return rows.map(fromRow).filter(p => {
    if (!includeDeleted && p.isDeleted) return false;
    if (company && p.company && p.company !== company) return false;
    if (status && status !== 'ALL' && p.status !== status.toUpperCase()) return false;
    if (from && p.planDate && p.planDate < from) return false;
    if (to && p.planDate && p.planDate > to) return false;
    if (productType && productType !== 'ALL' && p.productType !== productType) return false;
    if (market && p.market !== market) return false;
    if (qLower && !(p.productName || '').toLowerCase().includes(qLower) && !(p.batchNo || '').toLowerCase().includes(qLower)) 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;
}

// "Prod. As per Single" is always No. of Package × Quantity — computed
// server-side so a client can never submit a mismatched figure.
function computeProdAsPerSingle(noOfPackage, quantity) {
  return (parseFloat(noOfPackage) || 0) * (parseFloat(quantity) || 0);
}

// Quantity may never exceed the day's capacity (Per Cycle Qty × No. of Cycle/Day).
function validateQuantity(quantity, perCycleQty, noOfCycleDay) {
  const totalPerDay = (parseFloat(perCycleQty) || 0) * (parseFloat(noOfCycleDay) || 0);
  const qty = parseFloat(quantity) || 0;
  if (totalPerDay > 0 && qty > totalPerDay) {
    throw new Error(`Quantity (${qty}) cannot exceed Total Qty/Day (${totalPerDay})`);
  }
}

async function createPlan(p) {
  if (!p.planDate) throw new Error('Date is required');
  if (!p.productName || !String(p.productName).trim()) throw new Error('Product Name is required');
  validateQuantity(p.quantity, p.perCycleQty, p.noOfCycleDay);
  const prodAsPerSingle = computeProdAsPerSingle(p.noOfPackage, p.quantity);
  const now = nowTs();
  const idRows = await exec(`
    INSERT INTO ${TABLE} (
      PLAN_DATE, PRODUCT_NAME, PRODUCT_TYPE, MARKET, NO_OF_PACKAGE, QUANTITY, PROD_AS_PER_SINGLE,
      PER_CYCLE_QTY, NO_OF_CYCLE_DAY, STATUS, BATCH_NO, COMPANY,
      CREATED_BY, CREATED_NAME, CREATED_AT
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [
    p.planDate, String(p.productName).trim(), p.productType || '', p.market || '', parseFloat(p.noOfPackage) || 0, parseFloat(p.quantity) || 0, prodAsPerSingle,
    parseFloat(p.perCycleQty) || 0, parseFloat(p.noOfCycleDay) || 0, (p.status === 'DONE' ? 'DONE' : 'PLANNED'), p.batchNo || '', p.company || '',
    p.createdBy || '', p.createdByName || p.createdBy || '', now,
  ]);
  return findById(idRows[0].ID);
}

async function updatePlan(id, p, { by, byName } = {}) {
  const existing = await findById(id);
  if (!existing) throw new Error('Plan not found');
  // An approved plan is locked — no silent re-editing that would leave an
  // old sign-off attached to changed numbers (or quietly invalidate it
  // without the approver noticing). It must be rejected first, which
  // reopens it for editing; editing a REJECTED plan is exactly how that
  // rejection gets resolved, clearing it back to "pending review" so the
  // approver sees it again.
  if (existing.approvedBy) throw new Error('This plan is approved and locked. It must be rejected before it can be edited again.');
  const noOfPackage = p.noOfPackage !== undefined ? p.noOfPackage : existing.noOfPackage;
  const quantity = p.quantity !== undefined ? p.quantity : existing.quantity;
  const perCycleQty = p.perCycleQty !== undefined ? p.perCycleQty : existing.perCycleQty;
  const noOfCycleDay = p.noOfCycleDay !== undefined ? p.noOfCycleDay : existing.noOfCycleDay;
  validateQuantity(quantity, perCycleQty, noOfCycleDay);
  const prodAsPerSingle = computeProdAsPerSingle(noOfPackage, quantity);
  await exec(`
    UPDATE ${TABLE} SET
      PLAN_DATE=?, PRODUCT_NAME=?, PRODUCT_TYPE=?, MARKET=?, NO_OF_PACKAGE=?, QUANTITY=?, PROD_AS_PER_SINGLE=?,
      PER_CYCLE_QTY=?, NO_OF_CYCLE_DAY=?, STATUS=?, BATCH_NO=?,
      UPDATED_BY=?, UPDATED_NAME=?, UPDATED_AT=?,
      REJECTED_BY=NULL, REJECTED_NAME=NULL, REJECTED_AT=NULL, REJECT_REASON=NULL
    WHERE ID=?
  `, [
    p.planDate !== undefined ? p.planDate : existing.planDate,
    p.productName !== undefined ? String(p.productName).trim() : existing.productName,
    p.productType !== undefined ? p.productType : existing.productType,
    p.market !== undefined ? p.market : existing.market,
    parseFloat(noOfPackage) || 0, parseFloat(quantity) || 0, prodAsPerSingle,
    p.perCycleQty !== undefined ? (parseFloat(p.perCycleQty) || 0) : existing.perCycleQty,
    p.noOfCycleDay !== undefined ? (parseFloat(p.noOfCycleDay) || 0) : existing.noOfCycleDay,
    p.status !== undefined ? (p.status === 'DONE' ? 'DONE' : 'PLANNED') : existing.status,
    p.batchNo !== undefined ? p.batchNo : existing.batchNo,
    by || '', byName || by || '', nowTs(),
    parseInt(id),
  ]);
  return findById(id);
}

async function approvePlan(id, { by, byName } = {}) {
  const existing = await findById(id);
  if (!existing) throw new Error('Plan not found');
  await exec(`
    UPDATE ${TABLE} SET APPROVED_BY=?, APPROVED_NAME=?, APPROVED_AT=?,
      REJECTED_BY=NULL, REJECTED_NAME=NULL, REJECTED_AT=NULL, REJECT_REASON=NULL
    WHERE ID=?
  `, [
    by || '', byName || by || '', nowTs(), parseInt(id),
  ]);
  return findById(id);
}

async function rejectPlan(id, reason, { by, byName } = {}) {
  const existing = await findById(id);
  if (!existing) throw new Error('Plan not found');
  if (!reason || !String(reason).trim()) throw new Error('A reason is required to reject a plan');
  await exec(`
    UPDATE ${TABLE} SET REJECTED_BY=?, REJECTED_NAME=?, REJECTED_AT=?, REJECT_REASON=?,
      APPROVED_BY=NULL, APPROVED_NAME=NULL, APPROVED_AT=NULL
    WHERE ID=?
  `, [
    by || '', byName || by || '', nowTs(), String(reason).trim(), parseInt(id),
  ]);
  return findById(id);
}

// Marking a plan Planned/Done is an operational progress flag, not a
// content edit — it must stay changeable even on an approved (locked)
// plan, otherwise nobody could ever record that an approved plan actually
// ran. Deliberately does NOT touch APPROVED_BY/REJECTED_BY — the approval
// itself still stands.
async function updateStatus(id, status, { by, byName } = {}) {
  const existing = await findById(id);
  if (!existing) throw new Error('Plan not found');
  const s = status === 'DONE' ? 'DONE' : 'PLANNED';
  await exec(`UPDATE ${TABLE} SET STATUS=?, UPDATED_BY=?, UPDATED_NAME=?, UPDATED_AT=? WHERE ID=?`, [
    s, by || '', byName || by || '', nowTs(), parseInt(id),
  ]);
  return findById(id);
}

async function softDeletePlan(id) {
  const existing = await findById(id);
  if (!existing) throw new Error('Plan not found');
  if (existing.approvedBy) throw new Error('This plan is approved and locked. It must be rejected before it can be deleted.');
  await exec(`UPDATE ${TABLE} SET IS_DELETED=1 WHERE ID=?`, [parseInt(id)]);
}

module.exports = {
  bootstrap, listPlans, findById, createPlan, updatePlan, approvePlan, rejectPlan, updateStatus, softDeletePlan,
};