<?php
session_start();
include "dbconnect.php"; // Database Connection

// Define the number of records per page
$records_per_page = 10;

// Handle the search functionality
$search_passport_no = '';
if (isset($_POST['search'])) {
    $search_passport_no = $_POST['passport_no'];
}

// Handle pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $records_per_page;

// Handle Delete action
if (isset($_GET['delete'])) {
    $payment_id = $_GET['delete'];
    // Delete the record from the database
    $sql_delete = "DELETE FROM clientspayment WHERE id = '$payment_id'";
    if ($con->query($sql_delete) === TRUE) {
        echo "<script>alert('Payment record deleted successfully'); window.location.href = 'clientpaymentlist.php';</script>";
        exit();
    } else {
        echo "<script>alert('Error: " . $con->error . "'); window.location.href = 'clientpaymentlist.php';</script>";
        exit();
    }
}

// Fetch the total number of records for pagination
$sql_total = "SELECT COUNT(*) as total FROM clientspayment cp 
              JOIN job_applicants ja ON cp.applicant_id = ja.id";
if ($search_passport_no) {
    $sql_total .= " WHERE ja.passport_no LIKE '%$search_passport_no%'";
}
$result_total = $con->query($sql_total);
$total_rows = $result_total->fetch_assoc()['total'];
$total_pages = ceil($total_rows / $records_per_page);

// Fetch payment records with pagination and search
$sql = "SELECT cp.id, cp.applicant_id, cp.total_payment, cp.paid_amount, cp.pending_amount, cp.payment_date, cp.payment_message, cp.installment_number, cp.installment_due_date, ja.name, ja.passport_no, ja.position, ja.job_country, ja.reference
        FROM clientspayment cp
        JOIN job_applicants ja ON cp.applicant_id = ja.id";
if ($search_passport_no) {
    $sql .= " WHERE ja.passport_no LIKE '%$search_passport_no%'";
}
$sql .= " ORDER BY cp.payment_date DESC LIMIT $offset, $records_per_page";
$result = $con->query($sql);

