const db = require('../models');
const { Op } = require('sequelize');
const atpService = require('./atpService');

/** Statuses whose batch quantity counts toward MRP / material demand */
const RELEASED_FOR_MRP_STATUSES = new Set(['RELEASED', 'IN_PROGRESS']);

/**
 * When the PO has no batch lines, MRP demand follows header release (same rule as productionOrderController list).
 */
const RELEASED_MRP_PO_HEADER_STATUSES = new Set([
  'RELEASED',
  'IN_PROGRESS',
  'PARTIALLY_CONFIRMED',
  'CONFIRMED',
  'CLOSED'
]);

const round3 = (n) => Math.round(parseFloat(n) * 1000) / 1000;

/** Split total into fixed lot sizes (last lot may be smaller). Used when auto-generating PO batches. */
const splitOrderIntoLots = (totalQty, lotSize) => {
  const total = parseFloat(totalQty);
  const lot = parseFloat(lotSize);
  if (!Number.isFinite(total) || total <= 0 || !Number.isFinite(lot) || lot <= 0) return [];
  const out = [];
  let remaining = total;
  const epsilon = 0.0005;
  while (remaining > epsilon) {
    const q = Math.min(lot, remaining);
    out.push(round3(q));
    remaining = round3(remaining - q);
  }
  return out;
};

const sumReleasedBatchQuantity = (batches = []) =>
  batches
    .filter((b) => RELEASED_FOR_MRP_STATUSES.has(String(b.status || '').toUpperCase()))
    .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);

/**
 * Quantity that drives MRP / reservations for batch-managed POs: only the latest "release wave"
 * (batches sharing the max released_at). Earlier RELEASED/IN_PROGRESS batches are ignored so a
 * follow-up batch release does not re-plan materials already consumed for prior waves.
 * Batches without released_at fall back to highest batch_sequence only.
 */
const sumMrpRecentReleaseBatchQuantity = (batches = []) => {
  const active = (Array.isArray(batches) ? batches : []).filter((b) =>
    RELEASED_FOR_MRP_STATUSES.has(String(b.status || '').toUpperCase())
  );
  if (active.length === 0) return 0;

  const releasedMs = (b) => {
    if (!b.released_at) return null;
    const t = new Date(b.released_at).getTime();
    return Number.isFinite(t) ? t : null;
  };

  const times = active.map(releasedMs).filter((t) => t != null);
  if (times.length > 0) {
    const maxT = Math.max(...times);
    return active
      .filter((b) => {
        const t = releasedMs(b);
        return t != null && t === maxT;
      })
      .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
  }

  const maxSeq = Math.max(...active.map((b) => parseInt(b.batch_sequence, 10) || 0), 0);
  return active
    .filter((b) => (parseInt(b.batch_sequence, 10) || 0) === maxSeq)
    .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
};

/**
 * Scale 0..1 for component qty vs production order BOM lines.
 * - With batch lines: latest release wave qty / order qty (not cumulative over older released batches).
 * - Without batch lines and batch_planning_enabled: always 0 (MRP follows released batches only; matches list "Released (MRP)").
 * - Without batch lines and not batch-managed: if productionOrderStatus is provided, 1 only when PO header is released-like.
 *   If productionOrderStatus is omitted, returns 1 when order qty > 0 (legacy callers e.g. stock reservation on release).
 */
const mrpScaleFromBatches = (
  orderQuantity,
  batchPlanningEnabled,
  batches = [],
  productionOrderStatus = undefined
) => {
  const oq = parseFloat(orderQuantity || 0);
  const bList = Array.isArray(batches) ? batches : [];
  if (bList.length > 0) {
    if (oq <= 0) return 0;
    const released = sumMrpRecentReleaseBatchQuantity(bList);
    return Math.max(0, Math.min(1, released / oq));
  }
  if (batchPlanningEnabled) {
    return 0;
  }
  if (productionOrderStatus === undefined) {
    return oq > 0 ? 1 : 0;
  }
  const st = String(productionOrderStatus || '').toUpperCase();
  if (RELEASED_MRP_PO_HEADER_STATUSES.has(st)) {
    return oq > 0 ? 1 : 0;
  }
  return 0;
};

