<?php
include '../db_connection.php';

$con->set_charset("utf8mb4");

// Pagination setup
$limit = 10; // এক পেইজে কতটি রেকর্ড দেখাবেন
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Total rows count for pagination
$countResult = $con->query("
    SELECT COUNT(DISTINCT customer_id) as total FROM sales
");
$totalRows = $countResult->fetch_assoc()['total'];
$totalPages = ceil($totalRows / $limit);

$sql = "
SELECT 
    s.customer_id,
    s.customer_name,
    s.customer_phone,
    s.customer_address,
    SUM(s.grand_total) AS total_grand,
    SUM(s.discount) AS discount_grand,
    SUM(s.collected) AS collected_from_sales,
    IFNULL((
        SELECT SUM(dc.collected_amount)
        FROM due_collections dc
        WHERE dc.customer_id = s.customer_id
    ), 0) AS collected_from_due_collections,

    (SUM(s.collected) + IFNULL((
        SELECT SUM(dc.collected_amount)
        FROM due_collections dc
        WHERE dc.customer_id = s.customer_id
    ), 0)) AS total_collected_due,

    (
        (SUM(s.grand_total) - SUM(s.discount)) - 
        (SUM(s.collected) + IFNULL((
            SELECT SUM(dc.collected_amount)
            FROM due_collections dc
            WHERE dc.customer_id = s.customer_id
        ), 0))
    ) AS final_due

FROM sales s
GROUP BY 
    s.customer_id, s.customer_name, s.customer_phone, s.customer_address
HAVING final_due > 0
ORDER BY final_due DESC
LIMIT $limit OFFSET $offset
";


$result = $con->query($sql);
?>

<!DOCTYPE html>
<html>
<head>
    <title>Due Customer List</title>
    <?php include '../../sidebar.php'; ?>
    <style>
        table {
            border-collapse: collapse;
            width: 90%;
            margin: 20px auto;
        }
        th, td {
            border: 1px solid #888;
            padding: 8px;
            text-align: center;
        }
        th {
            background-color: #eee;
        }
        .pagination {
            display: flex;
            justify-content: center;
            list-style-type: none;
            padding: 0;
        }
        .pagination li {
            margin: 0 4px;
        }
        .pagination a {
            display: block;
            padding: 6px 12px;
            text-decoration: none;
            background: #f0f0f0;
            color: #333;
            border: 1px solid #ccc;
            border-radius: 4px;
        }
        .pagination .active a {
            background: #007bff;
            color: #fff;
            pointer-events: none;
        }
    </style>
</head>
<body>

<h2 style="text-align:center;">Due Customer List</h2>

<table>
    <thead>
        <tr>
            <th>Customer ID</th>
            <th>Name</th>
            <th>Phone</th>
            <th>Total Purchase</th>
            <th>Total Discount</th>
            <th>Total Paid</th>
            <th>Total Due</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody>
        <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?= htmlspecialchars($row['customer_id']) ?></td>
            <td><?= htmlspecialchars($row['customer_name']) ?></td>
            <td><?= htmlspecialchars($row['customer_phone']) ?></td>
            <td><?= number_format($row['total_grand'], 2) ?>৳</td>
            <td><?= number_format($row['discount_grand'], 2) ?>৳</td>
            <td><?= number_format($row['total_collected_due'], 2) ?>৳</td>
            <td><strong><?= number_format($row['final_due'], 2) ?>৳</strong></td>
            <td>
                <a href="receive_due.php?customer_id=<?= $row['customer_id'] ?>" class="btn btn-success btn-sm">Receive Due</a>
            </td>
        </tr>
        <?php endwhile; ?>
    </tbody>
</table>

<!-- Pagination Links -->
<ul class="pagination">
    <?php if ($page > 1): ?>
        <li><a href="?page=<?= $page - 1 ?>">« Prev</a></li>
    <?php endif; ?>

    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
        <li class="<?= $i == $page ? 'active' : '' ?>">
            <a href="?page=<?= $i ?>"><?= $i ?></a>
        </li>
    <?php endfor; ?>

    <?php if ($page < $totalPages): ?>
        <li><a href="?page=<?= $page + 1 ?>">Next »</a></li>
    <?php endif; ?>
</ul>
<div class="recent-sales mt-5">
            <?php 
                  include '../../footer.php';
            ?>
        </div>
</body>
</html>
