/**
 * Sales Reports Service
 * Business logic for sales reports generation
 */

// Import Sale and related models
const { Sale, SaleItem, Product, User, Payment } = require('../../../models'); // Variants, Customer, and Shift removed
// 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');

/**
 * Generate sales report by date range
 * Generates sales report filtered by date range
 * @param {Object} filters - Filter options (start_date, end_date)
 * @returns {Promise<Object>} Sales report data
 */
const getSalesReportByDate = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date } = filters; // Extract date filters
  
  // Build where clause
  const where = {}; // Initialize where clause
  
  // Add date filter
  if (start_date || end_date) {
    where.created_at = {}; // Initialize date filter
    if (start_date) {
      where.created_at[Op.gte] = new Date(start_date); // Greater than or equal to 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
      where.created_at[Op.lte] = endDate; // Less than or equal to end date
    }
  }
  
  // Only include paid sales (revenue-generating, not draft or cancelled)
  // Sales with status PAID have been paid and generate revenue
  where.status = 'PAID'; // Only paid sales (completed/confirmed payments)
  
  // Log query parameters for debugging
  logger.info('Fetching sales report', {
    where,
    status: where.status,
    dateRange: where.created_at || 'all dates',
  });
  
  // Find all sales with items
  const sales = await Sale.findAll({
    where, // Where clause
    include: [
      {
        model: SaleItem, // Include sale items
        as: 'items', // Use items alias
        include: [
          {
            model: Product, // Include product
            as: 'product', // Use product alias
          },
          // ProductVariant and Customer removed - variants and customer registration not used
        ],
      },
      {
        model: User, // Include user (cashier)
        as: 'user', // Use user alias
        required: false, // Left join (user may be null)
      },
    ],
  });
  
  // Calculate totals (tax removed)
  const totalSales = sales.length; // Total number of sales
  const totalRevenue = sales.reduce((sum, sale) => sum + parseFloat(sale.total || 0), 0); // Total revenue
  const totalDiscount = sales.reduce((sum, sale) => sum + parseFloat(sale.discount_amount || 0), 0); // Total discount
  const totalItemsSold = sales.reduce((sum, sale) => {
    // Count items in this sale
    return sum + (sale.items ? sale.items.length : 0); // Add item count
  }, 0); // Total items sold
  
  // Return report data
  return {
    period: {
      start_date: start_date || null, // Start date
      end_date: end_date || null, // End date
    },
    summary: {
      total_sales: totalSales, // Total sales count
      total_revenue: totalRevenue, // Total revenue
      total_discount: totalDiscount, // Total discount
      total_items_sold: totalItemsSold, // Total items sold
      net_revenue: totalRevenue - totalDiscount, // Net revenue (after discount)
    },
    sales: sales, // Sales array
  };
};

/**
 * Generate sales report by product
 * Generates sales report grouped by product
 * @param {Object} filters - Filter options (start_date, end_date, product_id)
 * @returns {Promise<Object>} Sales report data by product
 */
