const db = require('../models');
const { Op } = require('sequelize');
const asyncHandler = require('../middlewares/asyncHandler');
const { sendResponse, sendError } = require('../utils/response');
const { getPagination, getPagingData } = require('../utils/pagination');
const { toNum } = require('../services/fgStockLedgerService');
const { generateNextNumber } = require('../services/numberFormatService');

const hasCol = (schema, col) => Boolean(schema && Object.prototype.hasOwnProperty.call(schema, col));

let dispatchSchemaCache = null;
const getDispatchSchema = async () => {
  if (dispatchSchemaCache) return dispatchSchemaCache;
  try {
    dispatchSchemaCache = await db.sequelize.getQueryInterface().describeTable('fg_dispatch');
  } catch (_) {
    dispatchSchemaCache = {};
  }
  return dispatchSchemaCache;
};

const normalizeDeliveryStatus = (row) => {
  const explicit = String(row?.delivery_status || '').toUpperCase();
  if (explicit === 'DELIVERED') return 'DELIVERED';
  if (explicit === 'IN_TRANSIT') return 'IN_TRANSIT';
  const legacy = String(row?.status || '').toUpperCase();
  if (legacy === 'DELIVERED' || legacy === 'CLOSED') return 'DELIVERED';
  return 'IN_TRANSIT';
};

