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

/**
 * Get substitution rules for a material
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Original material ID
 * @param {Date} effectiveDate - Date to check validity (optional, defaults to today)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of MaterialSubstitutionMaster records
 */
exports.getSubstitutionRules = async (companyId, plantId, materialId, effectiveDate = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const checkDate = effectiveDate || new Date();
  const dateStr = checkDate.toISOString().split('T')[0];

  const where = {
    company_id: companyId,
    plant_id: plantId,
    original_material_id: materialId,
    status: 'ACTIVE',
    [Op.or]: [
      { effective_from: null },
      { effective_from: { [Op.lte]: dateStr } }
    ],
    [Op.and]: [
      {
        [Op.or]: [
          { effective_to: null },
          { effective_to: { [Op.gte]: dateStr } }
        ]
      }
    ]
  };

  return await db.MaterialSubstitutionMaster.findAll({
    where,
    include: [
      { model: db.Material, as: 'originalMaterial' },
      { model: db.Material, as: 'substituteMaterial' }
    ],
    order: [['substitution_ratio', 'ASC']],
    ...options
  });
};

/**
 * Create a substitution transaction
 * 
 * @param {Object} substitutionData - Substitution data
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} MaterialSubstitutionTransaction record
 */
exports.createSubstitutionTransaction = async (substitutionData, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  // Check if approval is required
  let approvalStatus = 'NOT_REQUIRED';
  if (substitutionData.substitution_master_id) {
    const master = await db.MaterialSubstitutionMaster.findByPk(substitutionData.substitution_master_id, options);
    if (master && master.requires_approval) {
      approvalStatus = 'PENDING';
    }
  }

  const transactionRecord = await db.MaterialSubstitutionTransaction.create({
    ...substitutionData,
    approval_status: approvalStatus,
    substitution_date: substitutionData.substitution_date || new Date().toISOString().split('T')[0],
    created_by: userId,
    updated_by: userId
  }, options);

  return transactionRecord;
};

/**
 * Apply substitution to Production Order Component
 * 
 * @param {Integer} componentId - ProductionOrderComponent ID
 * @param {Integer} substituteMaterialId - Substitute material ID
 * @param {Decimal} substitutionRatio - Substitution ratio (optional, will use from master if available)
 * @param {String} reason - Reason for substitution
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated component and substitution transaction
 */
exports.substituteProductionOrderComponent = async (componentId, substituteMaterialId, substitutionRatio = null, reason = null, userId = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const component = await db.ProductionOrderComponent.findByPk(componentId, {
    include: [
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.Material, as: 'material' }
    ],
    ...options
  });

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

  if (component.is_substituted) {
    throw new Error('Component has already been substituted');
  }

  const originalMaterialId = component.material_id;
  const originalQuantity = parseFloat(component.required_quantity || 0);

  // Get substitution rule if available
  let substitutionMaster = null;
  if (component.productionOrder) {
    const rules = await exports.getSubstitutionRules(
      component.productionOrder.company_id,
      component.productionOrder.plant_id,
      originalMaterialId,
      null,
      transaction
    );
    substitutionMaster = rules.find(r => r.substitute_material_id === substituteMaterialId);
  }

  // Use ratio from master if available, otherwise use provided ratio or default to 1
  const effectiveRatio = substitutionRatio || (substitutionMaster ? parseFloat(substitutionMaster.substitution_ratio) : 1.0);
  const substituteQuantity = originalQuantity * effectiveRatio;

  // Create substitution transaction
  const substitutionTransaction = await exports.createSubstitutionTransaction({
    company_id: component.productionOrder.company_id,
    plant_id: component.productionOrder.plant_id,
    substitution_master_id: substitutionMaster ? substitutionMaster.id : null,
    reference_type: 'PRODUCTION_ORDER',
    reference_id: component.production_order_id,
    reference_item_id: componentId,
    original_material_id: originalMaterialId,
    substitute_material_id: substituteMaterialId,
    original_quantity: originalQuantity,
    substitute_quantity: substituteQuantity,
    substitution_ratio: effectiveRatio,
    substitution_reason: reason
  }, userId, transaction);

  // Update component
  await component.update({
    material_id: substituteMaterialId,
    original_material_id: originalMaterialId,
    required_quantity: substituteQuantity,
    is_substituted: true,
    substitution_transaction_id: substitutionTransaction.id
  }, options);

  return {
    component,
    substitutionTransaction
  };
};

/**
 * Apply substitution to Material Request Item
 * 
 * @param {Integer} itemId - MaterialRequestItem ID
 * @param {Integer} substituteMaterialId - Substitute material ID
 * @param {Decimal} substitutionRatio - Substitution ratio (optional)
 * @param {String} reason - Reason for substitution
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated item and substitution transaction
 */
