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

echo "Checking for orphaned student users...\n";

// First, let's check what we're going to delete
$stmt = $pdo->query("
    SELECT u.id, u.username, u.email, u.created_at 
    FROM users u 
    LEFT JOIN students s ON u.id = s.user_id 
    WHERE u.role = 'student' AND s.id IS NULL
");
$orphaned_users = $stmt->fetchAll();

echo "Found " . count($orphaned_users) . " orphaned student users.\n";

if (count($orphaned_users) > 0) {
    echo "\nOrphaned users:\n";
    foreach ($orphaned_users as $user) {
        echo "ID: " . $user['id'] . ", Username: " . $user['username'] . ", Email: " . $user['email'] . ", Created: " . $user['created_at'] . "\n";
    }
    
    echo "\nDo you want to delete these orphaned accounts? (yes/no): ";
    $handle = fopen("php://stdin", "r");
    $line = fgets($handle);
    fclose($handle);
    
    if (trim(strtolower($line)) === 'yes') {
        // Begin transaction
        $pdo->beginTransaction();
        
        try {
            // Delete the orphaned student users
            $stmt = $pdo->prepare("
                DELETE u FROM users u 
                LEFT JOIN students s ON u.id = s.user_id 
                WHERE u.role = 'student' AND s.id IS NULL
            ");
            $stmt->execute();
            $deleted_count = $stmt->rowCount();
            
            $pdo->commit();
            
            echo "Successfully deleted " . $deleted_count . " orphaned student accounts.\n";
            
            // Verify the cleanup
            $stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
            $result = $stmt->fetch();
            echo "Total users after cleanup: " . $result['total'] . "\n";
            
        } catch (Exception $e) {
            $pdo->rollback();
            echo "Error deleting orphaned accounts: " . $e->getMessage() . "\n";
        }
    } else {
        echo "Operation cancelled.\n";
    }
} else {
    echo "No orphaned student users found.\n";
}
?>