const generateDeliveryChallanNo = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'DELIVERY_CHALLAN', fiscalYearId, transaction);
  } catch (_) {
    const year = new Date().getFullYear();
    const prefix = `DC${year}`;
    const last = await db.DeliveryChallan.findOne({
      where: {
        company_id: companyId,
        challan_no: { [Op.like]: `${prefix}%` }
      },
      attributes: ['challan_no'],
      order: [['challan_no', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    const next = (parseInt(String(last?.challan_no || '').slice(-4), 10) || 0) + 1;
    return `${prefix}${String(next).padStart(4, '0')}`;
  }
};

const getDispatchDeliveredQty = async (dispatchId, itemId, excludeChallanId = null, transaction = null) => {
  const where = {
    dispatch_id: dispatchId,
    status: { [Op.ne]: 'CANCELLED' }
  };
  if (excludeChallanId) where.id = { [Op.ne]: excludeChallanId };
  const challans = await db.DeliveryChallan.findAll({
    where,
    attributes: ['id', 'status', 'delivery_status'],
    transaction
  });
  const challanIds = challans.map((r) => r.id);
  if (!challanIds.length) return 0;

  const deliveredChallanIds = challans
    .filter((c) => normalizeDeliveryStatus(c) === 'DELIVERED')
    .map((c) => c.id);
  if (!deliveredChallanIds.length) return 0;

  const itemWhere = { delivery_challan_id: { [Op.in]: deliveredChallanIds } };
  if (itemId) itemWhere.material_id = itemId;
  const total = await db.DeliveryChallanItem.sum('delivered_qty', { where: itemWhere, transaction });
  return toNum(total);
};

const refreshDispatchDeliveryStatus = async (dispatchId, transaction) => {
  if (!dispatchId) return;
  const schema = await getDispatchSchema();
  if (!hasCol(schema, 'delivery_status')) return;

  const dispatch = await db.FGDispatch.findByPk(dispatchId, {
    attributes: ['id', 'dispatch_qty', 'item_id'],
    transaction
  });
  if (!dispatch) return;

  const deliveredQty = await getDispatchDeliveredQty(dispatch.id, dispatch.item_id, null, transaction);
  let nextStatus = 'NOT_DELIVERED';
  if (deliveredQty > 0 && deliveredQty < toNum(dispatch.dispatch_qty)) nextStatus = 'PARTIALLY_DELIVERED';
  if (deliveredQty >= toNum(dispatch.dispatch_qty) && toNum(dispatch.dispatch_qty) > 0) nextStatus = 'FULLY_DELIVERED';
  await dispatch.update({ delivery_status: nextStatus }, { transaction });
};

exports.getDispatchOptions = asyncHandler(async (req, res) => {
  const companyId = req.query.companyId || req.user?.companyId;
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);
  if (!companyId || !Number.isFinite(plantId) || !Number.isFinite(fiscalYearId)) {
    return sendError(res, 'companyId, plantId and fiscalYearId are required', 400);
  }

  const schema = await getDispatchSchema();
  const where = {
    company_id: companyId,
    plant_id: plantId,
    fiscal_year_id: fiscalYearId,
    status: { [Op.ne]: 'CANCELLED' },
    invoice_id: { [Op.ne]: null }
  };

  const rows = await db.FGDispatch.findAll({
    where,
    attributes: ['id', 'dispatch_no', 'dispatch_date', 'invoice_id', 'customer_id', 'item_id', 'dispatch_qty', 'transporter_name', 'vehicle_no', 'driver_name'],
    include: [
      { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number', 'customer_po_no'], required: false },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
      { model: db.Material, as: 'item', attributes: ['id', 'material_number', 'description'], required: false }
    ],
    order: [['dispatch_date', 'DESC'], ['id', 'DESC']]
  });

  const payload = [];
  for (const row of rows) {
    const r = row.toJSON();
    // One active challan per dispatch.
    // eslint-disable-next-line no-await-in-loop
    const existing = await db.DeliveryChallan.findOne({
      where: {
        dispatch_id: r.id,
        status: { [Op.ne]: 'CANCELLED' }
      },
      attributes: ['id', 'delivery_status', 'status']
    });
    if (existing) continue;

    const already = await getDispatchDeliveredQty(r.id, r.item_id);
    const balance = Math.max(toNum(r.dispatch_qty) - already, 0);
    if (balance <= 0) continue;
    payload.push({
      id: r.id,
      dispatch_no: r.dispatch_no,
      dispatch_date: r.dispatch_date,
      invoice_id: r.invoice_id,
      invoice_no: r.salesInvoice?.invoice_number || '',
      customer_name: r.customer?.name || '',
      customer_code: r.customer?.bp_number || '',
      customer_po_no: r.salesInvoice?.customer_po_no || '',
      item_code: r.item?.material_number || '',
      item_name: r.item?.description || '',
      dispatch_qty: toNum(r.dispatch_qty),
      delivered_qty: already,
      balance_qty: balance,
      transport_name: r.transporter_name || '',
      vehicle_no: r.vehicle_no || '',
      driver_name: r.driver_name || '',
      dispatch_status: r.status || '',
      fg_delivery_status: hasCol(schema, 'delivery_status') ? (r.delivery_status || '') : ''
    });
  }

  sendResponse(res, 200, true, 'Delivery challan dispatch options retrieved successfully', payload);
});

exports.getPrefill = asyncHandler(async (req, res) => {
  const dispatchId = parseInt(req.params.dispatchId, 10);
  if (!Number.isFinite(dispatchId) || dispatchId <= 0) return sendError(res, 'Valid dispatchId is required', 400);

  const dispatch = await db.FGDispatch.findByPk(dispatchId, {
    include: [
      { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number', 'invoice_date', 'customer_po_no', 'customer_po_id', 'shipping_address', 'customer_id'], required: false },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
      { model: db.Material, as: 'item', attributes: ['id', 'material_number', 'description', 'hsn_code', 'base_uom_id'], required: false },
      { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false }
    ]
  });
  if (!dispatch) return sendError(res, 'Dispatch not found', 404);
  if (!dispatch.invoice_id) return sendError(res, 'Dispatch has no linked invoice', 400);

  const invoiceItem = await db.SalesInvoiceItem.findOne({
    where: { invoice_id: dispatch.invoice_id, material_id: dispatch.item_id },
    attributes: ['id', 'quantity', 'uom_id']
  });

  let orderQty = 0;
  if (dispatch.salesInvoice?.customer_po_id) {
    const poQty = await db.CustomerPOItem.sum('quantity', {
      where: {
        po_id: dispatch.salesInvoice.customer_po_id,
        material_id: dispatch.item_id
      }
    });
    orderQty = toNum(poQty);
  }

  const alreadyDelivered = await getDispatchDeliveredQty(dispatch.id, dispatch.item_id);
  const dispatchQty = toNum(dispatch.dispatch_qty);
  const balanceQty = Math.max(dispatchQty - alreadyDelivered, 0);
  const invoiceQty = toNum(invoiceItem?.quantity || dispatchQty);

  sendResponse(res, 200, true, 'Delivery challan prefill retrieved successfully', {
    dispatch_id: dispatch.id,
    dispatch_no: dispatch.dispatch_no,
    dispatch_date: dispatch.dispatch_date,
    invoice_id: dispatch.invoice_id,
    invoice_no: dispatch.salesInvoice?.invoice_number || '',
    customer_id: dispatch.customer_id || dispatch.salesInvoice?.customer_id || null,
    customer_name: dispatch.customer?.name || '',
    customer_po_no: dispatch.salesInvoice?.customer_po_no || '',
    delivery_address: dispatch.salesInvoice?.shipping_address || '',
    transport_name: dispatch.transporter_name || '',
    vehicle_no: dispatch.vehicle_no || '',
    driver_name: dispatch.driver_name || '',
    work_order_no: dispatch.workOrder?.work_order_number || '',
    payment_type: null,
    items: [
      {
        invoice_item_id: invoiceItem?.id || null,
        material_id: dispatch.item_id,
        item_code: dispatch.item?.material_number || '',
        item_name: dispatch.item?.description || '',
        hsn_code: dispatch.item?.hsn_code || '',
        uom_id: invoiceItem?.uom_id || dispatch.item?.base_uom_id || null,
        order_qty: orderQty,
        invoice_qty: invoiceQty,
        dispatch_qty: dispatchQty,
        previously_delivered_qty: alreadyDelivered,
        balance_qty: balanceQty,
        delivered_qty: balanceQty
      }
    ]
  });
});

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId } = 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 (status) {
    const normalized = String(status).toUpperCase();
    if (normalized === 'IN_TRANSIT') {
      where[Op.or] = [{ delivery_status: 'IN_TRANSIT' }, { delivery_status: null, status: { [Op.in]: ['DRAFT'] } }];
    } else if (normalized === 'DELIVERED') {
      where[Op.or] = [{ delivery_status: 'DELIVERED' }, { status: { [Op.in]: ['DELIVERED', 'CLOSED'] } }];
    } else {
      where.status = normalized;
    }
  }
  if (search) where.challan_no = { [Op.like]: `%${search}%` };

  const rows = await db.DeliveryChallan.findAndCountAll({
    where,
    distinct: true,
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
      { model: db.FGDispatch, as: 'dispatch', attributes: ['id', 'dispatch_no', 'dispatch_date', 'status', 'delivery_status'], required: false },
      { model: db.SalesInvoice, as: 'invoice', attributes: ['id', 'invoice_number'], required: false },
      { model: db.DeliveryChallanItem, as: 'items', attributes: ['id', 'dispatch_qty', 'delivered_qty'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const challanIds = (rows.rows || []).map((r) => r.id).filter((id) => Number.isFinite(id));
  const latestReceiptByChallan = new Map();
  if (challanIds.length > 0) {
    const receiptRows = await db.ReceiptEntry.findAll({
      where: { delivery_challan_id: { [Op.in]: challanIds } },
      attributes: ['id', 'delivery_challan_id', 'receipt_no', 'status'],
      order: [['id', 'DESC']],
      raw: true
    });
    receiptRows.forEach((rec) => {
      const key = parseInt(rec.delivery_challan_id, 10);
      if (!latestReceiptByChallan.has(key)) latestReceiptByChallan.set(key, rec);
    });
  }

  rows.rows = (rows.rows || []).map((row) => {
    const json = row.toJSON();
    const rec = latestReceiptByChallan.get(row.id);
    const itemTotals = (json.items || []).reduce((acc, it) => {
      acc.dispatch_qty += toNum(it.dispatch_qty);
      acc.delivered_qty += toNum(it.delivered_qty);
      return acc;
    }, { dispatch_qty: 0, delivered_qty: 0 });
    return {
      ...json,
      delivery_status: normalizeDeliveryStatus(json),
      total_dispatch_qty: itemTotals.dispatch_qty,
      total_delivered_qty: itemTotals.delivered_qty,
      receipt_no: rec?.receipt_no || '',
      receipt_status: rec?.status || ''
    };
  });

  const response = getPagingData(rows, page, limitNum);
  sendResponse(res, 200, true, 'Delivery challans retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const record = await db.DeliveryChallan.findByPk(req.params.id, {
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'] },
      { model: db.FGDispatch, as: 'dispatch', attributes: ['id', 'dispatch_no', 'dispatch_date', 'delivery_status', 'status'] },
      { model: db.SalesInvoice, as: 'invoice', attributes: ['id', 'invoice_number', 'invoice_date'] },
      {
        model: db.DeliveryChallanItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'], required: false },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false }
        ]
      }
    ]
  });
  if (!record) return sendError(res, 'Delivery challan not found', 404);
  const json = record.toJSON ? record.toJSON() : record;
  sendResponse(res, 200, true, 'Delivery challan retrieved successfully', {
    ...json,
    delivery_status: normalizeDeliveryStatus(json)
  });
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body || {};
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;
  const dispatchId = parseInt(data.dispatch_id, 10);
  if (!companyId) return sendError(res, 'Company ID is required', 400);
  if (!Number.isFinite(dispatchId) || dispatchId <= 0) return sendError(res, 'Dispatch No is required', 400);
  if (!data.plant_id || !data.fiscal_year_id) return sendError(res, 'plant_id and fiscal_year_id are required', 400);
  const paymentType = String(data.payment_type || 'CREDIT').toUpperCase();
  if (!['CREDIT', 'COD'].includes(paymentType)) return sendError(res, 'Invalid payment_type (CREDIT/COD)', 400);

  const t = await db.sequelize.transaction();
  try {
    const dispatchSchema = await getDispatchSchema();
    const dispatch = await db.FGDispatch.findByPk(dispatchId, {
      include: [
        { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number', 'customer_id', 'customer_po_no', 'shipping_address', 'customer_po_id'] },
        { model: db.Material, as: 'item', attributes: ['id', 'material_number', 'description', 'hsn_code', 'base_uom_id'] }
      ],
      transaction: t
    });
    if (!dispatch) throw new Error('Dispatch not found');
    if (!dispatch.invoice_id) throw new Error('Delivery Challan can be created only against dispatch linked to invoice');
    if (String(dispatch.status || '').toUpperCase() === 'CANCELLED') {
      throw new Error('Cannot create delivery challan for cancelled dispatch');
    }
    const existingForDispatch = await db.DeliveryChallan.findOne({
      where: {
        dispatch_id: dispatch.id,
        status: { [Op.ne]: 'CANCELLED' }
      },
      attributes: ['id'],
      transaction: t
    });
    if (existingForDispatch) {
      throw new Error('Delivery Challan already exists for this dispatch');
    }

    const invoiceItem = await db.SalesInvoiceItem.findOne({
      where: { invoice_id: dispatch.invoice_id, material_id: dispatch.item_id },
      attributes: ['id', 'quantity', 'uom_id'],
      transaction: t
    });

    const deliveredQty = await getDispatchDeliveredQty(dispatch.id, dispatch.item_id, null, t);
    const dispatchQty = toNum(dispatch.dispatch_qty);
    const remaining = Math.max(dispatchQty - deliveredQty, 0);
    if (remaining <= 0) throw new Error('Dispatch is already fully delivered');

    const reqItems = Array.isArray(data.items) && data.items.length
      ? data.items
      : [{ material_id: dispatch.item_id, delivered_qty: dispatchQty, remarks: null }];

    const dcItems = [];
    for (const it of reqItems) {
      const qty = toNum(it.delivered_qty);
      if (qty <= 0) throw new Error('Delivered quantity should be greater than 0');
      if (qty > dispatchQty) throw new Error('Delivered quantity cannot exceed dispatch quantity');
      dcItems.push({
        material_id: dispatch.item_id,
        item_code: dispatch.item?.material_number || it.item_code || '',
        item_name: dispatch.item?.description || it.item_name || '',
        hsn_code: dispatch.item?.hsn_code || it.hsn_code || '',
        uom_id: invoiceItem?.uom_id || dispatch.item?.base_uom_id || null,
        invoice_item_id: invoiceItem?.id || null,
        order_qty: toNum(it.order_qty || 0),
        invoice_qty: toNum(invoiceItem?.quantity || dispatchQty),
        dispatch_qty: dispatchQty,
        delivered_qty: qty,
        remarks: it.remarks || null
      });
    }

    const challan = await db.DeliveryChallan.create({
      company_id: companyId,
      plant_id: data.plant_id,
      fiscal_year_id: data.fiscal_year_id,
      challan_no: await generateDeliveryChallanNo(companyId, data.fiscal_year_id, t),
      dc_date: data.dc_date || new Date(),
      dispatch_id: dispatch.id,
      dispatch_date: dispatch.dispatch_date || null,
      invoice_id: dispatch.invoice_id,
      customer_id: dispatch.customer_id || dispatch.salesInvoice?.customer_id,
      customer_po_no: data.customer_po_no || dispatch.salesInvoice?.customer_po_no || null,
      delivery_address: data.delivery_address || dispatch.salesInvoice?.shipping_address || null,
      transport_name: data.transport_name || dispatch.transporter_name || null,
      vehicle_no: data.vehicle_no || dispatch.vehicle_no || null,
      driver_name: data.driver_name || dispatch.driver_name || null,
      driver_phone: data.driver_phone || null,
      eway_bill_no: data.eway_bill_no || null,
      payment_type: paymentType,
      delivery_status: 'IN_TRANSIT',
      status: 'DRAFT',
      remarks: data.remarks || null,
      created_by: userId,
      updated_by: userId
    }, { transaction: t });

    await Promise.all(dcItems.map((it, idx) => db.DeliveryChallanItem.create({
      ...it,
      item_number: idx + 1,
      delivery_challan_id: challan.id
    }, { transaction: t })));

    await t.commit();
    const created = await db.DeliveryChallan.findByPk(challan.id, {
      include: [{ model: db.DeliveryChallanItem, as: 'items' }]
    });
    sendResponse(res, 201, true, 'Delivery challan created successfully', created);
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to create delivery challan', 400);
  }
});

exports.update = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const userId = req.user?.userId || null;
  const data = req.body || {};
  const challan = await db.DeliveryChallan.findByPk(id);
  if (data.payment_type) {
    const paymentType = String(data.payment_type).toUpperCase();
    if (!['CREDIT', 'COD'].includes(paymentType)) return sendError(res, 'Invalid payment_type', 400);
  }

  if (!challan) return sendError(res, 'Delivery challan not found', 404);
  if (normalizeDeliveryStatus(challan) === 'DELIVERED') return sendError(res, 'Delivered challan cannot be edited', 400);
  if (String(challan.status || '').toUpperCase() === 'CANCELLED') return sendError(res, 'Cancelled challan cannot be edited', 400);

  const t = await db.sequelize.transaction();
  try {
    const dispatch = await db.FGDispatch.findByPk(challan.dispatch_id, { transaction: t });
    if (!dispatch) throw new Error('Dispatch not found');
    const dispatchQty = toNum(dispatch.dispatch_qty);
    const reqItems = Array.isArray(data.items) ? data.items : [];
    if (!reqItems.length) throw new Error('At least one item is required');

    let totalDelivered = 0;
    reqItems.forEach((it) => { totalDelivered += toNum(it.delivered_qty); });
    if (totalDelivered <= 0) throw new Error('Delivered quantity should be greater than 0');
    if (totalDelivered > dispatchQty) throw new Error('Delivered quantity cannot exceed dispatch quantity');

    await challan.update({
      dc_date: data.dc_date || challan.dc_date,
      transport_name: data.transport_name ?? challan.transport_name,
      vehicle_no: data.vehicle_no ?? challan.vehicle_no,
      driver_name: data.driver_name ?? challan.driver_name,
      driver_phone: data.driver_phone ?? challan.driver_phone,
      eway_bill_no: data.eway_bill_no ?? challan.eway_bill_no,
      payment_type: data.payment_type ? String(data.payment_type).toUpperCase() : challan.payment_type,
      remarks: data.remarks ?? challan.remarks,
      updated_by: userId
    }, { transaction: t });

    await db.DeliveryChallanItem.destroy({ where: { delivery_challan_id: challan.id }, transaction: t });
    await Promise.all(reqItems.map((it, idx) => db.DeliveryChallanItem.create({
      delivery_challan_id: challan.id,
      item_number: idx + 1,
      invoice_item_id: it.invoice_item_id || null,
      material_id: it.material_id || dispatch.item_id,
      item_code: it.item_code || null,
      item_name: it.item_name || null,
      hsn_code: it.hsn_code || null,
      uom_id: it.uom_id || null,
      order_qty: toNum(it.order_qty),
      invoice_qty: toNum(it.invoice_qty),
      dispatch_qty: dispatchQty,
      delivered_qty: toNum(it.delivered_qty),
      remarks: it.remarks || null
    }, { transaction: t })));

    await t.commit();
    const updated = await db.DeliveryChallan.findByPk(challan.id, { include: [{ model: db.DeliveryChallanItem, as: 'items' }] });
    sendResponse(res, 200, true, 'Delivery challan updated successfully', updated);
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to update delivery challan', 400);
  }
});

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

  const challan = await db.DeliveryChallan.findByPk(id, {
    include: [{ model: db.DeliveryChallanItem, as: 'items' }]
  });
  if (!challan) return sendError(res, 'Delivery challan not found', 404);
  if (normalizeDeliveryStatus(challan) === 'DELIVERED') return sendError(res, 'Delivery challan already marked as delivered', 400);

  const t = await db.sequelize.transaction();
  try {
    const deliveredBy = String(body.delivered_by || '').trim();
    const receivedBy = String(body.received_by || '').trim();
    const deliveryDate = String(body.delivery_date || '').trim();
    const deliveryTime = String(body.delivery_time || '').trim();
    const receiverSignature = String(body.receiver_signature || '').trim();
    if (!deliveredBy) throw new Error('Delivered By is required');
    if (!receivedBy) throw new Error('Received By is required');
    if (!deliveryDate) throw new Error('Delivery Date is required');
    if (!deliveryTime) throw new Error('Delivery Time is required');

    let items = [];
    if (Array.isArray(body.items)) items = body.items;
    else if (typeof body.items === 'string' && body.items.trim()) {
      try {
        const parsed = JSON.parse(body.items);
        if (Array.isArray(parsed)) items = parsed;
      } catch (_) {
        items = [];
      }
    }
    if (items.length > 0) {
      const challanItems = await db.DeliveryChallanItem.findAll({
        where: { delivery_challan_id: challan.id },
        transaction: t
      });
      const byId = new Map(challanItems.map((it) => [parseInt(it.id, 10), it]));
      for (const payloadItem of items) {
        const rowId = parseInt(payloadItem.id, 10);
        const row = byId.get(rowId);
        if (!row) continue;
        const nextDeliveredQty = toNum(payloadItem.delivered_qty);
        const maxDispatchQty = toNum(row.dispatch_qty);
        if (nextDeliveredQty < 0) throw new Error('Delivered quantity cannot be negative');
        if (nextDeliveredQty > maxDispatchQty) throw new Error('Delivered quantity cannot exceed dispatch quantity');
        // eslint-disable-next-line no-await-in-loop
        await row.update({
          delivered_qty: nextDeliveredQty,
          remarks: payloadItem.remarks ?? row.remarks
        }, { transaction: t });
      }
    }

    let deliveryProofPath = null;
    if (req.file?.path) {
      deliveryProofPath = req.file.path.replace(/\\/g, '/');
    }

    const deliveredAt = new Date(`${deliveryDate}T${deliveryTime}`);
    if (Number.isNaN(deliveredAt.getTime())) throw new Error('Invalid delivery date/time');

    await challan.update({
      delivered_by: userId,
      delivered_by_name: deliveredBy,
      received_by: receivedBy,
      delivery_date: deliveryDate,
      delivery_time: deliveryTime,
      receiver_signature: receiverSignature || null,
      delivery_proof_path: deliveryProofPath || challan.delivery_proof_path || null,
      delivered_at: deliveredAt,
      delivery_status: 'DELIVERED',
      status: 'DELIVERED',
      remarks: body.remarks ?? challan.remarks,
      updated_by: userId
    }, { transaction: t });

    await refreshDispatchDeliveryStatus(challan.dispatch_id, t);
    await t.commit();

    const updated = await db.DeliveryChallan.findByPk(challan.id, {
      include: [{ model: db.DeliveryChallanItem, as: 'items' }]
    });
    sendResponse(res, 200, true, 'Delivery challan marked as delivered successfully', updated);
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to mark delivery', 400);
  }
});