// Export to Excel functionality
if (isset($_POST['export_excel'])) {
    header("Content-Type: application/xlsx");
    header("Content-Disposition: attachment; filename=payment_records.xlsx");

    $excel_output = "Applicant Name\tPassport No\tPosition\tJob Country\tReference\tTotal Payment\tPaid Amount\tPending Amount\tPayment Date\tInstallment Number\tInstallment Due Date\tPayment Message\n";
    while ($row = $result->fetch_assoc()) {
        // Sanitize tabs/newlines for Excel
        $clean = function($text) {
            return str_replace(["\t", "\n", "\r"], ' ', $text);
        };
        $excel_output .= 
            $clean($row['name']) . "\t" .
            $clean($row['passport_no']) . "\t" .
            $clean($row['position']) . "\t" .
            $clean($row['job_country']) . "\t" .
            $clean($row['reference']) . "\t" .
            $row['total_payment'] . "\t" .
            $row['paid_amount'] . "\t" .
            $row['pending_amount'] . "\t" .
            $row['payment_date'] . "\t" .
            $row['installment_number'] . "\t" .
            (isset($row['installment_due_date']) ? $row['installment_due_date'] : 'N/A') . "\t" .
            $clean($row['payment_message']) . "\n";
    }
    echo $excel_output;
    exit();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>Payment Client List</title>

<!-- Bootstrap CSS + Bootstrap Icons -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet" />
<link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css" rel="stylesheet" />

<style>
    body {
        font-family: 'Arial', sans-serif;
        background: linear-gradient(135deg, #74ebd5 0%, #ACB6E5 100%);
        color: #222;
        min-height: 100vh;
        padding-bottom: 50px;
    }
    .container {
        margin-top: 50px;
        background: white;
        padding: 30px;
        border-radius: 12px;
        box-shadow: 0 12px 24px rgba(0,0,0,0.15);
        max-width: 1200px;
    }
    h3 {
        color: #3a6ee8;
        text-align: center;
        margin-bottom: 30px;
        font-weight: 700;
        letter-spacing: 1px;
    }
    .table thead th {
        background-color: #3a6ee8;
        color: #fff;
        vertical-align: middle;
    }
    .table td, .table th {
        vertical-align: middle;
    }
    .btn {
        border-radius: 8px;
        font-weight: 600;
    }
    .btn-delete {
        background-color: #dc3545;
        border-color: #dc3545;
        color: white;
    }
    .btn-delete:hover {
        background-color: #b52a37;
        border-color: #b52a37;
        color: white;
    }
    .btn-primary {
        background-color: #3a6ee8;
        border-color: #3a6ee8;
        color: white;
    }
    .btn-primary:hover {
        background-color: #2b54b0;
        border-color: #2b54b0;
        color: white;
    }
    .logout-btn {
        position: fixed;
        top: 20px;
        right: 20px;
        background-color: #dc3545;
        color: white;
        padding: 10px 20px;
        border-radius: 30px;
        font-weight: 600;
        text-decoration: none;
        box-shadow: 0 4px 8px rgba(0,0,0,0.2);
        display: flex;
        align-items: center;
        gap: 6px;
        transition: background-color 0.3s ease;
        z-index: 1000;
    }
    .logout-btn:hover {
        background-color: #b52a37;
        color: white;
    }
    .table-responsive {
        overflow-x: auto;
    }
    .pagination {
        justify-content: center;
    }
    .page-link {
        color: #3a6ee8;
        font-weight: 600;
    }
    .page-link:hover {
        background-color: #3a6ee8;
        color: white;
    }
    .page-item.active .page-link {
        background-color: #3a6ee8;
        border-color: #3a6ee8;
        color: white;
    }
    .input-group-text {
        background-color: #3a6ee8;
        color: white;
        border-radius: 0.375rem 0 0 0.375rem;
        border-right: none;
    }
    .form-control:focus {
        border-color: #3a6ee8;
        box-shadow: 0 0 0 0.2rem rgba(58,110,232,.25);
    }
    .btn-success i, .btn-delete i, .btn-primary i {
        margin-right: 6px;
    }
</style>
</head>
<body>

<!-- Logout Button -->
<a href="logout.php" class="logout-btn" title="Logout"><i class="bi bi-box-arrow-right"></i> Logout</a>

<div class="container">
    <h3><i class="bi bi-list-check"></i> Payment Client List</h3>

    <!-- Search Form -->
    <form method="POST" action="clientpaymentlist.php" class="mb-3">
        <div class="row g-2">
            <div class="col-md-8 col-sm-12">
                <div class="input-group">
                    <span class="input-group-text"><i class="bi bi-search"></i></span>
                    <input type="text" class="form-control" name="passport_no" value="<?= htmlspecialchars($search_passport_no) ?>" placeholder="Search by Passport No" aria-label="Search by Passport No" />
                </div>
            </div>
            <div class="col-md-4 col-sm-12 d-grid">
                <button type="submit" class="btn btn-primary" name="search"><i class="bi bi-funnel-fill"></i> Search</button>
            </div>
        </div>
    </form>

    <!-- Export to Excel Button -->
    <form method="POST" class="mb-4">
        <button type="submit" name="export_excel" class="btn btn-success d-flex align-items-center justify-content-center" style="max-width: 220px;">
            <i class="bi bi-file-earmark-excel-fill"></i> Export to Excel
        </button>
    </form>

    <!-- Payment Table -->
    <div class="table-responsive">
        <table class="table table-bordered table-striped align-middle">
            <thead>
                <tr>
                    <th>Applicant Name</th>
                    <th>Passport No</th>
                    <th>Position</th>
                    <th>Job Country</th>
                    <th>Reference</th>
                    <th>Total Payment</th>
                    <th>Paid Amount</th>
                    <th>Pending Amount</th>
                    <th>Payment Date</th>
                    <th>Installment Number</th>
                    <th>Installment Due Date</th>
                    <th>Payment Message</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if ($result && $result->num_rows > 0): ?>
                    <?php while ($row = $result->fetch_assoc()) { ?>
                        <tr>
                            <td><?= htmlspecialchars($row['name']) ?></td>
                            <td><?= htmlspecialchars($row['passport_no']) ?></td>
                            <td><?= htmlspecialchars($row['position']) ?></td>
                            <td><?= htmlspecialchars($row['job_country']) ?></td>
                            <td><?= htmlspecialchars($row['reference']) ?></td>
                            <td><?= number_format($row['total_payment'], 2) ?></td>
                            <td><?= number_format($row['paid_amount'], 2) ?></td>
                            <td><?= number_format($row['pending_amount'], 2) ?></td>
                            <td><?= htmlspecialchars($row['payment_date']) ?></td>
                            <td><?= htmlspecialchars($row['installment_number']) ?></td>
                            <td><?= !empty($row['installment_due_date']) ? htmlspecialchars($row['installment_due_date']) : 'N/A'; ?></td>
                            <td><?= htmlspecialchars($row['payment_message']) ?></td>
                            <td>
                                <a href="clientpaymentedit.php?id=<?= $row['id'] ?>" class="btn btn-primary btn-sm me-1" title="Edit">
                                    <i class="bi bi-pencil-fill"></i> Edit
                                </a>
                                <a href="clientpaymentlist.php?delete=<?= $row['id'] ?>" class="btn btn-delete btn-sm" onclick="return confirm('Are you sure you want to delete this record?')" title="Delete">
                                    <i class="bi bi-trash-fill"></i> Delete
                                </a>
                            </td>
                        </tr>
                    <?php } ?>
                <?php else: ?>
                    <tr><td colspan="13" class="text-center">No records found.</td></tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>

    <!-- Pagination -->
    <nav aria-label="Page navigation">
        <ul class="pagination">
            <!-- Previous page -->
            <li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>">
                <a class="page-link" href="clientpaymentlist.php?page=<?= $page - 1 ?>" aria-label="Previous">
                    <span aria-hidden="true">&laquo;</span>
                </a>
            </li>

            <?php for ($p = 1; $p <= $total_pages; $p++) : ?>
                <li class="page-item <?= $p == $page ? 'active' : '' ?>">
                    <a class="page-link" href="clientpaymentlist.php?page=<?= $p ?>"><?= $p ?></a>
                </li>
            <?php endfor; ?>

            <!-- Next page -->
            <li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
                <a class="page-link" href="clientpaymentlist.php?page=<?= $page + 1 ?>" aria-label="Next">
                    <span aria-hidden="true">&raquo;</span>
                </a>
            </li>
        </ul>
    </nav>
</div>

<!-- Bootstrap JS Bundle with Popper -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>

</body>
</html>
