<?php
require_once __DIR__ . '/../config/database.php';

try {
    $db = getDB();

    $stmt = $db->query('SELECT COUNT(*) as count FROM users WHERE role_id = (SELECT id FROM roles WHERE name = "researcher" LIMIT 1)');
    $result = $stmt->fetch();
    echo 'Total researchers in users table: ' . $result['count'] . "\n";

    $stmt = $db->query('SELECT COUNT(*) as count FROM researcher_profiles');
    $result = $stmt->fetch();
    echo 'Total researcher profiles: ' . $result['count'] . "\n";

    $stmt = $db->query('SELECT u.first_name, u.last_name, rp.specialization, rp.publications_count FROM users u JOIN researcher_profiles rp ON u.id = rp.user_id LIMIT 5');
    $researchers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "\nSample researchers:\n";
    foreach ($researchers as $r) {
        echo $r['first_name'] . ' ' . $r['last_name'] . ' - ' . $r['specialization'] . ' (' . $r['publications_count'] . ' publications)' . "\n";
    }

} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}
?>