// controllers/analyticsController.js
const { DailySales, Store, Register, Product,sequelize} = require('../models');
const { Op } = require('sequelize');
// const sequelize = require('../config/database');

// Get sales by period
exports.getSalesByPeriod = async (req, res) => {
  try {
    const { period = 'day', startDate, endDate } = req.query;

    let dateFormat;
    let groupBy;
    
    switch (period) {
      case 'hour':
        // For hourly, we'd need more granular data than DailySales provides
        return res.status(400).json({ 
          error: 'Hourly breakdown not available with DailySales model. Use daily or larger periods.' 
        });
      case 'day':
        dateFormat = '%Y-%m-%d';
        groupBy = ['salesDate'];
        break;
      case 'week':
        dateFormat = '%Y-%U';
        groupBy = [sequelize.fn('YEARWEEK', sequelize.col('salesDate'))];
        break;
      case 'month':
        dateFormat = '%Y-%m';
        groupBy = [sequelize.fn('DATE_FORMAT', sequelize.col('salesDate'), '%Y-%m')];
        break;
      case 'year':
        dateFormat = '%Y';
        groupBy = [sequelize.fn('YEAR', sequelize.col('salesDate'))];
        break;
      default:
        dateFormat = '%Y-%m-%d';
        groupBy = ['salesDate'];
    }

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }

    const salesByPeriod = await DailySales.findAll({
      attributes: [
        [sequelize.fn('DATE_FORMAT', sequelize.col('salesDate'), dateFormat), 'period'],
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.fn('SUM', sequelize.col('grossSales')), 'gross_sales'],
        [sequelize.fn('SUM', sequelize.col('discounts')), 'total_discounts'],
        [sequelize.fn('SUM', sequelize.col('taxes')), 'total_taxes'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'total_items'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_sale']
      ],
      where: whereClause,
      group: ['period'],
      order: [[sequelize.literal('period'), 'ASC']],
      raw: true
    });

    const formatted = salesByPeriod.map(item => ({
      period: item.period,
      total_sales: parseInt(item.total_sales || 0),
      total_revenue: parseFloat(item.total_revenue || 0),
      gross_sales: parseFloat(item.gross_sales || 0),
      total_discounts: parseFloat(item.total_discounts || 0),
      total_taxes: parseFloat(item.total_taxes || 0),
      total_items: parseInt(item.total_items || 0),
      avg_sale: parseFloat(item.avg_sale || 0)
    }));

    res.json(formatted);
  } catch (error) {
    console.error('Error fetching sales by period:', error);
    res.status(500).json({ error: 'Failed to fetch sales analytics', details: error.message });
  }
};

// Get payment methods statistics
exports.getPaymentMethodsStats = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }
    if (storeId) whereClause.storeId = storeId;
    if (registerId) whereClause.registerId = registerId;

    const paymentStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('cashSales')), 'cash_total'],
        [sequelize.fn('SUM', sequelize.col('cardSales')), 'card_total'],
        [sequelize.fn('SUM', sequelize.col('mobilePayments')), 'mobile_total'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'grand_total']
      ],
      where: whereClause,
      raw: true
    });

    const cashTotal = parseFloat(paymentStats.cash_total || 0);
    const cardTotal = parseFloat(paymentStats.card_total || 0);
    const mobileTotal = parseFloat(paymentStats.mobile_total || 0);
    const grandTotal = parseFloat(paymentStats.grand_total || 0);

    const paymentBreakdown = [
      {
        paymentMethod: 'cash',
        total: cashTotal,
        percentage: grandTotal > 0 ? ((cashTotal / grandTotal) * 100).toFixed(2) : 0,
        count: null // Not tracked in DailySales
      },
      {
        paymentMethod: 'card',
        total: cardTotal,
        percentage: grandTotal > 0 ? ((cardTotal / grandTotal) * 100).toFixed(2) : 0,
        count: null
      },
      {
        paymentMethod: 'mobile',
        total: mobileTotal,
        percentage: grandTotal > 0 ? ((mobileTotal / grandTotal) * 100).toFixed(2) : 0,
        count: null
      }
    ];

    res.json({
      breakdown: paymentBreakdown,
      total: grandTotal
    });
  } catch (error) {
    console.error('Error fetching payment methods stats:', error);
    res.status(500).json({ error: 'Failed to fetch payment analytics', details: error.message });
  }
};

