<?php
/**
 * pages/dashboard.php — Main dashboard
 * Super admin: sees totals across all agencies.
 * Agency user: sees totals scoped to 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_once __DIR__ . '/../includes/audit.php';

require_login();

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

// ── Stat queries ──────────────────────────────────────────────────────────────

if ($is_super) {
    // Totals across the entire system
    $total_agencies     = db()->query('SELECT COUNT(*) FROM agencies WHERE active = 1')->fetchColumn();
    $total_users        = db()->query('SELECT COUNT(*) FROM users WHERE active = 1')->fetchColumn();
    $total_learners     = db()->query('SELECT COUNT(*) FROM learners WHERE active = 1')->fetchColumn();
    $total_certificates = db()->query('SELECT COUNT(*) FROM certificates')->fetchColumn();

    // Certificates issued this month
    $certs_this_month = db()->query(
        "SELECT COUNT(*) FROM certificates WHERE issued_date >= DATE_FORMAT(NOW(),'%Y-%m-01')"
    )->fetchColumn();

    // Recent certificates
    $recent_certs = db()->query(
        'SELECT c.id, c.certificate_number, c.issued_date,
                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
          ORDER BY c.issued_date DESC
          LIMIT 8'
    )->fetchAll();

    // Recent agencies
    $recent_agencies = db()->query(
        'SELECT id, name, created_at FROM agencies ORDER BY created_at DESC LIMIT 5'
    )->fetchAll();

} else {
    // Scoped to this user's agency
    $total_agencies     = 1;
    $total_users        = null; // not shown for agency users

    $stmt = db()->prepare('SELECT COUNT(*) FROM learners WHERE agency_id = ? AND active = 1');
    $stmt->execute([$agency_id]);
    $total_learners = $stmt->fetchColumn();

    $stmt = db()->prepare(
        'SELECT COUNT(*) FROM certificates c JOIN learners l ON l.id = c.learner_id WHERE l.agency_id = ?'
    );
    $stmt->execute([$agency_id]);
    $total_certificates = $stmt->fetchColumn();

    $stmt = db()->prepare(
        "SELECT COUNT(*) FROM certificates c
           JOIN learners l ON l.id = c.learner_id
          WHERE l.agency_id = ? AND c.issued_date >= DATE_FORMAT(NOW(),'%Y-%m-01')"
    );
    $stmt->execute([$agency_id]);
    $certs_this_month = $stmt->fetchColumn();

    $stmt = db()->prepare(
        'SELECT c.id, c.certificate_number, c.issued_date,
                l.first_name, l.last_name
           FROM certificates c
           JOIN learners l ON l.id = c.learner_id
          WHERE l.agency_id = ?
          ORDER BY c.issued_date DESC
          LIMIT 8'
    );
    $stmt->execute([$agency_id]);
    $recent_certs = $stmt->fetchAll();

    // Agency name
    $stmt = db()->prepare('SELECT name FROM agencies WHERE id = ?');
    $stmt->execute([$agency_id]);
    $agency_name = $stmt->fetchColumn() ?: 'Your Agency';
}

$page_title  = 'Dashboard';
$active_page = 'dashboard';
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;">

            <!-- Page header -->
            <div class="page-header d-flex align-items-center justify-content-between mb-4">
                <div>
                    <h4 class="fw-bold mb-1">
                        <?= $is_super ? 'System Dashboard' : 'Dashboard — ' . e($agency_name) ?>
                    </h4>
                    <p class="text-muted mb-0">Overview of training activity</p>
                </div>
                <div>
                    <span class="text-muted small"><?= date('l, F j, Y') ?></span>
                </div>
            </div>

            <?php render_flash(); ?>

            <!-- ── Stat Cards ─────────────────────────────────────────────────── -->
            <div class="row g-3 mb-4">

                <?php if ($is_super): ?>
                <!-- Agencies -->
                <div class="col-sm-6 col-xl-3">
                    <div class="card border-0 shadow-sm h-100">
                        <div class="card-body d-flex align-items-center gap-3">
                            <div class="rounded-circle d-flex align-items-center justify-content-center"
                                 style="width:56px;height:56px;background:rgba(59,93,231,.12);">
                                <i class="feather-briefcase" style="color:#3b5de7;"></i>
                            </div>
                            <div>
                                <div class="h3 fw-bold mb-0"><?= (int)$total_agencies ?></div>
                                <div class="text-muted small">Active Agencies</div>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Users -->
                <div class="col-sm-6 col-xl-3">
                    <div class="card border-0 shadow-sm h-100">
                        <div class="card-body d-flex align-items-center gap-3">
                            <div class="rounded-circle d-flex align-items-center justify-content-center"
                                 style="width:56px;height:56px;background:rgba(69,202,252,.12);">
                                <i class="feather-users" style="color:#45cafc;"></i>
                            </div>
                            <div>
                                <div class="h3 fw-bold mb-0"><?= (int)$total_users ?></div>
                                <div class="text-muted small">Active Users</div>
                            </div>
                        </div>
                    </div>
                </div>
                <?php endif; ?>

                <!-- Learners -->
                <div class="col-sm-6 col-xl-<?= $is_super ? '3' : '4' ?>">
                    <div class="card border-0 shadow-sm h-100">
                        <div class="card-body d-flex align-items-center gap-3">
                            <div class="rounded-circle d-flex align-items-center justify-content-center"
                                 style="width:56px;height:56px;background:rgba(40,199,111,.12);">
                                <i class="feather-book-open" style="color:#28c76f;"></i>
                            </div>
                            <div>
                                <div class="h3 fw-bold mb-0"><?= (int)$total_learners ?></div>
                                <div class="text-muted small">Active Learners</div>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Total Certificates -->
                <div class="col-sm-6 col-xl-<?= $is_super ? '3' : '4' ?>">
                    <div class="card border-0 shadow-sm h-100">
                        <div class="card-body d-flex align-items-center gap-3">
                            <div class="rounded-circle d-flex align-items-center justify-content-center"
                                 style="width:56px;height:56px;background:rgba(255,159,67,.12);">
                                <i class="feather-award" style="color:#ff9f43;"></i>
                            </div>
                            <div>
                                <div class="h3 fw-bold mb-0"><?= (int)$total_certificates ?></div>
                                <div class="text-muted small">Certificates Issued</div>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- This month -->
                <div class="col-sm-6 col-xl-<?= $is_super ? '3' : '4' ?>">
                    <div class="card border-0 shadow-sm h-100">
                        <div class="card-body d-flex align-items-center gap-3">
                            <div class="rounded-circle d-flex align-items-center justify-content-center"
                                 style="width:56px;height:56px;background:rgba(234,84,85,.12);">
                                <i class="feather-calendar" style="color:#ea5455;"></i>
                            </div>
                            <div>
                                <div class="h3 fw-bold mb-0"><?= (int)$certs_this_month ?></div>
                                <div class="text-muted small">Certs This Month</div>
                            </div>
                        </div>
                    </div>
                </div>

            </div><!-- /.row stat cards -->

            <!-- ── Recent Certificates ────────────────────────────────────────── -->
            <div class="card border-0 shadow-sm mb-4">
                <div class="card-header bg-white d-flex align-items-center justify-content-between py-3">
                    <h6 class="fw-bold mb-0">Recent Certificates</h6>
                    <a href="<?= base_url('pages/certificates.php') ?>" class="btn btn-sm btn-outline-primary">
                        View All
                    </a>
                </div>
                <div class="card-body p-0">
                    <?php if (empty($recent_certs)): ?>
                    <div class="text-center text-muted py-5">No certificates issued yet.</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>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($recent_certs as $cert): ?>
                                <tr>
                                    <td class="fw-semibold"><?= e($cert['certificate_number']) ?></td>
                                    <td><?= e($cert['first_name'] . ' ' . $cert['last_name']) ?></td>
                                    <?php if ($is_super): ?>
                                    <td><?= e($cert['agency_name']) ?></td>
                                    <?php endif; ?>
                                    <td><?= fmt_date($cert['issued_date']) ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                    <?php endif; ?>
                </div>
            </div>

            <?php if ($is_super && !empty($recent_agencies)): ?>
            <!-- ── Recent Agencies (super admin only) ────────────────────────── -->
            <div class="card border-0 shadow-sm mb-4">
                <div class="card-header bg-white d-flex align-items-center justify-content-between py-3">
                    <h6 class="fw-bold mb-0">Recently Added Agencies</h6>
                    <a href="<?= base_url('pages/agencies.php') ?>" class="btn btn-sm btn-outline-primary">
                        Manage Agencies
                    </a>
                </div>
                <div class="card-body p-0">
                    <div class="table-responsive">
                        <table class="table table-hover align-middle mb-0">
                            <thead class="table-light">
                                <tr>
                                    <th>Agency Name</th>
                                    <th>Added</th>
                                    <th></th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($recent_agencies as $ag): ?>
                                <tr>
                                    <td class="fw-semibold"><?= e($ag['name']) ?></td>
                                    <td><?= fmt_date($ag['created_at']) ?></td>
                                    <td class="text-end">
                                        <a href="<?= base_url('pages/agency-form.php?id=' . $ag['id']) ?>"
                                           class="btn btn-sm btn-outline-secondary">Edit</a>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            <?php endif; ?>

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