Edit

Editing: /home/goacom/public_html/scratch_extract/admin/index.php

<?php
require_once 'db.php';
require_once 'auth.php';
check_admin_auth();

// Fetch statistics
$stats = [
    'total_users' => 0,
    'total_deposits' => 0.00,
    'total_withdrawals' => 0.00,
    'pending_deposits_count' => 0,
    'pending_withdrawals_count' => 0,
    'open_support_tickets' => 0
];

try {
    // 1. Total users
    $stats['total_users'] = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();

// 2. Approved Deposits
    $stats['total_deposits'] = (float)$pdo->query("SELECT SUM(amount) FROM deposit_withdrawal_history WHERE type='deposit' AND status='approved'")->fetchColumn();

// 3. Approved Withdrawals
    $stats['total_withdrawals'] = (float)$pdo->query("SELECT SUM(amount) FROM deposit_withdrawal_history WHERE type='withdrawal' AND status='approved'")->fetchColumn();

// 4. Pending Deposits
    $stats['pending_deposits_count'] = (int)$pdo->query("SELECT COUNT(*) FROM deposit_withdrawal_history WHERE type='deposit' AND (status='pending' OR status='processing')")->fetchColumn();

// 5. Pending Withdrawals
    $stats['pending_withdrawals_count'] = (int)$pdo->query("SELECT COUNT(*) FROM deposit_withdrawal_history WHERE type='withdrawal' AND status='pending'")->fetchColumn();

// 6. Open Support Tickets
    try {
        $stats['open_support_tickets'] = (int)$pdo->query("SELECT COUNT(*) FROM support_tickets WHERE status IN ('open','customer_reply')")->fetchColumn();
    } catch (Exception $e2) { }

// 7. Recent Activity
    $stmt = $pdo->query("SELECT h.*, u.phone FROM deposit_withdrawal_history h JOIN users u ON h.user_id = u.id ORDER BY h.created_at DESC LIMIT 8");
    $recent_activities = $stmt->fetchAll();

// 7. Get Chart Data (Deposits vs Withdrawals by Day for the last 7 days)
    $chart_labels = [];
    $chart_deposits = [];
    $chart_withdrawals = [];
    
    for ($i = 6; $i >= 0; $i--) {
        $date = date('Y-m-d', strtotime("-$i days"));
        $chart_labels[] = date('D', strtotime($date));
        
        $stmt_dep = $pdo->prepare("SELECT SUM(amount) FROM deposit_withdrawal_history WHERE type='deposit' AND status='approved' AND DATE(created_at) = ?");
        $stmt_dep->execute([$date]);
        $chart_deposits[] = (float)$stmt_dep->fetchColumn();

$stmt_wit = $pdo->prepare("SELECT SUM(amount) FROM deposit_withdrawal_history WHERE type='withdrawal' AND status='approved' AND DATE(created_at) = ?");
        $stmt_wit->execute([$date]);
        $chart_withdrawals[] = (float)$stmt_wit->fetchColumn();
    }
} catch (Exception $e) {
    // Suppress errors if DB tables are empty
    $recent_activities = [];
    $chart_labels = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'];
    $chart_deposits = [0, 0, 0, 0, 0, 0, 0];
    $chart_withdrawals = [0, 0, 0, 0, 0, 0, 0];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Admin Dashboard - MELBETE</title>
    <link href="https://fonts.googleapis.com/css2?family=Outfit:wght@300;400;500;600;700;800&display=swap" rel="stylesheet">
    <script src="https://cdn.tailwindcss.com"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
        tailwind.config = {
            theme: {
                extend: {
                    fontFamily: {
                        sans: ['Outfit', 'sans-serif'],
                    },
                    colors: {
                        darkBg: '#0b0f19',
                        darkCard: '#111827',
                        primary: '#f06827',
                        primaryHover: '#d85315'
                    }
                }
            }
        }
    </script>
    <style>
        /* Custom scrollbar styling */
        ::-webkit-scrollbar {
            width: 6px;
            height: 6px;
        }
        ::-webkit-scrollbar-track {
            background: #0b0f19;
        }
        ::-webkit-scrollbar-thumb {
            background: #1f2937;
            border-radius: 3px;
        }
        ::-webkit-scrollbar-thumb:hover {
            background: #374151;
        }
    </style>
</head>
<body class="bg-darkBg text-slate-100 min-h-screen flex flex-col md:flex-row">
    
    
    <?php include 'sidebar.php'; ?>

<main class="flex-1 p-6 md:p-10 overflow-y-auto space-y-8">
        
        
        <div class="flex flex-col md:flex-row md:items-center justify-between gap-4 border-b border-slate-900 pb-6">
            <div>
                <h1 class="text-2xl md:text-3xl font-extrabold text-white tracking-tight">Overview Dashboard</h1>
                <p class="text-slate-400 text-sm mt-1">Real-time financials and user activity tracking</p>
            </div>
            <div class="flex items-center gap-3">
                <span class="text-xs bg-slate-900 border border-slate-800 text-slate-300 px-4 py-2.5 rounded-2xl flex items-center gap-2 font-medium">
                    <span class="w-2 h-2 rounded-full bg-emerald-500 animate-pulse"></span>
                    System Live
                </span>
            </div>
        </div>

<div class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-4 gap-6">
            
            
            <div class="bg-darkCard/50 backdrop-blur border border-slate-900 rounded-3xl p-6 relative overflow-hidden transition-all duration-300 hover:border-slate-800 group">
                <div class="absolute -right-4 -bottom-4 text-slate-800/10 group-hover:text-primary/5 transition-all duration-300">
                    <svg xmlns="http://www.w3.org/2000/svg" class="h-28 w-28" fill="none" viewBox="0 0 24 24" stroke="currentColor">
                        <path stroke-linecap="round" stroke-linejoin="round" stroke-width="1.5" d="M12 4.354a4 4 0 110 5.292M15 21H3v-1a6 6 0 0112 0v1zm0 0h6v-1a6 6 0 00-9-5.197M13 7a4 4 0 11-8 0 4 4 0 018 0z" />
                    </svg>
                </div>
                <span class="text-slate-400 text-xs font-semibold uppercase tracking-wider">Total Members</span>
                <h2 class="text-3xl md:text-4xl font-extrabold text-white mt-2"><?php echo number_format($stats['total_users']); ?></h2>
                <div class="mt-4 flex items-center text-xs text-slate-500 font-medium">
                    <span>Registered users database</span>
                </div>
            </div>

<div class="bg-darkCard/50 backdrop-blur border border-slate-900 rounded-3xl p-6 relative overflow-hidden transition-all duration-300 hover:border-slate-800 group">
                <div class="absolute -right-4 -bottom-4 text-slate-800/10 group-hover:text-emerald-500/5 transition-all duration-300">
                    <svg xmlns="http://www.w3.org/2000/svg" class="h-28 w-28" fill="none" viewBox="0 0 24 24" stroke="currentColor">
                        <path stroke-linecap="round" stroke-linejoin="round" stroke-width="1.5" d="M17 9V7a2 2 0 00-2-2H5a2 2 0 00-2 2v6a2 2 0 002 2h2m2 4h10a2 2 0 002-2v-6a2 2 0 00-2-2H9a2 2 0 00-2 2v6a2 2 0 002 2zm7-5a2 2 0 11-4 0 2 2 0 014 0z" />
                    </svg>
                </div>
                <span class="text-slate-400 text-xs font-semibold uppercase tracking-wider">Approved Deposits</span>
                <h2 class="text-3xl md:text-4xl font-extrabold text-emerald-400 mt-2">৳ <?php echo number_format($stats['total_deposits'], 2); ?></h2>
                <div class="mt-4 flex items-center text-xs text-slate-500 font-medium">
                    <span>Aggregated approved deposits</span>
                </div>
            </div>

<div class="bg-darkCard/50 backdrop-blur border border-slate-900 rounded-3xl p-6 relative overflow-hidden transition-all duration-300 hover:border-slate-800 group">
                <div class="absolute -right-4 -bottom-4 text-slate-800/10 group-hover:text-rose-500/5 transition-all duration-300">
                    <svg xmlns="http://www.w3.org/2000/svg" class="h-28 w-28" fill="none" viewBox="0 0 24 24" stroke="currentColor">
                        <path stroke-linecap="round" stroke-linejoin="round" stroke-width="1.5" d="M12 8c-1.657 0-3 .895-3 2s1.343 2 3 2 3 .895 3 2-1.343 2-3 2m0-8c1.11 0 2.08.402 2.599 1M12 8V7m0 1v8m0 0v1m0-1c-1.11 0-2.08-.402-2.599-1M21 12a9 9 0 11-18 0 9 9 0 0118 0z" />
                    </svg>
                </div>
                <span class="text-slate-400 text-xs font-semibold uppercase tracking-wider">Approved Withdrawals</span>
                <h2 class="text-3xl md:text-4xl font-extrabold text-rose-400 mt-2">৳ <?php echo number_format($stats['total_withdrawals'], 2); ?></h2>
                <div class="mt-4 flex items-center text-xs text-slate-500 font-medium">
                    <span>Aggregated approved withdrawals</span>
                </div>
            </div>

<div class="bg-darkCard/50 backdrop-blur border border-slate-900 rounded-3xl p-6 relative overflow-hidden transition-all duration-300 hover:border-slate-800 group">
                <div class="absolute -right-4 -bottom-4 text-slate-800/10 group-hover:text-primary/5 transition-all duration-300">
                    <svg xmlns="http://www.w3.org/2000/svg" class="h-28 w-28" fill="none" viewBox="0 0 24 24" stroke="currentColor">
                        <path stroke-linecap="round" stroke-linejoin="round" stroke-width="1.5" d="M12 9v2m0 4h.01m-6.938 4h13.856c1.54 0 2.502-1.667 1.732-3L13.732 4c-.77-1.333-2.694-1.333-3.464 0L3.34 16c-.77 1.333.192 3 1.732 3z" />
                    </svg>
                </div>
                <span class="text-slate-400 text-xs font-semibold uppercase tracking-wider">Pending Transactions</span>
                <h2 class="text-3xl md:text-4xl font-extrabold text-primary mt-2">
                    <?php echo $stats['pending_deposits_count'] + $stats['pending_withdrawals_count']; ?>
                </h2>
                <div class="mt-4 flex items-center gap-3 text-[11px] font-medium text-slate-400">
                    <span class="flex items-center gap-1"><span class="w-1.5 h-1.5 rounded-full bg-emerald-400"></span><?php echo $stats['pending_deposits_count']; ?> Deposits</span>
                    <span class="flex items-center gap-1"><span class="w-1.5 h-1.5 rounded-full bg-rose-400"></span><?php echo $stats['pending_withdrawals_count']; ?> Withdraws</span>
                </div>
            </div>

</div>

<div class="grid grid-cols-1 lg:grid-cols-3 gap-8">
            
            
            <div class="bg-darkCard/30 border border-slate-900/60 rounded-3xl p-6 lg:col-span-2 space-y-6">
                <div class="flex items-center justify-between">
                    <div>
                        <h3 class="font-bold text-white text-base">Financial Analytics</h3>
                        <p class="text-xs text-slate-400 mt-0.5">Approved Deposits vs Withdrawals (Last 7 Days)</p>
                    </div>
                </div>
                <div class="h-80 relative">
                    <canvas id="financialChart"></canvas>
                </div>
            </div>

<div class="bg-darkCard/30 border border-slate-900/60 rounded-3xl p-6 flex flex-col space-y-6">
                <div class="flex items-center justify-between">
                    <div>
                        <h3 class="font-bold text-white text-base">Pending Action Overview</h3>
                        <p class="text-xs text-slate-400 mt-0.5 font-medium">Quick link shortcut items</p>
                    </div>
                </div>
                <div class="flex-1 flex flex-col gap-4 justify-center">
                    
                    <a href="deposits.php" class="bg-slate-900/40 border border-slate-800 hover:border-emerald-500/20 p-4 rounded-2xl flex items-center justify-between transition-all duration-300 group">
                        <div class="flex items-center gap-3">
                            <span class="p-2 bg-emerald-500/10 text-emerald-400 rounded-xl">
                                <svg xmlns="http://www.w3.org/2000/svg" class="h-5 w-5" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M17 9V7a2 2 0 00-2-2H5a2 2 0 00-2 2v6a2 2 0 002 2h2" /></svg>
                            </span>
                            <div>
                                <h4 class="font-bold text-white text-xs">Verify Deposits</h4>
                                <p class="text-[10px] text-slate-500 mt-0.5">Review incoming ledger payments</p>
                            </div>
                        </div>
                        <span class="bg-emerald-500/10 text-emerald-400 text-xs font-extrabold px-2.5 py-1 rounded-full group-hover:scale-105 transition-all">
                            <?php echo $stats['pending_deposits_count']; ?>
                        </span>
                    </a>

<a href="withdrawals.php" class="bg-slate-900/40 border border-slate-800 hover:border-rose-500/20 p-4 rounded-2xl flex items-center justify-between transition-all duration-300 group">
                        <div class="flex items-center gap-3">
                            <span class="p-2 bg-rose-500/10 text-rose-400 rounded-xl">
                                <svg xmlns="http://www.w3.org/2000/svg" class="h-5 w-5" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M12 8c-1.657 0-3 .895-3 2s1.343 2 3 2" /></svg>
                            </span>
                            <div>
                                <h4 class="font-bold text-white text-xs">Payout Withdrawals</h4>
                                <p class="text-[10px] text-slate-500 mt-0.5">Approve and transfer user withdrawals</p>
                            </div>
                        </div>
                        <span class="bg-rose-500/10 text-rose-400 text-xs font-extrabold px-2.5 py-1 rounded-full group-hover:scale-105 transition-all">
                            <?php echo $stats['pending_withdrawals_count']; ?>
                        </span>
                    </a>

<a href="suggestions.php" class="bg-slate-900/40 border border-slate-800 hover:border-primary/20 p-4 rounded-2xl flex items-center justify-between transition-all duration-300 group">
                        <div class="flex items-center gap-3">
                            <span class="p-2 bg-primary/10 text-primary rounded-xl">
                                <svg xmlns="http://www.w3.org/2000/svg" class="h-5 w-5" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9.663 17h4.673M12 3v1" /></svg>
                            </span>
                            <div>
                                <h4 class="font-bold text-white text-xs">User Suggestions</h4>
                                <p class="text-[10px] text-slate-500 mt-0.5">Read bugs and platform feedback</p>
                            </div>
                        </div>
                        <span class="bg-primary/10 text-primary text-xs font-extrabold px-2.5 py-1 rounded-full group-hover:scale-105 transition-all">
                            Check
                        </span>
                    </a>

</div>
            </div>

</div>

<div class="bg-darkCard/30 border border-slate-900/60 rounded-3xl p-6 space-y-6">
            <div class="flex flex-col sm:flex-row sm:items-center justify-between gap-4">
                <div>
                    <h3 class="font-bold text-white text-base">Recent Ledger Operations</h3>
                    <p class="text-xs text-slate-400 mt-0.5">Latest deposits and withdrawal requests</p>
                </div>
                <div class="flex gap-2">
                    <a href="deposits.php" class="text-xs bg-slate-900 hover:bg-slate-800 text-slate-300 font-semibold px-4 py-2 rounded-xl border border-slate-800/80 transition-all">Deposits</a>
                    <a href="withdrawals.php" class="text-xs bg-slate-900 hover:bg-slate-800 text-slate-300 font-semibold px-4 py-2 rounded-xl border border-slate-800/80 transition-all">Withdrawals</a>
                </div>
            </div>

<div class="overflow-x-auto">
                <table class="w-full text-left border-collapse text-sm">
                    <thead>
                        <tr class="border-b border-slate-900 text-slate-400 font-medium">
                            <th class="py-3 px-4">User Phone</th>
                            <th class="py-3 px-4">Type</th>
                            <th class="py-3 px-4">Amount</th>
                            <th class="py-3 px-4">Payment Method</th>
                            <th class="py-3 px-4">Created At</th>
                            <th class="py-3 px-4">Status</th>
                        </tr>
                    </thead>
                    <tbody class="divide-y divide-slate-900/40 text-slate-300">
                        <?php if (empty($recent_activities)): ?>
                            <tr>
                                <td colspan="6" class="py-6 text-center text-slate-500">No recent transactions found.</td>
                            </tr>
                        <?php else: ?>
                            <?php foreach ($recent_activities as $row): 
                                $typeColor = $row['type'] === 'deposit' ? 'text-emerald-400 bg-emerald-500/5 border border-emerald-500/10' : 'text-rose-400 bg-rose-500/5 border border-rose-500/10';
                                
                                switch($row['status']) {
                                    case 'approved':
                                        $statusClass = 'text-emerald-400 bg-emerald-400/10';
                                        break;
                                    case 'rejected':
                                        $statusClass = 'text-rose-400 bg-rose-400/10';
                                        break;
                                    case 'processing':
                                        $statusClass = 'text-amber-400 bg-amber-400/10';
                                        break;
                                    default:
                                        $statusClass = 'text-slate-400 bg-slate-400/10';
                                }
                            ?>
                                <tr class="hover:bg-slate-900/20 transition-colors">
                                    <td class="py-3.5 px-4 font-bold text-white"><?php echo htmlspecialchars($row['phone']); ?></td>
                                    <td class="py-3.5 px-4">
                                        <span class="text-[10px] font-bold uppercase tracking-wider px-2 py-0.5 rounded-full <?php echo $typeColor; ?>">
                                            <?php echo htmlspecialchars($row['type']); ?>
                                        </span>
                                    </td>
                                    <td class="py-3.5 px-4 font-bold">৳ <?php echo number_format($row['amount'], 2); ?></td>
                                    <td class="py-3.5 px-4 text-slate-400"><?php echo htmlspecialchars($row['payment_method']); ?></td>
                                    <td class="py-3.5 px-4 text-xs text-slate-500"><?php echo htmlspecialchars($row['created_at']); ?></td>
                                    <td class="py-3.5 px-4">
                                        <span class="text-xs px-2.5 py-0.5 rounded-full font-bold uppercase tracking-wide <?php echo $statusClass; ?>">
                                            <?php echo htmlspecialchars($row['status']); ?>
                                        </span>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

</main>

<script>
        document.addEventListener("DOMContentLoaded", function() {
            const ctx = document.getElementById('financialChart').getContext('2d');
            
            // Raw Dynamic PHP Chart arrays
            const labels = <?php echo json_encode($chart_labels); ?>;
            const deposits = <?php echo json_encode($chart_deposits); ?>;
            const withdrawals = <?php echo json_encode($chart_withdrawals); ?>;

new Chart(ctx, {
                type: 'line',
                data: {
                    labels: labels,
                    datasets: [
                        {
                            label: 'Deposits (৳)',
                            data: deposits,
                            borderColor: '#34d399',
                            backgroundColor: 'rgba(52, 211, 153, 0.05)',
                            fill: true,
                            tension: 0.4,
                            borderWidth: 3,
                            pointBackgroundColor: '#34d399',
                            pointHoverRadius: 6
                        },
                        {
                            label: 'Withdrawals (৳)',
                            data: withdrawals,
                            borderColor: '#f87171',
                            backgroundColor: 'rgba(248, 113, 113, 0.05)',
                            fill: true,
                            tension: 0.4,
                            borderWidth: 3,
                            pointBackgroundColor: '#f87171',
                            pointHoverRadius: 6
                        }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            labels: {
                                color: '#94a3b8',
                                font: {
                                    family: 'Outfit',
                                    size: 12,
                                    weight: 'bold'
                                }
                            }
                        }
                    },
                    scales: {
                        y: {
                            grid: {
                                color: '#1f2937'
                            },
                            ticks: {
                                color: '#94a3b8',
                                font: {
                                    family: 'Outfit'
                                }
                            }
                        },
                        x: {
                            grid: {
                                color: '#1f2937'
                            },
                            ticks: {
                                color: '#94a3b8',
                                font: {
                                    family: 'Outfit'
                                }
                            }
                        }
                    }
                }
            });
        });
    </script>
</body>
</html>