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 { generateNextNumber } = require('../services/numberFormatService');
const routeCardService = require('../services/routeCardService');

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

const buildOperationDefaultsFromRouting = async (productionOrderId, workOrderQty) => {
  const po = await db.ProductionOrder.findByPk(productionOrderId, {
    include: [{ model: db.Routing, as: 'routing', include: [{ model: db.RoutingOperation, as: 'operations', where: { status: 'ACTIVE' }, required: false }] }]
  });

  const routingOperations = po?.routing?.operations || [];
  const qty = parseFloat(workOrderQty || 0);

  return routingOperations
    .slice()
    .sort((a, b) => (a.operation_number || 0) - (b.operation_number || 0))
    .map((op) => {
      const machineTime = parseFloat(op.machine_time || 0);
      const laborTime = parseFloat(op.labor_time || 0);
      const standardMinutes = Math.round(Math.max(machineTime, laborTime) * 60);
      const opCode = op.operation_code ? String(op.operation_code).trim() : null;
      return {
        operation_sequence_no: op.operation_number,
        operation_name: opCode || op.operation_description || `Operation ${op.operation_number}`,
        work_center_id: op.work_center_id,
        machine_id: op.machine_id || null,
        planned_qty: qty,
        target_date: null,
        standard_time_minutes: isNaN(standardMinutes) ? null : standardMinutes,
        setup_time_minutes: op.setup_time != null ? parseInt(op.setup_time, 10) : null,
        status: 'PLANNED'
      };
    });
};

const batchStatusUpper = (s) => String(s || '').toUpperCase();

/** Quantity from the batch released most recently (by released_at), for default Work Order qty. */
const computeMostRecentReleasedBatchQty = ({ orderQtyVal, hasBatches, poStatus, batchesJson }) => {
  const st = batchStatusUpper;
  const rows = Array.isArray(batchesJson) ? batchesJson : [];
  const releasedWithTs = rows
    .filter(
      (bj) =>
        bj.released_at && ['RELEASED', 'IN_PROGRESS', 'COMPLETED'].includes(st(bj.status))
    )
    .sort((a, b) => {
      const d = new Date(b.released_at) - new Date(a.released_at);
      if (d !== 0) return d;
      return (b.batch_sequence || 0) - (a.batch_sequence || 0);
    });
  if (releasedWithTs.length > 0) {
    return Number((parseFloat(releasedWithTs[0].quantity || 0) || 0).toFixed(3));
  }
  if (!hasBatches && ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(st(poStatus))) {
    return Number(orderQtyVal.toFixed(3));
  }
  const releasedNoTs = rows
    .filter((bj) => ['RELEASED', 'IN_PROGRESS', 'COMPLETED'].includes(st(bj.status)))
    .sort((a, b) => (b.batch_sequence || 0) - (a.batch_sequence || 0));
  if (releasedNoTs.length > 0) {
    return Number((parseFloat(releasedNoTs[0].quantity || 0) || 0).toFixed(3));
  }
  return 0;
};

const round3 = (value) => Number((parseFloat(value || 0) || 0).toFixed(3));

const buildWorkOrderRawMaterialSnapshot = async ({ productionOrderId }) => {
  const po = await db.ProductionOrder.findByPk(productionOrderId, {
    attributes: ['id', 'bom_id'],
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'components',
        attributes: ['id', 'material_id', 'required_quantity', 'issued_quantity', 'uom_id'],
        required: false,
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'thickness', 'length', 'width', 'dimension_unit'],
            required: false,
            include: [
              { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name'], required: false },
              { model: db.MaterialSheetType, as: 'materialSheetType', attributes: ['id', 'sheet_type_code', 'sheet_type_name'], required: false }
            ]
          },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false }
        ]
      }
    ]
  });
  if (!po) return [];

  const byMaterialId = new Map();

  (po.components || []).forEach((c) => {
    const materialId = parseInt(c?.material_id, 10);
    if (!Number.isFinite(materialId) || materialId <= 0) return;

    if (!byMaterialId.has(materialId)) {
      byMaterialId.set(materialId, {
        material_id: materialId,
        material_code: c?.material?.material_number || null,
        material_name: c?.material?.description || null,
        thickness: c?.material?.thickness != null ? round3(c.material.thickness) : null,
        length: c?.material?.length != null ? round3(c.material.length) : null,
        width: c?.material?.width != null ? round3(c.material.width) : null,
        dimension_unit: c?.material?.dimension_unit || 'mm',
        grade_short:
          c?.material?.materialGrade?.grade_code ||
          c?.material?.materialGrade?.grade_name ||
          null,
        sheet_type_code:
          c?.material?.materialSheetType?.sheet_type_code ||
          c?.material?.materialSheetType?.sheet_type_name ||
          null,
        uom_id: c?.uom_id || c?.uom?.id || null,
        unit: c?.uom?.uom_code || c?.uom?.uom_name || null,
        bom_required_quantity: 0,
        nesting_confirmed_quantity: 0,
        issued_quantity: 0
      });
    }
    const row = byMaterialId.get(materialId);
    row.bom_required_quantity += parseFloat(c?.required_quantity || 0) || 0;
    row.issued_quantity += parseFloat(c?.issued_quantity || 0) || 0;
    if (!row.unit && (c?.uom?.uom_code || c?.uom?.uom_name)) {
      row.unit = c.uom.uom_code || c.uom.uom_name;
    }
  });

  const nestingRows = await db.ProductionNesting.findAll({
    where: { production_order_id: productionOrderId },
    attributes: ['material_id', 'nesting_confirmed_quantity'],
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'thickness', 'length', 'width', 'dimension_unit'],
        required: false,
        include: [
          { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name'], required: false },
          { model: db.MaterialSheetType, as: 'materialSheetType', attributes: ['id', 'sheet_type_code', 'sheet_type_name'], required: false }
        ]
      }
    ]
  });

  nestingRows.forEach((n) => {
    const materialId = parseInt(n?.material_id, 10);
    if (!Number.isFinite(materialId) || materialId <= 0) return;
    if (!byMaterialId.has(materialId)) {
      byMaterialId.set(materialId, {
        material_id: materialId,
        material_code: n?.material?.material_number || null,
        material_name: n?.material?.description || null,
        thickness: n?.material?.thickness != null ? round3(n.material.thickness) : null,
        length: n?.material?.length != null ? round3(n.material.length) : null,
        width: n?.material?.width != null ? round3(n.material.width) : null,
        dimension_unit: n?.material?.dimension_unit || 'mm',
        grade_short:
          n?.material?.materialGrade?.grade_code ||
          n?.material?.materialGrade?.grade_name ||
          null,
        sheet_type_code:
          n?.material?.materialSheetType?.sheet_type_code ||
          n?.material?.materialSheetType?.sheet_type_name ||
          null,
        uom_id: null,
        unit: null,
        bom_required_quantity: 0,
        nesting_confirmed_quantity: 0,
        issued_quantity: 0
      });
    }
    const row = byMaterialId.get(materialId);
    row.nesting_confirmed_quantity += parseFloat(n?.nesting_confirmed_quantity || 0) || 0;
    if (!row.material_code && n?.material?.material_number) row.material_code = n.material.material_number;
    if (!row.material_name && n?.material?.description) row.material_name = n.material.description;
    if (row.thickness == null && n?.material?.thickness != null) row.thickness = round3(n.material.thickness);
    if (row.length == null && n?.material?.length != null) row.length = round3(n.material.length);
    if (row.width == null && n?.material?.width != null) row.width = round3(n.material.width);
    if (!row.dimension_unit && n?.material?.dimension_unit) row.dimension_unit = n.material.dimension_unit;
    if (!row.grade_short && (n?.material?.materialGrade?.grade_code || n?.material?.materialGrade?.grade_name)) {
      row.grade_short = n.material.materialGrade?.grade_code || n.material.materialGrade?.grade_name;
    }
    if (!row.sheet_type_code && (n?.material?.materialSheetType?.sheet_type_code || n?.material?.materialSheetType?.sheet_type_name)) {
      row.sheet_type_code = n.material.materialSheetType?.sheet_type_code || n.material.materialSheetType?.sheet_type_name;
    }
  });

  return Array.from(byMaterialId.values())
    .map((row) => {
      const bomRequired = round3(row.bom_required_quantity);
      const nestingConfirmedRaw = round3(row.nesting_confirmed_quantity);
      const issuedQty = round3(row.issued_quantity);
      const hasNesting = nestingConfirmedRaw > 0;
      const effectiveConfirmed = hasNesting ? nestingConfirmedRaw : bomRequired;
      const pendingToIssue = round3(Math.max(effectiveConfirmed - issuedQty, 0));
      const issueStatus = issuedQty <= 0 ? 'NOT_ISSUED' : issuedQty >= effectiveConfirmed ? 'FULLY_ISSUED' : 'PARTIALLY_ISSUED';

      return {
        material_id: row.material_id,
        material_code: row.material_code,
        material_name: row.material_name,
        bom_required_quantity: bomRequired,
        nesting_confirmed_quantity: effectiveConfirmed,
        issued_quantity: issuedQty,
        pending_to_issue_quantity: pendingToIssue,
        unit: row.unit || null,
        has_nesting_data: hasNesting,
        issue_status: issueStatus
      };
    })
    .sort((a, b) => String(a.material_code || a.material_name || '').localeCompare(String(b.material_code || b.material_name || '')));
};