/**
 * Recursive BOM explosion for MRP
 * @param {number} bomId - BOM ID
 * @param {number} orderQuantity - Required quantity
 * @param {number} baseQuantity - BOM base quantity
 * @param {number} companyId - Company ID
 * @param {Set} processedBOMs - Set to track processed BOMs (prevent infinite loops)
 * @param {number} level - Current BOM level
 * @returns {Promise<Array>} Array of components with required quantities
 */
const explodeBOMForMRP = async (bomId, orderQuantity, baseQuantity, companyId, processedBOMs = new Set(), level = 0) => {
  // Prevent infinite loops
  if (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_number',
              'description',
              'material_type',
              'base_uom_id',
              'material_group_id',
              'material_group'
            ],
            include: [
              {
                model: db.MaterialGroup,
                as: 'materialGroup',
                attributes: ['id', 'group_code', 'group_name'],
                required: false
              }
            ]
          },
          { 
            model: db.UOM, 
            as: 'componentUOM',
            attributes: ['id', 'uom_code', 'uom_name']
          }
        ]
      }
    ]
  });

  if (!bom || !bom.components) {
    return [];
  }

  const multiplier = parseFloat(orderQuantity) / parseFloat(baseQuantity || 1);
  const components = [];

  for (const component of bom.components) {
    const componentMaterial = component.material;
    if (!componentMaterial) continue;

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

    // Add the component
    components.push({
      bom_component_id: component.id,
      material_id: component.material_id,
      material_number: componentMaterial.material_number,
      material_description: componentMaterial.description,
      material_type: componentMaterial.material_type,
      material_group_id: componentMaterial.material_group_id || null,
      material_group: componentMaterial.material_group || null,
      material_group_name: componentMaterial.materialGroup?.group_name || null,
      material_group_code: componentMaterial.materialGroup?.group_code || null,
      required_quantity: requiredQty,
      uom_id: componentUOMId,
      uom_code: component.componentUOM?.uom_code || '',
      issue_type: component.issue_type || 'MANUAL',
      storage_location: component.storage_location || null,
      level: level,
      parent_material_id: level > 0 ? bom.material_id : null
    });

    // Check if this component material has its own BOM (it's a sub-assembly)
    const isSubAssembly = componentMaterial.material_type === 'SEMI_FINISHED' || 
                          componentMaterial.material_type === 'FINISHED_GOOD';
    
    if (isSubAssembly) {
      // Find active BOM for this sub-assembly material
      const subBOM = await db.BOMHeader.findOne({
        where: {
          material_id: componentMaterial.id,
          company_id: companyId,
          status: 'ACTIVE'
        },
        order: [['id', 'DESC']],
        limit: 1
      });

      if (subBOM) {
        // Recursively explode the sub-BOM
        const subComponents = await explodeBOMForMRP(
          subBOM.id,
          requiredQty,
          subBOM.base_quantity || 1,
          companyId,
          processedBOMs,
          level + 1
        );
        
        // Add all sub-components to the result
        components.push(...subComponents);
      }
    }
  }

  return components;
};

/**
 * Run MRP calculation for Customer PO items or Production Orders
 * @param {Object} params - MRP parameters
 * @param {number} params.companyId - Company ID
 * @param {number} params.plantId - Plant ID
 * @param {Array<number>} params.customerPOIds - Array of Customer PO IDs (optional)
 * @param {Array<number>} params.productionOrderIds - Array of Production Order IDs (optional)
 * @param {Date} params.planningDate - Planning date (optional, defaults to today)
 * @param {boolean} params.useNestingConfirmedQty - Use nesting confirmed qty for RAW_MATERIAL in production orders
 * @returns {Promise<Object>} MRP results with material requirements and shortages
 */
