<?php include('inc/template.php'); ?>
<div class="container-fluid flex-grow-1 container-p-y">

    <!-- Filter Form -->
    <div class="row mb-4">
        <div class="col-xl-12">
            <div class="card mb-4">
                <form class="card-body" method="post">
                    <div class="row g-3">
						<div class="col-md-6">
      <label for="customer_id" class="form-label">Customer Name</label>
      
<select name="customer_id" id="customer_id"  class="select2 form-select form-select-lg">
  <option value="">Select Customer</option>
  <?php 
    $result = $conn->query("SELECT customers.id AS customer_id, customers.name, customers.phone FROM package_bookings pb JOIN customers ON pb.customer_id = customers.id ORDER BY pb.id ASC");

    while ($row = $result->fetch_assoc()) {
      $customerPhone = htmlspecialchars($row['phone']);
      $customerName = htmlspecialchars(ucwords(strtolower($row['name'])));
      echo '<option value="' . $row['customer_id'] . '">' . $customerName . ' [' . $customerPhone . ']</option>';
    }
  ?>
</select>

    </div>
                        <div class="col-md-3">
                            <label class="form-label">From Date</label>
                            <input type="date" name="fromdate" class="form-control">
                        </div>
                        <div class="col-md-3">
                            <label class="form-label">To Date</label>
                            <input type="date" name="todate" class="form-control">
                        </div>
                        <div class="col-md-3">
                            <label class="form-label">With GST</label>
                            <select name="gstinclude" class="form-select">
                                <option value="yes">Yes</option>
                                <option value="no">No</option>
                            </select>
                        </div>
                    </div>
                    <div class="pt-4">
                        <button type="submit" class="btn btn-primary me-2">Search</button>
                        <button type="reset" class="btn btn-label-secondary">Cancel</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Balance Sheet Table -->
    <div class="row">
        <div class="col-xl-12">
            <div class="mb-3">
                <button id="exportExcel" class="btn btn-success me-2">Export to Excel</button>
                <button id="exportPDF" class="btn btn-danger">Download PDF</button>
            </div>

            <div class="card">
                <div class="card-datatable table-responsive pt-0">
                   <?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $from = $_POST['fromdate'] ?? '';
    $to = $_POST['todate'] ?? '';
    $gst = $_POST['gstinclude'] ?? '';
    $customer_id = $_POST['customer_id'] ?? '';

    $where = "WHERE 1=1";

    if (!empty($from) && !empty($to)) {
        $where .= " AND payment_date BETWEEN '$from' AND '$to'";
    }

    if (!empty($gst)) {
        $where .= " AND gst_included = '$gst'";
    }

    if (!empty($customer_id)) {
        $where .= " AND customer_id = '$customer_id'";
    }

    $sql = "SELECT * FROM package_payments $where ORDER BY payment_date ASC, id ASC";
     $sql; // Remove or comment out in production

    $result = mysqli_query($conn, $sql);

    $balance = 0;
    $total_credit = 0;
    $total_debit = 0;
    ?>

    <table class="datatables-ajax table table-bordered table-responsive">
        <thead>
            <tr>
                <th>Date</th>
                <th>Details</th>
                <th>Payment Mode</th>
                <th>Debit</th>
                <th>Credit</th>
                <th>Balance</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($row = mysqli_fetch_assoc($result)) {
                $amount = $row['amount'];
                $credit = $row['type'] === 'credit' ? $amount : 0;
                $debit = $row['type'] === 'debit' ? $amount : 0;
                $balance += $credit - $debit;
                $total_credit += $credit;
                $total_debit += $debit;
            ?>
                <tr>
                    <td><?= date('d-m-Y', strtotime($row['payment_date'])); ?></td>
                    <td><?= htmlspecialchars($row['remarks'] ?? '-') ?></td>
                    <td><?= ucfirst($row['payment_mode']) ?></td>
                    <td><?= $debit > 0 ? '₹' . number_format($debit, 2) : '' ?></td>
                    <td><?= $credit > 0 ? '₹' . number_format($credit, 2) : '' ?></td>
                    <td>₹<?= number_format($balance, 2) ?></td>
                </tr>
            <?php } ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="3">Total</th>
                <th>₹<?= number_format($total_debit, 2) ?></th>
                <th>₹<?= number_format($total_credit, 2) ?></th>
                <th>₹<?= number_format($balance, 2) ?></th>
            </tr>
        </tfoot>
    </table>
<?php } ?>

                </div>
            </div>
        </div>
    </div>

</div>

<!-- DataTables + Export Buttons -->
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>

<script>
$(document).ready(function() {
    var table = $('#balanceTable').DataTable({
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excelHtml5',
                title: 'Balance_Sheet'
            },
            {
                extend: 'pdfHtml5',
                title: 'Balance_Sheet',
                orientation: 'landscape',
                pageSize: 'A4'
            }
        ]
    });

    $('#exportExcel').on('click', function () {
        table.button('.buttons-excel').trigger();
    });

    $('#exportPDF').on('click', function () {
        table.button('.buttons-pdf').trigger();
    });
});
</script>

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