const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const notificationService = require('../services/notificationService');
const statusValidationService = require('../services/statusValidationService');
const businessRuleValidationService = require('../services/businessRuleValidationService');
const { generateNextNumber } = require('../services/numberFormatService');
const { splitOrderIntoLots, mrpScaleFromBatches, sumReleasedBatchQuantity } = require('../services/mrpService');

const generatePONumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PRODUCTION_ORDER', fiscalYearId, transaction);
  } catch (err) {
    console.warn('Production order number from format master failed, using fallback:', err.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `PRO${year}`;
    const lastPO = await db.ProductionOrder.findOne({
      where: { company_id: companyId, production_order_number: { [Op.like]: `${prefix}%` } },
      order: [['production_order_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (lastPO) {
      const lastSequence = parseInt(lastPO.production_order_number.slice(-4)) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
};

const computeNestingHeaderStatus = (nestingRows = []) => {
  if (!Array.isArray(nestingRows) || nestingRows.length === 0) return 'PENDING';
  const statuses = nestingRows.map((r) => String(r?.status || '').toUpperCase());
  if (statuses.every((s) => s === 'COMPLETED')) return 'COMPLETED';
  if (statuses.some((s) => s === 'IN_PROGRESS')) return 'IN_PROGRESS';
  if (statuses.some((s) => s === 'COMPLETED')) return 'IN_PROGRESS';
  if (statuses.every((s) => s === 'CANCELLED')) return 'CANCELLED';
  return 'PENDING';
};

/** Matches getById — use after release/update so UI receives batches, batch_planning_enabled, components. */
const getProductionOrderDetailInclude = () => [
  { model: db.Company, as: 'company' },
  { model: db.Plant, as: 'plant' },
  { model: db.FiscalYear, as: 'fiscalYear' },
  { model: db.Material, as: 'finishedMaterial' },
  { model: db.BOMHeader, as: 'bom' },
  { model: db.Routing, as: 'routing' },
  { model: db.UOM, as: 'uom' },
  {
    model: db.CustomerPO,
    as: 'customerPO',
    attributes: ['id', 'po_number', 'po_date', 'status', 'delivery_date'],
    required: false,
    include: [
      {
        model: db.BusinessPartner,
        as: 'customer',
        attributes: ['id', 'bp_number', 'name', 'legal_name'],
        required: false,
      },
    ],
  },
  {
    model: db.CustomerPOItem,
    as: 'customerPOItem',
    attributes: ['id', 'item_number', 'quantity', 'delivery_date'],
    required: false,
  },
  {
    model: db.ProductionOrderBatch,
    as: 'batches',
    required: false,
    separate: true,
    order: [['batch_sequence', 'ASC']],
    include: [{ model: db.User, as: 'releasedByUser', attributes: ['id', 'username', 'email'], required: false }]
  },
  {
    model: db.ProductionOrderComponent,
    as: 'components',
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: [
          'id',
          'material_number',
          'description',
          'material_type',
          'material_type_id',
          'gross_weight',
          'net_weight',
          'weight_uom_id',
          'thickness',
          'thickness_id',
          'grade_id'
        ],
        include: [
          { model: db.MaterialThickness, as: 'materialThickness', attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit', 'description'] },
          { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name', 'description'] },
          { model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code', 'uom_name'] }
        ]
      },
      { model: db.UOM, as: 'uom' },
      { model: db.BOMComponent, as: 'bomComponent' }
    ],
    order: [['id', 'ASC']]
  }
];

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

/** Raw sheet (nesting path) vs fasteners — used for material receipt line_type. */
const materialFlowLineTypeFromMaterial = (material) => {
  if (!material) return null;
  const t = String(material.material_type || '').toUpperCase();
  if (t === 'RAW_MATERIAL') return 'RAW';
  if (t === 'CONSUMABLE' || t === 'PACKAGING') return 'FASTENER';
  return null;
};

const FLOW_QTY_EPS = 0.0005;

const BATCH_STATUSES = ['PLANNED', 'RELEASED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED'];

/**
 * Replace batch lines for a production order (manual rows and/or auto-split from standard lot size).
 * When replace is false, only updates scalar fields on the PO without touching batch lines.
 */
const persistProductionOrderBatches = async (productionOrder, input, transaction, { replace = true } = {}) => {
  if (!replace) {
    const upd = {};
    if (input.standard_batch_quantity !== undefined) {
      const v =
        input.standard_batch_quantity != null && input.standard_batch_quantity !== ''
          ? parseFloat(input.standard_batch_quantity)
          : null;
      upd.standard_batch_quantity = Number.isFinite(v) && v > 0 ? v : null;
    }
    if (input.batch_planning_enabled !== undefined) {
      upd.batch_planning_enabled = Boolean(input.batch_planning_enabled);
    }
    if (Object.keys(upd).length) {
      await productionOrder.update(upd, { transaction });
    }
    return;
  }

  const orderQty = parseFloat(productionOrder.order_quantity || 0);
  await db.ProductionOrderBatch.destroy({
    where: { production_order_id: productionOrder.id },
    transaction
  });

  const rowsToCreate = [];
  let planningEnabled = Boolean(input.batch_planning_enabled);

  const stdFromInput =
    input.standard_batch_quantity != null && input.standard_batch_quantity !== ''
      ? parseFloat(input.standard_batch_quantity)
      : NaN;
  const stdEffective = Number.isFinite(stdFromInput) && stdFromInput > 0
    ? stdFromInput
    : parseFloat(productionOrder.standard_batch_quantity || 0);
  const stdOk = Number.isFinite(stdEffective) && stdEffective > 0 ? stdEffective : null;

  if (input.regenerate_batches_from_standard_lot && stdOk && orderQty > 0) {
    planningEnabled = true;
    splitOrderIntoLots(orderQty, stdOk).forEach((qty, i) => {
      rowsToCreate.push({
        production_order_id: productionOrder.id,
        batch_sequence: i + 1,
        batch_label: `Batch ${i + 1}`,
        quantity: qty,
        planned_start_date: null,
        planned_finish_date: null,
        status: 'PLANNED',
        remarks: null
      });
    });
  } else if (Array.isArray(input.batches)) {
    if (input.batches.length > 0) planningEnabled = true;
    const sorted = [...input.batches].sort(
      (a, b) => (parseInt(a.batch_sequence, 10) || 0) - (parseInt(b.batch_sequence, 10) || 0)
    );
    sorted.forEach((b, idx) => {
      const qty = parseFloat(b.quantity || 0);
      if (!Number.isFinite(qty) || qty <= 0) return;
      const st = String(b.status || 'PLANNED').toUpperCase();
      const status = BATCH_STATUSES.includes(st) ? st : 'PLANNED';
      rowsToCreate.push({
        production_order_id: productionOrder.id,
        batch_sequence: b.batch_sequence != null ? parseInt(b.batch_sequence, 10) : idx + 1,
        batch_label: b.batch_label || null,
        quantity: qty,
        planned_start_date: b.planned_start_date || null,
        planned_finish_date: b.planned_finish_date || null,
        status,
        released_at: b.released_at || null,
        released_by: b.released_by != null ? parseInt(b.released_by, 10) : null,
        remarks: b.remarks || null
      });
    });
  } else if (planningEnabled && stdOk && orderQty > 0) {
    splitOrderIntoLots(orderQty, stdOk).forEach((qty, i) => {
      rowsToCreate.push({
        production_order_id: productionOrder.id,
        batch_sequence: i + 1,
        batch_label: `Batch ${i + 1}`,
        quantity: qty,
        planned_start_date: null,
        planned_finish_date: null,
        status: 'PLANNED',
        remarks: null
      });
    });
  }

  const sumBatchQty = rowsToCreate.reduce((s, r) => s + parseFloat(r.quantity || 0), 0);
  if (rowsToCreate.length > 0 && sumBatchQty - orderQty > 0.001) {
    throw new Error(
      `Sum of batch quantities (${sumBatchQty}) cannot exceed order quantity (${orderQty}).`
    );
  }

  for (const row of rowsToCreate) {
    await db.ProductionOrderBatch.create(row, { transaction });
  }

  let nextStd = productionOrder.standard_batch_quantity;
  if (input.standard_batch_quantity !== undefined) {
    nextStd = stdOk;
  }

  await productionOrder.update(
    {
      standard_batch_quantity: nextStd,
      batch_planning_enabled: planningEnabled && rowsToCreate.length > 0
    },
    { transaction }
  );
};

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

  const where = {};
  
  if (companyId) {
    where.company_id = companyId;
  } else if (req.user && !req.user.isSuperAdmin) {
    where.company_id = req.user.companyId;
  }

  if (plantId) {
    where.plant_id = plantId;
  }

  if (fiscalYearId) {
    where.fiscal_year_id = fiscalYearId;
  }

  if (materialId) {
    where.finished_material_id = materialId;
  }

  if (status) {
    // Handle comma-separated status values (e.g., "DRAFT,RELEASED,IN_PROGRESS")
    if (status.includes(',')) {
      const statusArray = status.split(',').map(s => s.trim()).filter(s => s);
      where.status = { [Op.in]: statusArray };
    } else {
      where.status = status;
    }
  }

  if (search) {
    where[Op.or] = [
      { production_order_number: { [Op.like]: `%${search}%` } }
    ];
  }

  if (startDate || endDate) {
    where.production_order_date = {};
    if (startDate) where.production_order_date[Op.gte] = startDate;
    if (endDate) where.production_order_date[Op.lte] = endDate;
  }

  // Add customer_po_id filter if provided
  if (req.query.customer_po_id) {
    where.customer_po_id = req.query.customer_po_id;
  }

  // Optional filter for Work Order creation screen:
  // exclude Production Orders that are fully allocated in Work Orders.
  const excludeUsedInWorkOrder =
    String(exclude_used_in_work_order || '').toLowerCase() === 'true' ||
    String(exclude_used_in_work_order || '') === '1';
  if (excludeUsedInWorkOrder) {
    const candidatePOs = await db.ProductionOrder.findAll({
      where,
      attributes: ['id', 'order_quantity'],
      raw: true
    });

    const candidateIds = candidatePOs.map((row) => row.id).filter(Boolean);
    if (candidateIds.length > 0) {
      const resolvedCompanyId = where.company_id || companyId || req.user?.companyId;
      const woAllocRows = await db.WorkOrder.findAll({
        where: {
          production_order_id: { [Op.in]: candidateIds },
          ...(resolvedCompanyId ? { company_id: resolvedCompanyId } : {})
        },
        attributes: [
          'production_order_id',
          [db.sequelize.fn('SUM', db.sequelize.col('work_order_quantity')), 'allocated_qty']
        ],
        group: ['production_order_id'],
        raw: true
      });
      const allocatedByPOId = new Map(
        woAllocRows.map((row) => [
          row.production_order_id,
          parseFloat(row.allocated_qty || 0) || 0
        ])
      );
      const fullyAllocatedIds = candidatePOs
        .filter((po) => {
          const orderQty = parseFloat(po.order_quantity || 0) || 0;
          const allocated = allocatedByPOId.get(po.id) || 0;
          return orderQty > 0 && allocated >= orderQty;
        })
        .map((po) => po.id);
      if (fullyAllocatedIds.length > 0) {
        where.id = { [Op.notIn]: fullyAllocatedIds };
      }
    }
  }

  const records = await db.ProductionOrder.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
      { model: db.BOMHeader, as: 'bom', attributes: ['id', 'bom_number'] },
      {
        model: db.CustomerPO,
        as: 'customerPO',
        attributes: ['id', 'po_number', 'po_date'],
        required: false,
        include: [
          { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false }
        ]
      },
      { model: db.CustomerPOItem, as: 'customerPOItem', attributes: ['id', 'item_number'], required: false },
      { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['production_order_date', 'DESC'], ['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  const poItems = Array.isArray(response.items) ? response.items : [];
  const poIds = poItems.map((item) => item?.id).filter(Boolean);

  if (poIds.length > 0) {
    const batchStatus = (s) => String(s || '').toUpperCase();
    const batchRows = await db.ProductionOrderBatch.findAll({
      where: { production_order_id: { [Op.in]: poIds } },
      attributes: ['production_order_id', 'quantity', 'status'],
      raw: true
    });
    const batchesByPoId = batchRows.reduce((acc, r) => {
      const pid = r.production_order_id;
      if (!acc.has(pid)) acc.set(pid, []);
      acc.get(pid).push(r);
      return acc;
    }, new Map());

    const resolvedCompanyId = where.company_id || companyId || req.user?.companyId;
    const woAllocRows = await db.WorkOrder.findAll({
      where: {
        production_order_id: { [Op.in]: poIds },
        ...(resolvedCompanyId ? { company_id: resolvedCompanyId } : {})
      },
      attributes: [
        'production_order_id',
        [db.sequelize.fn('SUM', db.sequelize.col('work_order_quantity')), 'allocated_qty']
      ],
      group: ['production_order_id'],
      raw: true
    });
    const allocatedByPOId = new Map(
      woAllocRows.map((row) => [
        row.production_order_id,
        parseFloat(row.allocated_qty || 0) || 0
      ])
    );

    const nestingWhere = {
      production_order_id: { [Op.in]: poIds }
    };
    if (resolvedCompanyId) nestingWhere.company_id = resolvedCompanyId;

    const nestingRows = await db.ProductionNesting.findAll({
      where: nestingWhere,
      attributes: ['production_order_id', 'status', 'nesting_confirmed_quantity'],
      include: [
        {
          model: db.Material,
          as: 'material',
          attributes: ['id', 'material_type', 'sheet_type_id', 'length', 'width'],
          required: false,
          include: [
            {
              model: db.MaterialSheetType,
              as: 'materialSheetType',
              attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
              required: false
            }
          ]
        }
      ]
    });

    // Match Production Nesting list/detail: only raw-material nesting lines drive header status.
    const groupedByPO = nestingRows.reduce((acc, row) => {
      const materialType = String(row?.material?.material_type || '').toUpperCase();
      if (materialType !== 'RAW_MATERIAL') return acc;
      const poId = row.production_order_id;
      if (!acc.has(poId)) acc.set(poId, []);
      acc.get(poId).push(row);
      return acc;
    }, new Map());

    response.items = poItems.map((item) => {
      const row = item?.toJSON ? item.toJSON() : item;
      const rows = groupedByPO.get(row.id) || [];
      const confirmedTotal = rows.reduce((sum, n) => sum + (parseFloat(n.nesting_confirmed_quantity || 0) || 0), 0);
      const orderQty = parseFloat(row.order_quantity || 0) || 0;
      const bList = batchesByPoId.get(row.id) || [];
      const mrpReleased = bList
        .filter((b) => ['RELEASED', 'IN_PROGRESS'].includes(batchStatus(b.status)))
        .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
      const committedRelease = bList
        .filter((b) => ['RELEASED', 'IN_PROGRESS', 'COMPLETED'].includes(batchStatus(b.status)))
        .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
      const plannedBatchQty = bList
        .filter((b) => batchStatus(b.status) === 'PLANNED')
        .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);

      let released_for_mrp_qty = mrpReleased;
      let balance_to_release_qty = Math.max(0, orderQty - committedRelease);

      if (!row.batch_planning_enabled && bList.length === 0) {
        if (['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(row.status)) {
          released_for_mrp_qty = orderQty;
          balance_to_release_qty = 0;
        } else {
          released_for_mrp_qty = 0;
          balance_to_release_qty = orderQty;
        }
      }

      return {
        ...row,
        work_order_allocated_qty: Number((allocatedByPOId.get(row.id) || 0).toFixed(3)),
        work_order_balance_qty: Number(
          Math.max((parseFloat(row.order_quantity || 0) || 0) - (allocatedByPOId.get(row.id) || 0), 0).toFixed(3)
        ),
        nesting_status: row.is_sent_to_nesting ? computeNestingHeaderStatus(rows) : 'NOT_SENT',
        nesting_confirmed_quantity: Number(confirmedTotal.toFixed(3)),
        released_for_mrp_qty: Number(released_for_mrp_qty.toFixed(3)),
        balance_to_release_qty: Number(balance_to_release_qty.toFixed(3)),
        planned_batch_qty_total: Number(plannedBatchQty.toFixed(3))
      };
    });
  }

  // Add dispatch status based on Production PO quantity vs dispatched quantity.
  const poRows = Array.isArray(response.items) ? response.items : [];
  const poIdsForDispatch = poRows.map((r) => r?.id).filter(Boolean);
  if (poIdsForDispatch.length > 0 && db.FGDispatch) {
    let hasDispatchPoIdCol = false;
    try {
      const schema = await db.sequelize.getQueryInterface().describeTable('fg_dispatch');
      hasDispatchPoIdCol = Object.prototype.hasOwnProperty.call(schema || {}, 'production_order_id');
    } catch (_) {
      hasDispatchPoIdCol = false;
    }

    const dispatchedByPoId = new Map();

    if (hasDispatchPoIdCol) {
      const dispatchedRows = await db.FGDispatch.findAll({
        where: {
          production_order_id: { [Op.in]: poIdsForDispatch },
          status: { [Op.ne]: 'CANCELLED' }
        },
        attributes: [
          'production_order_id',
          [db.sequelize.fn('SUM', db.sequelize.col('dispatch_qty')), 'dispatched_qty']
        ],
        group: ['production_order_id'],
        raw: true
      });
      dispatchedRows.forEach((r) => {
        dispatchedByPoId.set(r.production_order_id, parseFloat(r.dispatched_qty || 0) || 0);
      });
    } else {
      const inwardRows = await db.ProductionFGInward.findAll({
        where: { production_order_id: { [Op.in]: poIdsForDispatch } },
        attributes: ['production_order_id', 'company_id', 'plant_id', 'fiscal_year_id', 'finished_material_id', 'storage_location_id']
      });

      const scopesByPoId = new Map();
      inwardRows.forEach((r) => {
        const poId = parseInt(r.production_order_id, 10);
        if (!Number.isFinite(poId) || poId <= 0) return;
        const key = `${r.company_id}:${r.plant_id}:${r.fiscal_year_id}:${r.finished_material_id}:${r.storage_location_id}`;
        if (!scopesByPoId.has(poId)) scopesByPoId.set(poId, new Set());
        scopesByPoId.get(poId).add(key);
      });

      for (const [poId, scopeKeys] of scopesByPoId.entries()) {
        let total = 0;
        // eslint-disable-next-line no-restricted-syntax
        for (const k of scopeKeys.values()) {
          const [company_id, plant_id, fiscal_year_id, item_id, warehouse_id] = k.split(':').map((n) => parseInt(n, 10));
          const qty = await db.FGDispatch.sum('dispatch_qty', {
            where: {
              company_id,
              plant_id,
              fiscal_year_id,
              item_id,
              warehouse_id,
              status: { [Op.ne]: 'CANCELLED' }
            }
          });
          total += parseFloat(qty || 0) || 0;
        }
        dispatchedByPoId.set(poId, total);
      }
    }

    response.items = poRows.map((row) => {
      const json = row?.toJSON ? row.toJSON() : row;
      const productionQty = parseFloat(json?.order_quantity || 0) || 0;
      const dispatchedQty = parseFloat(dispatchedByPoId.get(json.id) || 0) || 0;
      const balanceDispatchQty = Math.max(productionQty - dispatchedQty, 0);
      let dispatchStatus = 'NOT_DISPATCHED';
      if (dispatchedQty > 0 && productionQty > 0) {
        dispatchStatus = dispatchedQty >= productionQty ? 'FULLY_DISPATCHED' : 'PARTIALLY_DISPATCHED';
      } else if (dispatchedQty > 0 && productionQty <= 0) {
        dispatchStatus = 'PARTIALLY_DISPATCHED';
      }
      return {
        ...json,
        dispatched_qty: Number(dispatchedQty.toFixed(3)),
        balance_dispatch_qty: Number(balanceDispatchQty.toFixed(3)),
        dispatch_status: dispatchStatus
      };
    });
  }

  sendResponse(res, 200, true, 'Production Orders retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const po = await db.ProductionOrder.findByPk(id, {
    include: getProductionOrderDetailInclude()
  });

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

  const json = po.toJSON();
  const comps = Array.isArray(json.components) ? json.components : [];
  const mids = [...new Set(comps.map((c) => c.material_id).filter((mid) => mid != null))];
  const plantId = po.plant_id;
  const planDate = json.finish_date || json.start_date || json.production_order_date || null;

  const plantByMaterial = new Map();
  const materialFallback = new Map();
  if (mids.length && plantId) {
    const mps = await db.MaterialPlant.findAll({
      where: {
        material_id: { [Op.in]: mids },
        plant_id: plantId,
        status: 'ACTIVE'
      },
      attributes: ['material_id', 'storage_location']
    });
    mps.forEach((r) => {
      const code = r.storage_location && String(r.storage_location).trim();
      if (code) plantByMaterial.set(r.material_id, code);
    });
    const mats = await db.Material.findAll({
      where: { id: { [Op.in]: mids } },
      attributes: ['id', 'storage_location_id'],
      include: [
        {
          model: db.StorageLocation,
          as: 'storageLocation',
          attributes: ['storage_location_code'],
          required: false
        }
      ]
    });
    mats.forEach((m) => {
      const code =
        m.storageLocation?.storage_location_code && String(m.storageLocation.storage_location_code).trim();
      if (code) materialFallback.set(m.id, code);
    });
  }

  json.components = comps.map((c) => {
    const mid = c.material_id;
    const fromCode = plantByMaterial.get(mid) || materialFallback.get(mid) || null;
    const toCode = c.storage_location && String(c.storage_location).trim() ? String(c.storage_location).trim() : null;
    return {
      ...c,
      warehouse_from_code: fromCode,
      warehouse_to_code: toCode,
      plan_date: planDate
    };
  });

  const receiptRows = await db.ProductionOrderMaterialReceiptLine.findAll({
    where: { production_order_id: parseInt(id, 10) },
    include: [
      { model: db.StorageLocation, as: 'fromStorageLocation', attributes: ['id', 'storage_location_code', 'description'] },
      { model: db.StorageLocation, as: 'toStorageLocation', attributes: ['id', 'storage_location_code', 'description'] }
    ],
    order: [['line_type', 'ASC'], ['line_key', 'ASC']]
  });
  json.material_receipt_lines = receiptRows.map((r) => (r.toJSON ? r.toJSON() : r));

  const poIdInt = parseInt(id, 10);
  const nsrWhere = { production_order_id: poIdInt, company_id: json.company_id };
  const nestingStoresRequestRows = await db.NestingStoresRequest.findAll({
    where: nsrWhere,
    include: [
      {
        model: db.NestingStoresRequestLine,
        as: 'lines',
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'drawing_number'],
            required: false
          }
        ]
      }
    ],
    order: [['created_at', 'DESC']]
  });
  json.nesting_stores_requests = nestingStoresRequestRows.map((r) => (r.toJSON ? r.toJSON() : r));

  const postedIssuesForAgg = await db.MaterialIssue.findAll({
    where: { production_order_id: poIdInt, status: 'POSTED' },
    attributes: ['id'],
    include: [
      {
        model: db.MaterialIssueItem,
        as: 'items',
        attributes: ['material_id', 'quantity'],
        required: false
      }
    ]
  });
  const materialIssuedQtyByMaterialId = {};
  for (const iss of postedIssuesForAgg) {
    for (const it of iss.items || []) {
      const mid = it.material_id;
      if (mid == null) continue;
      const k = String(mid);
      materialIssuedQtyByMaterialId[k] = (materialIssuedQtyByMaterialId[k] || 0) + toNumber(it.quantity);
    }
  }
  json.material_issued_qty_by_material_id = materialIssuedQtyByMaterialId;

  // Received qty should follow actual Production "Material Receive" postings.
  // Source of truth: `material_issue_receive_item` (separate table), aggregated per BOM line (`production_order_component_id`).
  const receivedQtyByComponentId = {};
  const receivedQtyByMaterialId = {};
  try {
    if (db.MaterialIssueReceiveItem) {
      const receivedIssues = await db.MaterialIssue.findAll({
        where: { production_order_id: poIdInt, status: 'RECEIVED' },
        attributes: ['id'],
        include: [
          {
            model: db.MaterialIssueItem,
            as: 'items',
            attributes: ['id', 'material_id', 'production_order_component_id'],
            required: false,
          },
        ],
      });

      const issueItemIds = [];
      const issueItemToComponentId = {};
      const issueItemToMaterialId = {};
      for (const iss of receivedIssues) {
        for (const it of iss.items || []) {
          if (!it?.id) continue;
          issueItemIds.push(it.id);
          issueItemToComponentId[it.id] = it.production_order_component_id || null;
          issueItemToMaterialId[it.id] = it.material_id || null;
        }
      }

      if (issueItemIds.length > 0) {
        const recvRows = await db.MaterialIssueReceiveItem.findAll({
          where: { material_issue_item_id: { [Op.in]: issueItemIds } },
          attributes: [
            'material_issue_item_id',
            [db.Sequelize.fn('SUM', db.Sequelize.col('received_quantity')), 'received_qty_sum'],
          ],
          group: ['material_issue_item_id'],
          raw: true,
        });

        for (const rr of recvRows) {
          const itemId = rr.material_issue_item_id;
          const compId = issueItemToComponentId[itemId];
          const mid = issueItemToMaterialId[itemId];
          const qty = toNumber(rr.received_qty_sum);
          if (compId != null) {
            const k = String(compId);
            receivedQtyByComponentId[k] = (receivedQtyByComponentId[k] || 0) + qty;
          }
          if (mid != null) {
            const mk = String(mid);
            receivedQtyByMaterialId[mk] = (receivedQtyByMaterialId[mk] || 0) + qty;
          }
        }
      }
    } else {
      // Fallback to old receipt lines if receive tables aren't present.
      for (const r of receiptRows) {
        const mid = r.material_id;
        if (mid == null) continue;
        const k = String(mid);
        receivedQtyByMaterialId[k] = (receivedQtyByMaterialId[k] || 0) + toNumber(r.received_qty);
      }
    }
  } catch (e) {
    console.warn('Could not aggregate received qty from material_issue_receive_item, falling back.', e?.message || e);
    for (const r of receiptRows) {
      const mid = r.material_id;
      if (mid == null) continue;
      const k = String(mid);
      receivedQtyByMaterialId[k] = (receivedQtyByMaterialId[k] || 0) + toNumber(r.received_qty);
    }
  }
  json.material_received_qty_by_material_id = receivedQtyByMaterialId;

  const receiptMap = new Map();
  for (const r of receiptRows) {
    receiptMap.set(`${String(r.line_type || '').toUpperCase()}|${String(r.line_key || '').trim()}`, r);
  }

  json.components = json.components.map((c) => {
    const mat = c.material;
    const flowLt = materialFlowLineTypeFromMaterial(mat);
    let rec = null;
    if (flowLt && c.id) {
      rec =
        receiptMap.get(`${flowLt}|${String(c.id)}`) ||
        receiptMap.get(`${flowLt}|${c.id}`);
      if (!rec && flowLt === 'FASTENER' && c.material_id) {
        rec = receiptMap.get(`FASTENER|${String(c.material_id)}`);
      }
    }
    const issued = toNumber(c.issued_quantity);
    const req = toNumber(c.required_quantity);
    // Prefer receive totals by BOM line when available (correct for PO screen).
    const receivedQtyByComp =
      c.id != null ? toNumber(receivedQtyByComponentId[String(c.id)]) : 0;
    const receivedQtyByMat =
      c.material_id != null ? toNumber(receivedQtyByMaterialId[String(c.material_id)]) : 0;
    const receivedQty =
      receivedQtyByComp > 0 ? receivedQtyByComp : receivedQtyByMat > 0 ? receivedQtyByMat : rec ? toNumber(rec.received_qty) : 0;
    let material_flow_status = 'N_A';
    if (flowLt) {
      if (req <= FLOW_QTY_EPS) {
        material_flow_status = 'NOT_ISSUED';
      } else if (issued <= FLOW_QTY_EPS) {
        material_flow_status = 'NOT_ISSUED';
      } else if (receivedQty <= FLOW_QTY_EPS) {
        material_flow_status = 'ISSUED_NOT_RECEIVED';
      } else {
        material_flow_status = 'RECEIVED';
      }
    }
    const pendingReceiptQty = Math.max(0, req - receivedQty);
    const production_material_ready =
      !flowLt ||
      req <= FLOW_QTY_EPS ||
      (issued > FLOW_QTY_EPS && receivedQty + FLOW_QTY_EPS >= issued);

    const fromSl = rec?.fromStorageLocation;
    const toSl = rec?.toStorageLocation;
    return {
      ...c,
      material_flow_line_type: flowLt,
      received_quantity: Number(receivedQty.toFixed(3)),
      pending_receipt_quantity: Number(pendingReceiptQty.toFixed(3)),
      material_flow_status: material_flow_status,
      production_material_ready: production_material_ready,
      receipt_received_date: rec?.received_date || null,
      receipt_from_storage_location_id: rec?.from_storage_location_id || null,
      receipt_to_storage_location_id: rec?.to_storage_location_id || null,
      receipt_from_warehouse_label: fromSl
        ? [fromSl.storage_location_code, fromSl.description].filter(Boolean).join(' — ')
        : null,
      receipt_to_warehouse_label: toSl
        ? [toSl.storage_location_code, toSl.description].filter(Boolean).join(' — ')
        : null
    };
  });

  json.material_flow_summary = {
    not_issued: json.components.filter((c) => c.material_flow_status === 'NOT_ISSUED').length,
    issued_not_received: json.components.filter((c) => c.material_flow_status === 'ISSUED_NOT_RECEIVED').length,
    received: json.components.filter((c) => c.material_flow_status === 'RECEIVED').length,
    all_tracked_ready: json.components.every(
      (c) => !materialFlowLineTypeFromMaterial(c.material) || c.production_material_ready
    )
  };

  sendResponse(res, 200, true, 'Production Order retrieved successfully', json);
});

// Recursive BOM explosion function to handle multi-level BOMs
const explodeBOM = async (bomId, orderQuantity, baseQuantity, 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', 'width', 'height', 'length', 'thickness']
          },
          { 
            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,
      required_quantity: requiredQty,
      uom_id: componentUOMId,
      component_weight: component.component_weight ?? null,
      component_height: component.component_height ?? null,
      component_length: component.component_length ?? null,
      component_thickness: component.component_thickness ?? null,
      issue_type: component.issue_type || 'MANUAL',
      storage_location: component.storage_location || null,
      level: level, // Track hierarchy level
      parent_material_id: level > 0 ? bom.material_id : null // Track parent for sub-assemblies
    });

    // Find active BOM for this component material and recursively explode it.
    // This keeps Routing BOM count aligned with BOM Header recursion logic.
    const subBOM = await db.BOMHeader.findOne({
      where: {
        material_id: componentMaterial.id,
        company_id: bom.company_id,
        status: 'ACTIVE'
      },
      order: [['id', 'DESC']],
      limit: 1
    });

    if (subBOM) {
      const subComponents = await explodeBOM(
        subBOM.id,
        requiredQty, // Use calculated parent requirement as sub-BOM order quantity
        subBOM.base_quantity || 1,
        processedBOMs,
        level + 1,
        bom.company_id
      );

      // Add all sub-components to the result
      components.push(...subComponents);
    }
  }

  return components;
};

