const { Op, fn, col } = require('sequelize');
const db = require('../models');
const {
  resolvePurchaseLineDescription,
  fetchMaterialsForLineDescription,
} = require('../utils/rawMaterialLineDescription');
const asyncHandler = require('../middlewares/asyncHandler');
const { sendResponse, sendError } = require('../utils/response');
const { getPagination, getPagingData } = require('../utils/pagination');
const stockLedgerService = require('../services/stockLedgerService');
const numberGenerationService = require('../services/numberGenerationService');
const PDFDocument = require('pdfkit');

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

const generatePurchaseReturnNumber = async (companyId, plantId, fiscalYearId) => {
  try {
    return await numberGenerationService.generateNumber({
      companyId,
      plantId,
      fiscalYearId,
      module: 'purchase_return',
      format: 'PRTN-{YYYY}-{####}'
    });
  } catch (err) {
    const year = new Date().getFullYear();
    const prefix = `PRTN${year}`;
    const last = await db.PurchaseReturn.findOne({
      where: { company_id: companyId, return_number: { [Op.like]: `${prefix}%` } },
      order: [['return_number', 'DESC']]
    });
    const seq = last ? ((parseInt(String(last.return_number).slice(-4), 10) || 0) + 1) : 1;
    return `${prefix}${String(seq).padStart(4, '0')}`;
  }
};

async function enrichPurchaseReturnItemsWithDescriptions(db, normalizedItems, { transaction } = {}) {
  const grnItemIds = [...new Set(normalizedItems.map((i) => Number(i.grn_item_id)).filter((n) => n > 0))];
  if (grnItemIds.length === 0) return normalizedItems;
  const grnRows = await db.GoodsReceiptNoteItem.findAll({
    where: { id: grnItemIds },
    attributes: ['id', 'description'],
    transaction,
  });
  const grnById = new Map(grnRows.map((r) => [r.id, r]));
  const materialIds = normalizedItems.map((i) => i.material_id);
  const matMap = await fetchMaterialsForLineDescription(db, materialIds, { transaction });
  return normalizedItems.map((it) => {
    const grnLine = grnById.get(Number(it.grn_item_id));
    const mat = matMap.get(Number(it.material_id));
    const description = resolvePurchaseLineDescription(
      { description: String(grnLine?.description || '').trim() },
      mat
    );
    return { ...it, description };
  });
}

const getReturnedMap = async (grnId, transaction = null, excludePurchaseReturnId = null) => {
  const where = {
    grn_id: grnId,
    // Reserve quantity from both draft and approved returns.
    // This prevents over-returning the same GRN in parallel drafts.
    status: { [Op.in]: ['DRAFT', 'APPROVED'] }
  };
  if (excludePurchaseReturnId) where.id = { [Op.ne]: excludePurchaseReturnId };

  const returns = await db.PurchaseReturn.findAll({
    where,
    attributes: ['id'],
    raw: true,
    transaction: transaction || undefined
  });
  const ids = returns.map((r) => r.id);
  if (ids.length === 0) return new Map();

  const rows = await db.PurchaseReturnItem.findAll({
    where: { purchase_return_id: { [Op.in]: ids } },
    attributes: ['grn_item_id', [fn('SUM', col('return_quantity')), 'returned_qty']],
    group: ['grn_item_id'],
    raw: true,
    transaction: transaction || undefined
  });

  const map = new Map();
  rows.forEach((r) => map.set(Number(r.grn_item_id), toNum(r.returned_qty)));
  return map;
};

