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

/**
 * Calculate Available to Promise (ATP) stock for a material
 * Uses Inventory Stock Ledger to get the current balance quantity
 * ATP = Current Balance from Stock Ledger - Reserved Quantity (not yet delivered)
 * 
 * @param {number} materialId - Material ID
 * @param {number} plantId - Plant ID
 * @param {string} storageLocation - Storage location (optional)
 * @returns {Promise<Object>} Available quantity and related information
 */
const getAvailableStock = async (materialId, plantId, storageLocation = null) => {
  const replacements = { materialId, plantId };
  let storageCondition = '';
  if (storageLocation) {
    storageCondition = 'AND storage_location = :storageLocation';
    replacements.storageLocation = storageLocation;
  }

  // Get company_id from plant
  const plant = await db.Plant.findByPk(plantId);
  if (!plant) {
    throw new Error(`Plant with ID ${plantId} not found`);
  }
  const companyId = plant.company_id;

  // Primary source: Get current balance from inventory_stock_ledger table
  // The stock ledger is the single source of truth for inventory balances
  // Opening stock and all inventory movements create entries in this table
  let balanceQuantity = 0;
  let receipts = 0;
  let issues = 0;

  // Get the latest balance quantity from stock ledger
  balanceQuantity = await stockLedgerService.getCurrentBalance(
    companyId,
    plantId,
    materialId,
    storageLocation
  );

  // If storageLocation is not specified, also fetch the latest balance per storage location
  // so UI can show "available locations" for the material.
  let availableLocations = [];
  if (!storageLocation) {
    try {
      const locationRows = await db.sequelize.query(
        `
        SELECT t.storage_location, t.balance_quantity
        FROM (
          SELECT
            l.storage_location,
            l.balance_quantity,
            ROW_NUMBER() OVER (
              PARTITION BY COALESCE(l.storage_location, '')
              ORDER BY l.transaction_date DESC, l.id DESC
            ) AS rn
          FROM inventory_stock_ledger l
          WHERE l.company_id = :companyId
            AND l.plant_id = :plantId
            AND l.material_id = :materialId
        ) t
        WHERE t.rn = 1
          AND COALESCE(t.balance_quantity, 0) > 0
        ORDER BY t.balance_quantity DESC
        `,
        {
          replacements: { companyId, plantId, materialId },
          type: Sequelize.QueryTypes.SELECT,
        }
      );

      availableLocations = (locationRows || []).map((r) => ({
        storage_location: r.storage_location || null,
        balance_quantity: parseFloat(r.balance_quantity || 0),
      }));
    } catch (e) {
      // Non-fatal: availability calculation should still work
      // Log warning only in development or use proper logging service
      if (process.env.NODE_ENV === 'development') {
        console.warn('[ATP Service] Failed to load available locations:', e.message);
      }
      availableLocations = [];
    }
  }

  // Debug logging removed - use proper logging service if needed

  // Get totals for receipts and issues from ledger for reporting/debugging
  const ledgerTotalsResult = await db.sequelize.query(`
    SELECT 
      COALESCE(SUM(CASE WHEN transaction_type IN ('RECEIPT', 'TRANSFER_IN', 'RETURN', 'OPENING_BALANCE') THEN quantity_in ELSE 0 END), 0) as total_receipts,
      COALESCE(SUM(CASE WHEN transaction_type IN ('ISSUE', 'TRANSFER_OUT', 'SCRAP') THEN quantity_out ELSE 0 END), 0) as total_issues
    FROM inventory_stock_ledger
    WHERE material_id = :materialId
      AND plant_id = :plantId
      ${storageCondition}
  `, {
    replacements,
    type: Sequelize.QueryTypes.SELECT
  });

  receipts = parseFloat(ledgerTotalsResult[0]?.total_receipts || 0);
  issues = parseFloat(ledgerTotalsResult[0]?.total_issues || 0);

  // Note: If balanceQuantity is 0, it means either:
  // 1. No stock available (correct)
  // 2. No ledger entries exist yet (should not happen if opening stock creates ledger entries)
  // We don't need fallback to Goods Receipt/Issue since stock ledger is the source of truth

  // Get pending quantity from sales orders (quantity - delivered_quantity)
  const pendingResult = await db.sequelize.query(`
    SELECT COALESCE(SUM(soi.quantity - soi.delivered_quantity), 0) as total
    FROM sales_order_item soi
    INNER JOIN sales_order so ON soi.so_id = so.id
    WHERE soi.material_id = :materialId
      AND so.plant_id = :plantId
      AND so.status IN ('APPROVED', 'RELEASED', 'PARTIALLY_DELIVERED')
  `, {
    replacements: { materialId, plantId },
    type: Sequelize.QueryTypes.SELECT
  });
  const pendingQuantity = parseFloat(pendingResult[0]?.total || 0);

  // Get reserved quantity from stock reservations (Production Orders, Material Requests, etc.)
  const stockReservationService = require('./stockReservationService');
  const reservedQuantity = await stockReservationService.getTotalReservedQuantity(
    companyId,
    plantId,
    materialId,
    storageLocation
  );

  // ATP = Current Balance from Stock Ledger - Reserved Quantity (Sales Orders + Stock Reservations)
  const totalReserved = pendingQuantity + reservedQuantity;
  const availableStock = balanceQuantity - totalReserved;

  // Debug logging removed - use proper logging service if needed

  return {
    available: Math.max(0, availableStock), // Return 0 if negative
    receipts,
    issues,
    pendingQuantity, // Sales order reservations
    reserved: totalReserved, // Total reserved (Sales Orders + Stock Reservations)
    reservedFromSalesOrders: pendingQuantity,
    reservedFromProductionOrders: reservedQuantity,
    balanceQuantity: balanceQuantity, // Current balance from ledger
    locations: availableLocations, // latest positive balances per storage location (when storageLocation is null)
  };
};

/**
 * Check ATP for multiple materials
 * @param {Array} items - Array of {materialId, plantId, quantity, storageLocation}
 * @returns {Promise<Array>} Array of {materialId, availableQuantity, requestedQuantity, isAvailable}
 */
const checkATP = async (items) => {
  const results = [];

  for (const item of items) {
    const stockInfo = await getAvailableStock(
      item.material_id,
      item.plant_id,
      item.storage_location
    );

    const requestedQuantity = parseFloat(item.quantity || 0);
    const availableStock = stockInfo.available;
    const isAvailable = availableStock >= requestedQuantity;

    results.push({
      material_id: item.material_id,
      available_quantity: availableStock,
      requested_quantity: requestedQuantity,
      is_available: isAvailable,
      shortfall: isAvailable ? 0 : (requestedQuantity - availableStock),
      receipts: stockInfo.receipts,
      issues: stockInfo.issues,
      pending_quantity: stockInfo.pendingQuantity
    });
  }

  return results;
};

module.exports = {
  getAvailableStock,
  checkATP
};

