const { Op } = require('sequelize');
const db = require('../models');
const path = require('path');
const asyncHandler = require('../middlewares/asyncHandler');
const { sendResponse, sendError } = require('../utils/response');
const { getPagination } = require('../utils/pagination');
const numberFormatService = require('../services/numberFormatService');
const atpService = require('../services/atpService');
const notificationService = require('../services/notificationService');

const toNumber = (value) => {
  const num = parseFloat(value);
  return Number.isFinite(num) ? num : 0;
};

const toFixed3 = (value) => Number(toNumber(value).toFixed(3));
const fallbackNestingNumberForPO = (po) => {
  const baseDate = po?.sent_to_nesting_at || po?.production_order_date || new Date();
  const year = new Date(baseDate).getFullYear();
  return `NST-${year}${String(po?.id || 0).padStart(3, '0')}`;
};

const generateNestingNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  // Prefer Number Format master for Nesting numbers.
  try {
    return await numberFormatService.generateNextNumber(companyId, 'NESTING', fiscalYearId, transaction);
  } catch (error) {
    // Backward-compatible fallback to legacy NST format when Number Format is not configured.
    console.warn('NESTING number format not configured, falling back to legacy format:', error.message);
  }

  const year = new Date().getFullYear();
  const prefix = `NST-${year}`;
  const lastPO = await db.ProductionOrder.findOne({
    where: {
      company_id: companyId,
      nesting_number: { [Op.like]: `${prefix}%` }
    },
    attributes: ['nesting_number'],
    order: [['nesting_number', 'DESC']],
    ...(transaction ? { transaction } : {})
  });

  let sequence = 1;
  if (lastPO?.nesting_number) {
    const tail = String(lastPO.nesting_number).replace(prefix, '');
    const parsed = parseInt(tail, 10);
    if (Number.isFinite(parsed) && parsed > 0) sequence = parsed + 1;
  }

  return `${prefix}${String(sequence).padStart(3, '0')}`;
};

const ensureNestingNumbersForCompany = async (companyId) => {
  if (!companyId) return;
  const missing = await db.ProductionOrder.findAll({
    where: {
      company_id: companyId,
      is_sent_to_nesting: true,
      sent_to_nesting_at: { [Op.ne]: null },
      nesting_number: null
    },
    attributes: ['id', 'fiscal_year_id', 'sent_to_nesting_at', 'production_order_date'],
    order: [['sent_to_nesting_at', 'ASC'], ['id', 'ASC']]
  });
  if (!missing.length) return;

  const transaction = await db.sequelize.transaction();
  try {
    for (const po of missing) {
      const nextNumber = await generateNestingNumber(companyId, po.fiscal_year_id || null, transaction);
      await po.update({ nesting_number: nextNumber }, { transaction });
    }
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
};

const getSheetTypeIds = async (companyId) => {
  const rows = await db.MaterialSheetType.findAll({
    where: {
      company_id: companyId,
      [Op.or]: [
        { sheet_type_code: { [Op.like]: 'SHEET' } },
        { sheet_type_name: { [Op.like]: 'SHEET' } }
      ]
    },
    attributes: ['id']
  });
  return rows.map((r) => r.id);
};

const getSheetMaterialIdSet = async (companyId, materialIds = []) => {
  if (!companyId || !Array.isArray(materialIds) || materialIds.length === 0) {
    return new Set();
  }
  const rows = await db.Material.findAll({
    where: {
      company_id: companyId,
      id: { [Op.in]: materialIds }
    },
    attributes: ['id'],
    include: [
      {
        model: db.MaterialSheetType,
        as: 'materialSheetType',
        attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
        required: true,
        where: {
          company_id: companyId,
          [Op.or]: [
            { sheet_type_code: 'SHEET' },
            { sheet_type_name: 'SHEET' }
          ]
        }
      }
    ]
  });
  return new Set(rows.map((r) => r.id));
};

const isSheetType = (material) => {
  if (!material?.sheet_type_id && !material?.materialSheetType) return false;
  const code = String(material?.materialSheetType?.sheet_type_code || '').trim().toUpperCase();
  const name = String(material?.materialSheetType?.sheet_type_name || '').trim().toUpperCase();
  return code === 'SHEET' || name === 'SHEET';
};

/** Material Master "Type" column: material_type_id → MaterialType (type_code / type_name contains word "sheet"). */
const isMaterialMasterSheetType = (material) => {
  const mt = material?.materialType;
  if (!mt) return false;
  const code = String(mt.type_code || '');
  const name = String(mt.type_name || '');
  const sheetWord = /\bsheet\b/i;
  return sheetWord.test(code) || sheetWord.test(name);
};

const isAnySheetCategoryMaterial = (material) => {
  if (!material) return false;
  if (isSheetType(material)) return true;
  return isMaterialMasterSheetType(material);
};

const hasSheetDimensions = (material) => {
  const length = toNumber(material?.length);
  const width = toNumber(material?.width);
  return length > 0 && width > 0;
};

const isValidNestingSheetMaterial = (material) => {
  return isAnySheetCategoryMaterial(material) && hasSheetDimensions(material);
};

/** Catalog sheet SKU: same rules as Material Master “sheet” (type name/code OR sheet-type master SHEET), with L×W. */
const isCatalogSheetMaterial = (material) => {
  return isAnySheetCategoryMaterial(material) && hasSheetDimensions(material);
};

const gradeShortFromMaterial = (m) => {
  const sheetCode = String(m?.materialSheetType?.sheet_type_code || '').trim();
  const gradeCode = String(m?.materialGrade?.grade_code || '').trim();
  let gradeShort = '-';
  if (sheetCode && gradeCode) gradeShort = `${sheetCode} ${gradeCode}`;
  else if (gradeCode) gradeShort = gradeCode;
  else gradeShort = String(m?.materialGrade?.grade_name || '').trim() || '-';
  if (!gradeShort || gradeShort === '-') {
    gradeShort = String(m?.description || '').trim() || '-';
  }
  return gradeShort;
};

const gradeShortFromNestingRow = (row) => gradeShortFromMaterial(row?.material);

const nestingRowMatchesCatalogGrade = (catalogGradeShort, row) => {
  const gTarget = String(catalogGradeShort || '').replace(/\s+/g, ' ').trim().toUpperCase();
  const gRow = String(gradeShortFromNestingRow(row) || '').replace(/\s+/g, ' ').trim().toUpperCase();
  if (!gTarget) return true;
  if (gRow === gTarget) return true;
  if (gRow && (gTarget === gRow || gTarget.includes(gRow) || gRow.includes(gTarget))) return true;
  const tokens = gTarget.split(/[\s/]+/).filter((t) => t.length >= 2);
  for (const tok of tokens) {
    if (tok.length >= 2 && gRow.includes(tok)) return true;
  }
  return false;
};

const preferredGradeShortFromPo = (po) => {
  const fg = po?.finishedMaterial;
  const sheetCode = String(fg?.materialSheetType?.sheet_type_code || '').trim();
  const gradeCode = String(fg?.materialGrade?.grade_code || '').trim();
  if (sheetCode && gradeCode) return `${sheetCode} ${gradeCode}`;
  if (gradeCode) return gradeCode;
  return String(fg?.materialGrade?.grade_name || '').trim() || '';
};

const groupKeySheet = (thickness, gradeShort, length, width) =>
  `${toFixed3(thickness)}|${String(gradeShort || '').trim()}|${toFixed3(length)}|${toFixed3(width)}`;

const resolveProductionStorageCodeForPlant = async (plantId) => {
  const pid = parseInt(plantId, 10);
  if (!(pid > 0)) return null;
  const rows = await db.StorageLocation.findAll({
    where: { plant_id: pid, status: 'ACTIVE' },
    attributes: ['storage_location_code', 'description'],
    order: [['id', 'ASC']]
  });
  const up = (s) => String(s || '').toUpperCase();
  for (const r of rows) {
    const code = String(r?.storage_location_code || '').trim();
    if (!code) continue;
    const c = up(code);
    const d = up(r?.description);
    if (
      (c.includes('PROD') && (c.includes('FLR') || c.includes('WIP') || c.includes('STORE')))
      || d.includes('PRODUCTION STORAGE')
      || d.includes('WORK IN PROGRESS')
      || d.includes('WIP')
    ) {
      return code;
    }
  }
  return null;
};

const computePoReleasedQuantity = (po) => {
  const batches = Array.isArray(po?.batches) ? po.batches : [];
  const releasedFromBatches = toFixed3(
    (batches || [])
      .filter((b) => ['RELEASED', 'IN_PROGRESS'].includes(String(b?.status || '').toUpperCase()))
      .reduce((sum, b) => sum + toNumber(b?.quantity || 0), 0)
  );
  if (toNumber(releasedFromBatches) > 0) return releasedFromBatches;
  if (
    ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(
      String(po?.status || '').toUpperCase()
    )
  ) {
    return toFixed3(po?.order_quantity || 0);
  }
  return toFixed3(0);
};

const formatPurposeQtyStr = (value) => {
  const n = toNumber(value);
  const rounded = Math.round(n * 1000) / 1000;
  let s = String(rounded);
  s = s.replace(/\.0+$/, '').replace(/(\.\d+?)0+$/, '$1');
  return s || '0';
};

/** Purpose column: finished good drawing + PO released qty only (no component line drawings). */
const buildPurposeTextForNestingGroup = (po) => {
  const fg = po?.finishedMaterial;
  const fgDrg = String(fg?.drawing_number || '').trim() || '-';
  const fgQty = formatPurposeQtyStr(computePoReleasedQuantity(po));
  return `Drg: ${fgDrg} | Qty: ${fgQty}`;
};

const weightValueToKg = (value, uomCode) => {
  const w = typeof value === 'number' ? value : parseFloat(String(value ?? ''));
  if (!Number.isFinite(w)) return null;
  const u = String(uomCode || '')
    .trim()
    .toUpperCase();
  if (!u || u === 'KG' || u === 'KGS' || u === 'KILOGRAM' || u === 'KILO') return w;
  if (u === 'G' || u === 'GM' || u === 'GRAM' || u === 'GRAMS' || u === 'GMS') return w / 1000;
  if (u === 'T' || u === 'TON' || u === 'MT' || u === 'TONNE' || u === 'TONS') return w * 1000;
  return w;
};

const getWeightKgFromMaterialLike = (src) => {
  if (!src || typeof src !== 'object') return null;
  let w = null;
  if (src.net_weight != null && src.net_weight !== '') {
    const n = parseFloat(String(src.net_weight));
    if (Number.isFinite(n)) w = n;
  }
  if (w === null && src.gross_weight != null && src.gross_weight !== '') {
    const g = parseFloat(String(src.gross_weight));
    if (Number.isFinite(g)) w = g;
  }
  if (w === null) return null;
  const uom = String(src.weight_uom_code || src.weightUOM?.uom_code || '').trim();
  return weightValueToKg(w, uom);
};

/** Preferred BOM qty for nesting UI / scrap (matches bom_components `released_net_required_quantity`). */
const preferredBomQtyForNestingRow = (requiredQty, issuedQty, releasedRatio) => {
  const rq = toNumber(requiredQty);
  const iq = toNumber(issuedQty);
  return toFixed3(Math.max(rq - iq, 0) * releasedRatio);
};

const getBomLineUnitWeightKg = (poComponentRow) => {
  if (!poComponentRow || typeof poComponentRow !== 'object') return null;
  const mat = poComponentRow.material || {};
  const fromMaterial = getWeightKgFromMaterialLike({
    net_weight: mat.net_weight,
    gross_weight: mat.gross_weight,
    weight_uom_code: mat.weightUOM?.uom_code || null
  });
  if (fromMaterial != null && Number.isFinite(fromMaterial)) return fromMaterial;
  const cw = poComponentRow.bomComponent?.component_weight;
  if (cw != null && cw !== '') {
    const n = typeof cw === 'number' ? cw : parseFloat(String(cw));
    if (Number.isFinite(n)) return n;
  }
  return null;
};

/**
 * Snapshot Σ weight (kg), length, width for selected PO components — aligned with nesting scrap “component total” logic.
 */
const computeTotalComponentsSnapshotForSelection = async ({
  productionOrderId,
  selectedComponentIds,
  po
}) => {
  if (!Array.isArray(selectedComponentIds) || selectedComponentIds.length === 0) {
    return {
      total_components_weight_kg: null,
      total_components_length: null,
      total_components_width: null
    };
  }
  const ids = [
    ...new Set(
      selectedComponentIds.map((x) => parseInt(x, 10)).filter((n) => Number.isFinite(n) && n > 0)
    )
  ];
  if (!ids.length) {
    return {
      total_components_weight_kg: null,
      total_components_length: null,
      total_components_width: null
    };
  }

  const releasedQty = computePoReleasedQuantity(po);
  const orderQty = toNumber(po?.order_quantity || 0);
  const releasedRatio = orderQty > 0 ? Math.max(0, toNumber(releasedQty) / orderQty) : 0;

  const rows = await db.ProductionOrderComponent.findAll({
    where: { production_order_id: productionOrderId, id: { [Op.in]: ids } },
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['net_weight', 'gross_weight', 'length', 'width'],
        required: false,
        include: [{ model: db.UOM, as: 'weightUOM', attributes: ['uom_code'], required: false }]
      },
      {
        model: db.BOMComponent,
        as: 'bomComponent',
        attributes: ['component_weight'],
        required: false
      }
    ]
  });

  let totalWeightKg = 0;
  let totalLen = 0;
  let totalWid = 0;
  let anyW = false;
  let anyL = false;
  let anyWd = false;

  for (const cid of ids) {
    const row = rows.find((r) => r.id === cid);
    if (!row) continue;
    const qty = toNumber(
      preferredBomQtyForNestingRow(row.required_quantity, row.issued_quantity, releasedRatio)
    );
    if (!(qty > 0)) continue;

    const unitKg = getBomLineUnitWeightKg(row);
    if (unitKg != null && Number.isFinite(unitKg)) {
      totalWeightKg += unitKg * qty;
      anyW = true;
    }

    const len = toNumber(row.material?.length);
    if (Number.isFinite(len) && len > 0) {
      totalLen += len * qty;
      anyL = true;
    }
    const wid = toNumber(row.material?.width);
    if (Number.isFinite(wid) && wid > 0) {
      totalWid += wid * qty;
      anyWd = true;
    }
  }

  return {
    total_components_weight_kg: anyW ? toFixed3(totalWeightKg) : toFixed3(0),
    total_components_length: anyL ? toFixed3(totalLen) : null,
    total_components_width: anyWd ? toFixed3(totalWid) : null
  };
};

