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

/**
 * Generate Material Issue number (uses Number Format Master; backflush uses same MI series)
 */
const generateIssueNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await numberFormatService.generateNextNumber(companyId, 'MATERIAL_ISSUE', fiscalYearId, transaction);
  } catch (err) {
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `BF${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')}`;
  }
};

/**
 * Calculate backflush consumption quantity
 * Formula: (Confirmed Qty / Order Qty) × Required Qty
 * 
 * @param {Number} confirmedQty - Confirmed quantity from production
 * @param {Number} orderQty - Original order quantity
 * @param {Number} requiredQty - Required quantity from BOM
 * @returns {Number} Calculated consumption quantity
 */
const calculateConsumption = (confirmedQty, orderQty, requiredQty) => {
  if (!orderQty || orderQty === 0) {
    throw new Error('Order quantity cannot be zero for backflush calculation');
  }
  const consumption = (parseFloat(confirmedQty) / parseFloat(orderQty)) * parseFloat(requiredQty);
  return Math.round(consumption * 1000) / 1000; // Round to 3 decimal places
};

/**
 * Get source storage location for a material in a plant
 * Priority: MaterialPlant.storage_location > Default storage location
 */
const getSourceStorageLocation = async (materialId, plantId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const materialPlant = await db.MaterialPlant.findOne({
    where: {
      material_id: materialId,
      plant_id: plantId,
      status: 'ACTIVE'
    },
    ...options
  });

  return materialPlant?.storage_location || null;
};

/**
 * Get material valuation for unit price
 */
const getMaterialUnitPrice = async (materialId, plantId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const materialValuation = await db.MaterialValuation.findOne({
    where: {
      material_id: materialId,
      plant_id: plantId,
      status: 'ACTIVE'
    },
    ...options
  });

  return materialValuation?.standard_price || 0;
};

/**
 * Auto-issue components with BACKFLUSH issue_type when production is confirmed
 * 
 * @param {Object} productionOrder - Production Order instance
 * @param {Number} confirmedQuantity - Confirmed quantity from production confirmation
 * @param {Integer} userId - User ID who triggered the confirmation
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Object} Created MaterialIssue
 */
