<?php
//Developer: core tech web designTechnology
//Whatsapp: +2349162671360
//Telegram: @jeditech
include ('include/header.php');
$page_title = WEB_NAME . " | Referral Statistics";

// ── Overall Stats ─────────────────────────────────────────────────────────
try {
    $total_referrals   = $pdo->query("SELECT COUNT(*) FROM referrals")->fetchColumn();
    $active_referrals  = $pdo->query("SELECT COUNT(*) FROM referrals WHERE status = 'active'")->fetchColumn();
    $pending_referrals = $pdo->query("SELECT COUNT(*) FROM referrals WHERE status = 'pending'")->fetchColumn();
    $total_commission  = $pdo->query("SELECT COALESCE(SUM(commission_amount),0) FROM referral_earnings WHERE status='credited'")->fetchColumn();
    $total_earners     = $pdo->query("SELECT COUNT(DISTINCT referrer_id) FROM referral_earnings WHERE status='credited'")->fetchColumn();
    $total_withdrawals = $pdo->query("SELECT COALESCE(SUM(amount),0) FROM withdrawals WHERE status='completed'")->fetchColumn();
} catch (PDOException $e) {
    $total_referrals = $active_referrals = $pending_referrals = 0;
    $total_commission = $total_earners = $total_withdrawals = 0;
}

// ── Top Referrers ─────────────────────────────────────────────────────────
try {
    $top_stmt = $pdo->query(
        "SELECT u.id, u.username, u.email, u.referral_balance, u.total_earnings,
                COUNT(r.id) as referral_count,
                COALESCE(SUM(re.commission_amount),0) as total_commission
         FROM users u
         LEFT JOIN referrals r ON r.referrer_id = u.id
         LEFT JOIN referral_earnings re ON re.referrer_id = u.id AND re.status='credited'
         GROUP BY u.id
         HAVING referral_count > 0
         ORDER BY total_commission DESC
         LIMIT 20"
    );
    $top_referrers = $top_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    $top_referrers = [];
}

// ── Recent Earnings ───────────────────────────────────────────────────────
try {
    $recent_stmt = $pdo->query(
        "SELECT re.*,
                ur.username AS referrer_username,
                ue.username AS referred_username,
                t.created_at AS transaction_date
         FROM referral_earnings re
         JOIN users ur ON re.referrer_id = ur.id
         JOIN users ue ON re.referred_user_id = ue.id
         JOIN transactions t ON re.transaction_id = t.id
         ORDER BY re.created_at DESC
         LIMIT 30"
    );
    $recent_earnings = $recent_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    $recent_earnings = [];
}

// ── All Referrals list ────────────────────────────────────────────────────
try {
    $all_stmt = $pdo->query(
        "SELECT r.*, ur.username AS referrer_name, ue.username AS referred_name, ue.email AS referred_email
         FROM referrals r
         JOIN users ur ON r.referrer_id = ur.id
         JOIN users ue ON r.referred_user_id = ue.id
         ORDER BY r.created_at DESC"
    );
    $all_referrals = $all_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    $all_referrals = [];
}
?>
<title><?php echo $page_title; ?></title>
<link href="vendor/datatables/dataTables.bootstrap4.min.css" rel="stylesheet">