const buildNestingSheetRows = async ({ productionOrderId }) => {
  const nestingRows = await db.ProductionNesting.findAll({
    where: {
      production_order_id: productionOrderId,
      status: 'COMPLETED'
    },
    attributes: ['thickness', 'length', 'width', 'nesting_confirmed_quantity', 'dimension_unit'],
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'productionOrderComponent',
        attributes: ['issued_quantity'],
        required: false
      },
      {
        model: db.Material,
        as: 'material',
        attributes: ['id'],
        required: false,
        include: [
          { model: db.MaterialGrade, as: 'materialGrade', attributes: ['grade_code', 'grade_name'], required: false },
          { model: db.MaterialSheetType, as: 'materialSheetType', attributes: ['sheet_type_code', 'sheet_type_name'], required: false }
        ]
      }
    ]
  });

  const grouped = new Map();
  nestingRows.forEach((row) => {
    const thickness = round3(row?.thickness);
    const length = round3(row?.length);
    const width = round3(row?.width);
    const unit = row?.dimension_unit || 'mm';
    const sheetCode = String(row?.material?.materialSheetType?.sheet_type_code || row?.material?.materialSheetType?.sheet_type_name || '').trim();
    const gradeCode = String(row?.material?.materialGrade?.grade_code || row?.material?.materialGrade?.grade_name || '').trim();
    const gradeShort = sheetCode && gradeCode ? `${sheetCode} ${gradeCode}` : gradeCode || sheetCode || '-';
    // Match Nesting "View Raw Material" grouping: thickness + grade.
    const key = `${thickness}|${gradeShort}|${unit}`;
    if (!grouped.has(key)) {
      grouped.set(key, {
        key,
        thickness,
        length_sum: 0,
        width_sum: 0,
        unit,
        grade_short: gradeShort,
        raw_material: '',
        nesting_confirmed_qty: 0,
        issued_qty: 0
      });
    }
    grouped.get(key).length_sum += length;
    grouped.get(key).width_sum += width;
    grouped.get(key).nesting_confirmed_qty += round3(row?.nesting_confirmed_quantity || 0);
    grouped.get(key).issued_qty += round3(row?.productionOrderComponent?.issued_quantity || 0);
  });

  return Array.from(grouped.values())
    .map((r) => {
      const confirmed = round3(r.nesting_confirmed_qty);
      const issued = round3(r.issued_qty);
      const lengthSum = round3(r.length_sum);
      const widthSum = round3(r.width_sum);
      const rawMaterial = `${r.thickness} ${r.unit} x ${lengthSum} ${r.unit} x ${widthSum} ${r.unit} -- ${r.grade_short}`;
      return {
        ...r,
        raw_material: rawMaterial,
        nesting_confirmed_qty: confirmed,
        issued_qty: issued,
        pending_to_issue_qty: round3(Math.max(confirmed - issued, 0))
      };
    })
    .filter((r) => r.nesting_confirmed_qty > 0)
    .sort((a, b) => String(a.raw_material || '').localeCompare(String(b.raw_material || '')));
};

/**
 * GET /api/v1/production/work-order
 */
exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, priority, companyId, plantId, fiscalYearId, productionOrderId } = 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 (productionOrderId) where.production_order_id = productionOrderId;

  if (status) {
    if (String(status).includes(',')) {
      const statusArray = String(status)
        .split(',')
        .map((s) => s.trim())
        .filter(Boolean);
      where.status = { [Op.in]: statusArray };
    } else {
      where.status = status;
    }
  }

  if (priority) {
    if (String(priority).includes(',')) {
      const priorityArray = String(priority)
        .split(',')
        .map((s) => s.trim())
        .filter(Boolean);
      where.priority = { [Op.in]: priorityArray };
    } else {
      where.priority = priority;
    }
  }

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

  const records = await db.WorkOrder.findAndCountAll({
    where,
    distinct: true,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.FiscalYear, as: 'fiscalYear', attributes: ['id', 'fiscal_year'] },
      { model: db.Routing, as: 'routing', attributes: ['id', 'routing_number'] },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      { model: db.User, as: 'approvedByUser', attributes: ['id', 'username', 'first_name', 'last_name'] }
    ],
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);

  // Enrich list with route card totals for current WO.
  const items = response.items || [];
  const workOrderIds = items
    .map((w) => w?.id)
    .filter((id) => id !== null && id !== undefined);
  const allWorkOrderIds = Array.from(new Set(workOrderIds));
  const routeCardByWorkOrderId = new Map();
  const routeCardMetaByWorkOrderId = new Map();
  const workOrderIdByRouteCardId = new Map();
  if (allWorkOrderIds.length > 0) {
    let rcRows = await db.RouteCard.findAll({
      where: {
        work_order_id: { [Op.in]: allWorkOrderIds },
        previous_route_card_id: null
      },
      attributes: ['id', 'work_order_id', 'route_card_number', 'status']
    });
    // Fallback for legacy data where previous_route_card_id may not be set.
    if (rcRows.length === 0) {
      rcRows = await db.RouteCard.findAll({
        where: { work_order_id: { [Op.in]: allWorkOrderIds } },
        attributes: ['id', 'work_order_id', 'route_card_number', 'status']
      });
    }
    rcRows.forEach((r) => {
      routeCardByWorkOrderId.set(r.work_order_id, r.id);
      routeCardMetaByWorkOrderId.set(r.work_order_id, {
        id: r.id,
        route_card_number: r.route_card_number,
        status: r.status
      });
      workOrderIdByRouteCardId.set(r.id, r.work_order_id);
    });
  }

  const routeCardIds = Array.from(new Set(Array.from(routeCardByWorkOrderId.values())));

  const totalsByRouteCardId = new Map();
  const qcPendingByRouteCardId = new Map();
  const qcInProgressByRouteCardId = new Map();
  const reworkSummaryByRouteCardId = new Map();
  const assemblyCompletedByRouteCardId = new Map();
  const finalInspectionByRouteCardId = new Map();
  const approvedFinalQcByWorkOrderId = new Map();
  if (routeCardIds.length > 0) {
    const totalRows = await db.RouteCardOperation.findAll({
      attributes: [
        'route_card_id',
        [db.sequelize.fn('SUM', db.sequelize.col('rework_qty')), 'rework_total']
      ],
      where: { route_card_id: { [Op.in]: routeCardIds } },
      group: ['route_card_id']
    });

    totalRows.forEach((r) => {
      const rcId = r.get('route_card_id');
      const reworkTotal = parseFloat(r.get('rework_total') || 0);
      totalsByRouteCardId.set(rcId, {
        rework_total: !isNaN(reworkTotal) ? reworkTotal : 0,
        completed_total: 0
      });
    });

    // Completed Qty must represent overall flow qty, not sum across operations.
    // Use completed qty from the latest operation (highest sequence) that has qty > 0.
    const completedRows = await db.RouteCardOperation.findAll({
      attributes: ['id', 'route_card_id', 'operation_sequence_no', 'completed_qty'],
      where: { route_card_id: { [Op.in]: routeCardIds } },
      order: [['route_card_id', 'ASC'], ['operation_sequence_no', 'ASC'], ['id', 'ASC']]
    });
    const latestCompletedByRouteCardId = new Map();
    completedRows.forEach((row) => {
      const rcId = row.route_card_id;
      const completedQty = parseFloat(row.completed_qty || 0) || 0;
      if (completedQty <= 0) return;
      const seqNo = parseInt(row.operation_sequence_no, 10) || 0;
      const prev = latestCompletedByRouteCardId.get(rcId);
      if (!prev || seqNo > prev.seqNo || (seqNo === prev.seqNo && row.id > prev.id)) {
        latestCompletedByRouteCardId.set(rcId, { seqNo, id: row.id, qty: completedQty });
      }
    });
    latestCompletedByRouteCardId.forEach((val, rcId) => {
      const prevTotals = totalsByRouteCardId.get(rcId) || { rework_total: 0, completed_total: 0 };
      totalsByRouteCardId.set(rcId, {
        ...prevTotals,
        completed_total: val.qty
      });
    });

    const opStatusRows = await db.RouteCardOperation.findAll({
      attributes: [
        'route_card_id',
        'status',
        [db.sequelize.fn('COUNT', db.sequelize.col('id')), 'count']
      ],
      where: { route_card_id: { [Op.in]: routeCardIds } },
      group: ['route_card_id', 'status']
    });
    const opCountsByRouteCardId = new Map();
    opStatusRows.forEach((r) => {
      const rcId = r.get('route_card_id');
      const st = r.get('status');
      const count = parseInt(r.get('count'), 10) || 0;
      if (!opCountsByRouteCardId.has(rcId)) opCountsByRouteCardId.set(rcId, {});
      opCountsByRouteCardId.get(rcId)[st] = count;
    });

    const opRows = await db.RouteCardOperation.findAll({
      where: { route_card_id: { [Op.in]: routeCardIds } },
      attributes: ['id', 'route_card_id', 'status'],
      include: [
        {
          model: db.ProductionQC,
          as: 'productionQC',
          attributes: ['id', 'status'],
          required: false
        }
      ]
    });
    opRows.forEach((op) => {
      const rcId = op.route_card_id;
      const st = String(op.status || '');
      if (st === 'COMPLETED' && String(op.productionQC?.status || '') !== 'APPROVED') {
        qcPendingByRouteCardId.set(rcId, (qcPendingByRouteCardId.get(rcId) || 0) + 1);
      }
      if (String(op.productionQC?.status || '') === 'IN_PROGRESS') {
        qcInProgressByRouteCardId.set(rcId, (qcInProgressByRouteCardId.get(rcId) || 0) + 1);
      }
    });

    const finalInspectionRows = await db.ProductionFinalInspection.findAll({
      where: { route_card_id: { [Op.in]: routeCardIds } },
      attributes: ['id', 'route_card_id', 'status', 'final_inspection_number']
    });
    finalInspectionRows.forEach((row) => {
      finalInspectionByRouteCardId.set(row.route_card_id, {
        id: row.id,
        status: row.status,
        final_inspection_number: row.final_inspection_number || ''
      });
    });
    if (allWorkOrderIds.length > 0) {
      const approvedRows = await db.ProductionFinalInspection.findAll({
        where: { status: 'APPROVED' },
        attributes: ['id'],
        include: [
          {
            model: db.RouteCard,
            as: 'routeCard',
            required: true,
            attributes: ['id', 'work_order_id'],
            where: { work_order_id: { [Op.in]: allWorkOrderIds } }
          }
        ]
      });
      approvedRows.forEach((fi) => {
        const woId = parseInt(fi?.routeCard?.work_order_id, 10);
        if (Number.isFinite(woId) && woId > 0) approvedFinalQcByWorkOrderId.set(woId, true);
      });
    }

    const reworkRows = await db.RouteCard.findAll({
      where: { previous_route_card_id: { [Op.in]: routeCardIds } },
      attributes: ['id', 'previous_route_card_id', 'status']
    });
    reworkRows.forEach((rc) => {
      const key = rc.previous_route_card_id;
      const prev = reworkSummaryByRouteCardId.get(key) || { total: 0, open: 0, closed: 0 };
      prev.total += 1;
      if (String(rc.status || '') === 'OPEN') prev.open += 1;
      if (String(rc.status || '') === 'CLOSED') prev.closed += 1;
      reworkSummaryByRouteCardId.set(key, prev);
    });

    routeCardIds.forEach((rcId) => {
      const counts = opCountsByRouteCardId.get(rcId) || {};
      const completed = counts.COMPLETED || 0;
      const skipped = counts.SKIPPED || 0;
      const cancelled = counts.CANCELLED || 0;
      const totalOps = Object.values(counts).reduce((sum, v) => sum + (parseInt(v, 10) || 0), 0);
      const allOpsTerminal = totalOps > 0 ? (completed + skipped + cancelled) === totalOps : false;
      const qcPending = qcPendingByRouteCardId.get(rcId) || 0;
      const qcInProgress = qcInProgressByRouteCardId.get(rcId) || 0;
      const rework = reworkSummaryByRouteCardId.get(rcId) || { total: 0, open: 0, closed: 0 };
      const productionClosureReady = allOpsTerminal && qcPending === 0 && rework.open === 0;
      const assemblyCompleted = productionClosureReady && rework.total === 0;
      let operationWiseQcStatus = 'PENDING';
      if (totalOps <= 0) {
        operationWiseQcStatus = 'PENDING';
      } else if (qcInProgress > 0 || (counts.IN_PROGRESS || 0) > 0) {
        operationWiseQcStatus = 'IN_PROGRESS';
      } else if (qcPending > 0 || (counts.PENDING || 0) > 0) {
        operationWiseQcStatus = 'PENDING';
      } else if (allOpsTerminal && qcPending === 0) {
        operationWiseQcStatus = 'COMPLETED';
      }

      const finalInspection = finalInspectionByRouteCardId.get(rcId) || null;
      let finalQcStatus = finalInspection?.status || null;
      if (!finalQcStatus) {
        if (productionClosureReady) finalQcStatus = 'PENDING';
        else if ((counts.IN_PROGRESS || 0) > 0) finalQcStatus = 'IN_PROGRESS';
        else if ((counts.PENDING || 0) > 0 || qcPending > 0 || rework.open > 0) finalQcStatus = 'PENDING';
      }
      const woId = parseInt(workOrderIdByRouteCardId.get(rcId), 10);
      const hasApprovedFinalQcInWo = Number.isFinite(woId) && approvedFinalQcByWorkOrderId.get(woId) === true;
      if (hasApprovedFinalQcInWo) {
        if (operationWiseQcStatus === 'COMPLETED') operationWiseQcStatus = 'APPROVED';
        if (finalQcStatus === 'COMPLETED' || (finalQcStatus == null && productionClosureReady)) finalQcStatus = 'APPROVED';
      }
      assemblyCompletedByRouteCardId.set(rcId, {
        qc_pending_count: qcPending,
        rework_summary: rework,
        production_closure_ready: productionClosureReady,
        assembly_completed: assemblyCompleted,
        operation_wise_qc_status: operationWiseQcStatus,
        final_qc_status: finalQcStatus,
        final_inspection_number: finalInspection?.final_inspection_number || ''
      });
    });
  }

  response.items = (response.items || []).map((w) => {
    const json = typeof w.toJSON === 'function' ? w.toJSON() : w;
    // Always prefer original (parent) route card selected by map to avoid picking a rework RC row.
    const mappedRcId = routeCardByWorkOrderId.get(json?.id);
    const mappedRcMeta = routeCardMetaByWorkOrderId.get(json?.id);
    const rcId = mappedRcId || json?.routeCard?.id;
    const totals = rcId ? (totalsByRouteCardId.get(rcId) || {}) : {};
    const closure = rcId ? (assemblyCompletedByRouteCardId.get(rcId) || {}) : {};
    const reworkTotal = totals.rework_total || 0;
    const completedTotal = totals.completed_total || 0;
    const hasRework = reworkTotal > 0;
    const baseRouteCard = mappedRcMeta || json.routeCard || null;
    return {
      ...json,
      routeCard: baseRouteCard
        ? {
            ...baseRouteCard,
            has_rework: hasRework,
            rework_qty_total: reworkTotal,
            completed_qty_total: completedTotal,
            qc_pending_count: closure.qc_pending_count || 0,
            rework_summary: closure.rework_summary || { total: 0, open: 0, closed: 0 },
            production_closure_ready: Boolean(closure.production_closure_ready),
            assembly_completed: Boolean(closure.assembly_completed),
            operation_wise_qc_status: closure.operation_wise_qc_status || null,
            final_qc_status: closure.final_qc_status || null,
            final_inspection_number: closure.final_inspection_number || ''
          }
        : null,
      route_card_has_rework: hasRework,
      route_card_rework_qty_total: reworkTotal,
      route_card_completed_qty_total: completedTotal,
      route_card_qc_pending_count: closure.qc_pending_count || 0,
      route_card_rework_summary: closure.rework_summary || { total: 0, open: 0, closed: 0 },
      route_card_production_closure_ready: Boolean(closure.production_closure_ready),
      route_card_assembly_completed: Boolean(closure.assembly_completed),
      route_card_operation_wise_qc_status: closure.operation_wise_qc_status || null,
      route_card_final_qc_status: closure.final_qc_status || null,
      route_card_final_inspection_number: closure.final_inspection_number || ''
    };
  });

  // Add dispatch status on Work Order list based on linked Production PO dispatch progress.
  const woItems = Array.isArray(response.items) ? response.items : [];
  const poIdsForDispatch = Array.from(
    new Set(
      woItems
        .map((w) => parseInt(w?.production_order_id, 10))
        .filter((id) => Number.isFinite(id) && id > 0)
    )
  );

  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 = woItems.map((w) => {
      const json = w?.toJSON ? w.toJSON() : w;
      const productionQty = parseFloat(json?.order_quantity || 0) || 0;
      const poId = parseInt(json?.production_order_id, 10);
      const dispatchedQty = Number.isFinite(poId) ? (parseFloat(dispatchedByPoId.get(poId) || 0) || 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, 'Work Orders retrieved successfully', response);
});

