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

// CORS headers
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, POST, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Authorization, X-Requested-With");

include_once __DIR__ . '/../config.php';

// Connect to database
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error) {
    die(json_encode([
        "success" => false,
        "error" => "Database connection failed: " . $conn->connect_error
    ]));
}

// Function to safely get sum
function getSafeSum($value) {
    return $value === null ? 0 : floatval($value);
}

// ===== Fetch totals from wallet tables =====
$totalsQuery = "
    SELECT 
        (SELECT COALESCE(SUM(CAST(deposit_amount AS DECIMAL(18,2))),0) FROM wallet_deposit) AS total_deposits,
        (SELECT COALESCE(SUM(CAST(withdraw_amount AS DECIMAL(18,2))),0) FROM wallet_withdraw) AS total_withdraws,
        (SELECT COALESCE(SUM(Balance),0) FROM liveaccounts) AS total_equity
";
$result = $conn->query($totalsQuery);
$totals = $result ? $result->fetch_assoc() : [];

$totalDeposits = getSafeSum($totals['total_deposits'] ?? 0);
$totalWithdraws = getSafeSum($totals['total_withdraws'] ?? 0);
$totalEquity = $totalDeposits - $totalWithdraws;

$totals = [
    "total_deposits" => $totalDeposits,
    "total_withdraws" => $totalWithdraws,
    "net_deposit" => $totalDeposits - $totalWithdraws,
    "total_equity" => $totalEquity,
    "total_credit" => $totalDeposits,
    "credit_used" => $totalDeposits - $totalWithdraws,
    "withdrawable_equity" => $totalEquity
];

// ===== USERS SECTION =====

// Total Users
$totalUsersQuery = "SELECT COUNT(*) AS total_users FROM loginusers";
$totalUsers = intval($conn->query($totalUsersQuery)->fetch_assoc()['total_users'] ?? 0);

// New Users (last 24 hours)
$newUsers24hQuery = "SELECT COUNT(*) AS new_users_24h FROM loginusers WHERE created_at >= NOW() - INTERVAL 1 DAY";
$newUsers24h = intval($conn->query($newUsers24hQuery)->fetch_assoc()['new_users_24h'] ?? 0);

// New Users (last 7 days)
$newUsers7dQuery = "SELECT COUNT(*) AS new_users_7d FROM loginusers WHERE created_at >= NOW() - INTERVAL 7 DAY";
$newUsers7d = intval($conn->query($newUsers7dQuery)->fetch_assoc()['new_users_7d'] ?? 0);

// ===== KYC SECTION =====

// // ✅ KYC Verified — count DISTINCT users with approved KYC
// $kycVerifiedQuery = "SELECT COUNT(DISTINCT user_id) AS kyc_verified FROM kycupload WHERE Status = 1";
// $kycVerified = intval($conn->query($kycVerifiedQuery)->fetch_assoc()['kyc_verified'] ?? 0);

// // ✅ KYC Unverified — users who have no verified KYC entry
// $kycUnverifiedQuery = "SELECT COUNT(DISTINCT id) AS kyc_unverified FROM loginusers WHERE id NOT IN (SELECT DISTINCT user_id FROM kycupload WHERE Status = 1)";
// $kycUnverified = intval($conn->query($kycUnverifiedQuery)->fetch_assoc()['kyc_unverified'] ?? 0);

// ✅ KYC Verified — count ALL approved KYC entries (including duplicates)
$kycVerifiedQuery = "SELECT COUNT(*) AS kyc_verified FROM kycupload WHERE Status = 1";
$kycVerified = intval($conn->query($kycVerifiedQuery)->fetch_assoc()['kyc_verified'] ?? 0);

// ✅ KYC Unverified — count ALL unverified KYC entries (including duplicates)
$kycUnverifiedQuery = "SELECT COUNT(*) AS kyc_unverified FROM kycupload WHERE Status = 0";
$kycUnverified = intval($conn->query($kycUnverifiedQuery)->fetch_assoc()['kyc_unverified'] ?? 0);


// ===== EMAIL SECTION =====

// Email Verified
$emailVerifiedQuery = "SELECT COUNT(*) AS email_verified FROM loginusers WHERE email_confirmed = 1";
$emailVerified = intval($conn->query($emailVerifiedQuery)->fetch_assoc()['email_verified'] ?? 0);

