/**
 * Inventory Reports Service
 * Business logic for inventory reports generation
 */

// Import Inventory and related models
const { Inventory, InventoryItem, Product, InventoryMovement, SaleItem, Sale } = require('../../../models');
// Import sequelize instance and operators
const { Op } = require('sequelize');
// Import sequelize instance from models
const { sequelize } = require('../../../models');
// Import logger
const logger = require('../../../utils/logger');

/**
 * Get fast-moving products
 * Retrieves products with highest sales velocity
 * @param {Object} filters - Filter options (start_date, end_date, limit)
 * @returns {Promise<Array>} Fast-moving products
 */
const getFastMovingProducts = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date, limit = 20 } = filters; // Extract filters (default limit 20)
  
  // Build date filter
  let dateFilter = ''; // Initialize date filter
  const replacements = {}; // Initialize replacements
  
  // Add date filter
  if (start_date) {
    dateFilter += 'AND s.created_at >= :start_date '; // Add start date filter
    replacements.start_date = new Date(start_date); // Set start date
  }
  if (end_date) {
    // Set end date to end of day
    const endDate = new Date(end_date); // Create end date
    endDate.setHours(23, 59, 59, 999); // Set to end of day
    dateFilter += 'AND s.created_at <= :end_date '; // Add end date filter
    replacements.end_date = endDate; // Set end date
  }
  
  // Query fast-moving products using raw SQL (variants removed)
  const fastMovingProducts = await sequelize.query(`
    SELECT 
      si.product_id,
      SUM(si.quantity) as total_quantity_sold,
      SUM(si.quantity * si.unit_price) as total_revenue,
      COUNT(DISTINCT si.sale_id) as sale_count,
      p.name as product_name,
      p.sku as product_sku
    FROM sale_items si
    INNER JOIN sales s ON si.sale_id = s.id
    INNER JOIN products p ON si.product_id = p.id
    WHERE s.status = 'PAID'
      ${dateFilter}
    GROUP BY si.product_id, p.name, p.sku
    ORDER BY total_quantity_sold DESC
    LIMIT :limit
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: {
      ...replacements,
      limit: parseInt(limit, 10), // Limit results
    },
  });
  
  // Fetch full product details
  const productsWithDetails = await Promise.all(fastMovingProducts.map(async (item) => {
    // Fetch product
    const product = await Product.findByPk(item.product_id); // Get product
    // Get current inventory quantity (variants removed)
    const inventory = await Inventory.findOne({
      where: {
        product_id: item.product_id, // Match product ID
      },
    }); // Get inventory
    
    return {
      product_id: item.product_id, // Product ID
      product: product, // Product details
      product_name: item.product_name, // Product name
      product_sku: item.product_sku, // Product SKU
      total_quantity: parseFloat(item.total_quantity_sold || 0), // Total quantity sold
      total_revenue: parseFloat(item.total_revenue || 0), // Total revenue
      sale_count: parseInt(item.sale_count || 0), // Number of sales
      quantity: parseFloat(item.total_quantity_sold || 0), // Alias for compatibility
    };
  }));
  
  // Return fast-moving products
  return productsWithDetails; // Return products
};

/**
 * Get slow-moving products
 * Retrieves products with lowest sales velocity
 * @param {Object} filters - Filter options (start_date, end_date, limit)
 * @returns {Promise<Array>} Slow-moving products
 */
const getSlowMovingProducts = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date, limit = 20 } = filters; // Extract filters (default limit 20)
  
  // Build date filter
  let dateFilter = ''; // Initialize date filter
  const replacements = {}; // Initialize replacements
  
  // Add date filter
  if (start_date) {
    dateFilter += 'AND s.created_at >= :start_date '; // Add start date filter
    replacements.start_date = new Date(start_date); // Set start date
  }
  if (end_date) {
    // Set end date to end of day
    const endDate = new Date(end_date); // Create end date
    endDate.setHours(23, 59, 59, 999); // Set to end of day
    dateFilter += 'AND s.created_at <= :end_date '; // Add end date filter
    replacements.end_date = endDate; // Set end date
  }
  
  // Query slow-moving products using raw SQL (variants removed)
  const slowMovingProducts = await sequelize.query(`
    SELECT 
      si.product_id,
      SUM(si.quantity) as total_quantity_sold,
      SUM(si.quantity * si.unit_price) as total_revenue,
      COUNT(DISTINCT si.sale_id) as sale_count,
      p.name as product_name,
      p.sku as product_sku
    FROM sale_items si
    INNER JOIN sales s ON si.sale_id = s.id
    INNER JOIN products p ON si.product_id = p.id
    WHERE s.status = 'PAID'
      ${dateFilter}
    GROUP BY si.product_id, p.name, p.sku
    HAVING SUM(si.quantity) > 0
    ORDER BY total_quantity_sold ASC
    LIMIT :limit
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: {
      ...replacements,
      limit: parseInt(limit, 10), // Limit results
    },
  });
  
  // Fetch full product details
  const productsWithDetails = await Promise.all(slowMovingProducts.map(async (item) => {
    // Fetch product
    const product = await Product.findByPk(item.product_id); // Get product
    // Get current inventory quantity (variants removed)
    const inventory = await Inventory.findOne({
      where: {
        product_id: item.product_id, // Match product ID
      },
    }); // Get inventory
    
    return {
      product_id: item.product_id, // Product ID
      product: product, // Product details
      product_name: item.product_name, // Product name
      product_sku: item.product_sku, // Product SKU
      total_quantity: parseFloat(item.total_quantity_sold || 0), // Total quantity sold
      total_revenue: parseFloat(item.total_revenue || 0), // Total revenue
      sale_count: parseInt(item.sale_count || 0), // Number of sales
      quantity: parseFloat(item.total_quantity_sold || 0), // Alias for compatibility
    };
  }));
  
  // Return slow-moving products
  return productsWithDetails; // Return products
};

