/**
 * Financial Reports Service
 * Business logic for profit/loss reports generation
 */

// Import Sale and related models
const { Sale, SaleItem, Product, GRN, GRNItem, Return } = 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');

/**
 * Generate profit/loss report
 * Calculates profit/loss based on sales revenue and cost of goods sold
 * @param {Object} filters - Filter options (start_date, end_date)
 * @returns {Promise<Object>} Profit/loss report data
 */
const getProfitLossReport = async (filters = {}) => {
  // Extract filters
  const { start_date, end_date } = filters; // Extract date filters
  
  // Build date filter for sales
  let salesDateFilter = ''; // Initialize date filter
  const replacements = {}; // Initialize replacements
  
  // Add date filter for sales
  if (start_date) {
    salesDateFilter += 'AND s.created_at >= :sales_start_date '; // Add start date filter
    replacements.sales_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
    salesDateFilter += 'AND s.created_at <= :sales_end_date '; // Add end date filter
    replacements.sales_end_date = endDate; // Set end date
  }
  
  // Build date filter for GRNs (cost of goods)
  let grnDateFilter = ''; // Initialize date filter
  
  // Add date filter for GRNs
  if (start_date) {
    grnDateFilter += 'AND g.created_at >= :grn_start_date '; // Add start date filter
    replacements.grn_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
    grnDateFilter += 'AND g.created_at <= :grn_end_date '; // Add end date filter
    replacements.grn_end_date = endDate; // Set end date
  }
  
  // Calculate total revenue from sales (tax removed)
  // Use 'PAID' status instead of 'COMPLETED' (Sale model has DRAFT, PAID, CANCELLED)
  const revenueData = await sequelize.query(`
    SELECT 
      SUM(s.total) as total_revenue,
      SUM(s.discount_amount) as total_discount,
      COUNT(s.id) as total_sales
    FROM sales s
    WHERE s.status = 'PAID'
      ${salesDateFilter}
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: replacements,
  });
  
  // Extract revenue data
  const revenueResult = revenueData[0] || {}; // Get first result
  const totalRevenue = parseFloat(revenueResult.total_revenue || 0); // Total revenue
  const totalDiscount = parseFloat(revenueResult.total_discount || 0); // Total discount
  const totalSales = parseInt(revenueResult.total_sales || 0); // Total sales count
  
  // Calculate total returns/refunds (deduct from revenue)
  let returnsDateFilter = ''; // Initialize date filter for returns
  const returnsReplacements = { ...replacements }; // Copy replacements
  
  // Add date filter for returns (use same dates as sales)
  if (start_date) {
    returnsDateFilter += 'AND r.refunded_at >= :returns_start_date '; // Add start date filter
    returnsReplacements.returns_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
    returnsDateFilter += 'AND r.refunded_at <= :returns_end_date '; // Add end date filter
    returnsReplacements.returns_end_date = endDate; // Set end date
  }
  
  // Calculate total refunds from completed returns
  const returnsData = await sequelize.query(`
    SELECT 
      SUM(r.refund_amount) as total_refunds,
      COUNT(r.id) as total_returns
    FROM returns r
    WHERE r.status = 'COMPLETED'
      AND r.refunded_at IS NOT NULL
      ${returnsDateFilter}
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: returnsReplacements,
  });
  
  // Extract returns data
  const returnsResult = returnsData[0] || {}; // Get first result
  const totalRefunds = parseFloat(returnsResult.total_refunds || 0); // Total refunds
  const totalReturns = parseInt(returnsResult.total_returns || 0); // Total returns count
  
  // Calculate cost of goods sold (COGS) from GRNs
  // Note: This is a simplified calculation. In a real system, you'd track exact cost per unit sold
  // GRN is considered completed when processed_at is not null (no status field exists)
  const cogsData = await sequelize.query(`
    SELECT 
      SUM(gi.quantity * gi.unit_cost) as total_cogs
    FROM grn_items gi
    INNER JOIN grns g ON gi.grn_id = g.id
    WHERE g.processed_at IS NOT NULL
      ${grnDateFilter}
  `, {
    type: sequelize.QueryTypes.SELECT,
    replacements: replacements,
  });
  
  // Extract COGS data
  const cogsResult = cogsData[0] || {}; // Get first result
  const totalCogs = parseFloat(cogsResult.total_cogs || 0); // Total COGS
  
  // Calculate gross profit (revenue - discount - refunds - COGS)
  const grossProfit = totalRevenue - totalDiscount - totalRefunds - totalCogs; // Gross profit
  
  // Calculate gross profit margin (percentage)
  const grossProfitMargin = totalRevenue > 0 
    ? ((grossProfit / (totalRevenue - totalDiscount)) * 100) 
    : 0; // Gross profit margin percentage
  
  // Return profit/loss report
  return {
    period: {
      start_date: start_date || null, // Start date
      end_date: end_date || null, // End date
    },
    revenue: {
      total_revenue: totalRevenue, // Total revenue
      total_discount: totalDiscount, // Total discount
      total_refunds: totalRefunds, // Total refunds from returns
      net_revenue: totalRevenue - totalDiscount - totalRefunds, // Net revenue (after discount and refunds)
      total_sales: totalSales, // Total sales count
    },
    returns: {
      total_returns: totalReturns, // Total returns count
      total_refunds: totalRefunds, // Total refund amount
    },
    costs: {
      cost_of_goods_sold: totalCogs, // Cost of goods sold
    },
    profit: {
      gross_profit: grossProfit, // Gross profit (revenue - discount - refunds - COGS)
      gross_profit_margin: parseFloat(grossProfitMargin.toFixed(2)), // Gross profit margin percentage
    },
  };
};

// Export financial report functions
module.exports = {
  getProfitLossReport,
};

