/**
 * Sales Service
 * Business logic for sales and POS operations
 */

// Import Sale and related models
const { Sale, SaleItem, Product, User, Inventory, InventoryItem, InventoryMovement, Discount } = require('../../../models');
// Import custom error classes
const { NotFoundError, ValidationError, InventoryError } = require('../../../utils/errors');
// Import logger for logging
const logger = require('../../../utils/logger');
// Import Sequelize operators and transaction
const { Op } = require('sequelize');
const { sequelize } = require('../../../models');
// Import discount service for applying discounts
const discountService = require('../../discounts/services');
// Import stock checking service for availability checks
const stockCheckingService = require('../../inventory/services/stockChecking');
// Import pricing service for getting product prices
const pricingService = require('../../pricing/services');

/**
 * Generate unique invoice number
 * Generates invoice number in format: INV-YYYYMMDD-XXXXX (e.g., INV-20241216-00001)
 * @returns {Promise<string>} Generated invoice number
 */
const generateInvoiceNumber = async () => {
  // Get current date components
  const now = new Date(); // Current date
  const year = now.getFullYear(); // Year (YYYY)
  const month = String(now.getMonth() + 1).padStart(2, '0'); // Month (MM)
  const day = String(now.getDate()).padStart(2, '0'); // Day (DD)
  const datePrefix = `${year}${month}${day}`; // Date prefix (YYYYMMDD)
  
  // Generate base invoice number format
  const baseInvoiceNo = `INV-${datePrefix}-`; // Base format: INV-YYYYMMDD-
  
  // Find the latest invoice number with same date prefix
  const latestSale = await Sale.findOne({
    where: {
      invoice_no: {
        [Op.like]: `${baseInvoiceNo}%`, // Match invoices with same date prefix
      },
    },
    order: [['invoice_no', 'DESC']], // Order by invoice_no descending
  });
  
  // Determine sequence number
  let sequence = 1; // Default sequence number
  if (latestSale && latestSale.invoice_no) {
    // Extract sequence number from latest invoice
    const sequencePart = latestSale.invoice_no.split('-')[2]; // Get sequence part (XXXXX)
    const lastSequence = parseInt(sequencePart, 10) || 0; // Parse to integer
    sequence = lastSequence + 1; // Increment sequence
  }
  
  // Format sequence number with leading zeros (5 digits)
  const sequenceStr = String(sequence).padStart(5, '0'); // Pad with zeros
  
  // Generate final invoice number
  const invoiceNo = `${baseInvoiceNo}${sequenceStr}`; // Final invoice number
  
  // Return invoice number
  return invoiceNo;
};

/**
 * Calculate item totals
 * Calculates line total for a sale item (tax removed)
 * @param {number} quantity - Item quantity
 * @param {number} unitPrice - Unit price
 * @returns {Object} Calculated totals
 */
const calculateItemTotals = (quantity, unitPrice) => {
  // Calculate line total (quantity * unit price)
  const lineTotal = parseFloat(quantity) * parseFloat(unitPrice); // Line total
  
  // Return calculated totals
  return {
    lineTotal: parseFloat(lineTotal.toFixed(2)), // Line total rounded to 2 decimals
  };
};

/**
 * Calculate sale totals
 * Calculates subtotal, discount, and total for a sale (tax removed)
 * @param {Array} items - Array of sale items with calculated totals
 * @param {number} discountAmount - Optional discount amount (default: 0)
 * @returns {Object} Calculated sale totals
 */
const calculateSaleTotals = (items, discountAmount = 0) => {
  // Calculate subtotal (sum of all line totals)
  const subtotal = items.reduce((sum, item) => sum + item.lineTotal, 0); // Sum line totals
  
  // Calculate total (subtotal - discount)
  const total = subtotal - parseFloat(discountAmount || 0); // Total amount with discount
  
  // Ensure total is not negative
  const finalTotal = Math.max(0, total); // Ensure total is at least 0
  
  // Return calculated totals
  return {
    subtotal: parseFloat(subtotal.toFixed(2)), // Subtotal rounded to 2 decimals
    discount_amount: parseFloat((discountAmount || 0).toFixed(2)), // Discount amount rounded to 2 decimals
    total: parseFloat(finalTotal.toFixed(2)), // Total rounded to 2 decimals
  };
};

// Tax calculation removed - tax is no longer supported

