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

/**
 * Create or update batch master
 * 
 * @param {Object} batchData - Batch data
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} BatchMaster record
 */
exports.createOrUpdateBatch = async (batchData, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: batchData.company_id,
    plant_id: batchData.plant_id,
    material_id: batchData.material_id,
    batch_number: batchData.batch_number
  };

  const [batch, created] = await db.BatchMaster.findOrCreate({
    where,
    defaults: {
      ...batchData,
      initial_quantity: batchData.initial_quantity || batchData.current_quantity || 0,
      current_quantity: batchData.current_quantity || batchData.initial_quantity || 0,
      created_by: userId,
      updated_by: userId
    },
    ...options
  });

  if (!created) {
    // Update existing batch
    const updateData = {
      ...batchData,
      updated_by: userId
    };
    
    // Only update quantity if explicitly provided
    if (batchData.current_quantity !== undefined) {
      updateData.current_quantity = batchData.current_quantity;
    }
    
    await batch.update(updateData, options);
  }

  // Check if batch is expired
  if (batch.expiry_date) {
    const today = new Date();
    const expiryDate = new Date(batch.expiry_date);
    if (expiryDate < today && batch.status === 'ACTIVE') {
      await batch.update({ status: 'EXPIRED' }, options);
    }
  }

  return batch;
};

/**
 * Update batch quantity
 * 
 * @param {Integer} batchId - Batch ID
 * @param {Decimal} quantityChange - Quantity change (positive for increase, negative for decrease)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated BatchMaster record
 */
exports.updateBatchQuantity = async (batchId, quantityChange, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const batch = await db.BatchMaster.findByPk(batchId, options);
  if (!batch) {
    throw new Error('Batch not found');
  }

  const newQuantity = parseFloat(batch.current_quantity || 0) + parseFloat(quantityChange);
  
  if (newQuantity < 0) {
    throw new Error(`Insufficient batch quantity. Available: ${batch.current_quantity}, Required: ${Math.abs(quantityChange)}`);
  }

  await batch.update({ current_quantity: newQuantity }, options);

  // Update status if consumed
  if (newQuantity === 0 && batch.status === 'ACTIVE') {
    await batch.update({ status: 'CONSUMED' }, options);
  }

  return batch;
};

/**
 * Get batch by batch number
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Material ID
 * @param {String} batchNumber - Batch number
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} BatchMaster record
 */
exports.getBatch = async (companyId, plantId, materialId, batchNumber, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  return await db.BatchMaster.findOne({
    where: {
      company_id: companyId,
      plant_id: plantId,
      material_id: materialId,
      batch_number: batchNumber
    },
    include: [
      { model: db.Material, as: 'material' },
      { model: db.Plant, as: 'plant' }
    ],
    ...options
  });
};

/**
 * Get batches for a material
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Material ID
 * @param {String} status - Status filter (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of BatchMaster records
 */
exports.getBatchesForMaterial = async (companyId, plantId, materialId, status = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId
  };

  if (status) {
    where.status = status;
  }

  return await db.BatchMaster.findAll({
    where,
    include: [
      { model: db.Material, as: 'material' }
    ],
    order: [['expiry_date', 'ASC'], ['batch_number', 'ASC']],
    ...options
  });
};

/**
 * Get expired batches
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID (optional)
 * @param {Date} beforeDate - Get batches expiring before this date (optional, defaults to today)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of expired BatchMaster records
 */
exports.getExpiredBatches = async (companyId, plantId = null, beforeDate = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: companyId,
    expiry_date: {
      [Op.lte]: beforeDate || new Date()
    }
  };

  if (plantId) {
    where.plant_id = plantId;
  }

  return await db.BatchMaster.findAll({
    where,
    include: [
      { model: db.Material, as: 'material' },
      { model: db.Plant, as: 'plant' }
    ],
    order: [['expiry_date', 'ASC']],
    ...options
  });
};

/**
 * Create or update serial number tracking
 * 
 * @param {Object} serialData - Serial number data
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} SerialNumberTracking record
 */