/**
 * Build sheet stock groups from Material Master (sheet category + dimensions), grouped by T / grade / L / W.
 * When nesting lines carry a required thickness, only sheet SKUs matching those thicknesses are included.
 * If no eligible line has a positive thickness, all sheet SKUs are listed (backward compatible).
 */
const buildSheetStockGroupsForNesting = async (
  poCompanyId,
  plantId,
  nestingLineItems,
  preferredGradeShort,
  productionOrderId = null
) => {
  if (!poCompanyId || !plantId) {
    return [];
  }

  const whereMat = {
    company_id: poCompanyId,
    status: 'ACTIVE',
    length: { [Op.gt]: 0 },
    width: { [Op.gt]: 0 },
  };

  const materials = await db.Material.findAll({
    where: whereMat,
    attributes: [
      'id',
      'material_number',
      'description',
      'thickness',
      'length',
      'width',
      'dimension_unit',
      'material_type',
      'material_type_id',
      'base_uom_id',
      'gross_weight',
      'net_weight',
      'weight_uom_id',
    ],
    include: [
      {
        model: db.UOM,
        as: 'baseUOM',
        attributes: ['id', 'uom_code', 'uom_name'],
        required: false,
      },
      {
        model: db.UOM,
        as: 'weightUOM',
        attributes: ['id', 'uom_code', 'uom_name'],
        required: false,
      },
      {
        model: db.MaterialSheetType,
        as: 'materialSheetType',
        attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
        required: false,
      },
      {
        model: db.MaterialType,
        as: 'materialType',
        attributes: ['id', 'type_code', 'type_name'],
        required: false,
      },
      {
        model: db.MaterialGrade,
        as: 'materialGrade',
        attributes: ['id', 'grade_code', 'grade_name'],
        required: false,
      },
    ],
    order: [['material_number', 'ASC']],
    limit: 2500,
  });

  const sheetMaterials = (materials || []).filter((m) => isCatalogSheetMaterial(m));
  if (sheetMaterials.length === 0) {
    return [];
  }

  const requiredThicknesses = collectRequiredThicknessesFromNestingLines(nestingLineItems);
  let catalogSheetMaterials = sheetMaterials;
  if (requiredThicknesses.size > 0) {
    catalogSheetMaterials = sheetMaterials.filter((m) => requiredThicknesses.has(toFixed3(m.thickness)));
  }
  if (catalogSheetMaterials.length === 0) {
    return [];
  }

  const mids = catalogSheetMaterials.map((m) => m.id);
  const [materialPlants, materialsWithStorage, poReceiptLines] = await Promise.all([
    db.MaterialPlant.findAll({
      where: { material_id: { [Op.in]: mids }, plant_id: plantId, status: 'ACTIVE' },
      attributes: ['material_id', 'storage_location'],
    }),
    db.Material.findAll({
      where: { id: { [Op.in]: mids } },
      attributes: ['id', 'storage_location_id'],
      include: [
        {
          model: db.StorageLocation,
          as: 'storageLocation',
          attributes: ['storage_location_code', 'description'],
          required: false,
        },
      ],
    }),
    productionOrderId
      ? db.ProductionOrderMaterialReceiptLine.findAll({
          where: {
            production_order_id: productionOrderId,
            material_id: { [Op.in]: mids },
            to_storage_location_id: { [Op.ne]: null }
          },
          attributes: ['id', 'material_id'],
          include: [
            {
              model: db.StorageLocation,
              as: 'toStorageLocation',
              attributes: ['id', 'storage_location_code'],
              required: false
            }
          ],
          order: [['id', 'DESC']]
        })
      : Promise.resolve([])
  ]);

  const plantStoreByMaterialId = new Map();
  (materialPlants || []).forEach((mp) => {
    const code = mp.storage_location && String(mp.storage_location).trim();
    if (code) plantStoreByMaterialId.set(mp.material_id, code);
  });
  const fallbackStoreById = new Map();
  (materialsWithStorage || []).forEach((m) => {
    const code = m.storageLocation?.storage_location_code && String(m.storageLocation.storage_location_code).trim();
    if (code) fallbackStoreById.set(m.id, code);
  });
  const poReceiptToStoreByMaterialId = new Map();
  (poReceiptLines || []).forEach((row) => {
    const mid = parseInt(row?.material_id, 10);
    if (!Number.isFinite(mid) || poReceiptToStoreByMaterialId.has(mid)) return;
    const code = row?.toStorageLocation?.storage_location_code
      ? String(row.toStorageLocation.storage_location_code).trim()
      : '';
    if (code) poReceiptToStoreByMaterialId.set(mid, code);
  });
  const productionStorageCode = await resolveProductionStorageCodeForPlant(plantId);

  const prefG = String(preferredGradeShort || '').replace(/\s+/g, ' ').trim().toUpperCase();

  const flatItems = await Promise.all(
    catalogSheetMaterials.map(async (m) => {
      let stock;
      try {
        stock = await atpService.getAvailableStock(m.id, plantId, null);
      } catch {
        stock = { available: 0, locations: [] };
      }
      const defaultStoreCandidate =
        poReceiptToStoreByMaterialId.get(m.id)
        || productionStorageCode
        || plantStoreByMaterialId.get(m.id)
        || fallbackStoreById.get(m.id)
        || null;
      let stockAtStore = stock;
      try {
        stockAtStore = await atpService.getAvailableStock(m.id, plantId, defaultStoreCandidate || null);
      } catch {
        stockAtStore = { available: 0, locations: [] };
      }
      const locations = (stock.locations || []).map((l) => ({
        storage_location: l.storage_location || '—',
        balance_quantity: toFixed3(toNumber(l.balance_quantity)),
      }));
      const byStore = new Map(
        locations
          .filter((l) => String(l.storage_location || '').trim() && l.storage_location !== '—')
          .map((l) => [String(l.storage_location).trim(), toNumber(l.balance_quantity)])
      );
      const defaultStoreBal = defaultStoreCandidate ? toNumber(byStore.get(defaultStoreCandidate) || 0) : 0;
      let bestStoreCode = null;
      let bestStoreBal = 0;
      for (const [code, bal] of byStore.entries()) {
        if (bal > bestStoreBal) {
          bestStoreBal = bal;
          bestStoreCode = code;
        }
      }
      const useBestStore = !(defaultStoreBal > 0) && bestStoreBal > 0;
      const effectiveStore = useBestStore ? bestStoreCode : defaultStoreCandidate;
      const effectiveStoreAvail = useBestStore
        ? toFixed3(bestStoreBal)
        : toFixed3(Math.max(0, toNumber(stockAtStore.available || 0)));

      const gradeShort = gradeShortFromMaterial(m);
      const thickness = toFixed3(m.thickness);
      const length = toFixed3(m.length);
      const width = toFixed3(m.width);
      const gNorm = String(gradeShort || '').replace(/\s+/g, ' ').trim().toUpperCase();
      const isPreferredGrade = Boolean(prefG && gNorm && (gNorm === prefG || gNorm.includes(prefG) || prefG.includes(gNorm)));

      return {
        material_id: m.id,
        material_number: m.material_number,
        description: m.description,
        material_type: m.material_type,
        thickness,
        length,
        width,
        dimension_unit: m.dimension_unit || 'mm',
        base_uom_id: m.base_uom_id || null,
        quantity_uom_code: m.baseUOM?.uom_code || null,
        gross_weight: m.gross_weight != null ? toFixed3(m.gross_weight) : null,
        net_weight: m.net_weight != null ? toFixed3(m.net_weight) : null,
        weight_uom_code: m.weightUOM?.uom_code || null,
        grade_short: gradeShort,
        is_preferred_grade: isPreferredGrade,
        mrp_available_stock: toFixed3(Math.max(0, toNumber(stock.available || 0))),
        mrp_store_available_qty: effectiveStoreAvail,
        default_storage_location_code: effectiveStore || null,
        warehouse_balances: locations,
        _groupKey: groupKeySheet(thickness, gradeShort, length, width),
      };
    })
  );

  const groupMap = new Map();
  for (const item of flatItems) {
    const k = item._groupKey;
    if (!groupMap.has(k)) {
      groupMap.set(k, {
        group_key: k,
        thickness: item.thickness,
        grade_short: item.grade_short,
        length: item.length,
        width: item.width,
        dimension_unit: item.dimension_unit,
        quantity_uom_id: item.base_uom_id ?? null,
        quantity_uom_code: item.quantity_uom_code ?? null,
        items: [],
        total_atp: 0,
        total_atp_at_default_store: 0,
        store_codes: new Set(),
        is_preferred_grade: item.is_preferred_grade,
      });
    }
    const g = groupMap.get(k);
    delete item._groupKey;
    g.items.push(item);
    if (!g.quantity_uom_id && item.base_uom_id) {
      g.quantity_uom_id = item.base_uom_id;
      g.quantity_uom_code = item.quantity_uom_code || g.quantity_uom_code;
    }
    g.total_atp = toFixed3(toNumber(g.total_atp) + toNumber(item.mrp_available_stock));
    g.total_atp_at_default_store = toFixed3(
      toNumber(g.total_atp_at_default_store) + toNumber(item.mrp_store_available_qty)
    );
    if (item.default_storage_location_code) g.store_codes.add(item.default_storage_location_code);
    g.is_preferred_grade = g.is_preferred_grade || item.is_preferred_grade;
  }

  const groups = Array.from(groupMap.values()).map((g) => {
    const items = g.items || [];
    let quantity_uom_id = g.quantity_uom_id ?? null;
    let quantity_uom_code = g.quantity_uom_code ?? null;
    if (!quantity_uom_code || !quantity_uom_id) {
      const chosen = items.find((it) => String(it.quantity_uom_code || '').trim());
      if (chosen) {
        if (!quantity_uom_id && chosen.base_uom_id) quantity_uom_id = chosen.base_uom_id;
        if (!quantity_uom_code && chosen.quantity_uom_code) quantity_uom_code = chosen.quantity_uom_code;
      }
    }
    return {
      ...g,
      quantity_uom_id,
      quantity_uom_code,
      store_summary: Array.from(g.store_codes).sort().join(', ') || '—',
      store_codes: undefined,
    };
  });

  groups.sort((a, b) => {
    const c = toNumber(a.thickness) - toNumber(b.thickness);
    if (c !== 0) return c;
    const gg = String(a.grade_short).localeCompare(String(b.grade_short));
    if (gg !== 0) return gg;
    const cl = toNumber(a.length) - toNumber(b.length);
    if (cl !== 0) return cl;
    return toNumber(a.width) - toNumber(b.width);
  });

  return groups;
};

/**
 * Determine which required thicknesses (from nesting lines) are missing in Material Master sheet SKUs.
 * Returns array of numeric thickness values (3dp) that are required but not present in sheet master.
 */
const computeMissingSheetThicknesses = (nestingLineItems, sheetMaterials) => {
  const required = collectRequiredThicknessesFromNestingLines(nestingLineItems);
  if (required.size === 0) return [];
  const available = new Set(
    (sheetMaterials || [])
      .filter((m) => isCatalogSheetMaterial(m))
      .map((m) => toFixed3(m.thickness))
      .filter((t) => Number.isFinite(t) && t > 0)
  );
  const missing = Array.from(required).filter((t) => !available.has(t));
  missing.sort((a, b) => toNumber(a) - toNumber(b));
  return missing;
};

