// controllers/reportController.js
const { Transaction, TransactionItem, TransactionPayment, Product, User, Store, Register, Category, Brand, sequelize } = require('../models');
const { Op } = require('sequelize');

// ==================== SALES REPORT ====================
exports.getSalesReport = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId, groupBy = 'day' } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const where = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) where.storeId = storeId;
    if (registerId) where.registerId = registerId;

    // Determine grouping format
    let dateFormat;
    switch(groupBy) {
      case 'hour':
        dateFormat = sequelize.fn('DATE_FORMAT', sequelize.col('transactionDate'), '%Y-%m-%d %H:00:00');
        break;
      case 'week':
        dateFormat = sequelize.fn('YEARWEEK', sequelize.col('transactionDate'));
        break;
      case 'month':
        dateFormat = sequelize.fn('DATE_FORMAT', sequelize.col('transactionDate'), '%Y-%m');
        break;
      default: // day
        dateFormat = sequelize.fn('DATE', sequelize.col('transactionDate'));
    }

    const salesData = await Transaction.findAll({
      where,
      attributes: [
        [dateFormat, 'date'],
        [sequelize.fn('COUNT', sequelize.col('Transaction.id')), 'totalTransactions'],
        [sequelize.fn('SUM', sequelize.col('subtotal')), 'totalSales'],
        [sequelize.fn('SUM', sequelize.col('taxAmount')), 'totalTax'],
        [sequelize.fn('SUM', sequelize.col('discountAmount')), 'totalDiscount'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalRevenue']
      ],
      group: [dateFormat],
      order: [[dateFormat, 'ASC']],
      raw: true
    });

    // Calculate totals
    const totals = {
      totalTransactions: salesData.reduce((sum, row) => sum + parseInt(row.totalTransactions || 0), 0),
      totalSales: salesData.reduce((sum, row) => sum + parseFloat(row.totalSales || 0), 0),
      totalTax: salesData.reduce((sum, row) => sum + parseFloat(row.totalTax || 0), 0),
      totalDiscount: salesData.reduce((sum, row) => sum + parseFloat(row.totalDiscount || 0), 0),
      totalRevenue: salesData.reduce((sum, row) => sum + parseFloat(row.totalRevenue || 0), 0)
    };

    res.status(200).json({
      success: true,
      data: salesData,
      totals,
      period: { startDate, endDate, groupBy }
    });

  } catch (error) {
    console.error('Sales report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating sales report', 
      error: error.message 
    });
  }
};

// ==================== TOP SELLING PRODUCTS ====================
exports.getTopSellingProducts = async (req, res) => {
  try {
    const { startDate, endDate, storeId, limit = 20, sortBy = 'quantity' } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const transactionWhere = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) transactionWhere.storeId = storeId;

    // Determine sort field - specify table name to avoid ambiguity
    const sortField = sortBy === 'revenue' 
      ? sequelize.fn('SUM', sequelize.col('TransactionItem.totalAmount'))
      : sequelize.fn('SUM', sequelize.col('TransactionItem.quantity'));

    const topProducts = await TransactionItem.findAll({
      attributes: [
        'productId',
        [sequelize.fn('SUM', sequelize.col('TransactionItem.quantity')), 'totalQuantity'],
        [sequelize.fn('SUM', sequelize.col('TransactionItem.totalAmount')), 'totalRevenue'],
        [sequelize.fn('AVG', sequelize.col('TransactionItem.unitPrice')), 'avgPrice'],
        [sequelize.fn('COUNT', sequelize.literal('DISTINCT transaction.id')), 'transactionCount']
      ],
      include: [
        {
          model: Transaction,
          as: 'transaction',
          where: transactionWhere,
          attributes: []
        },
        {
          model: Product,
          as: 'product',
          attributes: ['id', 'name', 'sku', 'unitPrice', 'imageUrl'],
          include: [
            {
              model: Category,
              as: 'category',
              attributes: ['id', 'name']
            },
            {
              model: Brand,
              as: 'brand',
              attributes: ['id', 'name']
            }
          ]
        }
      ],
      group: ['productId', 'product.id', 'product->category.id', 'product->brand.id'],
      order: [[sortField, 'DESC']],
      limit: parseInt(limit),
      raw: false,
      subQuery: false
    });

    res.status(200).json({
      success: true,
      data: topProducts,
      period: { startDate, endDate },
      sortedBy: sortBy
    });

  } catch (error) {
    console.error('Top products report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error fetching top selling products', 
      error: error.message 
    });
  }
};