const runMRP = async ({
  companyId,
  plantId,
  customerPOIds = [],
  productionOrderIds = [],
  planningDate = new Date(),
  useNestingConfirmedQty = false
}) => {
  const hasCustomerPOScope = Array.isArray(customerPOIds) && customerPOIds.length > 0;
  const hasProductionOrderScope = Array.isArray(productionOrderIds) && productionOrderIds.length > 0;
  /** Customer PO line keys `${customer_po_id}:${customer_po_item_id}` supplied by selected prod orders — skip PO explosion so batch-aware prod order path drives planned qty. */
  let poItemKeysCoveredBySelectedProdOrders = new Set();
  if (hasCustomerPOScope && hasProductionOrderScope) {
    const linkedRows = await db.ProductionOrder.findAll({
      where: {
        id: { [Op.in]: productionOrderIds },
        company_id: companyId,
        plant_id: plantId
      },
      attributes: ['id', 'customer_po_id', 'customer_po_item_id']
    });
    for (const row of linkedRows) {
      const cpId = row.customer_po_id;
      const cpiId = row.customer_po_item_id;
      if (cpId != null && cpiId != null) {
        poItemKeysCoveredBySelectedProdOrders.add(`${parseInt(cpId, 10)}:${parseInt(cpiId, 10)}`);
      }
    }
  }
  const effectiveProductionOrderIds = hasProductionOrderScope ? productionOrderIds : [];

  const materialRequirements = new Map(); // material_id -> { total_required, sources, material_info }
  const errors = [];
  const processedBOMs = new Set();

  // Process Customer PO items
  if (customerPOIds && customerPOIds.length > 0) {
    const customerPOs = await db.CustomerPO.findAll({
      where: {
        id: { [Op.in]: customerPOIds },
        company_id: companyId,
        plant_id: plantId,
        status: { [Op.in]: ['APPROVED', 'RELEASED', 'PARTIALLY_DELIVERED'] }
      },
      include: [
        {
          model: db.CustomerPOItem,
          as: 'items',
          include: [
            {
              model: db.Material,
              as: 'material',
              attributes: [
                'id',
                'material_number',
                'description',
                'material_type',
                'material_group_id',
                'material_group'
              ],
              include: [
                {
                  model: db.MaterialGroup,
                  as: 'materialGroup',
                  attributes: ['id', 'group_code', 'group_name'],
                  required: false
                }
              ]
            },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    for (const customerPO of customerPOs) {
      for (const item of customerPO.items || []) {
        const material = item.material;
        if (!material) {
          errors.push(`Customer PO ${customerPO.po_number}, Item ${item.item_number}: Material not found`);
          continue;
        }

        // Check if material is finished good or semi-finished
        const isFinishedGood = material.material_type === 'FINISHED_GOOD';
        const isSemiFinished = material.material_type === 'SEMI_FINISHED';

        if (!isFinishedGood && !isSemiFinished) {
          continue; // Skip raw materials in Customer PO
        }

        const poItemKey = `${parseInt(customerPO.id, 10)}:${parseInt(item.id, 10)}`;
        if (poItemKeysCoveredBySelectedProdOrders.has(poItemKey)) {
          continue; // Same demand comes from selected production order (latest release wave / batches)
        }

        // Find active BOM for this material
        const bom = await db.BOMHeader.findOne({
          where: {
            material_id: material.id,
            company_id: companyId,
            status: 'ACTIVE'
          },
          order: [['id', 'DESC']],
          limit: 1
        });

        if (!bom) {
          errors.push(`Customer PO ${customerPO.po_number}, Item ${item.item_number}: No active BOM found for material ${material.material_number}`);
          continue;
        }

        // Explode BOM
        const components = await explodeBOMForMRP(
          bom.id,
          parseFloat(item.quantity || 0),
          bom.base_quantity || 1,
          companyId,
          new Set(processedBOMs),
          0
        );

        // Aggregate requirements
        for (const comp of components) {
          const existing = materialRequirements.get(comp.material_id);
          const requiredQty = parseFloat(comp.required_quantity || 0);

          if (existing) {
            existing.total_required += requiredQty;
            existing.sources.push({
              type: 'CUSTOMER_PO',
              customer_po_id: customerPO.id,
              customer_po_number: customerPO.po_number,
              customer_po_item_id: item.id,
              material_id: material.id,
              material_number: material.material_number,
              quantity: parseFloat(item.quantity || 0),
              required_quantity: requiredQty,
              delivery_date: item.delivery_date,
              level: comp.level
            });
          } else {
            materialRequirements.set(comp.material_id, {
              material_id: comp.material_id,
              material_number: comp.material_number,
              material_description: comp.material_description,
              material_type: comp.material_type,
              material_group_id: comp.material_group_id || null,
              material_group: comp.material_group || null,
              material_group_name: comp.material_group_name || null,
              material_group_code: comp.material_group_code || null,
              uom_id: comp.uom_id,
              uom_code: comp.uom_code,
              total_required: requiredQty,
              sources: [{
                type: 'CUSTOMER_PO',
                customer_po_id: customerPO.id,
                customer_po_number: customerPO.po_number,
                customer_po_item_id: item.id,
                material_id: material.id,
                material_number: material.material_number,
                quantity: parseFloat(item.quantity || 0),
                required_quantity: requiredQty,
                delivery_date: item.delivery_date,
                level: comp.level
              }]
            });
          }
        }
      }
    }
  }

  // Process Production Orders
  if (effectiveProductionOrderIds && effectiveProductionOrderIds.length > 0) {
    let nestingQtyByPoMaterial = new Map();
    if (useNestingConfirmedQty) {
      const nestingRows = await db.ProductionNesting.findAll({
        where: {
          company_id: companyId,
          plant_id: plantId,
          production_order_id: { [Op.in]: effectiveProductionOrderIds }
        },
        attributes: ['production_order_id', 'material_id', 'nesting_confirmed_quantity']
      });

      nestingQtyByPoMaterial = nestingRows.reduce((acc, row) => {
        const poId = parseInt(row.production_order_id, 10);
        const materialId = parseInt(row.material_id, 10);
        const qty = parseFloat(row.nesting_confirmed_quantity || 0);
        const key = `${poId}:${materialId}`;
        acc.set(key, (acc.get(key) || 0) + (Number.isNaN(qty) ? 0 : qty));
        return acc;
      }, new Map());
    }

    const productionOrders = await db.ProductionOrder.findAll({
      where: {
        id: { [Op.in]: effectiveProductionOrderIds },
        company_id: companyId,
        plant_id: plantId,
        status: { [Op.in]: ['DRAFT', 'RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED'] }
      },
      include: [
        {
          model: db.ProductionOrderComponent,
          as: 'components',
          required: false,
          separate: true,
          order: [['id', 'ASC']],
          include: [
            {
              model: db.Material,
              as: 'material',
              attributes: ['id', 'material_number', 'description', 'material_type']
            },
            { model: db.UOM, as: 'uom' }
          ]
        },
        {
          model: db.Material,
          as: 'finishedMaterial',
          attributes: ['id', 'material_number', 'description']
        }
      ]
    });

    // Load batches in a dedicated query — eager-loading batches + components on one findAll often drops batches
    // (join explosion / Sequelize grouping), which made mrpScale stay 1 and planned qty follow full order qty.
    const poIdsForBatches = productionOrders.map((p) => p.id).filter(Boolean);
    const batchRows =
      poIdsForBatches.length > 0
        ? await db.ProductionOrderBatch.findAll({
            where: { production_order_id: { [Op.in]: poIdsForBatches } },
            attributes: ['id', 'production_order_id', 'quantity', 'status', 'batch_sequence', 'released_at'],
            order: [
              ['production_order_id', 'ASC'],
              ['batch_sequence', 'ASC']
            ]
          })
        : [];
    const batchesByPoId = batchRows.reduce((map, row) => {
      const pid = parseInt(row.production_order_id, 10);
      if (Number.isNaN(pid)) return map;
      if (!map.has(pid)) map.set(pid, []);
      map.get(pid).push(row);
      return map;
    }, new Map());

    for (const po of productionOrders) {
      const poIdNum = parseInt(po.id, 10);
      const batchesForPo = (Number.isNaN(poIdNum) ? [] : batchesByPoId.get(poIdNum)) || [];
      const productionOrderQtyVal = parseFloat(po.order_quantity || 0) || 0;
      const mrpScale = mrpScaleFromBatches(
        po.order_quantity,
        Boolean(po.batch_planning_enabled),
        batchesForPo,
        po.status
      );
      const qTotalActive = sumReleasedBatchQuantity(batchesForPo);
      const qNewWave = sumMrpRecentReleaseBatchQuantity(batchesForPo);
      const qOldActive =
        batchesForPo.length > 0 ? Math.max(0, round3(qTotalActive - qNewWave)) : 0;
      const releasedBatchQtyForMrpDisplay =
        batchesForPo.length > 0
          ? qNewWave
          : po.batch_planning_enabled
            ? 0
            : RELEASED_MRP_PO_HEADER_STATUSES.has(String(po.status || '').toUpperCase())
              ? productionOrderQtyVal
              : 0;

      for (const comp of po.components || []) {
        const material = comp.material;
        if (!material) continue;

        const isRawMaterial = String(material.material_type || '').toUpperCase() === 'RAW_MATERIAL';
        const defaultRequiredQty = parseFloat(comp.required_quantity || 0);
        let baseForScaling = defaultRequiredQty;

        if (useNestingConfirmedQty && isRawMaterial) {
          const key = `${parseInt(po.id, 10)}:${parseInt(comp.material_id, 10)}`;
          baseForScaling = parseFloat(nestingQtyByPoMaterial.get(key) || 0);
        }

        const oq = productionOrderQtyVal;
        const issuedQty = parseFloat(comp.issued_quantity || 0);
        let requiredQty;
        let netRequired;

        if (batchesForPo.length > 0 && oq > 0) {
          const requiredQtyNew = round3(baseForScaling * (qNewWave / oq));
          const priorCoveredGross = round3(baseForScaling * (qOldActive / oq));
          const issuedForNewWave = Math.max(0, round3(issuedQty - priorCoveredGross));
          requiredQty = requiredQtyNew;
          netRequired = Math.max(0, round3(requiredQtyNew - issuedForNewWave));
        } else {
          requiredQty = round3(baseForScaling * mrpScale);
          netRequired = Math.max(0, requiredQty - issuedQty);
        }

        const isFullyIssued = requiredQty > 0 && netRequired <= 0;
        // ERP behavior: even if a component is fully issued, users often want to SEE it in MRP results
        // for reference (status/stock visibility). We include it with total_required = 0 so it won't
        // create any further requirement, but it can still appear as "Available/Covered".
        const qtyToPlan = isFullyIssued ? 0 : netRequired;

        // If truly nothing is required (requiredQty <= 0), skip.
        if (requiredQty <= 0) continue;

        const existing = materialRequirements.get(comp.material_id);

        if (existing) {
          existing.total_required += qtyToPlan;
          existing.sources.push({
            type: 'PRODUCTION_ORDER',
            production_order_id: po.id,
            production_order_number: po.production_order_number,
            finished_material_id: po.finished_material_id,
            finished_material_number: po.finishedMaterial?.material_number,
            required_quantity: qtyToPlan,
            total_required: requiredQty,
            issued_quantity: issuedQty,
            fully_issued: isFullyIssued,
            requirement_source: useNestingConfirmedQty && isRawMaterial ? 'NESTING_CONFIRMED' : 'PRODUCTION_ORDER_COMPONENT',
            batch_mrp_scale: mrpScale,
            batch_planning_enabled: Boolean(po.batch_planning_enabled),
            production_order_quantity: productionOrderQtyVal,
            released_batch_qty_for_mrp: releasedBatchQtyForMrpDisplay
          });
        } else {
          materialRequirements.set(comp.material_id, {
            material_id: comp.material_id,
            material_number: material.material_number,
            material_description: material.description,
            material_type: material.material_type,
            material_group_id: material.material_group_id || null,
            material_group: material.material_group || null,
            material_group_name: material.materialGroup?.group_name || null,
            material_group_code: material.materialGroup?.group_code || null,
            uom_id: comp.uom_id,
            uom_code: comp.uom?.uom_code || '',
            total_required: qtyToPlan,
            sources: [{
              type: 'PRODUCTION_ORDER',
              production_order_id: po.id,
              production_order_number: po.production_order_number,
              finished_material_id: po.finished_material_id,
              finished_material_number: po.finishedMaterial?.material_number,
              required_quantity: qtyToPlan,
              total_required: requiredQty,
              issued_quantity: issuedQty,
              fully_issued: isFullyIssued,
              requirement_source: useNestingConfirmedQty && isRawMaterial ? 'NESTING_CONFIRMED' : 'PRODUCTION_ORDER_COMPONENT',
              batch_mrp_scale: mrpScale,
              batch_planning_enabled: Boolean(po.batch_planning_enabled),
              production_order_quantity: productionOrderQtyVal,
              released_batch_qty_for_mrp: releasedBatchQtyForMrpDisplay
            }]
          });
        }
      }
    }
  }

  // Convert Map to Array and check stock availability
  const mrpResults = [];
  const shortages = [];

  // Debug: Check if material ID 63 is in requirements
  const material63InRequirements = Array.from(materialRequirements.keys()).includes(63);
  console.log(`[MRP Service] Material ID 63 in requirements: ${material63InRequirements}`);
  if (material63InRequirements) {
    const req63 = materialRequirements.get(63);
    console.log(`[MRP Service] Material ID 63 requirement details:`, {
      material_number: req63?.material_number,
      total_required: req63?.total_required,
      material_type: req63?.material_type
    });
  }

  for (const [materialId, requirement] of materialRequirements.entries()) {
    // Check stock availability
    const stockInfo = await atpService.getAvailableStock(
      materialId,
      plantId,
      null // storage location - can be enhanced later
    );

    const available = stockInfo.available || 0;

    // Debug logging for material ID 63
    if (materialId === 63) {
      console.log(`[MRP Service] Material ID 63 - Stock Info:`, {
        available,
        balanceQuantity: stockInfo.balanceQuantity,
        receipts: stockInfo.receipts,
        issues: stockInfo.issues,
        reserved: stockInfo.reserved,
        material_number: requirement.material_number
      });
    }
    const required = parseFloat(requirement.total_required || 0);
    const balance = Math.max(0, required - available);
    const isAvailable = available >= required;
    const isShortage = balance > 0;

    const mt = String(requirement.material_type || '').toUpperCase();
    let mrp_flow_category = 'OTHER';
    if (mt === 'RAW_MATERIAL') mrp_flow_category = 'RAW_SHEET';
    else if (mt === 'CONSUMABLE' || mt === 'PACKAGING') mrp_flow_category = 'FASTENER';

    const result = {
      ...requirement,
      mrp_flow_category,
      available_stock: available,
      required_quantity: required,
      balance_quantity: balance,
      is_available: isAvailable,
      is_shortage: isShortage,
      stock_receipts: stockInfo.receipts || 0,
      stock_issues: stockInfo.issues || 0,
      reserved_quantity: stockInfo.reserved || 0,
      available_locations: stockInfo.locations || [],
      planning_date: planningDate
    };

    // Debug: Log first few items to verify available_stock is set
    if (mrpResults.length < 3) {
      console.log(`[MRP Service] Material ${requirement.material_number} (ID: ${materialId}):`, {
        available_stock: result.available_stock,
        stock_receipts: result.stock_receipts,
        stock_issues: result.stock_issues,
        reserved_quantity: result.reserved_quantity,
        required_quantity: result.required_quantity
      });
    }

    mrpResults.push(result);

    if (isShortage) {
      shortages.push({
        ...result,
        shortfall: balance
      });
    }
  }

  // Sort by material type and material number
  mrpResults.sort((a, b) => {
    // First by material type (RAW_MATERIAL first, then SEMI_FINISHED)
    const typeOrder = { 'RAW_MATERIAL': 1, 'SEMI_FINISHED': 2, 'FINISHED_GOOD': 3, 'OTHER': 4 };
    const aType = typeOrder[a.material_type] || 4;
    const bType = typeOrder[b.material_type] || 4;
    if (aType !== bType) return aType - bType;
    
    // Then by material number
    return (a.material_number || '').localeCompare(b.material_number || '');
  });

  shortages.sort((a, b) => {
    const typeOrder = { 'RAW_MATERIAL': 1, 'SEMI_FINISHED': 2, 'FINISHED_GOOD': 3, 'OTHER': 4 };
    const aType = typeOrder[a.material_type] || 4;
    const bType = typeOrder[b.material_type] || 4;
    if (aType !== bType) return aType - bType;
    return (a.material_number || '').localeCompare(b.material_number || '');
  });

  return {
    mrpResults,
    shortages,
    summary: {
      total_materials: mrpResults.length,
      materials_with_shortage: shortages.length,
      total_required_value: mrpResults.reduce((sum, r) => sum + parseFloat(r.required_quantity || 0), 0),
      total_shortage_value: shortages.reduce((sum, s) => sum + parseFloat(s.balance_quantity || 0), 0),
      planning_date: planningDate
    },
    errors: errors.length > 0 ? errors : null
  };
};

module.exports = {
  runMRP,
  explodeBOMForMRP,
  splitOrderIntoLots,
  sumReleasedBatchQuantity,
  sumMrpRecentReleaseBatchQuantity,
  mrpScaleFromBatches
};