/**
 * Determine which required (grade_short × thickness) pairs are missing in Material Master sheet SKUs.
 * grade_short format is same as sheet groups: "sheet_type_code + grade_code" or grade name fallback.
 */
const computeMissingSheetRequirements = (nestingLineItems, sheetMaterials, missingThicknesses = []) => {
  const requiredPairs = new Set();
  if (!Array.isArray(nestingLineItems) || nestingLineItems.length === 0) return [];
  const missingThicknessSet = new Set(
    (missingThicknesses || [])
      .map((t) => toFixed3(t))
      .filter((t) => Number.isFinite(t) && t > 0)
  );

  for (const row of nestingLineItems) {
    if (!isNestingLineEligible(row)) continue;
    const t = toNumber(row?.thickness ?? row?.material?.thickness);
    if (!Number.isFinite(t) || t <= 0) continue;
    const thickness = toFixed3(t);
    // Only warn for thicknesses that are actually missing from Material Master sheet SKUs.
    if (missingThicknessSet.size > 0 && !missingThicknessSet.has(thickness)) continue;
    const gradeShort = String(gradeShortFromNestingRow(row) || '').replace(/\s+/g, ' ').trim();
    if (!gradeShort) continue;
    requiredPairs.add(`${thickness}|${gradeShort.toUpperCase()}`);
  }

  if (requiredPairs.size === 0) return [];

  const availablePairs = new Set(
    (sheetMaterials || [])
      .filter((m) => isCatalogSheetMaterial(m))
      .map((m) => {
        const thickness = toFixed3(m.thickness);
        const gradeShort = String(gradeShortFromMaterial(m) || '').replace(/\s+/g, ' ').trim();
        if (!(thickness > 0) || !gradeShort) return null;
        return `${thickness}|${gradeShort.toUpperCase()}`;
      })
      .filter(Boolean)
  );

  const missing = Array.from(requiredPairs)
    .filter((k) => !availablePairs.has(k))
    .map((k) => {
      const [tStr, gUpper] = String(k).split('|');
      return {
        thickness: toFixed3(tStr),
        grade_short: String(gUpper || '').trim()
      };
    })
    .filter((x) => Number.isFinite(toNumber(x.thickness)) && toNumber(x.thickness) > 0 && x.grade_short);

  missing.sort((a, b) => {
    const dt = toNumber(a.thickness) - toNumber(b.thickness);
    if (dt !== 0) return dt;
    return String(a.grade_short).localeCompare(String(b.grade_short));
  });

  return missing;
};

const demandPlannedQtyForSheetGroup = (nestingLineItems, thickness, gradeShort) => {
  if (!Array.isArray(nestingLineItems)) return 0;
  const tT = toFixed3(thickness);
  return toFixed3(
    nestingLineItems.reduce((sum, row) => {
      if (!isNestingLineEligible(row)) return sum;
      const t = toFixed3(toNumber(row?.thickness ?? row?.material?.thickness));
      if (Math.abs(t - tT) > 0.001) return sum;
      if (!nestingRowMatchesCatalogGrade(gradeShort, row)) return sum;
      return sum + toNumber(row?.required_quantity ?? 0);
    }, 0)
  );
};

const relatedNestingEntryIdsForSheetGroup = (nestingLineItems, thickness, gradeShort) => {
  if (!Array.isArray(nestingLineItems)) return [];
  const tT = toFixed3(thickness);
  return nestingLineItems
    .filter((row) => {
      if (!isNestingLineEligible(row)) return false;
      const t = toFixed3(toNumber(row?.thickness ?? row?.material?.thickness));
      if (Math.abs(t - tT) > 0.001) return false;
      return nestingRowMatchesCatalogGrade(gradeShort, row);
    })
    .map((r) => r.id)
    .filter(Boolean);
};

const enrichSheetGroupsWithDemandAndStores = (groups, nestingLineItems, normalizedItemsByNestingId) => {
  return (groups || []).map((g) => {
    const relatedIds = relatedNestingEntryIdsForSheetGroup(nestingLineItems, g.thickness, g.grade_short);
    const relatedRows = relatedIds
      .map((id) => normalizedItemsByNestingId.get(id))
      .filter(Boolean);
    const rep = relatedRows[0] || null;
    const plannedFromLines = demandPlannedQtyForSheetGroup(nestingLineItems, g.thickness, g.grade_short);
    const defaultStore = rep?.mrp_default_storage_location_code || g.store_summary || null;
    return {
      ...g,
      demand_planned_qty: plannedFromLines,
      related_nesting_entry_ids: relatedIds,
      mrp_default_storage_location_code: defaultStore,
      /** Total ATP across Material Master sheet SKUs in this size group (not BOM rep stock). */
      mrp_store_available_qty: toFixed3(toNumber(g.total_atp_at_default_store)),
    };
  });
};

const reallocateNestingConfirmedForThicknessGrade = async ({
  productionOrderId,
  companyId,
  thickness,
  gradeShort,
  userId,
}) => {
  const tT = toFixed3(thickness);
  const gS = String(gradeShort || '').trim();

  const allConf = await db.ProductionNestingConfirmation.findAll({
    where: { production_order_id: productionOrderId, company_id: companyId },
  });

  const totalConfirmed = (allConf || []).reduce((sum, c) => {
    if (Math.abs(toFixed3(c.thickness) - tT) > 0.0001) return sum;
    if (String(c.grade_short || '').trim() !== gS) return sum;
    return sum + toNumber(c.confirmed_qty);
  }, 0);

  const items = await db.ProductionNesting.findAll({
    where: { production_order_id: productionOrderId, company_id: companyId },
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'length', 'width', 'thickness'],
        required: true,
        include: [
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['sheet_type_code', 'sheet_type_name'],
            required: false,
          },
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['grade_code', 'grade_name'],
            required: false,
          },
          {
            model: db.MaterialType,
            as: 'materialType',
            attributes: ['type_code', 'type_name'],
            required: false,
          },
        ],
      },
    ],
  });

  const targets = (items || []).filter((row) => {
    if (!isNestingLineEligible(row)) return false;
    const t = toFixed3(toNumber(row?.thickness ?? row?.material?.thickness));
    if (Math.abs(t - tT) > 0.001) return false;
    return nestingRowMatchesCatalogGrade(gS, row);
  });

  if (!targets.length) return;

  const confirmed = toFixed3(totalConfirmed);
  const plannedQtyByItem = targets.map((r) => toNumber(r.required_quantity));
  const totalPlanned = plannedQtyByItem.reduce((s, n) => s + n, 0);

  let allocated = targets.map((r, idx) => {
    const planned = plannedQtyByItem[idx] ?? 0;
    if (totalPlanned > 0) return toFixed3((confirmed * planned) / totalPlanned);
    return toFixed3(confirmed / Math.max(1, targets.length));
  });
  const allocatedSum = allocated.reduce((s, n) => s + n, 0);
  const diff = toFixed3(confirmed - allocatedSum);
  if (allocated.length) allocated[0] = toFixed3(allocated[0] + diff);

  for (let i = 0; i < targets.length; i += 1) {
    const entry = targets[i];
    const requiredQty = toNumber(entry.required_quantity);
    const c = toNumber(allocated[i] ?? 0);
    const scrapQty = toFixed3(Math.max(requiredQty - c, 0));
    const scrapPct = requiredQty > 0 ? toFixed3((scrapQty / requiredQty) * 100) : 0;
    const status = resolveNestingStatus(requiredQty, c, entry.status);
    await entry.update({
      nesting_confirmed_quantity: toFixed3(c),
      scrap_quantity: scrapQty,
      scrap_percentage: scrapPct,
      total_required_quantity: toFixed3(requiredQty),
      status,
      updated_by: userId,
    });
  }
};

const isRawMaterialType = (value) => {
  const normalized = String(value || '').trim().toUpperCase().replace(/\s+/g, '_');
  return normalized === 'RAW_MATERIAL' || normalized === 'RAW_MAT' || normalized === 'RAW';
};

const isSheetMaterialFromRow = (row) => {
  return isValidNestingSheetMaterial(row?.material);
};

/** Sheet-type nesting line: Material Master type "sheet" OR sheet-type master SHEET; L×W on material or on nesting row. */
const isSheetNestingRowEligible = (row) => {
  if (!isAnySheetCategoryMaterial(row?.material)) return false;
  if (hasSheetDimensions(row?.material)) return true;
  const length = toNumber(row?.length);
  const width = toNumber(row?.width);
  return length > 0 && width > 0;
};

/** Raw material BOM lines OR sheet master lines (Material Master sheet type with dimensions). */
const isNestingLineEligible = (row) => {
  const materialType = String(row?.material?.material_type || '').toUpperCase();
  if (materialType === 'RAW_MATERIAL') return true;
  return isSheetNestingRowEligible(row);
};

/**
 * Thickness values (toFixed3) required on eligible nesting lines — limits which sheet master SKUs appear in nesting.
 * Empty set means do not filter (no usable thickness on any line).
 */
const collectRequiredThicknessesFromNestingLines = (nestingLineItems) => {
  const set = new Set();
  if (!Array.isArray(nestingLineItems)) return set;
  for (const row of nestingLineItems) {
    if (!isNestingLineEligible(row)) continue;
    const t = toNumber(row?.thickness ?? row?.material?.thickness);
    if (!Number.isFinite(t) || t <= 0) continue;
    set.add(toFixed3(t));
  }
  return set;
};

/** Flat list of sheet SKUs for legacy callers — derived from grouped catalog (Material Master Type = sheet). */
const buildSheetStockCatalogForNesting = async (
  poCompanyId,
  plantId,
  nestingLineItems,
  preferredGradeShort,
  productionOrderId = null
) => {
  const groups = await buildSheetStockGroupsForNesting(
    poCompanyId,
    plantId,
    nestingLineItems,
    preferredGradeShort,
    productionOrderId
  );
  const catalog = [];
  for (const g of groups) {
    for (const it of g.items || []) {
      catalog.push({
        material_id: it.material_id,
        material_number: it.material_number,
        description: it.description,
        material_type: it.material_type,
        thickness: it.thickness,
        length: it.length,
        width: it.width,
        dimension_unit: it.dimension_unit || 'mm',
        base_uom_id: it.base_uom_id ?? null,
        quantity_uom_code: it.quantity_uom_code || null,
        gross_weight: it.gross_weight ?? null,
        net_weight: it.net_weight ?? null,
        weight_uom_code: it.weight_uom_code || null,
        grade_short: it.grade_short,
        mrp_available_stock: it.mrp_available_stock,
        warehouse_balances: it.warehouse_balances,
      });
    }
  }
  return catalog;
};

const resolveNestingStatus = (requiredQty, confirmedQty, previousStatus = null) => {
  const prev = String(previousStatus || '').toUpperCase();
  if (confirmedQty >= requiredQty) return 'COMPLETED';
  if (confirmedQty > 0) return 'IN_PROGRESS';
  if (prev === 'IN_PROGRESS' || prev === 'COMPLETED') return 'IN_PROGRESS';
  return 'PENDING';
};

const notifyNestingStatusChange = async ({ entry, status, userId }) => {
  try {
    if (!entry?.productionOrder?.company_id) return;
    const companyId = entry.productionOrder.company_id;
    const userIdsToNotify = new Set();
    if (entry.productionOrder?.sent_to_nesting_by) userIdsToNotify.add(entry.productionOrder.sent_to_nesting_by);
    if (entry.productionOrder?.created_by) userIdsToNotify.add(entry.productionOrder.created_by);
    if (userId) userIdsToNotify.add(userId);

    const materialLabel = entry.material?.material_number || entry.material?.description || `Material ${entry.material_id}`;
    const poNumber = entry.productionOrder?.production_order_number || `PO#${entry.production_order_id}`;
    const statusLabel = String(status || '').toUpperCase();
    const requiredQty = toFixed3(entry.required_quantity || 0);
    const confirmedQty = toFixed3(entry.nesting_confirmed_quantity || 0);

    const title = `Nesting ${statusLabel}: ${materialLabel}`;
    const message = `Production ${poNumber} | ${materialLabel} is now ${statusLabel}. Required ${requiredQty}, Confirmed ${confirmedQty}.`;
    const link = `/production/nesting/${entry.production_order_id}/view`;

    await Promise.all(
      Array.from(userIdsToNotify).map((notifyUserId) =>
        notificationService.createNotification({
          companyId,
          userId: notifyUserId,
          type: 'INFO',
          moduleName: 'ProductionNesting',
          transactionId: entry.id,
          transactionNumber: entry.productionOrder?.nesting_number || poNumber,
          title,
          message,
          link,
          priority: statusLabel === 'COMPLETED' ? 'HIGH' : 'MEDIUM',
          createdBy: userId || null
        })
      )
    );
  } catch (error) {
    console.error('Failed to create nesting status notification:', error.message);
  }
};