<div class="container-fluid" id="container-wrapper">
  <div class="d-sm-flex align-items-center justify-content-between mb-4">
    <h1 class="h3 mb-0 text-gray-800"><i class="fas fa-users mr-2"></i>Referral Statistics</h1>
    <ol class="breadcrumb">
      <li class="breadcrumb-item"><a href="dashboard.php">Home</a></li>
      <li class="breadcrumb-item active">Referral Statistics</li>
    </ol>
  </div>

  <!-- ── Summary Cards ──────────────────────────────────────────────────── -->
  <div class="row mb-4">
    <div class="col-xl-2 col-md-4 col-sm-6 mb-4">
      <div class="card border-left-primary shadow h-100 py-2">
        <div class="card-body">
          <div class="row no-gutters align-items-center">
            <div class="col mr-2">
              <div class="text-xs font-weight-bold text-primary text-uppercase mb-1">Total Referrals</div>
              <div class="h5 mb-0 font-weight-bold text-gray-800"><?php echo number_format($total_referrals); ?></div>
            </div>
            <div class="col-auto"><i class="fas fa-users fa-2x text-gray-300"></i></div>
          </div>
        </div>
      </div>
    </div>
    <div class="col-xl-2 col-md-4 col-sm-6 mb-4">
      <div class="card border-left-success shadow h-100 py-2">
        <div class="card-body">
          <div class="row no-gutters align-items-center">
            <div class="col mr-2">
              <div class="text-xs font-weight-bold text-success text-uppercase mb-1">Active</div>
              <div class="h5 mb-0 font-weight-bold text-gray-800"><?php echo number_format($active_referrals); ?></div>
            </div>
            <div class="col-auto"><i class="fas fa-user-check fa-2x text-gray-300"></i></div>
          </div>
        </div>
      </div>
    </div>
    <div class="col-xl-2 col-md-4 col-sm-6 mb-4">
      <div class="card border-left-warning shadow h-100 py-2">
        <div class="card-body">
          <div class="row no-gutters align-items-center">
            <div class="col mr-2">
              <div class="text-xs font-weight-bold text-warning text-uppercase mb-1">Pending</div>
              <div class="h5 mb-0 font-weight-bold text-gray-800"><?php echo number_format($pending_referrals); ?></div>
            </div>
            <div class="col-auto"><i class="fas fa-clock fa-2x text-gray-300"></i></div>
          </div>
        </div>
      </div>
    </div>
    <div class="col-xl-2 col-md-4 col-sm-6 mb-4">
      <div class="card border-left-info shadow h-100 py-2">
        <div class="card-body">
          <div class="row no-gutters align-items-center">
            <div class="col mr-2">
              <div class="text-xs font-weight-bold text-info text-uppercase mb-1">Total Commission</div>
              <div class="h5 mb-0 font-weight-bold text-gray-800">₦<?php echo number_format($total_commission, 2); ?></div>
            </div>
            <div class="col-auto"><i class="fas fa-coins fa-2x text-gray-300"></i></div>
          </div>
        </div>
      </div>
    </div>
    <div class="col-xl-2 col-md-4 col-sm-6 mb-4">
      <div class="card border-left-secondary shadow h-100 py-2">
        <div class="card-body">
          <div class="row no-gutters align-items-center">
            <div class="col mr-2">
              <div class="text-xs font-weight-bold text-secondary text-uppercase mb-1">Earners</div>
              <div class="h5 mb-0 font-weight-bold text-gray-800"><?php echo number_format($total_earners); ?></div>
            </div>
            <div class="col-auto"><i class="fas fa-award fa-2x text-gray-300"></i></div>
          </div>
        </div>
      </div>
    </div>
    <div class="col-xl-2 col-md-4 col-sm-6 mb-4">
      <div class="card border-left-danger shadow h-100 py-2">
        <div class="card-body">
          <div class="row no-gutters align-items-center">
            <div class="col mr-2">
              <div class="text-xs font-weight-bold text-danger text-uppercase mb-1">Withdrawn</div>
              <div class="h5 mb-0 font-weight-bold text-gray-800">₦<?php echo number_format($total_withdrawals, 2); ?></div>
            </div>
            <div class="col-auto"><i class="fas fa-money-bill-wave fa-2x text-gray-300"></i></div>
          </div>
        </div>
      </div>
    </div>
  </div>

  <div class="row">
    <!-- Top Referrers -->
    <div class="col-lg-6 mb-4">
      <div class="card shadow">
        <div class="card-header py-3 d-flex justify-content-between align-items-center"
             style="background:linear-gradient(135deg,#0201FD,#111199);">
          <h6 class="m-0 font-weight-bold text-white"><i class="fas fa-trophy mr-2"></i>Top Referrers</h6>
        </div>
        <div class="card-body p-0">
          <div class="table-responsive">
            <table class="table table-hover table-sm mb-0">
              <thead class="thead-light">
                <tr>
                  <th>#</th>
                  <th>Username</th>
                  <th>Referrals</th>
                  <th>Commission</th>
                  <th>Balance</th>
                </tr>
              </thead>
              <tbody>
                <?php foreach ($top_referrers as $i => $tr): ?>
                <tr>
                  <td><?php echo $i+1; ?></td>
                  <td>
                    <strong><?php echo htmlspecialchars($tr['username']); ?></strong><br>
                    <small class="text-muted"><?php echo htmlspecialchars($tr['email']); ?></small>
                  </td>
                  <td><span class="badge badge-primary"><?php echo $tr['referral_count']; ?></span></td>
                  <td class="text-success font-weight-bold">₦<?php echo number_format($tr['total_commission'], 2); ?></td>
                  <td>₦<?php echo number_format($tr['referral_balance'], 2); ?></td>
                </tr>
                <?php endforeach; ?>
                <?php if (empty($top_referrers)): ?>
                <tr><td colspan="5" class="text-center text-muted py-3">No referral data yet.</td></tr>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>

    <!-- Recent Earnings -->
    <div class="col-lg-6 mb-4">
      <div class="card shadow">
        <div class="card-header py-3 d-flex justify-content-between align-items-center"
             style="background:linear-gradient(135deg,#1cc88a,#17a673);">
          <h6 class="m-0 font-weight-bold text-white"><i class="fas fa-money-bill-trend-up mr-2"></i>Recent Commissions</h6>
        </div>
        <div class="card-body p-0">
          <div class="table-responsive">
            <table class="table table-hover table-sm mb-0">
              <thead class="thead-light">
                <tr>
                  <th>Referrer</th>
                  <th>Referred</th>
                  <th>Deposit</th>
                  <th>Commission</th>
                  <th>Date</th>
                </tr>
              </thead>
              <tbody>
                <?php foreach ($recent_earnings as $e): ?>
                <tr>
                  <td><strong><?php echo htmlspecialchars($e['referrer_username']); ?></strong></td>
                  <td><?php echo htmlspecialchars($e['referred_username']); ?></td>
                  <td>₦<?php echo number_format($e['deposit_amount'], 2); ?></td>
                  <td class="text-success font-weight-bold">
                    +₦<?php echo number_format($e['commission_amount'], 2); ?>
                    <small class="text-muted">(<?php echo $e['commission_percent']; ?>%)</small>
                  </td>
                  <td><small><?php echo date('M d, Y', strtotime($e['transaction_date'])); ?></small></td>
                </tr>
                <?php endforeach; ?>
                <?php if (empty($recent_earnings)): ?>
                <tr><td colspan="5" class="text-center text-muted py-3">No commissions yet.</td></tr>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>
  </div>

  <!-- ── All Referrals Table ────────────────────────────────────────────── -->
  <div class="card shadow mb-4">
    <div class="card-header py-3" style="background:linear-gradient(135deg,#0201FD,#111199);">
      <h6 class="m-0 font-weight-bold text-white"><i class="fas fa-list mr-2"></i>All Referral Records</h6>
    </div>
    <div class="card-body">
      <div class="table-responsive">
        <table class="table table-bordered table-hover" id="allReferralsTable" width="100%" cellspacing="0">
          <thead class="thead-light">
            <tr>
              <th>#</th>
              <th>Referrer</th>
              <th>Referred User</th>
              <th>Referral Code</th>
              <th>Earnings</th>
              <th>Status</th>
              <th>Date</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($all_referrals as $i => $ar): ?>
            <tr>
              <td><?php echo $i+1; ?></td>
              <td>
                <strong><?php echo htmlspecialchars($ar['referrer_name']); ?></strong>
                <br><small class="text-muted">#<?php echo $ar['referrer_id']; ?></small>
              </td>
              <td>
                <?php echo htmlspecialchars($ar['referred_name']); ?>
                <br><small class="text-muted"><?php echo htmlspecialchars($ar['referred_email']); ?></small>
              </td>
              <td><code><?php echo htmlspecialchars($ar['referral_code']); ?></code></td>
              <td class="font-weight-bold">₦<?php echo number_format($ar['earnings'], 2); ?></td>
              <td>
                <?php if ($ar['status'] === 'active'): ?>
                  <span class="badge badge-success">Active</span>
                <?php elseif ($ar['status'] === 'completed'): ?>
                  <span class="badge badge-info">Completed</span>
                <?php else: ?>
                  <span class="badge badge-warning text-dark">Pending</span>
                <?php endif; ?>
              </td>
              <td><?php echo date('M d, Y', strtotime($ar['created_at'])); ?></td>
            </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<script src="vendor/datatables/jquery.dataTables.min.js"></script>
<script src="vendor/datatables/dataTables.bootstrap4.min.js"></script>
<script>
$(document).ready(function() {
    $('#allReferralsTable').DataTable({ order: [[6, 'desc']], pageLength: 25 });
});
</script>

<?php include ('include/footer.php'); ?>