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 atpService = require('../services/atpService');
const notificationService = require('../services/notificationService');
const statusValidationService = require('../services/statusValidationService');
const businessRuleValidationService = require('../services/businessRuleValidationService');
const { generateNextNumber } = require('../services/numberFormatService');
const { computeLineWeightKg, allocateNestingWeightForMaterialRequestItem } = require('../utils/lineWeightKg');

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

const toDateOnly = (value) => {
  if (!value) return null;
  const d = new Date(value);
  if (Number.isNaN(d.getTime())) return null;
  return new Date(d.getFullYear(), d.getMonth(), d.getDate());
};

const isPastRequiredDate = (requiredDate, referenceDate = new Date()) => {
  const req = toDateOnly(requiredDate);
  if (!req) return false;
  const ref = toDateOnly(referenceDate);
  return req < ref;
};

const getMaterialDisplay = (item) => {
  const m = item?.material;
  if (!m) return `Material ${item?.material_id || '-'}`;
  const dr = (m.drawing_number || '').trim();
  const desc = (m.description || '').trim();
  if (dr && desc) return `${dr} — ${desc}`;
  if (dr) return dr;
  if (desc) return desc;
  return m.material_number || `Material ${item?.material_id || '-'}`;
};

/** Same rules as Stores UI "Materials for Issue" tab. */
const mrItemIssueEligible = (item) => {
  const available = parseFloat(item.available_stock || 0);
  const required = parseFloat(item.required_quantity || 0);
  const issued = parseFloat(item.issued_quantity || 0);
  const pending = required - issued;
  const remainingFromSnapshot = Math.max(0, available - issued);
  return pending > 0 && remainingFromSnapshot > 0;
};

/** Same rules as Stores UI "Materials for PR" tab. */
const mrItemPrEligible = (item) => {
  const available = parseFloat(item.available_stock || 0);
  const required = parseFloat(item.required_quantity || 0);
  const issued = parseFloat(item.issued_quantity || 0);
  const pending = required - issued;
  const remainingFromSnapshot = Math.max(0, available - issued);
  const purchaseShortfall = Math.max(0, pending - remainingFromSnapshot);
  return pending > 0 && purchaseShortfall > 0;
};