const buildPrefill = async ({ grnId, companyId, plantId, fiscalYearId, excludePurchaseReturnId = null, transaction = null }) => {
  const grn = await db.GoodsReceiptNote.findOne({
    where: {
      id: grnId,
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      status: 'POSTED'
    },
    include: [
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'name', 'legal_name'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] },
      { model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code', 'description'] },
      {
        model: db.GoodsReceiptNoteItem,
        as: 'items',
        attributes: [
          'id',
          'line_number',
          'material_id',
          'uom_id',
          'quantity',
          'unit_price',
          'tax_rate',
          'tax_amount',
          'line_total',
          'storage_location_id',
          'description'
        ],
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_name', 'uom_code'] },
          { model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code', 'description'] }
        ]
      }
    ],
    order: [[{ model: db.GoodsReceiptNoteItem, as: 'items' }, 'line_number', 'ASC']],
    transaction: transaction || undefined
  });

  if (!grn) {
    throw new Error('Posted GRN not found for selected company/plant/fiscal year');
  }

  const returnedMap = await getReturnedMap(grn.id, transaction, excludePurchaseReturnId);

  const materialIdsForSpec = (grn.items || []).map((it) => it.material_id).filter((id) => id != null);
  const matMapForPrefill = await fetchMaterialsForLineDescription(db, materialIdsForSpec, { transaction });

  const items = (grn.items || []).map((it) => {
    const receivedQty = toNum(it.quantity);
    const alreadyReturned = returnedMap.get(Number(it.id)) || 0;
    const balanceQty = Math.max(0, receivedQty - alreadyReturned);
    let unitPrice = toNum(it.unit_price);
    if (!(unitPrice > 0)) {
      const lineTotal = toNum(it.line_total);
      unitPrice = receivedQty > 0 ? (lineTotal / receivedQty) : 0;
    }
    let taxRate = toNum(it.tax_rate);
    if (!(taxRate > 0)) {
      const baseAmount = receivedQty * unitPrice;
      const taxAmount = toNum(it.tax_amount);
      if (baseAmount > 0 && taxAmount > 0) {
        taxRate = (taxAmount / baseAmount) * 100;
      }
    }
    const matRow = matMapForPrefill.get(Number(it.material_id)) || it.material;
    const item_name = resolvePurchaseLineDescription(
      { description: String(it.description || '').trim() },
      matRow
    );
    return {
      grn_item_id: it.id,
      line_number: it.line_number,
      material_id: it.material_id,
      uom_id: it.uom_id,
      item_code: it.material?.material_number || '',
      item_name,
      received_quantity: receivedQty,
      already_returned_quantity: alreadyReturned,
      balance_return_quantity: balanceQty,
      unit_price: unitPrice,
      tax_rate: taxRate,
      grn_tax_amount: toNum(it.tax_amount),
      grn_line_total: toNum(it.line_total),
      warehouse_id: it.storage_location_id || grn.storage_location_id || null
    };
  });

  const fallbackWarehouseId = items.find((it) => Number(it.warehouse_id) > 0)?.warehouse_id || null;
  const resolvedWarehouseId = grn.storage_location_id || fallbackWarehouseId || null;

  return {
    grn_id: grn.id,
    grn_number: grn.grn_number,
    vendor_id: grn.vendor_id,
    vendor_name: grn.vendor?.name || grn.vendor?.legal_name || '',
    po_id: grn.po_id || null,
    po_number: grn.purchaseOrder?.po_number || '',
    warehouse_id: resolvedWarehouseId,
    warehouse_name: grn.storageLocation
      ? `${grn.storageLocation.storage_location_code || ''}${grn.storageLocation.description ? ` - ${grn.storageLocation.description}` : ''}`
      : '',
    items
  };
};

exports.getGRNOptions = asyncHandler(async (req, res) => {
  const { companyId, plantId, fiscalYearId, search = '' } = req.query;
  const where = { status: 'POSTED' };
  if (companyId) where.company_id = companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (search) {
    where[Op.or] = [
      { grn_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const rows = await db.GoodsReceiptNote.findAll({
    where,
    attributes: ['id', 'grn_number', 'grn_date', 'vendor_id', 'po_id', 'storage_location_id'],
    include: [
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'name', 'legal_name'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] }
    ],
    order: [['grn_date', 'DESC'], ['id', 'DESC']],
    limit: 200
  });

  sendResponse(res, 200, true, 'GRN options fetched successfully', rows);
});

exports.getGRNPrefill = asyncHandler(async (req, res) => {
  const grnId = parseInt(req.params.grnId, 10);
  const companyId = parseInt(req.query.companyId || req.user?.companyId, 10);
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);
  const excludePurchaseReturnId = parseInt(req.query.excludePurchaseReturnId, 10);

  if (!(grnId > 0) || !(companyId > 0) || !(plantId > 0) || !(fiscalYearId > 0)) {
    return sendError(res, 'grnId, companyId, plantId and fiscalYearId are required', 400);
  }

  const data = await buildPrefill({
    grnId,
    companyId,
    plantId,
    fiscalYearId,
    excludePurchaseReturnId: Number.isFinite(excludePurchaseReturnId) && excludePurchaseReturnId > 0
      ? excludePurchaseReturnId
      : null
  });
  sendResponse(res, 200, true, 'Purchase return prefill loaded successfully', data);
});

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

  const where = {};
  where.company_id = companyId || req.user?.companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (status) where.status = status;
  if (search) {
    where[Op.or] = [
      { return_number: { [Op.like]: `%${search}%` } },
      { '$grn.grn_number$': { [Op.like]: `%${search}%` } }
    ];
  }

  const result = await db.PurchaseReturn.findAndCountAll({
    where,
    include: [
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'name', 'legal_name'], required: false },
      { model: db.GoodsReceiptNote, as: 'grn', attributes: ['id', 'grn_number', 'grn_date'], required: false },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'], required: false },
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'], required: false },
      { model: db.PurchaseReturnItem, as: 'items', attributes: ['id', 'return_quantity'], required: false }
    ],
    order: [['return_date', 'DESC'], ['id', 'DESC']],
    distinct: true,
    limit: pageSize,
    offset
  });

  const payload = getPagingData(result, page, pageSize);
  sendResponse(res, 200, true, 'Purchase returns fetched successfully', payload);
});

