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

session_start();
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: 900px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; }
        .section { margin: 20px 0; padding: 15px; border: 1px solid #ddd; border-radius: 4px; }
        .error { color: red; background: #ffe0e0; }
        .success { color: green; background: #e0ffe0; }
        code { background: #f0f0f0; padding: 2px 6px; font-family: monospace; }
        pre { background: #f0f0f0; padding: 10px; overflow-x: auto; }
        table { width: 100%; border-collapse: collapse; margin: 10px 0; }
        td, th { border: 1px solid #ddd; padding: 10px; text-align: left; }
        th { background: #f0f0f0; }
    </style>
</head>
<body>
    <div class='container'>
        <h1>🔍 Deep OTP Debug - Step by Step</h1>";

// Show session data
echo "<div class='section'>";
echo "<h2>1️⃣ Session Data</h2>";
if (isset($_SESSION['reg_email'])) {
    echo "<p class='success'>✓ reg_email in session: <code>" . htmlspecialchars($_SESSION['reg_email']) . "</code></p>";
} else {
    echo "<p class='error'>✗ reg_email NOT in session!</p>";
    echo "<p>Session contents: <pre>" . print_r($_SESSION, true) . "</pre></p>";
}
echo "</div>";

// Get the email from session (same as register.php)
$email = $_SESSION['reg_email'] ?? '';

// Show all OTPs for this email
echo "<div class='section'>";
echo "<h2>2️⃣ All OTPs for email: <code>" . htmlspecialchars($email) . "</code></h2>";

$query = "SELECT id, email, otp_code, purpose, expires_at, created_at, (NOW() < expires_at) as is_valid FROM otp_verification WHERE email = ? ORDER BY created_at DESC";
$stmt = $conn->prepare($query);
$stmt->bind_param('s', $email);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    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>Valid?</th>
        </tr>";
    
    while ($row = $result->fetch_assoc()) {
        $is_valid = $row['is_valid'] ? '✓ YES' : '✗ NO';
        echo "<tr>
            <td>{$row['id']}</td>
            <td><code>" . htmlspecialchars($row['email']) . "</code></td>
            <td><code>" . htmlspecialchars($row['otp_code']) . "</code> (Length: " . strlen($row['otp_code']) . ")</td>
            <td><code>" . htmlspecialchars($row['purpose']) . "</code></td>
            <td>{$row['expires_at']}</td>
            <td>{$row['created_at']}</td>
            <td>{$is_valid}</td>
        </tr>";
    }
    echo "</table>";
} else {
    echo "<p class='error'>✗ No OTPs found for this email!</p>";
    echo "<p>This means either:</p>";
    echo "<ul>
        <li>The email in session doesn't match the email used to create OTP</li>
        <li>OTPs are being stored with a different email</li>
    </ul>";
}
$stmt->close();

// Show ALL OTPs (not filtered by email)
echo "<div class='section'>";
echo "<h2>3️⃣ ALL OTPs in Database (Last 5)</h2>";

$all_otps = $conn->query("SELECT id, email, otp_code, purpose, expires_at, created_at FROM otp_verification ORDER BY created_at DESC LIMIT 5");

echo "<table>
    <tr>
        <th>ID</th>
        <th>Email</th>
        <th>OTP Code</th>
        <th>Purpose</th>
        <th>Expires At</th>
    </tr>";

while ($row = $all_otps->fetch_assoc()) {
    echo "<tr>
        <td>{$row['id']}</td>
        <td><code>" . htmlspecialchars($row['email']) . "</code></td>
        <td><code>" . htmlspecialchars($row['otp_code']) . "</code></td>
        <td><code>" . htmlspecialchars($row['purpose']) . "</code></td>
        <td>{$row['expires_at']}</td>
    </tr>";
}
echo "</table>";
echo "</div>";

// Test query
echo "<div class='section'>";
echo "<h2>4️⃣ Test OTP Lookup Query</h2>";

if ($email) {
    echo "<p>Testing with:</p>";
    echo "<ul>
        <li>Email: <code>" . htmlspecialchars($email) . "</code></li>
        <li>Purpose: <code>email_verification</code></li>
        <li>Expiry: Must be > NOW()</li>
    </ul>";
    
    $test_query = "SELECT id, email, otp_code, purpose FROM otp_verification WHERE email = ? AND purpose = ? AND expires_at > NOW()";
    $test_stmt = $conn->prepare($test_query);
    $purpose = 'email_verification';
    $test_stmt->bind_param('ss', $email, $purpose);
    $test_stmt->execute();
    $test_result = $test_stmt->get_result();
    
    echo "<p>Query found: <strong>" . $test_result->num_rows . " OTP(s)</strong></p>";
    
    if ($test_result->num_rows > 0) {
        echo "<p class='success'>✓ OTPs found that match criteria!</p>";
        echo "<table>
            <tr>
                <th>ID</th>
                <th>Email</th>
                <th>OTP Code</th>
                <th>Purpose</th>
            </tr>";
        while ($row = $test_result->fetch_assoc()) {
            echo "<tr>
                <td>{$row['id']}</td>
                <td><code>" . htmlspecialchars($row['email']) . "</code></td>
                <td><code>" . htmlspecialchars($row['otp_code']) . "</code></td>
                <td><code>" . htmlspecialchars($row['purpose']) . "</code></td>
            </tr>";
        }
        echo "</table>";
        
        echo "<p><strong>📋 To test manually:</strong></p>";
        echo "<p>Copy one of the OTP codes above and enter it in the registration form.</p>";
    } else {
        echo "<p class='error'>✗ No OTPs match the query criteria!</p>";
        echo "<p>Check:</p>";
        echo "<ul>
            <li>Is the email exactly matching?</li>
            <li>Is the purpose exactly 'email_verification'?</li>
            <li>Is the OTP still valid (not expired)?</li>
        </ul>";
    }
    $test_stmt->close();
} else {
    echo "<p class='error'>✗ No email in session - can't test!</p>";
}

echo "</div>";

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