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

/**
 * Get current stock balance for a material
 */
exports.getCurrentBalance = async (companyId, plantId, materialId, storageLocation = null) => {
  // If storageLocation is provided, return balance for that specific location.
  // If storageLocation is NOT provided, return TOTAL balance across all storage locations.
  // Note: inventory_stock_ledger maintains running balance per storage_location; the correct total
  // is the SUM of the latest balance per storage_location (not simply the latest row overall).
  const whereBase = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId,
  };

  let balance = 0;

  if (storageLocation) {
    const latestEntry = await db.InventoryStockLedger.findOne({
      where: { ...whereBase, storage_location: storageLocation },
      order: [['transaction_date', 'DESC'], ['id', 'DESC']],
    });
    balance = latestEntry ? parseFloat(latestEntry.balance_quantity || 0) : 0;
  } else {
    // Total across locations:
    // sum of the latest balance row per storage_location, where "latest" is by (transaction_date DESC, id DESC).
    // This is important because IDs may not strictly represent the most recent business date.
    const rows = await db.sequelize.query(
      `
      SELECT COALESCE(SUM(t.balance_quantity), 0) AS total_balance
      FROM (
        SELECT
          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
      `,
      {
        replacements: { companyId, plantId, materialId },
        type: db.sequelize.QueryTypes.SELECT,
      }
    );
    balance = parseFloat(rows?.[0]?.total_balance || 0);
  }

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

  return balance;
};

/**
 * Create stock ledger entry
 */
exports.createLedgerEntry = async (entryData, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  // For OPENING_BALANCE, set balance directly without calculating from current balance
  if (entryData.transaction_type === 'OPENING_BALANCE') {
    const quantityIn = parseFloat(entryData.quantity_in || 0);
    const ledgerEntry = await db.InventoryStockLedger.create({
      ...entryData,
      balance_quantity: quantityIn, // Opening balance sets the initial balance
      created_at: new Date(),
      updated_at: new Date()
    }, options);

    return ledgerEntry;
  }

  // For other transaction types, calculate balance from current balance
  // Get current balance
  const currentBalance = await exports.getCurrentBalance(
    entryData.company_id,
    entryData.plant_id,
    entryData.material_id,
    entryData.storage_location,
    transaction
  );

  // Calculate new balance
  const quantityIn = parseFloat(entryData.quantity_in || 0);
  const quantityOut = parseFloat(entryData.quantity_out || 0);
  const newBalance = currentBalance + quantityIn - quantityOut;

  // Prevent negative stock for ISSUE transactions (unless it's an adjustment that allows negative)
  // Allow negative for: ADJUSTMENT, OPENING_BALANCE (handled above), TRANSFER_OUT (if configured)
  const preventNegative = entryData.transaction_type === 'ISSUE' || 
                         (entryData.transaction_type === 'TRANSFER_OUT' && entryData.prevent_negative !== false);

  if (preventNegative && newBalance < 0) {
    // Validate using centralized service for better error messages
    if (quantityOut > 0) {
      const validation = await negativeStockPreventionService.validateStockIssue({
        companyId: entryData.company_id,
        plantId: entryData.plant_id,
        materialId: entryData.material_id,
        storageLocation: entryData.storage_location || null,
        quantityToIssue: quantityOut,
        operation: entryData.reference_type || 'Stock Ledger Entry',
        referenceNumber: entryData.reference_number || null,
        transaction
      });

      if (!validation.isValid) {
        throw new Error(validation.errorMessage);
      }
    }
    
    // If validation service didn't throw, but balance is still negative, throw generic error
    // This should not happen if validation service works correctly
    throw new Error(
      `Negative stock prevention: Cannot create ${entryData.transaction_type} transaction. ` +
      `Material ID: ${entryData.material_id}, Storage Location: ${entryData.storage_location || 'N/A'}, ` +
      `Current Balance: ${currentBalance}, Quantity Out: ${quantityOut}, Resulting Balance: ${newBalance}`
    );
  }

  // Create ledger entry
  const ledgerEntry = await db.InventoryStockLedger.create({
    ...entryData,
    balance_quantity: newBalance,
    created_at: new Date(),
    updated_at: new Date()
  }, options);

  return ledgerEntry;
};

/**
 * Get stock ledger with filters
 */
