const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const mrpService = require('../services/mrpService');
const notificationService = require('../services/notificationService');
const numberFormatService = require('../services/numberFormatService');
const atpService = require('../services/atpService');
const { Op } = require('sequelize');
const { computeLineWeightKg } = require('../utils/lineWeightKg');

/**
 * Run MRP calculation
 * POST /production/mrp/run
 * Body: { customerPOIds: [], productionOrderIds: [], planningDate: '2026-01-01', useNestingConfirmedQty?: boolean }
 */
exports.runMRP = asyncHandler(async (req, res) => {
  const { customerPOIds, productionOrderIds, planningDate, useNestingConfirmedQty } = req.body;
  const companyId = req.user?.companyId;
  const plantId = req.body.plantId || req.query.plantId;

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

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

  if ((!customerPOIds || customerPOIds.length === 0) && 
      (!productionOrderIds || productionOrderIds.length === 0)) {
    return sendError(res, 'At least one Customer PO ID or Production Order ID is required', 400);
  }

  try {
    const planningDateObj = planningDate ? new Date(planningDate) : new Date();
    
    const mrpResult = await mrpService.runMRP({
      companyId,
      plantId,
      customerPOIds: customerPOIds || [],
      productionOrderIds: productionOrderIds || [],
      useNestingConfirmedQty: Boolean(useNestingConfirmedQty),
      planningDate: planningDateObj
    });

    sendResponse(res, 200, true, 'MRP calculation completed successfully', mrpResult);
  } catch (error) {
    console.error('MRP calculation error:', error);
    sendError(res, error.message || 'Failed to run MRP calculation', 500);
  }
});

/**
 * Get MRP results for Customer PO
 * GET /production/mrp/customer-po/:id
 */
exports.getMRPForCustomerPO = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const companyId = req.user?.companyId;
  const plantId = req.query.plantId;

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

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

  try {
    const mrpResult = await mrpService.runMRP({
      companyId,
      plantId,
      customerPOIds: [parseInt(id)],
      productionOrderIds: [],
      planningDate: new Date()
    });

    sendResponse(res, 200, true, 'MRP results retrieved successfully', mrpResult);
  } catch (error) {
    console.error('MRP calculation error:', error);
    sendError(res, error.message || 'Failed to calculate MRP', 500);
  }
});

/**
 * Get MRP results for Production Order
 * GET /production/mrp/production-order/:id
 */
exports.getMRPForProductionOrder = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const companyId = req.user?.companyId;
  const plantId = req.query.plantId;

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

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

  try {
    const mrpResult = await mrpService.runMRP({
      companyId,
      plantId,
      customerPOIds: [],
      productionOrderIds: [parseInt(id)],
      planningDate: new Date()
    });

    sendResponse(res, 200, true, 'MRP results retrieved successfully', mrpResult);
  } catch (error) {
    console.error('MRP calculation error:', error);
    sendError(res, error.message || 'Failed to calculate MRP', 500);
  }
});

/**
 * Get MRP shortages only
 * GET /production/mrp/shortages
 * Query: customerPOIds, productionOrderIds, plantId
 */
exports.getMRPShortages = asyncHandler(async (req, res) => {
  const { customerPOIds, productionOrderIds } = req.query;
  const companyId = req.user?.companyId;
  const plantId = req.query.plantId;

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

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

  try {
    const customerPOIdsArray = customerPOIds 
      ? (Array.isArray(customerPOIds) ? customerPOIds : customerPOIds.split(',').map(id => parseInt(id.trim())))
      : [];
    
    const productionOrderIdsArray = productionOrderIds
      ? (Array.isArray(productionOrderIds) ? productionOrderIds : productionOrderIds.split(',').map(id => parseInt(id.trim())))
      : [];

    const mrpResult = await mrpService.runMRP({
      companyId,
      plantId,
      customerPOIds: customerPOIdsArray,
      productionOrderIds: productionOrderIdsArray,
      planningDate: new Date()
    });

    sendResponse(res, 200, true, 'MRP shortages retrieved successfully', {
      shortages: mrpResult.shortages,
      summary: mrpResult.summary
    });
  } catch (error) {
    console.error('MRP calculation error:', error);
    sendError(res, error.message || 'Failed to calculate MRP shortages', 500);
  }
});

/**
 * Create Purchase Requisitions from MRP shortages
 * POST /production/mrp/create-pr
 * Body: { materialIds: [], customerPOIds: [], productionOrderIds: [], plantId }
 */