exports.createOrUpdateSerial = async (serialData, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const [serial, created] = await db.SerialNumberTracking.findOrCreate({
    where: {
      serial_number: serialData.serial_number
    },
    defaults: {
      ...serialData,
      created_by: userId,
      updated_by: userId
    },
    ...options
  });

  if (!created) {
    await serial.update({
      ...serialData,
      updated_by: userId
    }, options);
  }

  return serial;
};

/**
 * Update serial number status
 * 
 * @param {String} serialNumber - Serial number
 * @param {String} status - New status
 * @param {String} referenceType - Reference type (optional)
 * @param {Integer} referenceId - Reference ID (optional)
 * @param {String} referenceNumber - Reference number (optional)
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated SerialNumberTracking record
 */
exports.updateSerialStatus = async (serialNumber, status, referenceType = null, referenceId = null, referenceNumber = null, userId = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const serial = await db.SerialNumberTracking.findOne({
    where: { serial_number: serialNumber },
    ...options
  });

  if (!serial) {
    throw new Error(`Serial number ${serialNumber} not found`);
  }

  const updateData = {
    current_status: status,
    updated_by: userId
  };

  if (referenceType) updateData.reference_type = referenceType;
  if (referenceId) updateData.reference_id = referenceId;
  if (referenceNumber) updateData.reference_number = referenceNumber;
  if (status === 'DELIVERED' && referenceId) {
    // Try to get customer from delivery or sales invoice
    // This will be handled by the calling function
  }

  updateData.transaction_date = new Date().toISOString().split('T')[0];

  await serial.update(updateData, options);

  return serial;
};

/**
 * Get serial number by serial number
 * 
 * @param {String} serialNumber - Serial number
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} SerialNumberTracking record
 */
exports.getSerial = async (serialNumber, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  return await db.SerialNumberTracking.findOne({
    where: { serial_number: serialNumber },
    include: [
      { model: db.Material, as: 'material' },
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.BusinessPartner, as: 'customer' }
    ],
    ...options
  });
};

/**
 * Get serial numbers for a material
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Material ID
 * @param {String} status - Status filter (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of SerialNumberTracking records
 */
exports.getSerialsForMaterial = async (companyId, plantId, materialId, status = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId
  };

  if (status) {
    where.current_status = status;
  }

  return await db.SerialNumberTracking.findAll({
    where,
    include: [
      { model: db.Material, as: 'material' }
    ],
    order: [['serial_number', 'ASC']],
    ...options
  });
};

/**
 * Get serial number traceability (history)
 * 
 * @param {String} serialNumber - Serial number
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Serial number with traceability information
 */
exports.getSerialTraceability = async (serialNumber, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const serial = await db.SerialNumberTracking.findOne({
    where: { serial_number: serialNumber },
    include: [
      { model: db.Material, as: 'material' },
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.BusinessPartner, as: 'customer' },
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' }
    ],
    ...options
  });

  if (!serial) {
    return null;
  }

  // Get transaction history from stock ledger
  const stockLedgerEntries = await db.InventoryStockLedger.findAll({
    where: {
      material_id: serial.material_id,
      batch_number: serial.batch_number || null,
      [Op.or]: [
        { reference_number: { [Op.like]: `%${serialNumber}%` } },
        { remarks: { [Op.like]: `%${serialNumber}%` } }
      ]
    },
    order: [['transaction_date', 'DESC'], ['created_at', 'DESC']],
    limit: 50,
    ...options
  });

  return {
    serial,
    history: stockLedgerEntries
  };
};

/**
 * Track batch/serial in transaction
 * Called when materials are received, issued, or transferred
 * 
 * @param {Integer} materialId - Material ID
 * @param {String} batchNumber - Batch number (optional)
 * @param {Array<String>} serialNumbers - Array of serial numbers (optional)
 * @param {String} transactionType - Transaction type (RECEIPT, ISSUE, etc.)
 * @param {String} referenceType - Reference type (GRN, MATERIAL_ISSUE, etc.)
 * @param {Integer} referenceId - Reference ID
 * @param {String} referenceNumber - Reference number
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {String} storageLocation - Storage location
 * @param {Decimal} quantity - Quantity
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Tracking result
 */