// Recompute exploded BOM quantities using current PO order quantity.
const explodeBOMForNesting = async (
  bomId,
  orderQuantity,
  baseQuantity,
  processedBOMs = new Set(),
  level = 0,
  companyId = null
) => {
  if (!bomId || processedBOMs.has(bomId)) return [];
  processedBOMs.add(bomId);

  const bom = await db.BOMHeader.findByPk(bomId, {
    include: [
      {
        model: db.BOMComponent,
        as: 'components',
        where: { status: 'ACTIVE' },
        required: false,
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_type']
          }
        ]
      }
    ]
  });

  if (!bom || !Array.isArray(bom.components) || bom.components.length === 0) return [];

  const denominator = parseFloat(baseQuantity || 1);
  const multiplier = denominator > 0 ? toNumber(orderQuantity) / denominator : 0;
  const components = [];

  for (const component of bom.components) {
    if (!component?.material_id) continue;

    const requiredQty = toNumber(component.component_quantity || component.quantity || 0) * multiplier;
    const componentUOMId = component.component_uom_id || component.uom_id || null;

    components.push({
      bom_component_id: component.id || null,
      material_id: component.material_id,
      required_quantity: requiredQty,
      uom_id: componentUOMId,
      issue_type: component.issue_type || 'MANUAL',
      storage_location: component.storage_location || null,
      level,
      parent_material_id: level > 0 ? bom.material_id : null
    });

    const materialType = String(component?.material?.material_type || '').toUpperCase();
    const isSubAssembly = materialType === 'SEMI_FINISHED' || materialType === 'FINISHED_GOOD';
    if (!isSubAssembly) continue;

    const subBOM = await db.BOMHeader.findOne({
      where: {
        material_id: component.material_id,
        status: 'ACTIVE',
        ...(companyId ? { company_id: companyId } : {})
      },
      order: [['id', 'DESC']],
      limit: 1
    });
    if (!subBOM) continue;

    const subComponents = await explodeBOMForNesting(
      subBOM.id,
      requiredQty,
      subBOM.base_quantity || 1,
      processedBOMs,
      level + 1,
      companyId || bom.company_id
    );
    components.push(...subComponents);
  }

  return components;
};

exports.sendProductionOrderToNesting = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;
  const replace = req.body?.replace !== false;
  const selectedMaterialIds = Array.isArray(req.body?.materialIds)
    ? new Set(
        req.body.materialIds
          .map((value) => parseInt(value, 10))
          .filter((value) => Number.isFinite(value))
      )
    : new Set();
  const isPartialMaterialSend = selectedMaterialIds.size > 0;

  const po = await db.ProductionOrder.findByPk(id, {
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'components',
        attributes: ['id', 'material_id', 'bom_component_id', 'required_quantity', 'issued_quantity']
      }
    ]
  });

  if (!po) {
    return sendError(res, 'Production Order not found', 404);
  }

  if (companyId && po.company_id !== companyId) {
    return sendError(res, 'You do not have access to this Production Order', 403);
  }

  if (!po.bom_id) {
    return sendError(res, 'BOM is required for nesting generation', 400);
  }

  const rootBom = await db.BOMHeader.findByPk(po.bom_id, { attributes: ['id', 'base_quantity', 'company_id'] });
  if (!rootBom) {
    return sendError(res, 'BOM not found for this Production Order', 404);
  }

  const existingCount = await db.ProductionNesting.count({
    where: { production_order_id: po.id, company_id: po.company_id }
  });
  if (existingCount > 0 && !replace && !isPartialMaterialSend) {
    return sendError(res, 'Nesting already exists for this Production Order. Use replace=true to regenerate.', 400);
  }

  const materialIds = [...new Set([
    ...(po.components || [])
      .map((component) => component?.material_id)
      .filter((materialId) => materialId != null),
    ...(isPartialMaterialSend ? Array.from(selectedMaterialIds) : [])
  ])];
  const materials = materialIds.length > 0
    ? await db.Material.findAll({
        where: {
          company_id: po.company_id,
          id: { [Op.in]: materialIds }
        },
        attributes: [
          'id',
          'material_number',
          'description',
          'material_type',
          'sheet_type_id',
          'thickness',
          'length',
          'width',
          'height',
          'dimension_unit',
          'net_weight',
          'gross_weight',
          'weight_uom_id'
        ],
        include: [
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
            required: false
          },
          {
            model: db.UOM,
            as: 'weightUOM',
            attributes: ['id', 'uom_code'],
            required: false
          }
        ]
      })
    : [];
  const materialMap = new Map(materials.map((row) => [row.id, row]));

  const productionComponentIdsByMaterialId = new Map();
  (po.components || []).forEach((componentRow) => {
    const materialId = parseInt(componentRow?.material_id, 10);
    if (!Number.isFinite(materialId)) return;
    if (!productionComponentIdsByMaterialId.has(materialId)) {
      productionComponentIdsByMaterialId.set(materialId, []);
    }
    productionComponentIdsByMaterialId.get(materialId).push(componentRow.id);
  });

  const rawMaterialComponents = (po.components || [])
    .map((component) => ({
      ...component,
      // Default nesting qty aligns with MRP net requirement (required - issued).
      // For selected-material send we can fall back to base required qty when net is 0,
      // so valid RAW_MATERIAL components are still nestable.
      base_required_quantity: toNumber(component?.required_quantity || 0),
      net_required_quantity: Math.max(
        0,
        toNumber(component?.required_quantity || 0) - toNumber(component?.issued_quantity || 0)
      ),
      material: materialMap.get(component.material_id) || null
    }))
    .filter((component) => {
      const materialType = component?.material?.material_type;
      const isRaw = isRawMaterialType(materialType);
      const qtyToUse = isPartialMaterialSend
        ? (component.net_required_quantity > 0 ? component.net_required_quantity : component.base_required_quantity)
        : component.net_required_quantity;
      component.required_quantity = qtyToUse;
      return isRaw && toNumber(component?.required_quantity || 0) > 0;
    });
  const filteredRawMaterialComponents = isPartialMaterialSend
    ? rawMaterialComponents.filter((component) => selectedMaterialIds.has(parseInt(component.material_id, 10)))
    : rawMaterialComponents;

  // Fallback: if PO components don't contain RAW_MATERIAL rows, derive from recursive BOM explosion.
  const effectiveRawMaterialComponents = [...filteredRawMaterialComponents];
  if (effectiveRawMaterialComponents.length === 0) {
    const explodedComponents = await explodeBOMForNesting(
      rootBom.id,
      po.order_quantity,
      rootBom.base_quantity || 1,
      new Set(),
      0,
      po.company_id
    );
    const missingMaterialIds = [...new Set(
      explodedComponents
        .map((component) => parseInt(component?.material_id, 10))
        .filter((materialId) => Number.isFinite(materialId) && !materialMap.has(materialId))
    )];
    if (missingMaterialIds.length > 0) {
      const explodedMaterials = await db.Material.findAll({
        where: {
          company_id: po.company_id,
          id: { [Op.in]: missingMaterialIds }
        },
        attributes: [
          'id',
          'material_number',
          'description',
          'material_type',
          'sheet_type_id',
          'thickness',
          'length',
          'width',
          'height',
          'dimension_unit',
          'net_weight',
          'gross_weight',
          'weight_uom_id'
        ],
        include: [
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
            required: false
          },
          {
            model: db.UOM,
            as: 'weightUOM',
            attributes: ['id', 'uom_code'],
            required: false
          }
        ]
      });
      explodedMaterials.forEach((row) => {
        materialMap.set(row.id, row);
      });
    }
    const explodedMap = new Map();
    explodedComponents.forEach((component) => {
      const materialId = parseInt(component?.material_id, 10);
      if (!Number.isFinite(materialId)) return;
      if (isPartialMaterialSend && !selectedMaterialIds.has(materialId)) return;
      const material = materialMap.get(materialId) || null;
      if (!isRawMaterialType(material?.material_type)) return;
      const qty = toNumber(component?.required_quantity || 0);
      if (qty <= 0) return;
      const existing = explodedMap.get(materialId);
      if (!existing) {
        explodedMap.set(materialId, {
          material_id: materialId,
          required_quantity: qty,
          material
        });
      } else {
        existing.required_quantity = toNumber(existing.required_quantity) + qty;
      }
    });
    effectiveRawMaterialComponents.push(
      ...Array.from(explodedMap.values()).map((row) => ({
        material_id: row.material_id,
        required_quantity: row.required_quantity,
        material: row.material
      }))
    );
  }

  const aggregatedRawMaterialsMap = new Map();
  effectiveRawMaterialComponents.forEach((component) => {
    const materialId = parseInt(component?.material_id, 10);
    if (!Number.isFinite(materialId)) return;
    const existing = aggregatedRawMaterialsMap.get(materialId);
    if (!existing) {
      aggregatedRawMaterialsMap.set(materialId, {
        material_id: materialId,
        required_quantity: toFixed3(component?.required_quantity || 0),
        material: component?.material || null
      });
      return;
    }
    existing.required_quantity = toFixed3(
      toNumber(existing.required_quantity) + toNumber(component?.required_quantity || 0)
    );
  });
  const aggregatedRawMaterials = Array.from(aggregatedRawMaterialsMap.values());

  if (aggregatedRawMaterials.length === 0) {
    return sendError(res, 'No RAW_MATERIAL components found in this Production Order', 400);
  }

  const transaction = await db.sequelize.transaction();
  try {
    if (existingCount > 0 && replace) {
      await db.ProductionNesting.destroy({
        where: { production_order_id: po.id, company_id: po.company_id },
        transaction
      });
    } else if (existingCount > 0 && isPartialMaterialSend) {
      await db.ProductionNesting.destroy({
        where: {
          production_order_id: po.id,
          company_id: po.company_id,
          material_id: { [Op.in]: Array.from(selectedMaterialIds) }
        },
        transaction
      });
    }

    const entriesPayload = aggregatedRawMaterials.map((component) => {
      const requiredQty = toNumber(component.required_quantity);
      const confirmedQty = 0;
      const scrapQty = toFixed3(Math.max(requiredQty - confirmedQty, 0));
      const scrapPct = requiredQty > 0 ? toFixed3((scrapQty / requiredQty) * 100) : 0;
      const totalQty = toFixed3(requiredQty);
      const matchedCompIds = productionComponentIdsByMaterialId.get(component.material_id) || [];
      const matchedProductionOrderComponentId = matchedCompIds.length > 0 ? matchedCompIds.shift() : null;
      const unitWeightRaw = component.material?.net_weight ?? component.material?.gross_weight;
      const unitWeight = unitWeightRaw != null ? toNumber(unitWeightRaw) : null;
      const hasUnitWeight = unitWeight != null && Number.isFinite(unitWeight);
      const totalWeight = hasUnitWeight ? toFixed3(unitWeight * requiredQty) : null;
      const weightUomCode = String(component.material?.weightUOM?.uom_code || '').trim() || null;

      return {
        company_id: po.company_id,
        plant_id: po.plant_id,
        fiscal_year_id: po.fiscal_year_id,
        production_order_id: po.id,
        production_order_component_id: matchedProductionOrderComponentId,
        bom_component_id: null,
        material_id: component.material_id,
        required_quantity: toFixed3(requiredQty),
        mrp_unit_weight: hasUnitWeight ? toFixed3(unitWeight) : null,
        mrp_total_weight: totalWeight,
        mrp_weight_uom_code: weightUomCode,
        nesting_confirmed_quantity: toFixed3(confirmedQty),
        scrap_percentage: toFixed3(scrapPct),
        scrap_quantity: scrapQty,
        total_required_quantity: totalQty,
        thickness: component.material?.thickness != null ? toFixed3(component.material.thickness) : null,
        length: component.material?.length != null ? toFixed3(component.material.length) : null,
        width: component.material?.width != null ? toFixed3(component.material.width) : null,
        height: component.material?.height != null ? toFixed3(component.material.height) : null,
        dimension_unit: component.material?.dimension_unit || 'mm',
        status: resolveNestingStatus(requiredQty, confirmedQty),
        created_by: userId,
        updated_by: userId
      };
    });

    await db.ProductionNesting.bulkCreate(entriesPayload, { transaction });
    const nestingNumber = po.nesting_number || await generateNestingNumber(po.company_id, po.fiscal_year_id || null, transaction);

    await po.update({
      is_sent_to_nesting: true,
      sent_to_nesting_at: new Date(),
      sent_to_nesting_by: userId,
      nesting_number: nestingNumber
    }, { transaction });

    await transaction.commit();

    sendResponse(res, 200, true, 'Production Order sent to nesting successfully', {
      production_order_id: po.id,
      production_order_number: po.production_order_number,
      nesting_number: nestingNumber,
      total_nesting_items: entriesPayload.length,
      replaced_existing: existingCount > 0 && replace,
      partial_material_send: isPartialMaterialSend
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to send Production Order to nesting', 500);
  }
});