/**
 * Create a new sale
 * Creates a sale with items and reserves inventory (walk-in customers only, no tax)
 * @param {Object} saleData - Sale data (sale_type, user_id, items)
 * @param {number} userId - User ID creating the sale (cashier)
 * @returns {Promise<Object>} Created sale with items
 */
const createSale = async (saleData, userId) => {
  // Extract sale data
  const {
    sale_type = 'POS',
    items = [],
    discount_code = null,
    discount_id = null,
    manager_approval_id = null,
    stock_override = false,
    stock_override_reason = '',
    // Optional monetary values from POS UI so backend can stay in sync with frontend totals
    subtotal: clientSubtotal = null,
    discount_amount: clientDiscountAmount = null,
    total: clientTotal = null,
  } = saleData;
  
  // Customer registration removed - all sales are walk-in (customer_id always null)
  const customer_id = null;
  
  // Validate items
  if (!items || items.length === 0) {
    throw new ValidationError('Sale must have at least one item'); // Throw error if no items
  }
  
  // Validate sale_type
  const validSaleTypes = ['POS', 'INVOICE']; // Valid sale types
  if (!validSaleTypes.includes(sale_type)) {
    throw new ValidationError(`Sale type must be one of: ${validSaleTypes.join(', ')}`); // Throw error if invalid type
  }
  
  // Customer registration removed - all sales are walk-in customers only
  // Shift functionality removed - sales no longer require shifts
  
  // Start database transaction
  const transaction = await sequelize.transaction(); // Start transaction
  let committed = false; // Track if transaction was committed
  
  try {
    // Get user to check role for manager/admin checks
    const user = await User.findByPk(userId, { transaction });
    if (!user) {
      throw new NotFoundError(`User with ID ${userId} not found`);
    }
    const isManagerOrAdmin = user && (user.role === 'manager' || user.role === 'system_admin');
    // Handle discount if provided
    let discount = null; // Initialize discount variable
    let discountAmount = 0; // Initialize discount amount
    if (discount_code || discount_id) {
      // Get discount by code or ID
      if (discount_code) {
        discount = await discountService.getDiscountByCode(discount_code); // Get discount by code
      } else if (discount_id) {
        discount = await discountService.getDiscount(parseInt(discount_id)); // Get discount by ID
      }
      
      // Validate discount
      const validation = await discountService.validateDiscount(discount, customer_id || null); // Validate discount
      if (!validation.isValid) {
        throw new ValidationError(validation.message); // Throw error if discount is invalid
      }
    }
    
    // Generate invoice number
    const invoiceNo = await generateInvoiceNumber(); // Generate unique invoice number
    
    // Process sale items and calculate totals
    const processedItems = []; // Array to store processed items
    for (const item of items) {
      // Extract item data (variants removed)
      const { product_id, quantity, unit_price = null } = item; // Extract item data
      
      // Validate required fields
      if (!product_id || !quantity) {
        throw new ValidationError('Item must have product_id and quantity'); // Throw error if missing required fields
      }
      
      // Get product
      const product = await Product.findByPk(product_id, { transaction }); // Find product in transaction
      if (!product) {
        throw new NotFoundError(`Product not found: ${product_id}`); // Throw error if product doesn't exist
      }

      // Check if product requires manager approval for POS sales
      // isManagerOrAdmin is already set above
      
      // Check sell_on_pos flag - if false and user is not manager/admin, require approval
      if (sale_type === 'POS' && !product.sell_on_pos && !isManagerOrAdmin) {
        // Check if manager_approval_id is provided in saleData
        if (!manager_approval_id) {
          throw new ValidationError(
            `Product "${product.name}" (ID: ${product_id}) cannot be sold on POS without manager approval. ` +
            `Manager approval ID is required.`
          );
        }
        
        // Validate manager approval
        const approvingManager = await User.findByPk(manager_approval_id, { transaction });
        if (!approvingManager) {
          throw new NotFoundError(`Manager with ID ${manager_approval_id} not found`);
      // Use isManagerOrAdmin from above
      // User already fetched above
      // isManagerOrAdmin already set above
          throw new ValidationError(`User ${manager_approval_id} is not authorized to approve RM sales`);
        }
        
        // Log manager approval
        logger.info(`RM product sale approved by manager`, {
          productId: product_id,
          productName: product.name,
          saleId: 'pending',
          cashierId: userId,
          managerId: manager_approval_id,
        });
      }
      
      // Get unit price (use provided, then price list, then product's selling_price)
      let finalUnitPrice = unit_price; // Use provided unit price if available
      if (!finalUnitPrice) {
        // Try to get price from pricing service (price list)
        try {
          const productPrice = await pricingService.getPrice(product_id, null, parseFloat(quantity));
          if (productPrice && productPrice.price) {
            finalUnitPrice = parseFloat(productPrice.price);
          }
        } catch (error) {
          // If pricing service throws error (e.g., no default price list), continue to fallback
          // Don't throw error yet, try product's selling_price first
        }
        
        // Fallback to product's selling_price if no price list price found
        if (!finalUnitPrice && product.selling_price) {
          finalUnitPrice = parseFloat(product.selling_price);
        }
        
        // If still no price, throw error
        if (!finalUnitPrice) {
          throw new ValidationError(
            `Unit price required for product "${product.name}" (ID: ${product_id}). ` +
            `Please provide unit_price, set product's selling_price, or set up pricing in price list.`
          );
        }
      }
      
      // Validate unit price
      if (finalUnitPrice < 0) {
        throw new ValidationError('Unit price cannot be negative'); // Throw error if negative price
      }
      
      // Check inventory availability and calculate fulfilled/preordered quantities
      let quantityFulfilled = parseFloat(quantity); // Default: all fulfilled
      let quantityPreordered = 0; // Default: none preordered
      
      if (product.track_inventory) {
        // Get stock availability using stock checking service (variants removed)
        const availability = await stockCheckingService.getProductAvailability(product_id, null);
        const requestedQty = parseFloat(quantity);
        const availableQty = parseFloat(availability.available_qty || 0);
        
        
        // Check if stock override is enabled (manager/admin only)
        if (stock_override && isManagerOrAdmin) {
          // Manager override: allow sale even with negative stock
          quantityFulfilled = requestedQty; // Fulfill all requested
          quantityPreordered = 0; // No preorder
          
          // Log stock override
          logger.warn("Stock override applied by manager", {
            productId: product_id,
            productName: product.name,
            requestedQty,
            availableQty,
            overrideReason: stock_override_reason,
            managerId: userId,
          });
        } else {
          // Normal stock checking: calculate fulfilled and preordered
          quantityFulfilled = Math.min(requestedQty, availableQty); // Fulfill up to available
          quantityPreordered = Math.max(0, requestedQty - availableQty); // Preorder the rest
        }
      } // End if (product.track_inventory)
      
      // Calculate item totals based on total quantity (both fulfilled and preordered)
      // This must be outside the track_inventory block so it always executes
      const itemTotals = calculateItemTotals(quantity, finalUnitPrice); // Calculate item totals (tax removed)
      
      // Store processed item data with fulfillment info
      processedItems.push({
        product_id, // Product ID
        quantity: parseFloat(quantity), // Total quantity requested
        quantity_fulfilled: quantityFulfilled, // Quantity to fulfill immediately
        quantity_preordered: quantityPreordered, // Quantity to preorder
        unit_price: parseFloat(finalUnitPrice), // Unit price
        lineTotal: itemTotals.lineTotal, // Line total
      });
    }
    
    // Apply discount if provided
    if (discount) {
      // Prepare sale items for discount calculation (format required by applyDiscount)
      const saleItemsForDiscount = processedItems.map(item => ({
        product_id: item.product_id, // Product ID
        quantity: item.quantity, // Quantity
        unit_price: item.unit_price, // Unit price
      })); // Map processed items to discount format (variants removed)
      
      // Apply discount
      const discountResult = await discountService.applyDiscount(discount, saleItemsForDiscount, customer_id || null); // Apply discount
      discountAmount = discountResult.totalDiscountAmount; // Get discount amount
      
      // Track discount usage (increment usage_count)
      discount.usage_count = (discount.usage_count || 0) + 1; // Increment usage count
      await discount.save({ transaction }); // Save discount in transaction
    }
    
    // Calculate sale totals (with discount) based on server-side calculations
    const saleTotals = calculateSaleTotals(processedItems, discountAmount); // Calculate sale totals with discount

    // For POS sales, prefer client-provided monetary values (subtotal, discount_amount, total)
    // to keep backend, payments, and reports aligned with what the cashier sees.
    let finalSubtotal = saleTotals.subtotal;
    let finalDiscountAmount = saleTotals.discount_amount;
    let finalTotal = saleTotals.total;

    if (sale_type === 'POS' && clientSubtotal !== null && clientTotal !== null) {
      const parsedClientSubtotal = parseFloat(clientSubtotal);
      const parsedClientTotal = parseFloat(clientTotal);
      const parsedClientDiscount =
        clientDiscountAmount !== null
          ? parseFloat(clientDiscountAmount)
          : parsedClientSubtotal - parsedClientTotal;

      // Basic guards to avoid invalid values from client
      if (
        Number.isFinite(parsedClientSubtotal) &&
        Number.isFinite(parsedClientTotal) &&
        Number.isFinite(parsedClientDiscount) &&
        parsedClientSubtotal >= 0 &&
        parsedClientDiscount >= 0 &&
        parsedClientDiscount <= parsedClientSubtotal
      ) {
        finalSubtotal = parsedClientSubtotal;
        finalDiscountAmount = parsedClientDiscount;
        finalTotal = parsedClientTotal;
      } else {
        logger.warn('Ignoring invalid client-provided sale totals for POS sale', {
          clientSubtotal,
          clientTotal,
          clientDiscountAmount,
          sale_type,
        });
      }
    }
    
    // Determine fulfillment type based on items
    let fulfillmentType = 'POS_SALE'; // Default to POS_SALE
    const hasFulfilled = processedItems.some(item => item.quantity_fulfilled > 0);
    const hasPreordered = processedItems.some(item => item.quantity_preordered > 0);
    
    if (hasFulfilled && hasPreordered) {
      // Both fulfilled and preordered items - split fulfillment
      fulfillmentType = 'SPLIT_FULFILLMENT';
    } else if (hasPreordered && !hasFulfilled) {
      // All items are preordered
      fulfillmentType = 'PREORDER';
    } else if (sale_type === 'INVOICE') {
      // Invoice sales that are all fulfilled
      fulfillmentType = 'ORDER';
    }
    // Otherwise, POS_SALE (all fulfilled, immediate sale)
    
    // User is already fetched above for manager/admin checks

    // Create sale (tax removed, customer_id always null for walk-in customers)
    const sale = await Sale.create({
      invoice_no: invoiceNo, // Invoice number
      customer_id: null, // Always null - walk-in customers only
      sale_type, // Sale type (POS or INVOICE)
      user_id: userId, // User ID (cashier)
      status: 'DRAFT', // Initial status is DRAFT
      fulfillment_type: fulfillmentType, // Set fulfillment type
      subtotal: finalSubtotal, // Subtotal (aligned with POS if provided)
      discount_id: discount ? discount.id : null, // Discount ID if discount applied
      discount_amount: finalDiscountAmount, // Discount amount (aligned with POS if provided)
      total: finalTotal, // Total amount (with discount, aligned with POS if provided)
    }, { transaction }); // Create sale in transaction

    // Log manager approval and stock override actions if applicable
    if (manager_approval_id) {
      logger.info('Sale created with manager approval', {
        saleId: sale.id,
        invoiceNo: sale.invoice_no,
        managerId: manager_approval_id,
        cashierId: userId,
      });
    }
    if (stock_override && isManagerOrAdmin) {
      logger.info('Sale created with stock override', {
        saleId: sale.id,
        invoiceNo: sale.invoice_no,
        managerId: userId,
        overrideReason: stock_override_reason,
      });
    }
    
    // Create sale items and link to inventory items if provided
    for (let i = 0; i < processedItems.length; i++) {
      const item = processedItems[i]; // Get processed item
      const originalItem = items[i]; // Get original item data (may have inventory_item_id)
      
      // Get inventory_item_id from original item if provided (UID-based tracking)
      const inventoryItemId = originalItem.inventory_item_id || null; // Get inventory item ID
      
      // Create sale item with fulfillment quantities (variants and tax removed)
      const saleItem = await SaleItem.create({
        sale_id: sale.id, // Link to sale
        product_id: item.product_id, // Product ID
        inventory_item_id: inventoryItemId, // Link to inventory item if provided (UID-based)
        quantity: item.quantity, // Total quantity requested
        quantity_fulfilled: item.quantity_fulfilled, // Quantity to fulfill immediately
        quantity_preordered: item.quantity_preordered, // Quantity to preorder
        unit_price: item.unit_price, // Unit price
      }, { transaction }); // Create sale item in transaction
      
      // If inventory item ID provided, update inventory item status to RESERVED
      if (inventoryItemId) {
        // Find inventory item
        const inventoryItem = await InventoryItem.findByPk(inventoryItemId, { transaction }); // Find inventory item in transaction
        if (inventoryItem) {
          // Validate inventory item belongs to the product (variants removed)
          if (inventoryItem.product_id !== item.product_id) {
            throw new ValidationError(`Inventory item ${inventoryItemId} does not match product`); // Throw error if mismatch
          }
          
          // Validate inventory item is available (IN_STOCK)
          if (inventoryItem.status !== 'IN_STOCK') {
            throw new InventoryError(`Inventory item ${inventoryItemId} is not available (status: ${inventoryItem.status})`); // Throw error if not available
          }
          
          // Update inventory item status to RESERVED
          inventoryItem.status = 'RESERVED'; // Set status to RESERVED
          await inventoryItem.save({ transaction }); // Save inventory item in transaction
          
          // Create inventory movement record for UID-based item (variants removed)
          await InventoryMovement.create({
            product_id: item.product_id, // Product ID
            inventory_item_id: inventoryItemId, // Inventory item ID
            movement_type: 'OUT', // Movement type is OUT (reserved)
            quantity_change: -1, // Quantity change is -1 (one item reserved)
            quantity_after: null, // Not applicable for UID-based (quantity is per item)
            reason: 'SALE', // Reason: SALE
            reference_id: sale.id, // Reference ID: sale ID
            user_id: userId, // User ID
          }, { transaction }); // Create movement in transaction
        }
      }
      
      // Reserve inventory if product tracks inventory (only for quantity-based, skip if UID-based item provided)
      if (!inventoryItemId) {
        // Only do quantity-based reservation if inventory_item_id not provided
        const product = await Product.findByPk(item.product_id, { transaction }); // Get product in transaction
        if (product.track_inventory) {
          // Get or create inventory record (within transaction) (variants removed)
          let inventory = await Inventory.findOne({
            where: {
              product_id: item.product_id, // Match product ID
            },
            transaction, // Use transaction
          });
          
          if (!inventory) {
            // Create inventory if it doesn't exist
            inventory = await Inventory.create({
              product_id: item.product_id, // Set product ID
              quantity: 0, // Default quantity
              reorder_level: 0, // Default reorder level
            }, { transaction }); // Create inventory in transaction
          }
          
          // Only reserve inventory for fulfilled quantity (not preordered)
          const quantityToReserve = item.quantity_fulfilled || 0; // Only reserve fulfilled quantity
          
          // Calculate new quantity (reduce stock only for fulfilled items)
          const newQuantity = parseFloat(inventory.quantity) - parseFloat(quantityToReserve); // Reduce quantity
          
          // Check if sufficient stock for fulfilled quantity
          if (newQuantity < 0) {
            throw new InventoryError(`Insufficient stock for product ${item.product_id}. Current: ${inventory.quantity}, Requested to fulfill: ${quantityToReserve}`); // Throw error if insufficient stock
          }
          
          // Update inventory quantity
          inventory.quantity = newQuantity; // Set new quantity
          await inventory.save({ transaction }); // Save inventory in transaction
          
          // Record inventory movement (only for fulfilled quantity) (variants removed)
          await InventoryMovement.create({
            product_id: item.product_id, // Product ID
            inventory_item_id: null, // No inventory item for quantity-based
            quantity_change: -quantityToReserve, // Negative quantity change (reserve stock for fulfilled only)
            reason: 'SALE', // Reason: SALE
            reference_id: sale.id, // Reference ID: sale ID
            user_id: userId, // User ID
          }, { transaction }); // Create movement in transaction
        }
      }
    }
    
    // Commit transaction
    await transaction.commit(); // Commit transaction
    committed = true; // Mark transaction as committed
    
    // Reload sale with items and associations (outside transaction)
    const saleWithItems = await Sale.findByPk(sale.id, {
      include: [
        {
          model: SaleItem, // Include sale items
          as: 'items', // Use items alias
          include: [
            {
              model: Product, // Include product details
              as: 'product', // Use product alias
              attributes: ['id', 'name', 'sku'], // Select specific attributes
            },
          ],
        },
        {
          model: Discount, // Include discount details
          as: 'discount', // Use discount alias
          required: false, // Left join (discount may be null)
        },
        {
          model: User, // Include user details (cashier)
          as: 'user', // Use user alias
          required: false, // Left join
          attributes: ['id', 'username', 'full_name'], // Select specific attributes
        },
      ],
    });
    
    // Log sale creation
    logger.info(`Sale created: ${invoiceNo}`, {
      saleId: sale.id,
      invoiceNo,
      userId,
      total: saleTotals.total,
    });
    
    // Return created sale with items
    return { sale: saleWithItems }; // Return sale with items
  } catch (error) {
    // Rollback transaction on error (only if not already committed)
    if (!committed) {
      await transaction.rollback(); // Rollback transaction
    }
    throw error; // Re-throw error
  }
};