/**
 * GET /api/v1/production/work-order/:id
 */
exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const record = await db.WorkOrder.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.Routing, as: 'routing', attributes: ['id', 'routing_number'] },
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        attributes: ['id', 'production_order_number', 'order_quantity', 'remarks', 'start_date', 'finish_date'],
        include: [{ model: db.BOMHeader, as: 'bom', attributes: ['id', 'bom_number', 'drawing_number', 'revision_number'] }]
      },
      { model: db.BusinessPartner, as: 'customer' },
      { model: db.Material, as: 'finishedMaterial' },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      { model: db.User, as: 'approvedByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      {
        model: db.WorkOrderOperationPlan,
        as: 'operations',
        include: [
          { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'] },
          { model: db.WorkCenter, as: 'workCenter', attributes: ['id', 'work_center_code', 'description'] },
          { model: db.Machine, as: 'machine', attributes: ['id', 'machine_code', 'machine_description'] }
        ]
      }
    ],
    order: [[{ model: db.WorkOrderOperationPlan, as: 'operations' }, 'operation_sequence_no', 'ASC']]
  });

  if (!record) return sendError(res, 'Work Order not found', 404);
  const json = typeof record.toJSON === 'function' ? record.toJSON() : record;

  if (json?.production_order_id) {
    const allocRow = await db.WorkOrder.findOne({
      where: {
        company_id: json.company_id,
        production_order_id: json.production_order_id
      },
      attributes: [[db.sequelize.fn('SUM', db.sequelize.col('work_order_quantity')), 'allocated_qty']],
      raw: true
    });
    const allocatedQty = parseFloat(allocRow?.allocated_qty || 0) || 0;
    const orderQty = parseFloat(json?.order_quantity || json?.productionOrder?.order_quantity || 0) || 0;
    const balanceQty = Math.max(orderQty - allocatedQty, 0);

    const poHeader = await db.ProductionOrder.findByPk(json.production_order_id, {
      attributes: ['order_quantity', 'status', 'batch_planning_enabled', 'released_at', 'released_by'],
      include: [{ model: db.User, as: 'releasedByUser', attributes: ['username', 'email'], required: false }]
    });
    const poHeaderJson = poHeader?.toJSON?.() || poHeader || {};
    const batchSt = (s) => String(s || '').toUpperCase();
    const batchRowsDetailed = await db.ProductionOrderBatch.findAll({
      where: { production_order_id: json.production_order_id },
      include: [{ model: db.User, as: 'releasedByUser', attributes: ['username', 'email'], required: false }],
      order: [['batch_sequence', 'ASC']]
    });
    const batchRowsForRelease = batchRowsDetailed.map((b) => {
      const x = b.toJSON ? b.toJSON() : b;
      return { quantity: x.quantity, status: x.status };
    });
    const poOrderQty = parseFloat(poHeaderJson.order_quantity || orderQty) || 0;
    let releasedForMrpQty = batchRowsForRelease
      .filter((b) => ['RELEASED', 'IN_PROGRESS'].includes(batchSt(b.status)))
      .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
    if (!poHeaderJson.batch_planning_enabled && batchRowsForRelease.length === 0) {
      if (['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(batchSt(poHeaderJson.status))) {
        releasedForMrpQty = poOrderQty;
      } else {
        releasedForMrpQty = 0;
      }
    }
    releasedForMrpQty = Number(releasedForMrpQty.toFixed(3));

    const batchesJsonForRecent = batchRowsDetailed.map((b) => (b.toJSON ? b.toJSON() : b));
    const mostRecentReleasedQty = computeMostRecentReleasedBatchQty({
      orderQtyVal: poOrderQty,
      hasBatches: batchRowsForRelease.length > 0,
      poStatus: poHeaderJson.status,
      batchesJson: batchesJsonForRecent
    });

    const release_history = [
      {
        kind: 'order_total',
        label: 'Order quantity (total on PO)',
        quantity: Number(poOrderQty.toFixed(3)),
        quantity_role: 'ORDER_TOTAL',
        status: poHeaderJson.status,
        released_at:
          !batchRowsForRelease.length && poHeaderJson.released_at ? poHeaderJson.released_at : null,
        released_by:
          !batchRowsForRelease.length && poHeaderJson.released_at
            ? poHeaderJson.releasedByUser?.username || poHeaderJson.releasedByUser?.email || null
            : null
      }
    ];
    batchRowsDetailed.forEach((b) => {
      const bj = b.toJSON ? b.toJSON() : b;
      release_history.push({
        kind: 'batch',
        batch_sequence: bj.batch_sequence,
        batch_label: bj.batch_label || `Batch ${bj.batch_sequence}`,
        quantity: Number((parseFloat(bj.quantity || 0) || 0).toFixed(3)),
        quantity_role: 'BATCH',
        status: bj.status,
        released_at: bj.released_at,
        released_by: bj.releasedByUser?.username || bj.releasedByUser?.email || null
      });
    });
    json.production_order_release_history = release_history;

    json.productionOrder = {
      ...(json.productionOrder || {}),
      work_order_allocated_qty: Number(allocatedQty.toFixed(3)),
      work_order_balance_qty: Number(balanceQty.toFixed(3)),
      released_for_mrp_qty: releasedForMrpQty,
      most_recent_released_qty: mostRecentReleasedQty
    };

    json.raw_materials = await buildWorkOrderRawMaterialSnapshot({
      productionOrderId: json.production_order_id
    });
    json.nesting_sheet_rows = await buildNestingSheetRows({
      productionOrderId: json.production_order_id
    });
  }

  // Enrich single Work Order view with route-card/QC/final-QC/dispatch statuses (same semantics as list).
  const woId = parseInt(json?.id, 10);
  const rootRouteCard = await db.RouteCard.findOne({
    where: { work_order_id: woId, previous_route_card_id: null },
    attributes: ['id', 'route_card_number', 'status']
  }) || await db.RouteCard.findOne({
    where: { work_order_id: woId },
    attributes: ['id', 'route_card_number', 'status']
  });

  if (rootRouteCard) {
    const rcId = rootRouteCard.id;

    const opRows = await db.RouteCardOperation.findAll({
      where: { route_card_id: rcId },
      attributes: ['id', 'status', 'operation_sequence_no', 'completed_qty'],
      include: [
        {
          model: db.ProductionQC,
          as: 'productionQC',
          attributes: ['id', 'status'],
          required: false
        }
      ],
      order: [['operation_sequence_no', 'ASC'], ['id', 'ASC']]
    });

    const counts = {};
    let qcPending = 0;
    let qcInProgress = 0;
    let latestCompletedBySeq = { seqNo: -1, id: -1, qty: 0 };
    opRows.forEach((op) => {
      const st = String(op.status || '');
      counts[st] = (counts[st] || 0) + 1;
      const qcSt = String(op.productionQC?.status || '');
      if (st === 'COMPLETED' && qcSt !== 'APPROVED') qcPending += 1;
      if (qcSt === 'IN_PROGRESS') qcInProgress += 1;

      const qty = parseFloat(op.completed_qty || 0) || 0;
      const seqNo = parseInt(op.operation_sequence_no, 10) || 0;
      if (qty > 0 && (seqNo > latestCompletedBySeq.seqNo || (seqNo === latestCompletedBySeq.seqNo && op.id > latestCompletedBySeq.id))) {
        latestCompletedBySeq = { seqNo, id: op.id, qty };
      }
    });

    const totalOps = Object.values(counts).reduce((sum, v) => sum + (parseInt(v, 10) || 0), 0);
    const completed = counts.COMPLETED || 0;
    const skipped = counts.SKIPPED || 0;
    const cancelled = counts.CANCELLED || 0;
    const allOpsTerminal = totalOps > 0 ? (completed + skipped + cancelled) === totalOps : false;

    const finalInspection = await db.ProductionFinalInspection.findOne({
      where: { route_card_id: rcId },
      attributes: ['id', 'status', 'final_inspection_number']
    });
    const approvedFinalQcInWo = await db.ProductionFinalInspection.findOne({
      where: { status: 'APPROVED' },
      attributes: ['id'],
      include: [
        {
          model: db.RouteCard,
          as: 'routeCard',
          required: true,
          attributes: ['id', 'work_order_id'],
          where: { work_order_id: woId }
        }
      ]
    });

    const reworkRows = await db.RouteCard.findAll({
      where: { previous_route_card_id: rcId },
      attributes: ['id', 'status']
    });
    const reworkSummary = { total: 0, open: 0, closed: 0 };
    reworkRows.forEach((r) => {
      reworkSummary.total += 1;
      if (String(r.status || '') === 'OPEN') reworkSummary.open += 1;
      if (String(r.status || '') === 'CLOSED') reworkSummary.closed += 1;
    });

    const productionClosureReady = allOpsTerminal && qcPending === 0 && reworkSummary.open === 0;
    const assemblyCompleted = productionClosureReady && reworkSummary.total === 0;
    let operationWiseQcStatus = 'PENDING';
    if (totalOps <= 0) operationWiseQcStatus = 'PENDING';
    else if (qcInProgress > 0 || (counts.IN_PROGRESS || 0) > 0) operationWiseQcStatus = 'IN_PROGRESS';
    else if (qcPending > 0 || (counts.PENDING || 0) > 0) operationWiseQcStatus = 'PENDING';
    else if (allOpsTerminal && qcPending === 0) operationWiseQcStatus = 'COMPLETED';

    let finalQcStatus = finalInspection?.status || null;
    if (!finalQcStatus) {
      if (productionClosureReady) finalQcStatus = 'PENDING';
      else if ((counts.IN_PROGRESS || 0) > 0) finalQcStatus = 'IN_PROGRESS';
      else if ((counts.PENDING || 0) > 0 || qcPending > 0 || reworkSummary.open > 0) finalQcStatus = 'PENDING';
    }
    if (approvedFinalQcInWo) {
      if (operationWiseQcStatus === 'COMPLETED') operationWiseQcStatus = 'APPROVED';
      if (finalQcStatus === 'COMPLETED' || (finalQcStatus == null && productionClosureReady)) finalQcStatus = 'APPROVED';
    }

    json.routeCard = {
      id: rootRouteCard.id,
      route_card_number: rootRouteCard.route_card_number,
      status: rootRouteCard.status,
      has_rework: reworkSummary.total > 0,
      rework_qty_total: 0,
      completed_qty_total: latestCompletedBySeq.qty || 0,
      qc_pending_count: qcPending,
      rework_summary: reworkSummary,
      production_closure_ready: productionClosureReady,
      assembly_completed: assemblyCompleted,
      operation_wise_qc_status: operationWiseQcStatus,
      final_qc_status: finalQcStatus,
      final_inspection_number: finalInspection?.final_inspection_number || ''
    };
    json.route_card_operation_wise_qc_status = operationWiseQcStatus;
    json.route_card_final_qc_status = finalQcStatus;
    json.route_card_status = rootRouteCard.status || 'NOT_GENERATED';
  } else {
    json.routeCard = null;
    json.route_card_status = 'NOT_GENERATED';
    json.route_card_operation_wise_qc_status = null;
    json.route_card_final_qc_status = null;
  }

  // Dispatch status by linked Production Order.
  const productionQty = parseFloat(json?.order_quantity || json?.productionOrder?.order_quantity || 0) || 0;
  const poId = parseInt(json?.production_order_id, 10);
  let dispatchedQty = 0;
  if (Number.isFinite(poId) && poId > 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;
    }

    if (hasDispatchPoIdCol) {
      const qty = await db.FGDispatch.sum('dispatch_qty', {
        where: {
          production_order_id: poId,
          status: { [Op.ne]: 'CANCELLED' }
        }
      });
      dispatchedQty = parseFloat(qty || 0) || 0;
    } else {
      const inwardRows = await db.ProductionFGInward.findAll({
        where: { production_order_id: poId },
        attributes: ['company_id', 'plant_id', 'fiscal_year_id', 'finished_material_id', 'storage_location_id']
      });
      const scopeKeys = Array.from(new Set(inwardRows.map((r) => `${r.company_id}:${r.plant_id}:${r.fiscal_year_id}:${r.finished_material_id}:${r.storage_location_id}`)));
      for (const k of scopeKeys) {
        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' }
          }
        });
        dispatchedQty += parseFloat(qty || 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';
  }
  json.dispatched_qty = Number(dispatchedQty.toFixed(3));
  json.balance_dispatch_qty = Number(balanceDispatchQty.toFixed(3));
  json.dispatch_status = dispatchStatus;

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

