<?php
include('authentication.php');
include('includes/header.php');
include('includes/navbar.php');
include('dbcon.php');
?>

<div class="container-fluid">

     <!-- Date Filter Form -->
    <form method="GET" action="" class="mb-4">
        <div class="row ">
            <div class="col-md-3">
                <label for="start_date" class="form-label">Start Date</label>
                <input type="date" name="start_date" id="start_date" class="form-control" value="<?= isset($_GET['start_date']) ? $_GET['start_date'] : '' ?>">
            </div>
            <div class="col-md-3">
                <label for="end_date" class="form-label">End Date</label>
                <input type="date" name="end_date" id="end_date" class="form-control" value="<?= isset($_GET['end_date']) ? $_GET['end_date'] : '' ?>">
            </div>
            <div class="col-md-2">
                <button type="submit" class="btn btn-primary mt-5">Search</button>
            </div>
        </div>
    </form>

    <h1 class="h3 text-gray-800 text-center mb-4">Due Payments</h1>

    <div class="containers">
        <div class="card-body">
            <div class="table-responsive">

                <?php
                // Get filter values from GET request
                $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : '';
                $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : '';

                // Base query for fetching due payments
                $query = "
                    SELECT 
                        c.Id AS client_id,
                        c.cust_name,
                        c.service,
                        c.total_amount,
                        IFNULL(SUM(p.paid_amount), 0) AS total_paid,
                        (c.total_amount - IFNULL(SUM(p.paid_amount), 0)) AS due_amount,
                        MAX(p.due_date) AS due_date
                    FROM tbl_client c
                    LEFT JOIN tbl_payment p ON c.Id = p.client_Id
                ";

                // Add WHERE clause for date filters
                $conditions = [];
                if ($start_date) {
                    $conditions[] = "p.payment_date >= '$start_date'";
                }
                if ($end_date) {
                    $conditions[] = "p.payment_date <= '$end_date'";
                }

                // Add conditions to the query
                if (!empty($conditions)) {
                    $query .= " WHERE " . implode(" AND ", $conditions);
                }

                // Group by client to get the due payment details
                $query .= "
                    GROUP BY c.Id, c.cust_name, c.service, c.total_amount
                    HAVING due_amount > 0
                    ORDER BY p.due_date DESC
                ";

                // Execute the query
                $query_run = mysqli_query($conn, $query);

                // Initialize total due amount
                $total_due_amount = 0;
                ?>

                <table class="table table-bordered text-center">
                    <thead class="">
                        <tr>
                            <th>Sr. No.</th>
                            <th>Client Name</th>
                            <th>Service</th>
                            <th>Total Amount</th>
                            <th>Total Paid</th>
                            <th>Due Amount</th>
                            <th>Last Due Date</th>
                            <th>Action</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $serial_no = 1;
                        if (mysqli_num_rows($query_run) > 0) {
                            while ($row = mysqli_fetch_assoc($query_run)) {
                                // Add the due amount to the total
                                $total_due_amount += $row['due_amount'];
                                ?>
                                <tr>
                                    <td><?= $serial_no++; ?></td>
                                    <td><?= $row['cust_name']; ?></td>
                                    <td><?= $row['service']; ?></td>
                                    <td><?= $row['total_amount']; ?></td>
                                    <td><?= $row['total_paid']; ?></td>
                                    <td><?= $row['due_amount']; ?></td>
                                    <td><?= $row['due_date']; ?></td>
                                    <td>
                                        <button onclick="window.location.href='update-payment.php?id=<?= $row['client_id']; ?>'" class="btn btn-sm btn-dark">Update</button>
                                    </td>
                                </tr>
                                <?php
                            }
                        } else {
                            echo "<tr><td colspan='7'>No Due Payments Found</td></tr>";
                        }
                        ?>
                    </tbody>
                </table>
                <!-- Total Due Amount at the bottom -->
                    <div class="">
                        <strong>Total Due Amount:</strong> <?= number_format($total_due_amount, 2); ?>
                    </div>
            </div>
        </div>
    </div>

</div>

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