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

const generateIssueNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'MATERIAL_ISSUE', fiscalYearId, transaction);
  } catch (err) {
    console.warn('Material issue number from format master failed, using fallback:', err.message);
    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 ? { transaction } : {})
    });
    let sequence = 1;
    if (lastIssue) {
      const lastSequence = parseInt(lastIssue.issue_number.slice(-4)) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
};

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

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

  const records = await db.MaterialIssue.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
      { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'], required: false },
      { model: db.MaterialRequest, as: 'materialRequest', attributes: ['id', 'request_number'], required: false },
      { 
        model: db.MaterialIssueItem, 
        as: 'items', 
        attributes: ['id', 'source_storage_location', 'storage_location'],
        required: false
      }
    ],
    limit: limitNum,
    offset,
    order: [['issue_date', 'DESC'], ['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'Material Issues retrieved successfully', response);
});

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

  const issue = await db.MaterialIssue.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'], required: false },
      { 
        model: db.ProductionOrder, 
        as: 'productionOrder',
        include: [
          { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
        ]
      },
      { 
        model: db.MaterialRequest, 
        as: 'materialRequest', 
        attributes: ['id', 'request_number', 'request_date', 'status', 'remarks'],
        include: [
          {
            model: db.MaterialRequestItem,
            as: 'items',
            include: [
              { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
              { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
            ]
          }
        ]
      },
      { model: db.User, as: 'postedByUser', attributes: ['id', 'first_name', 'last_name'], required: false },
      { model: db.User, as: 'receivedByUser', attributes: ['id', 'first_name', 'last_name'], required: false },
      { model: db.User, as: 'issuedByUser', attributes: ['id', 'first_name', 'last_name'], required: false },
      {
        model: db.MaterialIssueItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' },
          { model: db.ProductionOrderComponent, as: 'productionOrderComponent' }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

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

  // Attach received quantity totals from dedicated receive tables (used by Material Receive view).
  if (Array.isArray(issue.items) && issue.items.length > 0 && db.MaterialIssueReceiveItem) {
    const issueItemIds = issue.items.map((it) => it.id).filter(Boolean);
    if (issueItemIds.length > 0) {
      const receivedRows = await db.MaterialIssueReceiveItem.findAll({
        where: { material_issue_item_id: { [Op.in]: issueItemIds } },
        attributes: [
          'material_issue_item_id',
          [fn('SUM', col('received_quantity')), 'received_quantity_total'],
        ],
        group: ['material_issue_item_id'],
        raw: true,
      });

      const receivedMap = receivedRows.reduce((acc, r) => {
        acc[r.material_issue_item_id] = parseFloat(r.received_quantity_total || 0) || 0;
        return acc;
      }, {});

      issue.items.forEach((it) => {
        // Make it available on the existing item objects for frontend usage.
        const issuedQty = parseFloat(it.quantity || it.issued_quantity || 0) || 0;
        it.received_quantity_total = receivedMap[it.id] ?? issuedQty;
      });
    }
  }

  sendResponse(res, 200, true, 'Material Issue retrieved successfully', issue);
});

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);
  }

  // Ensure empty string production_order_id is converted to null
  if (data.production_order_id === '' || data.production_order_id === 'null') {
    data.production_order_id = null;
  }

  // Validate: one of Production Order OR Material Request OR Department must be provided
  // (Production issues can be linked to Production Order, stores issues can be linked to Material Request,
  // and direct department issues can be recorded via department_id.)
  if (!data.production_order_id && !data.material_request_id && !data.department_id) {
    return sendError(res, 'Provide Production Order ID, Material Request ID, or Department ID', 400);
  }

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

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

  try {
    if (!data.issue_number) {
      data.issue_number = await generateIssueNumber(companyId, data.fiscal_year_id, transaction);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;

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

    // If linked to a Material Request, default department_id from the request header (if not provided)
    if (!issueData.department_id && issueData.material_request_id) {
      const mr = await db.MaterialRequest.findByPk(issueData.material_request_id, {
        attributes: ['id', 'department_id'],
        transaction
      });
      if (mr?.department_id) {
        issueData.department_id = mr.department_id;
      }
    }

    const issue = await db.MaterialIssue.create(issueData, { transaction });

    for (let i = 0; i < items.length; i++) {
      const item = items[i];
      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const totalAmount = quantity * unitPrice;

      await db.MaterialIssueItem.create({
        issue_id: issue.id,
        material_request_item_id: item.material_request_item_id || null,
        production_order_component_id: item.production_order_component_id || null,
        line_number: i + 1,
        material_id: item.material_id,
        description: item.description || null,
        quantity: quantity,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        total_amount: totalAmount,
        batch_number: item.batch_number || null,
        source_storage_location: item.source_storage_location || null,
        storage_location: item.storage_location || null,
        remarks: item.remarks || null
      }, { transaction });

      if (data.status === 'POSTED') {
        const poComponent = await db.ProductionOrderComponent.findByPk(item.production_order_component_id);
        if (poComponent) {
          const issuedQty = parseFloat(poComponent.issued_quantity) + quantity;
          await poComponent.update({ issued_quantity: issuedQty }, { transaction });
        }
      }
    }

    if (data.status === 'POSTED') {
      await issue.update({
        status: 'POSTED',
        posted_at: new Date(),
        posted_by: userId
      }, { transaction });
    }

    await transaction.commit();

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

    sendResponse(res, 201, true, 'Material Issue created successfully', createdIssue);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

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

  const issue = await db.MaterialIssue.findByPk(id);
  if (!issue) {
    return sendError(res, 'Material Issue not found', 404);
  }

  if (issue.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Material Issues can be updated', 400);
  }

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

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

    await issue.update(issueData, { transaction });

    await db.MaterialIssueItem.destroy({
      where: { issue_id: id },
      transaction
    });

    for (let i = 0; i < items.length; i++) {
      const item = items[i];
      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const totalAmount = quantity * unitPrice;

      await db.MaterialIssueItem.create({
        issue_id: id,
        material_request_item_id: item.material_request_item_id || null,
        production_order_component_id: item.production_order_component_id || null,
        line_number: i + 1,
        material_id: item.material_id,
        description: item.description || null,
        quantity: quantity,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        total_amount: totalAmount,
        batch_number: item.batch_number || null,
        source_storage_location: item.source_storage_location || null,
        storage_location: item.storage_location || null,
        remarks: item.remarks || null
      }, { transaction });
    }

    await transaction.commit();

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

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

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

  const issue = await db.MaterialIssue.findByPk(id);
  if (!issue) {
    return sendError(res, 'Material Issue not found', 404);
  }

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

  await issue.update({ status: 'CANCELLED' });

  sendResponse(res, 200, true, 'Material Issue cancelled successfully');
});

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

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

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

  if (issue.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Material Issues can be posted', 400);
  }

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

  try {
    const stockLedgerService = require('../services/stockLedgerService');
    const negativeStockPreventionService = require('../services/negativeStockPreventionService');

    // Strict validation: if linked to a Material Request, do not allow issuing when requested qty is 0
    // and do not allow issuing qty > requested qty.
    if (issue.material_request_id && issue.materialRequest) {
      const mrItems = issue.materialRequest.items || [];
      const mrById = new Map(mrItems.map((mri) => [String(mri.id), mri]));
      const mrByMaterialId = new Map(mrItems.map((mri) => [String(mri.material_id), mri]));

      for (const item of issue.items) {
        const mri =
          (item.material_request_item_id ? mrById.get(String(item.material_request_item_id)) : null) ||
          mrByMaterialId.get(String(item.material_id));

        const requestedQty = parseFloat(mri?.required_quantity || 0) || 0;
        const issuedQty = parseFloat(item.quantity || 0) || 0;

        if (!requestedQty || requestedQty <= 0) {
          await transaction.rollback();
          return sendError(
            res,
            `Cannot issue material ${item.material?.material_number || item.material_id}: Requested quantity is 0`,
            400
          );
        }
        if (issuedQty > requestedQty) {
          await transaction.rollback();
          return sendError(
            res,
            `Cannot issue material ${item.material?.material_number || item.material_id}: Issued quantity (${issuedQty}) exceeds requested quantity (${requestedQty})`,
            400
          );
        }
      }
    }

    // Production-order safeguard: do not issue beyond nesting confirmed qty.
    // If nesting data is absent for a component, fall back to BOM/required qty.
    if (issue.production_order_id) {
      for (const item of issue.items) {
        if (!item.production_order_component_id) continue;
        const issueQty = parseFloat(item.quantity || 0) || 0;
        if (issueQty <= 0) continue;

        const poComponent = await db.ProductionOrderComponent.findByPk(item.production_order_component_id, {
          attributes: ['id', 'required_quantity', 'issued_quantity'],
          transaction
        });
        if (!poComponent) continue;

        const nestingConfirmed = await db.ProductionNesting.sum('nesting_confirmed_quantity', {
          where: {
            production_order_id: issue.production_order_id,
            production_order_component_id: poComponent.id
          },
          transaction
        });
        const requiredQty = parseFloat(poComponent.required_quantity || 0) || 0;
        const confirmedQtyRaw = parseFloat(nestingConfirmed || 0) || 0;
        const allowedQty = confirmedQtyRaw > 0 ? confirmedQtyRaw : requiredQty;
        const alreadyIssued = parseFloat(poComponent.issued_quantity || 0) || 0;
        const projectedIssued = alreadyIssued + issueQty;

        if (projectedIssued > allowedQty) {
          await transaction.rollback();
          return sendError(
            res,
            `Cannot issue material ${item.material?.material_number || item.material_id}: Issued quantity (${projectedIssued.toFixed(3)}) exceeds confirmed quantity (${allowedQty.toFixed(3)})`,
            400
          );
        }
      }
    }
    
    // Validate that all items have source and destination storage locations (and they must be different)
    for (const item of issue.items) {
      if (!item.source_storage_location) {
        await transaction.rollback();
        return sendError(res, `Source storage location is required for material ${item.material?.material_number || item.material_id}`, 400);
      }
      if (!item.storage_location) {
        await transaction.rollback();
        return sendError(res, `Destination storage location is required for material ${item.material?.material_number || item.material_id}`, 400);
      }
      if (String(item.source_storage_location) === String(item.storage_location)) {
        await transaction.rollback();
        return sendError(
          res,
          `Source and destination storage locations cannot be the same for material ${item.material?.material_number || item.material_id}`,
          400
        );
      }
    }

    // Validate stock availability for all items before processing
    const batchValidation = await negativeStockPreventionService.validateBatchStockIssues(
      issue.items,
      {
        companyId: issue.company_id,
        plantId: issue.plant_id,
        operation: 'Material Issue',
        transaction
      }
    );

    if (!batchValidation.isValid) {
      await transaction.rollback();
      const errorMessage = negativeStockPreventionService.formatValidationErrors(batchValidation.errors);
      return sendError(res, errorMessage, 400);
    }

    // Generate Inventory Goods Issue number for stock movement
    const generateMovementNumber = async (companyId) => {
      const today = new Date();
      const year = today.getFullYear();
      const prefix = `GI${year}`;
      
      const lastGI = await db.InventoryGoodsIssue.findOne({
        where: {
          company_id: companyId,
          movement_number: { [Op.like]: `${prefix}%` }
        },
        order: [['movement_number', 'DESC']],
        transaction
      });

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

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

    // Create Inventory Goods Issue to record stock movement from stores to production
    const movementNumber = await generateMovementNumber(issue.company_id);
    
    const inventoryGoodsIssue = await db.InventoryGoodsIssue.create({
      company_id: issue.company_id,
      plant_id: issue.plant_id,
      fiscal_year_id: issue.fiscal_year_id,
      movement_number: movementNumber,
      movement_date: issue.issue_date,
      movement_type: '261', // SAP Movement Type: 261 = Issue to Production Order
      storage_location: issue.items[0]?.source_storage_location || null, // Source location (stores)
      cost_center_id: null, // Can be linked to production cost center
      reference_number: issue.issue_number,
      reference_type: 'MATERIAL_ISSUE',
      status: 'POSTED', // Auto-post since Material Issue is being posted
      remarks: `Material Issue: ${issue.issue_number} for Production Order: ${issue.productionOrder?.production_order_number || ''}. Materials in transit to production.`,
      posted_at: new Date(),
      posted_by: userId,
      created_by: userId,
      updated_by: userId
    }, { transaction });

    // Process each item: reduce stock from source location and record in stock ledger
    // Note: Stock validation already done above in batch, but we keep individual validation as safety check
    for (let i = 0; i < issue.items.length; i++) {
      const item = issue.items[i];
      const quantity = parseFloat(item.quantity || 0);
      const unitPrice = parseFloat(item.unit_price || 0) || 0;
      const totalAmount = quantity * unitPrice;
      
      // Additional safety check (batch validation already done, but this ensures no race conditions)
      const validation = await negativeStockPreventionService.validateStockIssue({
        companyId: issue.company_id,
        plantId: issue.plant_id,
        materialId: item.material_id,
        storageLocation: item.source_storage_location || null,
        quantityToIssue: quantity,
        operation: 'Material Issue',
        referenceNumber: issue.issue_number,
        transaction
      });

      if (!validation.isValid) {
        await transaction.rollback();
        return sendError(res, validation.errorMessage, 400);
      }

      // Create stock ledger entry - ISSUE from source location
      await stockLedgerService.createLedgerEntry({
        company_id: issue.company_id,
        plant_id: issue.plant_id,
        material_id: item.material_id,
        transaction_date: issue.issue_date,
        transaction_type: 'ISSUE',
        reference_type: 'MIN', // Material Issue Note
        reference_id: issue.id,
        reference_number: issue.issue_number,
        storage_location: item.source_storage_location, // Source: stores
        quantity_in: 0,
        quantity_out: quantity,
        unit_cost: unitPrice,
        total_cost: totalAmount,
        batch_number: item.batch_number || null,
        remarks: `Material Issue: ${issue.issue_number} - Issued to production (IN_TRANSIT). Destination: ${item.storage_location}`,
        created_by: userId
      }, transaction);

      // Create Inventory Goods Issue Item
      await db.InventoryGoodsIssueItem.create({
        movement_id: inventoryGoodsIssue.id,
        line_number: i + 1,
        material_id: item.material_id,
        description: item.material?.description || item.description || null,
        quantity: quantity,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        total_amount: totalAmount,
        batch_number: item.batch_number || null,
        remarks: item.remarks || null
      }, { transaction });

      // Update Production Order Component issued quantity
      if (item.production_order_component_id) {
        const poComponent = await db.ProductionOrderComponent.findByPk(item.production_order_component_id, { transaction });
        if (poComponent) {
          const currentIssued = parseFloat(poComponent.issued_quantity || 0);
          const newIssuedQty = currentIssued + quantity;
          await poComponent.update({ 
            issued_quantity: newIssuedQty,
            pending_quantity: Math.max(0, parseFloat(poComponent.required_quantity || 0) - newIssuedQty)
          }, { transaction });
        }
      }

      // Update stock reservation when material is issued
      if (issue.production_order_id) {
        try {
          const stockReservationService = require('../services/stockReservationService');
          await stockReservationService.updateReservationOnIssue(
            issue.production_order_id,
            item.material_id,
            quantity,
            item.source_storage_location || null,
            transaction
          );
        } catch (reservationError) {
          console.error('Error updating stock reservation:', reservationError);
          // Don't fail the issue if reservation update fails
        }
      }

      // Update Material Request Item issued quantity if linked
      if (item.material_request_item_id) {
        const mrItem = await db.MaterialRequestItem.findByPk(item.material_request_item_id, { transaction });
        if (mrItem) {
          const currentIssued = parseFloat(mrItem.issued_quantity || 0);
          const newIssuedQty = currentIssued + quantity;
          const pendingQty = Math.max(0, parseFloat(mrItem.required_quantity || 0) - newIssuedQty);
          
          await mrItem.update({
            issued_quantity: newIssuedQty,
            pending_quantity: pendingQty
          }, { transaction });

          // Update Material Request status if all items are issued
          const materialRequest = await db.MaterialRequest.findByPk(mrItem.request_id, {
            include: [{ model: db.MaterialRequestItem, as: 'items' }],
            transaction
          });

          if (materialRequest) {
            const allIssued = materialRequest.items.every(mri => 
              parseFloat(mri.issued_quantity || 0) >= parseFloat(mri.required_quantity || 0)
            );
            const partiallyIssued = materialRequest.items.some(mri => 
              parseFloat(mri.issued_quantity || 0) > 0 && 
              parseFloat(mri.issued_quantity || 0) < parseFloat(mri.required_quantity || 0)
            );

            if (allIssued) {
              await materialRequest.update({ status: 'COMPLETED', updated_by: userId }, { transaction });
            } else if (partiallyIssued && materialRequest.status !== 'PARTIALLY_ISSUED') {
              await materialRequest.update({ status: 'PARTIALLY_ISSUED', updated_by: userId }, { transaction });
            }
          }
        }
      }
    }

    // Update Material Issue status to IN_TRANSIT (not POSTED - materials are in transit)
    await issue.update({
      status: 'IN_TRANSIT',
      posted_at: new Date(),
      posted_by: userId,
      issued_by: userId
    }, { transaction });

    await transaction.commit();

    // Post to GL (accounting integration) - WIP and Inventory
    try {
      await glPostingService.postMaterialIssueToGL(issue, userId);
    } catch (glError) {
      console.warn('GL posting failed for material issue:', glError.message);
      // Don't fail the material issue posting if GL posting fails
      // Log the error but continue
    }

    // Reload the updated Material Issue with all associations
    const updatedIssue = await db.MaterialIssue.findByPk(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.User, as: 'postedByUser' },
        { model: db.User, as: 'issuedByUser' },
        {
          model: db.MaterialIssueItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ],
          order: [['line_number', 'ASC']]
        }
      ]
    });

    sendResponse(res, 200, true, 'Material Issue posted successfully. Materials are now IN_TRANSIT. Stock reduced from source storage location. Production department should receive the materials.', {
      materialIssue: updatedIssue,
      inventoryGoodsIssue: inventoryGoodsIssue
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error posting Material Issue:', error);
    console.error('Error details:', {
      message: error.message,
      name: error.name,
      stack: error.stack
    });
    sendError(res, error.message || 'Failed to post Material Issue', 400);
  }
});