/**
 * GET /api/v1/production/work-order/prefill/:productionOrderId
 * Returns header prefill + suggested operation planning rows from routing.
 */
exports.getPrefillFromProductionOrder = asyncHandler(async (req, res) => {
  const { productionOrderId } = req.params;
  const companyId = req.user?.companyId;

  if (!productionOrderId) return sendError(res, 'Production Order ID is required', 400);

  const po = await db.ProductionOrder.findByPk(productionOrderId, {
    include: [
      { model: db.CustomerPO, as: 'customerPO', include: [{ model: db.BusinessPartner, as: 'customer' }] },
      { model: db.Material, as: 'finishedMaterial' },
      { model: db.User, as: 'releasedByUser', attributes: ['username', 'email'], required: false },
      { model: db.BOMHeader, as: 'bom', required: false, attributes: ['id', 'bom_number', 'drawing_number', 'revision_number'] },
      {
        model: db.Routing,
        as: 'routing',
        include: [
          {
            model: db.RoutingOperation,
            as: 'operations',
            where: { status: 'ACTIVE' },
            required: false,
            include: [{ model: db.WorkCenter, as: 'workCenter' }],
            order: [['operation_number', 'ASC']]
          }
        ]
      }
    ]
  });

  if (!po) return sendError(res, 'Production Order not found', 404);
  if (companyId && String(po.company_id) !== String(companyId)) return sendError(res, 'Access denied for this Production Order', 403);

  // Same "released for MRP" semantics as production order list (batch release totals).
  const batchStatus = (s) => String(s || '').toUpperCase();
  const batchRows = await db.ProductionOrderBatch.findAll({
    where: { production_order_id: po.id },
    attributes: ['quantity', 'status'],
    raw: true
  });
  const orderQtyVal = parseFloat(po.order_quantity || 0) || 0;
  let releasedForMrpQty = batchRows
    .filter((b) => ['RELEASED', 'IN_PROGRESS'].includes(batchStatus(b.status)))
    .reduce((sum, b) => sum + (parseFloat(b.quantity || 0) || 0), 0);
  if (!po.batch_planning_enabled && batchRows.length === 0) {
    if (['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(batchStatus(po.status))) {
      releasedForMrpQty = orderQtyVal;
    } else {
      releasedForMrpQty = 0;
    }
  }
  releasedForMrpQty = Number(releasedForMrpQty.toFixed(3));

  const batchHistoryRows = await db.ProductionOrderBatch.findAll({
    where: { production_order_id: po.id },
    include: [{ model: db.User, as: 'releasedByUser', attributes: ['username', 'email'], required: false }],
    order: [['batch_sequence', 'ASC']]
  });
  const batchesJson = batchHistoryRows.map((b) => (typeof b.toJSON === 'function' ? b.toJSON() : b));
  const mostRecentReleasedQty = computeMostRecentReleasedBatchQty({
    orderQtyVal,
    hasBatches: batchRows.length > 0,
    poStatus: po.status,
    batchesJson
  });

  const release_history = [
    {
      kind: 'order_total',
      label: 'Order quantity (total on PO)',
      quantity: Number(orderQtyVal.toFixed(3)),
      quantity_role: 'ORDER_TOTAL',
      status: po.status,
      released_at: !batchRows.length && po.released_at ? po.released_at : null,
      released_by:
        !batchRows.length && po.released_at
          ? po.releasedByUser?.username || po.releasedByUser?.email || null
          : null
    }
  ];
  batchHistoryRows.forEach((b) => {
    const bj = typeof b.toJSON === 'function' ? b.toJSON() : b;
    release_history.push({
      kind: 'batch',
      batch_sequence: bj.batch_sequence,
      batch_label: bj.batch_label || `Batch ${bj.batch_sequence}`,
      quantity: Number((parseFloat(bj.quantity || 0) || 0).toFixed(3)),
      quantity_role: 'BATCH',
      status: bj.status,
      released_at: bj.released_at,
      released_by: bj.releasedByUser?.username || bj.releasedByUser?.email || null
    });
  });

  const customer = po.customerPO?.customer || null;
  const material = po.finishedMaterial || null;
  const bom = po.bom || null;
  let routing = po.routing || null;
  if (!routing && po.routing_id) {
    routing = await db.Routing.findByPk(po.routing_id, {
      attributes: ['id', 'routing_number', 'status'],
      include: [
        {
          model: db.RoutingOperation,
          as: 'operations',
          where: { status: 'ACTIVE' },
          required: false,
          include: [{ model: db.WorkCenter, as: 'workCenter' }]
        }
      ]
    });
  }
  // Same as Production Order UI: if PO has no routing on file, use first ACTIVE routing for the finished material.
  if (!routing && po.finished_material_id) {
    const fallbackRow = await db.Routing.findOne({
      where: {
        material_id: po.finished_material_id,
        status: 'ACTIVE',
        company_id: po.company_id,
        plant_id: po.plant_id
      },
      attributes: ['id'],
      order: [
        ['routing_number', 'ASC'],
        ['id', 'ASC']
      ]
    });
    if (fallbackRow?.id) {
      routing = await db.Routing.findByPk(fallbackRow.id, {
        attributes: ['id', 'routing_number', 'status'],
        include: [
          {
            model: db.RoutingOperation,
            as: 'operations',
            where: { status: 'ACTIVE' },
            required: false,
            include: [{ model: db.WorkCenter, as: 'workCenter' }]
          }
        ]
      });
    }
  }
  const drawingNumber = bom?.drawing_number || material?.drawing_number || null;
  const revisionNumber = bom?.revision_number || material?.revision_number || null;

  const routingForOps = routing || po.routing;
  const suggestedOperations = (routingForOps?.operations || [])
    .slice()
    .sort((a, b) => (a.operation_number || 0) - (b.operation_number || 0))
    .map((op) => {
      const machineTime = parseFloat(op.machine_time || 0);
      const laborTime = parseFloat(op.labor_time || 0);
      const standardMinutes = Math.round(Math.max(machineTime, laborTime) * 60);
      const opCode = op.operation_code ? String(op.operation_code).trim() : null;
      return {
        operation_sequence_no: op.operation_number,
        operation_name: opCode || op.operation_description || `Operation ${op.operation_number}`,
        work_center_id: op.work_center_id,
        work_center: op.workCenter
          ? { id: op.workCenter.id, work_center_code: op.workCenter.work_center_code, description: op.workCenter.description }
          : null,
        machine_id: op.machine_id || null,
        planned_qty:
          mostRecentReleasedQty > 0
            ? mostRecentReleasedQty
            : releasedForMrpQty > 0
              ? releasedForMrpQty
              : parseFloat(po.order_quantity || 0),
        target_date: null,
        standard_time_minutes: isNaN(standardMinutes) ? null : standardMinutes,
        setup_time_minutes: op.setup_time != null ? parseInt(op.setup_time, 10) : null,
        status: 'PLANNED'
      };
    });

  const rawMaterials = await buildWorkOrderRawMaterialSnapshot({
    productionOrderId: po.id
  });
  const nestingSheetRows = await buildNestingSheetRows({
    productionOrderId: po.id
  });

  sendResponse(res, 200, true, 'Prefill data retrieved successfully', {
    production_order_id: po.id,
    production_order_number: po.production_order_number,
    company_id: po.company_id,
    plant_id: po.plant_id,
    fiscal_year_id: po.fiscal_year_id,
    routing_id: routing?.id || po.routing_id || null,
    routing_number: routing?.routing_number || null,
    customer_id: customer?.id || null,
    customer_name: customer?.name || customer?.legal_name || null,
    finished_material_id: material?.id || null,
    item_code: material?.material_number || null,
    item_name: material?.description || null,
    bom_id: bom?.id || null,
    bom_number: bom?.bom_number || null,
    bom_base_quantity: bom?.base_quantity != null ? parseFloat(bom.base_quantity) : null,
    drawing_number: drawingNumber,
    revision_number: revisionNumber,
    order_quantity: po.order_quantity != null ? parseFloat(po.order_quantity) : null,
    released_for_mrp_qty: releasedForMrpQty,
    most_recent_released_qty: mostRecentReleasedQty,
    release_history,
    planned_start_date: po.start_date || null,
    planned_end_date: po.finish_date || null,
    remarks: po.remarks || null,
    suggested_operations: suggestedOperations,
    raw_materials: rawMaterials,
    nesting_sheet_rows: nestingSheetRows
  });
});

/**
 * POST /api/v1/production/work-order
 */
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) return sendError(res, 'Plant ID and Fiscal Year ID are required', 400);
  if (!data.production_order_id) return sendError(res, 'Production Order ID is required', 400);

  const transaction = await db.sequelize.transaction();
  try {
    const productionOrder = await db.ProductionOrder.findByPk(data.production_order_id, {
      include: [{ model: db.CustomerPO, as: 'customerPO' }],
      transaction
    });

    if (!productionOrder) {
      await transaction.rollback();
      return sendError(res, 'Production Order not found', 404);
    }
    if (String(productionOrder.company_id) !== String(companyId)) {
      await transaction.rollback();
      return sendError(res, 'Access denied for this Production Order', 403);
    }

    const existingForPO = await db.WorkOrder.findAll({
      where: {
        company_id: companyId,
        production_order_id: productionOrder.id
      },
      attributes: ['id', 'work_order_number', 'production_order_id', 'work_order_quantity'],
      transaction
    });
    const orderQtyLimit = parseFloat(productionOrder.order_quantity || 0);
    const allocatedQty = existingForPO.reduce((sum, w) => sum + (parseFloat(w?.work_order_quantity || 0) || 0), 0);
    const effectiveOrderQty = Number.isFinite(orderQtyLimit) ? orderQtyLimit : 0;
    const remainingQty = Math.max(effectiveOrderQty - allocatedQty, 0);
    if (remainingQty <= 0) {
      await transaction.rollback();
      return sendError(res, 'Work Order cannot be created. Production Order quantity is fully allocated.', 400);
    }
    const requestedWOQty = data.work_order_quantity != null ? parseFloat(data.work_order_quantity) : null;
    if (requestedWOQty != null && requestedWOQty > remainingQty) {
      await transaction.rollback();
      return sendError(res, `Work Order Quantity cannot be greater than Balance Quantity (${remainingQty})`, 400);
    }
    if (Array.isArray(data.operations)) {
      const plannedQtyLimit = requestedWOQty != null ? requestedWOQty : remainingQty;
      const invalidPlanned = data.operations.find((op) => parseFloat(op?.planned_qty || 0) > plannedQtyLimit);
      if (invalidPlanned) {
        await transaction.rollback();
        return sendError(res, `Planned Qty cannot be greater than Work Order Quantity (${plannedQtyLimit})`, 400);
      }
    }

    const workOrderNumber = data.work_order_number || (await generateWorkOrderNumber(companyId, data.fiscal_year_id, transaction));

    const customerId = productionOrder.customer_po_id
      ? productionOrder.customerPO?.customer_id || null
      : null;

    const workOrder = await db.WorkOrder.create(
      {
        company_id: companyId,
        plant_id: data.plant_id,
        fiscal_year_id: data.fiscal_year_id,
        work_order_number: workOrderNumber,
        production_order_id: productionOrder.id,
        routing_id: data.routing_id != null && data.routing_id !== '' ? parseInt(data.routing_id, 10) : (productionOrder.routing_id || null),
        customer_id: customerId,
        finished_material_id: productionOrder.finished_material_id || null,
        order_quantity: productionOrder.order_quantity != null ? parseFloat(productionOrder.order_quantity) : null,
        batch_quantity: data.batch_quantity != null ? parseFloat(data.batch_quantity) : null,
        work_order_quantity: data.work_order_quantity != null ? parseFloat(data.work_order_quantity) : 0,
        planned_start_date: data.planned_start_date || productionOrder.start_date || null,
        planned_end_date: data.planned_end_date || productionOrder.finish_date || null,
        priority: data.priority || 'MEDIUM',
        status: data.status || 'PLANNED',
        created_by: userId,
        approved_by: data.approved_by || null,
        remarks: data.remarks != null ? data.remarks : productionOrder.remarks || null
      },
      { transaction }
    );

    const operationsInput = Array.isArray(data.operations) ? data.operations : null;
    const operationsToCreate =
      operationsInput && operationsInput.length > 0
        ? operationsInput
        : await buildOperationDefaultsFromRouting(productionOrder.id, workOrder.work_order_quantity);

    if (operationsToCreate && operationsToCreate.length > 0) {
      const normalized = operationsToCreate.map((op) => ({
        work_order_id: workOrder.id,
        operation_sequence_no: parseInt(op.operation_sequence_no, 10),
        operation_name: op.operation_name,
        department_id: op.department_id != null && op.department_id !== '' ? parseInt(op.department_id, 10) : null,
        work_center_id: op.work_center_id,
        machine_id: op.machine_id || null,
        planned_qty: op.planned_qty != null ? parseFloat(op.planned_qty) : 0,
        target_date: op.target_date || null,
        standard_time_minutes: op.standard_time_minutes != null ? parseInt(op.standard_time_minutes, 10) : null,
        setup_time_minutes: op.setup_time_minutes != null ? parseInt(op.setup_time_minutes, 10) : null,
        status: op.status || 'PLANNED',
        created_by: userId,
        updated_by: userId
      }));

      await db.WorkOrderOperationPlan.bulkCreate(normalized, { transaction });
    }

    await transaction.commit();

    const created = await db.WorkOrder.findByPk(workOrder.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
        { model: db.Routing, as: 'routing', attributes: ['id', 'routing_number'] },
        { model: db.BusinessPartner, as: 'customer' },
        { model: db.Material, as: 'finishedMaterial' },
        { model: db.WorkOrderOperationPlan, as: 'operations' }
      ],
      order: [[{ model: db.WorkOrderOperationPlan, as: 'operations' }, 'operation_sequence_no', 'ASC']]
    });

    sendResponse(res, 201, true, 'Work Order created successfully', created);
  } catch (error) {
    await transaction.rollback();
    console.error('Error creating Work Order:', error);
    sendError(res, error.message || 'Failed to create Work Order', 500);
  }
});