exports.updateStatus = asyncHandler(async (req, res) => {
  const status = String(req.body?.status || '').toUpperCase();
  if (!['IN_TRANSIT', 'DELIVERED', 'CANCELLED'].includes(status)) {
    return sendError(res, 'Invalid status. Allowed: IN_TRANSIT, DELIVERED, CANCELLED', 400);
  }

  if (status === 'DELIVERED') {
    return exports.markDelivered(req, res);
  }

  const id = parseInt(req.params.id, 10);
  const userId = req.user?.userId || null;
  const challan = await db.DeliveryChallan.findByPk(id);
  if (!challan) return sendError(res, 'Delivery challan not found', 404);

  const nextDeliveryStatus = status === 'IN_TRANSIT' ? 'IN_TRANSIT' : (challan.delivery_status || 'IN_TRANSIT');
  const nextLegacyStatus = status === 'CANCELLED' ? 'CANCELLED' : 'DRAFT';
  await challan.update({
    delivery_status: nextDeliveryStatus,
    status: nextLegacyStatus,
    updated_by: userId
  });
  const updated = await db.DeliveryChallan.findByPk(challan.id, {
    include: [{ model: db.DeliveryChallanItem, as: 'items' }]
  });
  sendResponse(res, 200, true, 'Delivery challan status updated successfully', updated);
});