// Email Unverified
$emailUnverifiedQuery = "SELECT COUNT(*) AS email_unverified FROM loginusers WHERE email_confirmed = 0";
$emailUnverified = intval($conn->query($emailUnverifiedQuery)->fetch_assoc()['email_unverified'] ?? 0);

// ===== DEPOSIT SECTION =====

// Pending Deposits
$pendingDepositsQuery = "SELECT COUNT(*) AS pending_deposits FROM deposit_requests WHERE status = 'pending'";
$pendingDeposits = intval($conn->query($pendingDepositsQuery)->fetch_assoc()['pending_deposits'] ?? 0);

// Approved Deposits
$approvedDepositsQuery = "SELECT COUNT(*) AS approved_deposits FROM deposit_requests WHERE status = 'approved'";
$approvedDeposits = intval($conn->query($approvedDepositsQuery)->fetch_assoc()['approved_deposits'] ?? 0);

// Rejected Deposits
$rejectedDepositsQuery = "SELECT COUNT(*) AS rejected_deposits FROM deposit_requests WHERE status = 'rejected'";
$rejectedDeposits = intval($conn->query($rejectedDepositsQuery)->fetch_assoc()['rejected_deposits'] ?? 0);

// ===== WITHDRAWAL SECTION =====

// Pending Withdrawals
$pendingWithdrawQuery = "SELECT COUNT(*) AS pending_withdraw FROM withdrawal_requests WHERE status = 'pending'";
$pendingWithdraw = intval($conn->query($pendingWithdrawQuery)->fetch_assoc()['pending_withdraw'] ?? 0);

// Approved Withdrawals
$approvedWithdrawQuery = "SELECT COUNT(*) AS approved_withdraw FROM withdrawal_requests WHERE status = 'approved'";
$approvedWithdraw = intval($conn->query($approvedWithdrawQuery)->fetch_assoc()['approved_withdraw'] ?? 0);

// Rejected Withdrawals
$rejectedWithdrawQuery = "SELECT COUNT(*) AS rejected_withdraw FROM withdrawal_requests WHERE status = 'rejected'";
$rejectedWithdraw = intval($conn->query($rejectedWithdrawQuery)->fetch_assoc()['rejected_withdraw'] ?? 0);

// ===== Other Existing Stats =====

// Total clients
$clientsResult = $conn->query("SELECT COUNT(*) AS total_clients FROM loginusers");
$totalClients = $clientsResult ? intval($clientsResult->fetch_assoc()['total_clients']) : 0;

// Total trading accounts
$accountsResult = $conn->query("SELECT COUNT(*) AS total_accounts FROM liveaccounts");
$totalTradingAccounts = $accountsResult ? intval($accountsResult->fetch_assoc()['total_accounts']) : 0;

// Total live accounts (where status = 'active')
$liveAccountsResult = $conn->query("SELECT COUNT(*) AS total_liveaccounts FROM liveaccounts WHERE status = 'active'");
$totalLiveAccounts = $liveAccountsResult ? intval($liveAccountsResult->fetch_assoc()['total_liveaccounts']) : 0;


// Total IBs
$ibsResult = $conn->query("SELECT COUNT(*) AS total_ibs FROM ib1");
$totalIBs = $ibsResult ? intval($ibsResult->fetch_assoc()['total_ibs']) : 0;

// Total transactions (deposits + withdrawals)
$totalTransactions = $totalDeposits + $totalWithdraws;

// ===== Final JSON Output =====
echo json_encode([
    "success" => true,
    "totals" => $totals,
    "users" => [
        "total_users" => $totalUsers,
        "new_users_24h" => $newUsers24h,
        "new_users_7d" => $newUsers7d,
        "kyc_verified" => $kycVerified,
        "kyc_unverified" => $kycUnverified,
        "email_verified" => $emailVerified,
        "email_unverified" => $emailUnverified
    ],
    "deposits" => [
        "pending" => $pendingDeposits,
        "approved" => $approvedDeposits,
        "rejected" => $rejectedDeposits
    ],
    "withdrawals" => [
        "pending" => $pendingWithdraw,
        "approved" => $approvedWithdraw,
        "rejected" => $rejectedWithdraw
    ],
    "total_clients" => $totalClients,
    "total_trading_accounts" => $totalTradingAccounts,
    "total_transactions" => $totalTransactions,
    "total_ibs" => $totalIBs,
    "total_liveaccounts" => $totalLiveAccounts,

]);

$conn->close();
?>