/**
 * Get sale by ID
 * Retrieves a sale with all details
 * @param {number} saleId - Sale ID
 * @returns {Promise<Object>} Sale with items and associations
 */
const getSale = async (saleId) => {
  // Find sale by ID
  const sale = await Sale.findByPk(saleId, {
    include: [
      {
        model: SaleItem, // Include sale items
        as: 'items', // Use items alias
          include: [
            {
              model: Product, // Include product details
              as: 'product', // Use product alias
              attributes: ['id', 'name', 'sku', 'product_type'], // Select specific attributes
            },
          ],
        },
      {
        model: User, // Include user details (cashier)
        as: 'user', // Use user alias
        required: false, // Left join
        attributes: ['id', 'username', 'full_name'], // Select specific attributes
      },
    ],
  });
  
  // If sale not found, throw error
  if (!sale) {
    throw new NotFoundError('Sale not found'); // Throw error if sale doesn't exist
  }
  
  // Return sale
  return sale; // Return sale
};

/**
 * List sales with filters and pagination
 * Retrieves paginated list of sales
 * @param {Object} options - Query options
 * @param {number} options.page - Page number (default: 1)
 * @param {number} options.limit - Items per page (default: 10)
 * @param {number|null} options.customerId - Filter by customer ID
 * @param {string|null} options.status - Filter by status (DRAFT, PAID, CANCELLED)
 * @param {string|null} options.saleType - Filter by sale type (POS, INVOICE)
 * @param {string|null} options.startDate - Filter by start date (ISO string)
 * @param {string|null} options.endDate - Filter by end date (ISO string)
 * @param {number|null} options.userId - Filter by user ID (cashier)
 * @returns {Promise<Object>} Paginated sales list
 */