exports.create = asyncHandler(async (req, res) => {
  const data = req.body;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  if (!data.plant_id || !data.fiscal_year_id || !data.finished_material_id || !data.bom_id) {
    return sendError(res, 'Plant ID, Fiscal Year ID, Finished Material ID, and BOM ID are required', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    if (!data.production_order_number) {
      data.production_order_number = await generatePONumber(companyId, data.fiscal_year_id, transaction);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;

    const {
      components,
      batches,
      standard_batch_quantity,
      batch_planning_enabled,
      regenerate_batches_from_standard_lot,
      ...poData
    } = data;

    const bom = await db.BOMHeader.findByPk(data.bom_id);
    if (!bom) {
      throw new Error('BOM not found');
    }

    const po = await db.ProductionOrder.create(poData, { transaction });

    if (components && Array.isArray(components) && components.length > 0) {
      for (let i = 0; i < components.length; i++) {
        const comp = components[i];
        await db.ProductionOrderComponent.create({
          production_order_id: po.id,
          bom_component_id: comp.bom_component_id || null,
          material_id: comp.material_id,
          required_quantity: comp.required_quantity,
          uom_id: comp.uom_id,
          issue_type: comp.issue_type || 'MANUAL',
          storage_location: comp.storage_location || null
        }, { transaction });
      }
    } else {
      const explodedComponents = await explodeBOM(data.bom_id, data.order_quantity, bom.base_quantity, new Set(), 0, companyId);
      for (const comp of explodedComponents) {
        await db.ProductionOrderComponent.create({
          production_order_id: po.id,
          bom_component_id: comp.bom_component_id || null,
          material_id: comp.material_id,
          required_quantity: comp.required_quantity,
          uom_id: comp.uom_id,
          issue_type: comp.issue_type || 'MANUAL',
          storage_location: comp.storage_location || null
        }, { transaction });
      }
    }

    await persistProductionOrderBatches(
      po,
      {
        batches,
        standard_batch_quantity,
        batch_planning_enabled,
        regenerate_batches_from_standard_lot: Boolean(regenerate_batches_from_standard_lot)
      },
      transaction,
      { replace: true }
    );

    await transaction.commit();

    const createdPO = await db.ProductionOrder.findByPk(po.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.Material, as: 'finishedMaterial' },
        { model: db.BOMHeader, as: 'bom' },
        {
          model: db.ProductionOrderBatch,
          as: 'batches',
          required: false,
          separate: true,
          order: [['batch_sequence', 'ASC']]
        },
        {
          model: db.ProductionOrderComponent,
          as: 'components',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        },
        { model: db.CustomerPO, as: 'customerPO', required: false }
      ]
    });

    // Create notifications for Production Order creation
    try {
      const userIdsToNotify = new Set();
      
      // Add creator
      if (userId) {
        userIdsToNotify.add(userId);
      }

      // Get users with production-related roles (PROD, PRODUCTION, PRODUCTION_MANAGER, etc.)
      const productionUserRoles = await db.UserRole.findAll({
        where: { status: 'ACTIVE' },
        include: [
          {
            model: db.Role,
            as: 'role',
            where: {
              company_id: companyId,
              status: 'ACTIVE',
              [Op.or]: [
                { role_code: { [Op.like]: '%PROD%' } },
                { role_code: { [Op.like]: '%PRODUCTION%' } },
                { role_name: { [Op.like]: '%Production%' } },
                { role_name: { [Op.like]: '%Prod%' } }
              ]
            },
            required: true
          },
          {
            model: db.User,
            as: 'user',
            where: { status: 'ACTIVE', is_active: true },
            required: true
          }
        ]
      });

      // Add users with production roles
      productionUserRoles.forEach(userRole => {
        if (userRole.user && userRole.user.id) {
          userIdsToNotify.add(userRole.user.id);
        }
      });

      // Create notification for each user
      const notificationPromises = Array.from(userIdsToNotify).map(userIdToNotify => {
        const isFromCustomerPO = data.customer_po_id ? true : false;
        const customerPONumber = createdPO.customerPO?.po_number || '';
        const title = isFromCustomerPO 
          ? `Production Order Created from Customer PO`
          : `New Production Order Created`;
        const message = isFromCustomerPO
          ? `Production Order ${createdPO.production_order_number} has been created from Customer PO ${customerPONumber}. Material: ${createdPO.finishedMaterial?.description || createdPO.finishedMaterial?.material_number || 'N/A'}, Quantity: ${createdPO.order_quantity} ${createdPO.uom?.uom_code || ''}`
          : `Production Order ${createdPO.production_order_number} has been created. Material: ${createdPO.finishedMaterial?.description || createdPO.finishedMaterial?.material_number || 'N/A'}, Quantity: ${createdPO.order_quantity} ${createdPO.uom?.uom_code || ''}`;

        return notificationService.createNotification({
          companyId,
          userId: userIdToNotify,
          type: 'INFO',
          moduleName: 'ProductionOrder',
          transactionId: createdPO.id,
          transactionNumber: createdPO.production_order_number,
          title,
          message,
          link: `/production/productionorder/${createdPO.id}/view`,
          priority: 'MEDIUM',
          createdBy: userId
        });
      });

      await Promise.all(notificationPromises);
    } catch (notificationError) {
      // Log error but don't fail the request
      console.error('Error creating notifications for Production Order:', notificationError);
    }

    sendResponse(res, 201, true, 'Production Order created successfully', createdPO);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const data = req.body;
  const userId = req.user?.userId || null;

  const po = await db.ProductionOrder.findByPk(id);
  if (!po) {
    return sendError(res, 'Production Order not found', 404);
  }

  // Validate business rules for update
  const businessRuleValidation = await businessRuleValidationService.productionOrder.canUpdate(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    data.updated_by = userId;
    const {
      components,
      batches,
      standard_batch_quantity,
      batch_planning_enabled,
      regenerate_batches_from_standard_lot,
      ...poData
    } = data;

    // If status is being changed, validate the transition
    if (poData.status && poData.status !== po.status) {
      const statusValidation = statusValidationService.validateStatusTransition('productionOrder', po.status, poData.status);
      if (!statusValidation.valid) {
        await transaction.rollback();
        return sendError(res, statusValidation.message, 400);
      }

      // If status is being changed to CANCELLED, validate and cancel stock reservations
      if (poData.status === 'CANCELLED') {
        const cancelValidation = await businessRuleValidationService.productionOrder.canCancel(po);
        if (!cancelValidation.valid) {
          await transaction.rollback();
          return sendError(res, cancelValidation.errors.join(', '), 400);
        }
        const productionOrderStatusService = require('../services/productionOrderStatusService');
        await productionOrderStatusService.onProductionOrderCancelled(id, userId, transaction);
      }
    }

    await po.update(poData, { transaction });

    if (components && Array.isArray(components)) {
      await db.ProductionOrderComponent.destroy({
        where: { production_order_id: id },
        transaction
      });

      for (const comp of components) {
        await db.ProductionOrderComponent.create({
          production_order_id: id,
          bom_component_id: comp.bom_component_id || null,
          material_id: comp.material_id,
          required_quantity: comp.required_quantity,
          uom_id: comp.uom_id,
          issue_type: comp.issue_type || 'MANUAL',
          storage_location: comp.storage_location || null
        }, { transaction });
      }
    }

    await po.reload({ transaction });

    const shouldReplaceBatches =
      Object.prototype.hasOwnProperty.call(data, 'batches') ||
      Boolean(data.regenerate_batches_from_standard_lot);
    if (shouldReplaceBatches) {
      await persistProductionOrderBatches(
        po,
        {
          batches: data.batches,
          standard_batch_quantity: data.standard_batch_quantity,
          batch_planning_enabled: data.batch_planning_enabled,
          regenerate_batches_from_standard_lot: Boolean(data.regenerate_batches_from_standard_lot)
        },
        transaction,
        { replace: true }
      );
    } else if (
      Object.prototype.hasOwnProperty.call(data, 'standard_batch_quantity') ||
      Object.prototype.hasOwnProperty.call(data, 'batch_planning_enabled')
    ) {
      await persistProductionOrderBatches(
        po,
        {
          standard_batch_quantity: data.standard_batch_quantity,
          batch_planning_enabled: data.batch_planning_enabled
        },
        transaction,
        { replace: false }
      );
    }

    await transaction.commit();

    const updatedPO = await db.ProductionOrder.findByPk(id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.Material, as: 'finishedMaterial' },
        { model: db.BOMHeader, as: 'bom' },
        {
          model: db.ProductionOrderBatch,
          as: 'batches',
          required: false,
          separate: true,
          order: [['batch_sequence', 'ASC']]
        },
        {
          model: db.ProductionOrderComponent,
          as: 'components',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Production Order updated successfully', updatedPO);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const po = await db.ProductionOrder.findByPk(id);
  if (!po) {
    return sendError(res, 'Production Order not found', 404);
  }

  if (po.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Production Orders can be deleted', 400);
  }

  // Check if Production Order is linked to Customer PO
  // If linked, we should prevent deletion or handle it carefully
  if (po.customer_po_id) {
    // Check if there are other production orders for the same customer PO item
    const otherPOs = await db.ProductionOrder.count({
      where: {
        customer_po_item_id: po.customer_po_item_id,
        id: { [Op.ne]: id },
        status: { [Op.ne]: 'CANCELLED' }
      }
    });

    // Allow deletion if it's the only production order or if user explicitly wants to delete
    // For now, we'll allow deletion but log a warning
    console.warn(`Deleting Production Order ${po.production_order_number} that is linked to Customer PO ${po.customer_po_id}`);
  }

  const transaction = await db.sequelize.transaction();

  try {
    // Delete all related components first
    await db.ProductionOrderComponent.destroy({
      where: { production_order_id: id },
      transaction
    });

    // Actually delete the Production Order (not just cancel)
    await po.destroy({ transaction });
    await transaction.commit();

    sendResponse(res, 200, true, 'Production Order deleted successfully', null);
  } catch (error) {
    await transaction.rollback();
    // If foreign key constraint error, provide helpful message
    if (error.name === 'SequelizeForeignKeyConstraintError') {
      return sendError(res, 'Cannot delete Production Order because it is referenced by other records. Please cancel it instead.', 400);
    }
    sendError(res, error.message, 400);
  }
});

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

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    const po = await db.ProductionOrder.findByPk(id, { transaction });
    if (!po || po.company_id !== companyId) {
      await transaction.rollback();
      return sendError(res, 'Production Order not found', 404);
    }

    const batch = await db.ProductionOrderBatch.findOne({
      where: { id: batchId, production_order_id: id },
      transaction
    });
    if (!batch) {
      await transaction.rollback();
      return sendError(res, 'Production batch not found', 404);
    }
    if (batch.status !== 'PLANNED') {
      await transaction.rollback();
      return sendError(res, 'Only planned batches can be released for production', 400);
    }

    await batch.update(
      {
        status: 'RELEASED',
        released_at: new Date(),
        released_by: userId
      },
      { transaction }
    );

    await transaction.commit();

    const updatedBatch = await db.ProductionOrderBatch.findByPk(batch.id, {
      include: [{ model: db.User, as: 'releasedByUser', attributes: ['id', 'username', 'email'], required: false }]
    });

    sendResponse(res, 200, true, 'Production batch released successfully', { batch: updatedBatch });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to release batch', 400);
  }
});