// Get store performance comparison
exports.getStorePerformance = async (req, res) => {
  try {
    const { startDate, endDate, limit = 10 } = req.query;

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }

    const storePerformance = await DailySales.findAll({
      attributes: [
        'storeId',
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_transactions'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'total_items'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.fn('SUM', sequelize.col('grossSales')), 'gross_sales'],
        [sequelize.fn('SUM', sequelize.col('discounts')), 'total_discounts'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_transaction']
      ],
      where: whereClause,
      include: [
        {
          model: Store,
          as: 'store',
          attributes: ['id', 'name', 'address']
        }
      ],
      group: ['storeId', 'store.id'],
      order: [[sequelize.literal('total_revenue'), 'DESC']],
      limit: parseInt(limit)
    });

    res.json(storePerformance);
  } catch (error) {
    console.error('Error fetching store performance:', error);
    res.status(500).json({ error: 'Failed to fetch store performance', details: error.message });
  }
};

// Get register performance comparison
exports.getRegisterPerformance = async (req, res) => {
  try {
    const { startDate, endDate, storeId } = req.query;

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }
    if (storeId) whereClause.storeId = storeId;

    const registerPerformance = await DailySales.findAll({
      attributes: [
        'registerId',
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_transactions'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'total_items'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.fn('SUM', sequelize.col('cashSales')), 'cash_sales'],
        [sequelize.fn('SUM', sequelize.col('cardSales')), 'card_sales'],
        [sequelize.fn('SUM', sequelize.col('mobilePayments')), 'mobile_sales'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_transaction']
      ],
      where: whereClause,
      include: [
        {
          model: Register,
          as: 'register',
          attributes: ['id', 'registerNumber'],
          include: [
            {
              model: Store,
              as: 'store',
              attributes: ['id', 'name']
            }
          ]
        }
      ],
      group: ['registerId', 'register.id', 'register->store.id'],
      order: [[sequelize.literal('total_revenue'), 'DESC']]
    });

    res.json(registerPerformance);
  } catch (error) {
    console.error('Error fetching register performance:', error);
    res.status(500).json({ error: 'Failed to fetch register performance', details: error.message });
  }
};

// Get sales summary with detailed breakdown
exports.getSalesSummary = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }
    if (storeId) whereClause.storeId = storeId;
    if (registerId) whereClause.registerId = registerId;

    const summary = await DailySales.findOne({
      attributes: [
        [sequelize.fn('COUNT', sequelize.col('id')), 'total_days'],
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_transactions'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'total_items'],
        [sequelize.fn('SUM', sequelize.col('grossSales')), 'gross_sales'],
        [sequelize.fn('SUM', sequelize.col('discounts')), 'total_discounts'],
        [sequelize.fn('SUM', sequelize.col('taxes')), 'total_taxes'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'net_sales'],
        [sequelize.fn('SUM', sequelize.col('refunds')), 'total_refunds'],
        [sequelize.fn('SUM', sequelize.col('cashSales')), 'cash_sales'],
        [sequelize.fn('SUM', sequelize.col('cardSales')), 'card_sales'],
        [sequelize.fn('SUM', sequelize.col('mobilePayments')), 'mobile_payments'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_transaction'],
        [sequelize.literal('SUM(totalItems) / NULLIF(SUM(totalTransactions), 0)'), 'avg_items_per_transaction'],
        [sequelize.fn('MAX', sequelize.col('netSales')), 'max_daily_sales'],
        [sequelize.fn('MIN', sequelize.col('netSales')), 'min_daily_sales']
      ],
      where: whereClause,
      raw: true
    });

    // Get daily averages
    const totalDays = parseInt(summary.total_days || 0);
    const dailyAverage = totalDays > 0 ? parseFloat(summary.net_sales) / totalDays : 0;

    // Payment method breakdown
    const paymentBreakdown = {
      cash: {
        total: parseFloat(summary.cash_sales || 0),
        percentage: summary.net_sales > 0 ? ((summary.cash_sales / summary.net_sales) * 100).toFixed(2) : 0
      },
      card: {
        total: parseFloat(summary.card_sales || 0),
        percentage: summary.net_sales > 0 ? ((summary.card_sales / summary.net_sales) * 100).toFixed(2) : 0
      },
      mobile: {
        total: parseFloat(summary.mobile_payments || 0),
        percentage: summary.net_sales > 0 ? ((summary.mobile_payments / summary.net_sales) * 100).toFixed(2) : 0
      }
    };

    res.json({
      summary: {
        total_days: totalDays,
        total_transactions: parseInt(summary.total_transactions || 0),
        total_items: parseInt(summary.total_items || 0),
        gross_sales: parseFloat(summary.gross_sales || 0),
        total_discounts: parseFloat(summary.total_discounts || 0),
        total_taxes: parseFloat(summary.total_taxes || 0),
        net_sales: parseFloat(summary.net_sales || 0),
        total_refunds: parseFloat(summary.total_refunds || 0),
        avg_transaction: parseFloat(summary.avg_transaction || 0),
        avg_items_per_transaction: parseFloat(summary.avg_items_per_transaction || 0),
        daily_average: dailyAverage,
        max_daily_sales: parseFloat(summary.max_daily_sales || 0),
        min_daily_sales: parseFloat(summary.min_daily_sales || 0)
      },
      paymentBreakdown
    });
  } catch (error) {
    console.error('Error generating sales summary:', error);
    res.status(500).json({ error: 'Failed to generate sales summary', details: error.message });
  }
};

