<?php
/**
 * Database Diagnostic Tool
 * Check if all required tables exist and have correct structure
 */

require_once 'config/config.php';

echo "<h2>🔍 Database Diagnostic Report</h2>";
echo "<p>Database: " . DB_NAME . "</p>";
echo "<hr>";

try {
    $db = new Database();
    
    // List all tables in database
    echo "<h3>📋 All Tables in Database:</h3>";
    $tables = $db->fetch('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?', [DB_NAME]);
    
    // Get all tables as array
    $allTables = $db->query('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?', [DB_NAME]);
    
    if ($allTables && count($allTables) > 0) {
        echo "<ul>";
        foreach ($allTables as $table) {
            echo "<li><strong>" . $table['TABLE_NAME'] . "</strong></li>";
        }
        echo "</ul>";
    } else {
        echo "<p>❌ No tables found</p>";
    }
    
    // Check for required tables
    echo "<h3>✅ Required Tables Check:</h3>";
    
    $requiredTables = ['email_otps', 'admin_settings', 'users'];
    
    foreach ($requiredTables as $tableName) {
        $check = $db->fetch(
            'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?',
            [DB_NAME, $tableName]
        );
        
        if ($check) {
            echo "<p>✅ <strong>$tableName</strong> - EXISTS</p>";
            
            // Show structure
            $structure = $db->query("DESCRIBE $tableName");
            echo "<table border='1' cellpadding='5' style='margin-left: 20px;'>";
            echo "<tr><th>Column</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
            foreach ($structure as $col) {
                echo "<tr>";
                echo "<td>" . $col['Field'] . "</td>";
                echo "<td>" . $col['Type'] . "</td>";
                echo "<td>" . ($col['Null'] === 'YES' ? 'YES' : 'NO') . "</td>";
                echo "<td>" . ($col['Key'] ?: 'None') . "</td>";
                echo "<td>" . ($col['Default'] ?: 'None') . "</td>";
                echo "</tr>";
            }
            echo "</table><br>";
            
            // Show row count
            $countResult = $db->fetch("SELECT COUNT(*) as count FROM $tableName");
            echo "<p>Rows in $tableName: <strong>" . $countResult['count'] . "</strong></p><br>";
        } else {
            echo "<p>❌ <strong>$tableName</strong> - MISSING! ⚠️</p>";
        }
    }
    
    // Check email_otps records
    echo "<h3>Recent OTP Records:</h3>";
    $otps = $db->query('SELECT id, email, otp, attempts, created_at, expires_at FROM email_otps ORDER BY created_at DESC LIMIT 5');
    
    if ($otps && count($otps) > 0) {
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>ID</th><th>Email</th><th>OTP</th><th>Attempts</th><th>Created</th><th>Expires</th><th>Status</th></tr>";
        foreach ($otps as $otp) {
            // Check if expired
            $expireCheck = $db->fetch('SELECT NOW() > ? as is_expired', [$otp['expires_at']]);
            $status = $expireCheck['is_expired'] ? '❌ EXPIRED' : '✅ VALID';
            
            echo "<tr>";
            echo "<td>" . $otp['id'] . "</td>";
            echo "<td>" . $otp['email'] . "</td>";
            echo "<td>" . $otp['otp'] . "</td>";
            echo "<td>" . $otp['attempts'] . "</td>";
            echo "<td>" . $otp['created_at'] . "</td>";
            echo "<td>" . $otp['expires_at'] . "</td>";
            echo "<td>" . $status . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>No OTP records found</p>";
    }
    
    // Check admin_settings
    echo "<h3>Admin Settings:</h3>";
    $settings = $db->query('SELECT setting_key, setting_value FROM admin_settings LIMIT 20');
    
    if ($settings && count($settings) > 0) {
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>Setting Key</th><th>Setting Value</th></tr>";
        foreach ($settings as $setting) {
            $value = strlen($setting['setting_value']) > 50 ? substr($setting['setting_value'], 0, 50) . "..." : $setting['setting_value'];
            echo "<tr>";
            echo "<td><strong>" . $setting['setting_key'] . "</strong></td>";
            echo "<td>" . htmlspecialchars($value) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>❌ No admin settings found</p>";
    }
    
    // Check database time
    echo "<h3>Database Time Check:</h3>";
    $timeCheck = $db->fetch('SELECT NOW() as db_time, CURTIME() as db_time_only, CURDATE() as db_date');
    echo "<p>Database NOW(): <strong>" . $timeCheck['db_time'] . "</strong></p>";
    echo "<p>Database CURTIME(): <strong>" . $timeCheck['db_time_only'] . "</strong></p>";
    echo "<p>Database CURDATE(): <strong>" . $timeCheck['db_date'] . "</strong></p>";
    
    // Test OTP generation and expiry
    echo "<h3>Test OTP Expiry Logic:</h3>";
    $testResult = $db->fetch(
        'SELECT NOW() as now, DATE_ADD(NOW(), INTERVAL 5 MINUTE) as expiry_5min, DATE_ADD(NOW(), INTERVAL -1 MINUTE) as expired_1min_ago'
    );
    echo "<p>Now: <strong>" . $testResult['now'] . "</strong></p>";
    echo "<p>5 minutes from now: <strong>" . $testResult['expiry_5min'] . "</strong></p>";
    echo "<p>1 minute ago: <strong>" . $testResult['expired_1min_ago'] . "</strong></p>";
    
    // Check if timestamps are being compared correctly
    $expireTest = $db->fetch(
        'SELECT 
            NOW() as current_time,
            DATE_ADD(NOW(), INTERVAL 5 MINUTE) as future_time,
            NOW() > DATE_ADD(NOW(), INTERVAL 5 MINUTE) as is_expired_positive,
            DATE_ADD(NOW(), INTERVAL -1 MINUTE) as past_time,
            NOW() > DATE_ADD(NOW(), INTERVAL -1 MINUTE) as is_expired_negative'
    );
    
    echo "<h4>Expiry Test Results:</h4>";
    echo "<ul>";
    echo "<li>Current: " . $expireTest['current_time'] . "</li>";
    echo "<li>Future (5 min): " . $expireTest['future_time'] . " - Is expired? " . ($expireTest['is_expired_positive'] ? '❌ YES' : '✅ NO') . "</li>";
    echo "<li>Past (1 min ago): " . $expireTest['past_time'] . " - Is expired? " . ($expireTest['is_expired_negative'] ? '✅ YES' : '❌ NO') . "</li>";
    echo "</ul>";
    
    echo "<hr>";
    echo "<p style='color: green;'><strong>✅ Diagnostic Complete</strong></p>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'><strong>❌ Error:</strong> " . $e->getMessage() . "</p>";
    echo "<pre>" . $e->getTraceAsString() . "</pre>";
}
?>
