<?php
/**
 * Cleanup Old Verification Codes
 * This script removes expired and used verification codes from the database
 * Run this periodically via cron job or manually
 */

require 'core.php';

// Check if admin is logged in (for manual execution)
if (!isAdmin()) {
    die('Access denied. Admin login required.');
}

// Delete expired codes
$stmt1 = $connect->prepare("DELETE FROM verification_codes WHERE expires_at < NOW()");
$deleted_expired = $stmt1->execute();
$expired_count = $stmt1->rowCount();

// Delete used codes older than 7 days
$stmt2 = $connect->prepare("DELETE FROM verification_codes WHERE is_used = 1 AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)");
$deleted_used = $stmt2->execute();
$used_count = $stmt2->rowCount();

// Get statistics
$stats = $connect->query("
    SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN is_used = 1 THEN 1 ELSE 0 END) as used,
        SUM(CASE WHEN expires_at < NOW() THEN 1 ELSE 0 END) as expired,
        SUM(CASE WHEN is_used = 0 AND expires_at > NOW() THEN 1 ELSE 0 END) as active
    FROM verification_codes
")->fetch();

?>
<!DOCTYPE html>
<html>
<head>
    <title>Verification Codes Cleanup</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            border-bottom: 2px solid #667eea;
            padding-bottom: 10px;
        }
        .stats {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
            gap: 15px;
            margin: 20px 0;
        }
        .stat-card {
            background: #f8f9fa;
            padding: 20px;
            border-radius: 8px;
            text-align: center;
            border-left: 4px solid #667eea;
        }
        .stat-number {
            font-size: 32px;
            font-weight: bold;
            color: #667eea;
        }
        .stat-label {
            color: #666;
            font-size: 14px;
            margin-top: 5px;
        }
        .success {
            background: #d4edda;
            color: #155724;
            padding: 15px;
            border-radius: 4px;
            border: 1px solid #c3e6cb;
            margin: 20px 0;
        }
        .info {
            background: #d1ecf1;
            color: #0c5460;
            padding: 15px;
            border-radius: 4px;
            border: 1px solid #bee5eb;
            margin: 20px 0;
        }
        .btn {
            display: inline-block;
            background: #667eea;
            color: white;
            padding: 12px 30px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            text-decoration: none;
            font-size: 16px;
            font-weight: bold;
        }
        .btn:hover {
            background: #5568d3;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>🧹 Verification Codes Cleanup</h1>
        
        <div class="success">
            <strong>✅ Cleanup Completed!</strong><br>
            <p style="margin: 10px 0 0 0;">
                Deleted <?php echo $expired_count; ?> expired codes<br>
                Deleted <?php echo $used_count; ?> old used codes
            </p>
        </div>

        <h2>Current Statistics</h2>
        <div class="stats">
            <div class="stat-card">
                <div class="stat-number"><?php echo $stats['total']; ?></div>
                <div class="stat-label">Total Codes</div>
            </div>
            <div class="stat-card">
                <div class="stat-number"><?php echo $stats['active']; ?></div>
                <div class="stat-label">Active Codes</div>
            </div>
            <div class="stat-card">
                <div class="stat-number"><?php echo $stats['used']; ?></div>
                <div class="stat-label">Used Codes</div>
            </div>
            <div class="stat-card">
                <div class="stat-number"><?php echo $stats['expired']; ?></div>
                <div class="stat-label">Expired Codes</div>
            </div>
        </div>

        <div class="info">
            <strong>ℹ️ Automation Tip:</strong><br>
            Set up a cron job to run this cleanup automatically:<br>
            <code>0 2 * * * php <?php echo __FILE__; ?></code><br>
            <small>(Runs daily at 2:00 AM)</small>
        </div>

        <a href="<?php echo SITE_URL; ?>/admin" class="btn">← Back to Admin</a>
        <a href="<?php echo $_SERVER['PHP_SELF']; ?>" class="btn" style="background: #28a745;">🔄 Run Cleanup Again</a>
    </div>
</body>
</html>
