<?php
require_once '../includes/db_config.php';


try {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT s.*, u.name as customer_name, u.email as customer_email,
               DATEDIFF(s.end_date, CURDATE()) as days_left
        FROM subscriptions s 
        LEFT JOIN users u ON s.user_id = u.id 
        WHERE s.status = 'active' 
        AND s.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
    ");
    $stmt->execute();
    $expiring_subs = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($expiring_subs as $sub) {
        $days_left = $sub['days_left'];
        $customer_name = $sub['customer_name'];
        $plan_name = $sub['plan_name'];
        $end_date = date('Y-m-d', strtotime($sub['end_date']));
        
        $notification_title = "Subscription Expiring Soon";
        $notification_message = "Customer {$customer_name}'s {$plan_name} subscription will expire in {$days_left} day(s) on {$end_date}";
        
        $check_stmt = $pdo->prepare("
            SELECT id FROM notifications 
            WHERE title = ? AND message LIKE ? AND created_at >= CURDATE()
        ");
        $check_stmt->execute([$notification_title, "%{$customer_name}%{$plan_name}%"]);
        
        if ($check_stmt->rowCount() == 0) {
            $insert_stmt = $pdo->prepare("
                INSERT INTO notifications (title, message, type, user_id) 
                VALUES (?, ?, 'warning', NULL)
            ");
            $insert_stmt->execute([$notification_title, $notification_message]);
            
            echo "Notification created for {$customer_name} - {$plan_name}\n";
        }
    }
    
    $stmt = $pdo->prepare("
        UPDATE subscriptions 
        SET status = 'expired' 
        WHERE status = 'active' AND end_date < CURDATE()
    ");
    $stmt->execute();
    $expired_count = $stmt->rowCount();
    
    if ($expired_count > 0) {
        $notification_title = "Subscriptions Expired";
        $notification_message = "{$expired_count} subscription(s) have expired and been automatically updated";
        
        $insert_stmt = $pdo->prepare("
            INSERT INTO notifications (title, message, type, user_id) 
            VALUES (?, ?, 'info', NULL)
        ");
        $insert_stmt->execute([$notification_title, $notification_message]);
        
        echo "Updated {$expired_count} expired subscriptions\n";
    }
    
    echo "Subscription check completed successfully\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
    error_log("Subscription check error: " . $e->getMessage());
}
?>