const getSalesReportByProduct = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date, product_id } = filters; // Extract filters
  
  // Build where clause for sale items
  const saleItemWhere = {}; // Initialize where clause
  
  // Add product filter
  if (product_id) {
    saleItemWhere.product_id = product_id; // Filter by product ID
  }
  
  // Build where clause for sales
  const saleWhere = {
    status: 'PAID', // Only paid sales (revenue-generating)
  }; // Initialize sale where clause
  
  // Add date filter
  if (start_date || end_date) {
    saleWhere.created_at = {}; // Initialize date filter
    if (start_date) {
      saleWhere.created_at[Op.gte] = new Date(start_date); // Greater than or equal to 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
      saleWhere.created_at[Op.lte] = endDate; // Less than or equal to end date
    }
  }
  
  // Query sale items with aggregations using raw SQL for better grouping
  const productSalesData = await sequelize.query(`
    SELECT 
      si.product_id,
      SUM(si.quantity) as total_quantity,
      SUM(si.quantity * si.unit_price) as total_revenue,
      COUNT(DISTINCT si.sale_id) as sale_count
    FROM sale_items si
    INNER JOIN sales s ON si.sale_id = s.id
    WHERE s.status = 'PAID'
      ${start_date ? `AND s.created_at >= :start_date` : ''}
      ${end_date ? `AND s.created_at <= :end_date` : ''}
      ${product_id ? `AND si.product_id = :product_id` : ''}
    GROUP BY si.product_id
    ORDER BY total_revenue DESC
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: {
      start_date: start_date ? new Date(start_date) : null,
      end_date: end_date ? (() => {
        const endDate = new Date(end_date);
        endDate.setHours(23, 59, 59, 999);
        return endDate;
      })() : null,
      product_id: product_id || null,
    },
  });
  
  // Fetch product details for each result (variants removed)
  const productSales = await Promise.all(productSalesData.map(async (item) => {
    // Fetch product
    const product = await Product.findByPk(item.product_id); // Get product
    return {
      product_id: item.product_id, // Product ID
      product: product, // Product details
      total_quantity: parseFloat(item.total_quantity || 0), // Total quantity sold
      total_revenue: parseFloat(item.total_revenue || 0), // Total revenue
      sale_count: parseInt(item.sale_count || 0), // Number of sales
    };
  }));
  
  // Calculate totals
  const totalRevenue = productSales.reduce((sum, item) => {
    // Sum total revenue from all products
    return sum + parseFloat(item.total_revenue || 0); // Add revenue
  }, 0); // Total revenue
  
  // Return report data
  return {
    period: {
      start_date: start_date || null, // Start date
      end_date: end_date || null, // End date
    },
    summary: {
      total_products: productSales.length, // Total products sold
      total_revenue: totalRevenue, // Total revenue
    },
    products: productSales, // Products array
  };
};

/**
 * Generate sales report by customer (DEPRECATED - customer registration removed)
 * All sales are walk-in customers only
 * @deprecated Customer registration removed - this function may need to be removed or updated
 */
const getSalesReportByCustomer = async (filters = {}) => {
  // Customer registration removed - all sales are walk-in customers
  // This report may need to be removed or redesigned
  const { start_date, end_date } = filters; // Extract filters (customer_id removed)
  
  // Build where clause
  const where = {
    status: 'PAID', // Only paid sales (revenue-generating)
    customer_id: null, // All sales are walk-in (no registered customers)
  }; // Initialize where clause
  
  // Add date filter
  if (start_date || end_date) {
    where.created_at = {}; // Initialize date filter
    if (start_date) {
      where.created_at[Op.gte] = new Date(start_date); // Greater than or equal to 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
      where.created_at[Op.lte] = endDate; // Less than or equal to end date
    }
  }
  
  // Query sales (all walk-in customers - customer registration removed)
  const sales = await Sale.findAll({
    where, // Where clause
    include: [
      {
        model: SaleItem, // Include sale items
        as: 'items', // Use items alias
        include: [
          {
            model: Product, // Include product
            as: 'product', // Use product alias
          },
        ],
      },
    ],
  });
  
  // Calculate summary (all walk-in customers)
  const totalSales = sales.length;
  const totalRevenue = sales.reduce((sum, sale) => sum + parseFloat(sale.total || 0), 0);
  const totalDiscount = sales.reduce((sum, sale) => sum + parseFloat(sale.discount_amount || 0), 0);
  
  // Return report data
  return {
    period: {
      start_date: start_date || null, // Start date
      end_date: end_date || null, // End date
    },
    summary: {
      note: 'All sales are walk-in customers (customer registration removed)',
      total_sales: totalSales, // Total sales count
      total_revenue: totalRevenue, // Total revenue
      total_discount: totalDiscount, // Total discount
    },
    sales: sales, // Sales array
  };
};

/**
 * Generate sales report by cashier
 * Generates sales report grouped by cashier (user)
 * @param {Object} filters - Filter options (start_date, end_date, user_id)
 * @returns {Promise<Object>} Sales report data by cashier
 */
const getSalesReportByCashier = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date, user_id } = filters; // Extract filters
  
  // Build where clause
  const where = {
    status: 'PAID', // Only paid sales (revenue-generating)
  }; // Initialize where clause
  
  // Add user filter
  if (user_id) {
    where.user_id = user_id; // Filter by user ID
  }
  
  // Add date filter
  if (start_date || end_date) {
    where.created_at = {}; // Initialize date filter
    if (start_date) {
      where.created_at[Op.gte] = new Date(start_date); // Greater than or equal to 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
      where.created_at[Op.lte] = endDate; // Less than or equal to end date
    }
  }
  
  // Query sales with aggregations using raw SQL for better grouping
  const cashierSalesData = await sequelize.query(`
    SELECT 
      s.user_id,
      COUNT(s.id) as sale_count,
      SUM(s.total) as total_revenue,
      SUM(s.discount_amount) as total_discount
    FROM sales s
    WHERE s.status = 'PAID'
      ${user_id ? `AND s.user_id = :user_id` : ''}
      ${start_date ? `AND s.created_at >= :start_date` : ''}
      ${end_date ? `AND s.created_at <= :end_date` : ''}
    GROUP BY s.user_id
    ORDER BY total_revenue DESC
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: {
      user_id: user_id || null,
      start_date: start_date ? new Date(start_date) : null,
      end_date: end_date ? (() => {
        const endDate = new Date(end_date);
        endDate.setHours(23, 59, 59, 999);
        return endDate;
      })() : null,
    },
  });
  
  // Fetch user details for each result
  const cashierSales = await Promise.all(cashierSalesData.map(async (item) => {
    // Fetch user if exists
    const user = item.user_id ? await User.findByPk(item.user_id) : null; // Get user if exists
    return {
      user_id: item.user_id, // User ID
      user: user, // User details
      sale_count: parseInt(item.sale_count || 0), // Number of sales
      total_revenue: parseFloat(item.total_revenue || 0), // Total revenue
      total_discount: parseFloat(item.total_discount || 0), // Total discount
    };
  }));
  
  // Calculate totals
  const totalRevenue = cashierSales.reduce((sum, item) => {
    // Sum total revenue from all cashiers
    return sum + parseFloat(item.total_revenue || 0); // Add revenue
  }, 0); // Total revenue
  
  // Return report data
  return {
    period: {
      start_date: start_date || null, // Start date
      end_date: end_date || null, // End date
    },
    summary: {
      total_cashiers: cashierSales.length, // Total cashiers
      total_revenue: totalRevenue, // Total revenue
    },
    cashiers: cashierSales, // Cashiers array
  };
};

