'use strict';
// routes/requirementCalc.js — "Requirement Calculator": a worksheet under the
// Requirements module that computes, per item, a suggested production
// requirement from historical sale quantity and current stock, so a planner
// can push the result straight into a real Requirement record (POST
// /api/requirements already accepts {periodFrom,periodTo,lines[]} — this
// route only supplies the numbers that feed that form, it does not persist
// anything itself).
//
// Formula (per the reference worksheet):
//   Average Sale/Month = (Qty sold in Period A + Qty sold in Period B) / (months spanned by A+B, typically 10)
//   Total FG            = Stock + Quarantine Stock
//   Stock in Hand (mo)  = Total FG / Average Sale per Month
//   Requirement         = Average Sale per Month × 2 − Total FG
//
// Admin → System Settings can disable either Period A or Period B (at least
// one must always stay enabled). With only one period enabled, Average
// Sale/Month instead uses just that period's qty ÷ the actual span of its
// own From/To dates (auto-derived — the manual "Months" divisor field is
// only meaningful when averaging A+B together).
//
// Sale Qty and Stock are looked up live from SAP (INV1/OINV net of RIN1/ORIN
// returns; OITW.OnHand). There is no "Quarantine" warehouse concept anywhere
// else in this codebase, so the caller must supply which warehouse code(s)
// represent normal Stock and Quarantine Stock for this calculation (picked
// from GET /api/sap/lookup/warehouses) — nothing is hardcoded/guessed here.
const express = require('express');
const router  = express.Router();
const { verifyToken } = require('../middleware/auth');
const { getPool } = require('../services/sqlPool');
const appSettings = require('../services/appSettingsStore');
// NOTE: getPool() connects to the single fixed SQL_DATABASE from .env (same
// as every other direct-SQL route in this app) — it is not company-switched
// per request, unlike the SAP Service-Layer calls elsewhere.

// Whole+fractional months between two ISO dates, inclusive on both ends —
// used to auto-derive the divisor when only ONE comparison period is enabled
// (Admin → System Settings → Requirement Calculator), instead of the manual
// "Months" field which only makes sense when averaging A+B together.
function monthSpan(from, to) {
  const days = (new Date(to + 'T00:00:00Z') - new Date(from + 'T00:00:00Z')) / 86400000 + 1;
  return Math.max(days / 30.44, 1 / 30.44);   // never zero — a same-day range still spans a fraction of a month
}

// Net quantity sold (Invoices minus Returns/Credit Memos) for one item in one
// date range. DocDate is inclusive on both ends.
async function saleQty(pool, itemCode, from, to) {
  const sql = `
    SELECT
      (
        ISNULL((SELECT SUM(inv."Quantity") FROM [dbo].[INV1] inv
                JOIN [dbo].[OINV] h ON h."DocEntry" = inv."DocEntry"
                WHERE inv."ItemCode" = @itemCode AND h."DocDate" BETWEEN @from AND @to AND h."CANCELED" = 'N'), 0)
        -
        ISNULL((SELECT SUM(r."Quantity") FROM [dbo].[RIN1] r
                JOIN [dbo].[ORIN] rh ON rh."DocEntry" = r."DocEntry"
                WHERE r."ItemCode" = @itemCode AND rh."DocDate" BETWEEN @from AND @to AND rh."CANCELED" = 'N'), 0)
      ) AS "Qty"
  `;
  const r = await pool.request().input('itemCode', itemCode).input('from', from).input('to', to).query(sql);
  return Number(r.recordset?.[0]?.Qty) || 0;
}

// On-hand quantity for one item, optionally restricted to one warehouse
// (blank/omitted = summed across all warehouses).
async function stockQty(pool, itemCode, whsCode) {
  const req = pool.request().input('itemCode', itemCode);
  let where = `"ItemCode" = @itemCode`;
  if (whsCode) { req.input('whsCode', whsCode); where += ` AND "WhsCode" = @whsCode`; }
  const r = await req.query(`SELECT ISNULL(SUM("OnHand"), 0) AS "Qty" FROM [dbo].[OITW] WHERE ${where}`);
  return Number(r.recordset?.[0]?.Qty) || 0;
}