exports.getById = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const pr = await db.PurchaseReturn.findByPk(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.BusinessPartner, as: 'vendor', attributes: ['id', 'name', 'legal_name'] },
      { model: db.GoodsReceiptNote, as: 'grn', attributes: ['id', 'grn_number', 'grn_date'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] },
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'] },
      {
        model: db.PurchaseReturnItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            include: [
              {
                model: db.MaterialThickness,
                as: 'materialThickness',
                attributes: ['id', 'thickness_value', 'thickness_unit'],
                required: false,
              },
              {
                model: db.MaterialGrade,
                as: 'materialGrade',
                attributes: ['id', 'grade_code', 'grade_name'],
                required: false,
              },
              {
                model: db.MaterialType,
                as: 'materialType',
                attributes: ['id', 'type_code', 'type_name'],
                required: false,
              },
            ],
          },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_name', 'uom_code'] },
          {
            model: db.GoodsReceiptNoteItem,
            as: 'grnItem',
            attributes: ['id', 'line_number', 'quantity', 'description'],
          },
        ],
      }
    ],
    order: [[{ model: db.PurchaseReturnItem, as: 'items' }, 'line_number', 'ASC']]
  });
  if (!pr) return sendError(res, 'Purchase return not found', 404);
  sendResponse(res, 200, true, 'Purchase return fetched successfully', pr);
});