const computeHeaderStatus = (items = [], confirmedGroupKeys = null) => {
  if (!items.length) return 'PENDING';
  const normalized = items.map((i) => ({
    status: String(i?.status || '').toUpperCase(),
    confirmedQty: toNumber(i?.nesting_confirmed_quantity ?? 0),
    thickness: toFixed3(toNumber(i?.thickness ?? i?.material?.thickness)),
    gradeShort: String(gradeShortFromNestingRow(i) || '').replace(/\s+/g, ' ').trim().toUpperCase(),
  }));
  if (normalized.every((x) => x.status === 'CANCELLED')) return 'CANCELLED';
  const active = normalized.filter((x) => x.status !== 'CANCELLED');
  if (!active.length) return 'PENDING';

  // Show COMPLETED only when every active row is completed and has saved confirmed qty.
  const allCompleted = active.every((x) => x.status === 'COMPLETED' && x.confirmedQty > 0);
  if (allCompleted) {
    // If sheet-group confirmations exist, every required thickness+grade group must be confirmed (> 0).
    if (confirmedGroupKeys && confirmedGroupKeys.size > 0) {
      const requiredGroupKeys = new Set(
        active
          .map((x) => `${toFixed3(x.thickness)}|${String(x.gradeShort || '').trim().toUpperCase()}`)
          .filter((k) => !k.endsWith('|-') && !k.endsWith('|'))
      );
      if (requiredGroupKeys.size > 0) {
        const allGroupsConfirmed = Array.from(requiredGroupKeys).every((k) => confirmedGroupKeys.has(k));
        if (!allGroupsConfirmed) return 'IN_PROGRESS';
      }
    }
    return 'COMPLETED';
  }

  // Any saved qty or in-progress/completed state means overall header is in-progress.
  const anyProgress = active.some(
    (x) => x.status === 'IN_PROGRESS' || x.status === 'COMPLETED' || x.confirmedQty > 0
  );
  if (anyProgress) return 'IN_PROGRESS';

  return 'PENDING';
};

const normalizeQueryDate = (value) => {
  if (value == null || value === '') return null;
  const raw = Array.isArray(value) ? value[0] : value;
  const str = String(raw).trim().slice(0, 10);
  return /^\d{4}-\d{2}-\d{2}$/.test(str) ? str : null;
};

exports.getNestingList = asyncHandler(async (req, res) => {
  const {
    page = 1,
    limit = 25,
    search,
    startDate,
    endDate,
    companyId,
    plantId,
    fiscalYearId,
    status
  } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {};
  const effectiveCompanyId = companyId || (req.user && !req.user.isSuperAdmin ? req.user.companyId : null);
  if (effectiveCompanyId) {
    await ensureNestingNumbersForCompany(effectiveCompanyId);
  }
  if (effectiveCompanyId) where.company_id = effectiveCompanyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  where.is_sent_to_nesting = true;

  const startD = normalizeQueryDate(startDate ?? req.query.start_date);
  const endD = normalizeQueryDate(endDate ?? req.query.end_date);
  const andParts = [];

  if (startD || endD) {
    const dateOfSent = db.sequelize.fn('DATE', db.sequelize.col('sent_to_nesting_at'));
    if (startD && endD) {
      andParts.push(db.sequelize.where(dateOfSent, { [Op.between]: [startD, endD] }));
    } else if (startD) {
      andParts.push(db.sequelize.where(dateOfSent, { [Op.gte]: startD }));
    } else {
      andParts.push(db.sequelize.where(dateOfSent, { [Op.lte]: endD }));
    }
  }

  const searchTrim = typeof search === 'string' ? search.trim() : '';
  if (searchTrim) {
    const like = `%${searchTrim}%`;
    andParts.push({
      [Op.or]: [
        { production_order_number: { [Op.like]: like } },
        { '$finishedMaterial.material_number$': { [Op.like]: like } },
        { '$finishedMaterial.description$': { [Op.like]: like } }
      ]
    });
  }

  if (andParts.length) {
    where[Op.and] = andParts;
  }

  const records = await db.ProductionOrder.findAndCountAll({
    where,
    include: [
      {
        model: db.Material,
        as: 'finishedMaterial',
        attributes: ['id', 'material_number', 'description', 'grade_id', 'sheet_type_id'],
        required: false,
        include: [
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['id', 'grade_code', 'grade_name', 'description'],
            required: false
          },
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
            required: false
          }
        ]
      },
      {
        model: db.UOM,
        as: 'uom',
        attributes: ['id', 'uom_code', 'uom_name']
      }
    ],
    distinct: true,
    subQuery: false,
    limit: limitNum,
    offset,
    order: [['sent_to_nesting_at', 'DESC'], ['id', 'DESC']]
  });

  const poIds = (records.rows || []).map((po) => po.id);
  if (!poIds.length) {
    return sendResponse(res, 200, true, 'Nesting list retrieved successfully', {
      totalItems: 0,
      items: [],
      totalPages: 0,
      currentPage: Number(page)
    });
  }

  const nestingRows = await db.ProductionNesting.findAll({
    where: {
      ...(effectiveCompanyId ? { company_id: effectiveCompanyId } : {}),
      production_order_id: { [Op.in]: poIds },
      ...(status ? { status } : {})
    },
    attributes: [
      'id',
      'production_order_id',
      'status',
      'nesting_confirmed_quantity',
      'total_required_quantity',
      'thickness',
      'length',
      'width'
    ],
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_type', 'length', 'width', 'height', 'thickness', 'dimension_unit', 'sheet_type_id'],
        required: true,
        include: [
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['sheet_type_code', 'sheet_type_name'],
            required: false,
          },
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['grade_code', 'grade_name'],
            required: false,
          },
        ],
      },
    ],
  });

  const rowsByPO = new Map();
  nestingRows.forEach((row) => {
    if (!isNestingLineEligible(row)) return;
    const poId = row.production_order_id;
    if (!rowsByPO.has(poId)) rowsByPO.set(poId, []);
    rowsByPO.get(poId).push(row);
  });

  const confirmationRows = await db.ProductionNestingConfirmation.findAll({
    where: {
      ...(effectiveCompanyId ? { company_id: effectiveCompanyId } : {}),
      production_order_id: { [Op.in]: poIds },
    },
    attributes: ['production_order_id', 'thickness', 'grade_short', 'confirmed_qty'],
  });
  const confirmedGroupKeysByPO = new Map();
  (confirmationRows || []).forEach((c) => {
    const poId = c?.production_order_id;
    if (!poId) return;
    const qty = toNumber(c?.confirmed_qty ?? 0);
    if (!(qty > 0)) return;
    const key = `${toFixed3(toNumber(c?.thickness))}|${String(c?.grade_short || '').replace(/\s+/g, ' ').trim().toUpperCase()}`;
    if (!confirmedGroupKeysByPO.has(poId)) confirmedGroupKeysByPO.set(poId, new Set());
    confirmedGroupKeysByPO.get(poId).add(key);
  });

  const headerItems = (records.rows || []).map((po) => {
    const entries = rowsByPO.get(po.id) || [];
    if (!entries.length) return null;
    const confirmedGroupKeys = confirmedGroupKeysByPO.get(po.id) || null;
    return {
      id: po.id,
      production_order_id: po.id,
      production_order_number: po.production_order_number,
      production_order_date: po.production_order_date,
      nesting_number: po.nesting_number || fallbackNestingNumberForPO(po),
      finished_material: po.finishedMaterial || null,
      status: computeHeaderStatus(entries, confirmedGroupKeys),
      order_quantity: toFixed3(po.order_quantity),
      uom: po.uom || null,
      sent_to_nesting_at: po.sent_to_nesting_at
    };
  }).filter(Boolean);

  const response = {
    totalItems: headerItems.length,
    items: headerItems,
    totalPages: limitNum ? Math.ceil(headerItems.length / limitNum) : 1,
    currentPage: Number(page)
  };
  sendResponse(res, 200, true, 'Nesting list retrieved successfully', response);
});

exports.startNestingForProductionOrder = asyncHandler(async (req, res) => {
  const { productionOrderId } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  if (!productionOrderId) {
    return sendError(res, 'productionOrderId is required', 400);
  }

  const po = await db.ProductionOrder.findByPk(productionOrderId);
  if (!po) {
    return sendError(res, 'Production Order not found', 404);
  }
  if (companyId && po.company_id !== companyId) {
    return sendError(res, 'You do not have access to this Production Order', 403);
  }

  const items = await db.ProductionNesting.findAll({
    where: {
      production_order_id: po.id,
      company_id: po.company_id,
      status: 'PENDING'
    },
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['material_type', 'material_type_id', 'sheet_type_id', 'length', 'width', 'thickness'],
        required: true,
        include: [
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['sheet_type_code', 'sheet_type_name'],
            required: false,
          },
          {
            model: db.MaterialType,
            as: 'materialType',
            attributes: ['type_code', 'type_name'],
            required: false,
          },
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['grade_code', 'grade_name'],
            required: false,
          },
        ],
      },
    ],
  });

  const targets = items.filter((row) => isNestingLineEligible(row));

  if (!targets.length) {
    return sendResponse(res, 200, true, 'Nesting already started or no pending raw/sheet lines', {
      updated: 0,
      production_order_id: po.id,
    });
  }

  const transaction = await db.sequelize.transaction();
  try {
    for (const row of targets) {
      await row.update({ status: 'IN_PROGRESS', updated_by: userId }, { transaction });
    }
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    return sendError(res, error.message || 'Failed to start nesting', 500);
  }

  sendResponse(res, 200, true, 'Nesting started — lines are now In Progress', {
    updated: targets.length,
    production_order_id: po.id
  });
});