exports.trackBatchSerialInTransaction = async (
  materialId,
  batchNumber,
  serialNumbers,
  transactionType,
  referenceType,
  referenceId,
  referenceNumber,
  companyId,
  plantId,
  storageLocation,
  quantity,
  userId,
  transaction = null
) => {
  const options = transaction ? { transaction } : {};
  
  // Get material to check if batch/serial managed
  const material = await db.Material.findByPk(materialId, options);
  if (!material) {
    throw new Error('Material not found');
  }

  const result = {
    batchesUpdated: [],
    serialsUpdated: []
  };

  // Handle batch tracking
  if (material.is_batch_managed && batchNumber) {
    const quantityChange = transactionType === 'RECEIPT' || transactionType === 'TRANSFER_IN' 
      ? parseFloat(quantity) 
      : -parseFloat(quantity);

    // Get or create batch
    let batch = await exports.getBatch(companyId, plantId, materialId, batchNumber, transaction);
    
    if (!batch) {
      // Create new batch on receipt
      if (transactionType === 'RECEIPT') {
        batch = await exports.createOrUpdateBatch({
          company_id: companyId,
          plant_id: plantId,
          material_id: materialId,
          batch_number: batchNumber,
          current_quantity: parseFloat(quantity),
          storage_location: storageLocation,
          status: 'ACTIVE'
        }, userId, transaction);
        result.batchesUpdated.push(batch);
      } else {
        throw new Error(`Batch ${batchNumber} not found for material ${materialId}`);
      }
    } else {
      // Update batch quantity
      await exports.updateBatchQuantity(batch.id, quantityChange, transaction);
      result.batchesUpdated.push(batch);
    }
  }

  // Handle serial number tracking
  if (material.is_serial_managed && serialNumbers && serialNumbers.length > 0) {
    for (const serialNumber of serialNumbers) {
      let status = 'IN_STOCK';
      
      if (transactionType === 'RECEIPT' || transactionType === 'TRANSFER_IN') {
        status = 'IN_STOCK';
      } else if (transactionType === 'ISSUE' || transactionType === 'TRANSFER_OUT') {
        status = 'ISSUED';
      } else if (referenceType === 'PRODUCTION_ORDER') {
        status = 'IN_PRODUCTION';
      } else if (referenceType === 'DELIVERY' || referenceType === 'SALES_INVOICE') {
        status = 'DELIVERED';
      } else if (referenceType === 'SCRAP') {
        status = 'SCRAPPED';
      } else if (referenceType === 'RETURN') {
        status = 'RETURNED';
      }

      const serial = await exports.createOrUpdateSerial({
        company_id: companyId,
        plant_id: plantId,
        material_id: materialId,
        serial_number: serialNumber,
        batch_number: batchNumber || null,
        current_status: status,
        storage_location: storageLocation,
        reference_type: referenceType,
        reference_id: referenceId,
        reference_number: referenceNumber,
        transaction_date: new Date().toISOString().split('T')[0]
      }, userId, transaction);

      result.serialsUpdated.push(serial);
    }
  }

  return result;
};

/**
 * Get available batches for a material (with stock)
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Material ID
 * @param {String} storageLocation - Storage location (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of available BatchMaster records
 */
exports.getAvailableBatches = async (companyId, plantId, materialId, storageLocation = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId,
    status: { [Op.in]: ['ACTIVE'] },
    current_quantity: { [Op.gt]: 0 }
  };

  if (storageLocation) {
    where.storage_location = storageLocation;
  }

  return await db.BatchMaster.findAll({
    where,
    include: [
      { model: db.Material, as: 'material' }
    ],
    order: [
      ['expiry_date', 'ASC'], // FIFO - First In First Out (earliest expiry first)
      ['batch_number', 'ASC']
    ],
    ...options
  });
};

/**
 * Get available serial numbers for a material
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Material ID
 * @param {String} storageLocation - Storage location (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of available SerialNumberTracking records
 */
exports.getAvailableSerials = async (companyId, plantId, materialId, storageLocation = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId,
    current_status: 'IN_STOCK'
  };

  if (storageLocation) {
    where.storage_location = storageLocation;
  }

  return await db.SerialNumberTracking.findAll({
    where,
    include: [
      { model: db.Material, as: 'material' }
    ],
    order: [['serial_number', 'ASC']],
    ...options
  });
};

