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

echo "Removing orphaned student users...\n";

// Begin transaction
$pdo->beginTransaction();

try {
    // First, let's check what we're going to delete
    $stmt = $pdo->query("
        SELECT COUNT(*) as count 
        FROM users u 
        LEFT JOIN students s ON u.id = s.user_id 
        WHERE u.role = 'student' AND s.id IS NULL
    ");
    $orphaned_count = $stmt->fetch()['count'];
    
    echo "Found " . $orphaned_count . " orphaned student users to remove.\n";
    
    if ($orphaned_count > 0) {
        // 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";
        
        // Also check student users count
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM users u LEFT JOIN students s ON u.id = s.user_id WHERE u.role = 'student' AND s.id IS NOT NULL");
        $valid_student_users = $stmt->fetch()['count'];
        echo "Valid student users (with corresponding student records): " . $valid_student_users . "\n";
    } else {
        echo "No orphaned student users found.\n";
        $pdo->rollback();
    }
} catch (Exception $e) {
    $pdo->rollback();
    echo "Error deleting orphaned accounts: " . $e->getMessage() . "\n";
}
?>