// Get sales trend analysis
exports.getSalesTrend = async (req, res) => {
  try {
    const { days = 30, storeId, registerId } = req.query;

    const startDate = new Date();
    startDate.setDate(startDate.getDate() - parseInt(days));
    startDate.setHours(0, 0, 0, 0);

    const whereClause = {
      salesDate: { [Op.gte]: startDate }
    };
    if (storeId) whereClause.storeId = storeId;
    if (registerId) whereClause.registerId = registerId;

    const trendData = await DailySales.findAll({
      attributes: [
        [sequelize.fn('DATE_FORMAT', sequelize.col('salesDate'), '%Y-%m-%d'), 'date'],
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'transactions'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'revenue'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'items']
      ],
      where: whereClause,
      group: [sequelize.fn('DATE', sequelize.col('salesDate'))],
      order: [[sequelize.literal('date'), 'ASC']],
      raw: true
    });

    // Calculate moving averages
    const movingAvgWindow = 7;
    const trendWithAvg = trendData.map((day, index) => {
      const start = Math.max(0, index - movingAvgWindow + 1);
      const window = trendData.slice(start, index + 1);
      const avgRevenue = window.reduce((sum, d) => sum + parseFloat(d.revenue), 0) / window.length;

      return {
        date: day.date,
        transactions: parseInt(day.transactions),
        revenue: parseFloat(day.revenue),
        items: parseInt(day.items),
        moving_avg_revenue: parseFloat(avgRevenue.toFixed(2))
      };
    });

    res.json(trendWithAvg);
  } catch (error) {
    console.error('Error fetching sales trend:', error);
    res.status(500).json({ error: 'Failed to fetch sales trend', details: error.message });
  }
};

// Get sales comparison (this period vs previous period)
exports.getSalesComparison = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ error: 'startDate and endDate are required' });
    }

    const start = new Date(startDate);
    start.setHours(0, 0, 0, 0);
    const end = new Date(endDate);
    end.setHours(23, 59, 59, 999);

    // Calculate period length in days
    const periodLength = Math.ceil((end - start) / (1000 * 60 * 60 * 24));
    const prevStart = new Date(start);
    prevStart.setDate(prevStart.getDate() - periodLength);
    const prevEnd = new Date(start);

    const baseWhereClause = {};
    if (storeId) baseWhereClause.storeId = storeId;
    if (registerId) baseWhereClause.registerId = registerId;

    // Current period stats
    const currentStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.fn('SUM', sequelize.col('grossSales')), 'gross_sales'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'total_items'],
        [sequelize.fn('SUM', sequelize.col('discounts')), 'total_discounts'],
        [sequelize.fn('SUM', sequelize.col('refunds')), 'total_refunds'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_sale']
      ],
      where: {
        ...baseWhereClause,
        salesDate: { [Op.between]: [start, end] }
      },
      raw: true
    });

    // Previous period stats
    const previousStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.fn('SUM', sequelize.col('grossSales')), 'gross_sales'],
        [sequelize.fn('SUM', sequelize.col('totalItems')), 'total_items'],
        [sequelize.fn('SUM', sequelize.col('discounts')), 'total_discounts'],
        [sequelize.fn('SUM', sequelize.col('refunds')), 'total_refunds'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_sale']
      ],
      where: {
        ...baseWhereClause,
        salesDate: { [Op.between]: [prevStart, prevEnd] }
      },
      raw: true
    });

    // Calculate growth percentages
    const calculateGrowth = (current, previous) => {
      if (!previous || previous === 0) return 0;
      return (((current - previous) / previous) * 100).toFixed(2);
    };

    const comparison = {
      current: {
        total_sales: parseInt(currentStats.total_sales || 0),
        total_revenue: parseFloat(currentStats.total_revenue || 0),
        gross_sales: parseFloat(currentStats.gross_sales || 0),
        total_items: parseInt(currentStats.total_items || 0),
        total_discounts: parseFloat(currentStats.total_discounts || 0),
        total_refunds: parseFloat(currentStats.total_refunds || 0),
        avg_sale: parseFloat(currentStats.avg_sale || 0)
      },
      previous: {
        total_sales: parseInt(previousStats.total_sales || 0),
        total_revenue: parseFloat(previousStats.total_revenue || 0),
        gross_sales: parseFloat(previousStats.gross_sales || 0),
        total_items: parseInt(previousStats.total_items || 0),
        total_discounts: parseFloat(previousStats.total_discounts || 0),
        total_refunds: parseFloat(previousStats.total_refunds || 0),
        avg_sale: parseFloat(previousStats.avg_sale || 0)
      },
      growth: {
        sales: parseFloat(calculateGrowth(currentStats.total_sales, previousStats.total_sales)),
        revenue: parseFloat(calculateGrowth(currentStats.total_revenue, previousStats.total_revenue)),
        items: parseFloat(calculateGrowth(currentStats.total_items, previousStats.total_items)),
        avg_sale: parseFloat(calculateGrowth(currentStats.avg_sale, previousStats.avg_sale))
      },
      period: {
        start: start.toISOString().split('T')[0],
        end: end.toISOString().split('T')[0],
        days: periodLength
      }
    };

    res.json(comparison);
  } catch (error) {
    console.error('Error fetching sales comparison:', error);
    res.status(500).json({ error: 'Failed to fetch sales comparison', details: error.message });
  }
};

