<?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);
}

// ===== EXCLUDE DEMO ACCOUNTS =====

// ===== EXCLUDE DEMO ACCOUNTS BY TRADE ID (Correct Approach) =====

// Fetch demo trade IDs
$demoTradeIds = [];
$demoQuery = "SELECT trade_id FROM liveaccounts WHERE LOWER(account_type) LIKE '%demo%'";
$demoResult = $conn->query($demoQuery);

if ($demoResult && $demoResult->num_rows > 0) {
    while ($row = $demoResult->fetch_assoc()) {
        if (!empty($row['trade_id'])) {
            $demoTradeIds[] = "'" . $conn->real_escape_string($row['trade_id']) . "'";
        }
    }
}

$excludeDemoSQL = "";
if (!empty($demoTradeIds)) {
    $excludeDemoSQL = "WHERE trade_id NOT IN (" . implode(",", $demoTradeIds) . ")";
}


// ===== Fetch totals from wallet tables (excluding demo accounts) =====

$totalsQuery = "
    SELECT 
        (SELECT COALESCE(SUM(CAST(deposit_amount AS DECIMAL(18,2))),0) 
         FROM wallet_deposit $excludeDemoSQL) AS total_deposits,

        (SELECT COALESCE(SUM(CAST(withdraw_amount AS DECIMAL(18,2))),0) 
         FROM wallet_withdraw $excludeDemoSQL) AS total_withdraws,

        (SELECT COALESCE(SUM(Balance),0) 
         FROM liveaccounts 
         WHERE LOWER(account_type) NOT LIKE '%demo%') 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 = getSafeSum($totals['total_equity'] ?? 0);

$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 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
]);

$conn->close();
?>
