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

try {
    $db = getDB();

    // Get all researchers without photos or with incorrect photo paths
    $stmt = $db->query('
        SELECT u.id, u.first_name, u.last_name, rp.photo
        FROM users u
        JOIN researcher_profiles rp ON u.id = rp.user_id
        WHERE u.role_id = (SELECT id FROM roles WHERE name = "researcher" LIMIT 1)
        AND (rp.photo IS NULL OR rp.photo = "" OR rp.photo NOT LIKE CONCAT("assets/uploads/profile_pictures/researcher_", u.id, ".jpg"))
        ORDER BY u.id
    ');
    $researchers = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo 'Found ' . count($researchers) . ' researchers needing correct profile pictures' . "\n";

    $uploadsDir = __DIR__ . '/../assets/uploads/profile_pictures/';
    if (!is_dir($uploadsDir)) {
        mkdir($uploadsDir, 0755, true);
    }

    $updated = 0;
    foreach ($researchers as $researcher) {
        $imagePath = $uploadsDir . "researcher_{$researcher['id']}.jpg";

        // Check if file already exists
        if (file_exists($imagePath)) {
            echo "✓ Image already exists for {$researcher['first_name']} {$researcher['last_name']} (ID: {$researcher['id']})\n";
            // Update database path if needed
            $correctPath = "assets/uploads/profile_pictures/researcher_{$researcher['id']}.jpg";
            if ($researcher['photo'] !== $correctPath) {
                $updateStmt = $db->prepare("UPDATE researcher_profiles SET photo = ? WHERE user_id = ?");
                $updateStmt->execute([$correctPath, $researcher['id']]);
                $updated++;
                echo "  Updated database path\n";
            }
            continue;
        }

        // Download new image
        $imageUrl = "https://picsum.photos/300/300?random={$researcher['id']}";
        $imageData = file_get_contents($imageUrl);

        if ($imageData !== false) {
            $bytesWritten = file_put_contents($imagePath, $imageData);
            if ($bytesWritten !== false && $bytesWritten > 0) {
                $photoPath = "assets/uploads/profile_pictures/researcher_{$researcher['id']}.jpg";

                // Update the database
                $updateStmt = $db->prepare("UPDATE researcher_profiles SET photo = ? WHERE user_id = ?");
                $updateStmt->execute([$photoPath, $researcher['id']]);

                $updated++;
                echo "✓ Downloaded and updated profile picture for {$researcher['first_name']} {$researcher['last_name']} (ID: {$researcher['id']})\n";
            } else {
                echo "✗ Failed to save image for {$researcher['first_name']} {$researcher['last_name']}\n";
            }
        } else {
            echo "✗ Failed to download image for {$researcher['first_name']} {$researcher['last_name']}\n";
        }
    }

    echo "\nUpdated $updated profile pictures\n";

    // Final count
    $stmt = $db->query('SELECT COUNT(*) as count FROM researcher_profiles WHERE photo IS NOT NULL AND photo != ""');
    $result = $stmt->fetch();
    echo 'Total researchers with photos: ' . $result['count'] . "\n";

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