<?php
include 'admin-header.php';

// --- KPI DATA FETCHING ---
$kpi_sql = "SELECT 
    (SELECT COUNT(DISTINCT referred_by) FROM users WHERE referred_by IS NOT NULL) as total_referrers,
    (SELECT COUNT(*) FROM users WHERE referred_by IS NOT NULL) as total_referred_users,
    (SELECT SUM(amount) FROM transactions WHERE transaction_type = 'Affiliate Commission') as total_commission_paid
";
$kpis = $conn->query($kpi_sql)->fetch_assoc();

// --- MAIN TABLE DATA ---
$affiliates_sql = "
    SELECT 
        u.id, u.full_name, u.email,
        COUNT(r.id) as referral_count,
        COALESCE(SUM(t.amount), 0) as total_commission
    FROM users u
    LEFT JOIN users r ON u.id = r.referred_by
    LEFT JOIN transactions t ON u.id = t.user_id AND t.transaction_type = 'Affiliate Commission'
    WHERE u.id IN (SELECT DISTINCT referred_by FROM users WHERE referred_by IS NOT NULL)
    GROUP BY u.id
    ORDER BY total_commission DESC
";
$affiliates_result = $conn->query($affiliates_sql);
?>
<div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
    <h1 class="h2">Affiliate Program Overview</h1>
</div>

<div class="row row-deck">
    <div class="col-md-4 mb-4">
        <div class="card kpi-card shadow-sm" style="--bs-primary: #0d6efd;">
            <div class="card-body"><div class="row align-items-center"><div class="col"><div class="card-title">Active Referrers</div><div class="card-text"><?php echo $kpis['total_referrers'] ?? 0; ?></div></div><div class="col-auto"><i class="bi bi-people-fill kpi-icon"></i></div></div></div>
        </div>
    </div>
    <div class="col-md-4 mb-4">
        <div class="card kpi-card shadow-sm" style="--bs-primary: #198754;">
            <div class="card-body"><div class="row align-items-center"><div class="col"><div class="card-title">Total Referred Users</div><div class="card-text"><?php echo $kpis['total_referred_users'] ?? 0; ?></div></div><div class="col-auto"><i class="bi bi-person-plus-fill kpi-icon"></i></div></div></div>
        </div>
    </div>
    <div class="col-md-4 mb-4">
        <div class="card kpi-card shadow-sm" style="--bs-primary: #6f42c1;">
            <div class="card-body"><div class="row align-items-center"><div class="col"><div class="card-title">Total Commission Paid</div><div class="card-text">₱<?php echo number_format($kpis['total_commission_paid'] ?? 0, 2); ?></div></div><div class="col-auto"><i class="bi bi-cash-stack kpi-icon"></i></div></div></div>
        </div>
    </div>
</div>

<div class="card shadow-sm">
    <div class="card-header fw-bold">Top Referrers</div>
    <div class="table-responsive">
        <table class="table table-hover align-middle">
            <thead class="table-dark">
                <tr><th>Referrer Name</th><th>Email</th><th class="text-center">Successful Referrals</th><th class="text-end">Total Commission Earned</th></tr>
            </thead>
            <tbody>
                <?php if ($affiliates_result && $affiliates_result->num_rows > 0): while($row = $affiliates_result->fetch_assoc()): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['full_name']); ?></td>
                    <td><?php echo htmlspecialchars($row['email']); ?></td>
                    <td class="text-center"><?php echo $row['referral_count']; ?></td>
                    <td class="text-end fw-bold text-success">₱<?php echo number_format($row['total_commission'], 2); ?></td>
                </tr>
                <?php endwhile; else: ?>
                <tr><td colspan="4" class="text-center text-muted p-4">No affiliate activity yet.</td></tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<?php include 'admin-footer.php'; ?>