exports.generatePDF = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const pr = await db.PurchaseReturn.findByPk(id, {
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name', 'legal_name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.FiscalYear, as: 'fiscalYear', attributes: ['id', 'fiscal_year'] },
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name', 'vendor_name', 'legal_name', 'phone_numbers'] },
      { model: db.GoodsReceiptNote, as: 'grn', attributes: ['id', 'grn_number', 'grn_date'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] },
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'] },
      {
        model: db.PurchaseReturnItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] },
          {
            model: db.GoodsReceiptNoteItem,
            as: 'grnItem',
            attributes: ['id', 'line_number', 'quantity', 'unit_price', 'line_total', 'description'],
          },
        ],
      }
    ],
    order: [[{ model: db.PurchaseReturnItem, as: 'items' }, 'line_number', 'ASC']]
  });

  if (!pr) return sendError(res, 'Purchase return not found', 404);

  const fmtNum = (v, d = 2) => {
    const n = parseFloat(v);
    return Number.isFinite(n) ? n.toFixed(d) : (0).toFixed(d);
  };
  const fmtDate = (v) => {
    if (!v) return '';
    const dt = new Date(v);
    if (Number.isNaN(dt.getTime())) return '';
    return dt.toLocaleDateString('en-GB');
  };
  const safeName = (value) => String(value || '')
    .trim()
    .replace(/[<>:"/\\|?*\x00-\x1F]/g, '')
    .replace(/\s+/g, '_')
    .replace(/_+/g, '_')
    .replace(/^_+|_+$/g, '');

  const items = Array.isArray(pr.items) ? pr.items : [];
  const currencyCode = 'INR';
  const itemRows = items.map((item, idx) => {
    const qty = toNum(item.return_quantity);
    let unitPrice = toNum(item.grnItem?.unit_price);
    if (!(unitPrice > 0)) {
      const grnQty = toNum(item.grnItem?.quantity);
      const grnLine = toNum(item.grnItem?.line_total);
      unitPrice = grnQty > 0 ? grnLine / grnQty : 0;
    }
    const amount = qty * unitPrice;
    const lineDesc = String(item.description || '').trim();
    const grnLineDesc = String(item.grnItem?.description || '').trim();
    const matShort = String(item.material?.description || '').trim();
    return {
      no: idx + 1,
      code: item.material?.material_number || '',
      name: lineDesc || grnLineDesc || matShort || '',
      uom: item.uom?.uom_code || '',
      qty,
      unitPrice,
      amount
    };
  });

  const totalAmount = itemRows.reduce((sum, r) => sum + toNum(r.amount), 0);
  const totalQty = itemRows.reduce((sum, r) => sum + toNum(r.qty), 0);

  const vendorName = pr.vendor?.vendor_name || pr.vendor?.name || pr.vendor?.legal_name || 'Vendor';
  const fileName = `Purchase_Return_${safeName(pr.return_number || pr.id)}_${safeName(vendorName)}.pdf`;

  res.setHeader('Content-Type', 'application/pdf');
  res.setHeader('Content-Disposition', `attachment; filename="${fileName}"`);

  const doc = new PDFDocument({ margin: 36, size: 'A4' });
  doc.pipe(res);

  const pageWidth = doc.page.width;
  const contentWidth = pageWidth - 72;
  const leftX = 36;
  let y = 36;

  doc.font('Helvetica-Bold').fontSize(15).text(pr.company?.legal_name || pr.company?.name || 'Company', leftX, y, {
    width: contentWidth,
    align: 'left'
  });
  y += 20;
  doc.font('Helvetica-Bold').fontSize(12).text('PURCHASE RETURN', leftX, y, { width: contentWidth, align: 'center' });
  y += 24;

  doc.fontSize(9).font('Helvetica');
  doc.text(`PR Number: ${pr.return_number || '-'}`, leftX, y);
  doc.text(`PR Date: ${fmtDate(pr.return_date)}`, leftX + 280, y);
  y += 14;
  doc.text(`Vendor: ${vendorName}`, leftX, y);
  doc.text(`Vendor Code: ${pr.vendor?.bp_number || '-'}`, leftX + 280, y);
  y += 14;
  doc.text(`GRN Number: ${pr.grn?.grn_number || '-'}`, leftX, y);
  doc.text(`PO Number: ${pr.purchaseOrder?.po_number || '-'}`, leftX + 280, y);
  y += 14;
  doc.text(`Warehouse: ${pr.warehouse?.storage_location_code || '-'}`, leftX, y);
  doc.text(`Status: ${pr.status || '-'}`, leftX + 280, y);
  y += 10;
  doc.moveTo(leftX, y).lineTo(leftX + contentWidth, y).stroke('#666');
  y += 10;

  const cols = [
    { key: 'no', label: 'S.No', width: 35, align: 'center' },
    { key: 'code', label: 'Item Code', width: 90, align: 'left' },
    { key: 'name', label: 'Item Name', width: 170, align: 'left' },
    { key: 'uom', label: 'UOM', width: 45, align: 'center' },
    { key: 'qty', label: 'Qty', width: 55, align: 'right' },
    { key: 'unitPrice', label: `Rate (${currencyCode})`, width: 80, align: 'right' },
    { key: 'amount', label: `Amount (${currencyCode})`, width: 90, align: 'right' }
  ];
  const rowHeight = 18;

  const drawHeader = () => {
    let x = leftX;
    doc.rect(leftX, y, contentWidth, rowHeight).fillAndStroke('#f2f2f2', '#333');
    doc.fillColor('#000').font('Helvetica-Bold').fontSize(8);
    cols.forEach((c) => {
      doc.text(c.label, x + 3, y + 5, { width: c.width - 6, align: c.align });
      x += c.width;
    });
    y += rowHeight;
  };

  const drawRow = (r) => {
    let x = leftX;
    doc.rect(leftX, y, contentWidth, rowHeight).stroke('#ccc');
    doc.fillColor('#000').font('Helvetica').fontSize(8);
    cols.forEach((c) => {
      let val = r[c.key];
      if (c.key === 'qty') val = fmtNum(val, 3);
      if (c.key === 'unitPrice' || c.key === 'amount') val = fmtNum(val, 2);
      doc.text(String(val ?? ''), x + 3, y + 5, { width: c.width - 6, align: c.align, ellipsis: true });
      x += c.width;
    });
    y += rowHeight;
  };

  drawHeader();
  itemRows.forEach((row) => {
    if (y > doc.page.height - 120) {
      doc.addPage();
      y = 36;
      drawHeader();
    }
    drawRow(row);
  });

  y += 8;
  doc.moveTo(leftX, y).lineTo(leftX + contentWidth, y).stroke('#666');
  y += 8;
  doc.font('Helvetica-Bold').fontSize(10);
  doc.text(`Total Return Qty: ${fmtNum(totalQty, 3)}`, leftX, y, { width: 280, align: 'left' });
  doc.text(`Total Amount: ${fmtNum(totalAmount, 2)} ${currencyCode}`, leftX + 280, y, { width: contentWidth - 280, align: 'right' });
  y += 18;

  doc.font('Helvetica').fontSize(9);
  if (pr.return_reason) doc.text(`Return Reason: ${pr.return_reason}`, leftX, y, { width: contentWidth });
  y += 14;
  if (pr.remarks) doc.text(`Remarks: ${pr.remarks}`, leftX, y, { width: contentWidth });

  doc.end();
});

