<?php
/**
 * Fixed Dashboard Page
 * Replace your existing dashboard PHP page with this file.
 * It keeps your old features but prevents common blank-page/runtime errors.
 */

require_once("../layouts/members/head.php");

/* -----------------------------
   Safe helpers
------------------------------ */
if (!function_exists('e')) {
    function e($value) {
        return htmlspecialchars((string)($value ?? ''), ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('arr_get')) {
    function arr_get($array, $key, $default = '') {
        return (is_array($array) && array_key_exists($key, $array)) ? $array[$key] : $default;
    }
}

if (!function_exists('money')) {
    function money($value) {
        return number_format((float)($value ?? 0), 2);
    }
}

if (!function_exists('safe_sql')) {
    function safe_sql($value) {
        return addslashes((string)($value ?? ''));
    }
}

if (!function_exists('is_role')) {
    function is_role($roles) {
        global $user_data;
        return isset($user_data['usertype']) && in_array($user_data['usertype'], (array)$roles, true);
    }
}

if (!function_exists('is_service_active')) {
    function is_service_active($key) {
        global $user_data;
        return isset($user_data[$key]) && strtoupper((string)$user_data[$key]) === 'YES';
    }
}

if (!function_exists('safe_db_count')) {
    function safe_db_count($table, $condition) {
        try {
            return (int)db_count($table, $condition);
        } catch (Throwable $e) {
            error_log('db_count error: ' . $e->getMessage());
            return 0;
        }
    }
}

if (!function_exists('safe_db_fetch_array')) {
    function safe_db_fetch_array($table, $condition, $select = '*') {
        try {
            $row = db_fetch_array($table, $condition, $select);
            return is_array($row) ? $row : [];
        } catch (Throwable $e) {
            error_log('db_fetch_array error: ' . $e->getMessage());
            return [];
        }
    }
}

if (!function_exists('safe_db_fetch_all')) {
    function safe_db_fetch_all($table, $condition) {
        try {
            $rows = db_fetch_all($table, $condition);
            return is_array($rows) ? $rows : [];
        } catch (Throwable $e) {
            error_log('db_fetch_all error: ' . $e->getMessage());
            return [];
        }
    }
}

if (!function_exists('fetch_update_info')) {
    function fetch_update_info($url) {
        if (empty($url) || !function_exists('curl_init')) {
            return null;
        }

        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_USERAGENT      => 'Mozilla/5.0',
            CURLOPT_SSL_VERIFYPEER => false,
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT        => 10,
        ]);

        $result = curl_exec($ch);
        curl_close($ch);

        if (!$result) {
            return null;
        }

        $data = json_decode($result, true);
        return is_array($data) ? $data : null;
    }
}

$fromDate = date('Y-m-01');
$toDate   = date('Y-m-d');

$userId    = arr_get($user_data ?? [], 'user_id', '');
$userName  = arr_get($user_data ?? [], 'username', '');
$fullName  = arr_get($user_data ?? [], 'name', 'User');
$userType  = arr_get($user_data ?? [], 'usertype', '');

$memberRoles = ['White Label', 'Master Distributor', 'Distributor', 'Retailer'];
$networkRoles = ['Master Distributor', 'Distributor', 'Retailer'];
$isAdmin = ((string)$userId === '1' && $userType === 'Admin');
$isMemberRole = in_array($userType, $memberRoles, true);
?>

