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 numberFormatService = require('../services/numberFormatService');
const {
  resolvePurchaseLineDescription,
  fetchMaterialsForLineDescription,
} = require('../utils/rawMaterialLineDescription');
const { computeLineWeightKg } = require('../utils/lineWeightKg');

// Compute balance_quantity and receiving_status for each gate entry item
const enrichItemsWithBalance = (items) => {
  if (!items || !Array.isArray(items)) return items;
  return items.map(item => {
    const plain = item.toJSON ? item.toJSON() : { ...item };
    const poQty = plain.po_quantity !== null && plain.po_quantity !== undefined ? parseFloat(plain.po_quantity) : null;
    const recQty = parseFloat(plain.received_quantity) || 0;

    if (poQty !== null && poQty > 0) {
      plain.balance_quantity = parseFloat((poQty - recQty).toFixed(3));
      if (recQty <= 0) {
        plain.receiving_status = 'Not Received';
      } else if (recQty < poQty) {
        plain.receiving_status = 'Partial Received';
      } else {
        plain.receiving_status = 'Fully Received';
      }
    } else {
      plain.balance_quantity = null;
      plain.receiving_status = null;
    }
    return plain;
  });
};

// Compute balance_quantity and receiving_status for a single item (for DB persistence)
const computeItemBalanceAndStatus = (poQtyRaw, receivedQtyRaw) => {
  const poQty = poQtyRaw !== null && poQtyRaw !== undefined ? parseFloat(poQtyRaw) : null;
  const recQty = parseFloat(receivedQtyRaw) || 0;
  if (poQty !== null && poQty > 0) {
    const balance = parseFloat((poQty - recQty).toFixed(3));
    let status;
    if (recQty <= 0) status = 'NOT_RECEIVED';
    else if (recQty < poQty) status = 'PARTIAL_RECEIVED';
    else status = 'FULLY_RECEIVED';
    return { balance_quantity: balance, receiving_status: status };
  }
  return { balance_quantity: null, receiving_status: null };
};

// Compute overall receiving_status from item statuses
const computeOverallReceivingStatus = (itemStatuses) => {
  const valid = itemStatuses.filter(s => s !== null);
  if (valid.length === 0) return null;
  if (valid.every(s => s === 'FULLY_RECEIVED')) return 'FULLY_RECEIVED';
  if (valid.every(s => s === 'NOT_RECEIVED')) return 'NOT_RECEIVED';
  return 'PARTIAL_RECEIVED';
};

const generateGateEntryNumber = async (companyId, fiscalYearId) => {
  try {
    const generatedNumber = await numberFormatService.generateNextNumber(companyId, 'GATE_ENTRY', fiscalYearId, null);
    if (generatedNumber) return generatedNumber;
  } catch (error) {
    console.warn('Number format not found, using fallback method:', error.message);
  }

  // Fallback
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `GE${year}`;

  const lastGE = await db.GateEntry.findOne({
    where: {
      company_id: companyId,
      gate_entry_number: { [Op.like]: `${prefix}%` }
    },
    order: [['gate_entry_number', 'DESC']]
  });

  let sequence = 1;
  if (lastGE) {
    const lastSequence = parseInt(lastGE.gate_entry_number.slice(-4)) || 0;
    sequence = lastSequence + 1;
  }

  return `${prefix}${sequence.toString().padStart(4, '0')}`;
};

const isQARole = (role) => {
  const roleName = String(role?.role_name || '').toUpperCase();
  const roleCode = String(role?.role_code || '').toUpperCase();
  return roleName.includes('QA') || roleCode.includes('QA');
};

const validateReceivedByUser = async (receivedByUserId, companyId) => {
  if (!receivedByUserId) {
    return { valid: false, message: 'Received Person is required' };
  }

  const user = await db.User.findOne({
    where: {
      id: receivedByUserId,
      company_id: companyId,
      is_active: true
    },
    include: [
      {
        model: db.Role,
        as: 'roles',
        through: { attributes: [] },
        attributes: ['id', 'role_code', 'role_name'],
        required: false
      }
    ]
  });

  if (!user) {
    return { valid: false, message: 'Selected Received Person is invalid or inactive' };
  }

  if (!Array.isArray(user.roles) || !user.roles.some(isQARole)) {
    return { valid: false, message: 'Received Person must have QA role' };
  }

  return { valid: true };
};

