<?php
require 'includes/db_connect.php';

$q = $_GET['q'] ?? '';
$page = $_GET['page'] ?? 1;
$limit = 10;
$offset = ($page - 1) * $limit;

$search = '';
if($q != ''){
    $q_safe = $conn->real_escape_string($q);
    $search = "WHERE s.street_name LIKE '%$q_safe%'";
}

/* Get streets with latest renewal info */
$query = "
SELECT 
s.id,
s.street_name,
r.renewal_from,
r.renewal_to,
r.amount

FROM registered_streets s

LEFT JOIN renewal r 
ON r.id = (
    SELECT id 
    FROM renewal 
    WHERE street_id = s.id 
    ORDER BY renewal_to DESC 
    LIMIT 1
)

ORDER BY s.street_name ASC
LIMIT 10 OFFSET 0;
$search
GROUP BY s.id
ORDER BY s.street_name ASC
LIMIT $limit OFFSET $offset
";

$result = $conn->query($query);
if(!$result){ die("SQL Error: " . $conn->error); }

/* Total records for pagination */
$totalQuery = "SELECT COUNT(*) AS total FROM registered_streets s $search";
$totalRows = $conn->query($totalQuery)->fetch_assoc()['total'];
$totalPages = ceil($totalRows / $limit);
?>

<div class="table-responsive">
<table class="table table-bordered table-striped">
<thead class="table-light">
<tr>
    <th>#</th>
    <th>Street Name</th>
    <th>Renewal Period</th>
    <th>Last Amount Paid</th>
    <th>Status</th>
    <th>Action</th>
</tr>
</thead>
<tbody>
<?php
$count = $offset + 1;
$currentYear = date("Y");

while($row = $result->fetch_assoc()){
    $renewFrom = $row['renewal_from'];
    $renewTo   = $row['renewal_to'];
    $amount    = $row['amount'];

    echo "<tr>";
    echo "<td>".$count++."</td>";
    echo "<td>".$row['street_name']."</td>";

    // Renewal Period
    if($renewFrom){
        echo "<td>".$renewFrom." - ".$renewTo."</td>";
    }else{
        echo "<td><span class='text-danger'>Not Renewed</span></td>";
    }

    // Amount Paid
    if($amount){
        echo "<td>₦".number_format($amount)."</td>";
    }else{
        echo "<td>-</td>";
    }

    // Status Logic
    if(!$renewTo){
        $status = "<span class='badge bg-danger'>Not Renewed</span>";
    } elseif($renewTo < $currentYear){
        $status = "<span class='badge bg-warning'>Expired</span>";
    } else{
        $status = "<span class='badge bg-success'>Active</span>";
    }
    echo "<td>".$status."</td>";

    // Action
    echo "<td>
            <a href='street_renewal.php?id=".$row['id']."' class='btn btn-sm btn-warning'>Renew</a>
          </td>";
    echo "</tr>";
}
?>
</tbody>
</table>
</div>

<!-- Pagination -->
<nav>
<ul class="pagination justify-content-center mt-3">
<?php
for($i = 1; $i <= $totalPages; $i++){
    $active = ($i == $page) ? 'active' : '';
    echo "<li class='page-item $active'>
            <a href='#' class='page-link pagination-link' data-page='$i'>$i</a>
          </li>";
}
?>
</ul>
</nav>

<script>
// AJAX Search
document.getElementById('streetSearch').addEventListener('keyup', function(){
    let query = this.value;
    fetch("ajax_street_renewal_table.php?q=" + encodeURIComponent(query))
    .then(res => res.text())
    .then(data => document.getElementById('streetRenewalTable').innerHTML = data);
});

// AJAX Pagination
document.addEventListener("click", function(e){
    if(e.target.classList.contains("pagination-link")){
        e.preventDefault();
        let page = e.target.getAttribute("data-page");
        let q = document.getElementById("streetSearch").value;
        fetch("ajax_street_renewal_table.php?page=" + page + "&q=" + encodeURIComponent(q))
        .then(res => res.text())
        .then(data => document.getElementById("streetRenewalTable").innerHTML = data);
    }
});
</script>