/**
 * PUT /api/v1/production/work-order/:id
 */
exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const data = req.body || {};
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  const transaction = await db.sequelize.transaction();
  try {
    const workOrder = await db.WorkOrder.findByPk(id, { transaction });
    if (!workOrder) {
      await transaction.rollback();
      return sendError(res, 'Work Order not found', 404);
    }
    if (companyId && String(workOrder.company_id) !== String(companyId)) {
      await transaction.rollback();
      return sendError(res, 'Access denied for this Work Order', 403);
    }

    if (data.production_order_id && String(data.production_order_id) !== String(workOrder.production_order_id)) {
      await transaction.rollback();
      return sendError(res, 'Changing Production Order is not allowed for an existing Work Order', 400);
    }
    const orderQtyLimit = parseFloat(workOrder.order_quantity || 0);
    const requestedWOQty = data.work_order_quantity != null ? parseFloat(data.work_order_quantity) : null;
    if (requestedWOQty != null && Number.isFinite(orderQtyLimit) && requestedWOQty > orderQtyLimit) {
      await transaction.rollback();
      return sendError(res, `Work Order Quantity cannot be greater than Order Quantity (${orderQtyLimit})`, 400);
    }
    if (Array.isArray(data.operations)) {
      const invalidPlanned = data.operations.find((op) => parseFloat(op?.planned_qty || 0) > orderQtyLimit);
      if (invalidPlanned) {
        await transaction.rollback();
        return sendError(res, `Planned Qty cannot be greater than Order Quantity (${orderQtyLimit})`, 400);
      }
    }

    await workOrder.update(
      {
        batch_quantity: data.batch_quantity != null ? parseFloat(data.batch_quantity) : workOrder.batch_quantity,
        work_order_quantity: data.work_order_quantity != null ? parseFloat(data.work_order_quantity) : workOrder.work_order_quantity,
        planned_start_date: data.planned_start_date !== undefined ? data.planned_start_date : workOrder.planned_start_date,
        planned_end_date: data.planned_end_date !== undefined ? data.planned_end_date : workOrder.planned_end_date,
        priority: data.priority || workOrder.priority,
        status: data.status || workOrder.status,
        approved_by: data.approved_by !== undefined ? data.approved_by : workOrder.approved_by,
        remarks: data.remarks !== undefined ? data.remarks : workOrder.remarks,
        plant_id: data.plant_id || workOrder.plant_id,
        fiscal_year_id: data.fiscal_year_id || workOrder.fiscal_year_id,
        routing_id: data.routing_id !== undefined ? (data.routing_id != null && data.routing_id !== '' ? parseInt(data.routing_id, 10) : null) : workOrder.routing_id
      },
      { transaction }
    );

    if (Array.isArray(data.operations)) {
      await db.WorkOrderOperationPlan.destroy({ where: { work_order_id: workOrder.id }, transaction });

      const normalized = data.operations.map((op) => ({
        work_order_id: workOrder.id,
        operation_sequence_no: parseInt(op.operation_sequence_no, 10),
        operation_name: op.operation_name,
        department_id: op.department_id != null && op.department_id !== '' ? parseInt(op.department_id, 10) : null,
        work_center_id: op.work_center_id,
        machine_id: op.machine_id || null,
        planned_qty: op.planned_qty != null ? parseFloat(op.planned_qty) : 0,
        target_date: op.target_date || null,
        standard_time_minutes: op.standard_time_minutes != null ? parseInt(op.standard_time_minutes, 10) : null,
        setup_time_minutes: op.setup_time_minutes != null ? parseInt(op.setup_time_minutes, 10) : null,
        status: op.status || 'PLANNED',
        created_by: userId,
        updated_by: userId
      }));

      if (normalized.length > 0) {
        await db.WorkOrderOperationPlan.bulkCreate(normalized, { transaction });
      }
    }

    await transaction.commit();

    const updated = await db.WorkOrder.findByPk(workOrder.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
        { model: db.Routing, as: 'routing', attributes: ['id', 'routing_number'] },
        { model: db.BusinessPartner, as: 'customer' },
        { model: db.Material, as: 'finishedMaterial' },
        { model: db.WorkOrderOperationPlan, as: 'operations' }
      ],
      order: [[{ model: db.WorkOrderOperationPlan, as: 'operations' }, 'operation_sequence_no', 'ASC']]
    });

    sendResponse(res, 200, true, 'Work Order updated successfully', updated);
  } catch (error) {
    await transaction.rollback();
    console.error('Error updating Work Order:', error);
    sendError(res, error.message || 'Failed to update Work Order', 500);
  }
});