async function refreshMaterialRequestItemAtp(item, request) {
  try {
    const stockInfo = await atpService.getAvailableStock(
      item.material_id,
      request.plant_id,
      item.storage_location || null
    );

    let availableStock = parseFloat(stockInfo?.available || 0);
    const balanceQuantity = parseFloat(stockInfo?.balanceQuantity || 0);

    if (availableStock === 0 && balanceQuantity > 0) {
      const reservedQty = parseFloat(stockInfo?.reserved || stockInfo?.pendingQuantity || 0);
      if (reservedQty === 0) {
        availableStock = balanceQuantity;
      } else {
        availableStock = Math.max(0, balanceQuantity - reservedQty);
      }
    }

    const existingAvailableStock = parseFloat(item.available_stock || 0);
    if (availableStock === 0 && existingAvailableStock > 0) {
      console.warn(
        `[Material Request Review] Material ID ${item.material_id}: ATP returned 0 but available_stock was ${existingAvailableStock}. Preserving.`
      );
      availableStock = existingAvailableStock;
    }

    const requiredQty = parseFloat(item.required_quantity || 0);
    const issuedQty = parseFloat(item.issued_quantity || 0);
    const pendingQty = requiredQty - issuedQty;
    const shortfall = Math.max(0, pendingQty - availableStock);

    await item.update({
      available_stock: availableStock,
      shortfall_quantity: shortfall,
      pending_quantity: pendingQty
    });
  } catch (error) {
    console.error(`Error updating stock for item ${item.id} (Material ID: ${item.material_id}):`, error);
  }
}

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

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

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

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

  if (productionOrderId) {
    where.production_order_id = productionOrderId;
  }

  if (requestType) {
    where.request_type = requestType;
  }

  if (departmentId) {
    where.department_id = departmentId;
  }

  // Support both single status and multiple statuses (comma-separated)
  if (statuses) {
    const list = String(statuses)
      .split(',')
      .map((s) => s.trim())
      .filter(Boolean);
    if (list.length > 0) {
      where.status = { [Op.in]: list };
    }
  } else if (status) {
    where.status = status;
  }

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

  const records = await db.MaterialRequest.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      // Include production_order_component_id to track which components have MR sent
      { model: db.MaterialRequestItem, as: 'items', attributes: ['id', 'required_date', 'production_order_component_id'], required: false },
      { 
        model: db.ProductionOrder, 
        as: 'productionOrder', 
        attributes: ['id', 'production_order_number', 'status'] 
      },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] }
    ],
    limit: limitNum,
    offset,
    order: [['request_date', 'DESC'], ['id', 'DESC']]
  });

  // Convert Sequelize instances to plain objects to ensure nested associations are serialized
  const items = records.rows.map(record => {
    if (record && typeof record.toJSON === 'function') {
      const json = record.toJSON();
      // Derive min required_date from line items (useful for list page)
      const itemDates = (json.items || [])
        .map((it) => it.required_date)
        .filter(Boolean)
        .sort();
      json.min_required_date = itemDates.length > 0 ? itemDates[0] : null;
      // Backward-compatible alias for UI convenience
      if (!json.required_date) {
        json.required_date = json.min_required_date;
      }
      return json;
    }
    return record;
  });

  const response = getPagingData({ ...records, rows: items }, page, limitNum);
  sendResponse(res, 200, true, 'Material Requests retrieved successfully', response);
});

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

  const request = await db.MaterialRequest.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.Department, as: 'department' },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      { 
        model: db.ProductionOrder, 
        as: 'productionOrder',
        include: [
          { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] }
        ]
      },
      { model: db.PurchaseRequisition, as: 'purchaseRequisition', attributes: ['id', 'pr_number'] },
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { 
            model: db.Material, 
            as: 'material',
            attributes: [
              'id',
              'material_number',
              'drawing_number',
              'description',
              'material_type',
              'material_type_id',
              'gross_weight',
              'net_weight',
              'weight_uom_id',
              'length',
              'width',
              'height',
              'dimension_unit',
              'thickness',
              'thickness_id',
              'grade_id'
            ],
            include: [
              { model: db.MaterialThickness, as: 'materialThickness', attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit', 'description'] },
              { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name', 'description'] },
              { model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code', 'uom_name'] }
            ]
          },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] },
          { model: db.ProductionOrderComponent, as: 'productionOrderComponent' }
        ],
        order: [['line_number', 'ASC']]
      },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] },
      { model: db.User, as: 'sentToStoresByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'reviewedByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'sentToPurchaseByUser', attributes: ['id', 'first_name', 'last_name'] }
    ]
  });

  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }
  const plain = request.toJSON ? request.toJSON() : { ...request };
  const isFromNesting = String(plain?.remarks || '').toUpperCase().includes('FROM_NESTING');

  if (isFromNesting && Array.isArray(plain.items) && plain.items.length > 0) {
    const nestingReqs = await db.NestingStoresRequest.findAll({
      where: { material_request_id: id },
      include: [
        {
          model: db.NestingStoresRequestLine,
          as: 'lines',
          include: [
            { model: db.UOM, as: 'quantityUOM', attributes: ['id', 'uom_code', 'uom_name'], required: false }
          ],
          required: false
        }
      ]
    });

    const byMaterialId = new Map();
    (nestingReqs || []).forEach((reqRow) => {
      const lines = reqRow?.lines || [];
      lines.forEach((line) => {
        const mid = parseInt(line?.material_id, 10);
        if (!Number.isFinite(mid) || mid <= 0) return;
        const prev = byMaterialId.get(mid) || { nesting_weight: 0, quantity_uom: null };
        const lineWeight = parseFloat(line?.nesting_weight);
        const nextWeight = prev.nesting_weight + (Number.isFinite(lineWeight) ? lineWeight : 0);
        const quantityUom = prev.quantity_uom || line?.quantityUOM || null;
        byMaterialId.set(mid, { nesting_weight: nextWeight, quantity_uom: quantityUom });
      });
    });

    plain.items = plain.items.map((item) => {
      const mid = parseInt(item?.material_id, 10);
      const agg = Number.isFinite(mid) ? byMaterialId.get(mid) : null;
      if (!agg) return item;
      return {
        ...item,
        nesting_weight: Number.isFinite(agg.nesting_weight) ? parseFloat(agg.nesting_weight.toFixed(3)) : null,
        quantity_uom: agg.quantity_uom || null
      };
    });
  }

  sendResponse(res, 200, true, 'Material Request 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) {
    return sendError(res, 'Plant ID and Fiscal Year ID are required', 400);
  }

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

  try {
    // Optional: common required date for all items (frontend can pass this)
    const globalRequiredDate = data.required_date || data.requiredDate || null;
    // Optional: per-component/item required dates map (keyed by production_order_component_id)
    const requiredDatesMap = data.required_dates || data.requiredDates || null;

    // Get user's department_id for the Material Request (if not provided in request body)
    if (!data.department_id && userId) {
      const user = await db.User.findByPk(userId, {
        attributes: ['id', 'department_id'],
        transaction
      });
      if (user?.department_id) {
        data.department_id = user.department_id;
      }
    }

    // Decide creation mode:
    // 1) PRODUCTION_ORDER: production_order_id present (existing behavior)
    // 2) GENERAL: no production_order_id, items[] provided (new behavior)
    const isProductionOrderRequest = !!data.production_order_id;
    const isGeneralRequest = !data.production_order_id && Array.isArray(data.items) && data.items.length > 0;

    if (!isProductionOrderRequest && !isGeneralRequest) {
      throw new Error('Provide either production_order_id (Production Order request) OR items[] (General request)');
    }

    // Department must always be captured so Stores can identify which department requested it.
    // For Production Order requests, try to get default Production department if user doesn't have one
    if (!data.department_id) {
      if (isProductionOrderRequest) {
        // Try multiple methods to find a Production department for the company
        let productionDept = await db.Department.findOne({
          where: {
            company_id: companyId,
            department_code: 'PROD',
            status: 'ACTIVE'
          },
          attributes: ['id'],
          transaction
        });
        
        // If not found by code, try by name
        if (!productionDept) {
          productionDept = await db.Department.findOne({
            where: {
              company_id: companyId,
              department_name: { [Op.like]: '%Production%' },
              status: 'ACTIVE'
            },
            attributes: ['id'],
            order: [['id', 'ASC']],
            transaction
          });
        }
        
        // If still not found, try to find any active department for the company
        if (!productionDept) {
          productionDept = await db.Department.findOne({
            where: {
              company_id: companyId,
              status: 'ACTIVE'
            },
            attributes: ['id'],
            order: [['id', 'ASC']],
            transaction
          });
        }
        
        if (productionDept) {
          data.department_id = productionDept.id;
        }
      }
      
      // If still no department_id, throw error
      if (!data.department_id) {
        throw new Error('department_id is required for Material Request. Please ensure your user account has a department assigned, or contact administrator to set up a default Production department.');
      }
    }

    // Generate request number (must be globally unique as per schema)
    if (!data.request_number) {
      data.request_number = await generateRequestNumber(companyId, data.fiscal_year_id, transaction);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;
    data.status = 'DRAFT';
    if (!data.request_date) {
      data.request_date = new Date().toISOString().split('T')[0];
    }
    // Persist header-level required_date (optional). If not explicitly provided, fall back to globalRequiredDate/request_date.
    if (!data.required_date) {
      data.required_date = globalRequiredDate || data.request_date;
    }

    // Default request type if not provided
    if (!data.request_type) {
      data.request_type = isProductionOrderRequest ? 'PRODUCTION_ORDER' : 'GENERAL';
    }

    const { items, ...requestData } = data;
    
    const materialRequest = await db.MaterialRequest.create(requestData, { transaction });

    if (isProductionOrderRequest) {
      // Get Production Order with components
      const productionOrder = await db.ProductionOrder.findByPk(data.production_order_id, {
        include: [
          {
            model: db.ProductionOrderComponent,
            as: 'components',
            include: [
              { model: db.Material, as: 'material' },
              { model: db.UOM, as: 'uom' }
            ]
          }
        ],
        transaction
      });

      if (!productionOrder) {
        throw new Error('Production Order not found');
      }

      // Filter only raw materials
      let rawMaterialComponents = productionOrder.components.filter(comp => {
        const material = comp.material;
        return material && material.material_type === 'RAW_MATERIAL';
      });

      // If component_ids are provided, filter to only include selected components
      if (data.component_ids && Array.isArray(data.component_ids) && data.component_ids.length > 0) {
        rawMaterialComponents = rawMaterialComponents.filter(comp =>
          data.component_ids.includes(comp.id)
        );
      }

      if (rawMaterialComponents.length === 0) {
        throw new Error('No raw materials selected for Material Request');
      }

      // Create request items with stock availability check
      for (let i = 0; i < rawMaterialComponents.length; i++) {
        const comp = rawMaterialComponents[i];
        const material = comp.material;

        let availableStock = 0;
        try {
          // Third parameter is storageLocation (optional), not companyId
          const stockInfo = await atpService.getAvailableStock(
            material.id,
            data.plant_id,
            comp.storage_location || null
          );
          availableStock = parseFloat(stockInfo.available || 0);
        } catch (error) {
          console.error(`Error getting stock for material ${material.id}:`, error);
        }

        const requiredQty = parseFloat(comp.required_quantity || 0);
        const shortfall = Math.max(0, requiredQty - availableStock);
        const itemRequiredDate =
          (requiredDatesMap && (requiredDatesMap[comp.id] || requiredDatesMap[String(comp.id)])) ||
          globalRequiredDate ||
          productionOrder.finish_date ||
          data.request_date ||
          new Date().toISOString().split('T')[0];

        await db.MaterialRequestItem.create({
          request_id: materialRequest.id,
          production_order_component_id: comp.id,
          line_number: i + 1,
          material_id: material.id,
          material_number: material.material_number
            ? String(material.material_number).trim().slice(0, 100)
            : null,
          description: material.description || '',
          required_quantity: requiredQty,
          uom_id: comp.uom_id,
          available_stock: availableStock,
          shortfall_quantity: shortfall,
          issued_quantity: 0,
          pending_quantity: requiredQty,
          storage_location: comp.storage_location || null,
          required_date: itemRequiredDate,
          remarks: comp.remarks || null
        }, { transaction });
      }
    } else {
      // GENERAL request: create items from request body
      if (!Array.isArray(items) || items.length === 0) {
        throw new Error('General Material Request requires items[]');
      }

      for (let idx = 0; idx < items.length; idx++) {
        const it = items[idx] || {};
        const materialId = parseInt(it.material_id, 10);
        if (!materialId) throw new Error(`Item ${idx + 1}: material_id is required`);

        const requiredQty = parseFloat(it.required_quantity || 0);
        if (!(requiredQty > 0)) throw new Error(`Item ${idx + 1}: required_quantity must be > 0`);

        const storageLocation = it.storage_location || null;
        const itemRequiredDate =
          it.required_date ||
          it.requiredDate ||
          globalRequiredDate ||
          data.required_date ||
          data.request_date ||
          new Date().toISOString().split('T')[0];

        // Resolve UOM
        let uomId = it.uom_id ? parseInt(it.uom_id, 10) : null;
        let materialDescription = it.description || '';
        const materialRecord = await db.Material.findByPk(materialId, {
          attributes: ['id', 'description', 'base_uom_id', 'material_number'],
          transaction
        });
        if (!materialRecord) throw new Error(`Item ${idx + 1}: Material not found (${materialId})`);
        materialDescription = materialDescription || materialRecord.description || '';
        uomId = uomId || materialRecord.base_uom_id;
        if (!uomId) throw new Error(`Item ${idx + 1}: uom_id is required (and material has no base_uom_id)`);
        const itemMaterialNumber =
          it.material_number != null && String(it.material_number).trim()
            ? String(it.material_number).trim().slice(0, 100)
            : materialRecord.material_number
              ? String(materialRecord.material_number).trim().slice(0, 100)
              : null;

        // Stock availability
        let availableStock = 0;
        try {
          const stockInfo = await atpService.getAvailableStock(materialId, data.plant_id, storageLocation);
          availableStock = parseFloat(stockInfo.available || 0);
        } catch (error) {
          console.error(`Error getting stock for material ${materialId}:`, error);
        }

        const shortfall = Math.max(0, requiredQty - availableStock);

        await db.MaterialRequestItem.create({
          request_id: materialRequest.id,
          production_order_component_id: null,
          line_number: it.line_number ? parseInt(it.line_number, 10) : (idx + 1),
          material_id: materialId,
          description: materialDescription,
          required_quantity: requiredQty,
          uom_id: uomId,
          available_stock: availableStock,
          shortfall_quantity: shortfall,
          issued_quantity: 0,
          pending_quantity: requiredQty,
          storage_location: storageLocation,
          required_date: itemRequiredDate,
          remarks: it.remarks || null,
        }, { transaction });
      }
    }

    await transaction.commit();

    const createdRequest = await db.MaterialRequest.findByPk(materialRequest.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.ProductionOrder, as: 'productionOrder' },
        { model: db.Department, as: 'department' },
        { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
        {
          model: db.MaterialRequestItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        },
        { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] }
      ]
    });

    sendResponse(res, 201, true, 'Material Request created successfully', createdRequest);
  } catch (error) {
    await transaction.rollback();

    // Log full error for debugging in backend console
    console.error('Material Request create failed:', error);

    // Surface Sequelize validation details to frontend
    if (error?.name === 'SequelizeValidationError' || error?.name === 'SequelizeUniqueConstraintError') {
      const details = Array.isArray(error.errors) ? error.errors.map((e) => e.message).filter(Boolean) : [];
      const message = details.length > 0 ? details.join(', ') : (error.message || 'Validation error');
      const statusCode = error.name === 'SequelizeUniqueConstraintError' ? 409 : 400;
      return sendError(res, message, statusCode, details.length > 0 ? details : null);
    }

    if (error?.name === 'SequelizeForeignKeyConstraintError') {
      return sendError(res, 'Referenced record does not exist.', 400);
    }

    if (error?.name === 'SequelizeDatabaseError') {
      const sqlMsg = error?.original?.sqlMessage || error?.original?.message || error.message;
      return sendError(res, sqlMsg || 'Database operation failed.', 500);
    }

    return sendError(res, error.message || 'Failed to create Material Request', 400);
  }
});

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

  const request = await db.MaterialRequest.findByPk(id);
  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  if (!['DRAFT', 'SENT_TO_STORES'].includes(request.status)) {
    return sendError(res, 'Only DRAFT or SENT_TO_STORES Material Requests can be updated', 400);
  }

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

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

    // Keep header required_date in sync (optional). If items have dates, prefer earliest.
    const headerRequiredDate = requestData.required_date || requestData.requiredDate || null;

    await request.update(requestData, { transaction });

    if (items && Array.isArray(items)) {
      await db.MaterialRequestItem.destroy({
        where: { request_id: id },
        transaction
      });

      const idsNeedingMn = [
        ...new Set(
          items
            .map((it) => parseInt(it.material_id, 10))
            .filter((mid) => Number.isFinite(mid) && mid > 0)
        )
      ];
      const materialNumberById = new Map();
      if (idsNeedingMn.length > 0) {
        const mats = await db.Material.findAll({
          where: { id: { [Op.in]: idsNeedingMn } },
          attributes: ['id', 'material_number'],
          transaction
        });
        mats.forEach((m) => {
          const mn = m.material_number != null ? String(m.material_number).trim() : '';
          if (mn) materialNumberById.set(m.id, mn.slice(0, 100));
        });
      }

      const itemDates = [];
      for (const item of items) {
        const itemRequiredDate = item.required_date || item.requiredDate || headerRequiredDate || null;
        if (itemRequiredDate) itemDates.push(itemRequiredDate);
        const explicitMn =
          item.material_number != null && String(item.material_number).trim()
            ? String(item.material_number).trim().slice(0, 100)
            : null;
        const mid = parseInt(item.material_id, 10);
        const lineMaterialNumber = explicitMn || materialNumberById.get(mid) || null;
        await db.MaterialRequestItem.create({
          request_id: id,
          production_order_component_id: item.production_order_component_id || null,
          line_number: item.line_number,
          material_id: item.material_id,
          material_number: lineMaterialNumber,
          description: item.description || '',
          required_quantity: parseFloat(item.required_quantity),
          uom_id: item.uom_id,
          available_stock: parseFloat(item.available_stock || 0),
          shortfall_quantity: parseFloat(item.shortfall_quantity || 0),
          issued_quantity: parseFloat(item.issued_quantity || 0),
          pending_quantity: parseFloat(item.pending_quantity || item.required_quantity),
          storage_location: item.storage_location || null,
          required_date: itemRequiredDate,
          remarks: item.remarks || null
        }, { transaction });
      }

      // If header required_date not explicitly provided, derive from earliest item date
      if (!headerRequiredDate && itemDates.length > 0) {
        const sorted = itemDates.filter(Boolean).sort();
        await request.update({ required_date: sorted[0] }, { transaction });
      }
    }

    await transaction.commit();

    const updatedRequest = await db.MaterialRequest.findByPk(id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.ProductionOrder, as: 'productionOrder' },
        {
          model: db.MaterialRequestItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Material Request updated successfully', updatedRequest);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

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

  const request = await db.MaterialRequest.findByPk(id);
  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  // Required Date is mandatory before sending to Stores
  const items = await db.MaterialRequestItem.findAll({
    where: { request_id: id },
      include: [{ model: db.Material, as: 'material', attributes: ['id', 'material_number', 'drawing_number', 'description'] }],
    order: [['line_number', 'ASC']]
  });
  const missingRequiredDate = (items || []).filter((it) => !it.required_date);
  if (missingRequiredDate.length > 0) {
    const errors = missingRequiredDate.map((it) => {
      const mat = it.material;
      const matLabel = mat?.material_number || mat?.description || it.material_id;
      return `Line ${it.line_number}: ${matLabel} - Required Date is mandatory`;
    });
    return sendError(res, 'Required Date is mandatory for all items before sending to Stores', 400, errors);
  }

  // Validate status transition
  const statusValidation = statusValidationService.validateStatusTransition('materialRequest', request.status, 'SENT_TO_STORES');
  if (!statusValidation.valid) {
    return sendError(res, statusValidation.message, 400);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.materialRequest.canSendToStores(request);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  await request.update({
    status: 'SENT_TO_STORES',
    sent_to_stores_at: new Date(),
    sent_to_stores_by: userId,
    updated_by: userId
  });

  const updatedRequest = await db.MaterialRequest.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ]
      }
    ]
  });

  // Notify Stores users (and the requester) that a new Material Request has been sent
  try {
    const storesUserRoles = await db.UserRole.findAll({
      include: [
        {
          model: db.Role,
          as: 'role',
          where: {
            company_id: request.company_id,
            status: 'ACTIVE',
            [Op.or]: [
              { role_code: { [Op.like]: '%STORES%' } },
              { role_code: { [Op.like]: '%STORE%' } },
              { role_name: { [Op.like]: '%Stores%' } },
              { role_name: { [Op.like]: '%Store%' } }
            ]
          },
          required: true
        },
        {
          model: db.User,
          as: 'user',
          where: { status: 'ACTIVE', is_active: true },
          required: true
        }
      ]
    });

    const userIdsToNotify = new Set();
    if (userId) userIdsToNotify.add(userId);
    storesUserRoles.forEach(ur => {
      if (ur.user?.id) userIdsToNotify.add(ur.user.id);
    });

    const link = `/stores/materialrequest/${request.id}/view`;
    await Promise.all(
      Array.from(userIdsToNotify).map((notifyUserId) =>
        notificationService.createNotification({
          companyId: request.company_id,
          userId: notifyUserId,
          type: 'INFO',
          moduleName: 'MaterialRequest',
          transactionId: request.id,
          transactionNumber: request.request_number,
          title: 'Material Request Sent to Stores',
          message: `Material Request ${request.request_number} sent to Stores for review and issue. Please complete issue before required dates.`,
          link,
          priority: 'MEDIUM',
          createdBy: userId,
        })
      )
    );

    // SLA warning: if any pending lines are already past required date, alert immediately.
    const overdueItems = (items || []).filter((it) => {
      const required = parseFloat(it.required_quantity || 0);
      const issued = parseFloat(it.issued_quantity || 0);
      const pending = Math.max(0, required - issued);
      return pending > 0 && isPastRequiredDate(it.required_date);
    });
    if (overdueItems.length > 0) {
      const overduePreview = overdueItems.slice(0, 5).map((it) => getMaterialDisplay(it)).join(', ');
      await Promise.all(
        Array.from(userIdsToNotify).map((notifyUserId) =>
          notificationService.createNotification({
            companyId: request.company_id,
            userId: notifyUserId,
            type: 'WARNING',
            moduleName: 'MaterialRequest',
            transactionId: request.id,
            transactionNumber: request.request_number,
            title: 'Material Request Time Limit Reached',
            message: `Material Request ${request.request_number}: ${overdueItems.length} line(s) exceeded required date and are still pending issue. ${overduePreview}${overdueItems.length > 5 ? '...' : ''}`,
            link,
            priority: 'HIGH',
            createdBy: userId
          })
        )
      );
    }
  } catch (e) {
    console.error('Failed to create stores notification for Material Request:', e.message);
  }

  sendResponse(res, 200, true, 'Material Request sent to stores successfully', updatedRequest);
});

