const { Order, OrderItem, Product, User } = require('../models');
const { Op, fn, col, literal } = require('sequelize');
const sequelize = require('../config/database');

class ReportService {
  async getDashboardStats() {
    const totalRevenue = await Order.sum('total', {
      where: { status: { [Op.notIn]: ['cancelled', 'refunded'] } },
    }) || 0;

    const totalOrders = await Order.count();
    const pendingOrders = await Order.count({ where: { status: 'pending' } });
    const processingOrders = await Order.count({ where: { status: 'processing' } });
    const shippedOrders = await Order.count({ where: { status: 'shipped' } });
    const deliveredOrders = await Order.count({ where: { status: 'delivered' } });
    const cancelledOrders = await Order.count({ where: { status: 'cancelled' } });

    const totalProducts = await Product.count({ where: { isActive: true } });
    const totalCustomers = await User.count({ where: { isAdmin: false } });

    const todayStart = new Date();
    todayStart.setHours(0, 0, 0, 0);
    const todayRevenue = await Order.sum('total', {
      where: {
        createdAt: { [Op.gte]: todayStart },
        status: { [Op.notIn]: ['cancelled', 'refunded'] },
      },
    }) || 0;

    const thisMonthStart = new Date();
    thisMonthStart.setDate(1);
    thisMonthStart.setHours(0, 0, 0, 0);
    const monthRevenue = await Order.sum('total', {
      where: {
        createdAt: { [Op.gte]: thisMonthStart },
        status: { [Op.notIn]: ['cancelled', 'refunded'] },
      },
    }) || 0;

    return {
      totalRevenue: parseFloat(totalRevenue.toFixed(2)),
      totalOrders,
      pendingOrders,
      processingOrders,
      shippedOrders,
      deliveredOrders,
      cancelledOrders,
      totalProducts,
      totalCustomers,
      todayRevenue: parseFloat(todayRevenue.toFixed(2)),
      monthRevenue: parseFloat(monthRevenue.toFixed(2)),
    };
  }

  async getSalesChartData(days = 30) {
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - days);

    const data = await Order.findAll({
      attributes: [
        [fn('date', col('created_at')), 'date'],
        [fn('sum', col('total')), 'revenue'],
        [fn('count', col('id')), 'orders'],
      ],
      where: {
        createdAt: { [Op.gte]: startDate },
        status: { [Op.notIn]: ['cancelled', 'refunded'] },
      },
      group: [fn('date', col('created_at'))],
      order: [[fn('date', col('created_at')), 'ASC']],
      raw: true,
    });

    return data;
  }

  async getTopProducts(limit = 10) {
    return OrderItem.findAll({
      attributes: [
        'productId',
        'productName',
        [fn('sum', col('quantity')), 'totalSold'],
        [fn('sum', col('total')), 'totalRevenue'],
      ],
      include: [{
        model: Order,
        attributes: [],
        where: { status: { [Op.notIn]: ['cancelled', 'refunded'] } },
      }],
      group: ['productId'],
      order: [[fn('sum', col('quantity')), 'DESC']],
      limit,
      raw: true,
    });
  }

  async getRecentOrders(limit = 10) {
    return Order.findAll({
      order: [['createdAt', 'DESC']],
      limit,
      raw: true,
    });
  }

  async getLowStockProducts(threshold = 5) {
    return Product.findAll({
      where: {
        isActive: true,
        stockQuantity: { [Op.lte]: threshold },
      },
      order: [['stockQuantity', 'ASC']],
    });
  }
}

module.exports = new ReportService();
