<?php
// Test batch statistics calculation

$host = '162.215.11.244';
$username = 'ac3_marketing_portal';
$password = '=Y^3GIyrG;J)TV]z';
$database = 'ac3_marketing_portal';
$port = 3306;

try {
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$database;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "🧮 TESTING BATCH STATISTICS CALCULATION\n";
    echo str_repeat("=", 50) . "\n";
    
    $campaign_id = 27; // Using campaign 27
    $user_id = 2; // Assuming user ID 2
    
    // Get batch information for campaign 27
    $batches = $pdo->query("
        SELECT smb.*, sm.total_recipients
        FROM scheduled_message_batches smb
        JOIN scheduled_messages sm ON sm.id = smb.scheduled_message_id
        WHERE sm.campaign_id = {$campaign_id}
        ORDER BY smb.batch_number
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($batches)) {
        echo "❌ No batches found for campaign {$campaign_id}\n";
        exit;
    }
    
    // Check if we have campaign_user_data for testing
    $total_user_data = $pdo->query("
        SELECT COUNT(*) as count 
        FROM campaign_user_data 
        WHERE campaign_id = {$campaign_id} AND user_id = {$user_id}
    ")->fetch()['count'];
    
    echo "📊 Campaign {$campaign_id} - User {$user_id}:\n";
    echo "   Total User Data Records: " . number_format($total_user_data) . "\n";
    echo "   Total Recipients (Expected): " . number_format($batches[0]['total_recipients']) . "\n\n";
    
    if ($total_user_data == 0) {
        echo "⚠️  No campaign_user_data found. Creating sample data...\n";
        
        // Create sample data with mixed statuses
        $total_to_create = $batches[0]['total_recipients'];
        
        for ($i = 1; $i <= $total_to_create; $i++) {
            // 80% sent, 15% failed, 5% pending
            $status = 'sent';
            if ($i % 20 < 3) $status = 'failed';      // 15%
            if ($i % 20 >= 19) $status = 'pending';   // 5%
            
            $pdo->exec("
                INSERT IGNORE INTO campaign_user_data 
                (campaign_id, user_id, master_data_id, status, assigned_at) 
                VALUES ({$campaign_id}, {$user_id}, {$i}, '{$status}', NOW())
            ");
        }
        
        echo "✅ Created " . number_format($total_to_create) . " sample records\n\n";
    }
    
    // Now test each batch's statistics
    foreach ($batches as $batch) {
        $batch_size = $batch['batch_size'];
        $batch_number = $batch['batch_number'];
        
        // Calculate batch offset
        $batch_start_offset = ($batch_number - 1) * $batch_size;
        
        echo "🎯 Testing Batch {$batch_number} (ID: {$batch['id']}):\n";
        echo "   Batch Size: " . number_format($batch_size) . "\n";
        echo "   Record Range: " . number_format($batch_start_offset + 1) . " - " . number_format($batch_start_offset + $batch_size) . "\n";
        
        // Test the same logic as in the controller
        $batch_stats = $pdo->query("
            SELECT status, COUNT(*) as count
            FROM (
                SELECT status 
                FROM campaign_user_data 
                WHERE campaign_id = {$campaign_id} 
                  AND user_id = {$user_id}
                ORDER BY id ASC 
                LIMIT {$batch_size} OFFSET {$batch_start_offset}
            ) as batch_data
            GROUP BY status
        ")->fetchAll(PDO::FETCH_ASSOC);
        
        $sent_count = 0;
        $failed_count = 0;
        $pending_count = 0;
        
        foreach ($batch_stats as $stat) {
            if ($stat['status'] == 'sent') $sent_count = $stat['count'];
            elseif ($stat['status'] == 'failed') $failed_count = $stat['count'];  
            elseif ($stat['status'] == 'pending') $pending_count = $stat['count'];
        }
        
        $total_batch = $sent_count + $failed_count + $pending_count;
        
        echo "   📈 Statistics:\n";
        echo "     • Total Assigned: " . number_format($total_batch) . "\n";
        echo "     • Delivered: " . number_format($sent_count) . "\n";
        echo "     • Failed: " . number_format($failed_count) . "\n";
        echo "     • Pending: " . number_format($pending_count) . "\n";
        
        // Calculate percentages
        if ($total_batch > 0) {
            $sent_pct = round(($sent_count / $total_batch) * 100, 1);
            $failed_pct = round(($failed_count / $total_batch) * 100, 1);
            echo "     • Success Rate: {$sent_pct}%\n";
            echo "     • Failure Rate: {$failed_pct}%\n";
        }
        
        echo "\n";
    }
    
    echo "✅ BATCH STATISTICS TEST COMPLETE!\n";
    echo "🔄 Now refresh the campaign view and each delivery should show correct counts\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>