<?php
/**
 * Check and fix student user account completeness
 * This script will verify all students have proper user accounts
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<!DOCTYPE html>
<html>
<head>
    <title>Student User Account Completeness Check</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .success { color: green; }
        .error { color: red; }
        .warning { color: orange; }
        .info { color: blue; }
        table { border-collapse: collapse; width: 100%; margin: 10px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .progress-bar { 
            width: 100%; 
            background-color: #f0f0f0; 
            border-radius: 5px; 
            margin: 10px 0;
        }
        .progress-fill { 
            height: 20px; 
            background-color: #006747; 
            border-radius: 5px;
            text-align: center;
            color: white;
            font-weight: bold;
            line-height: 20px;
        }
    </style>
</head>
<body>";

echo "<h1>🔍 Student User Account Completeness Check</h1>";

try {
    // Try database connection
    $pdo = null;
    
    // Try standard config first
    try {
        require_once 'config/database.php';
    } catch (Exception $e) {
        echo "<p class='warning'>Standard config failed: " . $e->getMessage() . "</p>";
    }
    
    // Try direct connection if needed
    if (!isset($pdo) || $pdo === null) {
        $host = 'localhost';
        $dbname = 'school_management';
        $username = 'root';
        $password = 'root';
        
        try {
            $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
            echo "<p class='success'>✅ Database connected successfully</p>";
        } catch (Exception $e) {
            // Try with empty password
            try {
                $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, '', [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ]);
                echo "<p class='success'>✅ Database connected with empty password</p>";
            } catch (Exception $e2) {
                die("<p class='error'>❌ Cannot connect to database: " . $e2->getMessage() . "</p>");
            }
        }
    }
    
    echo "<h2>📊 Current Status</h2>";
    
    // Get total counts
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students");
    $total_students = $stmt->fetch()['total'];
    
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM students WHERE user_id IS NOT NULL AND user_id != 0 AND user_id != ''");
    $students_with_user = $stmt->fetch()['count'];
    
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM users WHERE role = 'student'");
    $student_users = $stmt->fetch()['count'];
    
    $completion_percentage = $total_students > 0 ? round(($students_with_user / $total_students) * 100, 1) : 0;
    
    echo "<p><strong>Total Students in Database:</strong> $total_students</p>";
    echo "<p><strong>Students with User Accounts:</strong> $students_with_user</p>";
    echo "<p><strong>Student User Records:</strong> $student_users</p>";
    echo "<p><strong>Completion Rate:</strong> $completion_percentage%</p>";
    
    // Progress bar
    echo "<div class='progress-bar'>";
    echo "<div class='progress-fill' style='width: {$completion_percentage}%'>$completion_percentage%</div>";
    echo "</div>";
    
    if ($total_students == $students_with_user) {
        echo "<p class='success'>✅ All students have user accounts!</p>";
        echo "<p><a href='admin/users.php?role_filter=student' target='_blank'>View Complete Student List</a></p>";
        exit;
    }
    
    echo "<h2>🔧 Missing User Accounts</h2>";
    
    // Get students without user accounts
    $stmt = $pdo->query("
        SELECT id, first_name, last_name, student_id, class_id, session, customer_number
        FROM students 
        WHERE user_id IS NULL OR user_id = 0 OR user_id = '' 
        ORDER BY id 
        LIMIT 20
    ");
    $missing_students = $stmt->fetchAll();
    
    $missing_count = count($missing_students);
    echo "<p><strong>Students Needing User Accounts:</strong> $missing_count (showing first 20)</p>";
    
    if (!empty($missing_students)) {
        echo "<table>";
        echo "<tr><th>ID</th><th>Name</th><th>Student ID</th><th>Class</th><th>Session</th><th>Customer No</th><th>Status</th></tr>";
        
        foreach ($missing_students as $student) {
            echo "<tr>";
            echo "<td>" . $student['id'] . "</td>";
            echo "<td>" . $student['first_name'] . " " . $student['last_name'] . "</td>";
            echo "<td>" . $student['student_id'] . "</td>";
            echo "<td>" . $student['class_id'] . "</td>";
            echo "<td>" . $student['session'] . "</td>";
            echo "<td>" . ($student['customer_number'] ?? 'N/A') . "</td>";
            echo "<td><span class='warning'>Needs Account</span></td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    echo "<h2>🛠️ Creating Missing User Accounts</h2>";
    
    if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['create_accounts'])) {
        echo "<p class='info'>Creating user accounts for missing students...</p>";
        
        $created_count = 0;
        $errors = [];
        
        foreach ($missing_students as $student) {
            try {
                // Create username from student name
                $clean_name = preg_replace('/[^a-zA-Z0-9]/', '', $student['first_name'] . $student['last_name']);
                $base_username = strtolower(substr($clean_name, 0, 15));
                
                if (empty($base_username)) {
                    $base_username = 'student' . $student['id'];
                }
                
                $username = $base_username;
                $counter = 1;
                
                // Ensure unique username
                while (true) {
                    $stmt_check = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
                    $stmt_check->execute([$username]);
                    if ($stmt_check->fetchColumn() == 0) {
                        break;
                    }
                    $username = $base_username . $counter;
                    $counter++;
                    if ($counter > 100) {
                        $username = 'student' . $student['id'] . '_' . time();
                        break;
                    }
                }
                
                // Create email and password
                $email = $username . '@school.edu';
                $password_raw = $student['student_id'] ?: $username;
                $password = password_hash($password_raw, PASSWORD_DEFAULT);
                
                // Insert user with transaction
                $pdo->beginTransaction();
                
                $stmt_insert = $pdo->prepare("
                    INSERT INTO users (username, email, password, role, status, created_at) 
                    VALUES (?, ?, ?, 'student', 'active', NOW())
                ");
                $stmt_insert->execute([$username, $email, $password]);
                
                $user_id = $pdo->lastInsertId();
                
                // Update student record
                $stmt_update = $pdo->prepare("UPDATE students SET user_id = ? WHERE id = ?");
                $stmt_update->execute([$user_id, $student['id']]);
                
                $pdo->commit();
                
                echo "<p class='success'>✅ Created user '$username' for {$student['first_name']} {$student['last_name']}</p>";
                $created_count++;
                
            } catch (Exception $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollback();
                }
                $error_msg = "Failed for student ID {$student['id']}: " . $e->getMessage();
                echo "<p class='error'>❌ $error_msg</p>";
                $errors[] = $error_msg;
            }
        }
        
        echo "<h3>📊 Creation Summary</h3>";
        echo "<p><strong>Successfully Created:</strong> $created_count accounts</p>";
        echo "<p><strong>Errors:</strong> " . count($errors) . "</p>";
        
        if ($created_count > 0) {
            echo "<div class='success'>";
            echo "<h4>🎉 Success!</h4>";
            echo "<p>Created $created_count user accounts for students.</p>";
            echo "<p><a href='?refresh=1'>Refresh Status</a> | <a href='admin/users.php?role_filter=student' target='_blank'>View Student Users</a></p>";
            echo "</div>";
        }
        
    } else {
        echo "<form method='POST'>";
        echo "<button type='submit' name='create_accounts' class='btn btn-primary'>";
        echo "Create User Accounts for " . count($missing_students) . " Students";
        echo "</button>";
        echo "</form>";
        
        echo "<p class='info'>This will create user accounts for all students currently missing them.</p>";
    }
    
    // Refresh check
    if (isset($_GET['refresh'])) {
        echo "<script>setTimeout(function(){ location.reload(); }, 3000);</script>";
        echo "<p class='info'>Refreshing in 3 seconds...</p>";
    }
    
} catch (Exception $e) {
    echo "<div class='error'>";
    echo "<h3>❌ Error</h3>";
    echo "<p>" . htmlspecialchars($e->getMessage()) . "</p>";
    echo "</div>";
}

echo "</body></html>";
?>