// ==================== PAYMENT METHOD REPORT ====================
exports.getPaymentMethodReport = async (req, res) => {
  try {
    const { startDate, endDate, storeId, includeStatus = 'true' } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const transactionWhere = {
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) transactionWhere.storeId = storeId;

    // Group by payment method
    const groupFields = includeStatus === 'true' 
      ? ['paymentMethod', 'status']
      : ['paymentMethod'];

    const paymentSummary = await TransactionPayment.findAll({
      attributes: [
        'paymentMethod',
        ...(includeStatus === 'true' ? ['status'] : []),
        [sequelize.fn('COUNT', sequelize.col('TransactionPayment.id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('amount')), 'totalAmount'],
        [sequelize.fn('AVG', sequelize.col('amount')), 'avgAmount'],
        [sequelize.fn('MIN', sequelize.col('amount')), 'minAmount'],
        [sequelize.fn('MAX', sequelize.col('amount')), 'maxAmount']
      ],
      include: [{
        model: Transaction,
        as: 'transaction',
        where: transactionWhere,
        attributes: []
      }],
      group: groupFields,
      order: [[sequelize.fn('SUM', sequelize.col('amount')), 'DESC']],
      raw: true
    });

    // Calculate percentage distribution
    const totalAmount = paymentSummary.reduce((sum, row) => sum + parseFloat(row.totalAmount || 0), 0);
    const enrichedData = paymentSummary.map(row => ({
      ...row,
      percentage: totalAmount > 0 ? ((parseFloat(row.totalAmount) / totalAmount) * 100).toFixed(2) : 0
    }));

    res.status(200).json({
      success: true,
      data: enrichedData,
      totals: {
        totalAmount,
        totalTransactions: paymentSummary.reduce((sum, row) => sum + parseInt(row.count || 0), 0)
      },
      period: { startDate, endDate }
    });

  } catch (error) {
    console.error('Payment method report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating payment report', 
      error: error.message 
    });
  }
};

// ==================== CASH VS MOBILE MONEY DETAILED REPORT ====================
exports.getCashVsMobileMoneyReport = async (req, res) => {
  try {
    const { startDate, endDate, storeId, groupBy = 'day' } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const where = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) where.storeId = storeId;

    // Date grouping
    let dateFormat;
    switch(groupBy) {
      case 'hour':
        dateFormat = sequelize.fn('DATE_FORMAT', sequelize.col('Transaction.transactionDate'), '%Y-%m-%d %H:00:00');
        break;
      case 'week':
        dateFormat = sequelize.fn('YEARWEEK', sequelize.col('Transaction.transactionDate'));
        break;
      case 'month':
        dateFormat = sequelize.fn('DATE_FORMAT', sequelize.col('Transaction.transactionDate'), '%Y-%m');
        break;
      default:
        dateFormat = sequelize.fn('DATE', sequelize.col('Transaction.transactionDate'));
    }

    const paymentData = await TransactionPayment.findAll({
      attributes: [
        [dateFormat, 'date'],
        'paymentMethod',
        'status',
        [sequelize.fn('COUNT', sequelize.col('TransactionPayment.id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('amount')), 'totalAmount']
      ],
      include: [{
        model: Transaction,
        as: 'transaction',
        where,
        attributes: []
      }],
      group: [dateFormat, 'paymentMethod', 'status'],
      order: [[dateFormat, 'ASC']],
      raw: true
    });

    // Process data for easy consumption
    const processedData = {};
    paymentData.forEach(row => {
      const date = row.date;
      if (!processedData[date]) {
        processedData[date] = {
          date,
          cash: { completed: 0, pending: 0, failed: 0, total: 0, count: 0 },
          card: { completed: 0, pending: 0, failed: 0, total: 0, count: 0 },
          mobile_money: { completed: 0, pending: 0, failed: 0, total: 0, count: 0 }
        };
      }

      const method = row.paymentMethod || 'cash';
      const status = row.status || 'completed';
      const amount = parseFloat(row.totalAmount || 0);
      const count = parseInt(row.count || 0);

      if (processedData[date][method]) {
        processedData[date][method][status] += amount;
        processedData[date][method].total += amount;
        processedData[date][method].count += count;
      }
    });

    // Convert to array
    const result = Object.values(processedData);

    res.status(200).json({
      success: true,
      data: result,
      period: { startDate, endDate, groupBy }
    });

  } catch (error) {
    console.error('Cash vs Mobile Money report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating cash vs mobile money report', 
      error: error.message 
    });
  }
};

// ==================== CASHIER PERFORMANCE REPORT ====================
exports.getCashierPerformanceReport = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const where = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) where.storeId = storeId;
    if (registerId) where.registerId = registerId;

    const cashierStats = await Transaction.findAll({
      attributes: [
        'cashierId',
        [sequelize.fn('COUNT', sequelize.col('Transaction.id')), 'transactionCount'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalSales'],
        [sequelize.fn('AVG', sequelize.col('totalAmount')), 'avgTransactionValue'],
        [sequelize.fn('SUM', sequelize.col('discountAmount')), 'totalDiscounts'],
        [sequelize.fn('MIN', sequelize.col('transactionDate')), 'firstTransaction'],
        [sequelize.fn('MAX', sequelize.col('transactionDate')), 'lastTransaction']
      ],
      where,
      include: [{
        model: User,
        as: 'cashier',
        attributes: ['id', 'firstName', 'lastName', 'email', 'role']
      }],
      group: ['cashierId', 'cashier.id'],
      order: [[sequelize.fn('SUM', sequelize.col('totalAmount')), 'DESC']],
      raw: false
    });

    // Enrich with additional metrics
    const enrichedStats = await Promise.all(cashierStats.map(async (stat) => {
      // Get item count
      const itemCount = await TransactionItem.sum('quantity', {
        include: [{
          model: Transaction,
          as: 'transaction',
          where: { 
            ...where,
            cashierId: stat.cashierId 
          },
          attributes: []
        }]
      });

      // Get voided/suspended transactions
      const voidedCount = await Transaction.count({
        where: {
          ...where,
          cashierId: stat.cashierId,
          status: 'voided'
        }
      });

      const suspendedCount = await Transaction.count({
        where: {
          ...where,
          cashierId: stat.cashierId,
          isSuspended: true
        }
      });

      return {
        ...stat.toJSON(),
        totalItems: itemCount || 0,
        voidedTransactions: voidedCount,
        suspendedTransactions: suspendedCount,
        itemsPerTransaction: itemCount && stat.transactionCount 
          ? (itemCount / stat.transactionCount).toFixed(2) 
          : 0
      };
    }));

    res.status(200).json({
      success: true,
      data: enrichedStats,
      period: { startDate, endDate }
    });

  } catch (error) {
    console.error('Cashier performance report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating cashier performance report', 
      error: error.message 
    });
  }
};