const listSales = async (options = {}) => {
  // Extract options with defaults
  const {
    page = 1,
    limit = 10,
    customerId = null,
    status = null,
    saleType = null,
    startDate = null,
    endDate = null,
    userId = null,
  } = options; // Extract and set defaults
  
  // Build where clause
  const where = {}; // Initialize where clause
  
  // Customer registration removed - all sales are walk-in (no customer filtering)
  
  // Extract invoice_no search from options if provided
  const invoiceNo = options.invoice_no || null; // Get invoice_no search
  
  // Add invoice number filter if provided
  if (invoiceNo) {
    where.invoice_no = { [Op.like]: `%${invoiceNo}%` }; // Search invoice number
  }
  
  // Add status filter if provided
  if (status) {
    where.status = status; // Filter by status
  }
  
  // Add sale type filter if provided
  if (saleType) {
    where.sale_type = saleType; // Filter by sale type
  }
  
  // Add user ID filter if provided
  if (userId) {
    where.user_id = userId; // Filter by user ID
  }
  
  // Add date range filter if provided
  if (startDate || endDate) {
    where.created_at = {}; // Initialize date filter
    if (startDate) {
      where.created_at[Op.gte] = new Date(startDate); // Greater than or equal to start date
    }
    if (endDate) {
      // Set end date to end of day
      const endDateTime = new Date(endDate); // Create date object
      endDateTime.setHours(23, 59, 59, 999); // Set to end of day
      where.created_at[Op.lte] = endDateTime; // Less than or equal to end date
    }
  }
  
  // Calculate offset for pagination
  const offset = (page - 1) * limit; // Calculate offset
  
  // Build query options
  const queryOptions = {
    where, // Apply where clause
    include: [
      {
        model: User, // Include user details (cashier)
        as: 'user', // Use user alias
        required: false, // Left join
        attributes: ['id', 'username', 'full_name'], // Select specific attributes
      },
    ],
    limit: parseInt(limit), // Set limit
    offset: parseInt(offset), // Set offset
    order: [['created_at', 'DESC']], // Order by creation date descending (newest first)
  };
  
  // Execute query to get sales and total count
  const { count, rows } = await Sale.findAndCountAll(queryOptions); // Get sales with count
  
  // Calculate total pages
  const totalPages = Math.ceil(count / limit); // Calculate total pages
  
  // Return paginated results
  return {
    sales: rows, // Sales records
    pagination: {
      page: parseInt(page), // Current page
      limit: parseInt(limit), // Items per page
      total: count, // Total items
      totalPages, // Total pages
    },
  };
};