exports.review = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const now = new Date();
  const reviewScopeRaw = String(req.body?.review_scope || req.body?.scope || '').toLowerCase();
  const reviewScope = reviewScopeRaw === 'issue' || reviewScopeRaw === 'pr' ? reviewScopeRaw : 'all';

  const request = await db.MaterialRequest.findByPk(id, {
    include: [
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [{ model: db.Material, as: 'material' }]
      }
    ]
  });

  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  const currentStatus = request.status;
  let statusValidation;

  if (currentStatus === 'SENT_TO_STORES') {
    statusValidation = statusValidationService.validateStatusTransition('materialRequest', currentStatus, 'REVIEWED');
  } else if (currentStatus === 'DRAFT') {
    statusValidation = { valid: true };
  } else {
    statusValidation = {
      valid: false,
      message: `Only DRAFT or SENT_TO_STORES Material Requests can be reviewed. Current status: ${currentStatus}`
    };
  }

  if (!statusValidation.valid) {
    return sendError(res, statusValidation.message, 400);
  }

  const businessRuleValidation = await businessRuleValidationService.materialRequest.canReview(request);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  const items = request.items || [];

  /** DRAFT or explicit full review: refresh every line and mark fully reviewed (legacy / admin). */
  const useFullReview = currentStatus === 'DRAFT' || reviewScope === 'all';

  if (useFullReview) {
    for (const item of items) {
      await refreshMaterialRequestItemAtp(item, request);
    }
    await request.update({
      status: 'REVIEWED',
      reviewed_at: now,
      reviewed_by: userId,
      stores_reviewed_issue_at: now,
      stores_reviewed_issue_by: userId,
      stores_reviewed_pr_at: now,
      stores_reviewed_pr_by: userId,
      updated_by: userId
    });
  } else {
    let toRefresh = [];
    if (reviewScope === 'issue') {
      toRefresh = items.filter(mrItemIssueEligible);
    } else if (reviewScope === 'pr') {
      toRefresh = items.filter(mrItemPrEligible);
    }

    for (const item of toRefresh) {
      await refreshMaterialRequestItemAtp(item, request);
    }

    const merge = { updated_by: userId };
    if (reviewScope === 'issue') {
      merge.stores_reviewed_issue_at = now;
      merge.stores_reviewed_issue_by = userId;
    } else if (reviewScope === 'pr') {
      merge.stores_reviewed_pr_at = now;
      merge.stores_reviewed_pr_by = userId;
    }
    await request.update(merge);
    await request.reload();

    const rows = await db.MaterialRequestItem.findAll({ where: { request_id: id } });
    const issueNeed = rows.filter(mrItemIssueEligible).length;
    const prNeed = rows.filter(mrItemPrEligible).length;

    await request.reload();
    const extra = {};
    if (issueNeed === 0 && !request.stores_reviewed_issue_at) {
      extra.stores_reviewed_issue_at = now;
      extra.stores_reviewed_issue_by = userId;
    }
    if (prNeed === 0 && !request.stores_reviewed_pr_at) {
      extra.stores_reviewed_pr_at = now;
      extra.stores_reviewed_pr_by = userId;
    }
    if (Object.keys(extra).length) {
      await request.update({ ...extra, updated_by: userId });
      await request.reload();
    }

    const issueDone = !!request.stores_reviewed_issue_at || issueNeed === 0;
    const prDone = !!request.stores_reviewed_pr_at || prNeed === 0;

    if (issueDone && prDone && request.status === 'SENT_TO_STORES') {
      await request.update({
        status: 'REVIEWED',
        reviewed_at: request.reviewed_at || now,
        reviewed_by: request.reviewed_by || userId,
        updated_by: userId
      });
    }
  }

  const updatedRequest = await db.MaterialRequest.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
        ]
      }
    ]
  });

  sendResponse(res, 200, true, 'Material Request reviewed successfully', updatedRequest);
});

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

  const request = await db.MaterialRequest.findByPk(id, {
    include: [
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ]
      }
    ]
  });

  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  if (!['REVIEWED', 'SENT_TO_STORES'].includes(request.status)) {
    return sendError(res, 'Material Request must be REVIEWED or SENT_TO_STORES to send to purchase', 400);
  }

  // If purchase_requisition_id is provided, link it
  // Otherwise, the stores will create a new PR
  await request.update({
    status: 'SENT_TO_PURCHASE',
    sent_to_purchase_at: new Date(),
    sent_to_purchase_by: userId,
    purchase_requisition_id: purchase_requisition_id || null,
    updated_by: userId
  });

  const updatedRequest = await db.MaterialRequest.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.PurchaseRequisition, as: 'purchaseRequisition' },
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ]
      }
    ]
  });

  sendResponse(res, 200, true, 'Material Request sent to purchase successfully', updatedRequest);
});

