<?php
/**
 * Debug OTP Verification Issues
 */

require_once __DIR__ . '/auth-system/config/db.php';

echo "<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: Arial; margin: 20px; background: #f5f5f5; }
        .container { max-width: 800px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; }
        table { width: 100%; border-collapse: collapse; margin-top: 10px; }
        td, th { border: 1px solid #ddd; padding: 10px; text-align: left; }
        th { background: #f0f0f0; }
        code { background: #f0f0f0; padding: 2px 6px; }
        .error { color: red; }
        .success { color: green; }
    </style>
</head>
<body>
    <div class='container'>
        <h1>🔍 OTP Verification Debug</h1>";

// Check OTP table
$result = $conn->query("SHOW TABLES LIKE 'otp_verification'");
if ($result && $result->num_rows > 0) {
    echo "<p class='success'>✓ OTP verification table exists</p>";
    
    // Get recent OTPs
    $otps = $conn->query("SELECT * FROM otp_verification ORDER BY created_at DESC LIMIT 10");
    
    echo "<h2>Recent OTPs (Last 10)</h2>";
    echo "<table>
        <tr>
            <th>ID</th>
            <th>Email</th>
            <th>OTP Code</th>
            <th>Purpose</th>
            <th>Expires At</th>
            <th>Created At</th>
            <th>Status</th>
        </tr>";
    
    while ($row = $otps->fetch_assoc()) {
        $now = new DateTime();
        $expires = new DateTime($row['expires_at']);
        $is_expired = $now > $expires ? 'EXPIRED' : 'VALID';
        $status_color = $is_expired === 'EXPIRED' ? 'error' : 'success';
        
        echo "<tr>
            <td>{$row['id']}</td>
            <td>{$row['email']}</td>
            <td><code>{$row['otp_code']}</code></td>
            <td>{$row['purpose']}</td>
            <td>{$row['expires_at']}</td>
            <td>{$row['created_at']}</td>
            <td class='{$status_color}'>{$is_expired}</td>
        </tr>";
    }
    echo "</table>";
    
    echo "<h2>⚠️ Common Issues</h2>";
    echo "<ul>
        <li><strong>OTP Expired:</strong> Check 'Expires At' column - should be in the future</li>
        <li><strong>Wrong Email:</strong> Make sure the email in registration matches OTP record</li>
        <li><strong>Extra Spaces:</strong> OTP code might have leading/trailing spaces</li>
        <li><strong>Database Time:</strong> Server time might be off (check timestamps)</li>
    </ul>";
    
    echo "<h2>📋 Database Schema Check</h2>";
    $schema = $conn->query("DESCRIBE otp_verification");
    echo "<table>
        <tr>
            <th>Field</th>
            <th>Type</th>
            <th>Null</th>
            <th>Key</th>
            <th>Default</th>
            <th>Extra</th>
        </tr>";
    while ($col = $schema->fetch_assoc()) {
        echo "<tr>
            <td>{$col['Field']}</td>
            <td>{$col['Type']}</td>
            <td>{$col['Null']}</td>
            <td>{$col['Key']}</td>
            <td>{$col['Default']}</td>
            <td>{$col['Extra']}</td>
        </tr>";
    }
    echo "</table>";
    
} else {
    echo "<p class='error'>✗ OTP verification table does NOT exist!</p>";
    echo "<p>Run this SQL to create it:</p>";
    echo "<pre>
CREATE TABLE otp_verification (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    otp_code VARCHAR(10) NOT NULL,
    purpose VARCHAR(50),
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_otp (email, otp_code),
    INDEX idx_expires (expires_at)
);
    </pre>";
}

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