// ==================== STORE COMPARISON REPORT ====================
exports.getStoreComparisonReport = async (req, res) => {
  try {
    const { startDate, endDate } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const storeStats = await Transaction.findAll({
      attributes: [
        'storeId',
        [sequelize.fn('COUNT', sequelize.col('Transaction.id')), 'transactionCount'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalSales'],
        [sequelize.fn('AVG', sequelize.col('totalAmount')), 'avgTransactionValue'],
        [sequelize.fn('SUM', sequelize.col('taxAmount')), 'totalTax'],
        [sequelize.fn('SUM', sequelize.col('discountAmount')), 'totalDiscounts']
      ],
      where: {
        status: 'completed',
        transactionDate: {
          [Op.between]: [new Date(startDate), new Date(endDate)]
        }
      },
      include: [{
        model: Store,
        as: 'store',
        attributes: ['id', 'name', 'address', 'phone']
      }],
      group: ['storeId', 'store.id'],
      order: [[sequelize.fn('SUM', sequelize.col('totalAmount')), 'DESC']],
      raw: false
    });

    // Calculate market share
    const totalRevenue = storeStats.reduce((sum, store) => 
      sum + parseFloat(store.dataValues.totalSales || 0), 0
    );

    const enrichedStats = storeStats.map(store => ({
      ...store.toJSON(),
      marketShare: totalRevenue > 0 
        ? ((parseFloat(store.dataValues.totalSales) / totalRevenue) * 100).toFixed(2)
        : 0
    }));

    res.status(200).json({
      success: true,
      data: enrichedStats,
      totals: {
        totalRevenue,
        totalStores: storeStats.length
      },
      period: { startDate, endDate }
    });

  } catch (error) {
    console.error('Store comparison report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating store comparison report', 
      error: error.message 
    });
  }
};

// ==================== HOURLY SALES PATTERN ====================
exports.getHourlySalesPattern = async (req, res) => {
  try {
    const { startDate, endDate, storeId } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const where = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) where.storeId = storeId;

    const hourlyData = await Transaction.findAll({
      attributes: [
        [sequelize.fn('HOUR', sequelize.col('transactionDate')), 'hour'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'transactionCount'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalSales'],
        [sequelize.fn('AVG', sequelize.col('totalAmount')), 'avgSales']
      ],
      where,
      group: [sequelize.fn('HOUR', sequelize.col('transactionDate'))],
      order: [[sequelize.fn('HOUR', sequelize.col('transactionDate')), 'ASC']],
      raw: true
    });

    res.status(200).json({
      success: true,
      data: hourlyData,
      period: { startDate, endDate }
    });

  } catch (error) {
    console.error('Hourly sales pattern error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating hourly sales pattern', 
      error: error.message 
    });
  }
};

