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

/**
 * Create stock reservations for Production Order components
 * Called when Production Order status changes to RELEASED
 * 
 * @param {Object} productionOrder - Production Order object with components
 * @param {Integer} userId - User ID creating the reservation
 * @param {Object} transaction - Sequelize transaction (optional)
 * @param {number} [quantityScale=1] - Multiply reserved qty (0..1) when batch planning releases partial order qty
 * @returns {Promise<Array>} Array of created reservation records
 */
exports.createReservationsForProductionOrder = async (
  productionOrder,
  userId,
  transaction = null,
  quantityScale = 1
) => {
  if (!productionOrder.components || productionOrder.components.length === 0) {
    return [];
  }

  const scale = Math.max(0, Math.min(1, parseFloat(quantityScale) || 0));
  if (scale <= 0) {
    return [];
  }

  const reservations = [];
  const options = transaction ? { transaction } : {};

  for (const component of productionOrder.components) {
    // Skip if component doesn't require stock (e.g., service items)
    if (!component.material_id || component.required_quantity <= 0) {
      continue;
    }

    // Get storage location from MaterialPlant or component
    let storageLocation = component.storage_location || null;
    if (!storageLocation && component.material?.materialPlants) {
      const materialPlant = component.material.materialPlants.find(
        mp => mp.plant_id === productionOrder.plant_id
      );
      if (materialPlant) {
        storageLocation = materialPlant.storage_location || null;
      }
    }

    const reqQty = parseFloat(component.required_quantity || 0) * scale;

    // Create reservation
    const reservation = await db.StockReservation.create({
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      material_id: component.material_id,
      storage_location: storageLocation,
      reserved_quantity: reqQty,
      issued_quantity: 0,
      available_quantity: reqQty,
      reference_type: 'PRODUCTION_ORDER',
      reference_id: productionOrder.id,
      reference_number: productionOrder.production_order_number,
      reservation_date: new Date().toISOString().split('T')[0],
      required_date: productionOrder.start_date || null,
      status: 'ACTIVE',
      remarks: `Reserved for Production Order: ${productionOrder.production_order_number}, Component: ${component.material?.material_number || component.material_id}`,
      created_by: userId,
      updated_by: userId
    }, options);

    reservations.push(reservation);
  }

  return reservations;
};

/**
 * Increase reservations when more order quantity is released in a follow-up batch (PO already RELEASED).
 * Adds (additionalReleasedQty / order_quantity) * component.required_quantity per component.
 */
exports.addReservationsForAdditionalRelease = async (
  productionOrder,
  additionalReleasedQty,
  userId,
  transaction = null
) => {
  const options = transaction ? { transaction } : {};
  const orderQty = parseFloat(productionOrder.order_quantity || 0);
  const addRelease = parseFloat(additionalReleasedQty || 0);
  if (!productionOrder.components || productionOrder.components.length === 0 || orderQty <= 0 || addRelease <= 0) {
    return [];
  }

  const ratio = addRelease / orderQty;
  const updated = [];

  for (const component of productionOrder.components) {
    if (!component.material_id || component.required_quantity <= 0) continue;

    const delta = parseFloat(component.required_quantity || 0) * ratio;
    if (delta <= 0) continue;

    let storageLocation = component.storage_location || null;
    if (!storageLocation && component.material?.materialPlants) {
      const materialPlant = component.material.materialPlants.find(
        (mp) => mp.plant_id === productionOrder.plant_id
      );
      if (materialPlant) {
        storageLocation = materialPlant.storage_location || null;
      }
    }

    const baseWhere = {
      reference_type: 'PRODUCTION_ORDER',
      reference_id: productionOrder.id,
      material_id: component.material_id,
      status: { [Op.in]: ['ACTIVE', 'PARTIALLY_ISSUED', 'FULLY_ISSUED'] }
    };

    let reservation = await db.StockReservation.findOne({
      where: storageLocation != null ? { ...baseWhere, storage_location: storageLocation } : baseWhere,
      order: [['id', 'ASC']],
      ...options
    });

    if (!reservation && storageLocation != null) {
      reservation = await db.StockReservation.findOne({
        where: baseWhere,
        order: [['id', 'ASC']],
        ...options
      });
    }

    if (reservation) {
      const newReserved = parseFloat(reservation.reserved_quantity || 0) + delta;
      const issued = parseFloat(reservation.issued_quantity || 0);
      const newAvailable = Math.max(0, newReserved - issued);
      let newStatus = reservation.status;
      if (issued <= 0) {
        newStatus = 'ACTIVE';
      } else if (newAvailable <= 0) {
        newStatus = 'FULLY_ISSUED';
      } else {
        newStatus = 'PARTIALLY_ISSUED';
      }

      await reservation.update(
        {
          reserved_quantity: newReserved,
          available_quantity: newAvailable,
          status: newStatus,
          updated_by: userId,
          updated_at: new Date()
        },
        options
      );
      updated.push(reservation);
    } else {
      const created = await db.StockReservation.create(
        {
          company_id: productionOrder.company_id,
          plant_id: productionOrder.plant_id,
          material_id: component.material_id,
          storage_location: storageLocation,
          reserved_quantity: delta,
          issued_quantity: 0,
          available_quantity: delta,
          reference_type: 'PRODUCTION_ORDER',
          reference_id: productionOrder.id,
          reference_number: productionOrder.production_order_number,
          reservation_date: new Date().toISOString().split('T')[0],
          required_date: productionOrder.start_date || null,
          status: 'ACTIVE',
          remarks: `Additional batch release — Production Order: ${productionOrder.production_order_number}`,
          created_by: userId,
          updated_by: userId
        },
        options
      );
      updated.push(created);
    }
  }

  return updated;
};