exports.substituteMaterialRequestItem = async (itemId, substituteMaterialId, substitutionRatio = null, reason = null, userId = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const item = await db.MaterialRequestItem.findByPk(itemId, {
    include: [
      { model: db.MaterialRequest, as: 'request' },
      { model: db.Material, as: 'material' }
    ],
    ...options
  });

  if (!item) {
    throw new Error('Material Request Item not found');
  }

  if (item.is_substituted) {
    throw new Error('Item has already been substituted');
  }

  const originalMaterialId = item.material_id;
  const originalQuantity = parseFloat(item.required_quantity || 0);

  // Get substitution rule if available
  let substitutionMaster = null;
  if (item.request) {
    const rules = await exports.getSubstitutionRules(
      item.request.company_id,
      item.request.plant_id,
      originalMaterialId,
      null,
      transaction
    );
    substitutionMaster = rules.find(r => r.substitute_material_id === substituteMaterialId);
  }

  // Use ratio from master if available, otherwise use provided ratio or default to 1
  const effectiveRatio = substitutionRatio || (substitutionMaster ? parseFloat(substitutionMaster.substitution_ratio) : 1.0);
  const substituteQuantity = originalQuantity * effectiveRatio;

  // Create substitution transaction
  const substitutionTransaction = await exports.createSubstitutionTransaction({
    company_id: item.request.company_id,
    plant_id: item.request.plant_id,
    substitution_master_id: substitutionMaster ? substitutionMaster.id : null,
    reference_type: 'MATERIAL_REQUEST',
    reference_id: item.request_id,
    reference_item_id: itemId,
    original_material_id: originalMaterialId,
    substitute_material_id: substituteMaterialId,
    original_quantity: originalQuantity,
    substitute_quantity: substituteQuantity,
    substitution_ratio: effectiveRatio,
    substitution_reason: reason
  }, userId, transaction);

  // Update item
  await item.update({
    material_id: substituteMaterialId,
    original_material_id: originalMaterialId,
    required_quantity: substituteQuantity,
    is_substituted: true,
    substitution_transaction_id: substitutionTransaction.id
  }, options);

  return {
    item,
    substitutionTransaction
  };
};

/**
 * Approve or reject substitution
 * 
 * @param {Integer} transactionId - MaterialSubstitutionTransaction ID
 * @param {String} action - 'APPROVE' or 'REJECT'
 * @param {String} remarks - Approval/rejection remarks
 * @param {Integer} userId - User ID (approver)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated substitution transaction
 */
exports.approveSubstitution = async (transactionId, action, remarks = null, userId = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const substitutionTransaction = await db.MaterialSubstitutionTransaction.findByPk(transactionId, options);

  if (!substitutionTransaction) {
    throw new Error('Substitution transaction not found');
  }

  if (substitutionTransaction.approval_status !== 'PENDING') {
    throw new Error(`Substitution is already ${substitutionTransaction.approval_status}`);
  }

  const approvalStatus = action === 'APPROVE' ? 'APPROVED' : 'REJECTED';

  await substitutionTransaction.update({
    approval_status: approvalStatus,
    approved_by: userId,
    approved_at: new Date(),
    approval_remarks: remarks,
    updated_by: userId
  }, options);

  // If rejected, revert the substitution
  if (action === 'REJECT') {
    if (substitutionTransaction.reference_type === 'PRODUCTION_ORDER') {
      const component = await db.ProductionOrderComponent.findByPk(substitutionTransaction.reference_item_id, options);
      if (component && component.is_substituted) {
        await component.update({
          material_id: substitutionTransaction.original_material_id,
          required_quantity: substitutionTransaction.original_quantity,
          is_substituted: false,
          substitution_transaction_id: null
        }, options);
      }
    } else if (substitutionTransaction.reference_type === 'MATERIAL_REQUEST') {
      const item = await db.MaterialRequestItem.findByPk(substitutionTransaction.reference_item_id, options);
      if (item && item.is_substituted) {
        await item.update({
          material_id: substitutionTransaction.original_material_id,
          required_quantity: substitutionTransaction.original_quantity,
          is_substituted: false,
          substitution_transaction_id: null
        }, options);
      }
    }
  }

  return substitutionTransaction;
};

/**
 * Get substitution transactions
 * 
 * @param {Object} filters - Filter criteria
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of MaterialSubstitutionTransaction records
 */
exports.getSubstitutionTransactions = async (filters = {}, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {};
  
  if (filters.companyId) where.company_id = filters.companyId;
  if (filters.plantId) where.plant_id = filters.plantId;
  if (filters.referenceType) where.reference_type = filters.referenceType;
  if (filters.referenceId) where.reference_id = filters.referenceId;
  if (filters.approvalStatus) where.approval_status = filters.approvalStatus;
  if (filters.status) where.status = filters.status;
  if (filters.originalMaterialId) where.original_material_id = filters.originalMaterialId;
  if (filters.substituteMaterialId) where.substitute_material_id = filters.substituteMaterialId;

  return await db.MaterialSubstitutionTransaction.findAll({
    where,
    include: [
      { model: db.Material, as: 'originalMaterial' },
      { model: db.Material, as: 'substituteMaterial' },
      { model: db.MaterialSubstitutionMaster, as: 'substitutionMaster' },
      { model: db.User, as: 'approvedByUser' }
    ],
    order: [['substitution_date', 'DESC'], ['created_at', 'DESC']],
    ...options
  });
};