// ==================== PRODUCT CATEGORY REPORT ====================
exports.getProductCategoryReport = async (req, res) => {
  try {
    const { startDate, endDate, storeId } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const transactionWhere = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) transactionWhere.storeId = storeId;

    const categoryData = await TransactionItem.findAll({
      attributes: [
        [sequelize.col('product->category.id'), 'categoryId'],
        [sequelize.col('product->category.name'), 'categoryName'],
        [sequelize.fn('COUNT', sequelize.literal('DISTINCT transaction.id')), 'transactionCount'],
        [sequelize.fn('SUM', sequelize.col('TransactionItem.quantity')), 'totalQuantity'],
        [sequelize.fn('SUM', sequelize.col('TransactionItem.totalAmount')), 'totalRevenue'],
        [sequelize.fn('AVG', sequelize.col('TransactionItem.totalAmount')), 'avgRevenue']
      ],
      include: [
        {
          model: Transaction,
          as: 'transaction',
          where: transactionWhere,
          attributes: []
        },
        {
          model: Product,
          as: 'product',
          attributes: [],
          include: [{
            model: Category,
            as: 'category',
            attributes: []
          }]
        }
      ],
      group: [sequelize.col('product->category.id'), sequelize.col('product->category.name')],
      order: [[sequelize.fn('SUM', sequelize.col('TransactionItem.totalAmount')), 'DESC']],
      raw: true
    });

    // Calculate percentages
    const totalRevenue = categoryData.reduce((sum, cat) => 
      sum + parseFloat(cat.totalRevenue || 0), 0
    );

    const enrichedData = categoryData.map(cat => ({
      ...cat,
      percentage: totalRevenue > 0 
        ? ((parseFloat(cat.totalRevenue) / totalRevenue) * 100).toFixed(2)
        : 0
    }));

    res.status(200).json({
      success: true,
      data: enrichedData,
      totals: { totalRevenue },
      period: { startDate, endDate }
    });

  } catch (error) {
    console.error('Product category report error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating product category report', 
      error: error.message 
    });
  }
};

// ==================== COMPREHENSIVE DASHBOARD SUMMARY ====================
exports.getDashboardSummary = async (req, res) => {
  try {
    const { startDate, endDate, storeId } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({ 
        success: false, 
        message: 'Start date and end date are required' 
      });
    }

    const where = {
      status: 'completed',
      transactionDate: {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      }
    };

    if (storeId) where.storeId = storeId;

    // Get overall stats
    const [overallStats, paymentStats, topProducts, hourlyPattern] = await Promise.all([
      // Overall transaction stats
      Transaction.findOne({
        attributes: [
          [sequelize.fn('COUNT', sequelize.col('id')), 'totalTransactions'],
          [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalRevenue'],
          [sequelize.fn('SUM', sequelize.col('taxAmount')), 'totalTax'],
          [sequelize.fn('SUM', sequelize.col('discountAmount')), 'totalDiscounts'],
          [sequelize.fn('AVG', sequelize.col('totalAmount')), 'avgTransactionValue']
        ],
        where,
        raw: true
      }),

      // Payment method breakdown
      TransactionPayment.findAll({
        attributes: [
          'paymentMethod',
          [sequelize.fn('COUNT', sequelize.col('TransactionPayment.id')), 'count'],
          [sequelize.fn('SUM', sequelize.col('amount')), 'total']
        ],
        include: [{
          model: Transaction,
          as: 'transaction',
          where,
          attributes: []
        }],
        group: ['paymentMethod'],
        raw: true
      }),

      // Top 5 products
      TransactionItem.findAll({
        attributes: [
          'productId',
          [sequelize.fn('SUM', sequelize.col('TransactionItem.quantity')), 'totalQuantity'],
          [sequelize.fn('SUM', sequelize.col('TransactionItem.totalAmount')), 'totalRevenue']
        ],
        include: [
          {
            model: Transaction,
            as: 'transaction',
            where,
            attributes: []
          },
          {
            model: Product,
            as: 'product',
            attributes: ['name', 'sku']
          }
        ],
        group: ['productId'],
        order: [[sequelize.fn('SUM', sequelize.col('TransactionItem.totalAmount')), 'DESC']],
        limit: 5,
        raw: false
      }),

      // Peak hours
      Transaction.findAll({
        attributes: [
          [sequelize.fn('HOUR', sequelize.col('transactionDate')), 'hour'],
          [sequelize.fn('COUNT', sequelize.col('id')), 'count']
        ],
        where,
        group: [sequelize.fn('HOUR', sequelize.col('transactionDate'))],
        order: [[sequelize.fn('COUNT', sequelize.col('id')), 'DESC']],
        limit: 3,
        raw: true
      })
    ]);

    res.status(200).json({
      success: true,
      summary: {
        overview: overallStats,
        paymentMethods: paymentStats,
        topProducts,
        peakHours: hourlyPattern
      },
      period: { startDate, endDate }
    });

  } catch (error) {
    console.error('Dashboard summary error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error generating dashboard summary', 
      error: error.message 
    });
  }
};