exports.backflushComponents = async (productionOrder, confirmedQuantity, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  const useTransaction = !transaction;
  
  let txn = transaction;
  if (useTransaction) {
    txn = await db.sequelize.transaction();
  }

  try {
    // Get all components with BACKFLUSH issue_type
    const backflushComponents = await db.ProductionOrderComponent.findAll({
      where: {
        production_order_id: productionOrder.id,
        issue_type: 'BACKFLUSH'
      },
      include: [
        { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
        { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
      ],
      transaction: txn
    });

    if (!backflushComponents || backflushComponents.length === 0) {
      console.log(`No BACKFLUSH components found for Production Order ${productionOrder.production_order_number}`);
      if (useTransaction) await txn.rollback();
      return null;
    }

    // Check if backflush MaterialIssue already exists for this confirmation
    // We'll create one per confirmation to track consumption accurately
    const issueNumber = await generateIssueNumber(productionOrder.company_id, productionOrder.fiscal_year_id, txn);

    // Create MaterialIssue for backflushing
    const materialIssue = await db.MaterialIssue.create({
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      fiscal_year_id: productionOrder.fiscal_year_id,
      issue_number: issueNumber,
      issue_date: new Date().toISOString().split('T')[0],
      production_order_id: productionOrder.id,
      issue_type: 'BACKFLUSH',
      status: 'DRAFT', // Will be posted immediately
      remarks: `Auto-created backflush for Production Order ${productionOrder.production_order_number}. Confirmed Qty: ${confirmedQuantity}, Order Qty: ${productionOrder.order_quantity}`,
      created_by: userId,
      updated_by: userId
    }, { transaction: txn });

    const issueItems = [];
    let lineNumber = 1;

    // Process each BACKFLUSH component
    for (const component of backflushComponents) {
      // Calculate consumption: (Confirmed Qty / Order Qty) × Required Qty
      const consumptionQty = calculateConsumption(
        confirmedQuantity,
        productionOrder.order_quantity,
        component.required_quantity
      );

      if (consumptionQty <= 0) {
        console.log(`Skipping component ${component.material?.material_number} - calculated consumption is zero or negative`);
        continue;
      }

      // Get source storage location
      const sourceStorageLocation = component.storage_location || 
        await getSourceStorageLocation(component.material_id, productionOrder.plant_id, txn);

      if (!sourceStorageLocation) {
        throw new Error(`Storage location not found for material ${component.material?.material_number || component.material_id} in plant ${productionOrder.plant_id}`);
      }

      // Get unit price
      const unitPrice = await getMaterialUnitPrice(component.material_id, productionOrder.plant_id, txn);
      const totalAmount = consumptionQty * parseFloat(unitPrice);

      // Validate stock availability using centralized service
      const validation = await negativeStockPreventionService.validateStockIssue({
        companyId: productionOrder.company_id,
        plantId: productionOrder.plant_id,
        materialId: component.material_id,
        storageLocation: sourceStorageLocation,
        quantityToIssue: consumptionQty,
        operation: 'Backflush',
        referenceNumber: productionOrder.production_order_number,
        transaction: txn
      });

      if (!validation.isValid) {
        throw new Error(validation.errorMessage);
      }

      // Create MaterialIssueItem
      const issueItem = await db.MaterialIssueItem.create({
        issue_id: materialIssue.id,
        production_order_component_id: component.id,
        line_number: lineNumber++,
        material_id: component.material_id,
        description: component.material?.description || component.material?.material_number || '',
        quantity: consumptionQty,
        uom_id: component.uom_id,
        unit_price: unitPrice,
        total_amount: totalAmount,
        batch_number: null,
        source_storage_location: sourceStorageLocation,
        storage_location: sourceStorageLocation, // For backflush, source and destination are same (stores)
        remarks: `Backflushed: ${consumptionQty} ${component.uom?.uom_code || ''} (Required: ${component.required_quantity}, Confirmed: ${confirmedQuantity}, Order: ${productionOrder.order_quantity})`
      }, { transaction: txn });

      issueItems.push(issueItem);

      // Update ProductionOrderComponent: backflushed_quantity and issued_quantity
      const currentBackflushed = parseFloat(component.backflushed_quantity || 0);
      const currentIssued = parseFloat(component.issued_quantity || 0);
      
      await component.update({
        backflushed_quantity: currentBackflushed + consumptionQty,
        issued_quantity: currentIssued + consumptionQty
      }, { transaction: txn });
    }

    if (issueItems.length === 0) {
      // No items to issue, cancel the MaterialIssue
      await materialIssue.update({ status: 'CANCELLED' }, { transaction: txn });
      if (useTransaction) await txn.commit();
      console.log(`No items to backflush for Production Order ${productionOrder.production_order_number}`);
      return null;
    }

    // Auto-post the MaterialIssue (this will create InventoryGoodsIssue and stock ledger entries)
    // We need to call the post logic here
    await exports.postBackflushMaterialIssue(materialIssue.id, userId, txn);

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

    // Reload with associations
    const createdIssue = await db.MaterialIssue.findByPk(materialIssue.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' }
          ]
        }
      ]
    });

    console.log(`Backflush MaterialIssue created: ${issueNumber} for Production Order ${productionOrder.production_order_number}`);
    
    return createdIssue;
  } catch (error) {
    if (useTransaction && txn) {
      await txn.rollback();
    }
    console.error('Error in backflushing components:', error);
    throw error;
  }
};

/**
 * Post backflush MaterialIssue (similar to materialIssueController.post but simplified for backflush)
 */
exports.postBackflushMaterialIssue = async (issueId, userId, transaction = null) => {
  const issue = await db.MaterialIssue.findByPk(issueId, {
    include: [
      {
        model: db.MaterialIssueItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ]
      },
      { model: db.ProductionOrder, as: 'productionOrder' }
    ],
    transaction
  });

  if (!issue) {
    throw new Error('Material Issue not found');
  }

  // Generate Inventory Goods Issue number
  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')}`;
  };

  const movementNumber = await generateMovementNumber(issue.company_id);
  
  // Create Inventory Goods Issue
  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,
    cost_center_id: null,
    reference_number: issue.issue_number,
    reference_type: 'MATERIAL_ISSUE',
    status: 'POSTED',
    remarks: `Backflush Material Issue: ${issue.issue_number} for Production Order: ${issue.productionOrder?.production_order_number || ''}`,
    posted_at: new Date(),
    posted_by: userId,
    created_by: userId,
    updated_by: userId
  }, { transaction });

  // Process each item: reduce stock 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 - 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: 'BACKFLUSH',
      reference_id: issue.id,
      reference_number: issue.issue_number,
      storage_location: item.source_storage_location,
      quantity_in: 0,
      quantity_out: quantity,
      unit_price: unitPrice,
      remarks: `Backflush: ${issue.issue_number} - Auto-issued to production. Material: ${item.material?.material_number || item.material_id}`,
    }, 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 Material Issue status to POSTED (for backflush, materials are immediately consumed)
  await issue.update({
    status: 'POSTED',
    posted_at: new Date(),
    posted_by: userId,
    issued_by: userId
  }, { transaction });

  return { issue, inventoryGoodsIssue };
};

