<?php
// Update ALL campaigns to have completely random dates between Dec 11-16 for each delivery

$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 "🎲 RANDOMIZING ALL CAMPAIGNS - Dec 11-16 Random Dates\n";
    echo str_repeat("=", 60) . "\n";
    
    // Get all campaigns first
    $campaigns = $pdo->query("SELECT COUNT(*) as total FROM scheduled_messages")->fetch();
    $batches = $pdo->query("SELECT COUNT(*) as total FROM scheduled_message_batches")->fetch();
    
    echo "📊 Found {$campaigns['total']} campaigns with {$batches['total']} delivery batches\n\n";
    
    // 1. Update ALL campaign created dates to random Dec 11-16
    echo "Step 1: Randomizing ALL campaign created dates...\n";
    $update_campaign_created = "
    UPDATE scheduled_messages 
    SET created_at = CONCAT(
        '2025-12-',
        LPAD(11 + FLOOR(RAND() * 6), 2, '0'),  -- Random day 11-16
        ' ',
        LPAD(FLOOR(RAND() * 24), 2, '0'),      -- Random hour 0-23
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0'),      -- Random minute 0-59
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0')       -- Random second 0-59
    )";
    
    $result1 = $pdo->exec($update_campaign_created);
    echo "✅ Updated {$result1} campaign created dates\n\n";
    
    // 2. Update ALL batch created dates to random Dec 11-16
    echo "Step 2: Randomizing ALL batch created dates...\n";
    $update_batch_created = "
    UPDATE scheduled_message_batches 
    SET created_at = CONCAT(
        '2025-12-',
        LPAD(11 + FLOOR(RAND() * 6), 2, '0'),  -- Random day 11-16
        ' ',
        LPAD(FLOOR(RAND() * 24), 2, '0'),      -- Random hour 0-23
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0'),      -- Random minute 0-59
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0')       -- Random second 0-59
    )";
    
    $result2 = $pdo->exec($update_batch_created);
    echo "✅ Updated {$result2} batch created dates\n\n";
    
    // 3. Update ALL scheduled_for dates to COMPLETELY RANDOM Dec 11-16
    echo "Step 3: Randomizing ALL delivery scheduled dates...\n";
    $update_scheduled = "
    UPDATE scheduled_message_batches 
    SET scheduled_for = CONCAT(
        '2025-12-',
        LPAD(11 + FLOOR(RAND() * 6), 2, '0'),  -- Random day 11-16
        ' ',
        LPAD(9 + FLOOR(RAND() * 9), 2, '0'),   -- Random hour 9-17
        ':',
        CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END,  -- Random minute 00/30
        ':00'
    )";
    
    $result3 = $pdo->exec($update_scheduled);
    echo "✅ Updated {$result3} delivery scheduled dates\n\n";
    
    // 4. Update ALL sent_at dates to match the random pattern
    echo "Step 4: Randomizing ALL sent_at dates...\n";
    $update_sent = "
    UPDATE scheduled_message_batches 
    SET sent_at = CONCAT(
        '2025-12-',
        LPAD(11 + FLOOR(RAND() * 6), 2, '0'),  -- Random day 11-16
        ' ',
        LPAD(9 + FLOOR(RAND() * 9), 2, '0'),   -- Random hour 9-17
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0'),      -- Random minute 0-59
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0')       -- Random second 0-59
    )
    WHERE sent_at IS NOT NULL";
    
    $result4 = $pdo->exec($update_sent);
    echo "✅ Updated {$result4} sent_at dates\n\n";
    
    echo str_repeat("=", 60) . "\n";
    echo "📊 FINAL RANDOM RESULTS:\n";
    echo str_repeat("=", 60) . "\n";
    
    // Show sample results from all campaigns
    $stmt = $pdo->query("
        SELECT 
            sm.id as schedule_id,
            sm.created_at,
            smb.batch_number,
            smb.scheduled_for,
            smb.sent_at,
            smb.status
        FROM scheduled_messages sm
        JOIN scheduled_message_batches smb ON sm.id = smb.scheduled_message_id
        ORDER BY sm.id DESC, smb.batch_number
        LIMIT 20
    ");
    
    $current_schedule = null;
    $count = 0;
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC) && $count < 20) {
        if ($current_schedule !== $row['schedule_id']) {
            $current_schedule = $row['schedule_id'];
            echo "\n🎯 Schedule #{$row['schedule_id']} (Created: {$row['created_at']}):\n";
        }
        $sent_info = $row['sent_at'] ? " (Sent: {$row['sent_at']})" : "";
        echo "  └─ Delivery {$row['batch_number']}: {$row['scheduled_for']} [{$row['status']}]{$sent_info}\n";
        $count++;
    }
    
    echo "\n🎉 ALL CAMPAIGNS RANDOMIZED!\n";
    echo "🎲 Every delivery now has random dates between Dec 11-16\n";
    echo "📅 No more sequential patterns - completely random!\n";
    echo "🔄 Hard refresh your browser (Ctrl+F5) to see changes!\n";
    
    // Show summary statistics
    $date_stats = $pdo->query("
        SELECT 
            DATE(scheduled_for) as schedule_date,
            COUNT(*) as delivery_count
        FROM scheduled_message_batches 
        WHERE scheduled_for BETWEEN '2025-12-11' AND '2025-12-16'
        GROUP BY DATE(scheduled_for)
        ORDER BY schedule_date
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "\n📈 DISTRIBUTION SUMMARY:\n";
    echo str_repeat("-", 30) . "\n";
    foreach ($date_stats as $stat) {
        echo "Dec {$stat['schedule_date']}: {$stat['delivery_count']} deliveries\n";
    }
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>