// Receive materials by production department
exports.receive = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

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

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

  if (issue.status !== 'IN_TRANSIT') {
    return sendError(res, `Only IN_TRANSIT Material Issues can be received. Current status: ${issue.status}`, 400);
  }

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

  try {
    const stockLedgerService = require('../services/stockLedgerService');
    
    // Generate Inventory Goods Receipt number for receiving materials
    const generateReceiptNumber = async (companyId) => {
      const today = new Date();
      const year = today.getFullYear();
      const prefix = `GR${year}`;
      
      const lastGR = await db.InventoryGoodsReceipt.findOne({
        where: {
          company_id: companyId,
          movement_number: { [Op.like]: `${prefix}%` }
        },
        order: [['movement_number', 'DESC']],
        transaction
      });

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

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

    // Create Inventory Goods Receipt to record stock receipt at production location
    const receiptNumber = await generateReceiptNumber(issue.company_id);
    
    const inventoryGoodsReceipt = await db.InventoryGoodsReceipt.create({
      company_id: issue.company_id,
      plant_id: issue.plant_id,
      fiscal_year_id: issue.fiscal_year_id,
      movement_number: receiptNumber,
      movement_date: new Date().toISOString().split('T')[0],
      movement_type: '101', // SAP Movement Type: 101 = Goods Receipt
      storage_location: issue.items[0]?.storage_location || null, // Destination location (production)
      reference_number: issue.issue_number,
      reference_type: 'PRODUCTION_ORDER',
      status: 'POSTED',
      remarks: `Material Issue Receipt: ${issue.issue_number} - Received materials at production department. Production Order: ${issue.productionOrder?.production_order_number || ''}`,
      posted_at: new Date(),
      posted_by: userId,
      created_by: userId,
      updated_by: userId
    }, { transaction });

    // Store receive details in a separate table for the Material Receive view.
    const materialIssueReceive = await db.MaterialIssueReceive.create(
      {
        company_id: issue.company_id,
        plant_id: issue.plant_id,
        fiscal_year_id: issue.fiscal_year_id,
        material_issue_id: issue.id,
        receive_number: receiptNumber,
        receive_date: new Date().toISOString().split('T')[0],
        received_by: userId,
        remarks: `Material Issue Receive: ${issue.issue_number} - Received at production department.`,
        created_by: userId,
        updated_by: userId,
      },
      { transaction }
    );

    // Process each item: add stock to destination location and record in stock ledger
    for (let i = 0; i < issue.items.length; i++) {
      const item = issue.items[i];
      const quantity = parseFloat(item.quantity || 0);
      const unitPrice = parseFloat(item.unit_price || 0) || 0;
      const totalAmount = quantity * unitPrice;

      // Create stock ledger entry - RECEIPT to destination location
      await stockLedgerService.createLedgerEntry({
        company_id: issue.company_id,
        plant_id: issue.plant_id,
        material_id: item.material_id,
        transaction_date: new Date().toISOString().split('T')[0],
        transaction_type: 'RECEIPT',
        reference_type: 'PRODUCTION_RECEIPT',
        reference_id: issue.id,
        reference_number: issue.issue_number,
        storage_location: item.storage_location, // Destination: production department
        quantity_in: quantity,
        quantity_out: 0,
        unit_cost: unitPrice,
        total_cost: totalAmount,
        batch_number: item.batch_number || null,
        remarks: `Material Issue Receipt: ${issue.issue_number} - Received from source location ${item.source_storage_location || 'N/A'}`,
        created_by: userId
      }, transaction);

      // Create Inventory Goods Receipt Item
      await db.InventoryGoodsReceiptItem.create({
        movement_id: inventoryGoodsReceipt.id,
        line_number: i + 1,
        material_id: item.material_id,
        description: item.material?.description || item.description || null,
        quantity: quantity,
        uom_id: item.uom_id,
        unit_price: unitPrice,
        total_amount: totalAmount,
        batch_number: item.batch_number || null,
        remarks: item.remarks || null
      }, { transaction });

      // Persist the received quantity in dedicated receive item table.
      await db.MaterialIssueReceiveItem.create(
        {
          material_issue_receive_id: materialIssueReceive.id,
          material_issue_item_id: item.id,
          material_id: item.material_id,
          uom_id: item.uom_id,
          received_quantity: quantity,
          batch_number: item.batch_number || null,
          received_from_storage_location: item.source_storage_location || null,
          received_to_storage_location: item.storage_location || null,
          remarks: item.remarks || null,
          created_by: userId,
          updated_by: userId,
        },
        { transaction }
      );
    }

    // Update Material Issue status to RECEIVED
    await issue.update({
      status: 'RECEIVED',
      received_at: new Date(),
      received_by: userId
    }, { transaction });

    const productionWipService = require('../services/productionWipService');
    await productionWipService.creditFromMaterialIssueReceive(transaction, { issue, userId });

    // Auto-update Production Order status to IN_PROGRESS when materials are received
    if (issue.production_order_id) {
      try {
        const productionOrderStatusService = require('../services/productionOrderStatusService');
        await productionOrderStatusService.onMaterialReceived(issue, userId, transaction);
        // Status update logged via notification service if needed
      } catch (statusError) {
        console.error('Error updating Production Order status:', statusError);
        // Don't fail the material receipt if status update fails
      }
    }

    await transaction.commit();

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

    sendResponse(res, 200, true, 'Materials received successfully. Stock added to destination storage location.', {
      materialIssue: updatedIssue,
      inventoryGoodsReceipt: inventoryGoodsReceipt
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message, 400);
  }
});

