<?php

include "../includes/admin_auth.php";
include "../config/database.php";

if(!isset($_GET['plan'])){
    header("Location: dashboard.php");
    exit();
}

$planId = (int)$_GET['plan'];

$selectedMonth = isset($_GET['month'])
    ? (int)$_GET['month']
    : date("n");

$selectedYear = isset($_GET['year'])
    ? (int)$_GET['year']
    : date("Y");


/*
|--------------------------------------------------------------------------
| Scheme Details
|--------------------------------------------------------------------------
*/

$stmt = $pdo->prepare("
SELECT *
FROM plans
WHERE id=?
LIMIT 1
");

$stmt->execute([$planId]);

$plan = $stmt->fetch(PDO::FETCH_ASSOC);

if(!$plan){
    die("Scheme not found.");
}


/*
|--------------------------------------------------------------------------
| Monthly Summary
|--------------------------------------------------------------------------
*/

$stmt = $pdo->prepare("

SELECT

COUNT(DISTINCT s.user_id) members,

IFNULL(SUM(s.hands),0) hands,

COUNT(DISTINCT s.id) subscriptions,

IFNULL(SUM(

CASE
WHEN c.status='paid'
THEN c.amount
ELSE 0
END

),0) collected,

IFNULL(SUM(

CASE
WHEN c.status IN('pending','overdue')
THEN c.amount
ELSE 0
END

),0) remaining

FROM subscriptions s

LEFT JOIN contributions c
ON s.id=c.subscription_id
AND MONTH(c.due_date)=?
AND YEAR(c.due_date)=?

WHERE s.plan_id=?
AND s.status='active'

");

$stmt->execute([
    $selectedMonth,
    $selectedYear,
    $planId
]);

$summary = $stmt->fetch(PDO::FETCH_ASSOC);


/*
|--------------------------------------------------------------------------
| Members
|--------------------------------------------------------------------------
*/

$stmt = $pdo->prepare("

SELECT

u.id,

u.fullname,

u.phone,

u.wallet_balance,

s.id subscription_id,

s.hands,

p.amount monthly_amount,

COUNT(c.id) total_due,

SUM(
CASE
WHEN c.status='paid'
THEN c.amount
ELSE 0
END
) paid,

SUM(
CASE
WHEN c.status IN('pending','overdue')
THEN c.amount
ELSE 0
END
) remaining,

MAX(c.status) payment_status

FROM subscriptions s

INNER JOIN users u
ON s.user_id=u.id

INNER JOIN plans p
ON s.plan_id=p.id

LEFT JOIN contributions c
ON s.id=c.subscription_id
AND MONTH(c.due_date)=?
AND YEAR(c.due_date)=?

WHERE s.plan_id=?
AND s.status='active'

GROUP BY s.id

ORDER BY u.fullname ASC

");

$stmt->execute([
    $selectedMonth,
    $selectedYear,
    $planId
]);

$members = $stmt->fetchAll(PDO::FETCH_ASSOC);

?>























<!DOCTYPE html>
<html lang="en">

<head>

<meta charset="UTF-8">

<meta name="viewport" content="width=device-width, initial-scale=1.0">

<title><?= htmlspecialchars($plan['plan_name']); ?></title>

<link rel="stylesheet" href="../assets/css/admin.css">
<link rel="stylesheet" href="../assets/css/responsive.css">

<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.7.2/css/all.min.css">

</head>

<body>

<?php include "../includes/admin_sidebar.php"; ?>

<div class="sidebar-overlay"></div>

<?php include "../includes/admin_topbar.php"; ?>

<div class="main-content">

<div class="page-header">

<h2>

<i class="fas fa-layer-group"></i>

<?= htmlspecialchars($plan['plan_name']); ?>

</h2>

<p>

Monthly contribution monitoring dashboard

</p>

</div>


<!-- FILTER -->

<form method="GET" class="filter-bar">

<input
type="hidden"
name="plan"
value="<?= $planId; ?>">

<select name="month">

<?php

for($m=1;$m<=12;$m++){

?>

<option value="<?= $m; ?>" <?= $selectedMonth==$m?'selected':''; ?>>

<?= date("F",mktime(0,0,0,$m,1)); ?>

</option>

<?php } ?>

</select>

<select name="year">

<?php

$currentYear=date("Y");

for($y=$currentYear-2;$y<=$currentYear+3;$y++){

?>

<option value="<?= $y; ?>" <?= $selectedYear==$y?'selected':''; ?>>

<?= $y; ?>

</option>

<?php } ?>

</select>

<button type="submit">

<i class="fas fa-filter"></i>

Load

</button>

</form>



<div class="stats-grid">

<div class="stat-card">

<i class="fas fa-users"></i>

<h3><?= number_format($summary['members']); ?></h3>

<p>Members</p>

</div>


<div class="stat-card">

<i class="fas fa-handshake"></i>

<h3><?= number_format($summary['hands']); ?></h3>

<p>Hands</p>

</div>


<div class="stat-card">

<i class="fas fa-money-bill-wave"></i>

<h3>

₦<?= number_format($summary['collected'],2); ?>

</h3>

<p>Collected This Month</p>

</div>


<div class="stat-card">

<i class="fas fa-clock"></i>

<h3>

₦<?= number_format($summary['remaining'],2); ?>

</h3>

<p>Remaining This Month</p>

</div>

</div>



<div class="tables-card">

<div class="tables-header">

<h3>

<i class="fas fa-users"></i>

Scheme Members

</h3>

</div>

<div class="table-tools">

<input
type="text"
id="memberSearch"
placeholder="Search member...">

</div>

<div class="tables-responsive">

<table
class="admin-tables"
id="membersTable">

<thead>

<tr>

<th>Member</th>

<th>Hands</th>

<th>Monthly Expected</th>

<th>Paid</th>

<th>Remaining</th>

<th>Status</th>

<th>History</th>

</tr>

</thead>

<tbody>

<?php foreach($members as $member): ?>

<?php

$expected = $member['monthly_amount'] * $member['hands'];

$paid = $member['paid'] ?? 0;

$remaining = max(0, $expected - $paid);

if($remaining <= 0){

    $badge = "<span class='badge-paid'>Paid</span>";

}elseif($paid > 0){

    $badge = "<span class='badge-pending'>Part Payment</span>";

}else{

    $badge = "<span class='badge-overdue'>Unpaid</span>";

}

?>

<tr>

<td>

<strong><?= htmlspecialchars($member['fullname']); ?></strong>

<br>

<small><?= htmlspecialchars($member['phone']); ?></small>

</td>

<td>

<?= number_format($member['hands']); ?>

</td>

<td>

₦<?= number_format($expected,2); ?>

</td>

<td>

₦<?= number_format($paid,2); ?>

</td>

<td>

₦<?= number_format($remaining,2); ?>

</td>

<td>

<?= $badge; ?>

</td>

<td>

<a href="member_contributions.php?subscription=<?= $member['subscription_id']; ?>" class="success-btn">

<i class="fas fa-eye"></i>

</a>

</td>

</tr>

<?php endforeach; ?>

</tbody>
</table>

</div>

</div>

</div>

<script src="../assets/js/admin.js"></script>

<script>

const memberSearch=document.getElementById("memberSearch");

memberSearch.addEventListener("keyup",function(){

const value=this.value.toLowerCase();

document.querySelectorAll("#membersTable tbody tr").forEach(function(row){

row.style.display=row.innerText.toLowerCase().includes(value) ? "" : "none";

});

});

</script>

</body>

</html>