<style>
.dashboard-card { cursor:pointer; transition:transform .15s ease, box-shadow .15s ease; }
.dashboard-card:hover { transform:translateY(-2px); box-shadow:0 8px 18px rgba(0,0,0,.15); }
.card-icon img { max-width:56px; height:56px; object-fit:contain; }
.hr { margin:.65rem 0; }
.scroll { max-height:380px; overflow:auto; }
.whatsapp-join-box { position:fixed; bottom:25px; right:25px; background:linear-gradient(135deg,#25d366,#20c55c); color:#fff; border-radius:14px; padding:12px 16px; width:240px; box-shadow:0 6px 18px rgba(0,0,0,.25); display:flex; align-items:flex-start; justify-content:space-between; z-index:9999; font-family:"Poppins",sans-serif; animation:slideUp .6s ease forwards; }
.whatsapp-join-left { display:flex; gap:10px; flex:1; }
.whatsapp-join-left img { width:24px; height:24px; }
.whatsapp-join-content { flex:1; }
.whatsapp-join-content h4 { font-size:14px; font-weight:600; margin:0; }
.whatsapp-join-content p { font-size:11.5px; margin:2px 0 8px; opacity:.9; }
.whatsapp-join-actions a { background:#fff; color:#25d366; border-radius:20px; padding:3px 12px; font-size:12px; font-weight:600; text-decoration:none; display:inline-flex; align-items:center; transition:all .3s ease; }
.whatsapp-join-actions a:hover { background:#e7f7ee; transform:scale(1.05); }
.whatsapp-join-close { cursor:pointer; font-size:16px; margin-left:8px; opacity:.8; }
.whatsapp-join-close:hover { opacity:1; }
@keyframes slideUp { from { opacity:0; transform:translateY(40px) scale(.95); } to { opacity:1; transform:translateY(0) scale(1); } }
@keyframes fadeOut { from { opacity:1; transform:translateY(0) scale(1); } to { opacity:0; transform:translateY(40px) scale(.9); visibility:hidden; } }
.popup-overlay { position:fixed; inset:0; width:100vw; height:100vh; background:rgba(0,0,0,.8); display:flex; align-items:center; justify-content:center; z-index:10000; }
.popup-box { background:#050000; color:#fff; padding:24px 32px; border-radius:12px; position:relative; width:400px; max-width:90%; box-shadow:0 8px 20px rgba(255,255,255,.1); animation:popupFadeIn .3s ease; text-align:center; font-family:Arial,sans-serif; }
.popup-close { position:absolute; top:12px; right:12px; background:none; border:0; font-size:20px; cursor:pointer; color:#fff; }
.popup-close:hover { color:#ccc; }
@keyframes popupFadeIn { from { opacity:0; transform:scale(.95); } to { opacity:1; transform:scale(1); } }
.update-modal { display:none; position:fixed; top:33%; left:50%; transform:translate(-50%,-50%); background:#000; color:#fff; padding:20px; border:2px solid #007bff; box-shadow:0 0 10px #000; z-index:10001; width:400px; max-width:92%; }
.banner-container { display:flex; justify-content:space-between; align-items:center; background:#4CAF50; padding:10px; color:#fff; font-weight:bold; gap:12px; flex-wrap:wrap; }
</style>

<?php if (arr_get($main_site_data ?? [], 'popup_active') === 'ON' && $isMemberRole) { ?>
<div class="popup-overlay" id="popupOverlay">
    <div class="popup-box">
        <button type="button" class="popup-close" onclick="closePopup()">&times;</button>
        <h3>📌 Alert!</h3>
        <h6><?= e(arr_get($main_site_data ?? [], 'popupalert')) ?></h6>
    </div>
</div>
<script>
function closePopup() {
    var el = document.getElementById('popupOverlay');
    if (el) el.style.display = 'none';
}
</script>
<?php } ?>

<?php if ($isMemberRole) { ?>
<div class="whatsapp-join-box" id="whatsappJoinBox">
    <div class="whatsapp-join-left">
        <img src="https://img.icons8.com/ios-filled/50/ffffff/whatsapp.png" alt="WhatsApp">
        <div class="whatsapp-join-content">
            <h4>📢 Join Our Channel</h4>
            <p>Instant updates & offers</p>
            <div class="whatsapp-join-actions">
                <a href="https://wa.me/91<?= e(arr_get($main_site_data ?? [], 'mobile', '0000000000')) ?>" target="_blank" rel="noopener">
                    <img src="https://img.icons8.com/ios-filled/20/25d366/whatsapp.png" style="margin-right:5px;" alt=""> Join
                </a>
            </div>
        </div>
    </div>
    <div class="whatsapp-join-close" onclick="closeWhatsAppBox()">&times;</div>
</div>
<script>
function closeWhatsAppBox() {
    var box = document.getElementById('whatsappJoinBox');
    if (box) box.style.animation = 'fadeOut .4s forwards';
}
</script>
<?php } ?>

<div class="row">
    <div class="col-md-12 mb-2">
        <?php if ($isAdmin) { ?>
            <?php
            $gatewayUrl = function_exists('paymentapi') ? paymentapi('gateway_Url') : '';
            $updateApiUrl = $gatewayUrl ? ('https://' . $gatewayUrl . '/update/update_info.json') : '';
            $updateInfo = fetch_update_info($updateApiUrl);

            $versionFile = '../update/current_version.txt';
            $currentVersion = file_exists($versionFile) ? trim((string)file_get_contents($versionFile)) : '1.0.0';
            $newVersion = arr_get($updateInfo, 'version', '');
            $updateAvailable = ($newVersion !== '' && $newVersion !== $currentVersion);
            $buttonText = '💰 Check API Balance';

            if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['check_balance']) && function_exists('Api_balance_info_api_run')) {
                $responseApi = Api_balance_info_api_run();
                $response = json_decode((string)$responseApi, true);
                if (is_array($response) && arr_get($response, 'status') === 'success') {
                    $buttonText = '💵 Balance: ' . arr_get($response, 'balance', '0.00');
                } else {
                    $buttonText = '❌ Error Fetching Balance';
                }
            }
            ?>

            <?php if ($updateAvailable) { ?>
                <div id="updateBanner" style="background:#ffcc00; padding:10px; text-align:center; font-weight:bold;">
                    🚀 Update Available! (Version <?= e($newVersion) ?>) - <a href="#" onclick="showUpdatePopup(); return false;">Click here to update</a>
                </div>
            <?php } else { ?>
            <div class="banner-container">
    <span>✅ Your system is up to date! (Version <?php echo $currentVersion; ?>)</span>
    <div class="button-container">
        <form method="post">
            <button type="submit" name="check_balance" class="button"><?php echo $buttonText; ?></button>
        </form>
    </div>
</div>
<style>.button {
    background: linear-gradient(135deg, #ffffff, #f2f4f8);
    color: #222;
    border: 1px solid #e0e0e0;
    padding: 10px 22px;
    border-radius: 8px;
    font-size: 15px;
    font-weight: 600;
    cursor: pointer;
    box-shadow: 0 4px 12px rgba(0, 0, 0, 0.08);
    transition: 0.3s ease;
}

.button:hover {
    background: linear-gradient(135deg, #ffffff, #e9eef5);
    box-shadow: 0 6px 18px rgba(0, 0, 0, 0.12);
    transform: translateY(-1px);
}

.button:active {
    transform: scale(0.98);
}</style>
        <?php } ?>
        <?php if ($updateAvailable) { ?>
            <div id="updateModal" class="update-modal">
                <h2>Update Available</h2>
                <p><strong>Current Version:</strong> <?= e($currentVersion) ?></p>
                <p><strong>New Version:</strong> <?= e($newVersion) ?></p>
                <p><strong>Changelog:</strong></p>
                <p><?= nl2br(e(arr_get($updateInfo, 'changelog', 'No changelog available.'))) ?></p>
                <button type="button" class="btn btn-primary btn-sm" onclick="applyUpdate()">Update Now</button>
                <button type="button" class="btn btn-secondary btn-sm" onclick="hideUpdatePopup()">Cancel</button>
            </div>
            <script>
            function showUpdatePopup() {
                var modal = document.getElementById('updateModal');
                if (modal) modal.style.display = 'block';
            }
            function hideUpdatePopup() {
                var modal = document.getElementById('updateModal');
                if (modal) modal.style.display = 'none';
            }
            function applyUpdate() {
                if (!confirm('Are you sure you want to apply the update?')) return;
                fetch('../update/update.php', {
                    method: 'POST',
                    body: JSON.stringify({
                        update_url: <?= json_encode(arr_get($updateInfo, 'update_url', '')) ?>,
                        version: <?= json_encode($newVersion) ?>,
                        extractPath: <?= json_encode(arr_get($updateInfo, 'extractPath', '')) ?>
                    }),
                    headers: {'Content-Type': 'application/json'}
                })
                .then(function(response) { return response.json(); })
                .then(function(data) {
                    alert(data.message || 'Update response received.');
                    if (data.success) location.reload();
                })
                .catch(function(error) {
                    alert('❌ Update Failed: ' + error);
                    console.error(error);
                });
            }
            </script>
            <?php } ?>
        <?php } ?>

        <span class="font-weight-bold">Hi, <?= e($fullName) ?></span>
    </div>

<?php if ($isMemberRole) { ?>
    <?php if (in_array($userType, ['Master Distributor', 'Distributor', 'Retailer'], true)) { ?>
    <div class="col-md-12 mb-2">
        <div class="card p-1 text-danger">
            <div id="carouselExampleControls" class="carousel slide" data-ride="carousel">
                <div class="carousel-inner">
                    <?php foreach (['banner.png', 'banner2.png', 'banner3.png'] as $i => $banner) { ?>
                    <div class="carousel-item <?= $i === 0 ? 'active' : '' ?>">
                        <img class="d-block w-100" src="../layouts/members/assets/images/<?= e($banner) ?>" style="height:120px; object-fit:cover;" alt="Banner">
                    </div>
                    <?php } ?>
                </div>
                <a class="carousel-control-prev" href="#carouselExampleControls" role="button" data-slide="prev">
                    <span class="carousel-control-prev-icon" aria-hidden="true"></span><span class="sr-only">Previous</span>
                </a>
                <a class="carousel-control-next" href="#carouselExampleControls" role="button" data-slide="next">
                    <span class="carousel-control-next-icon" aria-hidden="true"></span><span class="sr-only">Next</span>
                </a>
            </div>
        </div>
    </div>
    <?php } ?>

    <?php
    $serviceCards = [
        ['key' => 'mobile_dth_recharge_services', 'url' => 'Recharge',              'img' => 'recharge2.png',       'title' => 'Recharge Service'],
        ['key' => 'aadhar_service',              'url' => 'Uid_Flx-Services',      'img' => '3dcard1.png',         'title' => 'Aadhaar Services'],
        ['key' => 'pan_service',                 'url' => 'panService',            'img' => 'subservice57.png',    'title' => 'Pan Card Services'],
        ['key' => 'vehical_service',             'url' => 'VehicalService',        'img' => 'Yamaha-Bike-PNG.png', 'title' => 'Vehical Services'],
        ['key' => 'rationpdf_service',           'url' => 'RationService',         'img' => 'ration1.png',         'title' => 'Ration Services'],
        ['key' => 'voter_service',               'url' => 'VoterService',          'img' => 'voter.png',           'title' => 'Voter Services'],
        ['key' => 'farmer_services',             'url' => 'farmerService',         'img' => 'farmerLogo.png',      'title' => 'Farmer Services'],
        ['key' => 'electricBill_services',       'url' => 'electricBill_Service',  'img' => 'electricBill.png',    'title' => 'Electric Bill Services'],
        ['key' => 'rtps_service',                'url' => 'Rtps_Services',         'img' => 'rtps.png',            'title' => 'RTPS Services'],
        ['key' => 'janaadhar_service',           'url' => 'janAadhar_Services',    'img' => 'janaadhar.png',       'title' => 'JanAadhar Services'],
        ['key' => 'familyid_service',            'url' => 'familyID_Service',      'img' => 'familyidLogo.png',    'title' => 'FamilyID Services'],
    ];

    foreach ($serviceCards as $card) {
        if (!is_service_active($card['key'])) continue;
    ?>
    <div class="col-lg-2 col-md-6 col-6 mb-2 hand">
        <div class="card card-figure is-hoverable p-0 pmd-card text-center hand dashboard-card" onclick="location.href='<?= e($card['url']) ?>'">
            <div class="card-body">
                <div class="card-icon"><img src="../layouts/members/assets/images/<?= e($card['img']) ?>" alt=""></div>
                <hr class="hr"><p class="card-text"><?= e($card['title']) ?></p>
            </div>
        </div>
    </div>
    <?php } ?>

    <div class="col-lg-2 col-md-6 col-6 mb-2 hand">
        <div class="card card-figure is-hoverable p-0 pmd-card text-center hand dashboard-card" onclick="location.href='Aditional_Services'">
            <div class="card-body">
                <div class="card-icon"><img src="../layouts/members/assets/images/pdf.png" alt=""></div>
                <hr class="hr"><p class="card-text">Other Services</p>
            </div>
        </div>
    </div>

    <div class="col-12"><hr></div>

    <?php
    if (in_array($userType, ['Master Distributor', 'Distributor'], true)) {
        $parent = safe_sql($userName);
        $totaldismasRetailer = safe_db_count('members', "parent_uid='{$parent}'");
        $totalDistributorr = safe_db_count('members', "parent_uid='{$parent}' AND usertype='Distributor'");
        $totalRetailer = safe_db_count('members', "parent_uid='{$parent}' AND usertype='Retailer'");
        $totalActivateRetailer = safe_db_count('members', "parent_uid='{$parent}' AND status='Active'");
        $totalBlockRetailer = safe_db_count('members', "parent_uid='{$parent}' AND status!='Active'");
    ?>
        <?php if ($userType === 'Distributor') { ?>
            <?php
            $networkStats = [
                ['title' => 'Total Retailer', 'value' => $totalRetailer, 'class' => 'bg-danger'],
                ['title' => 'Active Users', 'value' => $totalActivateRetailer, 'class' => 'bg-purple'],
                ['title' => 'Inactive Users', 'value' => $totalBlockRetailer, 'class' => 'bg-red'],
            ];
            foreach ($networkStats as $stat) { ?>
            <div class="col-lg-4 col-md-6 col-6 mb-2 hand">
                <div class="card pmd-card card-figure is-hoverable p-0 text-center <?= e($stat['class']) ?> text-white hand dashboard-card">
                    <div class="card-body"><div class="card-icon"><h4><?= e($stat['value']) ?></h4></div><hr class="hr bg-white"><p class="card-text"><?= e($stat['title']) ?></p></div>
                </div>
            </div>
            <?php } ?>
        <?php } ?>

        <?php if ($userType === 'Master Distributor') { ?>
            <?php
            $masterStats = [
                ['title' => 'Total Users', 'value' => $totaldismasRetailer, 'class' => 'bg-danger'],
                ['title' => 'Total Distributor', 'value' => $totalDistributorr, 'class' => 'bg-purple'],
                ['title' => 'Total Retailer', 'value' => $totalRetailer, 'class' => 'bg-danger'],
                ['title' => 'Inactive Users', 'value' => $totalBlockRetailer, 'class' => 'bg-red'],
            ];
            foreach ($masterStats as $stat) { ?>
            <div class="col-lg-3 col-md-6 col-6 mb-2 hand">
                <div class="card pmd-card card-figure is-hoverable p-0 text-center <?= e($stat['class']) ?> text-white hand dashboard-card">
                    <div class="card-body"><div class="card-icon"><h4><?= e($stat['value']) ?></h4></div><hr class="hr bg-white"><p class="card-text"><?= e($stat['title']) ?></p></div>
                </div>
            </div>
            <?php } ?>
        <?php } ?>
    <?php } ?>

    <?php if (in_array($userType, ['Master Distributor', 'Distributor', 'Retailer'], true)) { ?>
    <div class="col-lg-12 col-md-12 col-12 mb-4 hand mt-2">
        <div class="card pmd-card hand">
            <div class="card-header bg-primary text-white py-2">
                <div class="d-flex align-items-center">
                    <span class="mr-auto">Last Account Activity</span>
                    <a class="btn btn-secondary btn-sm" href="walletledger">View All</a>
                </div>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive scroll">
                    <table class="table w-100">
                        <thead><tr class="bg-secondary"><th>Service</th><th>Type</th><th>Amount</th><th>Description</th><th>Date Time</th><th>Status</th><th>UserID</th></tr></thead>
                        <tbody>
                        <?php
                        $uid = safe_sql($userId);
                        $rows = safe_db_fetch_all('reports', "user_id='{$uid}' ORDER BY id DESC LIMIT 2");
                        if (!$rows) { ?>
                            <tr><td colspan="7" class="text-center">No recent activity found.</td></tr>
                        <?php }
                        foreach ($rows as $value) {
                            $dateTime = !empty($value['date_time']) ? date('d-M-Y h:i:s A', strtotime($value['date_time'])) : '';
                            $typeValue = arr_get($value, 'type');
                            $statusValue = arr_get($value, 'status');
                            $typeBadge = $typeValue === 'Credit' ? '<span class="badge badge-subtle badge-success">Credit</span>' : '<span class="badge badge-subtle badge-danger">Debit</span>';
                            if ($statusValue === 'Success') {
                                $statusBadge = '<span class="badge badge-success"><span class="oi oi-media-record pulse mr-1"></span>Success</span>';
                            } elseif (in_array($statusValue, ['Pending', 'Process'], true)) {
                                $statusBadge = '<span class="badge badge-warning"><span class="oi oi-media-record pulse mr-1"></span>' . e($statusValue) . '</span>';
                            } elseif (in_array($statusValue, ['Failed', 'Refund'], true)) {
                                $statusBadge = '<span class="badge badge-danger"><span class="oi oi-media-record pulse mr-1"></span>' . e($statusValue) . '</span>';
                            } else {
                                $statusBadge = '<span class="badge badge-info"><span class="oi oi-media-record pulse mr-1"></span>' . e($statusValue) . '</span>';
                            }
                        ?>
                            <tr>
                                <td><?= e(arr_get($value, 'service')) ?></td>
                                <td><?= $typeBadge ?></td>
                                <td><i class="fas fa-inr fa-sm fa-fw"></i><?= e(arr_get($value, 'main_amount', 0)) ?></td>
                                <td><?= e(arr_get($value, 'description')) ?></td>
                                <td><?= e($dateTime) ?></td>
                                <td><?= $statusBadge ?></td>
                                <td><?= e(arr_get($value, 'username')) ?></td>
                            </tr>
                        <?php } ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
    <?php } ?>
<?php } ?>
<?php if ($isAdmin) { ?>
    <?php
    /*
     * Use one database connection and three queries for the complete admin
     * dashboard. The previous version opened a new database connection for
     * every card and every service.
     */
    $todayStart = $toDate . ' 00:00:00';
    $todayEnd   = $toDate . ' 23:59:59';
    $rangeStart = $fromDate . ' 00:00:00';
    $rangeEnd   = $toDate . ' 23:59:59';

    $db = db();

    /*
     * Query 1: all member counts and balances.
     */
    $memberStats = $db->query("
        SELECT
            COALESCE(SUM(CASE WHEN status != 'Active' THEN 1 ELSE 0 END), 0) AS inactive_users,
            COALESCE(SUM(CASE WHEN usertype = 'Admin' THEN 1 ELSE 0 END), 0) AS total_admin,

            COALESCE(SUM(CASE WHEN usertype != 'Admin' THEN 1 ELSE 0 END), 0) AS total_member,
            COALESCE(SUM(CASE WHEN usertype != 'Admin' THEN balance ELSE 0 END), 0) AS total_member_balance,

            COALESCE(SUM(CASE WHEN usertype = 'Retailer' THEN 1 ELSE 0 END), 0) AS total_retailer,
            COALESCE(SUM(CASE WHEN usertype = 'Retailer' THEN balance ELSE 0 END), 0) AS total_retailer_balance,

            COALESCE(SUM(CASE WHEN usertype = 'Distributor' THEN 1 ELSE 0 END), 0) AS total_distributor,
            COALESCE(SUM(CASE WHEN usertype = 'Distributor' THEN balance ELSE 0 END), 0) AS total_distributor_balance,

            COALESCE(SUM(CASE WHEN usertype = 'Master Distributor' THEN 1 ELSE 0 END), 0) AS total_master,
            COALESCE(SUM(CASE WHEN usertype = 'Master Distributor' THEN balance ELSE 0 END), 0) AS total_master_balance
        FROM members
    ")->fetchArray();

    /*
     * Query 2: open complaint count.
     */
    $opencomplaints = $db->query("
        SELECT COUNT(complaint_id) AS total_reports
        FROM complaints
        WHERE status = 'Open'
    ")->fetchArray();

    /*
     * Query 3: monthly, today, recharge and service statistics.
     * The date values are bound parameters instead of being placed directly
     * inside the SQL query.
     */
    $reportRows = $db->query("
        SELECT
            reports.service,

            COALESCE(SUM(
                CASE
                    WHEN reports.status = 'Success'
                         AND reports.type = 'Debit'
                    THEN 1 ELSE 0
                END
            ), 0) AS monthly_debit_reports,

            COALESCE(SUM(
                CASE
                    WHEN reports.status = 'Success'
                         AND reports.type = 'Debit'
                    THEN reports.amount ELSE 0
                END
            ), 0) AS monthly_debit_amount,

            COALESCE(SUM(
                CASE
                    WHEN reports.date_time >= date_limits.today_start
                         AND reports.date_time <= date_limits.today_end
                         AND reports.status = 'Success'
                         AND reports.type = 'Debit'
                    THEN 1 ELSE 0
                END
            ), 0) AS today_debit_reports,

            COALESCE(SUM(
                CASE
                    WHEN reports.date_time >= date_limits.today_start
                         AND reports.date_time <= date_limits.today_end
                         AND reports.status = 'Success'
                         AND reports.type = 'Debit'
                    THEN reports.amount ELSE 0
                END
            ), 0) AS today_debit_amount,

            COALESCE(SUM(
                CASE
                    WHEN reports.status = 'Success'
                         AND reports.service = 'WALLET'
                         AND reports.type = 'Credit'
                    THEN 1 ELSE 0
                END
            ), 0) AS monthly_recharge_reports,

            COALESCE(SUM(
                CASE
                    WHEN reports.status = 'Success'
                         AND reports.service = 'WALLET'
                         AND reports.type = 'Credit'
                    THEN reports.amount ELSE 0
                END
            ), 0) AS monthly_recharge_amount,

            COALESCE(SUM(
                CASE
                    WHEN reports.date_time >= date_limits.today_start
                         AND reports.date_time <= date_limits.today_end
                         AND reports.status = 'Success'
                         AND reports.service = 'WALLET'
                         AND reports.type = 'Credit'
                    THEN 1 ELSE 0
                END
            ), 0) AS today_recharge_reports,

            COALESCE(SUM(
                CASE
                    WHEN reports.date_time >= date_limits.today_start
                         AND reports.date_time <= date_limits.today_end
                         AND reports.status = 'Success'
                         AND reports.service = 'WALLET'
                         AND reports.type = 'Credit'
                    THEN reports.amount ELSE 0
                END
            ), 0) AS today_recharge_amount,

            COALESCE(SUM(
                CASE
                    WHEN reports.date_time >= date_limits.today_start
                         AND reports.date_time <= date_limits.today_end
                         AND reports.status = 'Success'
                         AND reports.type = 'Debit'
                    THEN 1 ELSE 0
                END
            ), 0) AS service_today_reports,

            COALESCE(SUM(
                CASE
                    WHEN reports.date_time >= date_limits.today_start
                         AND reports.date_time <= date_limits.today_end
                         AND reports.status = 'Success'
                         AND reports.type = 'Debit'
                    THEN reports.amount ELSE 0
                END
            ), 0) AS service_today_amount

        FROM reports

        CROSS JOIN (
            SELECT
                ? AS today_start,
                ? AS today_end,
                ? AS range_start,
                ? AS range_end
        ) AS date_limits

        WHERE reports.date_time >= date_limits.range_start
          AND reports.date_time <= date_limits.range_end

        GROUP BY reports.service
    ", $todayStart, $todayEnd, $rangeStart, $rangeEnd)->fetchAll();

    $db->close();

    $totaladmininActiveUsers = (int)arr_get(
        $memberStats,
        'inactive_users',
        0
    );

    $totaladminr = (int)arr_get(
        $memberStats,
        'total_admin',
        0
    );

    $totalmember = [
        'total_member'  => (int)arr_get($memberStats, 'total_member', 0),
        'total_balance' => arr_get($memberStats, 'total_member_balance', 0),
    ];

    $totalretailer = [
        'total_member'  => (int)arr_get($memberStats, 'total_retailer', 0),
        'total_balance' => arr_get($memberStats, 'total_retailer_balance', 0),
    ];

    $totaldistributor = [
        'total_member'  => (int)arr_get($memberStats, 'total_distributor', 0),
        'total_balance' => arr_get($memberStats, 'total_distributor_balance', 0),
    ];

    $totalmaster = [
        'total_member'  => (int)arr_get($memberStats, 'total_master', 0),
        'total_balance' => arr_get($memberStats, 'total_master_balance', 0),
    ];

    $rechargetoday = [
        'total_reports' => 0,
        'total_amount'  => 0,
    ];

    $rechargehistory = [
        'total_reports' => 0,
        'total_amount'  => 0,
    ];

    $transactionMonthly = [
        'total_reports' => 0,
        'total_amount'  => 0,
    ];

    $transactionToday = [
        'total_reports' => 0,
        'total_amount'  => 0,
    ];

    $services = [
        'NSDL_PAN' => ['title' => 'Today NSDL PAN', 'class' => 'bg-success', 'url' => 'walletledger'],
        'RECHARGE' => ['title' => 'Today Mobile/DTH Recharge', 'class' => 'bg-success', 'url' => 'walletledger'],
        'AADHAAR FINGERPRINT' => ['title' => 'Today UID Fingerprint', 'class' => 'bg-success', 'url' => 'listofmember'],
        'AADHAAR NUMBER VERIFY' => ['title' => 'Today UID No. Verify', 'class' => 'bg-success', 'url' => 'listofmember'],
        'CHALLAN' => ['title' => 'Today Challan Verify', 'class' => 'bg-success', 'url' => 'listofmember'],
        'DL PDF' => ['title' => 'Today Driving Licence', 'class' => 'bg-yellow text-black', 'url' => 'listofmember'],
        'ENROLLMENT STATUS CHECK' => ['title' => 'Today Enroll Status', 'class' => 'bg-success', 'url' => 'listofmember'],
        'RATIONPDF' => ['title' => 'Today Ration PDF', 'class' => 'bg-yellow text-black', 'url' => 'listofmember'],
        'PANFIND' => ['title' => 'Today UID Pan FIND', 'class' => 'bg-yellow text-black', 'url' => 'listofmember'],
        'PANDETAILS' => ['title' => 'Today Pan details', 'class' => 'bg-yellow text-black', 'url' => 'listofmember'],
        'PAN UID LINK STATUS' => ['title' => 'Today Pan-UID Link-S', 'class' => 'bg-yellow text-black', 'url' => 'listofmember'],
        'PAN TO MASK UID' => ['title' => 'Today Pan Mask UID', 'class' => 'bg-purple', 'url' => 'listofmember'],
        'IFSCVERIFY' => ['title' => 'Today IFSC Verify', 'class' => 'bg-purple', 'url' => 'listofmember'],
        'MOBILEVERIFY' => ['title' => 'Today Mobile Verify', 'class' => 'bg-purple', 'url' => 'listofmember'],
        'PINCODEVERIFY' => ['title' => 'Today Pincode Verify', 'class' => 'bg-purple', 'url' => 'listofmember'],
        'VEHICALRCPDF' => ['title' => 'Today Vehical RC PDF', 'class' => 'bg-yellow text-black', 'url' => 'listofmember'],
        'VOTERADVANCE' => ['title' => 'Today Voter Advance', 'class' => 'bg-purple', 'url' => 'listofmember'],
        'VOTERPDF' => ['title' => 'Today Voter PDF', 'class' => 'bg-blue', 'url' => 'listofmember'],
        'VOTERMOBILELINK' => ['title' => 'Today Voter Mobile Link', 'class' => 'bg-purple', 'url' => 'listofmember'],
        'Pmjay_Verification' => ['title' => 'Today Ayushman', 'class' => 'bg-blue', 'url' => 'listofmember'],
        'LL REQUEST' => ['title' => 'Today LL Exam', 'class' => 'bg-blue', 'url' => 'listofmember'],
        'e_Sharm_Verification' => ['title' => 'Today e-Shram Card', 'class' => 'bg-blue', 'url' => 'listofmember'],
        'UID_TO_INSTANT_DETAILS' => ['title' => 'Today UID 2 Details', 'class' => 'bg-blue', 'url' => 'listofmember'],
        'RATION2_UID' => ['title' => 'Today Ration 2 UID', 'class' => 'bg-blue', 'url' => 'listofmember'],
        'Agri_Farmer_Certificate' => ['title' => 'Today Agri Farmer PDF', 'class' => 'bg-red', 'url' => 'listofmember'],
        'Agri_Farmer_Status' => ['title' => 'Today Agri Farmer Status', 'class' => 'bg-red', 'url' => 'listofmember'],
        'AADHAR_OTP_VERIFICATION' => ['title' => 'Today Aadhar OTP PDF', 'class' => 'bg-red', 'url' => 'listofmember'],
    ];

    $serviceStats = [];
    $serviceLookup = [];

    foreach ($services as $service => $meta) {
        $serviceStats[$service] = [
            'total_reports' => 0,
            'total_amount'  => 0,
        ];

        $serviceLookup[strtoupper($service)] = $service;
    }

    foreach ($reportRows as $reportRow) {
        $transactionMonthly['total_reports'] += (int)arr_get(
            $reportRow,
            'monthly_debit_reports',
            0
        );

        $transactionMonthly['total_amount'] += (float)arr_get(
            $reportRow,
            'monthly_debit_amount',
            0
        );

        $transactionToday['total_reports'] += (int)arr_get(
            $reportRow,
            'today_debit_reports',
            0
        );

        $transactionToday['total_amount'] += (float)arr_get(
            $reportRow,
            'today_debit_amount',
            0
        );

        $rechargehistory['total_reports'] += (int)arr_get(
            $reportRow,
            'monthly_recharge_reports',
            0
        );

        $rechargehistory['total_amount'] += (float)arr_get(
            $reportRow,
            'monthly_recharge_amount',
            0
        );

        $rechargetoday['total_reports'] += (int)arr_get(
            $reportRow,
            'today_recharge_reports',
            0
        );

        $rechargetoday['total_amount'] += (float)arr_get(
            $reportRow,
            'today_recharge_amount',
            0
        );

        $databaseService = strtoupper(
            trim((string)arr_get($reportRow, 'service', ''))
        );

        if (
            $databaseService !== '' &&
            isset($serviceLookup[$databaseService])
        ) {
            $serviceKey = $serviceLookup[$databaseService];

            $serviceStats[$serviceKey]['total_reports'] += (int)arr_get(
                $reportRow,
                'service_today_reports',
                0
            );

            $serviceStats[$serviceKey]['total_amount'] += (float)arr_get(
                $reportRow,
                'service_today_amount',
                0
            );
        }
    }

    /*
     * Use a closure instead of declaring a global function inside the
     * admin condition. This prevents "Cannot redeclare admin_card()" errors
     * if the template is included more than once.
     */
    $adminCard = function (
        $title,
        $value,
        $url = '#',
        $class = 'bg-primary',
        $small = ''
    ) {
        ?>
        <div class="col-lg-2 col-md-6 col-6 mb-2 hand">
            <div
                class="card pmd-card card-figure is-hoverable p-0 text-center <?= e($class) ?> text-white hand dashboard-card"
                onclick="location.href='<?= e($url) ?>'"
            >
                <div class="card-body">
                    <div class="card-icon">
                        <h4>
                            <?= $value ?>
                            <?php if ($small !== '') { ?>
                                <small><?= e($small) ?></small>
                            <?php } ?>
                        </h4>
                    </div>

                    <hr class="hr bg-white">
                    <p class="card-text"><?= e($title) ?></p>
                </div>
            </div>
        </div>
        <?php
    };

    $adminCard(
        'Total Users',
        e(arr_get($totalmember, 'total_member', 0)),
        'listofmember',
        'bg-red',
        '₹' . money(arr_get($totalmember, 'total_balance', 0))
    );

    $adminCard(
        'Inactive Users',
        '<small><i class="fas fa-times-circle" style="color:black;"></i></small> : ' .
            e($totaladmininActiveUsers),
        'listofmember',
        'bg-red'
    );

    $adminCard(
        'Total Verified Admin',
        '<small><i class="fas fa-times-circle" style="color:black;"></i></small> : ' .
            e($totaladminr),
        'listofmember',
        'bg-red'
    );

    $adminCard(
        'Total Master Distributor',
        e(arr_get($totalmaster, 'total_member', 0)),
        'listofmember',
        'bg-red',
        '₹' . money(arr_get($totalmaster, 'total_balance', 0))
    );

    $adminCard(
        'Total Distributor',
        e(arr_get($totaldistributor, 'total_member', 0)),
        'listofmember',
        'bg-red',
        '₹' . money(arr_get($totaldistributor, 'total_balance', 0))
    );

    $adminCard(
        'Total Retailer',
        e(arr_get($totalretailer, 'total_member', 0)),
        'listofmember',
        'bg-red',
        '₹' . money(arr_get($totalretailer, 'total_balance', 0))
    );

    $adminCard(
        'Add New Member',
        '<span class="fas fa-users"></span>',
        'addmember',
        'bg-primary'
    );

    $adminCard(
        'Open Complaints',
        e(arr_get($opencomplaints, 'total_reports', 0)),
        'opencomplaint',
        'bg-primary'
    );

    $adminCard(
        'Monthly Transactions',
        e(arr_get($transactionMonthly, 'total_reports', 0)),
        '#',
        'bg-primary',
        '₹' . money(arr_get($transactionMonthly, 'total_amount', 0))
    );

    $adminCard(
        'Today Transactions',
        e(arr_get($transactionToday, 'total_reports', 0)),
        '#',
        'bg-primary',
        '₹' . money(arr_get($transactionToday, 'total_amount', 0))
    );

    $adminCard(
        'Monthly Recharge',
        e(arr_get($rechargehistory, 'total_reports', 0)),
        '#',
        'bg-primary',
        '₹' . money(arr_get($rechargehistory, 'total_amount', 0))
    );

    $adminCard(
        'Today Recharge',
        e(arr_get($rechargetoday, 'total_reports', 0)),
        '#',
        'bg-primary',
        '₹' . money(arr_get($rechargetoday, 'total_amount', 0))
    );

    foreach ($services as $service => $meta) {
        $row = $serviceStats[$service];

        $adminCard(
            $meta['title'],
            e(arr_get($row, 'total_reports', 0)),
            $meta['url'],
            $meta['class'],
            '₹' . money(arr_get($row, 'total_amount', 0))
        );
    }
    ?>
<?php } ?>
</div>

<?php require_once("../layouts/members/foot.php"); ?>