exports.getNestingByProductionOrder = asyncHandler(async (req, res) => {
  const { productionOrderId } = req.params;
  const companyId = req.user?.companyId;

  const po = await db.ProductionOrder.findByPk(productionOrderId, {
    include: [
      {
        model: db.Material,
        as: 'finishedMaterial',
        attributes: ['id', 'material_number', 'description', 'grade_id', 'drawing_number'],
        required: false,
        include: [
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['id', 'grade_code', 'grade_name', 'description'],
            required: false
          },
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
            required: false
          }
        ]
      },
      { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false },
      {
        model: db.CustomerPO,
        as: 'customerPO',
        attributes: ['id', 'po_number'],
        required: false,
        include: [
          {
            model: db.BusinessPartner,
            as: 'customer',
            attributes: ['id', 'name'],
            required: false
          }
        ]
      }
      ,
      {
        model: db.ProductionOrderBatch,
        as: 'batches',
        attributes: ['quantity', 'status']
      }
    ]
  });

  if (!po) {
    return sendError(res, 'Production Order not found', 404);
  }
  if (companyId && po.company_id !== companyId) {
    return sendError(res, 'You do not have access to this Production Order', 403);
  }

  const releasedQty = computePoReleasedQuantity(po);
  const orderQty = toNumber(po?.order_quantity || 0);
  const releasedRatio = orderQty > 0 ? Math.max(0, toNumber(releasedQty) / orderQty) : 0;

  // BOM components for the finished good (via production order components) — used for per-row multi-select dropdown.
  const poComponents = await db.ProductionOrderComponent.findAll({
    where: { production_order_id: po.id },
    attributes: ['id', 'production_order_id', 'material_id', 'bom_component_id', 'required_quantity', 'issued_quantity'],
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: [
          'id',
          'material_number',
          'description',
          'material_type',
          'drawing_number',
          'thickness',
          'length',
          'width',
          'height',
          'dimension_unit',
          'base_uom_id',
          'gross_weight',
          'net_weight',
          'weight_uom_id',
        ],
        required: false,
        include: [
          {
            model: db.UOM,
            as: 'baseUOM',
            attributes: ['id', 'uom_code', 'uom_name'],
            required: false,
          },
          {
            model: db.UOM,
            as: 'weightUOM',
            attributes: ['id', 'uom_code', 'uom_name'],
            required: false,
          },
        ],
      },
      {
        model: db.BOMComponent,
        as: 'bomComponent',
        attributes: ['id', 'item_number', 'component_weight'],
        required: false
      }
    ],
    order: [['id', 'ASC']]
  });
  const bom_components = (poComponents || []).map((c) => ({
    id: c.id,
    production_order_component_id: c.id,
    material_id: c.material_id,
    material_number: c.material?.material_number || null,
    description: c.material?.description || null,
    material_type: c.material?.material_type || null,
    drawing_number: c.material?.drawing_number || null,
    item_number: c.bomComponent?.item_number || null,
    required_quantity: toFixed3(c.required_quantity || 0),
    issued_quantity: toFixed3(c.issued_quantity || 0),
    net_required_quantity: toFixed3(Math.max(toNumber(c.required_quantity || 0) - toNumber(c.issued_quantity || 0), 0)),
    released_required_quantity: toFixed3(toNumber(c.required_quantity || 0) * releasedRatio),
    released_net_required_quantity: toFixed3(
      Math.max(toNumber(c.required_quantity || 0) - toNumber(c.issued_quantity || 0), 0) * releasedRatio
    ),
    thickness: c.material?.thickness != null ? toFixed3(c.material.thickness) : null,
    length: c.material?.length != null ? toFixed3(c.material.length) : null,
    width: c.material?.width != null ? toFixed3(c.material.width) : null,
    height: c.material?.height != null ? toFixed3(c.material.height) : null,
    dimension_unit: c.material?.dimension_unit || 'mm',
    quantity_uom_id: c.material?.base_uom_id || null,
    quantity_uom_code: c.material?.baseUOM?.uom_code || null,
    component_weight: c.bomComponent?.component_weight != null ? toFixed3(c.bomComponent.component_weight) : null,
    material_net_weight: c.material?.net_weight != null ? toFixed3(c.material.net_weight) : null,
    material_gross_weight: c.material?.gross_weight != null ? toFixed3(c.material.gross_weight) : null,
    weight_uom_code: c.material?.weightUOM?.uom_code || null,
  }));

  const items = await db.ProductionNesting.findAll({
    where: {
      company_id: po.company_id,
      production_order_id: po.id
    },
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'productionOrderComponent',
        attributes: ['id', 'required_quantity', 'issued_quantity', 'storage_location'],
        required: false,
        include: [
          {
            model: db.BOMComponent,
            as: 'bomComponent',
            attributes: ['id', 'item_number'],
            required: false
          }
        ]
      },
      {
        model: db.Material,
        as: 'material',
        attributes: [
          'id',
          'material_number',
          'description',
          'material_type',
          'material_type_id',
          'sheet_type_id',
          'thickness',
          'length',
          'width',
          'height',
          'dimension_unit',
          'grade_id',
          'drawing_number',
          'base_uom_id',
          'gross_weight',
          'net_weight',
          'weight_uom_id',
        ],
        required: true,
        include: [
          {
            model: db.UOM,
            as: 'baseUOM',
            attributes: ['id', 'uom_code', 'uom_name'],
            required: false,
          },
          {
            model: db.UOM,
            as: 'weightUOM',
            attributes: ['id', 'uom_code', 'uom_name'],
            required: false,
          },
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
            required: false
          },
          {
            model: db.MaterialType,
            as: 'materialType',
            attributes: ['id', 'type_code', 'type_name'],
            required: false
          },
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['id', 'grade_code', 'grade_name', 'description'],
            required: false
          }
        ]
      }
    ],
    order: [['id', 'ASC']]
  });

  const nestingLineItems = items.filter((row) => isNestingLineEligible(row));

  if (!nestingLineItems.length) {
    return sendError(res, 'No nesting entries found for this Production Order', 404);
  }

  const uniqueMaterialIds = [...new Set(
    nestingLineItems
      .map((row) => row.material_id)
      .filter((id) => id != null)
  )];

  const materialPlants = await db.MaterialPlant.findAll({
    where: {
      material_id: { [Op.in]: uniqueMaterialIds },
      plant_id: po.plant_id,
      status: 'ACTIVE'
    },
    attributes: ['material_id', 'storage_location']
  });

  const materialsWithStorage = await db.Material.findAll({
    where: { id: { [Op.in]: uniqueMaterialIds } },
    attributes: ['id', 'storage_location_id'],
    include: [
      {
        model: db.StorageLocation,
        as: 'storageLocation',
        attributes: ['storage_location_code', 'description'],
        required: false
      }
    ]
  });

  const plantStorageByMaterialId = new Map();
  materialPlants.forEach((mp) => {
    const code = mp.storage_location && String(mp.storage_location).trim();
    if (code) plantStorageByMaterialId.set(mp.material_id, code);
  });

  const materialFallbackStorageById = new Map();
  materialsWithStorage.forEach((m) => {
    const code = m.storageLocation?.storage_location_code && String(m.storageLocation.storage_location_code).trim();
    if (code) materialFallbackStorageById.set(m.id, code);
  });

  const poReceiptLinesForItems = await db.ProductionOrderMaterialReceiptLine.findAll({
    where: {
      production_order_id: po.id,
      material_id: { [Op.in]: uniqueMaterialIds },
      to_storage_location_id: { [Op.ne]: null }
    },
    attributes: ['id', 'material_id'],
    include: [
      {
        model: db.StorageLocation,
        as: 'toStorageLocation',
        attributes: ['id', 'storage_location_code'],
        required: false
      }
    ],
    order: [['id', 'DESC']]
  });
  const poReceiptToStoreByMaterialId = new Map();
  (poReceiptLinesForItems || []).forEach((row) => {
    const mid = parseInt(row?.material_id, 10);
    if (!Number.isFinite(mid) || poReceiptToStoreByMaterialId.has(mid)) return;
    const code = row?.toStorageLocation?.storage_location_code
      ? String(row.toStorageLocation.storage_location_code).trim()
      : '';
    if (code) poReceiptToStoreByMaterialId.set(mid, code);
  });
  const productionStorageCode = await resolveProductionStorageCodeForPlant(po.plant_id);

  const defaultStorageCodeByMaterialId = new Map();
  uniqueMaterialIds.forEach((mid) => {
    const fromReceiptTo = poReceiptToStoreByMaterialId.get(mid);
    const fromPlant = plantStorageByMaterialId.get(mid);
    const fromMaterial = materialFallbackStorageById.get(mid);
    defaultStorageCodeByMaterialId.set(
      mid,
      fromReceiptTo || productionStorageCode || fromPlant || fromMaterial || null
    );
  });

  const availableStockByMaterialId = new Map();
  const availableAtMrpStoreByMaterialId = new Map();

  await Promise.all(uniqueMaterialIds.map(async (materialId) => {
    let stockInfo = null;
    try {
      stockInfo = await atpService.getAvailableStock(materialId, po.plant_id, null);
      availableStockByMaterialId.set(materialId, toNumber(stockInfo?.available || 0));
    } catch (error) {
      availableStockByMaterialId.set(materialId, 0);
    }

    const locations = Array.isArray(stockInfo?.locations) ? stockInfo.locations : [];
    let bestStoreCode = null;
    let bestStoreBal = 0;
    for (const loc of locations) {
      const code = String(loc?.storage_location || '').trim();
      if (!code) continue;
      const bal = toNumber(loc?.balance_quantity);
      if (bal > bestStoreBal) {
        bestStoreBal = bal;
        bestStoreCode = code;
      }
    }

    const configuredStoreCode = defaultStorageCodeByMaterialId.get(materialId);
    const configuredStoreBal = configuredStoreCode
      ? toNumber(
          locations.find((loc) => String(loc?.storage_location || '').trim() === configuredStoreCode)?.balance_quantity
        )
      : 0;
    const storeCode =
      (!(configuredStoreBal > 0) && bestStoreBal > 0 && bestStoreCode)
        ? bestStoreCode
        : configuredStoreCode;
    defaultStorageCodeByMaterialId.set(materialId, storeCode || null);

    try {
      const stockAtStore = await atpService.getAvailableStock(materialId, po.plant_id, storeCode || null);
      availableAtMrpStoreByMaterialId.set(materialId, toNumber(stockAtStore?.available || 0));
    } catch (error) {
      availableAtMrpStoreByMaterialId.set(materialId, 0);
    }
  }));

  const mrByMaterialId = new Map();
  const materialRequestsForPo = await db.MaterialRequest.findAll({
    where: {
      production_order_id: po.id,
      company_id: po.company_id,
      status: { [Op.ne]: 'CANCELLED' }
    },
    include: [
      {
        model: db.Department,
        as: 'department',
        attributes: ['department_code', 'department_name'],
        required: false
      },
      {
        model: db.MaterialRequestItem,
        as: 'items',
        attributes: ['material_id', 'issued_quantity', 'required_quantity', 'pending_quantity', 'shortfall_quantity'],
        required: false
      }
    ]
  });

  materialRequestsForPo.forEach((mr) => {
    const dept = mr.department;
    const deptLabel = [dept?.department_name, dept?.department_code].filter(Boolean).join(' · ') || null;
    (mr.items || []).forEach((it) => {
      const mid = it.material_id;
      if (mid == null) return;
      if (!mrByMaterialId.has(mid)) {
        mrByMaterialId.set(mid, { issued: 0, required: 0, pending: 0, shortfall: 0, depts: new Set() });
      }
      const agg = mrByMaterialId.get(mid);
      agg.issued += toNumber(it.issued_quantity);
      agg.required += toNumber(it.required_quantity);
      agg.pending += toNumber(it.pending_quantity);
      agg.shortfall += toNumber(it.shortfall_quantity);
      if (deptLabel) agg.depts.add(deptLabel);
    });
  });

  const normalizedItems = nestingLineItems.map((row) => {
    const currentRequiredQty = row?.required_quantity != null
      ? toFixed3(row.required_quantity)
      : toFixed3(row?.productionOrderComponent?.required_quantity);
    const availableStock = toFixed3(availableStockByMaterialId.get(row.material_id) || 0);
    const mrpRequiredQty = toFixed3(Math.max(currentRequiredQty - availableStock, 0));
    const confirmedQty = toFixed3(row.nesting_confirmed_quantity || 0);
    const releasedQty = toFixed3(row?.productionOrderComponent?.issued_quantity ?? 0);
    const scrapQty = toFixed3(Math.max(currentRequiredQty - confirmedQty, 0));
    const scrapPct = currentRequiredQty > 0 ? toFixed3((scrapQty / currentRequiredQty) * 100) : 0;
    const defaultStore = defaultStorageCodeByMaterialId.get(row.material_id) || null;
    const availableAtMrpStore = toFixed3(availableAtMrpStoreByMaterialId.get(row.material_id) || 0);
    const shortageAtMrpStore = toFixed3(Math.max(toNumber(confirmedQty) - toNumber(availableAtMrpStore), 0));
    const data = row.toJSON();
    data.required_quantity = currentRequiredQty;
    data.mrp_planned_qty = currentRequiredQty;
    data.released_qty = releasedQty;
    data.mrp_available_stock = availableStock;
    data.mrp_required_qty = mrpRequiredQty;
    data.mrp_default_storage_location_code = defaultStore;
    data.mrp_store_available_qty = availableAtMrpStore;
    data.nesting_shortage_at_mrp_store = shortageAtMrpStore;
    data.scrap_quantity = scrapQty;
    data.scrap_percentage = scrapPct;
    data.total_required_quantity = currentRequiredQty;
    data.bom_item_number = row?.productionOrderComponent?.bomComponent?.item_number || null;

    const mrAgg = mrByMaterialId.get(row.material_id);
    if (mrAgg) {
      data.store_mr_issued_qty = toFixed3(mrAgg.issued);
      data.store_mr_required_qty = toFixed3(mrAgg.required);
      data.store_mr_pending_qty = toFixed3(mrAgg.pending);
      data.store_mr_shortfall_qty = toFixed3(mrAgg.shortfall);
      data.store_mr_issue_departments =
        mrAgg.depts && mrAgg.depts.size > 0 ? Array.from(mrAgg.depts).sort().join(', ') : '';
    } else {
      data.store_mr_issued_qty = toFixed3(0);
      data.store_mr_required_qty = toFixed3(0);
      data.store_mr_pending_qty = toFixed3(0);
      data.store_mr_shortfall_qty = toFixed3(0);
      data.store_mr_issue_departments = '';
    }

    return data;
  });

  const totalQty = normalizedItems.reduce((sum, row) => sum + toNumber(row.total_required_quantity), 0);

  const normalizedItemsByNestingId = new Map((normalizedItems || []).map((row) => [row.id, row]));
  const preferredGradeShort = preferredGradeShortFromPo(po);

  let sheetStockCatalog = [];
  let sheetStockGroups = [];
  let missing_sheet_thicknesses = [];
  let missing_sheet_requirements = [];
  try {
    // Build sheet master candidate list (same constraints as groups): Material Master sheet SKUs with dimensions.
    // Also compute which required thicknesses are missing from Material Master.
    const sheetMasterRows = await db.Material.findAll({
      where: {
        company_id: po.company_id,
        status: 'ACTIVE',
        length: { [Op.gt]: 0 },
        width: { [Op.gt]: 0 },
      },
      attributes: ['id', 'thickness', 'length', 'width', 'sheet_type_id', 'material_type_id'],
      include: [
        {
          model: db.MaterialSheetType,
          as: 'materialSheetType',
          attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
          required: false,
        },
        {
          model: db.MaterialType,
          as: 'materialType',
          attributes: ['id', 'type_code', 'type_name'],
          required: false,
        },
      ],
      order: [['id', 'ASC']],
      limit: 2500,
    });
    missing_sheet_thicknesses = computeMissingSheetThicknesses(nestingLineItems, sheetMasterRows || []);
    missing_sheet_requirements = computeMissingSheetRequirements(
      nestingLineItems,
      sheetMasterRows || [],
      missing_sheet_thicknesses
    );

    sheetStockGroups = await buildSheetStockGroupsForNesting(
      po.company_id,
      po.plant_id,
      nestingLineItems,
      preferredGradeShort,
      po.id
    );
    sheetStockGroups = enrichSheetGroupsWithDemandAndStores(
      sheetStockGroups,
      nestingLineItems,
      normalizedItemsByNestingId
    );

    const confRows = await db.ProductionNestingConfirmation.findAll({
      where: { production_order_id: po.id, company_id: po.company_id },
    });
    const confByGroupKey = new Map();
    (confRows || []).forEach((c) => {
      const row = c.toJSON ? c.toJSON() : c;
      const k = groupKeySheet(row.thickness, row.grade_short, row.max_length, row.max_width);
      confByGroupKey.set(k, row);
    });

    const confIds = (confRows || []).map((c) => c.id).filter(Boolean);
    const selectedComponentsByConfirmationId = new Map();
    if (confIds.length > 0 && db.ProductionNestingConfirmationComponent) {
      const rows = await db.ProductionNestingConfirmationComponent.findAll({
        where: { confirmation_id: { [Op.in]: confIds } },
        attributes: ['confirmation_id', 'production_order_component_id'],
        order: [['id', 'ASC']]
      });
      (rows || []).forEach((r) => {
        const cid = r.confirmation_id;
        if (!selectedComponentsByConfirmationId.has(cid)) selectedComponentsByConfirmationId.set(cid, []);
        selectedComponentsByConfirmationId.get(cid).push(r.production_order_component_id);
      });
    }

    sheetStockGroups = (sheetStockGroups || []).map((g) => {
      const conf = confByGroupKey.get(g.group_key);
      let selections = [];
      if (conf?.sheet_material_selections) {
        try {
          const parsed = JSON.parse(conf.sheet_material_selections);
          selections = Array.isArray(parsed) ? parsed : [];
        } catch {
          selections = [];
        }
      }
      const savedConfirmed = conf ? toFixed3(conf.confirmed_qty) : 0;
      const shortageVsAtp = toFixed3(Math.max(toNumber(savedConfirmed) - toNumber(g.total_atp), 0));
      const purposeText = buildPurposeTextForNestingGroup(po);
      const selected_component_ids = conf?.id ? (selectedComponentsByConfirmationId.get(conf.id) || []) : [];
      return {
        ...g,
        preferred_grade_short: preferredGradeShort || null,
        saved_confirmed_qty: savedConfirmed,
        sheet_material_selections: selections,
        selected_component_ids,
        nesting_shortage_at_mrp_store: shortageVsAtp,
        purpose_text: purposeText,
        saved_nesting_net_weight:
          conf != null && conf.nesting_net_weight != null ? toFixed3(conf.nesting_net_weight) : null,
        saved_nesting_gross_weight:
          conf != null && conf.nesting_gross_weight != null ? toFixed3(conf.nesting_gross_weight) : null,
        saved_nesting_weight_uom_code: conf?.nesting_weight_uom_code || null,
        saved_total_components_weight_kg:
          conf != null && conf.total_components_weight_kg != null
            ? toFixed3(conf.total_components_weight_kg)
            : null,
        saved_total_components_length:
          conf != null && conf.total_components_length != null ? toFixed3(conf.total_components_length) : null,
        saved_total_components_width:
          conf != null && conf.total_components_width != null ? toFixed3(conf.total_components_width) : null
      };
    });

    sheetStockCatalog = await buildSheetStockCatalogForNesting(
      po.company_id,
      po.plant_id,
      nestingLineItems,
      preferredGradeShort,
      po.id
    );
  } catch (catalogErr) {
    console.warn('sheet stock groups/catalog failed:', catalogErr?.message || catalogErr);
    sheetStockCatalog = [];
    sheetStockGroups = [];
    missing_sheet_thicknesses = [];
    missing_sheet_requirements = [];
  }

  const nestingStoresWhere = { production_order_id: po.id };
  if (companyId) nestingStoresWhere.company_id = companyId;
  const nestingStoresRequests = await db.NestingStoresRequest.findAll({
    where: nestingStoresWhere,
    include: [
      {
        model: db.NestingStoresRequestLine,
        as: 'lines',
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'net_weight', 'gross_weight', 'weight_uom_id'],
            required: false,
            include: [{ model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code'], required: false }]
          }
        ]
      }
    ],
    order: [['created_at', 'DESC']]
  });

  const payload = {
    production_order_id: po.id,
    production_order_number: po.production_order_number,
    production_order_date: po.production_order_date,
    nesting_number: po.nesting_number || fallbackNestingNumberForPO(po),
    customer_po_number: po?.customerPO?.po_number || null,
    customer_name: po?.customerPO?.customer?.name || null,
    order_quantity: toFixed3(po.order_quantity || 0),
    order_uom: po?.uom?.uom_code || null,
    released_quantity: releasedQty,
    finished_material: po.finishedMaterial || null,
    total_items: normalizedItems.length,
    total_quantity: toFixed3(totalQty),
    status: computeHeaderStatus(normalizedItems),
    sent_to_nesting_at: po.sent_to_nesting_at,
    items: normalizedItems,
    sheet_stock_catalog: sheetStockCatalog,
    sheet_stock_groups: sheetStockGroups,
    missing_sheet_thicknesses,
    missing_sheet_requirements,
    bom_components,
    preferred_grade_short: preferredGradeShort || null,
    nesting_stores_requests: (nestingStoresRequests || []).map((r) => {
      const reqRow = r.toJSON ? r.toJSON() : r;
      const lines = Array.isArray(reqRow.lines) ? reqRow.lines : [];
      return {
        ...reqRow,
        lines: lines.map((ln) => ({
          ...ln,
          // Backward-compatible alias for UI label change: "Store Available" -> "Available Qty".
          available_qty: ln.available_at_store_qty
        }))
      };
    })
  };

  sendResponse(res, 200, true, 'Nesting details retrieved successfully', payload);
});

