<?php
/**
 * Diagnose database and login_attempts table
 */

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

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

echo "<h2>Database Diagnosis Report</h2>\n";

try {
    // Check if login_attempts table exists
    echo "<h3>1. Checking for login_attempts table...</h3>\n";
    $result = $pdo->query("SHOW TABLES LIKE 'login_attempts'");
    $exists = $result->rowCount() > 0;

    if ($exists) {
        echo "<p style='color: green;'>✅ login_attempts table exists</p>";
        
        // Check table structure
        echo "<h3>2. Checking table structure...</h3>\n";
        $columns = $pdo->query("DESCRIBE login_attempts")->fetchAll();
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
        foreach ($columns as $column) {
            echo "<tr>";
            echo "<td>{$column['Field']}</td>";
            echo "<td>{$column['Type']}</td>";
            echo "<td>{$column['Null']}</td>";
            echo "<td>{$column['Key']}</td>";
            echo "<td>{$column['Default']}</td>";
            echo "<td>{$column['Extra']}</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        // Count records in the table
        echo "<h3>3. Checking table records...</h3>\n";
        $count = $pdo->query("SELECT COUNT(*) as count FROM login_attempts")->fetch()['count'];
        echo "<p>Total records: <strong>$count</strong></p>";
        
        if ($count > 0) {
            echo "<p>Sample records:</p>";
            $sample = $pdo->query("SELECT * FROM login_attempts LIMIT 5")->fetchAll();
            echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
            if (!empty($sample)) {
                // Header row
                echo "<tr>";
                foreach (array_keys($sample[0]) as $key) {
                    echo "<th>$key</th>";
                }
                echo "</tr>";
                
                // Data rows
                foreach ($sample as $row) {
                    echo "<tr>";
                    foreach ($row as $value) {
                        echo "<td>" . htmlspecialchars($value ?? 'NULL') . "</td>";
                    }
                    echo "</tr>";
                }
            }
            echo "</table>";
        }
        
    } else {
        echo "<p style='color: red;'>❌ login_attempts table does NOT exist</p>";
        
        // Try to create the table
        echo "<h3>4. Attempting to create login_attempts table...</h3>\n";
        try {
            $pdo->exec("
                CREATE TABLE login_attempts (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50),
                    ip_address VARCHAR(45) NOT NULL,
                    user_agent VARCHAR(255),
                    attempt_status ENUM('success', 'failed') NOT NULL,
                    failure_reason VARCHAR(255),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_ip_time (ip_address, created_at),
                    INDEX idx_username (username)
                )
            ");
            echo "<p style='color: green;'>✅ login_attempts table created successfully!</p>";
        } catch (PDOException $e) {
            echo "<p style='color: red;'>❌ Failed to create login_attempts table: " . $e->getMessage() . "</p>";
        }
    }

    echo "<h3>5. Testing basic query...</h3>\n";
    try {
        $testQuery = $pdo->prepare("SELECT COUNT(*) as count FROM login_attempts");
        $testQuery->execute([]);
        $result = $testQuery->fetch();
        echo "<p style='color: green;'>✅ Basic query works. Record count: " . $result['count'] . "</p>";
    } catch (PDOException $e) {
        echo "<p style='color: red;'>❌ Basic query failed: " . $e->getMessage() . "</p>";
    }
    
    echo "<h3>6. Checking MySQL version and capabilities...</h3>\n";
    try {
        $version = $pdo->query("SELECT VERSION() as version")->fetch()['version'];
        echo "<p>MySQL Version: $version</p>";
        
        // Check if ENUM is supported
        $enumCheck = $pdo->query("SHOW ENGINES")->fetchAll();
        echo "<p>Storage engines available:</p><ul>";
        foreach ($enumCheck as $engine) {
            echo "<li>{$engine['Engine']} - Support: {$engine['Support']}</li>";
        }
        echo "</ul>";
    } catch (PDOException $e) {
        echo "<p style='color: orange;'>Could not check MySQL version: " . $e->getMessage() . "</p>";
    }

    echo "<h3>7. Final Status</h3>\n";
    echo "<p>If the login_attempts table now exists and basic queries work, ";
    echo "the login_attempts.php page should work. Try accessing it again.</p>";

} catch (PDOException $e) {
    echo "<h3>Error connecting to database:</h3>\n";
    echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
}
?>