<?php
session_start();
include('../includes/config.php');

header('Content-Type: application/json');


$response = array('success' => false);

// Total Users
$sql = "SELECT COUNT(*) as total FROM users";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$total_users = $row['total'];

// New Users Today
$today = date('Y-m-d');
$sql = "SELECT COUNT(*) as total FROM users WHERE DATE(registration_date) = '$today'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$new_users_today = $row['total'];

// New Users This Week
$week_start = date('Y-m-d', strtotime('monday this week'));
$week_end = date('Y-m-d', strtotime('sunday this week'));
$sql = "SELECT COUNT(*) as total FROM users WHERE DATE(registration_date) BETWEEN '$week_start' AND '$week_end'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$new_users_week = $row['total'];

// Active Subscriptions
$sql = "SELECT COUNT(*) as total FROM user_bot_subscriptions WHERE status = 'active'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$active_subscriptions = $row['total'];

// Active Bots (running)
$sql = "SELECT COUNT(*) as total FROM user_bot_subscriptions WHERE status = 'active'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$active_bots = $row['total'];

// Expired Subscriptions
$sql = "SELECT COUNT(*) as total FROM user_bot_subscriptions WHERE status = 'expired'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$expired_subscriptions = $row['total'];

// Running Bots (assume all active are running)
$running_bots = $active_bots;
$idle_bots = 0; // You can implement logic for idle bots

// Pending Deposits
$sql = "SELECT COUNT(*) as total, COALESCE(SUM(amount), 0) as amount FROM deposits WHERE status = 'pending'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$pending_deposits = $row['total'];
$pending_deposits_amount = number_format($row['amount'], 2);

// Pending Deposits Today
$sql = "SELECT COUNT(*) as total FROM deposits WHERE status = 'pending' AND DATE(created_at) = '$today'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$pending_deposits_today = $row['total'];

// Pending Withdrawals
$sql = "SELECT COUNT(*) as total, COALESCE(SUM(amount), 0) as amount FROM withdrawals WHERE status = 'pending'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$pending_withdrawals = $row['total'];
$pending_withdrawals_amount = number_format($row['amount'], 2);

// Pending Withdrawals Today
$sql = "SELECT COUNT(*) as total FROM withdrawals WHERE status = 'pending' AND DATE(created_at) = '$today'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$pending_withdrawals_today = $row['total'];

// Total Profit (from transactions table)
$sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions WHERE type = 'profit' AND status = 'completed'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$total_profit = number_format($row['total'], 2);

// Profit This Month
$month_start = date('Y-m-01');
$month_end = date('Y-m-t');
$sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions WHERE type = 'profit' AND status = 'completed' AND DATE(date) BETWEEN '$month_start' AND '$month_end'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$profit_this_month = number_format($row['total'], 2);

// Today's Profit
$sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions WHERE type = 'profit' AND status = 'completed' AND DATE(date) = '$today'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$todays_profit = number_format($row['total'], 2);

// Yesterday's Profit
$yesterday = date('Y-m-d', strtotime('-1 day'));
$sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions WHERE type = 'profit' AND status = 'completed' AND DATE(date) = '$yesterday'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$yesterday_profit = number_format($row['total'], 2);

// Profit Change
$profit_change = 0;
if ($yesterday_profit > 0) {
    $profit_change = round((($todays_profit - $yesterday_profit) / $yesterday_profit) * 100, 2);
}

// Open Tickets
$sql = "SELECT COUNT(*) as total FROM support_tickets WHERE status IN ('open', 'in_progress')";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$open_tickets = $row['total'];

// New Tickets Today
$sql = "SELECT COUNT(*) as total FROM support_tickets WHERE status IN ('open') AND DATE(created_at) = '$today'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$new_tickets = $row['total'];

// In Progress Tickets
$sql = "SELECT COUNT(*) as total FROM support_tickets WHERE status = 'in_progress'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$in_progress_tickets = $row['total'];

$response = array(
    'success' => true,
    'total_users' => $total_users,
    'new_users_today' => $new_users_today,
    'new_users_week' => $new_users_week,
    'active_subscriptions' => $active_subscriptions,
    'active_bots' => $active_bots,
    'expired_subscriptions' => $expired_subscriptions,
    'running_bots' => $running_bots,
    'idle_bots' => $idle_bots,
    'pending_deposits' => $pending_deposits,
    'pending_deposits_today' => $pending_deposits_today,
    'pending_deposits_amount' => $pending_deposits_amount,
    'pending_withdrawals' => $pending_withdrawals,
    'pending_withdrawals_today' => $pending_withdrawals_today,
    'pending_withdrawals_amount' => $pending_withdrawals_amount,
    'total_profit' => $total_profit,
    'profit_this_month' => $profit_this_month,
    'todays_profit' => $todays_profit,
    'yesterday_profit' => $yesterday_profit,
    'profit_change' => $profit_change,
    'open_tickets' => $open_tickets,
    'new_tickets' => $new_tickets,
    'in_progress_tickets' => $in_progress_tickets
);

echo json_encode($response);
?>