/**
 * DELETE /api/v1/production/work-order/:id
 */
exports.delete = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const companyId = req.user?.companyId;

  const record = await db.WorkOrder.findByPk(id);
  if (!record) return sendError(res, 'Work Order not found', 404);
  if (companyId && String(record.company_id) !== String(companyId)) return sendError(res, 'Access denied for this Work Order', 403);

  await record.destroy(); // cascades to operation plans
  sendResponse(res, 200, true, 'Work Order deleted successfully', { id });
});

/**
 * POST /api/v1/production/work-order/:id/release
 * Set Work Order status to RELEASED (cannot release from CLOSED/COMPLETED).
 */
exports.release = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const companyId = req.user?.companyId;
  const userId = req.user?.userId || null;

  const record = await db.WorkOrder.findByPk(id, {
    include: [{ model: db.WorkOrderOperationPlan, as: 'operations' }]
  });
  if (!record) return sendError(res, 'Work Order not found', 404);
  if (companyId && String(record.company_id) !== String(companyId)) return sendError(res, 'Access denied for this Work Order', 403);

  if (['CLOSED', 'COMPLETED'].includes(record.status)) {
    return sendError(res, `Cannot release Work Order from status ${record.status}`, 400);
  }
  if (!record.operations || record.operations.length === 0) {
    return sendError(res, 'Cannot release Work Order without operations. Please add operations first.', 400);
  }

  await record.update({
    status: 'RELEASED',
    approved_by: record.approved_by || userId
  });

  const updated = await db.WorkOrder.findByPk(record.id, {
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.FiscalYear, as: 'fiscalYear', attributes: ['id', 'fiscal_year'] },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      { model: db.User, as: 'approvedByUser', attributes: ['id', 'username', 'first_name', 'last_name'] },
      { model: db.WorkOrderOperationPlan, as: 'operations' }
    ],
    order: [[{ model: db.WorkOrderOperationPlan, as: 'operations' }, 'operation_sequence_no', 'ASC']]
  });

  let routeCardPayload = null;
  let routeCardError = null;
  try {
    const rc = await routeCardService.generateFromWorkOrder({ workOrderId: record.id, userId });
    routeCardPayload = {
      id: rc.id,
      route_card_number: rc.route_card_number,
      status: rc.status,
      work_order_id: rc.work_order_id
    };
  } catch (e) {
    routeCardError = e.message || String(e);
  }

  const workOrderJson = updated && typeof updated.toJSON === 'function' ? updated.toJSON() : updated;

  sendResponse(res, 200, true, routeCardError ? 'Work Order released. Route Card was not created automatically.' : 'Work Order released and Route Card generated successfully', {
    work_order: workOrderJson,
    route_card: routeCardPayload,
    route_card_error: routeCardError
  });
});