// GET /api/requirement-calc/line — one item's full computed row.
router.get('/line', verifyToken, async (req, res) => {
  const { itemCode, periodAFrom, periodATo, periodBFrom, periodBTo, stockWhs, quarantineWhs, months } = req.query;
  if (!itemCode) return res.status(400).json({ success: false, message: 'itemCode is required' });

  // Which periods the admin has enabled — see Admin → System Settings.
  const aOn = appSettings.reqCalcPeriodAEnabled();
  const bOn = appSettings.reqCalcPeriodBEnabled();
  if (aOn && (!periodAFrom || !periodATo)) return res.status(400).json({ success: false, message: 'Period A date range is required' });
  if (bOn && (!periodBFrom || !periodBTo)) return res.status(400).json({ success: false, message: 'Period B date range is required' });

  try {
    const pool = await getPool();
    const [qtyA, qtyB, stock, quarantine] = await Promise.all([
      aOn ? saleQty(pool, itemCode, periodAFrom, periodATo) : Promise.resolve(0),
      bOn ? saleQty(pool, itemCode, periodBFrom, periodBTo) : Promise.resolve(0),
      stockQty(pool, itemCode, stockWhs || null),
      quarantineWhs ? stockQty(pool, itemCode, quarantineWhs) : Promise.resolve(0),
    ]);

    // Both enabled: existing behavior — (A+B) ÷ manual Months divisor.
    // Only one enabled: that period's own qty ÷ the actual span of its own
    // From/To dates (auto-calculated — the manual Months field doesn't apply).
    let avgSalePerMonth;
    if (aOn && bOn) {
      const divisor = Math.max(1, parseFloat(months) || 10);
      avgSalePerMonth = (qtyA + qtyB) / divisor;
    } else if (aOn) {
      avgSalePerMonth = qtyA / monthSpan(periodAFrom, periodATo);
    } else {
      avgSalePerMonth = qtyB / monthSpan(periodBFrom, periodBTo);
    }
    const totalFG = stock + quarantine;
    const stockInHandMonths = avgSalePerMonth > 0 ? totalFG / avgSalePerMonth : 0;
    const requirement = avgSalePerMonth * 2 - totalFG;
    res.json({
      success: true,
      data: {
        itemCode, saleQtyPeriodA: qtyA, saleQtyPeriodB: qtyB,
        avgSalePerMonth, stock, quarantineStock: quarantine, totalFG,
        stockInHandMonths, requirement,
      },
    });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

// ── Saved Item Groups (self-service presets for the Calculator) ──────────
// Shared company-wide: everyone with Requirements access can see and use any
// group; only its creator or an admin may edit/delete it.
const groupStore = require('../services/reqCalcGroupStore');

function canEditGroup(req, group) {
  return req.user?.role === 'admin' || group.createdBy === req.user?.username;
}

router.get('/groups', verifyToken, async (req, res) => {
  try {
    const groups = await groupStore.listGroups(req.query.company || '');
    res.json({ success: true, data: groups });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

router.post('/groups', verifyToken, async (req, res) => {
  try {
    const { name, items, company } = req.body || {};
    if (!name || !String(name).trim()) return res.status(400).json({ success: false, message: 'Group name is required' });
    if (!Array.isArray(items) || !items.length) return res.status(400).json({ success: false, message: 'Select at least one item' });
    const group = await groupStore.createGroup({
      name: String(name).trim(), items, company: company || '',
      createdBy: req.user.username, createdByName: req.user.name || req.user.username,
    });
    res.json({ success: true, data: group });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

router.put('/groups/:id', verifyToken, async (req, res) => {
  try {
    const existing = await groupStore.getGroup(req.params.id);
    if (!existing) return res.status(404).json({ success: false, message: 'Group not found' });
    if (!canEditGroup(req, existing)) return res.status(403).json({ success: false, message: 'Only the creator or an admin can edit this group' });
    const { name, items } = req.body || {};
    if (!name || !String(name).trim()) return res.status(400).json({ success: false, message: 'Group name is required' });
    if (!Array.isArray(items) || !items.length) return res.status(400).json({ success: false, message: 'Select at least one item' });
    const group = await groupStore.updateGroup(req.params.id, { name: String(name).trim(), items });
    res.json({ success: true, data: group });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

router.delete('/groups/:id', verifyToken, async (req, res) => {
  try {
    const existing = await groupStore.getGroup(req.params.id);
    if (!existing) return res.status(404).json({ success: false, message: 'Group not found' });
    if (!canEditGroup(req, existing)) return res.status(403).json({ success: false, message: 'Only the creator or an admin can delete this group' });
    await groupStore.deleteGroup(req.params.id);
    res.json({ success: true });
  } catch (err) { res.status(500).json({ success: false, message: err.message }); }
});

module.exports = router;