/**
 * Cancel a sale
 * Cancels a sale and releases reserved inventory (only if status is DRAFT)
 * @param {number} saleId - Sale ID
 * @param {number} userId - User ID cancelling the sale
 * @returns {Promise<Object>} Cancelled sale
 */
const cancelSale = async (saleId, userId) => {
  // Start database transaction
  const transaction = await sequelize.transaction(); // Start transaction
  let committed = false; // Track if transaction was committed
  
  try {
    // Get sale with items
    const sale = await Sale.findByPk(saleId, {
      include: [
        {
          model: SaleItem, // Include sale items
          as: 'items', // Use items alias
          include: [
            {
              model: Product, // Include product details
              as: 'product', // Use product alias
            },
          ],
        },
      ],
      transaction, // Use transaction
    });
    
    // If sale not found, throw error
    if (!sale) {
      throw new NotFoundError('Sale not found'); // Throw error if sale doesn't exist
    }
    
    // Check if sale can be cancelled (only DRAFT status can be cancelled)
    if (sale.status !== 'DRAFT') {
      throw new ValidationError(`Sale cannot be cancelled. Current status: ${sale.status}`); // Throw error if cannot cancel
    }
    
    // Release inventory for each item
    for (const item of sale.items) {
      // If product tracks inventory, release reserved stock (within transaction)
      if (item.product.track_inventory) {
        // Get or create inventory record (within transaction) (variants removed)
        let inventory = await Inventory.findOne({
          where: {
            product_id: item.product_id, // Match product ID
          },
          transaction, // Use transaction
        });
        
        if (!inventory) {
          // Create inventory if it doesn't exist
          inventory = await Inventory.create({
            product_id: item.product_id, // Set product ID
            quantity: 0, // Default quantity
            reorder_level: 0, // Default reorder level
          }, { transaction }); // Create inventory in transaction
        }
        
        // Increase inventory quantity (release stock back)
        inventory.quantity = parseFloat(inventory.quantity) + parseFloat(item.quantity); // Increase quantity
        await inventory.save({ transaction }); // Save inventory in transaction
        
        // Record inventory movement (variants removed)
        await InventoryMovement.create({
          product_id: item.product_id, // Product ID
          inventory_item_id: null, // No inventory item for quantity-based
          quantity_change: item.quantity, // Positive quantity change (release stock)
          reason: 'SALE_RETURN', // Reason: SALE_RETURN
          reference_id: sale.id, // Reference ID: sale ID
        }, { transaction }); // Create movement in transaction
      }
    }
    
    // Update sale status to CANCELLED
    sale.status = 'CANCELLED'; // Set status to CANCELLED
    await sale.save({ transaction }); // Save sale in transaction
    
    // Commit transaction
    await transaction.commit(); // Commit transaction
    committed = true; // Mark transaction as committed
    
    // Reload sale with associations (outside transaction)
    const cancelledSale = await getSale(saleId); // Get sale with details
    
    // Log sale cancellation
    logger.info(`Sale cancelled: ${sale.invoice_no}`, {
      saleId: sale.id,
      invoiceNo: sale.invoice_no,
      userId,
    });
    
    // Return cancelled sale
    return cancelledSale; // Return cancelled sale
  } catch (error) {
    // Rollback transaction on error (only if not already committed)
    if (!committed) {
      await transaction.rollback(); // Rollback transaction
    }
    throw error; // Re-throw error
  }
};

// Export service functions
module.exports = {
  createSale, // Create sale function
  getSale, // Get sale function
  listSales, // List sales function
  cancelSale, // Cancel sale function
  generateInvoiceNumber, // Generate invoice number function
  calculateItemTotals, // Calculate item totals function
  calculateSaleTotals, // Calculate sale totals function
};