// Get PO items with actual already-received quantities from existing gate entries
exports.getPOPendingItems = asyncHandler(async (req, res) => {
  const { poId } = req.params;
  const excludeGateEntryId = req.query.excludeGateEntryId || null;

  // Get the PO with items
  const purchaseOrder = await db.PurchaseOrder.findByPk(poId, {
    include: [{
      model: db.PurchaseOrderItem,
      as: 'items',
      include: [
        { model: db.Material, as: 'material' },
        { model: db.UOM, as: 'uom' },
        {
          model: db.StorageLocation,
          as: 'storageLocation',
          attributes: ['id', 'storage_location_code', 'description']
        }
      ]
    }]
  });

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

  // Get all gate entry items linked to this PO's items (excluding cancelled gate entries and optionally the current one)
  const gateEntryWhere = { po_id: poId, status: { [Op.notIn]: ['CANCELLED'] } };
  if (excludeGateEntryId) {
    gateEntryWhere.id = { [Op.ne]: excludeGateEntryId };
  }

  const existingGateEntries = await db.GateEntry.findAll({
    where: gateEntryWhere,
    include: [{
      model: db.GateEntryItem,
      as: 'items',
      attributes: ['po_item_id', 'received_quantity']
    }],
    attributes: ['id']
  });

  // Sum received quantities per po_item_id across all existing gate entries
  const receivedMap = {};
  existingGateEntries.forEach(ge => {
    (ge.items || []).forEach(item => {
      if (item.po_item_id) {
        receivedMap[item.po_item_id] = (receivedMap[item.po_item_id] || 0) + (parseFloat(item.received_quantity) || 0);
      }
    });
  });

  // Enrich PO items with already_received and pending quantities
  const enrichedItems = (purchaseOrder.items || []).map(poItem => {
    const plain = poItem.toJSON ? poItem.toJSON() : { ...poItem };
    const orderedQty = parseFloat(plain.quantity) || 0;
    const alreadyReceived = parseFloat((receivedMap[plain.id] || 0).toFixed(3));
    const pendingQty = parseFloat((orderedQty - alreadyReceived).toFixed(3));

    plain.already_received_quantity = alreadyReceived;
    plain.pending_quantity = pendingQty > 0 ? pendingQty : 0;
    plain.is_fully_received = pendingQty <= 0;
    return plain;
  });

  sendResponse(res, 200, true, 'PO pending items retrieved', { items: enrichedItems });
});

