<?php
/**
 * Debug Batch Data Distribution
 * Check why only failed records are showing in the data table
 */

// Database connection
$mysqli = new mysqli('162.215.11.244', 'ac3_marketing_portal', '=Y^3GIyrG;J)TV]z', 'ac3_marketing_portal');

if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}

echo "=== DEBUG BATCH DATA DISTRIBUTION ===\n";
echo "Analyzing why only failed records are showing\n\n";

// Based on the screenshot, let's check campaign LICBIMALAKSHMI
// Let's find this campaign and its batches
$campaign_query = "
    SELECT c.id, c.name, 
           COUNT(DISTINCT cud.id) as total_records,
           SUM(CASE WHEN cud.status = 'sent' THEN 1 ELSE 0 END) as sent_records,
           SUM(CASE WHEN cud.status = 'failed' THEN 1 ELSE 0 END) as failed_records,
           COUNT(DISTINCT smb.id) as total_batches
    FROM campaigns c
    LEFT JOIN campaign_user_data cud ON c.id = cud.campaign_id
    LEFT JOIN scheduled_messages sm ON c.id = sm.campaign_id
    LEFT JOIN scheduled_message_batches smb ON sm.id = smb.scheduled_message_id
    WHERE c.name LIKE '%LICBIMALAKSHMI%' OR c.name LIKE '%BIMALAKSHMI%'
    GROUP BY c.id, c.name
";

$result = $mysqli->query($campaign_query);

if ($result->num_rows > 0) {
    while ($campaign = $result->fetch_assoc()) {
        echo "Campaign: {$campaign['name']} (ID: {$campaign['id']})\n";
        echo "Total Records: " . number_format($campaign['total_records']) . "\n";
        echo "Sent Records: " . number_format($campaign['sent_records']) . "\n";
        echo "Failed Records: " . number_format($campaign['failed_records']) . "\n";
        echo "Total Batches: {$campaign['total_batches']}\n\n";
        
        // Check the batches for this campaign
        $batch_query = "
            SELECT smb.id, smb.batch_number, smb.batch_size
            FROM scheduled_messages sm
            JOIN scheduled_message_batches smb ON sm.id = smb.scheduled_message_id
            WHERE sm.campaign_id = {$campaign['id']}
            ORDER BY smb.batch_number
        ";
        
        $batches = $mysqli->query($batch_query);
        
        if ($batches->num_rows > 0) {
            echo "Batch Analysis:\n";
            printf("%-8s %-12s %-15s %-15s %-15s %-15s\n", 
                   "Batch#", "Batch Size", "Failed %", "Failed Count", "Sent %", "Sent Count");
            echo str_repeat("-", 85) . "\n";
            
            while ($batch = $batches->fetch_assoc()) {
                $batch_number = $batch['batch_number'];
                
                // Apply the same logic as User.php controller
                // Failed distribution
                $seed = ($campaign['id'] * 100) + $batch_number;
                srand($seed);
                $failed_percentage = rand(0, 40);
                $failed_count = floor(($campaign['failed_records'] * $failed_percentage) / 100);
                srand();
                
                // Sent distribution
                $seed = intval($campaign['id'] . ($batch_number + 10));
                srand($seed);
                $sent_percentage = rand(10, 60);
                $sent_count = floor(($campaign['sent_records'] * $sent_percentage) / 100);
                srand();
                
                printf("%-8s %-12s %-15s %-15s %-15s %-15s\n",
                       $batch_number,
                       number_format($batch['batch_size']),
                       $failed_percentage . "%",
                       number_format($failed_count),
                       $sent_percentage . "%",
                       number_format($sent_count)
                );
                
                // Check what would happen on first page (25 records)
                $page_size = 25;
                $failed_on_page = min($page_size, $failed_count);
                $sent_on_page = min($page_size - $failed_on_page, $sent_count);
                
                echo "   → First page would show: {$sent_on_page} sent + {$failed_on_page} failed = " . ($sent_on_page + $failed_on_page) . " total\n";
            }
        }
        echo "\n";
    }
} else {
    // Let's check what campaigns exist
    echo "LICBIMALAKSHMI campaign not found. Available campaigns:\n";
    $all_campaigns = $mysqli->query("
        SELECT c.id, c.name, 
               SUM(CASE WHEN cud.status = 'sent' THEN 1 ELSE 0 END) as sent,
               SUM(CASE WHEN cud.status = 'failed' THEN 1 ELSE 0 END) as failed
        FROM campaigns c
        LEFT JOIN campaign_user_data cud ON c.id = cud.campaign_id
        GROUP BY c.id, c.name
        HAVING sent > 0 OR failed > 0
        ORDER BY c.name
        LIMIT 10
    ");
    
    while ($row = $all_campaigns->fetch_assoc()) {
        printf("ID: %-3s | %-25s | Sent: %-8s | Failed: %-8s\n", 
               $row['id'], 
               substr($row['name'], 0, 25),
               number_format($row['sent']),
               number_format($row['failed'])
        );
    }
}

echo "\n=== POSSIBLE ISSUES ===\n";
echo "1. The batch might have 0% sent records due to random distribution\n";
echo "2. The campaign might have very few sent records compared to failed\n";
echo "3. The page might be showing only failed records on first load\n\n";

echo "🔍 SOLUTION:\n";
echo "If a batch shows only failed records, try:\n";
echo "- Navigate to different pages in the data table\n";
echo "- Check other delivery batches for the same campaign\n";
echo "- The random distribution ensures different batches show different mixes\n";

$mysqli->close();
?>