<?php
/**
 * pages/certificates.php — Certificate list
 * Super admin sees all. Agency user sees only their agency.
 */
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/functions.php';
require_once __DIR__ . '/../includes/auth.php';

require_login();

$is_super  = is_super_admin();
$agency_id = current_user_agency_id();

$search    = get_search();
$page_num  = get_current_page();
$per_page  = 25;
$filter_ag = $is_super ? (isset($_GET['agency_id']) && ctype_digit($_GET['agency_id']) ? (int)$_GET['agency_id'] : 0) : (int)$agency_id;

// Optional filter: single learner (linked from learners page)
$filter_learner_id = isset($_GET['learner_id']) && ctype_digit($_GET['learner_id']) ? (int)$_GET['learner_id'] : 0;

// Count
$count_sql  = 'SELECT COUNT(*) FROM certificates c JOIN learners l ON l.id = c.learner_id WHERE 1=1';
$count_args = [];

if (!$is_super) {
    $count_sql   .= ' AND l.agency_id = ?';
    $count_args[] = $agency_id;
} elseif ($filter_ag) {
    $count_sql   .= ' AND l.agency_id = ?';
    $count_args[] = $filter_ag;
}
if ($filter_learner_id) {
    $count_sql   .= ' AND c.learner_id = ?';
    $count_args[] = $filter_learner_id;
}
if ($search !== '') {
    $count_sql   .= ' AND (c.certificate_number LIKE ? OR l.first_name LIKE ? OR l.last_name LIKE ?)';
    $count_args[] = "%$search%";
    $count_args[] = "%$search%";
    $count_args[] = "%$search%";
}
$count_stmt = db()->prepare($count_sql);
$count_stmt->execute($count_args);
$total = (int) $count_stmt->fetchColumn();

$pager = paginate($total, $per_page, $page_num);

// Fetch
$sql  = 'SELECT c.id, c.certificate_number, c.issued_date, c.expiry_date, c.notes,
                l.id AS learner_id, l.first_name, l.last_name,
                a.name AS agency_name
           FROM certificates c
           JOIN learners l ON l.id = c.learner_id
           JOIN agencies  a ON a.id = l.agency_id
          WHERE 1=1';
$args = [];

if (!$is_super) {
    $sql    .= ' AND l.agency_id = ?';
    $args[]  = $agency_id;
} elseif ($filter_ag) {
    $sql    .= ' AND l.agency_id = ?';
    $args[]  = $filter_ag;
}
if ($filter_learner_id) {
    $sql    .= ' AND c.learner_id = ?';
    $args[]  = $filter_learner_id;
}
if ($search !== '') {
    $sql    .= ' AND (c.certificate_number LIKE ? OR l.first_name LIKE ? OR l.last_name LIKE ?)';
    $args[]  = "%$search%";
    $args[]  = "%$search%";
    $args[]  = "%$search%";
}
$sql .= ' ORDER BY c.issued_date DESC LIMIT ' . $per_page . ' OFFSET ' . $pager['offset'];

$stmt = db()->prepare($sql);
$stmt->execute($args);
$certs = $stmt->fetchAll();

// Agency list for filter dropdown
if ($is_super) {
    $agencies = db()->query('SELECT id, name FROM agencies WHERE active = 1 ORDER BY name')->fetchAll();
}

$page_title  = 'Certificates';
$active_page = 'certificates';
require_once __DIR__ . '/../templates/head.php';
?>

<?php require_once __DIR__ . '/../templates/header.php'; ?>
<?php require_once __DIR__ . '/../templates/sidebar.php'; ?>

