const { User, Transaction, Trade, BinaryTrade } = require("../../models");
const { Op , fn, col } = require("sequelize");

class adminDashboardService {

  async getOverview() {

    // ✅ TOTAL REVENUE (Deposits only)
    const totalRevenue = await Transaction.sum("amount", {
      where: {
        kind: "deposit",
        status: "completed"
      }
    });
    

    // ✅ TOTAL USERS
    const totalUsers = await User.count();

    
    const activeOrders = await BinaryTrade.count({
      where: {
        status: "open"
      }
    });
     
    // ✅ CONVERSION RATE
    // (users who made deposit / total users)
    const usersWithDeposit = await Transaction.count({
      distinct: true,
      col: "userId",
      where: {
        kind: "deposit",
        status: "completed"
      }
    });
  
    const conversion = totalUsers === 0
      ? 0
      : ((usersWithDeposit / totalUsers) * 100).toFixed(2);

    // ✅ MONTHLY DATA (for charts)
    const monthlyData = await Transaction.findAll({
      attributes: [
        [require("sequelize").fn("MONTH", require("sequelize").col("created_at")), "month"],
        [require("sequelize").fn("SUM", require("sequelize").col("amount")), "revenue"]
      ],
      where: {
        kind: "deposit",
        status: "completed"
      },
      group: ["month"],
      order: [[require("sequelize").literal("month"), "ASC"]],
      raw: true
    });
     
    // ✅ USER GROWTH (monthly)
    const userGrowths = await User.findAll({
      attributes: [
        [require("sequelize").fn("MONTH", require("sequelize").col("created_at")), "month"],
        [require("sequelize").fn("COUNT", require("sequelize").col("id")), "users"]
      ],
      group: ["month"],
      order: [[require("sequelize").literal("month"), "ASC"]],
      raw: true
    });

    const userGrowth = await User.findAll({
  attributes: [
    [fn("DATE_FORMAT", col("created_at"), "%b"), "month"],
    [fn("COUNT", col("id")), "users"]
  ],
  group: [fn("DATE_FORMAT", col("created_at"), "%b")],
  order: [[fn("MIN", col("created_at")), "ASC"]],
  raw: true
});

    // ✅ PIE DATA (User status)
    const activeUsers = await User.count({ where: { status: "active" } });
    const suspendedUsers = await User.count({ where: { status: "suspended" } });
    const bannedUsers = await User.count({ where: { status: "banned" } });

    return {
      kpis: {
        totalRevenue: totalRevenue || 0,
        totalUsers,
        activeOrders,
        conversion: Number(conversion)
      },

      charts: {
        revenueData: monthlyData,
        userGrowth,
        pieData: [
          { name: "Active", value: activeUsers },
          { name: "Suspended", value: suspendedUsers },
          { name: "Banned", value: bannedUsers }
        ]
      }
    };
  }
}

module.exports = new adminDashboardService();