exports.getNestingConfirmations = asyncHandler(async (req, res) => {
  const { productionOrderId } = req.params;
  const companyId = req.user?.companyId;

  if (!productionOrderId) {
    return sendError(res, 'productionOrderId is required', 400);
  }

  const where = { production_order_id: productionOrderId };
  if (companyId) where.company_id = companyId;

  const confirmations = await db.ProductionNestingConfirmation.findAll({
    where,
    order: [['thickness', 'ASC'], ['grade_short', 'ASC'], ['max_length', 'ASC'], ['max_width', 'ASC']]
  });

  sendResponse(res, 200, true, 'Nesting confirmations retrieved successfully', {
    items: (confirmations || []).map((c) => c.toJSON())
  });
});

exports.upsertNestingConfirmation = asyncHandler(async (req, res) => {
  const { productionOrderId } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  const {
    thickness,
    grade_short: gradeShort,
    planned_qty: plannedQtyRaw,
    confirmed_qty: confirmedQtyRaw,
    max_length: maxLengthRaw,
    max_width: maxWidthRaw,
    item_ids: itemIdsRaw,
    sheet_material_selections: sheetSelectionsRaw,
    selected_component_ids: selectedComponentIdsRaw,
    nesting_net_weight: nestingNetWeightRaw,
    nesting_gross_weight: nestingGrossWeightRaw,
    nesting_weight_uom_code: nestingWeightUomCodeRaw
  } = req.body || {};

  const thicknessNum = toNumber(thickness);
  let plannedQty = toFixed3(plannedQtyRaw);
  let confirmedQty = toFixed3(confirmedQtyRaw);
  const maxLength = toFixed3(maxLengthRaw);
  const maxWidth = toFixed3(maxWidthRaw);

  if (!productionOrderId) return sendError(res, 'productionOrderId is required', 400);
  if (!companyId) return sendError(res, 'companyId is required', 403);
  if (!thicknessNum) return sendError(res, 'thickness is required', 400);
  if (!String(gradeShort || '').trim()) return sendError(res, 'grade_short is required', 400);
  if (!(maxLength > 0)) return sendError(res, 'max_length is required', 400);
  if (!(maxWidth > 0)) return sendError(res, 'max_width is required', 400);

  let sheetSelections = [];
  if (Array.isArray(sheetSelectionsRaw)) sheetSelections = sheetSelectionsRaw;
  else if (typeof sheetSelectionsRaw === 'string' && sheetSelectionsRaw.trim()) {
    try {
      const parsed = JSON.parse(sheetSelectionsRaw);
      sheetSelections = Array.isArray(parsed) ? parsed : [];
    } catch {
      sheetSelections = [];
    }
  }

  sheetSelections = (sheetSelections || [])
    .map((row) => ({
      material_id: parseInt(row?.material_id, 10),
      nesting_qty: toFixed3(Math.max(0, toNumber(row?.nesting_qty ?? row?.nesting_confirmed_qty ?? 0)))
    }))
    .filter((row) => Number.isFinite(row.material_id) && row.material_id > 0 && toNumber(row.nesting_qty) > 0);

  if (sheetSelections.length > 0) {
    confirmedQty = toFixed3(sheetSelections.reduce((s, r) => s + toNumber(r.nesting_qty), 0));
  }

  let itemIds = [];
  if (Array.isArray(itemIdsRaw)) itemIds = itemIdsRaw;
  else if (typeof itemIdsRaw === 'string') {
    try {
      const parsed = JSON.parse(itemIdsRaw);
      if (Array.isArray(parsed)) itemIds = parsed;
      else itemIds = [];
    } catch {
      itemIds = itemIdsRaw
        .split(',')
        .map((x) => parseInt(String(x).trim(), 10))
        .filter((n) => Number.isFinite(n));
    }
  }
  itemIds = itemIds.map((id) => parseInt(id, 10)).filter((n) => Number.isFinite(n));

  let selectedComponentIds = [];
  if (Array.isArray(selectedComponentIdsRaw)) selectedComponentIds = selectedComponentIdsRaw;
  else if (typeof selectedComponentIdsRaw === 'string' && selectedComponentIdsRaw.trim()) {
    try {
      const parsed = JSON.parse(selectedComponentIdsRaw);
      selectedComponentIds = Array.isArray(parsed) ? parsed : [];
    } catch {
      selectedComponentIds = [];
    }
  }
  selectedComponentIds = (selectedComponentIds || [])
    .map((x) => parseInt(x, 10))
    .filter((n) => Number.isFinite(n) && n > 0);

  const nestingNetWeight =
    nestingNetWeightRaw != null && nestingNetWeightRaw !== ''
      ? toFixed3(toNumber(nestingNetWeightRaw))
      : null;
  const nestingGrossWeight =
    nestingGrossWeightRaw != null && nestingGrossWeightRaw !== ''
      ? toFixed3(toNumber(nestingGrossWeightRaw))
      : null;
  const nestingWeightUomCode =
    nestingWeightUomCodeRaw != null && String(nestingWeightUomCodeRaw).trim()
      ? String(nestingWeightUomCodeRaw).trim().slice(0, 30)
      : null;

  const po = await db.ProductionOrder.findByPk(productionOrderId, {
    include: [
      { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code'], required: false },
      {
        model: db.CustomerPO,
        as: 'customerPO',
        attributes: ['id', 'po_number'],
        required: false,
        include: [{ model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name'], required: false }]
      },
      {
        model: db.Material,
        as: 'finishedMaterial',
        attributes: ['id', 'material_number', 'description', 'drawing_number'],
        required: false
      },
      {
        model: db.ProductionOrderBatch,
        as: 'batches',
        attributes: ['quantity', 'status'],
        required: false
      }
    ]
  });

  if (!po) return sendError(res, 'Production Order not found', 404);
  if (companyId && po.company_id !== companyId) return sendError(res, 'You do not have access to this Production Order', 403);

  const nestingForPo = await db.ProductionNesting.findAll({
    where: { production_order_id: productionOrderId, company_id: companyId },
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'productionOrderComponent',
        attributes: ['id', 'required_quantity', 'issued_quantity', 'storage_location'],
        required: false,
        include: [
          {
            model: db.BOMComponent,
            as: 'bomComponent',
            attributes: ['id', 'item_number'],
            required: false
          }
        ]
      },
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'length', 'width', 'dimension_unit', 'thickness', 'drawing_number'],
        required: false,
        include: [
          {
            model: db.MaterialSheetType,
            as: 'materialSheetType',
            attributes: ['sheet_type_code', 'sheet_type_name'],
            required: false
          },
          {
            model: db.MaterialGrade,
            as: 'materialGrade',
            attributes: ['grade_code', 'grade_name'],
            required: false
          },
          {
            model: db.MaterialType,
            as: 'materialType',
            attributes: ['type_code', 'type_name'],
            required: false
          }
        ]
      }
    ]
  });

  const nestingLineItems = (nestingForPo || []).filter((row) => isNestingLineEligible(row));
  if (!itemIds.length) {
    itemIds = relatedNestingEntryIdsForSheetGroup(nestingLineItems, thicknessNum, String(gradeShort).trim());
  }
  if (!itemIds.length && nestingForPo.length > 0) {
    itemIds = [nestingForPo[0].id];
  }

  if (!itemIds.length) {
    return sendError(res, 'No nesting entries on this production order', 400);
  }

  let entries = await db.ProductionNesting.findAll({
    where: { id: { [Op.in]: itemIds }, production_order_id: productionOrderId },
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'length', 'width', 'dimension_unit'],
        required: false
      }
    ]
  });

  if (!entries.length) {
    return sendError(res, 'No nesting entries found for provided item_ids', 404);
  }

  const sheetAreaMm2 = toFixed3(maxLength * maxWidth);
  let usedAreaMm2 = 0;

  if (sheetSelections.length > 0) {
    const mids = [...new Set(sheetSelections.map((s) => s.material_id))];
    const mats = await db.Material.findAll({
      where: { id: { [Op.in]: mids }, company_id: companyId },
      attributes: ['id', 'length', 'width']
    });
    const byId = new Map(mats.map((x) => [x.id, x]));
    usedAreaMm2 = toFixed3(
      sheetSelections.reduce((sum, s) => {
        const mat = byId.get(s.material_id);
        const len = toNumber(mat?.length);
        const wid = toNumber(mat?.width);
        return sum + len * wid * toNumber(s.nesting_qty);
      }, 0)
    );
  } else {
    usedAreaMm2 = toFixed3(
      entries.reduce((sum, e) => {
        const len = toNumber(e?.material?.length);
        const wid = toNumber(e?.material?.width);
        const qty = toNumber(e?.nesting_confirmed_quantity ?? 0);
        return sum + len * wid * qty;
      }, 0)
    );
  }

  if (plannedQty <= 0 && nestingLineItems.length > 0) {
    plannedQty = demandPlannedQtyForSheetGroup(nestingLineItems, thicknessNum, String(gradeShort).trim());
  }

  const sheetCount = sheetAreaMm2 > 0 && usedAreaMm2 >= 0 ? Math.ceil(toNumber(usedAreaMm2) / toNumber(sheetAreaMm2)) : 0;
  const capacityAreaMm2 = toFixed3(sheetCount * toNumber(sheetAreaMm2));
  const scrapAreaMm2 = Math.max(0, toNumber(capacityAreaMm2) - toNumber(usedAreaMm2));
  const scrapPercentage = capacityAreaMm2 > 0 ? toFixed3((scrapAreaMm2 / toNumber(capacityAreaMm2)) * 100) : 0;
  const scrapQty = toFixed3(toNumber(plannedQty) - toNumber(confirmedQty));

  const first = entries[0];

  const existing = await db.ProductionNestingConfirmation.findOne({
    where: {
      company_id: companyId,
      production_order_id: productionOrderId,
      thickness: thicknessNum,
      grade_short: String(gradeShort).trim(),
      max_length: maxLength,
      max_width: maxWidth
    }
  });

  const selectionsJson = sheetSelections.length > 0 ? JSON.stringify(sheetSelections) : null;

  const purposeTextForDb = buildPurposeTextForNestingGroup(po);

  const componentTotals = await computeTotalComponentsSnapshotForSelection({
    productionOrderId,
    selectedComponentIds,
    po
  });

  const updateData = {
    company_id: companyId,
    plant_id: po.plant_id,
    fiscal_year_id: po.fiscal_year_id,
    production_order_id: productionOrderId,
    thickness: thicknessNum,
    grade_short: String(gradeShort).trim(),

    purpose_text: purposeTextForDb,

    production_order_number: po.production_order_number,
    customer_po_number: po?.customerPO?.po_number || null,
    customer_name: po?.customerPO?.customer?.name || null,
    finished_material_number: po?.finishedMaterial?.material_number || null,
    finished_material_description: po?.finishedMaterial?.description || null,

    planned_qty: plannedQty,
    confirmed_qty: confirmedQty,
    max_length: maxLength,
    max_width: maxWidth,
    sheet_count: sheetCount,
    used_area_mm2: usedAreaMm2,
    scrap_area_mm2: toFixed3(scrapAreaMm2),
    scrap_percentage: scrapPercentage,
    scrap_qty: scrapQty,

    sheet_material_selections: selectionsJson,

    nesting_net_weight: nestingNetWeight,
    nesting_gross_weight: nestingGrossWeight,
    nesting_weight_uom_code: nestingWeightUomCode,

    total_components_weight_kg: componentTotals.total_components_weight_kg,
    total_components_length: componentTotals.total_components_length,
    total_components_width: componentTotals.total_components_width,

    nesting_file_path: first?.nesting_file_path || null,
    nesting_file_name: first?.nesting_file_name || null,
    nesting_file_type: first?.nesting_file_type || null,
    nesting_file_size: first?.nesting_file_size || null,

    updated_by: userId
  };

  let savedConfirmation = existing;
  if (existing) {
    savedConfirmation = await existing.update(updateData);
  } else {
    savedConfirmation = await db.ProductionNestingConfirmation.create({
      ...updateData,
      created_by: userId
    });
  }

  // Persist selected BOM components (normalized, no JSON).
  // Store production_order_component_id(s) chosen for this sheet group/confirmation.
  if (db.ProductionNestingConfirmationComponent && savedConfirmation?.id) {
    const componentRows = selectedComponentIds.length
      ? await db.ProductionOrderComponent.findAll({
          where: {
            id: { [Op.in]: selectedComponentIds },
            production_order_id: productionOrderId
          },
          attributes: ['id', 'material_id']
        })
      : [];
    const validComponentIds = new Set((componentRows || []).map((r) => r.id));

    await db.ProductionNestingConfirmationComponent.destroy({
      where: { confirmation_id: savedConfirmation.id }
    });

    if (validComponentIds.size > 0) {
      await db.ProductionNestingConfirmationComponent.bulkCreate(
        Array.from(validComponentIds).map((cid) => {
          const row = componentRows.find((r) => r.id === cid);
          return {
            company_id: companyId,
            production_order_id: productionOrderId,
            confirmation_id: savedConfirmation.id,
            production_order_component_id: cid,
            material_id: row?.material_id || null
          };
        })
      );
    }
  }

  await reallocateNestingConfirmedForThicknessGrade({
    productionOrderId,
    companyId,
    thickness: thicknessNum,
    gradeShort: String(gradeShort).trim(),
    userId
  });

  sendResponse(res, 200, true, 'Nesting confirmation saved successfully');
});