exports.createPurchaseRequisition = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { item_ids } = req.body; // Optional: array of item IDs to include
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  const request = await db.MaterialRequest.findByPk(id, {
    include: [
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ]
      },
      { model: db.ProductionOrder, as: 'productionOrder' }
    ]
  });

  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  if (!['REVIEWED', 'SENT_TO_STORES'].includes(request.status)) {
    return sendError(res, 'Material Request must be REVIEWED or SENT_TO_STORES to create Purchase Requisition', 400);
  }

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

  try {
    const isFromNesting = String(request?.remarks || '').toUpperCase().includes('FROM_NESTING');
    const computedPurchaseQty = (item) => {
      const required = parseFloat(item.required_quantity || 0);
      const issued = parseFloat(item.issued_quantity || 0);
      const available = parseFloat(item.available_stock || 0);
      const pending = required - issued;
      const remainingFromSnapshot = Math.max(0, available - issued);
      return Math.max(0, pending - remainingFromSnapshot);
    };
    // Need to purchase: demand not covered by MR snapshot stock still issuable
    let itemsToPurchase = request.items.filter((item) => computedPurchaseQty(item) > 0);

    // If item_ids are provided, filter to only include selected items
    if (item_ids && Array.isArray(item_ids) && item_ids.length > 0) {
      itemsToPurchase = itemsToPurchase.filter(item => item_ids.includes(item.id));
    }

    if (itemsToPurchase.length === 0) {
      throw new Error('No items with quantity to purchase (all demand covered by available stock snapshot)');
    }

    // Generate PR number from Number Format Master (fallback for backward compatibility).
    let prNumber;
    try {
      prNumber = await generateNextNumber(companyId, 'PURCHASE_REQUISITION', request.fiscal_year_id, transaction);
    } catch (err) {
      const today = new Date();
      const year = today.getFullYear();
      const prefix = `PR${year}`;
      const lastPR = await db.PurchaseRequisition.findOne({
        where: {
          company_id: companyId,
          pr_number: { [Op.like]: `${prefix}%` }
        },
        order: [['pr_number', 'DESC']],
        transaction
      });
      let sequence = 1;
      if (lastPR) {
        const lastSequence = parseInt(lastPR.pr_number.slice(-4)) || 0;
        sequence = lastSequence + 1;
      }
      prNumber = `${prefix}${sequence.toString().padStart(4, '0')}`;
    }

    const todayStr = new Date().toISOString().split('T')[0];
    const requiredDate =
      request.productionOrder?.finish_date ||
      request.request_date ||
      todayStr;

    const prRemarksBase = request.productionOrder
      ? `Created from Material Request: ${request.request_number} for Production Order: ${request.productionOrder.production_order_number}`
      : `Created from General Material Request: ${request.request_number}`;
    const prRemarks = isFromNesting ? `${prRemarksBase} | FROM_NESTING` : prRemarksBase;

    // Create Purchase Requisition
    const purchaseRequisition = await db.PurchaseRequisition.create({
      company_id: companyId,
      plant_id: request.plant_id,
      fiscal_year_id: request.fiscal_year_id,
      pr_number: prNumber,
      pr_date: new Date().toISOString().split('T')[0],
      required_date: requiredDate,
      department_id: request.department_id || null,
      requested_by: userId,
      status: 'DRAFT',
      remarks: prRemarks,
      created_by: userId,
      updated_by: userId
    }, { transaction });

    // Create PR items
    for (let i = 0; i < itemsToPurchase.length; i++) {
      const item = itemsToPurchase[i];
      // Get required_date from MR item, fallback to calculated requiredDate
      const itemRequiredDate = item.required_date || requiredDate;
      const nestingSpecText = String(item?.description || item?.remarks || '').trim();
      const mrMaterialNumber =
        item.material_number != null && String(item.material_number).trim()
          ? String(item.material_number).trim().slice(0, 100)
          : '';
      let itemDescription = '';
      if (isFromNesting) {
        if (mrMaterialNumber && nestingSpecText) {
          itemDescription = `${mrMaterialNumber} | ${nestingSpecText}`.slice(0, 500);
        } else if (nestingSpecText) {
          itemDescription = nestingSpecText.slice(0, 500);
        } else {
          itemDescription = (item?.material?.description || '').slice(0, 500);
        }
      } else {
        itemDescription = (item?.material?.description || item?.description || '').slice(0, 500);
      }

      const requiredQty = parseFloat(item.required_quantity || 0);
      const issuedQty = parseFloat(item.issued_quantity || 0);
      const availableSnap = parseFloat(item.available_stock || 0);
      const pendingDemand = Math.max(0, requiredQty - issuedQty);
      const remainingFromSnapshot = Math.max(0, availableSnap - issuedQty);
      const purchaseQty = computedPurchaseQty(item);

      const prMaterialNumber =
        mrMaterialNumber ||
        (item?.material?.material_number
          ? String(item.material.material_number).trim().slice(0, 100)
          : null);

      let lineWeightKg = computeLineWeightKg({
        quantity: purchaseQty,
        line_weight_kg: null,
        material: item.material,
      });
      if (isFromNesting) {
        const nestW = await allocateNestingWeightForMaterialRequestItem(
          db,
          {
            materialRequestId: id,
            materialId: item.material_id,
            purchaseQty,
            itemsToPurchase,
            computedPurchaseQtyFn: computedPurchaseQty,
          },
          { transaction }
        );
        if (nestW != null) lineWeightKg = nestW;
      }

      await db.PurchaseRequisitionItem.create({
        pr_id: purchaseRequisition.id,
        line_number: i + 1,
        material_id: item.material_id,
        material_number: prMaterialNumber,
        description: itemDescription || null,
        quantity: purchaseQty,
        uom_id: item.uom_id,
        unit_price: 0,
        estimated_amount: 0,
        required_date: itemRequiredDate,
        gross_required_quantity: pendingDemand,
        available_stock_at_pr_creation: remainingFromSnapshot,
        line_weight_kg: lineWeightKg,
        remarks: isFromNesting
          ? (request.productionOrder
            ? `FROM_NESTING | MR ${request.request_number} | PO ${request.productionOrder.production_order_number}`
            : `FROM_NESTING | MR ${request.request_number}`)
          : request.productionOrder
            ? `Required for Production Order: ${request.productionOrder.production_order_number}`
            : `Required for General Material Request: ${request.request_number}`
      }, { transaction });
    }

    // Update Material Request
    await request.update({
      status: 'SENT_TO_PURCHASE',
      sent_to_purchase_at: new Date(),
      sent_to_purchase_by: userId,
      purchase_requisition_id: purchaseRequisition.id,
      updated_by: userId
    }, { transaction });

    await transaction.commit();

    const updatedRequest = await db.MaterialRequest.findByPk(id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.ProductionOrder, as: 'productionOrder' },
        { model: db.PurchaseRequisition, as: 'purchaseRequisition' },
        {
          model: db.MaterialRequestItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'Purchase Requisition created and Material Request updated successfully', {
      materialRequest: updatedRequest,
      purchaseRequisition: purchaseRequisition
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

exports.createMaterialIssue = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { item_ids } = req.body; // Optional: array of item IDs to include
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  const request = await db.MaterialRequest.findByPk(id, {
    include: [
      {
        model: db.MaterialRequestItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' },
          { model: db.ProductionOrderComponent, as: 'productionOrderComponent' }
        ]
      },
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'] },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] }
    ]
  });

  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  if (!['REVIEWED', 'SENT_TO_STORES', 'PARTIALLY_ISSUED'].includes(request.status)) {
    return sendError(res, 'Material Request must be REVIEWED, SENT_TO_STORES, or PARTIALLY_ISSUED to create Material Issue', 400);
  }

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

  try {
    // Filter items with available stock (can be issued)
    let itemsToIssue = request.items.filter((item) => {
      const available = parseFloat(item.available_stock || 0);
      const issued = parseFloat(item.issued_quantity || 0);
      const required = parseFloat(item.required_quantity || 0);
      const pending = required - issued;
      const remainingFromSnapshot = Math.max(0, available - issued);
      return pending > 0 && remainingFromSnapshot > 0;
    });

    // If item_ids are provided, filter to only include selected items
    if (item_ids && Array.isArray(item_ids) && item_ids.length > 0) {
      itemsToIssue = itemsToIssue.filter(item => item_ids.includes(item.id));
    }

    if (itemsToIssue.length === 0) {
      throw new Error('No items with available stock to issue');
    }

    // Generate Material Issue number (from Number Format Master)
    let issueNumber;
    try {
      issueNumber = await generateNextNumber(companyId, 'MATERIAL_ISSUE', request.fiscal_year_id, transaction);
    } catch (err) {
      const today = new Date();
      const year = today.getFullYear();
      const prefix = `MI${year}`;
      const lastIssue = await db.MaterialIssue.findOne({
        where: { company_id: companyId, issue_number: { [Op.like]: `${prefix}%` } },
        order: [['issue_number', 'DESC']],
        transaction
      });
      let sequence = 1;
      if (lastIssue) {
        const lastSequence = parseInt(lastIssue.issue_number.slice(-4)) || 0;
        sequence = lastSequence + 1;
      }
      issueNumber = `${prefix}${sequence.toString().padStart(4, '0')}`;
    }

    // Get the user from the requesting department (the user who created the material request)
    // This represents the department that requested the materials
    const issuedToUserId = request.created_by || request.createdByUser?.id || userId;

    // Create Material Issue
    const materialIssue = await db.MaterialIssue.create({
      company_id: companyId,
      plant_id: request.plant_id,
      fiscal_year_id: request.fiscal_year_id,
      issue_number: issueNumber,
      issue_date: new Date().toISOString().split('T')[0],
      production_order_id: request.production_order_id,
      material_request_id: request.id,
      issue_type: 'MANUAL',
      status: 'DRAFT', // Create as DRAFT, user can post later
      remarks: request.productionOrder
        ? `Issued to Department: ${request.department?.department_name || 'N/A'} - Created from Material Request: ${request.request_number} for Production Order: ${request.productionOrder.production_order_number}`
        : `Issued to Department: ${request.department?.department_name || 'N/A'} - Created from General Material Request: ${request.request_number}`,
      created_by: userId, // Stores user creating the issue
      updated_by: userId,
      issued_by: userId, // Stores user issuing the materials
      issued_to: issuedToUserId // Department user receiving the materials
    }, { transaction });

    // Create Material Issue Items and update Material Request Items
    for (let i = 0; i < itemsToIssue.length; i++) {
      const item = itemsToIssue[i];
      const available = parseFloat(item.available_stock || 0);
      const issued = parseFloat(item.issued_quantity || 0);
      const required = parseFloat(item.required_quantity || 0);
      const pending = required - issued;
      const remainingFromSnapshot = Math.max(0, available - issued);

      // Issue quantity = min(pending, stock still issuable per MR snapshot after prior issues)
      const issueQty = Math.min(remainingFromSnapshot, pending);
      
      // Get material unit price from material master or production order component
      let unitPrice = 0;
      if (item.productionOrderComponent) {
        unitPrice = parseFloat(item.productionOrderComponent.unit_cost || 0);
      } else if (item.material) {
        unitPrice = parseFloat(item.material.standard_price || item.material.average_price || 0);
      }
      const totalAmount = issueQty * unitPrice;

      // Create Material Issue Item
      await db.MaterialIssueItem.create({
        issue_id: materialIssue.id,
        production_order_component_id: item.production_order_component_id || null,
        line_number: i + 1,
        material_id: item.material_id,
        description: item.material.description || '',
        quantity: issueQty,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        total_amount: totalAmount,
        batch_number: null,
        storage_location: item.storage_location || null,
        remarks: `Issued from Material Request: ${request.request_number}`
      }, { transaction });

      // Update Material Request Item with issued quantity
      const newIssuedQty = issued + issueQty;
      const newPendingQty = required - newIssuedQty;
      
      await item.update({
        issued_quantity: newIssuedQty,
        pending_quantity: Math.max(0, newPendingQty),
        updated_by: userId
      }, { transaction });

      // Update Production Order Component issued quantity if exists
      if (item.production_order_component_id) {
        const poComponent = await db.ProductionOrderComponent.findByPk(item.production_order_component_id, { transaction });
        if (poComponent) {
          const poIssuedQty = parseFloat(poComponent.issued_quantity || 0) + issueQty;
          await poComponent.update({ 
            issued_quantity: poIssuedQty 
          }, { transaction });
        }
      }
    }

    // Check if all items are fully issued
    const allItems = await db.MaterialRequestItem.findAll({
      where: { request_id: id },
      transaction
    });
    
    const allIssued = allItems.every(item => {
      const required = parseFloat(item.required_quantity || 0);
      const issued = parseFloat(item.issued_quantity || 0);
      return issued >= required;
    });

    const partiallyIssued = allItems.some(item => {
      const required = parseFloat(item.required_quantity || 0);
      const issued = parseFloat(item.issued_quantity || 0);
      return issued > 0 && issued < required;
    });

    // Update Material Request status
    let newStatus = request.status;
    if (allIssued) {
      newStatus = 'COMPLETED';
    } else if (partiallyIssued || request.status === 'REVIEWED' || request.status === 'SENT_TO_STORES') {
      newStatus = 'PARTIALLY_ISSUED';
    }

    await request.update({
      status: newStatus,
      updated_by: userId
    }, { transaction });

    await transaction.commit();

    // Fetch created Material Issue with all relations
    const createdIssue = await db.MaterialIssue.findByPk(materialIssue.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.ProductionOrder, as: 'productionOrder' },
        { model: db.MaterialRequest, as: 'materialRequest' },
        {
          model: db.MaterialIssueItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    const updatedRequest = await db.MaterialRequest.findByPk(id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'] },
        { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'] },
        { model: db.ProductionOrder, as: 'productionOrder' },
        {
          model: db.MaterialRequestItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    // SLA and receipt notifications
    try {
      const userIdsToNotify = new Set();
      if (request.created_by) userIdsToNotify.add(request.created_by); // requesting user/department representative
      if (request.sent_to_stores_by) userIdsToNotify.add(request.sent_to_stores_by);
      if (userId) userIdsToNotify.add(userId); // stores user performing issue

      const link = `/stores/materialrequest/${request.id}/view`;
      const issuedToday = itemsToIssue.filter((it) => {
        const available = parseFloat(it.available_stock || 0);
        const issued = parseFloat(it.issued_quantity || 0);
        const required = parseFloat(it.required_quantity || 0);
        const pending = required - issued;
        const issueQty = Math.min(available, pending);
        return issueQty > 0;
      });

      const issuedOnTime = issuedToday.filter((it) => !isPastRequiredDate(it.required_date));
      const issuedLate = issuedToday.filter((it) => isPastRequiredDate(it.required_date));

      if (issuedOnTime.length > 0) {
        const preview = issuedOnTime.slice(0, 5).map((it) => getMaterialDisplay(it)).join(', ');
        await Promise.all(
          Array.from(userIdsToNotify).map((notifyUserId) =>
            notificationService.createNotification({
              companyId: request.company_id,
              userId: notifyUserId,
              type: 'INFO',
              moduleName: 'MaterialRequest',
              transactionId: request.id,
              transactionNumber: request.request_number,
              title: 'Materials Received Within Timeline',
              message: `Material Request ${request.request_number}: ${issuedOnTime.length} material(s) issued/received within required timeline. ${preview}${issuedOnTime.length > 5 ? '...' : ''}`,
              link,
              priority: 'MEDIUM',
              createdBy: userId
            })
          )
        );
      }

      if (issuedLate.length > 0) {
        const preview = issuedLate.slice(0, 5).map((it) => getMaterialDisplay(it)).join(', ');
        await Promise.all(
          Array.from(userIdsToNotify).map((notifyUserId) =>
            notificationService.createNotification({
              companyId: request.company_id,
              userId: notifyUserId,
              type: 'WARNING',
              moduleName: 'MaterialRequest',
              transactionId: request.id,
              transactionNumber: request.request_number,
              title: 'Materials Issued After Time Limit',
              message: `Material Request ${request.request_number}: ${issuedLate.length} material(s) were issued after required date. ${preview}${issuedLate.length > 5 ? '...' : ''}`,
              link,
              priority: 'HIGH',
              createdBy: userId
            })
          )
        );
      }

      const overduePending = (updatedRequest?.items || []).filter((it) => {
        const required = parseFloat(it.required_quantity || 0);
        const issued = parseFloat(it.issued_quantity || 0);
        const pending = Math.max(0, required - issued);
        return pending > 0 && isPastRequiredDate(it.required_date);
      });
      if (overduePending.length > 0) {
        const preview = overduePending.slice(0, 5).map((it) => getMaterialDisplay(it)).join(', ');
        await Promise.all(
          Array.from(userIdsToNotify).map((notifyUserId) =>
            notificationService.createNotification({
              companyId: request.company_id,
              userId: notifyUserId,
              type: 'WARNING',
              moduleName: 'MaterialRequest',
              transactionId: request.id,
              transactionNumber: request.request_number,
              title: 'Pending Materials Reached Time Limit',
              message: `Material Request ${request.request_number}: ${overduePending.length} pending material(s) exceeded required date and need immediate action. ${preview}${overduePending.length > 5 ? '...' : ''}`,
              link,
              priority: 'HIGH',
              createdBy: userId
            })
          )
        );
      }
    } catch (notifyError) {
      console.error('Failed to create Material Request SLA notifications:', notifyError.message);
    }

    sendResponse(res, 201, true, 'Material Issue created and Material Request updated successfully', {
      materialRequest: updatedRequest,
      materialIssue: createdIssue
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

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

  const request = await db.MaterialRequest.findByPk(id);
  if (!request) {
    return sendError(res, 'Material Request not found', 404);
  }

  if (request.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Material Requests can be deleted', 400);
  }

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

  try {
    await db.MaterialRequestItem.destroy({
      where: { request_id: id },
      transaction
    });

    await request.destroy({ transaction });
    await transaction.commit();

    sendResponse(res, 200, true, 'Material Request deleted successfully');
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

