/**
 * DashboardService.js - Admin Dashboard Analytics & Statistics
 * Purpose: Generate comprehensive dashboard statistics and analytics
 * Features: User stats, revenue metrics, service analytics, top services
 */

class DashboardService {
  constructor(pool) {
    this.pool = pool;
  }

  /**
   * Get comprehensive dashboard statistics
   * @returns {Promise<Object>} Dashboard statistics including users, orders, revenue
   */
  async getDashboardStats() {
    try {
      const stats = {
        users: await this.getUserStats(),
        orders: await this.getOrderStats(),
        revenue: await this.getRevenueStats(),
        services: await this.getServiceStats(),
        topServices: await this.getTopServices(10),
        recentActivity: [],
        platformMetrics: {}
      };

      // Try to get additional metrics, but don't fail if tables don't exist
      try {
        stats.recentActivity = await this.getRecentActivity(10);
      } catch (e) {
        console.log('Recent activity unavailable:', e.message);
      }

      try {
        stats.platformMetrics = await this.getPlatformMetrics();
      } catch (e) {
        console.log('Platform metrics unavailable:', e.message);
      }

      return stats;
    } catch (error) {
      throw new Error(`Dashboard stats error: ${error.message}`);
    }
  }

  /**
   * Get user-related statistics
   */
  async getUserStats() {
    const [users] = await this.pool.execute(`
      SELECT
        COUNT(*) as total_users,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users,
        SUM(CASE WHEN status = 'banned' THEN 1 ELSE 0 END) as banned_users,
        SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) as suspended_users,
        SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admin_count,
        SUM(balance) as total_balance,
        AVG(balance) as avg_balance,
        COUNT(DISTINCT DATE(created_at)) as registration_days
      FROM users
    `);

    return users[0] || {};
  }

  /**
   * Get order-related statistics
   */
  async getOrderStats() {
    const [orders] = await this.pool.execute(`
      SELECT
        COUNT(*) as total_orders,
        SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) as completed_orders,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_orders,
        SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) as processing_orders,
        AVG(CAST(quantity AS DECIMAL(10,2))) as avg_quantity,
        COUNT(DISTINCT user_id) as unique_customers
      FROM orders
    `);

    return orders[0] || {};
  }

  /**
   * Get revenue-related statistics
   */
  async getRevenueStats() {
    const [revenue] = await this.pool.execute(`
      SELECT
        COUNT(*) as total_transactions,
        SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END) as total_deposits,
        SUM(CASE WHEN type = 'debit' THEN amount ELSE 0 END) as total_withdrawals,
        COUNT(DISTINCT user_id) as unique_depositors,
        MAX(amount) as largest_transaction,
        AVG(amount) as avg_transaction_size
      FROM transactions
    `);

    return revenue[0] || {};
  }

  /**
   * Get service catalog statistics
   */
  async getServiceStats() {
    const [services] = await this.pool.execute(`
      SELECT
        COUNT(*) as total_services,
        COUNT(DISTINCT category_id) as total_categories,
        COUNT(DISTINCT api_provider_id) as total_providers,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_services,
        SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive_services,
        MIN(price) as min_rate,
        MAX(price) as max_rate,
        AVG(price) as avg_rate
      FROM services
    `);

    return services[0] || {};
  }

  /**
   * Get top performing services by orders
   */
  async getTopServices(limit = 10) {
    const [services] = await this.pool.execute(`
      SELECT
        s.id,
        s.name,
        s.price as rate,
        c.name as category,
        COUNT(o.id) as order_count,
        SUM(o.quantity) as total_quantity,
        SUM(o.quantity * s.price) as revenue
      FROM services s
      LEFT JOIN orders o ON s.id = o.service_id
      LEFT JOIN categories c ON s.category_id = c.id
      WHERE s.name NOT LIKE '%view%'
        AND s.name NOT LIKE '%views%'
        AND s.name NOT LIKE '%View%'
      GROUP BY s.id, s.name, s.price, c.name
      ORDER BY order_count DESC
      LIMIT ?
    `, [limit]);

    return services;
  }

  /**
   * Get recent platform activity
   */
  async getRecentActivity(limit = 10) {
    const [activity] = await this.pool.execute(`
      SELECT 'order' as type, o.id, o.user_id, o.created_at, CONCAT('Order #', o.id) as description
      FROM orders o
      UNION ALL
      SELECT 'deposit', d.id, d.user_id, d.created_at, CONCAT('Deposit: ', FORMAT(d.amount, 2))
      FROM deposits d
      UNION ALL
      SELECT 'user_registered', u.id, u.id, u.created_at, CONCAT('New user: ', u.email)
      FROM users u
      ORDER BY created_at DESC
      LIMIT ?
    `, [limit]);

    return activity;
  }

  /**
   * Get platform-wide metrics
   */
  async getPlatformMetrics() {
    const [metrics] = await this.pool.execute(`
      SELECT
        (SELECT COUNT(*) FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) as orders_this_week,
        (SELECT SUM(amount) FROM deposits WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) as deposits_this_week,
        (SELECT COUNT(*) FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) as new_users_this_week,
        (SELECT COUNT(*) FROM login_activities WHERE login_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)) as logins_today
      FROM DUAL
    `);

    return metrics[0] || {};
  }

  /**
   * Get daily revenue for last 30 days
   */
  async getDailyRevenueChart() {
    const [data] = await this.pool.execute(`
      SELECT
        DATE(d.created_at) as date,
        SUM(d.amount) as deposits,
        COUNT(DISTINCT o.id) as orders
      FROM deposits d
      LEFT JOIN orders o ON DATE(o.created_at) = DATE(d.created_at)
      WHERE d.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      GROUP BY DATE(d.created_at)
      ORDER BY date DESC
    `);

    return data;
  }

  /**
   * Get user growth chart (daily signups for last 30 days)
   */
  async getUserGrowthChart() {
    const [data] = await this.pool.execute(`
      SELECT
        DATE(created_at) as date,
        COUNT(*) as new_users,
        (SELECT COUNT(*) FROM users WHERE created_at <= DATE_ADD(DATE(u.created_at), INTERVAL 1 DAY)) as cumulative_users
      FROM users u
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `);

    return data;
  }

  /**
   * Get category distribution
   */
  async getCategoryDistribution() {
    const [data] = await this.pool.execute(`
      SELECT
        c.id,
        c.name,
        COUNT(s.id) as service_count,
        COUNT(DISTINCT o.id) as order_count,
        SUM(o.quantity * s.price) as revenue
      FROM categories c
      LEFT JOIN services s ON c.id = s.category_id
      LEFT JOIN orders o ON s.id = o.service_id
      GROUP BY c.id, c.name
      ORDER BY service_count DESC
    `);

    return data;
  }

  /**
   * Get payment gateway performance
   */
  async getPaymentGatewayStats() {
    const [data] = await this.pool.execute(`
      SELECT
        payment_gateway,
        COUNT(*) as transaction_count,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount,
        SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as successful_transactions
      FROM deposits
      GROUP BY payment_gateway
      ORDER BY total_amount DESC
    `);

    return data;
  }
}

module.exports = DashboardService;