exports.getStockLedger = async (filters = {}) => {
  const {
    companyId,
    plantId,
    materialId,
    storageLocation,
    transactionType,
    referenceType,
    referenceId,
    startDate,
    endDate,
    batchNumber,
    lotNumber,
    page = 1,
    limit = 100
  } = filters;

  const where = {};

  if (companyId) where.company_id = companyId;
  if (plantId) where.plant_id = plantId;
  if (materialId) where.material_id = materialId;
  if (storageLocation) where.storage_location = storageLocation;
  if (transactionType) where.transaction_type = transactionType;
  if (referenceType) {
    // Backward compatibility: older entries used 'GoodsReceiptNote' for GRN reference type
    if (String(referenceType).toUpperCase() === 'GRN') {
      where.reference_type = { [Op.in]: ['GRN', 'GoodsReceiptNote'] };
    } else {
      where.reference_type = referenceType;
    }
  }
  if (referenceId) where.reference_id = referenceId;
  if (batchNumber) where.batch_number = batchNumber;
  if (lotNumber) where.lot_number = lotNumber;

  if (startDate || endDate) {
    where.transaction_date = {};
    if (startDate) where.transaction_date[Op.gte] = startDate;
    if (endDate) where.transaction_date[Op.lte] = endDate;
  }

  const offset = (page - 1) * limit;

  const { count, rows } = await db.InventoryStockLedger.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] }
    ],
    order: [['transaction_date', 'DESC'], ['id', 'DESC']],
    limit: parseInt(limit),
    offset: parseInt(offset)
  });

  return {
    items: rows,
    total: count,
    page: parseInt(page),
    limit: parseInt(limit),
    totalPages: Math.ceil(count / limit)
  };
};

/**
 * Get current stock balances for multiple materials
 */
exports.getCurrentBalances = async (companyId, plantId, materialIds = [], storageLocation = null) => {
  const where = {
    company_id: companyId,
    plant_id: plantId
  };

  if (materialIds.length > 0) {
    where.material_id = { [Op.in]: materialIds };
  }

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

  // Get latest balance for each material
  const balances = {};
  
  for (const materialId of materialIds) {
    const materialWhere = { ...where, material_id: materialId };
    const latestEntry = await db.InventoryStockLedger.findOne({
      where: materialWhere,
      order: [['transaction_date', 'DESC'], ['id', 'DESC']]
    });
    
    balances[materialId] = latestEntry ? parseFloat(latestEntry.balance_quantity || 0) : 0;
  }

  return balances;
};

/**
 * Get stock movement summary
 */
exports.getStockMovementSummary = async (companyId, plantId, materialId, startDate, endDate) => {
  const where = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId
  };

  if (startDate || endDate) {
    where.transaction_date = {};
    if (startDate) where.transaction_date[Op.gte] = startDate;
    if (endDate) where.transaction_date[Op.lte] = endDate;
  }

  const summary = await db.InventoryStockLedger.findAll({
    where,
    attributes: [
      [db.sequelize.fn('SUM', db.sequelize.col('quantity_in')), 'total_in'],
      [db.sequelize.fn('SUM', db.sequelize.col('quantity_out')), 'total_out']
    ],
    raw: true
  });

  return {
    totalIn: parseFloat(summary[0]?.total_in || 0),
    totalOut: parseFloat(summary[0]?.total_out || 0),
    netMovement: parseFloat(summary[0]?.total_in || 0) - parseFloat(summary[0]?.total_out || 0)
  };
};

/**
 * Recalculate running balances in chronological order.
 * Useful after historical backfills.
 */
exports.recalculateBalances = async ({ companyId = null, plantId = null, materialId = null, storageLocation = null, transaction = null } = {}) => {
  const where = {};
  if (companyId) where.company_id = companyId;
  if (plantId) where.plant_id = plantId;
  if (materialId) where.material_id = materialId;
  if (storageLocation) where.storage_location = storageLocation;

  const rows = await db.InventoryStockLedger.findAll({
    where,
    attributes: [
      'id',
      'company_id',
      'plant_id',
      'material_id',
      'storage_location',
      'quantity_in',
      'quantity_out',
      'balance_quantity',
      'transaction_date'
    ],
    order: [['transaction_date', 'ASC'], ['id', 'ASC']],
    transaction: transaction || undefined
  });

  const runningByScope = new Map();
  let updated = 0;

  for (const row of rows) {
    const scopeKey = [
      row.company_id,
      row.plant_id,
      row.material_id,
      row.storage_location || ''
    ].join(':');

    const previous = runningByScope.get(scopeKey) || 0;
    const next = previous + parseFloat(row.quantity_in || 0) - parseFloat(row.quantity_out || 0);
    if (parseFloat(row.balance_quantity || 0) !== next) {
      await row.update({ balance_quantity: next }, { transaction: transaction || undefined });
      updated += 1;
    }
    runningByScope.set(scopeKey, next);
  }

  return { total: rows.length, updated };
};
