<?php
/**
 * Cleanup Expired Subscriptions
 * 
 * This script should be run as a cron job to expire old subscriptions
 * Since we don't have SUPER privileges to use MySQL event scheduler,
 * we'll use a PHP cron job instead.
 * 
 * Add this to your crontab (runs every hour):
 * 0 * * * * /usr/bin/php /path/to/api/cleanup_expired_subscriptions.php
 * 
 * Or set up in cPanel: Cron Jobs -> Add New Cron Job
 * Command: php /home/yourusername/public_html/api/cleanup_expired_subscriptions.php
 */

require_once 'config.php';

// Log start
$logMessage = "Starting subscription cleanup at " . date('Y-m-d H:i:s') . "\n";

try {
    // 1. Downgrade expired users
    $stmt = $pdo->prepare("
        UPDATE users 
        SET user_level = 'registered',
            subscription_type = 'none'
        WHERE user_level IN ('pro', 'vip')
          AND subscription_expires_at IS NOT NULL
          AND subscription_expires_at < NOW()
    ");
    $stmt->execute();
    $downgraded = $stmt->rowCount();
    $logMessage .= "Downgraded $downgraded users\n";
    
    // 2. Deactivate expired subscription records
    $stmt = $pdo->prepare("
        UPDATE subscriptions
        SET is_active = 0
        WHERE expires_at < NOW() AND is_active = 1
    ");
    $stmt->execute();
    $deactivated = $stmt->rowCount();
    $logMessage .= "Deactivated $deactivated subscription records\n";
    
    // 3. Log the cleanup action
    $stmt = $pdo->prepare("
        INSERT INTO subscription_logs 
            (action, platform, status, message)
        VALUES 
            ('auto_expire_check', 'system', 'success', :message)
    ");
    $stmt->execute([
        'message' => "Expired subscriptions checked at " . date('Y-m-d H:i:s') . ". Downgraded: $downgraded, Deactivated: $deactivated"
    ]);
    
    $logMessage .= "✅ Cleanup completed successfully\n";
    $status = 'success';
    
} catch (PDOException $e) {
    $logMessage .= "❌ Error: " . $e->getMessage() . "\n";
    $status = 'failed';
    
    // Log the error
    try {
        $stmt = $pdo->prepare("
            INSERT INTO subscription_logs 
                (action, platform, status, message)
            VALUES 
                ('auto_expire_check', 'system', 'failed', :message)
        ");
        $stmt->execute([
            'message' => "Error during cleanup: " . $e->getMessage()
        ]);
    } catch (PDOException $logError) {
        // Can't log the error
        $logMessage .= "Failed to log error: " . $logError->getMessage() . "\n";
    }
}

// Write to log file
$logFile = __DIR__ . '/logs/subscription_cleanup.log';
$logDir = dirname($logFile);

if (!is_dir($logDir)) {
    mkdir($logDir, 0755, true);
}

file_put_contents($logFile, $logMessage, FILE_APPEND);

// Also output to console (for manual runs)
echo $logMessage;

// Return appropriate exit code
exit($status === 'success' ? 0 : 1);