<div class="nxl-everything">
    <div class="nxl-container">
        <div class="main-content" style="padding-top: 80px;">

            <div class="page-header d-flex flex-wrap align-items-center justify-content-between gap-3 mb-4">
                <div>
                    <h4 class="fw-bold mb-1">Certificates</h4>
                    <p class="text-muted mb-0"><?= number_format($total) ?> certificate<?= $total !== 1 ? 's' : '' ?></p>
                </div>
            </div>

            <?php render_flash(); ?>

            <!-- Filters -->
            <div class="card border-0 shadow-sm mb-3">
                <div class="card-body py-2">
                    <form method="GET" action="" class="d-flex flex-wrap gap-2 align-items-center">
                        <input type="search" name="q" class="form-control" style="max-width:280px;"
                               placeholder="Search certificate # or learner…" value="<?= e($search) ?>">
                        <?php if ($is_super): ?>
                        <select name="agency_id" class="form-control" style="max-width:220px;">
                            <option value="">All Agencies</option>
                            <?php foreach ($agencies as $ag): ?>
                            <option value="<?= $ag['id'] ?>" <?= $filter_ag === $ag['id'] ? 'selected' : '' ?>>
                                <?= e($ag['name']) ?>
                            </option>
                            <?php endforeach; ?>
                        </select>
                        <?php endif; ?>
                        <button type="submit" class="btn btn-outline-primary">Filter</button>
                        <?php if ($search || $filter_ag || $filter_learner_id): ?>
                        <a href="certificates.php" class="btn btn-outline-secondary">Clear</a>
                        <?php endif; ?>
                    </form>
                </div>
            </div>

            <!-- Table -->
            <div class="card border-0 shadow-sm">
                <div class="card-body p-0">
                    <?php if (empty($certs)): ?>
                    <div class="text-center text-muted py-5">No certificates found.</div>
                    <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-hover align-middle mb-0">
                            <thead class="table-light">
                                <tr>
                                    <th>Certificate #</th>
                                    <th>Learner</th>
                                    <?php if ($is_super): ?><th>Agency</th><?php endif; ?>
                                    <th>Issued</th>
                                    <th>Expires</th>
                                    <th>Notes</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($certs as $c): ?>
                                <tr>
                                    <td class="fw-semibold text-primary"><?= e($c['certificate_number']) ?></td>
                                    <td><?= e($c['first_name'] . ' ' . $c['last_name']) ?></td>
                                    <?php if ($is_super): ?>
                                    <td><?= e($c['agency_name']) ?></td>
                                    <?php endif; ?>
                                    <td><?= fmt_date($c['issued_date']) ?></td>
                                    <td>
                                        <?php if ($c['expiry_date']): ?>
                                            <?php
                                            $expired = strtotime($c['expiry_date']) < time();
                                            $cls     = $expired ? 'text-danger fw-semibold' : 'text-success';
                                            ?>
                                            <span class="<?= $cls ?>"><?= fmt_date($c['expiry_date']) ?></span>
                                        <?php else: ?>
                                            <span class="text-muted">No expiry</span>
                                        <?php endif; ?>
                                    </td>
                                    <td class="text-muted small">
                                        <?= $c['notes'] ? e(mb_substr($c['notes'], 0, 60)) . (mb_strlen($c['notes']) > 60 ? '…' : '') : '—' ?>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Pagination -->
                    <?php if ($pager['total_pages'] > 1): ?>
                    <div class="d-flex justify-content-between align-items-center px-3 py-2 border-top">
                        <small class="text-muted">Page <?= $pager['current'] ?> of <?= $pager['total_pages'] ?></small>
                        <div class="d-flex gap-1">
                            <?php if ($pager['has_prev']): ?>
                            <a href="?q=<?= urlencode($search) ?>&agency_id=<?= $filter_ag ?>&page=<?= $pager['current'] - 1 ?>"
                               class="btn btn-sm btn-outline-secondary">&laquo; Prev</a>
                            <?php endif; ?>
                            <?php if ($pager['has_next']): ?>
                            <a href="?q=<?= urlencode($search) ?>&agency_id=<?= $filter_ag ?>&page=<?= $pager['current'] + 1 ?>"
                               class="btn btn-sm btn-outline-secondary">Next &raquo;</a>
                            <?php endif; ?>
                        </div>
                    </div>
                    <?php endif; ?>
                    <?php endif; ?>
                </div>
            </div>

<?php require_once __DIR__ . '/../templates/footer.php'; ?>
