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

// Get dashboard statistics
exports.getStats = async (req, res) => {
  try {
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    
    const yesterday = new Date(today);
    yesterday.setDate(yesterday.getDate() - 1);
    
    const startOfWeek = new Date(today);
    startOfWeek.setDate(startOfWeek.getDate() - 7);
    
    const startOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);
    
    const startOfPrevMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
    const endOfPrevMonth = new Date(today.getFullYear(), today.getMonth(), 0);

    // Today's stats
    const todayStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue'],
        [sequelize.literal('SUM(netSales) / NULLIF(SUM(totalTransactions), 0)'), 'avg_sale']
      ],
      where: {
        salesDate: today
      },
      raw: true
    });

    // Yesterday's stats for growth
    const yesterdayStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue']
      ],
      where: {
        salesDate: yesterday
      },
      raw: true
    });

    // Week stats
    const weekStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue']
      ],
      where: {
        salesDate: {
          [Op.gte]: startOfWeek
        }
      },
      raw: true
    });

    // Month stats
    const monthStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue']
      ],
      where: {
        salesDate: {
          [Op.gte]: startOfMonth
        }
      },
      raw: true
    });

    // Previous month stats
    const prevMonthStats = await DailySales.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('totalTransactions')), 'total_sales'],
        [sequelize.fn('SUM', sequelize.col('netSales')), 'total_revenue']
      ],
      where: {
        salesDate: {
          [Op.between]: [startOfPrevMonth, endOfPrevMonth]
        }
      },
      raw: true
    });

    // Inventory stats
    const inventoryStats = await Product.findOne({
      attributes: [
        [sequelize.fn('COUNT', sequelize.col('id')), 'total_products'],
        [sequelize.fn('SUM', sequelize.literal('CASE WHEN stock < reorderLevel THEN 1 ELSE 0 END')), 'low_stock']
      ],
      raw: true
    });

    // Calculate growth percentages
    const todaySalesGrowth = yesterdayStats?.total_sales > 0 
      ? ((todayStats.total_sales - yesterdayStats.total_sales) / yesterdayStats.total_sales * 100).toFixed(1)
      : 0;

    const todayRevenueGrowth = yesterdayStats?.total_revenue > 0
      ? ((todayStats.total_revenue - yesterdayStats.total_revenue) / yesterdayStats.total_revenue * 100).toFixed(1)
      : 0;

    const monthSalesGrowth = prevMonthStats?.total_sales > 0
      ? ((monthStats.total_sales - prevMonthStats.total_sales) / prevMonthStats.total_sales * 100).toFixed(1)
      : 0;

    res.json({
      today: {
        total_sales: parseInt(todayStats?.total_sales || 0),
        total_revenue: parseFloat(todayStats?.total_revenue || 0),
        avg_sale: parseFloat(todayStats?.avg_sale || 0),
        growth: parseFloat(todaySalesGrowth),
        revenue_growth: parseFloat(todayRevenueGrowth)
      },
      week: {
        total_sales: parseInt(weekStats?.total_sales || 0),
        total_revenue: parseFloat(weekStats?.total_revenue || 0)
      },
      month: {
        total_sales: parseInt(monthStats?.total_sales || 0),
        total_revenue: parseFloat(monthStats?.total_revenue || 0),
        growth: parseFloat(monthSalesGrowth)
      },
      inventory: {
        total_products: parseInt(inventoryStats?.total_products || 0),
        low_stock: parseInt(inventoryStats?.low_stock || 0)
      },
      orders: {
        pending: 0 // You can add logic for pending orders if needed
      }
    });
  } catch (error) {
    console.error('Error fetching stats:', error);
    res.status(500).json({ error: 'Failed to fetch statistics', details: error.message });
  }
};