/**
 * Generate payment methods report
 * Generates sales report grouped by payment method (CASH, CARD, MPESA)
 * @param {Object} filters - Filter options (start_date, end_date, provider)
 * @returns {Promise<Object>} Payment methods report data
 */
const getPaymentMethodsReport = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date, provider } = filters; // Extract filters
  
  // Build where clause for payments
  const paymentWhere = {
    status: 'SUCCESS', // Only successful payments (CONFIRMED or SUCCESS)
  }; // Initialize payment where clause
  
  // Add provider filter
  if (provider && provider !== 'ALL') {
    paymentWhere.provider = provider; // Filter by provider (CASH, CARD, MPESA)
  }
  
  // Build where clause for sales (for date filtering)
  const saleWhere = {
    status: 'PAID', // Only paid sales
  }; // Initialize sale where clause
  
  // Add date filter
  if (start_date || end_date) {
    saleWhere.created_at = {}; // Initialize date filter
    if (start_date) {
      saleWhere.created_at[Op.gte] = new Date(start_date); // Greater than or equal to 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
      saleWhere.created_at[Op.lte] = endDate; // Less than or equal to end date
    }
  }
  
  // Query payments with aggregations grouped by provider
  const paymentMethodsData = await sequelize.query(`
    SELECT 
      p.provider,
      COUNT(DISTINCT p.sale_id) as transaction_count,
      COUNT(p.id) as payment_count,
      SUM(p.amount) as total_amount
    FROM payments p
    INNER JOIN sales s ON p.sale_id = s.id
    WHERE p.status IN ('SUCCESS', 'CONFIRMED')
      AND s.status = 'PAID'
      ${provider && provider !== 'ALL' ? `AND p.provider = :provider` : ''}
      ${start_date ? `AND s.created_at >= :start_date` : ''}
      ${end_date ? `AND s.created_at <= :end_date` : ''}
    GROUP BY p.provider
    ORDER BY p.provider
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: {
      provider: provider && provider !== 'ALL' ? provider : null,
      start_date: start_date ? new Date(start_date) : null,
      end_date: end_date ? (() => {
        const endDate = new Date(end_date);
        endDate.setHours(23, 59, 59, 999);
        return endDate;
      })() : null,
    },
  });
  
  // Initialize payment methods totals
  const paymentMethods = {
    CASH: { provider: 'CASH', transaction_count: 0, payment_count: 0, total_amount: 0 },
    CARD: { provider: 'CARD', transaction_count: 0, payment_count: 0, total_amount: 0 },
    MPESA: { provider: 'MPESA', transaction_count: 0, payment_count: 0, total_amount: 0 },
  };
  
  // Populate payment methods from query results
  paymentMethodsData.forEach((item) => {
    if (paymentMethods[item.provider]) {
      paymentMethods[item.provider] = {
        provider: item.provider,
        transaction_count: parseInt(item.transaction_count || 0),
        payment_count: parseInt(item.payment_count || 0),
        total_amount: parseFloat(item.total_amount || 0),
      };
    }
  });
  
  // Calculate totals
  const totalRevenue = Object.values(paymentMethods).reduce((sum, method) => {
    return sum + parseFloat(method.total_amount || 0);
  }, 0); // Total revenue across all payment methods
  
  const totalTransactions = Object.values(paymentMethods).reduce((sum, method) => {
    return sum + parseInt(method.transaction_count || 0);
  }, 0); // Total transactions across all payment methods
  
  // Return report data
  return {
    period: {
      start_date: start_date || null, // Start date
      end_date: end_date || null, // End date
    },
    summary: {
      total_revenue: totalRevenue, // Total revenue across all methods
      total_transactions: totalTransactions, // Total transactions
    },
    payment_methods: Object.values(paymentMethods), // Payment methods array
  };
};

// Export sales report functions
module.exports = {
  getSalesReportByDate,
  getSalesReportByProduct,
  getSalesReportByCustomer,
  getSalesReportByCashier,
  getPaymentMethodsReport,
};

