<?php
// Script to create some failed records for testing random distribution

// 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 "Creating test failed records for random distribution...\n";
    
    // Get a campaign with data
    $stmt = $pdo->prepare("
        SELECT campaign_id, user_id, COUNT(*) as total_records
        FROM campaign_user_data 
        GROUP BY campaign_id, user_id 
        HAVING total_records > 1000
        LIMIT 1
    ");
    $stmt->execute();
    $campaign = $stmt->fetch(PDO::FETCH_OBJ);
    
    if (!$campaign) {
        echo "No suitable campaign found with enough records.\n";
        exit;
    }
    
    echo "Using Campaign ID: {$campaign->campaign_id}, User ID: {$campaign->user_id}\n";
    echo "Total records: {$campaign->total_records}\n";
    
    // Calculate how many failed records to create (about 10% of total)
    $failed_count = floor($campaign->total_records * 0.1);
    $failed_count = min($failed_count, 500); // Limit to 500 for testing
    
    echo "Creating {$failed_count} failed records...\n";
    
    // Update random records to failed status
    $stmt = $pdo->prepare("
        UPDATE campaign_user_data 
        SET status = 'failed' 
        WHERE campaign_id = ? 
          AND user_id = ? 
          AND status = 'sent'
        ORDER BY RAND() 
        LIMIT ?
    ");
    
    $stmt->execute([$campaign->campaign_id, $campaign->user_id, $failed_count]);
    
    echo "Updated {$stmt->rowCount()} records to failed status.\n";
    
    // Verify the distribution
    $stmt = $pdo->prepare("
        SELECT status, COUNT(*) as count
        FROM campaign_user_data 
        WHERE campaign_id = ? AND user_id = ?
        GROUP BY status
    ");
    $stmt->execute([$campaign->campaign_id, $campaign->user_id]);
    
    echo "\nFinal Status Distribution:\n";
    while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
        echo "- {$row->status}: {$row->count}\n";
    }
    
    echo "\nNow test the view details for campaign {$campaign->campaign_id} to see random failed distribution!\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>