// Get recent sales (daily sales records)
exports.getRecentSales = async (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 10;
    
    const sales = await DailySales.findAll({
      include: [
        {
          model: Store,
          as: 'store',
          attributes: ['id', 'name']
        },
        {
          model: Register,
          as: 'register',
          attributes: ['id', 'code']
        }
      ],
      order: [['salesDate', 'DESC'], ['createdAt', 'DESC']],
      limit
    });

    // Format for frontend compatibility
    const formattedSales = sales.map(sale => ({
      id: sale.id,
      total_amount: parseFloat(sale.netSales),
      payment_method: sale.cashSales > sale.cardSales ? 
        (sale.cashSales > sale.mobilePayments ? 'cash' : 'mobile') : 
        (sale.cardSales > sale.mobilePayments ? 'card' : 'mobile'),
      created_at: sale.salesDate,
      items_count: sale.totalItems
    }));

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

// Get all sales with filters
exports.getAllSales = async (req, res) => {
  try {
    const { startDate, endDate, storeId, registerId } = req.query;
    
    const whereClause = {};

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

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

    const sales = await DailySales.findAll({
      where: whereClause,
      include: [
        {
          model: Store,
          as: 'store',
          attributes: ['id', 'name']
        },
        {
          model: Register,
          as: 'register',
          attributes: ['id', 'code']
        }
      ],
      order: [['salesDate', 'DESC']]
    });

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

// Get sale by ID
exports.getSaleById = async (req, res) => {
  try {
    const { id } = req.params;

    const sale = await DailySales.findByPk(id, {
      include: [
        {
          model: Store,
          as: 'store',
          attributes: ['id', 'name', 'address']
        },
        {
          model: Register,
          as: 'register',
          attributes: ['id', 'code']
        }
      ]
    });

    if (!sale) {
      return res.status(404).json({ error: 'Sale record not found' });
    }

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

// Create or update daily sales record
exports.createSale = async (req, res) => {
  try {
    const { 
      storeId, 
      registerId, 
      salesDate,
      totalTransactions,
      totalItems,
      grossSales,
      discounts,
      taxes,
      netSales,
      refunds,
      cashSales,
      cardSales,
      mobilePayments
    } = req.body;

    const date = salesDate ? new Date(salesDate) : new Date();
    date.setHours(0, 0, 0, 0);

    // Find or create daily sales record
    const [dailySales, created] = await DailySales.findOrCreate({
      where: {
        storeId: storeId || null,
        registerId: registerId || null,
        salesDate: date
      },
      defaults: {
        totalTransactions: totalTransactions || 0,
        totalItems: totalItems || 0,
        grossSales: grossSales || 0,
        discounts: discounts || 0,
        taxes: taxes || 0,
        netSales: netSales || 0,
        refunds: refunds || 0,
        cashSales: cashSales || 0,
        cardSales: cardSales || 0,
        mobilePayments: mobilePayments || 0
      }
    });

    // If record exists, increment values
    if (!created) {
      await dailySales.increment({
        totalTransactions: totalTransactions || 0,
        totalItems: totalItems || 0,
        grossSales: grossSales || 0,
        discounts: discounts || 0,
        taxes: taxes || 0,
        netSales: netSales || 0,
        refunds: refunds || 0,
        cashSales: cashSales || 0,
        cardSales: cardSales || 0,
        mobilePayments: mobilePayments || 0
      });
    }

    res.status(created ? 201 : 200).json({
      success: true,
      dailySales,
      message: created ? 'Daily sales record created' : 'Daily sales record updated'
    });
  } catch (error) {
    console.error('Error creating/updating sale:', error);
    res.status(500).json({ error: 'Failed to create/update sale', details: error.message });
  }
};

// Update daily sales
exports.updateSale = async (req, res) => {
  try {
    const { id } = req.params;
    const dailySales = await DailySales.findByPk(id);

    if (!dailySales) {
      return res.status(404).json({ error: 'Daily sales record not found' });
    }

    await dailySales.update(req.body);

    res.json({
      success: true,
      dailySales,
      message: 'Daily sales record updated successfully'
    });
  } catch (error) {
    console.error('Error updating sale:', error);
    res.status(500).json({ error: 'Failed to update sale', details: error.message });
  }
};

// Delete daily sales
exports.deleteSale = async (req, res) => {
  try {
    const { id } = req.params;
    const dailySales = await DailySales.findByPk(id);

    if (!dailySales) {
      return res.status(404).json({ error: 'Daily sales record not found' });
    }

    await dailySales.destroy();

    res.json({
      success: true,
      message: 'Daily sales record deleted successfully'
    });
  } catch (error) {
    console.error('Error deleting sale:', error);
    res.status(500).json({ error: 'Failed to delete sale', details: error.message });
  }
};