exports.createPRFromMRP = asyncHandler(async (req, res) => {
  const { materialIds, customerPOIds, productionOrderIds, plantId, fiscalYearId } = req.body;
  const companyId = req.user?.companyId;
  const userId = req.user?.userId || null;

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

  if (!plantId || !fiscalYearId) {
    return sendError(res, 'Plant ID and Fiscal Year ID are required', 400);
  }

  if (!materialIds || materialIds.length === 0) {
    return sendError(res, 'At least one material ID is required', 400);
  }

  try {
    // Run MRP to get requirements
    const mrpResult = await mrpService.runMRP({
      companyId,
      plantId,
      customerPOIds: customerPOIds || [],
      productionOrderIds: productionOrderIds || [],
      planningDate: new Date()
    });

    // Filter for selected materials that have shortages
    const selectedShortages = mrpResult.shortages.filter(s => 
      materialIds.includes(s.material_id) && s.is_shortage
    );

    if (selectedShortages.length === 0) {
      return sendError(res, 'No shortages found for selected materials', 400);
    }

    // Create Purchase Requisition
    const transaction = await db.sequelize.transaction();

    try {
      // Get user's department_id for the Purchase Requisition
      let departmentId = null;
      if (userId) {
        const user = await db.User.findByPk(userId, {
          attributes: ['id', 'department_id'],
          transaction
        });
        departmentId = user?.department_id || null;
      }

      // Generate PR number
      const today = new Date();
      const year = today.getFullYear();
      let prNumber;
      try {
        prNumber = await numberFormatService.generateNextNumber(companyId, 'PURCHASE_REQUISITION', fiscalYearId, transaction);
      } catch (err) {
        const year = new Date().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')}`;
      }

      // Create PR
      const pr = await db.PurchaseRequisition.create({
        company_id: companyId,
        plant_id: plantId,
        fiscal_year_id: fiscalYearId,
        department_id: departmentId,
        pr_number: prNumber,
        pr_date: today,
        status: 'DRAFT',
        remarks: `Auto-generated from MRP calculation`,
        created_by: userId,
        updated_by: userId,
        requested_by: userId
      }, { transaction });

      // Create PR items
      const prItems = [];
      for (const shortage of selectedShortages) {
        const material = await db.Material.findByPk(shortage.material_id, {
          include: [{ model: db.UOM, as: 'baseUOM' }]
        });

        if (!material) continue;

        // Store net requirement (balance_quantity) as the quantity to purchase
        // Also store gross requirement and available stock for reference
        const prItem = await db.PurchaseRequisitionItem.create({
          pr_id: pr.id,
          line_number: prItems.length + 1,
          material_id: shortage.material_id,
          material_number: material.material_number
            ? String(material.material_number).trim().slice(0, 100)
            : null,
          description: material.description ? String(material.description).slice(0, 500) : null,
          quantity: shortage.balance_quantity, // Net requirement (required - available)
          gross_required_quantity: shortage.required_quantity, // Gross requirement for reference
          available_stock_at_pr_creation: shortage.available_stock, // Available stock at PR creation
          uom_id: shortage.uom_id || material.base_uom_id,
          required_date: new Date(), // Can be enhanced to use delivery dates
          line_weight_kg: computeLineWeightKg({
            quantity: shortage.balance_quantity,
            line_weight_kg: null,
            material,
          }),
          remarks: `MRP: Gross Required ${shortage.required_quantity}, Available ${shortage.available_stock}, Net Required (PR Qty) ${shortage.balance_quantity}`
        }, { transaction });

        prItems.push(prItem);
      }

      await transaction.commit();

      const createdPR = await db.PurchaseRequisition.findByPk(pr.id, {
        include: [
          { model: db.Company, as: 'company' },
          { model: db.Plant, as: 'plant' },
          { model: db.FiscalYear, as: 'fiscalYear' },
          { model: db.Department, as: 'department' },
          {
            model: db.PurchaseRequisitionItem,
            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'] },
          { model: db.User, as: 'requestedByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] }
        ]
      });

      // Send notifications to stores users
      try {
        // Find users with stores-related roles
        const storesUserRoles = await db.UserRole.findAll({
          include: [
            {
              model: db.Role,
              as: 'role',
              where: {
                company_id: companyId,
                status: 'ACTIVE',
                [Op.or]: [
                  { role_code: { [Op.like]: '%STORES%' } },
                  { role_code: { [Op.like]: '%STORE%' } },
                  { role_name: { [Op.like]: '%Stores%' } },
                  { role_name: { [Op.like]: '%Store%' } },
                  { role_name: { [Op.like]: '%STORES%' } }
                ]
              },
              required: true
            },
            {
              model: db.User,
              as: 'user',
              where: { status: 'ACTIVE', is_active: true },
              required: true
            }
          ]
        });

        const userIdsToNotify = new Set();
        
        // Add creator to notifications
        if (userId) {
          userIdsToNotify.add(userId);
        }

        // Add stores users
        storesUserRoles.forEach(userRole => {
          if (userRole.user && userRole.user.id) {
            userIdsToNotify.add(userRole.user.id);
          }
        });

        // Create notification for each user
        const notificationPromises = Array.from(userIdsToNotify).map(userIdToNotify => {
          return notificationService.createNotification({
            companyId,
            userId: userIdToNotify,
            type: 'INFO',
            moduleName: 'PurchaseRequisition',
            transactionId: pr.id,
            transactionNumber: prNumber,
            title: 'Purchase Requisition Created from MRP',
            message: `Purchase Requisition ${prNumber} has been created from MRP calculation with ${prItems.length} item(s). Please review and process.`,
            link: `/purchase/purchaserequisition/${pr.id}/view`,
            priority: 'HIGH',
            createdBy: userId
          });
        });

        await Promise.all(notificationPromises);
      } catch (notificationError) {
        // Log error but don't fail the request
        console.error('Error creating notifications for Purchase Requisition from MRP:', notificationError);
      }

      sendResponse(res, 201, true, `Purchase Requisition created successfully with ${prItems.length} items`, createdPR);
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  } catch (error) {
    console.error('Error creating PR from MRP:', error);
    sendError(res, error.message || 'Failed to create Purchase Requisition from MRP', 500);
  }
});

/**
 * Check which materials already have Purchase Requisitions
 * GET /production/mrp/check-sent-materials
 * Query: productionOrderIds, customerPOIds, plantId
 */
exports.checkSentMaterials = asyncHandler(async (req, res) => {
  const { productionOrderIds, customerPOIds, plantId } = req.query;
  const companyId = req.user?.companyId;

  if (!companyId || !plantId) {
    return sendError(res, 'Company ID and Plant ID are required', 400);
  }

  try {
    const productionOrderIdsArray = productionOrderIds
      ? (Array.isArray(productionOrderIds) ? productionOrderIds : productionOrderIds.split(',').map(id => parseInt(id.trim())))
      : [];
    
    const customerPOIdsArray = customerPOIds
      ? (Array.isArray(customerPOIds) ? customerPOIds : customerPOIds.split(',').map(id => parseInt(id.trim())))
      : [];

    // Find Material Requests that were created from MRP for the same Production Orders/Customer POs
    // These materials have already been sent to stores
    const materialRequests = await db.MaterialRequest.findAll({
      where: {
        company_id: companyId,
        plant_id: parseInt(plantId),
        remarks: { [Op.like]: '%Created from MRP%' },
        ...(productionOrderIdsArray.length > 0 ? {
          production_order_id: { [Op.in]: productionOrderIdsArray }
        } : {})
      },
      include: [
        {
          model: db.MaterialRequestItem,
          as: 'items',
          attributes: ['material_id']
        }
      ]
    });

    // Also check for Purchase Requisitions created from MRP (legacy check)
    const mrpPRs = await db.PurchaseRequisition.findAll({
      where: {
        company_id: companyId,
        plant_id: parseInt(plantId),
        remarks: { [Op.like]: '%Auto-generated from MRP%' }
      },
      include: [
        {
          model: db.PurchaseRequisitionItem,
          as: 'items',
          attributes: ['material_id']
        }
      ]
    });

    // Get all material IDs that already have Material Requests or PRs
    const sentMaterialIds = new Set();
    
    // Add materials from Material Requests (new flow)
    materialRequests.forEach(mr => {
      if (mr.items) {
        mr.items.forEach(item => {
          sentMaterialIds.add(item.material_id);
        });
      }
    });
    
    // Add materials from Purchase Requisitions (legacy/backup check)
    mrpPRs.forEach(pr => {
      if (pr.items) {
        pr.items.forEach(item => {
          sentMaterialIds.add(item.material_id);
        });
      }
    });

    sendResponse(res, 200, true, 'Sent materials retrieved successfully', {
      sentMaterialIds: Array.from(sentMaterialIds)
    });
  } catch (error) {
    console.error('Error checking sent materials:', error);
    sendError(res, error.message || 'Failed to check sent materials', 500);
  }
});

/**
 * Create Material Request to Stores from MRP (for ALL shortages - available + need purchase)
 * POST /production/mrp/create-material-request
 * Body: { materialIds: [], productionOrderIds: [], customerPOIds: [], plantId, fiscalYearId }
 * 
 * This creates a Material Request that includes ALL selected shortages:
 * - Materials with available stock (can be issued immediately by stores)
 * - Materials needing purchase (stores will create PR for these)
 */
exports.createMaterialRequestFromMRP = asyncHandler(async (req, res) => {
  const {
    materialIds,
    productionOrderIds,
    customerPOIds,
    plantId,
    fiscalYearId,
    required_date,
    requiredQtyOverrides,
    nesting_raw_material_texts,
    reference_production_no,
    request_remarks
  } = req.body;
  const isFromNesting = String(request_remarks || '').toUpperCase().includes('FROM_NESTING');
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

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

  if (!plantId || !fiscalYearId) {
    return sendError(res, 'Plant ID and Fiscal Year ID are required', 400);
  }

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

  if ((!productionOrderIds || productionOrderIds.length === 0) && (!customerPOIds || customerPOIds.length === 0)) {
    return sendError(res, 'At least one Production Order ID or Customer PO ID is required', 400);
  }

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

  try {
    // Get user's department_id for the Material Request (MRP team's department)
    let departmentId = null;
    if (userId) {
      const user = await db.User.findByPk(userId, {
        attributes: ['id', 'department_id'],
        transaction
      });
      departmentId = user?.department_id || null;
    }

    // Nesting-originated MR: prefer a dedicated "Nesting" department when configured
    if (isFromNesting) {
      const nestingDept = await db.Department.findOne({
        where: {
          company_id: companyId,
          status: 'ACTIVE',
          [Op.or]: [
            { department_code: { [Op.like]: 'NEST%' } },
            { department_name: { [Op.like]: '%Nesting%' } },
            { department_name: { [Op.like]: '%nesting%' } }
          ]
        },
        order: [['id', 'ASC']],
        transaction
      });
      if (nestingDept?.id) {
        departmentId = nestingDept.id;
      }
    }

    let selectedMaterials = [];

    if (!isFromNesting) {
      const mrpResult = await mrpService.runMRP({
        companyId,
        plantId,
        customerPOIds: customerPOIds || [],
        productionOrderIds: productionOrderIds || [],
        planningDate: new Date()
      });

      if (!mrpResult) {
        await transaction.rollback();
        return sendError(res, 'Failed to calculate MRP results', 500);
      }

      const mrpResults = Array.isArray(mrpResult.mrpResults) ? mrpResult.mrpResults : [];
      selectedMaterials = mrpResults.filter((item) => materialIds.includes(item.material_id));
    } else {
      // Nesting → Material Master sheet SKUs; they are not required to appear on MRP explosion lines.
      const idSet = new Set(
        materialIds
          .map((id) => parseInt(id, 10))
          .filter((id) => Number.isFinite(id) && id > 0)
      );
      const ids = Array.from(idSet);
      const materialsFromMaster = await db.Material.findAll({
        where: { id: { [Op.in]: ids }, company_id: companyId },
        attributes: ['id', 'description', 'base_uom_id', 'material_number'],
        transaction
      });
      const byId = new Map(materialsFromMaster.map((m) => [m.id, m]));
      selectedMaterials = ids
        .filter((id) => byId.has(id))
        .map((id) => {
          const m = byId.get(id);
          const key = String(id);
          const reqOv =
            requiredQtyOverrides && Object.prototype.hasOwnProperty.call(requiredQtyOverrides, key)
              ? parseFloat(requiredQtyOverrides[key])
              : 0;
          return {
            material_id: id,
            material_description: m.description || '',
            uom_id: m.base_uom_id || null,
            required_quantity: Number.isFinite(reqOv) ? reqOv : 0
          };
        })
        .filter((row) => row.required_quantity > 0);
    }

    // Nesting send: prefer quantity UOM from each nesting_stores_lines row (matches dialog / sheet group).
    if (isFromNesting && Array.isArray(req.body.nesting_stores_lines) && req.body.nesting_stores_lines.length > 0) {
      const uomByMaterialId = new Map();
      for (const line of req.body.nesting_stores_lines) {
        const mid = parseInt(line.material_id, 10);
        if (!Number.isFinite(mid) || mid <= 0) continue;
        const qu = parseInt(line.quantity_uom_id, 10);
        if (!Number.isFinite(qu) || qu <= 0) continue;
        if (!uomByMaterialId.has(mid)) uomByMaterialId.set(mid, qu);
      }
      if (uomByMaterialId.size > 0) {
        selectedMaterials = selectedMaterials.map((sm) => {
          const fromLine = uomByMaterialId.get(sm.material_id);
          return fromLine != null ? { ...sm, uom_id: fromLine } : sm;
        });
      }
    }

    if (selectedMaterials.length === 0) {
      await transaction.rollback();
      return sendError(
        res,
        isFromNesting
          ? 'No materials found in Material Master for the selected IDs, or required quantity is zero.'
          : 'No materials found for the selected material IDs in MRP results.',
        400
      );
    }

    // Get Production Order(s) - use first one if multiple
    // Material Request can be linked to one Production Order or created per PO
    let productionOrderId = null;
    let productionOrder = null;
    let customerPO = null;
    let requiredDate = null; // computed default (PO finish_date or Customer PO delivery_date)
    
    if (productionOrderIds && productionOrderIds.length > 0) {
      productionOrderId = productionOrderIds[0];
      productionOrder = await db.ProductionOrder.findByPk(productionOrderId, {
        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');
      }
      
      // Get required_date from Production Order finish_date
      if (productionOrder.finish_date) {
        requiredDate = productionOrder.finish_date;
      }
    }
    
    // If no Production Order or no finish_date, try Customer PO
    if (!requiredDate && customerPOIds && customerPOIds.length > 0) {
      const customerPOId = customerPOIds[0];
      customerPO = await db.CustomerPO.findByPk(customerPOId, {
        attributes: ['id', 'delivery_date'],
        transaction
      });
      
      if (customerPO && customerPO.delivery_date) {
        requiredDate = customerPO.delivery_date;
      }
    }
    
    // Fallback to today if no date found
    if (!requiredDate) requiredDate = new Date();

    // Required date override from UI (mandatory in frontend)
    // Accepts DATEONLY string "YYYY-MM-DD" or Date-like.
    const requiredDateToUse = required_date ? new Date(required_date) : new Date(requiredDate);
    if (Number.isNaN(requiredDateToUse.getTime())) {
      await transaction.rollback();
      return sendError(res, 'Invalid required_date. Please provide a valid date (YYYY-MM-DD).', 400);
    }

    // Generate material request number (Number Format Master: company code + FY reset)
    let requestNumber;
    try {
      requestNumber = await numberFormatService.generateNextNumber(companyId, 'MATERIAL_REQUEST', fiscalYearId, transaction);
    } catch (err) {
      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
      });
      let sequence = 1;
      if (lastRequest) {
        const lastSequence = parseInt(lastRequest.request_number.slice(-4)) || 0;
        sequence = lastSequence + 1;
      }
      requestNumber = `${prefix}${sequence.toString().padStart(4, '0')}`;
    }
    const today = new Date();

    // Create Material Request - Status is set to SENT_TO_STORES since it's coming from MRP (already sent to stores)
    const referenceText = reference_production_no ? `Ref Production No: ${reference_production_no}. ` : '';
    const customRemarks = request_remarks ? `${request_remarks}. ` : '';
    const materialRequest = await db.MaterialRequest.create({
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      production_order_id: productionOrderId,
      department_id: departmentId,
      request_number: requestNumber,
      request_date: today,
      required_date: requiredDateToUse,
      status: 'SENT_TO_STORES', // Already sent from MRP team to Stores
      remarks: isFromNesting
        ? `${referenceText}${customRemarks}Created from Nesting — Material Request to Stores for ${selectedMaterials.length} material(s) (Material Master).`
        : `${referenceText}${customRemarks}Created from MRP - Material Request to Stores for ${selectedMaterials.length} selected material(s). Stores will create PR for materials needing purchase.`,
      created_by: userId,
      updated_by: userId
    }, { transaction });

    const selectedMaterialIds = [
      ...new Set(selectedMaterials.map((m) => m.material_id).filter((id) => id != null))
    ];

    const materialPlants = await db.MaterialPlant.findAll({
      where: {
        material_id: { [Op.in]: selectedMaterialIds },
        plant_id: plantId,
        status: 'ACTIVE'
      },
      attributes: ['material_id', 'storage_location'],
      transaction
    });

    const materialsWithStorage = await db.Material.findAll({
      where: { id: { [Op.in]: selectedMaterialIds } },
      attributes: ['id', 'storage_location_id', 'material_number'],
      include: [
        {
          model: db.StorageLocation,
          as: 'storageLocation',
          attributes: ['storage_location_code'],
          required: false
        }
      ],
      transaction
    });

    const plantStorageByMaterialId = new Map();
    materialPlants.forEach((mp) => {
      const code = mp.storage_location && String(mp.storage_location).trim();
      if (code) plantStorageByMaterialId.set(mp.material_id, code);
    });

    const materialFallbackStorageById = new Map();
    const materialMasterNumberById = new Map();
    materialsWithStorage.forEach((m) => {
      const code =
        m.storageLocation?.storage_location_code && String(m.storageLocation.storage_location_code).trim();
      if (code) materialFallbackStorageById.set(m.id, code);
      const mn = m.material_number != null ? String(m.material_number).trim() : '';
      if (mn) materialMasterNumberById.set(m.id, mn.slice(0, 100));
    });

    const resolveDefaultStorage = (materialId) =>
      plantStorageByMaterialId.get(materialId) || materialFallbackStorageById.get(materialId) || null;

    const materialNumberFromNestingLines = new Map();
    if (Array.isArray(req.body.nesting_stores_lines)) {
      for (const line of req.body.nesting_stores_lines) {
        const mid = parseInt(line.material_id, 10);
        if (!Number.isFinite(mid) || mid <= 0) continue;
        const raw = line.material_number != null ? String(line.material_number).trim() : '';
        if (raw && !materialNumberFromNestingLines.has(mid)) {
          materialNumberFromNestingLines.set(mid, raw.slice(0, 100));
        }
      }
    }

    const sliceMaterialNumber = (s) => {
      const t = s != null ? String(s).trim() : '';
      return t ? t.slice(0, 100) : null;
    };

    const atpCache = new Map();
    const getAvailableAtLocation = async (materialId, storageLocationCode) => {
      const key = `${materialId}__${storageLocationCode || ''}`;
      if (atpCache.has(key)) return atpCache.get(key);
      try {
        const stockInfo = await atpService.getAvailableStock(
          materialId,
          plantId,
          storageLocationCode || null
        );
        const av = parseFloat(stockInfo?.available || 0);
        atpCache.set(key, av);
        return av;
      } catch (e) {
        atpCache.set(key, 0);
        return 0;
      }
    };

    let cachedFallbackUomId = null;
    const resolveFinalUomId = async (materialId, preferredUomId) => {
      let uid = preferredUomId || null;
      if (!uid && materialId) {
        const m = await db.Material.findByPk(materialId, {
          attributes: ['base_uom_id'],
          transaction
        });
        uid = m?.base_uom_id || null;
      }
      if (!uid && !cachedFallbackUomId) {
        const u = await db.UOM.findOne({
          where: {
            status: 'ACTIVE',
            uom_code: { [Op.in]: ['PC', 'PCS', 'NOS', 'EA', 'UNIT'] }
          },
          order: [['id', 'ASC']],
          transaction
        });
        cachedFallbackUomId = u?.id || null;
      }
      if (!uid) uid = cachedFallbackUomId;
      return uid || null;
    };

    // Create Material Request Items for ALL selected materials (available + shortage)
    let lineNumber = 1;
    for (const material of selectedMaterials) {
      // Try to find corresponding production order component if PO exists
      let component = null;
      let uomId = material.uom_id;
      let storageLocation = null;
      let materialDescription = material.material_description || '';
      
      if (productionOrder && productionOrder.components) {
        component = productionOrder.components.find(comp => 
          comp.material_id === material.material_id
        );
        
        if (component) {
          // Nesting MR uses Material Master SKUs: keep master UOM when BOM line is a different material.
          if (isFromNesting) {
            uomId = material.uom_id || component.uom_id;
          } else {
            uomId = component.uom_id || material.uom_id;
          }
          storageLocation = component.storage_location || null;
          materialDescription = component.material?.description || material.material_description || '';
        }
      }

      const defaultMrpStore = resolveDefaultStorage(material.material_id);
      const componentStore =
        storageLocation && String(storageLocation).trim() ? String(storageLocation).trim() : null;
      const itemStorageLocation = componentStore || defaultMrpStore || null;

      // If component not found, get material details directly
      if (!materialDescription) {
        const materialRecord = await db.Material.findByPk(material.material_id, {
          attributes: ['id', 'material_number', 'description', 'base_uom_id'],
          transaction
        });
        if (materialRecord) {
          materialDescription = materialRecord.description || '';
          if (!uomId) {
            uomId = materialRecord.base_uom_id;
          }
        }
      }

      uomId = await resolveFinalUomId(material.material_id, uomId);
      if (!uomId) {
        await transaction.rollback();
        return sendError(
          res,
          'No unit of measure: set Base UOM on the material in Material Master, or add an active PC/PCS/NOS/EA/UNIT UOM.',
          400
        );
      }

      // Calculate quantities
      // For shortage materials only (already filtered above):
      // - required_quantity: Total quantity required
      // - available_stock: Current available stock
      // - shortfall_quantity: Balance quantity (required - available), always > 0 for shortage materials
      // - pending_quantity: Shortfall quantity (what needs to be procured/issued)
      const requestedOverride = requiredQtyOverrides && Object.prototype.hasOwnProperty.call(requiredQtyOverrides, String(material.material_id))
        ? parseFloat(requiredQtyOverrides[String(material.material_id)])
        : undefined;
      const requiredQtyBase = parseFloat(material.required_quantity || 0);
      const requiredQty = Number.isFinite(requestedOverride) ? requestedOverride : requiredQtyBase;
      // Skip reference/no-pending rows (e.g., fully issued components included only for visibility)
      if (requiredQty <= 0) {
        continue;
      }
      // ATP at MRP default store (or component store when set) — matches Stores MR "Issue vs PR" split
      const availableStock = await getAvailableAtLocation(material.material_id, itemStorageLocation);
      const shortfallQty = Math.max(0, requiredQty - availableStock);
      const pendingQty = requiredQty;

      const nestingRawKey = String(material.material_id);
      const nestingRawLine =
        isFromNesting &&
        nesting_raw_material_texts &&
        Object.prototype.hasOwnProperty.call(nesting_raw_material_texts, nestingRawKey)
          ? String(nesting_raw_material_texts[nestingRawKey] || '').trim()
          : '';

      const defaultMrpRemarks = `${reference_production_no ? `Ref ${reference_production_no}. ` : ''}MRP: Required ${requiredQty}, Available ${availableStock}, Shortage ${shortfallQty}. Requires purchase/issue for shortage quantity.`;

      let itemDescription = (materialDescription || '').trim();
      let itemRemarks = defaultMrpRemarks;

      let itemMaterialNumber = sliceMaterialNumber(
        materialNumberFromNestingLines.get(material.material_id)
      );

      if (nestingRawLine) {
        const pipeIdx = nestingRawLine.indexOf(' | ');
        if (pipeIdx >= 0) {
          if (!itemMaterialNumber) {
            itemMaterialNumber = sliceMaterialNumber(nestingRawLine.slice(0, pipeIdx));
          }
          itemDescription = nestingRawLine.slice(pipeIdx + 3).trim().slice(0, 500);
        } else {
          itemDescription = nestingRawLine.slice(0, 500);
        }
        itemRemarks = null;
      }

      if (!itemMaterialNumber) {
        itemMaterialNumber = materialMasterNumberById.get(material.material_id) || null;
      }

      await db.MaterialRequestItem.create({
        request_id: materialRequest.id,
        production_order_component_id: component?.id || null,
        line_number: lineNumber++,
        material_id: material.material_id,
        material_number: itemMaterialNumber,
        description: itemDescription || null,
        required_quantity: requiredQty,
        uom_id: uomId,
        available_stock: availableStock,
        shortfall_quantity: shortfallQty,
        issued_quantity: 0,
        pending_quantity: pendingQty,
        storage_location: itemStorageLocation,
        required_date: requiredDateToUse,
        remarks: itemRemarks
      }, { transaction });
    }

    // If everything was skipped (all selected were no-pending), stop with a clear error
    const createdItemsCount = await db.MaterialRequestItem.count({
      where: { request_id: materialRequest.id },
      transaction
    });
    if (createdItemsCount === 0) {
      await transaction.rollback();
      return sendError(res, 'Selected materials have no pending requirement (required qty is 0). Material Request not created.', 400);
    }

    if (isFromNesting && productionOrderId) {
      const poLog = await db.ProductionOrder.findByPk(productionOrderId, {
        attributes: ['id', 'production_order_number', 'order_quantity', 'status', 'customer_po_id'],
        include: [
          {
            model: db.CustomerPO,
            as: 'customerPO',
            attributes: ['id', 'po_number'],
            required: false,
            include: [{ model: db.BusinessPartner, as: 'customer', attributes: ['name'], required: false }]
          },
          { model: db.ProductionOrderBatch, as: 'batches', attributes: ['quantity', 'status'], required: false }
        ],
        transaction
      });

      const toNLog = (v) => {
        const n = parseFloat(v);
        return Number.isFinite(n) ? n : 0;
      };
      const releasedQtyLog = (() => {
        const batches = Array.isArray(poLog?.batches) ? poLog.batches : [];
        let sum = batches
          .filter((b) => ['RELEASED', 'IN_PROGRESS'].includes(String(b?.status || '').toUpperCase()))
          .reduce((s, b) => s + toNLog(b?.quantity), 0);
        sum = Math.round(sum * 1000) / 1000;
        if (sum > 0) return sum;
        if (
          ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(
            String(poLog?.status || '').toUpperCase()
          )
        ) {
          return Math.round(toNLog(poLog?.order_quantity) * 1000) / 1000;
        }
        return 0;
      })();

      let linesToInsert = Array.isArray(req.body.nesting_stores_lines) ? req.body.nesting_stores_lines : [];

      if (!linesToInsert.length && materialIds.length) {
        linesToInsert = materialIds
          .map((mid) => {
            const key = String(mid);
            const reqQ =
              requiredQtyOverrides && Object.prototype.hasOwnProperty.call(requiredQtyOverrides, key)
                ? parseFloat(requiredQtyOverrides[key])
                : 0;
            const rawTxt =
              nesting_raw_material_texts && Object.prototype.hasOwnProperty.call(nesting_raw_material_texts, key)
                ? nesting_raw_material_texts[key]
                : null;
            return {
              material_id: mid,
              nesting_confirmed_qty: Number.isFinite(reqQ) ? reqQ : 0,
              nesting_raw_material_text: rawTxt
            };
          })
          .filter((l) => Number.isFinite(l.nesting_confirmed_qty) && l.nesting_confirmed_qty > 0);
      }

      if (linesToInsert.length > 0) {
        const nsr = await db.NestingStoresRequest.create(
          {
            company_id: companyId,
            plant_id: plantId,
            fiscal_year_id: fiscalYearId,
            production_order_id: productionOrderId,
            material_request_id: materialRequest.id,
            customer_po_id: poLog?.customer_po_id || null,
            customer_po_number: poLog?.customerPO?.po_number || null,
            customer_name: poLog?.customerPO?.customer?.name || null,
            production_order_number: poLog?.production_order_number || null,
            order_quantity: poLog?.order_quantity != null ? poLog.order_quantity : null,
            released_quantity: releasedQtyLog,
            material_request_number: requestNumber,
            required_date: requiredDateToUse,
            created_by: userId,
            updated_by: userId
          },
          { transaction }
        );

        const lineRows = [];
        for (const line of linesToInsert) {
          const mid = parseInt(line.material_id, 10);
          if (!Number.isFinite(mid) || mid <= 0) continue;
          const nq = parseFloat(line.nesting_confirmed_qty);
          if (!Number.isFinite(nq) || nq <= 0) continue;
          const resolvedWarehouseCode =
            line.warehouse_code != null && String(line.warehouse_code).trim() !== ''
              ? String(line.warehouse_code).trim().slice(0, 50)
              : resolveDefaultStorage(mid);
          const inputAvailableAtStore =
            line.available_at_store_qty != null && line.available_at_store_qty !== ''
              ? parseFloat(line.available_at_store_qty)
              : (
                line.available_qty != null && line.available_qty !== ''
                  ? parseFloat(line.available_qty)
                  : null
              );
          const resolvedAvailableAtStore =
            Number.isFinite(inputAvailableAtStore)
              ? inputAvailableAtStore
              : await getAvailableAtLocation(mid, resolvedWarehouseCode || null);

          lineRows.push({
            nesting_stores_request_id: nsr.id,
            sheet_group_key: line.sheet_group_key != null ? String(line.sheet_group_key).slice(0, 200) : null,
            material_id: mid,
            material_number:
              line.material_number != null && String(line.material_number).trim() !== ''
                ? String(line.material_number).trim().slice(0, 100)
                : null,
            thickness: line.thickness != null && line.thickness !== '' ? line.thickness : null,
            grade_short: line.grade_short != null ? String(line.grade_short).slice(0, 120) : null,
            max_length: line.max_length != null && line.max_length !== '' ? line.max_length : null,
            max_width: line.max_width != null && line.max_width !== '' ? line.max_width : null,
            height: line.height != null && line.height !== '' ? line.height : null,
            dimension_unit: line.dimension_unit ? String(line.dimension_unit).slice(0, 20) : 'mm',
            nesting_confirmed_qty: Math.round(nq * 1000) / 1000,
            nesting_weight:
              line.nesting_weight != null && line.nesting_weight !== ''
                ? parseFloat(line.nesting_weight)
                : null,
            scrap_weight:
              line.scrap_weight != null && line.scrap_weight !== ''
                ? parseFloat(line.scrap_weight)
                : null,
            scrap_percentage:
              line.scrap_percentage != null && line.scrap_percentage !== ''
                ? parseFloat(line.scrap_percentage)
                : null,
            purpose_text: line.purpose_text != null ? String(line.purpose_text) : null,
            warehouse_code: resolvedWarehouseCode || null,
            available_at_store_qty:
              Number.isFinite(resolvedAvailableAtStore) ? resolvedAvailableAtStore : null,
            shortage_qty:
              line.shortage_qty != null && line.shortage_qty !== '' ? parseFloat(line.shortage_qty) : null,
            nesting_raw_material_text: line.nesting_raw_material_text != null ? String(line.nesting_raw_material_text) : null,
            quantity_uom_id: (() => {
              const q = parseInt(line.quantity_uom_id, 10);
              return Number.isFinite(q) && q > 0 ? q : null;
            })(),
            line_status: 'PENDING'
          });
        }
        if (lineRows.length > 0) {
          const idsMissingMn = [
            ...new Set(lineRows.filter((r) => !r.material_number).map((r) => r.material_id))
          ];
          if (idsMissingMn.length) {
            const mats = await db.Material.findAll({
              where: { id: { [Op.in]: idsMissingMn } },
              attributes: ['id', 'material_number'],
              transaction
            });
            const mnById = new Map(mats.map((m) => [m.id, m.material_number]));
            lineRows.forEach((r) => {
              if (!r.material_number) {
                const mn = mnById.get(r.material_id);
                if (mn != null && String(mn).trim() !== '') {
                  r.material_number = String(mn).trim().slice(0, 100);
                }
              }
            });
          }
          await db.NestingStoresRequestLine.bulkCreate(lineRows, { 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.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'] }
      ]
    });

    // Send notifications to stores users
    try {
      const notificationService = require('../services/notificationService');
      const storesUserRoles = await db.UserRole.findAll({
        include: [
          {
            model: db.Role,
            as: 'role',
            where: {
              company_id: companyId,
              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(userRole => {
        if (userRole.user && userRole.user.id) {
          userIdsToNotify.add(userRole.user.id);
        }
      });

      const notificationPromises = Array.from(userIdsToNotify).map(userIdToNotify => {
        return notificationService.createNotification({
          companyId,
          userId: userIdToNotify,
          type: 'INFO',
          moduleName: 'MaterialRequest',
          transactionId: materialRequest.id,
          transactionNumber: requestNumber,
          title: 'Material Request Created from MRP',
          message: `Material Request ${requestNumber} has been created from MRP with ${createdRequest.items?.length || 0} material(s). Please review stock availability, issue available materials, and create Purchase Requisition for shortages.`,
          link: `/stores/materialrequest/${materialRequest.id}/view`,
          priority: 'HIGH',
          createdBy: userId
        });
      });

      await Promise.all(notificationPromises);
    } catch (notificationError) {
      console.error('Error creating notifications for Material Request from MRP:', notificationError);
    }

    sendResponse(res, 201, true, `Material Request ${requestNumber} created successfully with ${createdRequest.items?.length || 0} material(s). Stores will handle issuing available materials and creating PR for shortages.`, createdRequest);
  } catch (error) {
    await transaction.rollback();
    console.error('Error creating Material Request from MRP:', error);
    sendError(res, error.message || 'Failed to create Material Request from MRP', 500);
  }
});