/**
 * Get low stock items (reorder alerts)
 * Retrieves products below reorder level
 * @param {Object} filters - Filter options (category_id)
 * @returns {Promise<Array>} Low stock items
 */
const getLowStockItems = async (filters = {}) => {
  // Extract filters
  const { category_id } = filters; // Extract filters
  
  // Build where clause
  const where = {}; // Initialize where clause
  
  // Add category filter if provided
  if (category_id) {
    // This would require joining with ProductCategory, but for simplicity, we'll filter by product category_id
    // For now, we'll get all low stock items and filter in memory if needed
  }
  
  // Find inventory items below reorder level or out of stock (variants removed)
  // Use same logic as inventory service: quantity <= reorder_level
  // This is simpler and more inclusive - shows items at or below reorder level
  const lowStockItems = await Inventory.findAll({
    where: sequelize.literal('quantity <= reorder_level'), // Quantity is less than or equal to reorder level
    include: [
      {
        model: Product, // Include product
        as: 'product', // Use product alias
        required: true, // Inner join (must have product)
        attributes: ['id', 'name', 'sku', 'product_type', 'track_inventory'], // Select specific product attributes
      },
    ],
    order: [
      // Order by quantity ascending (lowest first)
      ['quantity', 'ASC'],
    ],
  });

  // Also find products that track inventory but don't have an inventory record yet (treat as 0 stock)
  // Use raw SQL for a LEFT JOIN to find products without inventory records
  const productsWithoutInventoryData = await sequelize.query(`
    SELECT 
      p.id as product_id,
      p.name as product_name,
      p.sku as product_sku
    FROM products p
    WHERE p.track_inventory = true
      AND NOT EXISTS (
        SELECT 1 FROM inventories i WHERE i.product_id = p.id
      )
  `, {
    type: sequelize.QueryTypes.SELECT,
  });

  // Fetch full product details for products without inventory
  const missingInventoryItems = await Promise.all(productsWithoutInventoryData.map(async (item) => {
    const product = await Product.findByPk(item.product_id);
    return {
      inventory_id: null, // No inventory record
      product_id: item.product_id, // Product ID
      product: product, // Product details
      product_name: item.product_name || product?.name || 'Unknown', // Product name
      product_sku: item.product_sku || product?.sku || '—', // Product SKU
      quantity: 0, // Treat as 0 stock
      reorder_level: 0, // Default reorder level
      stock_status: 'OUT_OF_STOCK', // Out of stock
      stock_shortage: 0, // No shortage calculation needed
    };
  }));
  
  // Combine both sets of items
  const allLowStockItems = [...lowStockItems, ...missingInventoryItems];
  
  // Filter by category if provided (in memory)
  let filteredItems = allLowStockItems; // Initialize filtered items
  if (category_id) {
    // Note: This is a simplified filter. For proper category filtering, you'd need to join with ProductCategory
    filteredItems = allLowStockItems.filter(item => {
      // This would need proper category relationship lookup
      return true; // For now, return all items
    }); // Filter items
  }
  
  // Map to report format (handle both Inventory instances and plain objects)
  const items = filteredItems.map(item => {
    // Handle Sequelize instances
    if (item instanceof Inventory) {
      return {
        inventory_id: item.id, // Inventory ID
        product_id: item.product_id, // Product ID
        product: item.product, // Product details
        product_name: item.product?.name || 'Unknown', // Product name
        product_sku: item.product?.sku || '—', // Product SKU
        quantity: parseFloat(item.quantity || 0), // Current quantity
        reorder_level: parseFloat(item.reorder_level || 0), // Reorder level
        stock_status: parseFloat(item.quantity || 0) <= 0 ? 'OUT_OF_STOCK' : 'LOW_STOCK', // Stock status
        stock_shortage: Math.max(0, parseFloat(item.reorder_level || 0) - parseFloat(item.quantity || 0)), // Stock shortage
      };
    } else {
      // Already in the correct format (from missingInventoryItems)
      return item;
    }
  });
  
  // Return low stock items
  return items; // Return items
};

// Export inventory report functions
module.exports = {
  getFastMovingProducts,
  getSlowMovingProducts,
  getLowStockItems,
};

