<?php
/**
 * ESO Personnel Analytics — Employee Profile Cards
 * City of Cincinnati themed, searchable, filterable
 */
$db = getDB();

// Get all personnel
$personnel = $db->query("SELECT * FROM eso_personnel ORDER BY last_name, first_name")->fetchAll(PDO::FETCH_ASSOC);
$totalCount = count($personnel);

// Stats
$avgRuns = $db->query("SELECT AVG(incident_count_ytd) AS avg_runs FROM eso_personnel WHERE incident_count_ytd > 0")->fetch()['avg_runs'] ?? 0;
$medianRuns = $db->query("SELECT incident_count_ytd FROM eso_personnel WHERE incident_count_ytd > 0 ORDER BY incident_count_ytd LIMIT 1 OFFSET " . max(0, (int)(($db->query("SELECT COUNT(*) FROM eso_personnel WHERE incident_count_ytd > 0")->fetchColumn() ?? 0) / 2)))->fetchColumn() ?: $avgRuns;
$certBreakdown = $db->query("SELECT certification, COUNT(*) AS cnt FROM eso_personnel WHERE certification != '' GROUP BY certification ORDER BY cnt DESC")->fetchAll(PDO::FETCH_ASSOC);
$topRunners = $db->query("SELECT full_name, incident_count_ytd, primary_unit, certification FROM eso_personnel WHERE incident_count_ytd > 0 ORDER BY incident_count_ytd DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);

// Unique values for filters
$units = $db->query("SELECT DISTINCT primary_unit FROM eso_personnel WHERE primary_unit != '' ORDER BY primary_unit")->fetchAll(PDO::FETCH_COLUMN);
$certs = $db->query("SELECT DISTINCT certification FROM eso_personnel WHERE certification != '' ORDER BY certification")->fetchAll(PDO::FETCH_COLUMN);
?>

<style>
.personnel-page { max-width: 1400px; }
.personnel-header {
    background: linear-gradient(135deg, #0B2240 0%, #163158 50%, #1B6CB0 100%);
    color: #fff; padding: 20px 28px; border-radius: var(--ios-radius); margin-bottom: 16px;
    display: flex; align-items: center; justify-content: space-between;
}
.personnel-header h1 { font-size: 22px; font-weight: 800; }
.personnel-header .sub { font-size: 13px; color: #8EAEC4; margin-top: 2px; }

/* Search + Filters */
.filters { display: flex; gap: 10px; margin-bottom: 16px; flex-wrap: wrap; align-items: center; }
.filter-search {
    flex: 1; min-width: 200px; padding: 10px 14px 10px 36px;
    border: 1px solid var(--ios-border); border-radius: var(--ios-radius-sm);
    font-size: 14px; background: var(--ios-card); color: var(--ios-text);
    background-image: url("data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='16' height='16' fill='none' viewBox='0 0 16 16'%3E%3Ccircle cx='7' cy='7' r='5' stroke='%238E99A8' stroke-width='1.5'/%3E%3Cpath d='M11 11l3 3' stroke='%238E99A8' stroke-width='1.5' stroke-linecap='round'/%3E%3C/svg%3E");
    background-repeat: no-repeat; background-position: 12px center;
}
.filter-search:focus { outline: none; border-color: var(--cfd-blue); box-shadow: 0 0 0 3px rgba(27,108,176,0.15); }
.filter-select {
    padding: 10px 12px; border: 1px solid var(--ios-border); border-radius: var(--ios-radius-sm);
    font-size: 13px; background: var(--ios-card); color: var(--ios-text); min-width: 140px;
}
.filter-count { font-size: 13px; color: var(--ios-tertiary); font-weight: 600; }

/* Card grid */
.card-grid {
    display: grid; grid-template-columns: repeat(auto-fill, minmax(280px, 1fr));
    gap: 14px; margin-bottom: 20px;
}

/* Employee card — 3D flip */
.emp-card {
    perspective: 800px; cursor: pointer; height: 220px;
}
.emp-card-inner {
    position: relative; width: 100%; height: 100%;
    transform-style: preserve-3d; transition: transform 0.6s cubic-bezier(0.4, 0, 0.2, 1);
}
.emp-card.flipped .emp-card-inner { transform: rotateY(180deg); }
.emp-card-front, .emp-card-back {
    position: absolute; top: 0; left: 0; right: 0; bottom: 0;
    backface-visibility: hidden; -webkit-backface-visibility: hidden;
    background: var(--ios-card); border-radius: var(--ios-radius); padding: 20px;
    box-shadow: var(--ios-shadow); overflow: hidden;
}
.emp-card-back { transform: rotateY(180deg); }
.emp-card:hover .emp-card-inner { box-shadow: var(--ios-shadow-lg); }
.emp-card-front::before, .emp-card-back::before {
    content: ''; position: absolute; top: 0; left: 0; right: 0; height: 4px;
}
.emp-card.cert-paramedic .emp-card-front::before { background: linear-gradient(90deg, #C5A55A, #DAC07A); }
.emp-card.cert-emt .emp-card-front::before { background: linear-gradient(90deg, #1B6CB0, #5BA4D9); }
.emp-card.cert-other .emp-card-front::before { background: linear-gradient(90deg, #8E99A8, #B8C1CC); }
.emp-card-back::before { background: linear-gradient(90deg, #E74C3C, #C0392B) !important; }
.flip-hint { position: absolute; bottom: 6px; right: 10px; font-size: 9px; color: var(--ios-tertiary); opacity: 0.6; }
.fire-badge { display: inline-block; background: #FDE8E8; color: #C0392B; font-size: 9px; font-weight: 700; padding: 2px 8px; border-radius: 10px; text-transform: uppercase; }

.emp-card-top { display: flex; align-items: center; gap: 14px; margin-bottom: 12px; }
.emp-avatar {
    width: 48px; height: 48px; border-radius: 50%;
    display: flex; align-items: center; justify-content: center;
    font-size: 16px; font-weight: 800; color: #fff; flex-shrink: 0;
}
.emp-avatar.paramedic { background: linear-gradient(135deg, #C5A55A, #A08030); }
.emp-avatar.emt { background: linear-gradient(135deg, #1B6CB0, #0B2240); }
.emp-avatar.other { background: linear-gradient(135deg, #8E99A8, #5A6A7E); }

.emp-name { font-size: 16px; font-weight: 700; color: var(--ios-text); line-height: 1.2; }
.emp-cert-badge {
    display: inline-block; font-size: 9px; font-weight: 700; padding: 2px 8px;
    border-radius: 10px; text-transform: uppercase; letter-spacing: 0.5px; margin-top: 4px;
}
.emp-cert-badge.paramedic { background: #FDF2D0; color: #8B6914; }
.emp-cert-badge.emt { background: #DBEAFE; color: #1B6CB0; }
.emp-cert-badge.other { background: #E8EDF3; color: #5A6A7E; }

.emp-stats { display: grid; grid-template-columns: 1fr 1fr; gap: 8px; }
.emp-stat { padding: 6px 0; }
.emp-stat-value { font-size: 15px; font-weight: 700; color: var(--ios-text); }
.emp-stat-label { font-size: 9px; text-transform: uppercase; letter-spacing: 0.5px; color: var(--ios-tertiary); }

.emp-workload { margin-top: 10px; }
.emp-workload-bar { height: 6px; background: var(--ios-border); border-radius: 3px; overflow: hidden; }
.emp-workload-fill { height: 100%; border-radius: 3px; transition: width 0.4s ease; }
.emp-workload-label { font-size: 9px; color: var(--ios-tertiary); margin-top: 3px; display: flex; justify-content: space-between; }

.emp-unit-badge {
    display: inline-block; background: var(--ios-bg); padding: 2px 8px; border-radius: 8px;
    font-size: 11px; font-weight: 600; color: var(--ios-text); margin-top: 4px;
}

@media (max-width: 768px) { .card-grid { grid-template-columns: 1fr; } .filters { flex-direction: column; } }
</style>

<div class="personnel-page">
    <a href="dashboard.php?page=eso" style="font-size:13px;color:var(--ios-blue);text-decoration:none;display:inline-flex;align-items:center;gap:4px;margin-bottom:12px;">&larr; ESO Overview</a>

    <div class="personnel-header">
        <div>
            <h1>Personnel Analytics</h1>
            <div class="sub"><?= $totalCount ?> employees &bull; 2026 YTD incident data &bull; Cincinnati Fire Department</div>
        </div>
        <div style="text-align:right;">
            <div style="font-size:28px;font-weight:800;"><?= round($avgRuns) ?></div>
            <div style="font-size:10px;color:#8EAEC4;text-transform:uppercase;">Avg Runs/Person</div>
        </div>
    </div>

    <!-- Search + Filters -->
    <div class="filters">
        <input type="search" id="searchInput" class="filter-search" placeholder="Search by name..." autocomplete="off">
        <select id="filterCert" class="filter-select">
            <option value="">All Certifications</option>
            <?php foreach ($certs as $c): ?>
                <option value="<?= htmlspecialchars($c) ?>"><?= htmlspecialchars($c) ?></option>
            <?php endforeach; ?>
        </select>
        <select id="filterUnit" class="filter-select">
            <option value="">All Units</option>
            <?php foreach ($units as $u): ?>
                <option value="<?= htmlspecialchars($u) ?>"><?= htmlspecialchars($u) ?></option>
            <?php endforeach; ?>
        </select>
        <select id="sortBy" class="filter-select">
            <option value="name">Sort: Name</option>
            <option value="runs-desc">Sort: Most Runs</option>
            <option value="runs-asc">Sort: Fewest Runs</option>
            <option value="contacts-desc">Sort: Most Pt Contacts</option>
        </select>
        <span class="filter-count" id="filterCount"><?= $totalCount ?> shown</span>
    </div>

    <!-- Employee Cards -->
    <div class="card-grid" id="cardGrid">
        <?php foreach ($personnel as $p):
            $fn = $p['first_name'] ?? '';
            $ln = $p['last_name'] ?? '';
            $initials = strtoupper(substr($fn, 0, 1) . substr($ln, 0, 1));
            $cert = $p['certification'] ?? '';
            $certClass = '';
            if (stripos($cert, 'paramedic') !== false) $certClass = 'paramedic';
            elseif (stripos($cert, 'emt') !== false || stripos($cert, 'emergency medical') !== false) $certClass = 'emt';
            else $certClass = 'other';

            $runs = (int)($p['incident_count_ytd'] ?? 0);
            $ptContacts = (int)($p['patient_contacts_ytd'] ?? 0);
            $workloadPct = $medianRuns > 0 ? min(round(($runs / ($medianRuns * 2)) * 100), 100) : 0;
            $workloadColor = $workloadPct >= 80 ? '#E74C3C' : ($workloadPct >= 50 ? '#8CC63F' : '#3498DB');

            $unit = $p['primary_unit'] ?? '';
        ?>
        <?php
            $fireInc = (int)($p['fire_incidents_ytd'] ?? 0);
            $firePtC = (int)($p['fire_pt_contacts_ytd'] ?? 0);
        ?>
        <div class="emp-card cert-<?= $certClass ?>"
             data-name="<?= htmlspecialchars(strtolower($fn . ' ' . $ln)) ?>"
             data-cert="<?= htmlspecialchars($cert) ?>"
             data-unit="<?= htmlspecialchars($unit) ?>"
             data-runs="<?= $runs ?>"
             data-contacts="<?= $ptContacts ?>"
             onclick="this.classList.toggle('flipped')">
          <div class="emp-card-inner">
            <!-- FRONT: EMS Stats -->
            <div class="emp-card-front">
                <div class="emp-card-top">
                    <div class="emp-avatar <?= $certClass ?>"><?= $initials ?></div>
                    <div>
                        <div class="emp-name"><?= htmlspecialchars($fn . ' ' . $ln) ?></div>
                        <?php if ($cert): ?>
                            <span class="emp-cert-badge <?= $certClass ?>"><?= htmlspecialchars($cert) ?></span>
                        <?php endif; ?>
                    </div>
                </div>
                <div class="emp-stats">
                    <div class="emp-stat">
                        <div class="emp-stat-value"><?= $runs ?></div>
                        <div class="emp-stat-label">EMS Incidents</div>
                    </div>
                    <div class="emp-stat">
                        <div class="emp-stat-value"><?= $ptContacts ?></div>
                        <div class="emp-stat-label">Pt Contacts</div>
                    </div>
                </div>
                <?php if ($unit): ?>
                    <div style="margin-top:8px;">
                        <span class="emp-unit-badge"><?= htmlspecialchars($unit) ?></span>
                        <?php if ($p['shift_assignment']): ?>
                            <span class="emp-unit-badge"><?= htmlspecialchars($p['shift_assignment']) ?></span>
                        <?php endif; ?>
                    </div>
                <?php endif; ?>
                <div class="emp-workload">
                    <div class="emp-workload-bar">
                        <div class="emp-workload-fill" style="width:<?= $workloadPct ?>%;background:<?= $workloadColor ?>;"></div>
                    </div>
                    <div class="emp-workload-label"><span>EMS Workload</span><span><?= $workloadPct ?>%</span></div>
                </div>
                <span class="flip-hint">tap for fire stats &rarr;</span>
            </div>

            <!-- BACK: Fire Stats -->
            <div class="emp-card-back">
                <div class="emp-card-top">
                    <div class="emp-avatar" style="background:linear-gradient(135deg,#E74C3C,#7A1B1B);"><?= $initials ?></div>
                    <div>
                        <div class="emp-name"><?= htmlspecialchars($fn . ' ' . $ln) ?></div>
                        <span class="fire-badge">🔥 Fire Operations</span>
                    </div>
                </div>
                <div class="emp-stats">
                    <div class="emp-stat">
                        <div class="emp-stat-value" style="color:#E74C3C;"><?= $fireInc ?></div>
                        <div class="emp-stat-label">Fire Incidents</div>
                    </div>
                    <div class="emp-stat">
                        <div class="emp-stat-value" style="color:#C0392B;"><?= $firePtC ?></div>
                        <div class="emp-stat-label">Fire Pt Contacts</div>
                    </div>
                </div>
                <div class="emp-stats" style="margin-top:8px;">
                    <div class="emp-stat">
                        <div class="emp-stat-value"><?= $runs + $fireInc ?></div>
                        <div class="emp-stat-label">Total All Incidents</div>
                    </div>
                    <div class="emp-stat">
                        <div class="emp-stat-value"><?= $ptContacts + $firePtC ?></div>
                        <div class="emp-stat-label">Total Pt Contacts</div>
                    </div>
                </div>
                <span class="flip-hint">&larr; tap for EMS stats</span>
            </div>
          </div>
        </div>
        <?php endforeach; ?>
    </div>

    <div style="text-align:center;padding:12px;font-size:10px;color:var(--ios-tertiary);">
        Data Source: PM_PERSONS + EMS.EMS / Snowflake &bull; Cincinnati Fire Department &bull; Office of the Fire Chief
    </div>
</div>

<script>
const cards = document.querySelectorAll('.emp-card');
const searchInput = document.getElementById('searchInput');
const filterCert = document.getElementById('filterCert');
const filterUnit = document.getElementById('filterUnit');
const sortBy = document.getElementById('sortBy');
const filterCount = document.getElementById('filterCount');
const grid = document.getElementById('cardGrid');

function applyFilters() {
    const q = searchInput.value.toLowerCase().trim();
    const cert = filterCert.value;
    const unit = filterUnit.value;
    let visible = 0;

    cards.forEach(card => {
        const name = card.dataset.name;
        const cardCert = card.dataset.cert;
        const cardUnit = card.dataset.unit;

        const matchName = !q || name.includes(q);
        const matchCert = !cert || cardCert === cert;
        const matchUnit = !unit || cardUnit === unit;

        if (matchName && matchCert && matchUnit) {
            card.style.display = '';
            visible++;
        } else {
            card.style.display = 'none';
        }
    });
    filterCount.textContent = visible + ' shown';
}

function applySort() {
    const sortVal = sortBy.value;
    const arr = Array.from(cards);
    arr.sort((a, b) => {
        switch (sortVal) {
            case 'runs-desc': return (parseInt(b.dataset.runs) || 0) - (parseInt(a.dataset.runs) || 0);
            case 'runs-asc': return (parseInt(a.dataset.runs) || 0) - (parseInt(b.dataset.runs) || 0);
            case 'contacts-desc': return (parseInt(b.dataset.contacts) || 0) - (parseInt(a.dataset.contacts) || 0);
            default: return a.dataset.name.localeCompare(b.dataset.name);
        }
    });
    arr.forEach(card => grid.appendChild(card));
    applyFilters();
}

searchInput.addEventListener('input', applyFilters);
filterCert.addEventListener('change', applyFilters);
filterUnit.addEventListener('change', applyFilters);
sortBy.addEventListener('change', applySort);
</script>