// Get best performing days of week
exports.getDayOfWeekAnalysis = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }
    if (storeId) whereClause.storeId = storeId;
    if (registerId) whereClause.registerId = registerId;

    const dayOfWeekStats = await DailySales.findAll({
      attributes: [
        [sequelize.fn('DAYNAME', sequelize.col('salesDate')), 'day_name'],
        [sequelize.fn('DAYOFWEEK', sequelize.col('salesDate')), 'day_number'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'occurrence_count'],
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_transactions'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.fn('AVG', sequelize.col('netSales')), 'avg_daily_revenue'],
        [sequelize.fn('AVG', sequelize.col('totalTransactions')), 'avg_daily_transactions']
      ],
      where: whereClause,
      group: ['day_name', 'day_number'],
      order: [[sequelize.literal('day_number'), 'ASC']],
      raw: true
    });

    const formatted = dayOfWeekStats.map(day => ({
      day_name: day.day_name,
      day_number: parseInt(day.day_number),
      occurrence_count: parseInt(day.occurrence_count),
      total_transactions: parseInt(day.total_transactions),
      total_revenue: parseFloat(day.total_revenue),
      avg_daily_revenue: parseFloat(day.avg_daily_revenue),
      avg_daily_transactions: parseFloat(day.avg_daily_transactions)
    }));

    res.json(formatted);
  } catch (error) {
    console.error('Error fetching day of week analysis:', error);
    res.status(500).json({ error: 'Failed to fetch day of week analysis', details: error.message });
  }
};

// Get refund analysis
exports.getRefundAnalysis = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;

    const whereClause = {};
    if (startDate) whereClause.salesDate = { [Op.gte]: new Date(startDate) };
    if (endDate) {
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      whereClause.salesDate = { ...whereClause.salesDate, [Op.lte]: end };
    }
    if (storeId) whereClause.storeId = storeId;
    if (registerId) whereClause.registerId = registerId;

    const refundStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('refunds')), 'total_refunds'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_sales'],
        [sequelize.fn('COUNT', sequelize.literal('CASE WHEN refunds > 0 THEN 1 END')), 'days_with_refunds']
      ],
      where: whereClause,
      raw: true
    });

    const totalRefunds = parseFloat(refundStats.total_refunds || 0);
    const totalSales = parseFloat(refundStats.total_sales || 0);
    const refundRate = totalSales > 0 ? ((totalRefunds / totalSales) * 100).toFixed(2) : 0;

    res.json({
      total_refunds: totalRefunds,
      total_sales: totalSales,
      refund_rate: parseFloat(refundRate),
      days_with_refunds: parseInt(refundStats.days_with_refunds || 0)
    });
  } catch (error) {
    console.error('Error fetching refund analysis:', error);
    res.status(500).json({ error: 'Failed to fetch refund analysis', details: error.message });
  }
};