const db = require('../models');
const { Op } = require('sequelize');
const stockLedgerService = require('./stockLedgerService');
const glPostingService = require('./glPostingService');

/**
 * Generate movement number for finished goods receipt
 */
const generateMovementNumber = async (companyId) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `FGR${year}`; // Finished Goods Receipt
  
  const lastGR = await db.InventoryGoodsReceipt.findOne({
    where: {
      company_id: companyId,
      movement_number: { [Op.like]: `${prefix}%` }
    },
    order: [['movement_number', 'DESC']]
  });

  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 finished goods receipt from production order
 * This is called automatically when Production Order status becomes CONFIRMED
 * 
 * @param {Object} productionOrder - Production Order instance
 * @param {Integer} userId - User ID who triggered the confirmation
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Object} Created InventoryGoodsReceipt
 */
exports.createFinishedGoodsReceipt = async (productionOrder, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  const useTransaction = !transaction;
  
  let txn = transaction;
  if (useTransaction) {
    txn = await db.sequelize.transaction();
  }

  try {
    // Check if GRN already exists for this production order
    const existingGRN = await db.InventoryGoodsReceipt.findOne({
      where: {
        reference_type: 'PRODUCTION_ORDER',
        reference_number: productionOrder.production_order_number,
        status: { [Op.in]: ['DRAFT', 'POSTED'] }
      },
      transaction: txn
    });

    if (existingGRN) {
      console.log(`GRN already exists for Production Order ${productionOrder.production_order_number}: ${existingGRN.movement_number}`);
      if (useTransaction) await txn.rollback();
      return existingGRN;
    }

    // Get finished material details
    const finishedMaterial = await db.Material.findByPk(productionOrder.finished_material_id, {
      include: [
        { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code', 'uom_name'] }
      ],
      transaction: txn
    });

    if (!finishedMaterial) {
      throw new Error(`Finished material not found for Production Order ${productionOrder.production_order_number}`);
    }

    // Get MaterialPlant to get storage location
    const materialPlant = await db.MaterialPlant.findOne({
      where: {
        material_id: productionOrder.finished_material_id,
        plant_id: productionOrder.plant_id,
        status: 'ACTIVE'
      },
      transaction: txn
    });

    if (!materialPlant) {
      throw new Error(`Material Plant data not found for material ${productionOrder.finished_material_id} in plant ${productionOrder.plant_id}`);
    }

    const storageLocation = materialPlant.storage_location || null;

    // Generate movement number
    const movementNumber = await generateMovementNumber(productionOrder.company_id);

    // Get material valuation for unit price (if available)
    const materialValuation = await db.MaterialValuation.findOne({
      where: {
        material_id: productionOrder.finished_material_id,
        plant_id: productionOrder.plant_id,
        status: 'ACTIVE'
      },
      transaction: txn
    });

    const unitPrice = materialValuation?.standard_price || 0;
    const confirmedQuantity = parseFloat(productionOrder.confirmed_quantity || 0);
    const totalAmount = confirmedQuantity * parseFloat(unitPrice);

    // Create InventoryGoodsReceipt
    const grnData = {
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      fiscal_year_id: productionOrder.fiscal_year_id,
      movement_number: movementNumber,
      movement_date: new Date().toISOString().split('T')[0],
      movement_type: '101', // Goods Receipt
      reference_type: 'PRODUCTION_ORDER',
      reference_number: productionOrder.production_order_number,
      storage_location: storageLocation,
      status: 'DRAFT',
      remarks: `Auto-created from Production Order: ${productionOrder.production_order_number}`,
      created_by: userId,
      updated_by: userId
    };

    const grn = await db.InventoryGoodsReceipt.create(grnData, { transaction: txn });

    // Create GRN item
    const grnItem = await db.InventoryGoodsReceiptItem.create({
      movement_id: grn.id,
      line_number: 1,
      material_id: productionOrder.finished_material_id,
      description: finishedMaterial.description || finishedMaterial.material_number,
      quantity: confirmedQuantity,
      uom_id: productionOrder.uom_id,
      unit_price: unitPrice,
      total_amount: totalAmount,
      batch_number: null,
      remarks: `Finished goods from Production Order ${productionOrder.production_order_number}`
    }, { transaction: txn });

    // Auto-post the GRN
    await grn.update({
      status: 'POSTED',
      posted_at: new Date(),
      posted_by: userId
    }, { transaction: txn });

    // Create stock ledger entry
    await stockLedgerService.createLedgerEntry({
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      material_id: productionOrder.finished_material_id,
      storage_location: storageLocation,
      transaction_type: 'RECEIPT',
      transaction_date: new Date().toISOString().split('T')[0],
      reference_type: 'PRODUCTION_ORDER',
      reference_number: productionOrder.production_order_number,
      reference_id: productionOrder.id,
      quantity_in: confirmedQuantity,
      quantity_out: 0,
      unit_price: unitPrice,
      remarks: `Finished goods receipt from Production Order ${productionOrder.production_order_number}`
    }, txn);

    if (useTransaction) {
      await txn.commit();
    }

    // Post to GL (if accounting integration is enabled)
    // Do this after transaction commit since GL posting creates its own transaction
    try {
      // Reload grn to ensure it has all data
      const grnForGL = await db.InventoryGoodsReceipt.findByPk(grn.id, {
        include: [
          {
            model: db.InventoryGoodsReceiptItem,
            as: 'items',
            include: [
              { model: db.Material, as: 'material' }
            ]
          }
        ]
      });
      await glPostingService.postInventoryMovementToGL(grnForGL, 'GOODS_RECEIPT', userId);
    } catch (glError) {
      console.warn('GL posting failed for finished goods receipt:', glError.message);
      // Don't fail the entire process if GL posting fails
    }

    // Reload with associations
    const createdGRN = await db.InventoryGoodsReceipt.findByPk(grn.id, {
      include: [
        { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
        { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
        {
          model: db.InventoryGoodsReceiptItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
            { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
          ]
        }
      ]
    });

    console.log(`Finished Goods Receipt created: ${movementNumber} for Production Order ${productionOrder.production_order_number}`);
    
    return createdGRN;
  } catch (error) {
    if (useTransaction && txn) {
      await txn.rollback();
    }
    console.error('Error creating finished goods receipt:', error);
    throw error;
  }
};

/**
 * Check if Production Order should trigger GRN creation
 * Called when Production Order status changes to CONFIRMED
 */
exports.shouldCreateGRN = (productionOrder) => {
  // Only create GRN when status becomes CONFIRMED (fully confirmed)
  // Not for PARTIALLY_CONFIRMED
  return productionOrder.status === 'CONFIRMED' && 
         parseFloat(productionOrder.confirmed_quantity || 0) > 0;
};

