<?php
/**
 * Auto-Suspension Script for Expired Subscriptions
 * 
 * This script should be run via cron job every hour/day to automatically
 * suspend schools with expired subscriptions.
 * 
 * Cron job example (runs every hour):
 * 0 * * * * php /path/to/auto_suspend_expired.php
 * 
 * Or daily at midnight:
 * 0 0 * * * php /path/to/auto_suspend_expired.php
 */

require_once 'config.php';

echo "=== Auto-Suspension Script Started: " . date('Y-m-d H:i:s') . " ===\n";

if (!$conn || !($conn instanceof mysqli)) {
    error_log("Database connection not available in auto_suspend_expired.php");
    echo "ERROR: Database connection failed\n";
    exit(1);
}

try {
    // Find schools with expired subscriptions that are not already suspended
    $sql = "SELECT school_id, school_name, subscription_expiry_date, subscription_status 
            FROM schools 
            WHERE subscription_expiry_date IS NOT NULL 
            AND subscription_expiry_date < CURDATE() 
            AND subscription_status != 'suspended'";
    
    $result = $conn->query($sql);
    
    if ($result && $result->num_rows > 0) {
        echo "Found " . $result->num_rows . " schools with expired subscriptions\n";
        
        $suspended_count = 0;
        $already_suspended_count = 0;
        $error_count = 0;
        
        while ($school = $result->fetch_assoc()) {
            $school_id = $school['school_id'];
            $school_name = $school['school_name'];
            $expiry_date = $school['subscription_expiry_date'];
            
            echo "Processing: $school_name (ID: $school_id, Expired: $expiry_date)\n";
            
            // Start transaction for this school
            $conn->begin_transaction();
            
            try {
                // Check if school is already suspended (double-check)
                $check_sql = "SELECT COUNT(*) as user_count FROM users WHERE school_id = ? AND status != 'suspended'";
                $check_stmt = $conn->prepare($check_sql);
                $check_stmt->bind_param("i", $school_id);
                $check_stmt->execute();
                $check_result = $check_stmt->get_result();
                $user_count = $check_result->fetch_assoc()['user_count'];
                $check_stmt->close();
                
                if ($user_count == 0) {
                    echo "  -> School already suspended, skipping\n";
                    $already_suspended_count++;
                } else {
                    // Suspend all users in the school
                    $sql_users = "UPDATE users SET status = 'suspended' WHERE school_id = ?";
                    $stmt_users = $conn->prepare($sql_users);
                    $stmt_users->bind_param("i", $school_id);
                    
                    if ($stmt_users->execute()) {
                        $users_updated = $stmt_users->affected_rows;
                        echo "  -> Suspended $users_updated users\n";
                    } else {
                        throw new Exception("Failed to suspend users: " . $stmt_users->error);
                    }
                    $stmt_users->close();
                    
                    // Update school subscription status to suspended
                    $sql_school = "UPDATE schools SET subscription_status = 'suspended' WHERE school_id = ?";
                    $stmt_school = $conn->prepare($sql_school);
                    $stmt_school->bind_param("i", $school_id);
                    
                    if ($stmt_school->execute()) {
                        echo "  -> Updated school subscription status to suspended\n";
                    } else {
                        throw new Exception("Failed to update school status: " . $stmt_school->error);
                    }
                    $stmt_school->close();
                    
                    // Log the suspension
                    error_log("AUTO-SUSPENSION: School '$school_name' (ID: $school_id) automatically suspended due to expired subscription on $expiry_date");
                    
                    $suspended_count++;
                }
                
                $conn->commit();
                echo "  -> Completed successfully\n";
                
            } catch (Exception $e) {
                $conn->rollback();
                echo "  -> ERROR: " . $e->getMessage() . "\n";
                error_log("AUTO-SUSPENSION ERROR for school '$school_name' (ID: $school_id): " . $e->getMessage());
                $error_count++;
            }
        }
        
        echo "\n=== SUMMARY ===\n";
        echo "Schools processed: " . $result->num_rows . "\n";
        echo "Newly suspended: $suspended_count\n";
        echo "Already suspended: $already_suspended_count\n";
        echo "Errors: $error_count\n";
        
        // If we suspended any schools, we could send notifications here
        if ($suspended_count > 0) {
            echo "Schools were automatically suspended due to expired subscriptions.\n";
        }
        
    } else {
        echo "No schools with expired subscriptions found.\n";
    }
    
    // Check for schools coming close to expiry (warning zone)
    $warning_sql = "SELECT school_id, school_name, subscription_expiry_date 
                   FROM schools 
                   WHERE subscription_expiry_date IS NOT NULL 
                   AND subscription_expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                   AND subscription_status != 'suspended'";
    
    $warning_result = $conn->query($warning_sql);
    
    if ($warning_result && $warning_result->num_rows > 0) {
        echo "\n=== WARNING: Schools Expiring Soon ===\n";
        echo "Found " . $warning_result->num_rows . " schools expiring within 7 days:\n";
        
        while ($school = $warning_result->fetch_assoc()) {
            $school_name = $school['school_name'];
            $expiry_date = $school['subscription_expiry_date'];
            echo "- $school_name (expires: $expiry_date)\n";
            
            // Here you could send warning notifications to admins
            // This would require additional notification logic
        }
    }
    
} catch (Exception $e) {
    echo "FATAL ERROR: " . $e->getMessage() . "\n";
    error_log("AUTO-SUSPENSION FATAL ERROR: " . $e->getMessage());
}

// Clean up
if ($conn && $conn->ping()) {
    $conn->close();
}

echo "=== Auto-Suspension Script Completed: " . date('Y-m-d H:i:s') . " ===\n";
?>