exports.updateNestingEntry = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;
  const entry = await db.ProductionNesting.findByPk(id, {
    include: [
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'company_id', 'production_order_number', 'nesting_number', 'sent_to_nesting_by', 'created_by'] }
    ]
  });
  if (!entry) {
    return sendError(res, 'Nesting entry not found', 404);
  }
  if (companyId && entry.company_id !== companyId) {
    return sendError(res, 'You do not have access to this nesting entry', 403);
  }

  const requiredQty = toNumber(entry.required_quantity);
  const confirmedQtyRaw = req.body?.nesting_confirmed_quantity;
  const confirmedQty = toFixed3(Math.max(toNumber(confirmedQtyRaw), 0));
  const scrapQty = toFixed3(Math.max(requiredQty - confirmedQty, 0));
  const scrapPct = requiredQty > 0 ? toFixed3((scrapQty / requiredQty) * 100) : 0;
  const status = resolveNestingStatus(requiredQty, confirmedQty, entry.status);

  const updateData = {
    nesting_confirmed_quantity: confirmedQty,
    scrap_quantity: scrapQty,
    scrap_percentage: scrapPct,
    total_required_quantity: toFixed3(requiredQty),
    status,
    updated_by: userId
  };

  if (req.file) {
    const absoluteUploads = path.join(__dirname, '../uploads').replace(/\\/g, '/');
    const normalizedPath = req.file.path.replace(/\\/g, '/');
    const relativePath = normalizedPath.startsWith(absoluteUploads)
      ? normalizedPath.slice(absoluteUploads.length)
      : `/nesting/${path.basename(normalizedPath)}`;
    updateData.nesting_file_path = `/uploads${relativePath.startsWith('/') ? relativePath : `/${relativePath}`}`;
    updateData.nesting_file_name = req.file.originalname;
    updateData.nesting_file_type = req.file.mimetype;
    updateData.nesting_file_size = req.file.size;
  }

  await entry.update(updateData);
  await notifyNestingStatusChange({ entry, status: updateData.status, userId });
  const updated = await db.ProductionNesting.findByPk(id, {
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'thickness', 'length', 'width', 'height', 'dimension_unit']
      }
    ]
  });
  sendResponse(res, 200, true, 'Nesting entry updated successfully', updated);
});

exports.startNestingEntry = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;
  const entry = await db.ProductionNesting.findByPk(id, {
    include: [
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'company_id', 'production_order_number', 'nesting_number', 'sent_to_nesting_by', 'created_by'] }
    ]
  });
  if (!entry) {
    return sendError(res, 'Nesting entry not found', 404);
  }
  if (companyId && entry.company_id !== companyId) {
    return sendError(res, 'You do not have access to this nesting entry', 403);
  }
  if (entry.status === 'COMPLETED') {
    return sendError(res, 'Completed nesting entry cannot be started again', 400);
  }

  await entry.update({
    status: 'IN_PROGRESS',
    updated_by: userId
  });
  await notifyNestingStatusChange({ entry, status: 'IN_PROGRESS', userId });
  sendResponse(res, 200, true, 'Nesting entry started successfully', entry);
});

exports.completeNestingEntry = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;
  const entry = await db.ProductionNesting.findByPk(id, {
    include: [
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'company_id', 'production_order_number', 'nesting_number', 'sent_to_nesting_by', 'created_by'] }
    ]
  });
  if (!entry) {
    return sendError(res, 'Nesting entry not found', 404);
  }
  if (companyId && entry.company_id !== companyId) {
    return sendError(res, 'You do not have access to this nesting entry', 403);
  }

  const requiredQty = toNumber(entry.required_quantity);
  const providedQty = req.body?.nesting_confirmed_quantity;
  if (providedQty == null || providedQty === '') {
    return sendError(res, 'Nesting Confirm Qty is required to complete nesting', 400);
  }
  const confirmedQty = toFixed3(Math.max(toNumber(providedQty), 0));
  if (confirmedQty <= 0) {
    return sendError(res, 'Nesting Confirm Qty must be greater than 0 to complete nesting', 400);
  }
  const scrapQty = toFixed3(Math.max(requiredQty - confirmedQty, 0));
  const scrapPct = requiredQty > 0 ? toFixed3((scrapQty / requiredQty) * 100) : 0;

  const updateData = {
    nesting_confirmed_quantity: confirmedQty,
    scrap_quantity: scrapQty,
    scrap_percentage: scrapPct,
    total_required_quantity: toFixed3(requiredQty),
    status: 'COMPLETED',
    updated_by: userId
  };

  if (req.file) {
    const absoluteUploads = path.join(__dirname, '../uploads').replace(/\\/g, '/');
    const normalizedPath = req.file.path.replace(/\\/g, '/');
    const relativePath = normalizedPath.startsWith(absoluteUploads)
      ? normalizedPath.slice(absoluteUploads.length)
      : `/nesting/${path.basename(normalizedPath)}`;
    updateData.nesting_file_path = `/uploads${relativePath.startsWith('/') ? relativePath : `/${relativePath}`}`;
    updateData.nesting_file_name = req.file.originalname;
    updateData.nesting_file_type = req.file.mimetype;
    updateData.nesting_file_size = req.file.size;
  }

  await entry.update(updateData);
  await notifyNestingStatusChange({ entry, status: 'COMPLETED', userId });
  const updated = await db.ProductionNesting.findByPk(id, {
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'thickness', 'length', 'width', 'height', 'dimension_unit']
      }
    ]
  });
  sendResponse(res, 200, true, 'Nesting entry completed successfully', updated);
});

/** PATCH line status for nesting → stores raw material register (PENDING | COMPLETED). */
exports.patchNestingStoresRequestLine = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { line_status: lineStatus } = req.body || {};
  const companyId = req.user?.companyId;

  const line = await db.NestingStoresRequestLine.findByPk(id, {
    include: [
      {
        model: db.NestingStoresRequest,
        as: 'request',
        attributes: ['id', 'company_id', 'production_order_id']
      }
    ]
  });
  if (!line) return sendError(res, 'Nesting stores line not found', 404);
  if (companyId && line.request && line.request.company_id !== companyId) {
    return sendError(res, 'You do not have access to this record', 403);
  }

  const next = String(lineStatus || '').toUpperCase();
  if (next !== 'COMPLETED' && next !== 'PENDING') {
    return sendError(res, 'line_status must be PENDING or COMPLETED', 400);
  }

  await line.update({
    line_status: next,
    completed_at: next === 'COMPLETED' ? new Date() : null
  });

  const fresh = await db.NestingStoresRequestLine.findByPk(id, {
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'net_weight', 'gross_weight', 'weight_uom_id'],
        required: false,
        include: [{ model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code'], required: false }]
      }
    ]
  });
  sendResponse(res, 200, true, 'Nesting stores line updated', fresh);
});