// Return materials to stores from production
exports.returnToStores = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { items } = req.body; // Array of { material_issue_item_id, return_quantity, remarks }
  const userId = req.user?.userId || null;

  if (!items || items.length === 0) {
    return sendError(res, 'Please select at least one item to return', 400);
  }

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

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

  if (issue.status !== 'RECEIVED') {
    return sendError(res, `Only RECEIVED Material Issues can be returned to stores. Current status: ${issue.status}`, 400);
  }

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

  try {
    const stockLedgerService = require('../services/stockLedgerService');
    
    // Generate Material Return Note number
    const generateReturnNumber = async (companyId) => {
      const today = new Date();
      const year = today.getFullYear();
      const prefix = `MRN${year}`;
      
      const lastMRN = await db.MaterialReturnNote.findOne({
        where: {
          company_id: companyId,
          mrn_number: { [Op.like]: `${prefix}%` }
        },
        order: [['mrn_number', 'DESC']],
        transaction: dbTransaction
      });

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

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

    // Find stores storage location (WAREHOUSE type)
    const storesLocation = await db.StorageLocation.findOne({
      where: {
        company_id: issue.company_id,
        plant_id: issue.plant_id,
        storage_location_type: 'WAREHOUSE',
        status: 'ACTIVE'
      },
      transaction: dbTransaction
    });

    if (!storesLocation) {
      await dbTransaction.rollback();
      return sendError(res, 'Stores storage location not found. Please configure a WAREHOUSE type storage location.', 400);
    }

    // Create Material Return Note
    const returnNumber = await generateReturnNumber(issue.company_id);
    const returnDate = new Date().toISOString().split('T')[0];
    
    const materialReturnNote = await db.MaterialReturnNote.create({
      company_id: issue.company_id,
      plant_id: issue.plant_id,
      fiscal_year_id: issue.fiscal_year_id,
      mrn_number: returnNumber, // Fixed: Changed from return_number to mrn_number
      return_date: returnDate,
      material_issue_id: issue.id,
      production_order_id: issue.production_order_id,
      return_type: 'PRODUCTION_TO_STORES',
      status: 'IN_TRANSIT', // Changed from POSTED to IN_TRANSIT - stores needs to receive
      remarks: `Returned ${items.length} item(s) to stores from Material Issue ${issue.issue_number}. Materials are in transit to stores.`,
      returned_by: userId,
      created_by: userId,
      updated_by: userId
    }, { transaction: dbTransaction });

    // Process each return item
    let lineNumber = 1;
    for (const returnItem of items) {
      // Find the original Material Issue Item
      const issueItem = issue.items.find(item => item.id === returnItem.material_issue_item_id);
      if (!issueItem) {
        if (dbTransaction && !dbTransaction.finished) {
        await dbTransaction.rollback();
        }
        return sendError(res, `Material Issue Item ${returnItem.material_issue_item_id} not found`, 400);
      }

      const returnQty = parseFloat(returnItem.return_quantity || returnItem.quantity || issueItem.quantity || issueItem.issued_quantity || 0);
      const originalQty = parseFloat(issueItem.quantity || issueItem.issued_quantity || 0);
      
      if (returnQty <= 0) {
        continue; // Skip zero quantity returns
      }
      
      if (returnQty > originalQty) {
        if (dbTransaction && !dbTransaction.finished) {
        await dbTransaction.rollback();
        }
        return sendError(res, `Return quantity (${returnQty}) cannot exceed issued quantity (${originalQty}) for material ${issueItem.material?.material_number || issueItem.material_id}`, 400);
      }

      // Check for duplicate returns - validate that this item hasn't been returned already
      // Get all existing MRN items for this material_issue_item_id that are POSTED or IN_TRANSIT
      const existingReturns = await db.MaterialReturnNoteItem.findAll({
        where: {
          material_issue_item_id: issueItem.id,
        },
        include: [
          {
            model: db.MaterialReturnNote,
            as: 'mrn',
            where: {
              status: { [Op.in]: ['IN_TRANSIT', 'POSTED'] },
              id: { [Op.ne]: materialReturnNote.id } // Exclude current MRN if updating
            },
            required: true
          }
        ],
        transaction: dbTransaction
      });

      // Calculate total already returned quantity
      const alreadyReturnedQty = existingReturns.reduce((sum, ret) => {
        return sum + parseFloat(ret.returned_quantity || 0);
      }, 0);

      // Check if the new return quantity would exceed available quantity
      const availableToReturn = originalQty - alreadyReturnedQty;
      if (returnQty > availableToReturn) {
        if (dbTransaction && !dbTransaction.finished) {
          await dbTransaction.rollback();
        }
        return sendError(res, `Cannot return ${returnQty.toFixed(3)} for material ${issueItem.material?.material_number || issueItem.material_id}. Already returned: ${alreadyReturnedQty.toFixed(3)}, Available to return: ${availableToReturn.toFixed(3)}, Original issued: ${originalQty.toFixed(3)}`, 400);
      }

      // Check stock availability in production location (destination)
      const currentBalance = await stockLedgerService.getCurrentBalance(
        issue.company_id,
        issue.plant_id,
        issueItem.material_id,
        issueItem.storage_location || null // Destination location (production)
      );
      
      if (currentBalance < returnQty) {
        if (dbTransaction && !dbTransaction.finished) {
        await dbTransaction.rollback();
        }
        return sendError(res, `Insufficient stock in production location for material ${issueItem.material?.material_number || issueItem.material_id}. Available: ${currentBalance}, Requested: ${returnQty}`, 400);
      }

      // Create stock ledger entry - ISSUE from production location (destination)
      // This reduces stock from production location (materials are leaving production, IN_TRANSIT to stores)
      await stockLedgerService.createLedgerEntry({
        company_id: issue.company_id,
        plant_id: issue.plant_id,
        material_id: issueItem.material_id,
        transaction_date: returnDate,
        transaction_type: 'ISSUE',
        reference_type: 'MRN', // Material Return Note
        reference_id: materialReturnNote.id,
        reference_number: returnNumber,
        storage_location: issueItem.storage_location, // Source: production location (destination from original issue)
        quantity_in: 0,
        quantity_out: returnQty,
        unit_cost: parseFloat(issueItem.unit_price || 0),
        total_cost: returnQty * parseFloat(issueItem.unit_price || 0),
        batch_number: issueItem.batch_number || null,
        remarks: `Material Return to Stores (MRN: ${returnNumber}) - IN_TRANSIT. From production location ${issueItem.storage_location || 'N/A'} to stores ${storesLocation.storage_location_code}. Original Issue: ${issue.issue_number}. Stores needs to receive these materials.`,
        created_by: userId
      }, dbTransaction);

      // Note: Stock will be added to stores location when stores receives the materials (via MRN post/receive endpoint)
      // We don't create RECEIPT entry here since status is IN_TRANSIT

      // Create Material Return Note Item
      await db.MaterialReturnNoteItem.create({
        mrn_id: materialReturnNote.id,
        line_number: lineNumber++,
        material_issue_item_id: issueItem.id,
        production_order_component_id: issueItem.production_order_component_id || null,
        material_id: issueItem.material_id,
        description: issueItem.material?.description || issueItem.description || null,
        returned_quantity: returnQty,
        uom_id: issueItem.uom_id,
        batch_number: issueItem.batch_number || null,
        storage_location: storesLocation.storage_location_code,
        return_reason: returnItem.return_reason || 'Not needed for production',
        remarks: returnItem.remarks || null
      }, { transaction: dbTransaction });
    }

    await dbTransaction.commit();

    // Reload the Material Return Note with items
    const updatedReturnNote = await db.MaterialReturnNote.findByPk(materialReturnNote.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.MaterialIssue, as: 'materialIssue', attributes: ['id', 'issue_number', 'issue_date'] },
        { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
        {
          model: db.MaterialReturnNoteItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' },
            { model: db.MaterialIssueItem, as: 'materialIssueItem' }
          ],
          order: [['line_number', 'ASC']]
        }
      ]
    });

    sendResponse(res, 200, true, `Successfully returned ${items.length} item(s) to stores. Stock updated from production location to stores location.`, {
      materialReturnNote: updatedReturnNote,
      materialIssue: issue
    });
  } catch (error) {
    await dbTransaction.rollback();
    sendError(res, error.message, 400);
  }
});