/**
 * Update reservation when material is issued
 * Called when Material Issue is posted
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {Integer} materialId - Material ID
 * @param {Number} issuedQuantity - Quantity issued
 * @param {String} storageLocation - Storage location (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated reservation
 */
exports.updateReservationOnIssue = async (productionOrderId, materialId, issuedQuantity, storageLocation = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  // Find active reservation for this Production Order and material
  const whereClause = {
    reference_type: 'PRODUCTION_ORDER',
    reference_id: productionOrderId,
    material_id: materialId,
    status: { [Op.in]: ['ACTIVE', 'PARTIALLY_ISSUED'] }
  };

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

  const reservation = await db.StockReservation.findOne({ where: whereClause, ...options });

  if (!reservation) {
    console.warn(`No active reservation found for Production Order ${productionOrderId}, Material ${materialId}`);
    return null;
  }

  const newIssuedQuantity = parseFloat(reservation.issued_quantity || 0) + parseFloat(issuedQuantity || 0);
  const newAvailableQuantity = parseFloat(reservation.reserved_quantity || 0) - newIssuedQuantity;

  // Determine new status
  let newStatus = reservation.status;
  if (newAvailableQuantity <= 0) {
    newStatus = 'FULLY_ISSUED';
  } else if (newIssuedQuantity > 0 && newIssuedQuantity < parseFloat(reservation.reserved_quantity || 0)) {
    newStatus = 'PARTIALLY_ISSUED';
  }

  await reservation.update({
    issued_quantity: newIssuedQuantity,
    available_quantity: Math.max(0, newAvailableQuantity),
    status: newStatus,
    updated_at: new Date()
  }, options);

  return reservation;
};

/**
 * Cancel all reservations for a Production Order
 * Called when Production Order is cancelled
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {Integer} userId - User ID cancelling the reservation
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Number>} Number of reservations cancelled
 */
exports.cancelReservationsForProductionOrder = async (productionOrderId, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const [updatedCount] = await db.StockReservation.update(
    {
      status: 'CANCELLED',
      cancelled_at: new Date(),
      cancelled_by: userId,
      updated_at: new Date()
    },
    {
      where: {
        reference_type: 'PRODUCTION_ORDER',
        reference_id: productionOrderId,
        status: { [Op.in]: ['ACTIVE', 'PARTIALLY_ISSUED'] }
      },
      ...options
    }
  );

  return updatedCount;
};

/**
 * Get total reserved quantity for a material
 * Used by ATP service to calculate available stock
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID
 * @param {Integer} materialId - Material ID
 * @param {String} storageLocation - Storage location (optional, null for total)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Number>} Total reserved quantity
 */
exports.getTotalReservedQuantity = async (companyId, plantId, materialId, storageLocation = null, transaction = null) => {
  const whereClause = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId,
    status: { [Op.in]: ['ACTIVE', 'PARTIALLY_ISSUED'] }
  };

  if (storageLocation) {
    whereClause.storage_location = storageLocation;
  } else {
    // If storage location is null, include reservations with null storage_location
    // (reservations that can be fulfilled from any location)
    whereClause[Op.or] = [
      { storage_location: null },
      { storage_location: storageLocation }
    ];
  }

  const options = transaction ? { transaction } : {};

  const result = await db.StockReservation.findAll({
    where: whereClause,
    attributes: [
      [db.sequelize.fn('SUM', db.sequelize.col('available_quantity')), 'total_reserved']
    ],
    raw: true,
    ...options
  });

  return parseFloat(result[0]?.total_reserved || 0);
};

/**
 * Get reservations for a Production Order
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of reservation records
 */
exports.getReservationsForProductionOrder = async (productionOrderId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  return await db.StockReservation.findAll({
    where: {
      reference_type: 'PRODUCTION_ORDER',
      reference_id: productionOrderId
    },
    include: [
      { model: db.Material, as: 'material' },
      { model: db.Plant, as: 'plant' }
    ],
    order: [['material_id', 'ASC']],
    ...options
  });
};

/**
 * Release reservation when material is fully issued
 * This is called automatically when issued_quantity >= reserved_quantity
 * 
 * @param {Integer} reservationId - Reservation ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated reservation
 */
exports.releaseReservation = async (reservationId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const reservation = await db.StockReservation.findByPk(reservationId, options);

  if (!reservation) {
    throw new Error(`Reservation ${reservationId} not found`);
  }

  await reservation.update({
    status: 'FULLY_ISSUED',
    updated_at: new Date()
  }, options);

  return reservation;
};