// List Purchase Orders that still have pending quantities (not fully received)
exports.getPendingPurchaseOrders = asyncHandler(async (req, res) => {
  const { companyId, plantId, fiscalYearId, status = 'POSTED', limit = 1000 } = req.query;

  const where = {};
  if (companyId) where.company_id = companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (status) where.status = status;

  const purchaseOrders = await db.PurchaseOrder.findAll({
    where,
    attributes: ['id', 'po_number', 'po_date', 'vendor_id', 'status'],
    include: [{
      model: db.PurchaseOrderItem,
      as: 'items',
      attributes: ['id', 'quantity'],
      required: false,
    }],
    order: [['po_date', 'DESC'], ['id', 'DESC']],
    limit: parseInt(limit, 10) || 1000
  });

  const poItemIds = [];
  const poItemById = new Map();
  purchaseOrders.forEach((po) => {
    (po.items || []).forEach((it) => {
      const id = Number(it.id);
      if (!Number.isFinite(id) || id <= 0) return;
      poItemIds.push(id);
      poItemById.set(id, { po_id: Number(po.id), ordered: parseFloat(it.quantity || 0) || 0 });
    });
  });

  // Sum received quantities per po_item_id across all gate entries (excluding cancelled)
  const receivedMap = {};
  if (poItemIds.length > 0) {
    const gateEntries = await db.GateEntry.findAll({
      where: { status: { [Op.notIn]: ['CANCELLED'] } },
      include: [{
        model: db.GateEntryItem,
        as: 'items',
        where: { po_item_id: { [Op.in]: poItemIds } },
        required: false,
        attributes: ['po_item_id', 'received_quantity']
      }],
      attributes: ['id']
    });
    gateEntries.forEach((ge) => {
      (ge.items || []).forEach((it) => {
        const pid = Number(it.po_item_id);
        if (!pid) return;
        receivedMap[pid] = (receivedMap[pid] || 0) + (parseFloat(it.received_quantity) || 0);
      });
    });
  }

  const pendingByPoId = new Map();
  poItemById.forEach((meta, poItemId) => {
    const already = parseFloat((receivedMap[poItemId] || 0).toFixed(3));
    const pending = parseFloat((meta.ordered - already).toFixed(3));
    const poId = meta.po_id;
    pendingByPoId.set(poId, (pendingByPoId.get(poId) || 0) + Math.max(0, pending));
  });

  const filtered = purchaseOrders
    .map((po) => {
      const plain = po.toJSON ? po.toJSON() : { ...po };
      plain.pending_quantity = parseFloat((pendingByPoId.get(Number(plain.id)) || 0).toFixed(3));
      plain.is_fully_received = plain.pending_quantity <= 0;
      delete plain.items;
      return plain;
    })
    .filter((po) => (parseFloat(po.pending_quantity || 0) || 0) > 0);

  sendResponse(res, 200, true, 'Pending Purchase Orders retrieved', { items: filtered });
});

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId, vendorId, poId, qcPending, qcCompleted } = 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 (vendorId) {
    where.vendor_id = vendorId;
  }

  if (poId) {
    where.po_id = poId;
  }

  if (status) {
    where.status = status;
  }

  if (qcPending === 'true') {
    where.status = 'QC_PENDING';
  }

  // Filter for QC completed entries: QC_APPROVED, QC_REJECTED, GRN_READY, GRN_CREATED
  if (qcCompleted === 'true') {
    where.status = { [Op.in]: ['QC_APPROVED', 'QC_REJECTED', 'GRN_READY', 'GRN_CREATED'] };
  }

  if (search) {
    where[Op.or] = [
      { gate_entry_number: { [Op.like]: `%${search}%` } },
      { invoice_number: { [Op.like]: `%${search}%` } },
      { lr_number: { [Op.like]: `%${search}%` } },
      { vehicle_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.GateEntry.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      { model: db.PurchaseOrder, as: 'purchaseOrder', attributes: ['id', 'po_number'] },
      { model: db.GoodsReceiptNote, as: 'goodsReceiptNote', attributes: ['id', 'grn_number'] },
      {
        model: db.User,
        as: 'receivedByUser',
        attributes: ['id', 'username', 'first_name', 'last_name']
      },
      {
        model: db.GateEntryItem,
        as: 'items',
        attributes: ['id', 'line_number', 'material_id', 'po_quantity', 'received_quantity', 'excess_quantity', 'qc_status', 'accepted_quantity', 'rejected_quantity'],
        required: false
      }
    ],
    limit: limitNum,
    offset,
    order: [['gate_entry_date', 'DESC'], ['id', 'DESC']]
  });

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

  // Fallback mapping for legacy data:
  // If Gate Entry is still GRN_READY and missing goodsReceiptNote link,
  // try to infer GRN by PO + vendor + invoice so UI can show GRN number.
  let grnFallbackMap = new Map();
  if (response.items && Array.isArray(response.items) && response.items.length > 0) {
    const pendingEntries = response.items
      .map((entry) => (entry.toJSON ? entry.toJSON() : { ...entry }))
      .filter((entry) =>
        entry.status === 'GRN_READY' &&
        !entry?.goodsReceiptNote?.grn_number &&
        entry.po_id
      );

    if (pendingEntries.length > 0) {
      const poIds = [...new Set(pendingEntries.map((entry) => entry.po_id))];
      const candidateGRNs = await db.GoodsReceiptNote.findAll({
        where: {
          company_id: where.company_id,
          ...(where.plant_id ? { plant_id: where.plant_id } : {}),
          po_id: { [Op.in]: poIds }
        },
        attributes: ['id', 'po_id', 'vendor_id', 'invoice_number', 'grn_number', 'created_at'],
        order: [['created_at', 'DESC']]
      });

      candidateGRNs.forEach((grn) => {
        const key = `${grn.po_id}::${grn.vendor_id || ''}::${grn.invoice_number || ''}`;
        if (!grnFallbackMap.has(key)) {
          grnFallbackMap.set(key, { id: grn.id, grn_number: grn.grn_number });
        }
      });
    }
  }

  // Enrich items with balance_quantity and receiving_status; compute overall receiving_status per gate entry
  if (response.items && Array.isArray(response.items)) {
    response.items = response.items.map(entry => {
      const plain = entry.toJSON ? entry.toJSON() : { ...entry };
      if (!plain?.goodsReceiptNote?.grn_number && plain.status === 'GRN_READY' && plain.po_id) {
        const key = `${plain.po_id}::${plain.vendor_id || ''}::${plain.invoice_number || ''}`;
        const fallbackGrn = grnFallbackMap.get(key);
        if (fallbackGrn) {
          plain.goodsReceiptNote = fallbackGrn;
        }
      }
      if (plain.items) {
        plain.items = enrichItemsWithBalance(plain.items);
        // Compute overall receiving_status for the gate entry
        const poItems = plain.items.filter(it => it.po_quantity !== null && it.po_quantity !== undefined && parseFloat(it.po_quantity) > 0);
        if (poItems.length > 0) {
          const allFull = poItems.every(it => it.receiving_status === 'Fully Received');
          const allNot = poItems.every(it => it.receiving_status === 'Not Received');
          if (allFull) {
            plain.receiving_status = 'Fully Received';
          } else if (allNot) {
            plain.receiving_status = 'Not Received';
          } else {
            plain.receiving_status = 'Partial Received';
          }
        } else {
          plain.receiving_status = null;
        }
      }
      return plain;
    });
  }
  sendResponse(res, 200, true, 'Gate Entries retrieved successfully', response);
});

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

  const gateEntry = await db.GateEntry.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.BusinessPartner, as: 'vendor' },
      { model: db.PurchaseOrder, as: 'purchaseOrder' },
      { model: db.GoodsReceiptNote, as: 'goodsReceiptNote' },
      {
        model: db.User,
        as: 'receivedByUser',
        attributes: [
          'id',
          'username',
          'first_name',
          'last_name'
        ]
      },
      {
        model: db.User,
        as: 'checkedByUser',
        attributes: [
          'id',
          'username',
          'first_name',
          'last_name'
        ]
      },
      {
        model: db.User,
        as: 'qcApprovedByUser',
        attributes: [
          'id',
          'username',
          'first_name',
          'last_name'
        ]
      },
      {
        model: db.GateEntryItem,
        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'] },
              { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code', 'uom_name'] },
            ],
          },
          { model: db.UOM, as: 'uom' },
          {
            model: db.PurchaseOrderItem,
            as: 'purchaseOrderItem',
            include: [
              {
                model: db.StorageLocation,
                as: 'storageLocation',
                attributes: ['id', 'storage_location_code', 'description']
              }
            ]
          },
          {
            model: db.User,
            as: 'qcCheckedByUser',
            attributes: [
              'id',
              'username',
              'first_name',
              'last_name'
            ]
          }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

  if (!gateEntry) {
    return sendError(res, 'Gate Entry not found', 404);
  }

  // Enrich items with balance_quantity and receiving_status
  const plain = gateEntry.toJSON ? gateEntry.toJSON() : { ...gateEntry };
  if (plain.items) {
    plain.items = enrichItemsWithBalance(plain.items);
  }
  sendResponse(res, 200, true, 'Gate Entry retrieved successfully', plain);
});

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

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

  if (!data.plant_id || !data.fiscal_year_id || !data.vendor_id) {
    return sendError(res, 'Plant ID, Fiscal Year ID, and Vendor ID are required', 400);
  }

  const receivedByValidation = await validateReceivedByUser(data.received_by, companyId);
  if (!receivedByValidation.valid) {
    return sendError(res, receivedByValidation.message, 400);
  }

  if (!data.items || !Array.isArray(data.items) || data.items.length === 0) {
    return sendError(res, 'At least one item is required', 400);
  }

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

  try {
    if (!data.gate_entry_number) {
      data.gate_entry_number = await generateGateEntryNumber(companyId, data.fiscal_year_id);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;
    data.entry_time = data.entry_time || new Date();
    // Ensure a valid DATEONLY value (prevents 0000-00-00 / null issues)
    data.gate_entry_date = data.gate_entry_date || new Date().toISOString().split('T')[0];

    const { items, ...gateEntryData } = data;

    // Item-level routing: QC required is driven by Material Master (inspection_required),
    // but user may optionally send non-QC materials to QC.
    const materialMap = await fetchMaterialsForLineDescription(
      db,
      items.map((it) => it.material_id),
      { transaction }
    );

    // Always create as DRAFT. "Check Materials" will move it to QC_PENDING/GRN_READY.
    gateEntryData.status = 'DRAFT';

    gateEntryData.total_items = items.length;

    const forceQcByDestination = String(gateEntryData.destination_type || data.destination_type || '').toUpperCase() === 'QC';
    const qcRequiredForAnyItem = items.some((it) => {
      const mat = materialMap.get(parseInt(it.material_id, 10));
      const matRequiresQc = !!mat?.inspection_required || mat?.inspection_type === 'MANDATORY';
      const requestedQc = it.qc_required === true || it.qc_status === 'PENDING' || it.move_to === 'QC';
      return forceQcByDestination || matRequiresQc || requestedQc;
    });
    gateEntryData.qc_required = qcRequiredForAnyItem;
    gateEntryData.destination_type = qcRequiredForAnyItem ? 'QC' : 'STORES';

    const gateEntry = await db.GateEntry.create(gateEntryData, { transaction });

    // Create gate entry items
    const gateEntryItems = items.map((item, index) => {
      const receivedQty = parseFloat(item.received_quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const mat = materialMap.get(parseInt(item.material_id, 10));
      const matRequiresQc = !!mat?.inspection_required || mat?.inspection_type === 'MANDATORY';
      const requestedQc = item.qc_required === true || item.qc_status === 'PENDING' || item.move_to === 'QC';
      const qcRequired = forceQcByDestination || matRequiresQc || requestedQc;

      const poQty = item.po_quantity ? parseFloat(item.po_quantity) : null;
      const inputExcessQty = Number.isFinite(parseFloat(item.excess_quantity))
        ? parseFloat(item.excess_quantity)
        : null;
      const excessQty = inputExcessQty !== null
        ? inputExcessQty
        : (poQty !== null && poQty > 0 && receivedQty > poQty
          ? parseFloat((receivedQty - poQty).toFixed(3))
          : 0);
      const { balance_quantity, receiving_status } = computeItemBalanceAndStatus(poQty, receivedQty);

      return {
        gate_entry_id: gateEntry.id,
        po_item_id: item.po_item_id || null,
        line_number: index + 1,
        material_id: item.material_id,
        description: resolvePurchaseLineDescription(item, mat),
        po_quantity: poQty,
        received_quantity: receivedQty,
        excess_quantity: excessQty,
        balance_quantity,
        receiving_status,
        accepted_quantity: null,
        rejected_quantity: 0,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        batch_number: item.batch_number || null,
        condition: item.condition || 'GOOD',
        qc_status: qcRequired ? 'PENDING' : 'NOT_REQUIRED',
        qc_remarks: null,
        remarks: item.remarks || null,
        line_weight_kg: computeLineWeightKg({
          quantity: receivedQty,
          line_weight_kg: item.line_weight_kg,
          material: mat,
        }),
      };
    });

    await db.GateEntryItem.bulkCreate(gateEntryItems, { transaction });

    // Update overall receiving_status on gate entry header
    const overallStatus = computeOverallReceivingStatus(gateEntryItems.map(it => it.receiving_status));
    await gateEntry.update({ receiving_status: overallStatus }, { transaction });

    await transaction.commit();

    const createdGateEntry = await db.GateEntry.findByPk(gateEntry.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.BusinessPartner, as: 'vendor' },
        { model: db.PurchaseOrder, as: 'purchaseOrder' },
        {
          model: db.User,
          as: 'receivedByUser',
          attributes: ['id', 'username', 'first_name', 'last_name']
        },
        {
          model: db.GateEntryItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'Gate Entry created successfully', createdGateEntry);
  } catch (error) {
    await transaction.rollback();
    console.error('Error creating gate entry:', error);
    throw error;
  }
});

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

  const gateEntry = await db.GateEntry.findByPk(id);
  if (!gateEntry) {
    return sendError(res, 'Gate Entry not found', 404);
  }

  if (gateEntry.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Gate Entries can be updated', 400);
  }

  const gateEntryCompanyId = gateEntry.company_id || req.user?.companyId;
  const receivedByValidation = await validateReceivedByUser(data.received_by, gateEntryCompanyId);
  if (!receivedByValidation.valid) {
    return sendError(res, receivedByValidation.message, 400);
  }

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

  try {
    const { items, ...gateEntryData } = data;
    gateEntryData.updated_by = userId;

    if (items && Array.isArray(items)) {
      gateEntryData.total_items = items.length;
    }

    let lineMaterialMap = new Map();
    // Recompute header routing based on item/material QC requirement
    if (items && Array.isArray(items) && items.length > 0) {
      lineMaterialMap = await fetchMaterialsForLineDescription(
        db,
        items.map((it) => it.material_id),
        { transaction }
      );

      const forceQcByDestination = String(gateEntryData.destination_type || data.destination_type || '').toUpperCase() === 'QC';
      const qcRequiredForAnyItem = items.some((it) => {
        const mat = lineMaterialMap.get(parseInt(it.material_id, 10));
        const matRequiresQc = !!mat?.inspection_required || mat?.inspection_type === 'MANDATORY';
        const requestedQc = it.qc_required === true || it.qc_status === 'PENDING' || it.move_to === 'QC';
        return forceQcByDestination || matRequiresQc || requestedQc;
      });
      gateEntryData.qc_required = qcRequiredForAnyItem;
      gateEntryData.destination_type = qcRequiredForAnyItem ? 'QC' : 'STORES';
    }

    await gateEntry.update(gateEntryData, { transaction });

    if (items && Array.isArray(items)) {
      await db.GateEntryItem.destroy({ where: { gate_entry_id: gateEntry.id }, transaction });

      const gateEntryItems = items.map((item, index) => {
        const receivedQty = parseFloat(item.received_quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const mat = lineMaterialMap.get(parseInt(item.material_id, 10));
        const matRequiresQc = !!mat?.inspection_required || mat?.inspection_type === 'MANDATORY';
        const requestedQc = item.qc_required === true || item.qc_status === 'PENDING' || item.move_to === 'QC';
        const forceQcByDestination = String(gateEntryData.destination_type || data.destination_type || '').toUpperCase() === 'QC';
        const qcRequired = forceQcByDestination || matRequiresQc || requestedQc;
        const poQty = item.po_quantity ? parseFloat(item.po_quantity) : null;
        const inputExcessQty = Number.isFinite(parseFloat(item.excess_quantity))
          ? parseFloat(item.excess_quantity)
          : null;
        const excessQty = inputExcessQty !== null
          ? inputExcessQty
          : (poQty !== null && poQty > 0 && receivedQty > poQty
            ? parseFloat((receivedQty - poQty).toFixed(3))
            : 0);
        const { balance_quantity, receiving_status } = computeItemBalanceAndStatus(poQty, receivedQty);

        return {
          gate_entry_id: gateEntry.id,
          po_item_id: item.po_item_id || null,
          line_number: index + 1,
          material_id: item.material_id,
          description: resolvePurchaseLineDescription(item, mat),
          po_quantity: poQty,
          received_quantity: receivedQty,
          excess_quantity: excessQty,
          balance_quantity,
          receiving_status,
          accepted_quantity: item.accepted_quantity ? parseFloat(item.accepted_quantity) : null,
          rejected_quantity: item.rejected_quantity ? parseFloat(item.rejected_quantity) : 0,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          batch_number: item.batch_number || null,
          condition: item.condition || 'GOOD',
          qc_status: qcRequired ? 'PENDING' : 'NOT_REQUIRED',
          qc_remarks: item.qc_remarks || null,
          remarks: item.remarks || null,
          line_weight_kg: computeLineWeightKg({
            quantity: receivedQty,
            line_weight_kg: item.line_weight_kg,
            material: mat,
          }),
        };
      });

      await db.GateEntryItem.bulkCreate(gateEntryItems, { transaction });

      // Update overall receiving_status on gate entry header
      const overallStatus = computeOverallReceivingStatus(gateEntryItems.map(it => it.receiving_status));
      await gateEntry.update({ receiving_status: overallStatus }, { transaction });
    }

    await transaction.commit();

    const updatedGateEntry = await db.GateEntry.findByPk(gateEntry.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.BusinessPartner, as: 'vendor' },
        {
          model: db.User,
          as: 'receivedByUser',
          attributes: ['id', 'username', 'first_name', 'last_name']
        },
        {
          model: db.GateEntryItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Gate Entry updated successfully', updatedGateEntry);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const gateEntry = await db.GateEntry.findByPk(id, {
    include: [{ model: db.GateEntryItem, as: 'items' }]
  });

  if (!gateEntry) {
    return sendError(res, 'Gate Entry not found', 404);
  }

  if (gateEntry.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Gate Entries can be checked', 400);
  }

  // Validate received quantities for all items before checking
  for (const item of gateEntry.items || []) {
    const received = parseFloat(item.received_quantity) || 0;
    if (received <= 0) {
      return sendError(res, 'Enter received quantity for all materials before checking', 400);
    }
  }

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

  try {
    // Determine QC requirement per item from Material Master + draft selection (qc_status)
    const materialIds = [...new Set((gateEntry.items || []).map((it) => it.material_id).filter(Boolean))];
    const materials = await db.Material.findAll({
      where: { id: materialIds },
      attributes: ['id', 'inspection_required', 'inspection_type']
    });
    const materialMap = new Map(materials.map((m) => [m.id, m]));

    const itemsToUpdate = gateEntry.items || [];
    let anyQc = false;

    const forceQcByDestination = String(gateEntry.destination_type || '').toUpperCase() === 'QC';
    for (const it of itemsToUpdate) {
      const mat = materialMap.get(it.material_id);
      const matRequiresQc = !!mat?.inspection_required || mat?.inspection_type === 'MANDATORY';
      const requestedQc = it.qc_status === 'PENDING';
      const qcRequired = forceQcByDestination || matRequiresQc || requestedQc;
      anyQc = anyQc || qcRequired;

      await db.GateEntryItem.update(
        qcRequired
          ? { qc_status: 'PENDING', accepted_quantity: null, rejected_quantity: 0 }
          : {
            qc_status: 'NOT_REQUIRED',
            accepted_quantity: db.sequelize.col('received_quantity'),
            rejected_quantity: 0
          },
        { where: { id: it.id }, transaction }
      );
    }

    // GRN must be created before inward inspection; keep checked entries GRN-ready.
    // QC requirement is still preserved via qc_required + destination_type.
    const newStatus = 'GRN_READY';
    await gateEntry.update(
      {
        status: newStatus,
        destination_type: anyQc ? 'QC' : 'STORES',
        qc_required: anyQc,
        checked_by: userId,
        checked_at: new Date(),
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();

    const updatedGateEntry = await db.GateEntry.findByPk(gateEntry.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.BusinessPartner, as: 'vendor' },
        {
          model: db.GateEntryItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Materials checked successfully', updatedGateEntry);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const gateEntry = await db.GateEntry.findByPk(id, {
    include: [
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      {
        model: db.GateEntryItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' },
          {
            model: db.PurchaseOrderItem,
            as: 'purchaseOrderItem',
            include: [
              {
                model: db.PurchaseRequisitionItem,
                as: 'purchaseRequisitionItem'
              }
            ]
          }
        ]
      }
    ]
  });

  if (!gateEntry) {
    return sendError(res, 'Gate Entry not found', 404);
  }

  if (gateEntry.status !== 'GRN_READY' && gateEntry.status !== 'QC_APPROVED') {
    return sendError(res, 'Gate Entry must be GRN_READY or QC_APPROVED to create GRN', 400);
  }

  if (gateEntry.grn_id) {
    return sendError(res, 'GRN already created for this Gate Entry', 400);
  }

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

  try {
    // Generate GRN number (company code + FY reset from Number Format Master)
    let grnNumber;
    try {
      grnNumber = await numberFormatService.generateNextNumber(companyId, 'GOODS_RECEIPT_NOTE', gateEntry.fiscal_year_id, transaction);
    } catch (err) {
      console.warn('GRN number from format master failed, using fallback:', err.message);
      const today = new Date();
      const year = today.getFullYear();
      const prefix = `GRN${year}`;
      const lastGRN = await db.GoodsReceiptNote.findOne({
        where: { company_id: companyId, grn_number: { [Op.like]: `${prefix}%` } },
        order: [['grn_number', 'DESC']],
        transaction
      });
      let sequence = 1;
      if (lastGRN) {
        const lastSequence = parseInt(lastGRN.grn_number.slice(-4)) || 0;
        sequence = lastSequence + 1;
      }
      grnNumber = `${prefix}${sequence.toString().padStart(4, '0')}`;
    }

    // Create GRN
    const grnData = {
      company_id: companyId,
      plant_id: gateEntry.plant_id,
      fiscal_year_id: gateEntry.fiscal_year_id,
      grn_number: grnNumber,
      grn_date: new Date().toISOString().split('T')[0],
      po_id: gateEntry.po_id,
      vendor_id: gateEntry.vendor_id,
      invoice_number: gateEntry.invoice_number,
      invoice_date: gateEntry.invoice_date,
      lr_number: gateEntry.lr_number,
      lr_date: gateEntry.lr_date,
      transporter_name: gateEntry.transporter_name,
      vehicle_number: gateEntry.vehicle_number,
      status: 'DRAFT',
      remarks: `Created from Gate Entry: ${gateEntry.gate_entry_number}`,
      created_by: userId,
      updated_by: userId
    };

    // Calculate total from accepted items
    const filteredItems = gateEntry.items.filter(item => {
      const acceptedQty = parseFloat(item.accepted_quantity || item.received_quantity) || 0;
      const qcStatus = String(item.qc_status || '').toUpperCase();
      return acceptedQty > 0 && qcStatus !== 'REJECTED';
    });

    // Fetch PO to get PR, then fetch PR items to get required_date
    let prItemsMap = {};
    if (gateEntry.po_id) {
      const purchaseOrder = await db.PurchaseOrder.findByPk(gateEntry.po_id, {
        include: [
          {
            model: db.PurchaseRequisition,
            as: 'purchaseRequisition',
            include: [
              {
                model: db.PurchaseRequisitionItem,
                as: 'items',
                attributes: ['id', 'material_id', 'required_date']
              }
            ],
            required: false
          }
        ],
        transaction
      });

      if (purchaseOrder?.purchaseRequisition?.items) {
        purchaseOrder.purchaseRequisition.items.forEach(prItem => {
          prItemsMap[prItem.material_id] = prItem.required_date;
        });
      }
    }

    const grnLineMaterialMap = await fetchMaterialsForLineDescription(
      db,
      filteredItems.map((i) => i.material_id),
      { transaction }
    );

    let totalAmount = 0;
    const grnItems = filteredItems.map((item, index) => {
      const acceptedQty = parseFloat(item.accepted_quantity || item.received_quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const taxRate = parseFloat(item.purchaseOrderItem?.tax_rate || 0) || 0;

      const lineTotal = acceptedQty * unitPrice;
      const taxAmount = (lineTotal * taxRate) / 100;
      const lineTotalWithTax = lineTotal + taxAmount;

      totalAmount += lineTotalWithTax;

      // Get required_date from PR item by matching material_id
      const requiredDate = prItemsMap[item.material_id] || null;

      const linePayload = {
        material_id: item.material_id,
        description:
          item.description ||
          item.purchaseOrderItem?.description ||
          item.material?.description ||
          '',
      };

      return {
        grn_id: null, // Will be set after GRN creation
        po_item_id: item.po_item_id,
        line_number: index + 1,
        material_id: item.material_id,
        description: resolvePurchaseLineDescription(
          linePayload,
          grnLineMaterialMap.get(parseInt(item.material_id, 10))
        ),
        quantity: acceptedQty,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        tax_rate: taxRate,
        tax_amount: taxAmount,
        line_total: lineTotalWithTax,
        batch_number: item.batch_number,
        required_date: requiredDate,
        // Prefer explicit PO line storage location; fallback to material default if needed
        storage_location_id: item.purchaseOrderItem?.storage_location_id || item.material?.storage_location_id || null,
        remarks: item.remarks || item.qc_remarks || null
      };
    });

    if (grnItems.length === 0) {
      await transaction.rollback();
      return sendError(res, 'No accepted items found to create GRN', 400);
    }

    grnData.total_amount = totalAmount;

    const grn = await db.GoodsReceiptNote.create(grnData, { transaction });

    // Update grn_id in items
    grnItems.forEach(item => {
      item.grn_id = grn.id;
    });

    const createdGRNItems = await db.GoodsReceiptNoteItem.bulkCreate(grnItems, { transaction });

    // Link gate entry items to GRN items
    let grnItemIndex = 0;
    for (const gateItem of gateEntry.items) {
      const acceptedQty = parseFloat(gateItem.accepted_quantity || gateItem.received_quantity) || 0;
      const qcStatus = String(gateItem.qc_status || '').toUpperCase();
      if (acceptedQty > 0 && qcStatus !== 'REJECTED') {
        await gateItem.update({
          grn_item_id: createdGRNItems[grnItemIndex].id
        }, { transaction });
        grnItemIndex++;
      }
    }

    // Update gate entry
    await gateEntry.update({
      grn_id: grn.id,
      status: 'GRN_CREATED',
      updated_by: userId
    }, { transaction });

    await transaction.commit();

    const createdGRN = await db.GoodsReceiptNote.findByPk(grn.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.BusinessPartner, as: 'vendor' },
        { model: db.PurchaseOrder, as: 'purchaseOrder' },
        {
          model: db.GoodsReceiptNoteItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'GRN created successfully from Gate Entry', createdGRN);
  } catch (error) {
    await transaction.rollback();
    console.error('Error creating GRN from gate entry:', error);
    throw error;
  }
});

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

  const gateEntry = await db.GateEntry.findByPk(id);
  if (!gateEntry) {
    return sendError(res, 'Gate Entry not found', 404);
  }

  if (gateEntry.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Gate Entries can be deleted', 400);
  }

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

  try {
    await db.GateEntryItem.destroy({ where: { gate_entry_id: id }, transaction });
    await gateEntry.destroy({ transaction });
    await transaction.commit();

    sendResponse(res, 200, true, 'Gate Entry deleted successfully', null);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

