<?php
// Test script to verify complete random distribution for both sent and failed records

// Database connection
$host = '162.215.11.244';
$username = 'ac3_marketing';
$password = 'WsdF%!fh{nfEtWpz';
$database = 'ac3_marketing_portal';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$database", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Testing Complete Random Distribution for Sent & Failed Records\n";
    echo "===========================================================\n";
    
    // Get a campaign with both sent and failed records
    $stmt = $pdo->prepare("
        SELECT campaign_id, user_id,
               SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent_count,
               SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_count,
               COUNT(*) as total_records
        FROM campaign_user_data 
        GROUP BY campaign_id, user_id 
        HAVING sent_count > 100 AND failed_count > 10 AND total_records > 1000
        LIMIT 1
    ");
    $stmt->execute();
    $campaign = $stmt->fetch(PDO::FETCH_OBJ);
    
    if (!$campaign) {
        echo "No suitable campaign found with both sent and failed records.\n";
        exit;
    }
    
    echo "Campaign ID: {$campaign->campaign_id}, User ID: {$campaign->user_id}\n";
    echo "Total Records: {$campaign->total_records}\n";
    echo "Sent Records: {$campaign->sent_count}\n"; 
    echo "Failed Records: {$campaign->failed_count}\n\n";
    
    // Get total batches
    $stmt = $pdo->prepare("
        SELECT COUNT(*) as batch_count
        FROM scheduled_message_batches smb
        JOIN scheduled_messages sm ON sm.id = smb.scheduled_message_id
        WHERE sm.campaign_id = ?
    ");
    $stmt->execute([$campaign->campaign_id]);
    $total_batches = $stmt->fetch(PDO::FETCH_OBJ)->batch_count;
    
    echo "Total Batches: {$total_batches}\n\n";
    
    // Simulate random distribution multiple times
    echo "Random Distribution Simulation (3 runs):\n";
    echo "======================================\n";
    
    for ($run = 1; $run <= 3; $run++) {
        echo "Run {$run}:\n";
        echo "------\n";
        
        // Get all record IDs by status
        $stmt = $pdo->prepare("
            SELECT id, status
            FROM campaign_user_data 
            WHERE campaign_id = ? AND user_id = ?
            ORDER BY id
        ");
        $stmt->execute([$campaign->campaign_id, $campaign->user_id]);
        $all_records = $stmt->fetchAll(PDO::FETCH_OBJ);
        
        // Separate by status
        $sent_ids = [];
        $failed_ids = [];
        
        foreach ($all_records as $record) {
            if ($record->status == 'sent') {
                $sent_ids[] = $record->id;
            } elseif ($record->status == 'failed') {
                $failed_ids[] = $record->id;
            }
        }
        
        // Randomly distribute to batches
        $batch_distribution = [];
        for ($i = 1; $i <= $total_batches; $i++) {
            $batch_distribution[$i] = ['sent' => 0, 'failed' => 0, 'total' => 0];
        }
        
        // Distribute sent records randomly
        foreach ($sent_ids as $id) {
            $random_batch = rand(1, $total_batches);
            $batch_distribution[$random_batch]['sent']++;
            $batch_distribution[$random_batch]['total']++;
        }
        
        // Distribute failed records randomly
        foreach ($failed_ids as $id) {
            $random_batch = rand(1, $total_batches);
            $batch_distribution[$random_batch]['failed']++;
            $batch_distribution[$random_batch]['total']++;
        }
        
        // Display results
        echo "Batch | Sent | Failed | Total | % Sent | % Failed\n";
        echo "------|------|--------|-------|--------|----------\n";
        
        $total_check_sent = 0;
        $total_check_failed = 0;
        $total_check_all = 0;
        
        for ($batch = 1; $batch <= $total_batches; $batch++) {
            $sent = $batch_distribution[$batch]['sent'];
            $failed = $batch_distribution[$batch]['failed'];
            $total = $batch_distribution[$batch]['total'];
            
            $sent_pct = $total > 0 ? round(($sent / $total) * 100, 1) : 0;
            $failed_pct = $total > 0 ? round(($failed / $total) * 100, 1) : 0;
            
            printf("  %d   | %4d | %6d | %5d | %5.1f%% | %7.1f%%\n", 
                $batch, $sent, $failed, $total, $sent_pct, $failed_pct);
            
            $total_check_sent += $sent;
            $total_check_failed += $failed;
            $total_check_all += $total;
        }
        
        echo "------|------|--------|-------|--------|----------\n";
        printf("Total | %4d | %6d | %5d |\n", $total_check_sent, $total_check_failed, $total_check_all);
        
        // Verification
        $sent_match = ($total_check_sent == $campaign->sent_count);
        $failed_match = ($total_check_failed == $campaign->failed_count);
        
        echo "Verification: Sent " . ($sent_match ? "✅" : "❌") . 
             " | Failed " . ($failed_match ? "✅" : "❌") . "\n\n";
    }
    
    echo "Key Points:\n";
    echo "- Each run shows different random distribution\n";
    echo "- Some batches might have 0 records of a status\n";
    echo "- Distribution is completely unpredictable\n";
    echo "- Export will match exactly what's shown in view details\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>