<?php
// cron/check_expired_subscriptions.php - Run every hour
require_once '../connection.php';
require_once '../mikrotik_api.php';

// Find expired subscriptions
$stmt = $pdo->prepare("
    SELECT os.*, osp.bandwidth_mbps 
    FROM office_subscriptions os
    JOIN office_subscription_plans osp ON os.plan_id = osp.id
    WHERE os.status = 'active' AND os.end_date < NOW()
");
$stmt->execute();
$expired = $stmt->fetchAll();

foreach ($expired as $sub) {
    try {
        $pdo->beginTransaction();
        
        // Update subscription status
        $stmt = $pdo->prepare("UPDATE office_subscriptions SET status = 'expired' WHERE id = ?");
        $stmt->execute([$sub['id']]);
        
        // Update IP-MAC binding
        $stmt = $pdo->prepare("UPDATE ip_mac_bindings SET is_active = 0 WHERE subscription_id = ?");
        $stmt->execute([$sub['id']]);
        
        // Remove from MikroTik
        removeMikroTikInternetAccess($sub['ip_address'], $sub['mac_address']);
        
        $pdo->commit();
        
        error_log("Subscription {$sub['id']} expired and removed from MikroTik");
        
    } catch (Exception $e) {
        $pdo->rollBack();
        error_log("Failed to expire subscription {$sub['id']}: " . $e->getMessage());
    }
}

echo "Processed " . count($expired) . " expired subscriptions\n";
?>