exports.create = asyncHandler(async (req, res) => {
  const userId = req.user?.userId || req.user?.id || null;
  const data = req.body || {};
  const items = Array.isArray(data.items) ? data.items : [];

  const companyId = parseInt(data.company_id || req.user?.companyId, 10);
  const plantId = parseInt(data.plant_id, 10);
  const fiscalYearId = parseInt(data.fiscal_year_id, 10);
  const grnId = parseInt(data.grn_id, 10);
  const returnDate = data.return_date;

  if (!(companyId > 0) || !(plantId > 0) || !(fiscalYearId > 0) || !(grnId > 0) || !returnDate) {
    return sendError(res, 'company_id, plant_id, fiscal_year_id, grn_id and return_date are required', 400);
  }
  if (!items.length) return sendError(res, 'At least one return item is required', 400);

  const transaction = await db.sequelize.transaction();
  try {
    const prefill = await buildPrefill({ grnId, companyId, plantId, fiscalYearId, transaction });
    const prefillMap = new Map(prefill.items.map((it) => [Number(it.grn_item_id), it]));

    const normalizedItems = [];
    for (let i = 0; i < items.length; i += 1) {
      const row = items[i] || {};
      const grnItemId = parseInt(row.grn_item_id, 10);
      const returnQty = toNum(row.return_quantity);
      if (!(grnItemId > 0) || returnQty <= 0) continue;

      const pre = prefillMap.get(grnItemId);
      if (!pre) {
        await transaction.rollback();
        return sendError(res, `Invalid GRN item selected at row ${i + 1}`, 400);
      }
      if (returnQty > toNum(pre.balance_return_quantity)) {
        await transaction.rollback();
        return sendError(
          res,
          `Return quantity cannot exceed balance for item ${pre.item_code || pre.item_name}. Allowed: ${toNum(pre.balance_return_quantity).toFixed(3)}`,
          400
        );
      }

      normalizedItems.push({
        line_number: normalizedItems.length + 1,
        grn_item_id: grnItemId,
        material_id: pre.material_id,
        uom_id: pre.uom_id || null,
        received_quantity: toNum(pre.received_quantity),
        already_returned_quantity: toNum(pre.already_returned_quantity),
        return_quantity: returnQty,
        return_reason: row.return_reason || null,
        remarks: row.remarks || null
      });
    }

    if (!normalizedItems.length) {
      await transaction.rollback();
      return sendError(res, 'Please enter at least one valid return quantity', 400);
    }

    const enrichedItems = await enrichPurchaseReturnItemsWithDescriptions(db, normalizedItems, { transaction });

    const returnNumber = await generatePurchaseReturnNumber(companyId, plantId, fiscalYearId);
    const created = await db.PurchaseReturn.create({
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      return_number: returnNumber,
      return_date: returnDate,
      vendor_id: prefill.vendor_id,
      grn_id: grnId,
      po_id: prefill.po_id || null,
      warehouse_id: data.warehouse_id || prefill.warehouse_id || null,
      return_reason: data.return_reason || null,
      remarks: data.remarks || null,
      status: 'DRAFT',
      created_by: userId,
      updated_by: userId
    }, { transaction });

    await db.PurchaseReturnItem.bulkCreate(
      enrichedItems.map((it) => ({ ...it, purchase_return_id: created.id })),
      { transaction }
    );

    await transaction.commit();
    const fresh = await db.PurchaseReturn.findByPk(created.id, { include: [{ model: db.PurchaseReturnItem, as: 'items' }] });
    sendResponse(res, 201, true, 'Purchase return created successfully', fresh);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.update = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const userId = req.user?.userId || req.user?.id || null;
  const data = req.body || {};
  const items = Array.isArray(data.items) ? data.items : [];

  const existing = await db.PurchaseReturn.findByPk(id);
  if (!existing) return sendError(res, 'Purchase return not found', 404);
  if (existing.status !== 'DRAFT') return sendError(res, 'Only DRAFT purchase return can be updated', 400);
  if (!items.length) return sendError(res, 'At least one return item is required', 400);

  const companyId = parseInt(data.company_id || existing.company_id, 10);
  const plantId = parseInt(data.plant_id || existing.plant_id, 10);
  const fiscalYearId = parseInt(data.fiscal_year_id || existing.fiscal_year_id, 10);
  const grnId = parseInt(data.grn_id || existing.grn_id, 10);

  const transaction = await db.sequelize.transaction();
  try {
    const prefill = await buildPrefill({
      grnId,
      companyId,
      plantId,
      fiscalYearId,
      excludePurchaseReturnId: id,
      transaction
    });
    const prefillMap = new Map(prefill.items.map((it) => [Number(it.grn_item_id), it]));

    const normalizedItems = [];
    for (let i = 0; i < items.length; i += 1) {
      const row = items[i] || {};
      const grnItemId = parseInt(row.grn_item_id, 10);
      const returnQty = toNum(row.return_quantity);
      if (!(grnItemId > 0) || returnQty <= 0) continue;

      const pre = prefillMap.get(grnItemId);
      if (!pre) {
        await transaction.rollback();
        return sendError(res, `Invalid GRN item selected at row ${i + 1}`, 400);
      }
      if (returnQty > toNum(pre.balance_return_quantity)) {
        await transaction.rollback();
        return sendError(
          res,
          `Return quantity cannot exceed balance for item ${pre.item_code || pre.item_name}. Allowed: ${toNum(pre.balance_return_quantity).toFixed(3)}`,
          400
        );
      }

      normalizedItems.push({
        line_number: normalizedItems.length + 1,
        grn_item_id: grnItemId,
        material_id: pre.material_id,
        uom_id: pre.uom_id || null,
        received_quantity: toNum(pre.received_quantity),
        already_returned_quantity: toNum(pre.already_returned_quantity),
        return_quantity: returnQty,
        return_reason: row.return_reason || null,
        remarks: row.remarks || null
      });
    }

    if (!normalizedItems.length) {
      await transaction.rollback();
      return sendError(res, 'Please enter at least one valid return quantity', 400);
    }

    const enrichedItems = await enrichPurchaseReturnItemsWithDescriptions(db, normalizedItems, { transaction });

    await existing.update({
      return_date: data.return_date || existing.return_date,
      vendor_id: prefill.vendor_id,
      grn_id: grnId,
      po_id: prefill.po_id || null,
      warehouse_id: data.warehouse_id || prefill.warehouse_id || null,
      return_reason: data.return_reason || null,
      remarks: data.remarks || null,
      updated_by: userId
    }, { transaction });

    await db.PurchaseReturnItem.destroy({ where: { purchase_return_id: id }, transaction });
    await db.PurchaseReturnItem.bulkCreate(
      enrichedItems.map((it) => ({ ...it, purchase_return_id: id })),
      { transaction }
    );

    await transaction.commit();
    const fresh = await db.PurchaseReturn.findByPk(id, { include: [{ model: db.PurchaseReturnItem, as: 'items' }] });
    sendResponse(res, 200, true, 'Purchase return updated successfully', fresh);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const purchaseReturn = await db.PurchaseReturn.findByPk(id, {
    include: [
      {
        model: db.PurchaseReturnItem,
        as: 'items',
        include: [
          {
            model: db.GoodsReceiptNoteItem,
            as: 'grnItem',
            attributes: ['id', 'storage_location_id'],
            include: [{ model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code'] }]
          }
        ]
      },
      {
        model: db.GoodsReceiptNote,
        as: 'grn',
        attributes: ['id', 'grn_number', 'storage_location_id'],
        include: [{ model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code'] }]
      }
    ]
  });
  if (!purchaseReturn) return sendError(res, 'Purchase return not found', 404);
  if (purchaseReturn.status !== 'DRAFT') return sendError(res, 'Only DRAFT purchase return can be approved', 400);

  const transaction = await db.sequelize.transaction();
  try {
    let warehouseCode = null;
    if (purchaseReturn.warehouse_id) {
      const wh = await db.StorageLocation.findByPk(purchaseReturn.warehouse_id, {
        attributes: ['storage_location_code'],
        transaction
      });
      warehouseCode = wh?.storage_location_code || null;
    }

    const prefill = await buildPrefill({
      grnId: purchaseReturn.grn_id,
      companyId: purchaseReturn.company_id,
      plantId: purchaseReturn.plant_id,
      fiscalYearId: purchaseReturn.fiscal_year_id,
      excludePurchaseReturnId: id,
      transaction
    });
    const prefillMap = new Map(prefill.items.map((it) => [Number(it.grn_item_id), it]));

    for (const row of (purchaseReturn.items || [])) {
      const pre = prefillMap.get(Number(row.grn_item_id));
      const qty = toNum(row.return_quantity);
      if (!pre || qty <= 0) continue;
      if (qty > toNum(pre.balance_return_quantity)) {
        await transaction.rollback();
        return sendError(
          res,
          `Return quantity exceeds current balance for item ${pre.item_code || pre.item_name}. Allowed now: ${toNum(pre.balance_return_quantity).toFixed(3)}`,
          400
        );
      }

      const storageCode = warehouseCode
        || row.grnItem?.storageLocation?.storage_location_code
        || purchaseReturn.grn?.storageLocation?.storage_location_code
        || null;

      if (!storageCode) {
        await transaction.rollback();
        return sendError(res, `Storage location is required for item ${pre.item_code || pre.item_name}`, 400);
      }

      await stockLedgerService.createLedgerEntry({
        company_id: purchaseReturn.company_id,
        plant_id: purchaseReturn.plant_id,
        material_id: row.material_id,
        transaction_date: purchaseReturn.return_date,
        transaction_type: 'ISSUE',
        reference_type: 'OTHER',
        reference_id: purchaseReturn.id,
        reference_number: purchaseReturn.return_number,
        storage_location: storageCode,
        quantity_in: 0,
        quantity_out: qty,
        remarks: `Purchase Return ${purchaseReturn.return_number} against GRN ${purchaseReturn.grn?.grn_number || ''}`,
        created_by: userId
      }, transaction);
    }

    await purchaseReturn.update({
      status: 'APPROVED',
      approved_at: new Date(),
      approved_by: userId,
      updated_by: userId
    }, { transaction });

    await transaction.commit();

    const updated = await db.PurchaseReturn.findByPk(id, { include: [{ model: db.PurchaseReturnItem, as: 'items' }] });
    sendResponse(res, 200, true, 'Purchase return approved successfully', updated);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const transaction = await db.sequelize.transaction();
  try {
    const row = await db.PurchaseReturn.findByPk(id, { transaction });
    if (!row) {
      await transaction.rollback();
      return sendError(res, 'Purchase return not found', 404);
    }
    if (row.status !== 'DRAFT') {
      await transaction.rollback();
      return sendError(res, 'Only DRAFT purchase return can be deleted', 400);
    }
    await db.PurchaseReturnItem.destroy({ where: { purchase_return_id: id }, transaction });
    await row.destroy({ transaction });
    await transaction.commit();
    sendResponse(res, 200, true, 'Purchase return deleted successfully');
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

