<?php
// coordinator/lecturers.php
declare(strict_types=1);

require_once __DIR__ . '/../includes/coordinator_auth.php';

$db = null;
if (isset($conn) && $conn instanceof mysqli) {
    $db = $conn;
} elseif (isset($mysqli) && $mysqli instanceof mysqli) {
    $db = $mysqli;
}

if (!$db) {
    http_response_code(500);
    exit('Database connection not found.');
}

$db->set_charset('utf8mb4');

if (!function_exists('h')) {
    function h($value): string {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

function cl_table_exists(mysqli $db, string $table): bool {
    $safe = $db->real_escape_string($table);
    $res = $db->query("SHOW TABLES LIKE '{$safe}'");
    return $res && $res->num_rows > 0;
}

function cl_col_exists(mysqli $db, string $table, string $col): bool {
    $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    $safeCol = $db->real_escape_string($col);
    $res = $db->query("SHOW COLUMNS FROM `{$safeTable}` LIKE '{$safeCol}'");
    return $res && $res->num_rows > 0;
}

function cl_first_col(mysqli $db, string $table, array $cols): ?string {
    foreach ($cols as $col) {
        if (cl_col_exists($db, $table, $col)) {
            return $col;
        }
    }
    return null;
}

$rows = [];
$error = '';
$sourceTable = '';

if (cl_table_exists($db, 'lecturers')) {
    $sourceTable = 'lecturers';

    $nameCol   = cl_first_col($db, 'lecturers', ['full_name', 'name', 'lecturer_name', 'display_name']);
    $emailCol  = cl_first_col($db, 'lecturers', ['email', 'email_address']);
    $phoneCol  = cl_first_col($db, 'lecturers', ['phone', 'mobile', 'contact_no', 'telephone']);
    $statusCol = cl_first_col($db, 'lecturers', ['status', 'is_active']);
    $createdCol = cl_first_col($db, 'lecturers', ['created_at', 'created_on', 'date_created']);

    $select = [
        "id",
        $nameCol ? "`{$nameCol}` AS lecturer_name" : "CONCAT('Lecturer #', id) AS lecturer_name",
        $emailCol ? "`{$emailCol}` AS email" : "'' AS email",
        $phoneCol ? "`{$phoneCol}` AS phone" : "'' AS phone",
        $statusCol ? "`{$statusCol}` AS status" : "'Active' AS status",
        $createdCol ? "`{$createdCol}` AS created_at" : "NULL AS created_at"
    ];

    $orderCol = $nameCol ? "`{$nameCol}`" : "id";

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM lecturers
        ORDER BY {$orderCol} ASC
        LIMIT 500
    ";

    $res = $db->query($sql);

    if (!$res) {
        $error = 'Lecturers SQL error: ' . $db->error;
    } else {
        $rows = $res->fetch_all(MYSQLI_ASSOC);
    }

} elseif (cl_table_exists($db, 'users') || cl_table_exists($db, 'elms_users')) {
    $sourceTable = cl_table_exists($db, 'elms_users') ? 'elms_users' : 'users';

    $nameCol   = cl_first_col($db, $sourceTable, ['full_name', 'name', 'display_name', 'username']);
    $usernameCol = cl_first_col($db, $sourceTable, ['username', 'user_name']);
    $emailCol  = cl_first_col($db, $sourceTable, ['email', 'email_address']);
    $phoneCol  = cl_first_col($db, $sourceTable, ['phone', 'mobile', 'contact_no', 'telephone']);
    $roleCol   = cl_first_col($db, $sourceTable, ['role_slug', 'role', 'role_code']);
    $statusCol = cl_first_col($db, $sourceTable, ['status', 'is_active']);
    $createdCol = cl_first_col($db, $sourceTable, ['created_at', 'created_on', 'date_created']);

    $select = [
        "id",
        $nameCol ? "`{$nameCol}` AS lecturer_name" : "CONCAT('Lecturer #', id) AS lecturer_name",
        $usernameCol ? "`{$usernameCol}` AS username" : "'' AS username",
        $emailCol ? "`{$emailCol}` AS email" : "'' AS email",
        $phoneCol ? "`{$phoneCol}` AS phone" : "'' AS phone",
        $roleCol ? "`{$roleCol}` AS role_name" : "'lecturer' AS role_name",
        $statusCol ? "`{$statusCol}` AS status" : "'Active' AS status",
        $createdCol ? "`{$createdCol}` AS created_at" : "NULL AS created_at"
    ];

    $where = "1=1";
    if ($roleCol) {
        $where = "LOWER(`{$roleCol}`) IN ('lecturer','teacher','instructor')";
    }

    $orderCol = $nameCol ? "`{$nameCol}`" : "id";

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM `{$sourceTable}`
        WHERE {$where}
        ORDER BY {$orderCol} ASC
        LIMIT 500
    ";

    $res = $db->query($sql);

    if (!$res) {
        $error = 'Users SQL error: ' . $db->error;
    } else {
        $rows = $res->fetch_all(MYSQLI_ASSOC);
    }

} else {
    $error = 'No lecturers, elms_users, or users table found.';
}

$totalActive = 0;
$totalInactive = 0;

foreach ($rows as $r) {
    $status = strtolower((string)($r['status'] ?? 'active'));

    if ($status === '1' || $status === 'active' || $status === 'enabled') {
        $totalActive++;
    } elseif ($status === '0' || $status === 'inactive' || $status === 'disabled') {
        $totalInactive++;
    }
}

require_once __DIR__ . '/../includes/coordinator_layout_top.php';
?>

<div class="d-flex justify-content-between align-items-start mb-4">
    <div>
        <h1 class="page-title">Lecturers</h1>
        <div class="page-subtitle">View lecturer directory and contact details</div>
    </div>

    <a href="/elms/coordinator/dashboard.php" class="btn btn-light border rounded-4">
        <i class="bi bi-arrow-left me-1"></i> Dashboard
    </a>
</div>

<?php if ($error): ?>
    <div class="alert alert-warning rounded-4">
        <strong>Notice:</strong> <?= h($error) ?>
    </div>
<?php endif; ?>

<div class="row g-3 mb-4">
    <div class="col-md-4">
        <div class="eden-card p-3">
            <div class="text-muted small">Total Lecturers</div>
            <div class="fs-3 fw-bold"><?= number_format(count($rows)) ?></div>
            <i class="bi bi-people fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-4">
        <div class="eden-card p-3">
            <div class="text-muted small">Active</div>
            <div class="fs-3 fw-bold"><?= number_format($totalActive) ?></div>
            <i class="bi bi-check-circle fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-4">
        <div class="eden-card p-3">
            <div class="text-muted small">Source Table</div>
            <div class="fs-5 fw-bold mt-2"><?= $sourceTable !== '' ? h($sourceTable) : '-' ?></div>
            <i class="bi bi-database fs-4 text-danger"></i>
        </div>
    </div>
</div>

<div class="eden-card p-3 mb-4">
    <div class="row g-3 align-items-center">
        <div class="col-lg-7">
            <div class="input-group">
                <span class="input-group-text bg-white border-end-0">
                    <i class="bi bi-search"></i>
                </span>
                <input type="text" id="lecturerSearch" class="form-control border-start-0" placeholder="Search lecturer name, email, phone, status...">
            </div>
        </div>

        <div class="col-lg-3">
            <select id="statusFilter" class="form-select">
                <option value="">All Status</option>
                <option value="active">Active</option>
                <option value="inactive">Inactive</option>
                <option value="enabled">Enabled</option>
                <option value="disabled">Disabled</option>
            </select>
        </div>

        <div class="col-lg-2 text-lg-end">
            <span class="badge text-bg-danger rounded-pill px-3 py-2">
                <?= number_format(count($rows)) ?> Records
            </span>
        </div>
    </div>
</div>

<div class="eden-card overflow-hidden">
    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="lecturersTable">
            <thead class="table-light">
                <tr>
                    <th style="width:80px;">ID</th>
                    <th>Lecturer</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Status</th>
                    <th>Created</th>
                </tr>
            </thead>

            <tbody>
            <?php if (!$rows): ?>
                <tr>
                    <td colspan="6" class="text-center text-muted py-5">
                        <i class="bi bi-inbox fs-2 d-block mb-2"></i>
                        No lecturers found.
                    </td>
                </tr>
            <?php else: ?>
                <?php foreach ($rows as $r): ?>
                    <?php
                        $statusRaw = (string)($r['status'] ?? 'Active');

                        if ($statusRaw === '1') {
                            $statusText = 'Active';
                            $statusKey = 'active';
                        } elseif ($statusRaw === '0') {
                            $statusText = 'Inactive';
                            $statusKey = 'inactive';
                        } else {
                            $statusText = $statusRaw !== '' ? $statusRaw : 'Active';
                            $statusKey = strtolower($statusText);
                        }

                        $badgeClass = match ($statusKey) {
                            'active', 'enabled' => 'text-bg-success',
                            'inactive', 'disabled' => 'text-bg-secondary',
                            'pending' => 'text-bg-warning',
                            default => 'text-bg-light border'
                        };
                    ?>

                    <tr data-status="<?= h($statusKey) ?>">
                        <td class="text-muted">#<?= (int)$r['id'] ?></td>

                        <td>
                            <div class="fw-bold"><?= h($r['lecturer_name'] ?? ('Lecturer #' . $r['id'])) ?></div>
                            <?php if (!empty($r['username'])): ?>
                                <div class="small text-muted">@<?= h($r['username']) ?></div>
                            <?php endif; ?>
                        </td>

                        <td>
                            <?php if (!empty($r['email'])): ?>
                                <a href="mailto:<?= h($r['email']) ?>" class="text-decoration-none">
                                    <?= h($r['email']) ?>
                                </a>
                            <?php else: ?>
                                <span class="text-muted">-</span>
                            <?php endif; ?>
                        </td>

                        <td>
                            <?php if (!empty($r['phone'])): ?>
                                <a href="tel:<?= h($r['phone']) ?>" class="text-decoration-none">
                                    <?= h($r['phone']) ?>
                                </a>
                            <?php else: ?>
                                <span class="text-muted">-</span>
                            <?php endif; ?>
                        </td>

                        <td>
                            <span class="badge <?= h($badgeClass) ?> rounded-pill">
                                <?= h(ucwords(str_replace('_', ' ', $statusText))) ?>
                            </span>
                        </td>

                        <td>
                            <?= !empty($r['created_at']) ? h(date('Y-m-d', strtotime((string)$r['created_at']))) : '<span class="text-muted">-</span>' ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
document.addEventListener('DOMContentLoaded', function () {
    const searchInput = document.getElementById('lecturerSearch');
    const statusFilter = document.getElementById('statusFilter');
    const rows = Array.from(document.querySelectorAll('#lecturersTable tbody tr[data-status]'));

    function filterTable() {
        const q = (searchInput.value || '').toLowerCase().trim();
        const st = (statusFilter.value || '').toLowerCase().trim();

        rows.forEach(row => {
            const text = row.innerText.toLowerCase();
            const rowStatus = (row.dataset.status || '').toLowerCase();

            const matchSearch = q === '' || text.includes(q);
            const matchStatus = st === '' || rowStatus.includes(st);

            row.style.display = (matchSearch && matchStatus) ? '' : 'none';
        });
    }

    searchInput.addEventListener('input', filterTable);
    statusFilter.addEventListener('change', filterTable);
});
</script>

<?php require_once __DIR__ . '/../includes/coordinator_layout_bottom.php'; ?>