const ADDITIONAL_RELEASE_PO_STATUSES = ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED'];

/** Committed batch qty for balance (matches list API): released + in progress + completed batches, with legacy PO rule. */
const committedReleaseQuantityForBalance = (po, batches = []) => {
  const bList = Array.isArray(batches) ? batches : [];
  const orderQty = parseFloat(po.order_quantity || 0) || 0;
  if (!po.batch_planning_enabled && bList.length === 0) {
    if (['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(String(po.status || '').toUpperCase())) {
      return orderQty;
    }
    return 0;
  }
  return bList
    .filter((b) => ['RELEASED', 'IN_PROGRESS', 'COMPLETED'].includes(String(b.status || '').toUpperCase()))
    .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
};

const balanceToReleaseQuantity = (po, batches = []) => {
  const orderQty = parseFloat(po.order_quantity || 0) || 0;
  return Math.max(0, orderQty - committedReleaseQuantityForBalance(po, batches));
};

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

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  const releaseQtyRaw = req.body?.release_batch_quantity;
  const releaseQty = releaseQtyRaw != null && releaseQtyRaw !== '' ? parseFloat(releaseQtyRaw) : NaN;
  if (!Number.isFinite(releaseQty) || releaseQty <= 0) {
    return sendError(res, 'release_batch_quantity is required and must be greater than zero', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    const po = await db.ProductionOrder.findByPk(id, {
      include: [
        { model: db.ProductionOrderBatch, as: 'batches', required: false },
        {
          model: db.ProductionOrderComponent,
          as: 'components',
          include: [
            {
              model: db.Material,
              as: 'material',
              include: [
                {
                  model: db.MaterialPlant,
                  as: 'materialPlants',
                  where: { plant_id: db.sequelize.col('ProductionOrder.plant_id') },
                  required: false
                }
              ]
            }
          ]
        }
      ],
      transaction
    });

    if (!po || po.company_id !== companyId) {
      await transaction.rollback();
      return sendError(res, 'Production Order not found', 404);
    }

    const st = String(po.status || '').toUpperCase();
    if (!ADDITIONAL_RELEASE_PO_STATUSES.includes(st)) {
      await transaction.rollback();
      return sendError(
        res,
        'Additional batch release is only allowed when the order is Released, In progress, or Partially confirmed.',
        400
      );
    }

    const batchList = po.batches || [];
    const balance = balanceToReleaseQuantity(po, batchList);
    if (balance <= 0.0005) {
      await transaction.rollback();
      return sendError(res, 'No balance quantity left to release for this production order.', 400);
    }
    if (releaseQty - balance > 0.001) {
      await transaction.rollback();
      return sendError(
        res,
        `Release quantity exceeds balance to release (${balance.toFixed(3)}).`,
        400
      );
    }

    const maxSeq = batchList.length
      ? Math.max(...batchList.map((b) => parseInt(b.batch_sequence, 10) || 0))
      : 0;

    const newBatch = await db.ProductionOrderBatch.create(
      {
        production_order_id: po.id,
        batch_sequence: maxSeq + 1,
        batch_label: req.body?.batch_label || `Additional release ${new Date().toISOString().slice(0, 10)}`,
        quantity: releaseQty,
        planned_start_date: req.body?.batch_planned_start_date || null,
        planned_finish_date: req.body?.batch_planned_finish_date || null,
        status: 'RELEASED',
        released_at: new Date(),
        released_by: userId,
        remarks: req.body?.batch_remarks || null
      },
      { transaction }
    );

    if (!po.batch_planning_enabled) {
      await po.update({ batch_planning_enabled: true }, { transaction });
      po.batch_planning_enabled = true;
    }

    const stockReservationService = require('../services/stockReservationService');
    try {
      await stockReservationService.addReservationsForAdditionalRelease(po, releaseQty, userId, transaction);
    } catch (reservationError) {
      console.error('Error extending stock reservations for additional release:', reservationError);
    }

    await transaction.commit();

    const updatedBatch = await db.ProductionOrderBatch.findByPk(newBatch.id, {
      include: [{ model: db.User, as: 'releasedByUser', attributes: ['id', 'username', 'email'], required: false }]
    });

    const allBatches = await db.ProductionOrderBatch.findAll({
      where: { production_order_id: po.id },
      order: [['batch_sequence', 'ASC']]
    });

    sendResponse(res, 200, true, 'Additional batch released successfully', {
      batch: updatedBatch,
      batches: allBatches,
      balance_to_release_qty: Number(
        balanceToReleaseQuantity(
          {
            order_quantity: po.order_quantity,
            batch_planning_enabled: true,
            status: po.status
          },
          allBatches
        ).toFixed(3)
      )
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to release additional batch', 400);
  }
});

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

  const transaction = await db.sequelize.transaction();

  try {
    const po = await db.ProductionOrder.findByPk(id, {
      include: [
        {
          model: db.ProductionOrderBatch,
          as: 'batches',
          required: false
        },
        {
          model: db.ProductionOrderComponent,
          as: 'components',
          include: [
            {
              model: db.Material,
              as: 'material',
              include: [
                {
                  model: db.MaterialPlant,
                  as: 'materialPlants',
                  where: { plant_id: db.sequelize.col('ProductionOrder.plant_id') },
                  required: false
                }
              ]
            }
          ]
        },
        {
          model: db.Routing,
          as: 'routing',
          required: false
        }
      ],
      transaction
    });

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

    const releaseQtyRaw = req.body?.release_batch_quantity;
    const releaseQty =
      releaseQtyRaw != null && releaseQtyRaw !== '' ? parseFloat(releaseQtyRaw) : NaN;

    if (Number.isFinite(releaseQty) && releaseQty > 0) {
      const existing = po.batches || [];
      const releasedSoFar = sumReleasedBatchQuantity(existing);
      const orderQty = parseFloat(po.order_quantity || 0);
      if (releasedSoFar + releaseQty - orderQty > 0.001) {
        await transaction.rollback();
        return sendError(
          res,
          `Release quantity exceeds remaining order quantity (remaining: ${Math.max(0, orderQty - releasedSoFar).toFixed(3)}).`,
          400
        );
      }
      const maxSeq = existing.length
        ? Math.max(...existing.map((b) => parseInt(b.batch_sequence, 10) || 0))
        : 0;
      await db.ProductionOrderBatch.create(
        {
          production_order_id: po.id,
          batch_sequence: maxSeq + 1,
          batch_label: req.body?.batch_label || `Released ${new Date().toISOString().slice(0, 10)}`,
          quantity: releaseQty,
          planned_start_date: req.body?.batch_planned_start_date || null,
          planned_finish_date: req.body?.batch_planned_finish_date || null,
          status: 'RELEASED',
          released_at: new Date(),
          released_by: userId,
          remarks: req.body?.batch_remarks || null
        },
        { transaction }
      );
      if (!po.batch_planning_enabled) {
        await po.update({ batch_planning_enabled: true }, { transaction });
        po.batch_planning_enabled = true;
      }
      const refreshedBatches = await db.ProductionOrderBatch.findAll({
        where: { production_order_id: po.id },
        transaction
      });
      po.batches = refreshedBatches;
    }

    // Validate status transition
    const statusValidation = statusValidationService.validateStatusTransition('productionOrder', po.status, 'RELEASED');
    if (!statusValidation.valid) {
      await transaction.rollback();
      return sendError(res, statusValidation.message, 400);
    }

    // Validate business rules
    const businessRuleValidation = await businessRuleValidationService.productionOrder.canRelease(po, {
      transaction
    });
    if (!businessRuleValidation.valid) {
      await transaction.rollback();
      return sendError(res, businessRuleValidation.errors.join(', '), 400);
    }

    await po.update({
      status: 'RELEASED',
      released_at: new Date(),
      released_by: userId
    }, { transaction });

    // Create stock reservations for all components
    const stockReservationService = require('../services/stockReservationService');
    let reservations = [];
    try {
      const reservationScale = mrpScaleFromBatches(
        po.order_quantity,
        Boolean(po.batch_planning_enabled),
        po.batches || []
      );
      reservations = await stockReservationService.createReservationsForProductionOrder(
        po,
        userId,
        transaction,
        reservationScale
      );
      console.log(`Created ${reservations.length} stock reservations for Production Order ${po.production_order_number}`);
    } catch (reservationError) {
      console.error('Error creating stock reservations:', reservationError);
      // Don't fail the release if reservation creation fails, but log it
      // In production, you might want to rollback or handle this differently
    }

    // Create routing execution records if routing exists
    const routingExecutionService = require('../services/routingExecutionService');
    let routingExecutions = [];
    try {
      routingExecutions = await routingExecutionService.createRoutingExecutionForProductionOrder(
        po,
        userId,
        transaction
      );
      if (routingExecutions.length > 0) {
        console.log(`Created ${routingExecutions.length} routing execution records for Production Order ${po.production_order_number}`);
        
        // Update Production Order dates based on routing
        await routingExecutionService.updateProductionOrderDatesFromRouting(po.id, transaction);
      }
    } catch (routingError) {
      console.error('Error creating routing executions:', routingError);
      // Don't fail the release if routing execution creation fails, but log it
    }

    await transaction.commit();

    const updatedPO = await db.ProductionOrder.findByPk(id, {
      include: getProductionOrderDetailInclude()
    });

  // Create notifications for Production Order release
  try {
    const companyId = po.company_id;
    const userIdsToNotify = new Set();
    
    // Add releaser
    if (userId) {
      userIdsToNotify.add(userId);
    }

    // Get users with production-related roles
    const productionUserRoles = await db.UserRole.findAll({
      where: { status: 'ACTIVE' },
      include: [
        {
          model: db.Role,
          as: 'role',
          where: {
            company_id: companyId,
            status: 'ACTIVE',
            [Op.or]: [
              { role_code: { [Op.like]: '%PROD%' } },
              { role_code: { [Op.like]: '%PRODUCTION%' } },
              { role_name: { [Op.like]: '%Production%' } },
              { role_name: { [Op.like]: '%Prod%' } }
            ]
          },
          required: true
        },
        {
          model: db.User,
          as: 'user',
          where: { status: 'ACTIVE', is_active: true },
          required: true
        }
      ]
    });

    productionUserRoles.forEach(userRole => {
      if (userRole.user && userRole.user.id) {
        userIdsToNotify.add(userRole.user.id);
      }
    });

    // Create notification for each user
    const notificationPromises = Array.from(userIdsToNotify).map(userIdToNotify => {
      return notificationService.createNotification({
        companyId,
        userId: userIdToNotify,
        type: 'INFO',
        moduleName: 'ProductionOrder',
        transactionId: updatedPO.id,
        transactionNumber: updatedPO.production_order_number,
        title: 'Production Order Released',
        message: `Production Order ${updatedPO.production_order_number} has been released and is ready for production. Material: ${updatedPO.finishedMaterial?.description || updatedPO.finishedMaterial?.material_number || 'N/A'}, Quantity: ${updatedPO.order_quantity} ${updatedPO.uom?.uom_code || ''}`,
        link: `/production/productionorder/${updatedPO.id}/view`,
        priority: 'HIGH',
        createdBy: userId
      });
    });

    await Promise.all(notificationPromises);
  } catch (notificationError) {
    // Log error but don't fail the request
    console.error('Error creating notifications for Production Order release:', notificationError);
  }

    sendResponse(res, 200, true, 'Production Order released successfully', {
      productionOrder: updatedPO,
      reservationsCreated: reservations.length
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

// API endpoint to get stock reservations for a Production Order
exports.getReservations = asyncHandler(async (req, res) => {
  const { id } = req.params;
  
  if (!id) {
    return sendError(res, 'Production Order ID is required', 400);
  }

  const stockReservationService = require('../services/stockReservationService');
  const reservations = await stockReservationService.getReservationsForProductionOrder(id);

  sendResponse(res, 200, true, 'Stock reservations retrieved successfully', reservations);
});

// API endpoint to explode BOM recursively (for frontend use)
exports.explodeBOM = asyncHandler(async (req, res) => {
  const { bom_id, order_quantity, base_quantity } = req.body;
  const companyId = req.user?.companyId;

  if (!bom_id || !order_quantity) {
    return sendError(res, 'BOM ID and Order Quantity are required', 400);
  }

  try {
    const bom = await db.BOMHeader.findByPk(bom_id);
    if (!bom) {
      return sendError(res, 'BOM not found', 404);
    }

    const explodedComponents = await explodeBOM(
      bom_id, 
      parseFloat(order_quantity), 
      parseFloat(base_quantity || bom.base_quantity || 1),
      new Set(),
      0,
      companyId || bom.company_id
    );

    // Enrich components with material details for frontend display
    const enrichedComponents = await Promise.all(
      explodedComponents.map(async (comp) => {
        const material = await db.Material.findByPk(comp.material_id, {
          attributes: [
            'id',
            'material_number',
            'description',
            'material_type',
            'base_uom_id',
            'drawing_number',
            'drawing_file_path',
            'gross_weight',
            'net_weight',
            'width',
            'height',
            'length',
            'thickness',
            'thickness_id'
          ],
          include: [
            {
              model: db.MaterialThickness,
              as: 'materialThickness',
              attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit'],
              required: false
            }
          ]
        });
        const uom = await db.UOM.findByPk(comp.uom_id, {
          attributes: ['id', 'uom_code', 'uom_name']
        });

        return {
          ...comp,
          material: material ? {
            id: material.id,
            material_number: material.material_number,
            description: material.description,
            material_type: material.material_type,
            drawing_number: material.drawing_number,
            drawing_file_path: material.drawing_file_path,
            gross_weight: material.gross_weight,
            net_weight: material.net_weight,
            width: material.width,
            height: material.height,
            length: material.length,
            thickness: material.thickness,
            thickness_id: material.thickness_id,
            materialThickness: material.materialThickness
          } : null,
          uom: uom ? {
            id: uom.id,
            uom_code: uom.uom_code,
            uom_name: uom.uom_name
          } : null
        };
      })
    );

    sendResponse(res, 200, true, 'BOM exploded successfully', {
      components: enrichedComponents,
      total_components: enrichedComponents.length
    });
  } catch (error) {
    console.error('Error exploding BOM:', error);
    sendError(res, error.message || 'Failed to explode BOM', 500);
  }
});

const resolveDefaultMaterialReceiptWarehouses = async (plantId) => {
  const pid = parseInt(plantId, 10);
  if (!(pid > 0)) return { fromId: null, toId: null };
  const rows = await db.StorageLocation.findAll({
    where: { plant_id: pid, status: 'ACTIVE' },
    attributes: ['id', 'storage_location_code', 'description'],
    order: [['id', 'ASC']],
  });
  const upper = (s) => String(s || '').toUpperCase();
  let fromId = null;
  let toId = null;
  for (const r of rows) {
    const c = upper(r.storage_location_code);
    const d = upper(r.description);
    if (!fromId && (c.includes('RM') || (d.includes('RAW') && d.includes('MAT')) || d.includes('RAW MATERIAL'))) {
      fromId = r.id;
    }
    if (
      !toId
      && (c.includes('WIP')
        || d.includes('WIP')
        || (d.includes('PRODUCTION') && d.includes('STORE'))
        || d.includes('WORK IN PROGRESS'))
    ) {
      toId = r.id;
    }
  }
  if (!fromId && rows[0]) fromId = rows[0].id;
  if (!toId) {
    const alt = rows.find((r) => r.id !== fromId);
    toId = alt ? alt.id : fromId;
  }
  return { fromId, toId };
};

exports.getMaterialReceiptLines = asyncHandler(async (req, res) => {
  const poId = parseInt(req.params.id, 10);
  const companyId = req.user?.companyId;
  if (!(poId > 0)) return sendError(res, 'Invalid production order id', 400);

  const po = await db.ProductionOrder.findByPk(poId, {
    attributes: ['id', 'company_id', 'plant_id'],
  });
  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 { fromId, toId } = await resolveDefaultMaterialReceiptWarehouses(po.plant_id);

  const lines = await db.ProductionOrderMaterialReceiptLine.findAll({
    where: { production_order_id: poId },
    include: [
      { model: db.StorageLocation, as: 'fromStorageLocation', attributes: ['id', 'storage_location_code', 'description'] },
      { model: db.StorageLocation, as: 'toStorageLocation', attributes: ['id', 'storage_location_code', 'description'] },
    ],
    order: [['line_type', 'ASC'], ['line_key', 'ASC']],
  });

  sendResponse(res, 200, true, 'Material receipt lines loaded', {
    lines,
    default_from_storage_location_id: fromId,
    default_to_storage_location_id: toId,
  });
});

exports.saveMaterialReceiptLines = asyncHandler(async (req, res) => {
  const poId = parseInt(req.params.id, 10);
  const companyId = req.user?.companyId;
  const body = req.body || {};
  const inputLines = Array.isArray(body.lines) ? body.lines : [];

  if (!(poId > 0)) return sendError(res, 'Invalid production order id', 400);
  if (inputLines.length === 0) return sendError(res, 'At least one line is required', 400);

  const po = await db.ProductionOrder.findByPk(poId, {
    attributes: ['id', 'company_id', 'plant_id'],
  });
  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 { fromId: defaultFrom, toId: defaultTo } = await resolveDefaultMaterialReceiptWarehouses(po.plant_id);

  const transaction = await db.sequelize.transaction();
  try {
    for (let i = 0; i < inputLines.length; i += 1) {
      const row = inputLines[i] || {};
      const lineType = String(row.line_type || '').toUpperCase();
      if (!['RAW', 'FASTENER'].includes(lineType)) {
        await transaction.rollback();
        return sendError(res, `Invalid line_type at row ${i + 1}`, 400);
      }
      const rawKey = String(row.line_key || '').trim();
      if (!rawKey) {
        await transaction.rollback();
        return sendError(res, `line_key required at row ${i + 1}`, 400);
      }

      const pocId = parseInt(rawKey, 10);
      if (!(pocId > 0)) {
        await transaction.rollback();
        return sendError(
          res,
          `line_key must be the production order component id (numeric) at row ${i + 1}`,
          400
        );
      }

      const poc = await db.ProductionOrderComponent.findOne({
        where: { id: pocId, production_order_id: poId },
        include: [{ model: db.Material, as: 'material', attributes: ['id', 'description', 'material_number', 'material_type'] }],
        transaction
      });
      if (!poc) {
        await transaction.rollback();
        return sendError(res, `Production order component ${pocId} not found on this order (row ${i + 1})`, 400);
      }

      const expectedLt = materialFlowLineTypeFromMaterial(poc.material);
      if (!expectedLt) {
        await transaction.rollback();
        return sendError(
          res,
          `Material receipt applies only to raw materials and fasteners (component ${pocId}, row ${i + 1})`,
          400
        );
      }
      if (expectedLt !== lineType) {
        await transaction.rollback();
        return sendError(
          res,
          `line_type must be ${expectedLt} for component ${pocId} (row ${i + 1})`,
          400
        );
      }

      const lineKey = String(pocId);
      const issuedQty = toNumber(poc.issued_quantity);
      let receivedQty = toNumber(row.received_qty);
      if (receivedQty < 0) receivedQty = 0;
      if (receivedQty > issuedQty + FLOW_QTY_EPS) {
        await transaction.rollback();
        return sendError(
          res,
          `Received qty cannot exceed issued qty for component ${pocId} (max ${issuedQty})`,
          400
        );
      }

      const materialId = poc.material_id;
      const materialName =
        poc.material?.description != null ? String(poc.material.description).slice(0, 500) : null;

      const fromSl =
        row.from_storage_location_id != null && row.from_storage_location_id !== ''
          ? parseInt(row.from_storage_location_id, 10)
          : defaultFrom;
      const toSl =
        row.to_storage_location_id != null && row.to_storage_location_id !== ''
          ? parseInt(row.to_storage_location_id, 10)
          : defaultTo;

      let receivedDate = row.received_date || null;
      if (receivedDate) {
        receivedDate = String(receivedDate).slice(0, 10);
      }

      const [rec] = await db.ProductionOrderMaterialReceiptLine.findOrCreate({
        where: {
          production_order_id: poId,
          line_type: lineType,
          line_key: lineKey
        },
        defaults: {
          company_id: po.company_id,
          plant_id: po.plant_id,
          material_id: Number.isFinite(materialId) && materialId > 0 ? materialId : null,
          material_name: materialName,
          received_qty: receivedQty,
          from_storage_location_id: fromSl || null,
          to_storage_location_id: toSl || null,
          received_date: receivedDate
        },
        transaction
      });

      const oldReceivedQty = toNumber(rec.received_qty);

      await rec.update(
        {
          material_id: Number.isFinite(materialId) && materialId > 0 ? materialId : rec.material_id,
          material_name: materialName != null ? materialName : rec.material_name,
          received_qty: receivedQty,
          from_storage_location_id: fromSl || null,
          to_storage_location_id: toSl || null,
          received_date: receivedDate
        },
        { transaction }
      );

      const wipDelta = receivedQty - oldReceivedQty;
      if (Math.abs(wipDelta) > FLOW_QTY_EPS && Number.isFinite(materialId) && materialId > 0) {
        const productionWipService = require('../services/productionWipService');
        const wipPayload = {
          company_id: po.company_id,
          plant_id: po.plant_id,
          production_order_id: poId,
          material_id: materialId,
          uom_id: poc.uom_id,
          deltaQty: wipDelta,
          receipt_line_id: rec.id,
          userId: req.user?.userId || null
        };
        if (wipDelta > 0) {
          await productionWipService.creditFromReceiptLineDelta(transaction, wipPayload);
        } else {
          await productionWipService.debitFromReceiptLineDelta(transaction, wipPayload);
        }
      }
    }

    await transaction.commit();
  } catch (err) {
    if (transaction && !transaction.finished) await transaction.rollback();
    throw err;
  }

  const lines = await db.ProductionOrderMaterialReceiptLine.findAll({
    where: { production_order_id: poId },
    include: [
      { model: db.StorageLocation, as: 'fromStorageLocation', attributes: ['id', 'storage_location_code', 'description'] },
      { model: db.StorageLocation, as: 'toStorageLocation', attributes: ['id', 'storage_location_code', 'description'] },
    ],
    order: [['line_type', 'ASC'], ['line_key', 'ASC']],
  });

  sendResponse(res, 200, true, 'Material receipt saved', {
    lines,
    default_from_storage_location_id: defaultFrom,
    default_to_storage_location_id: defaultTo,
  });
});

