<?php
// Update both scheduled dates AND created dates for all campaigns

$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 "✅ Connected to database successfully!\n\n";
    
    // 1. Update scheduled_messages created_at dates
    echo "📅 STEP 1: Updating campaign created dates...\n";
    echo str_repeat("-", 50) . "\n";
    
    $update_created = "
    UPDATE scheduled_messages 
    SET created_at = CONCAT(
        '2025-12-',
        LPAD(11 + FLOOR(RAND() * 6), 2, '0'),
        ' ',
        LPAD(FLOOR(RAND() * 24), 2, '0'),
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0'),
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0')
    )";
    
    $result1 = $pdo->exec($update_created);
    echo "✅ Updated {$result1} campaign created dates\n\n";
    
    // 2. Update scheduled_message_batches created_at dates  
    echo "📅 STEP 2: Updating batch created dates...\n";
    echo str_repeat("-", 50) . "\n";
    
    $update_batch_created = "
    UPDATE scheduled_message_batches 
    SET created_at = CONCAT(
        '2025-12-',
        LPAD(11 + FLOOR(RAND() * 6), 2, '0'),
        ' ',
        LPAD(FLOOR(RAND() * 24), 2, '0'),
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0'),
        ':',
        LPAD(FLOOR(RAND() * 60), 2, '0')
    )";
    
    $result2 = $pdo->exec($update_batch_created);
    echo "✅ Updated {$result2} batch created dates\n\n";
    
    // 3. Update scheduled delivery dates (refresh the previous update)
    echo "📅 STEP 3: Updating scheduled delivery dates...\n";
    echo str_repeat("-", 50) . "\n";
    
    $update_scheduled = "
    UPDATE scheduled_message_batches smb
    JOIN (
        SELECT 
            id,
            scheduled_message_id,
            batch_number,
            ROW_NUMBER() OVER (PARTITION BY scheduled_message_id ORDER BY batch_number) as seq_num
        FROM scheduled_message_batches
    ) ranked ON smb.id = ranked.id
    SET smb.scheduled_for = CONCAT(
        '2025-12-',
        LPAD(10 + ranked.seq_num, 2, '0'),
        ' ',
        LPAD(9 + FLOOR(RAND() * 9), 2, '0'),
        ':',
        CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END,
        ':00'
    )";
    
    $result3 = $pdo->exec($update_scheduled);
    echo "✅ Updated {$result3} scheduled delivery dates\n\n";
    
    // Show final results
    echo str_repeat("=", 60) . "\n";
    echo "📊 FINAL RESULTS:\n";
    echo str_repeat("=", 60) . "\n";
    
    $stmt = $pdo->query("
        SELECT 
            sm.id as schedule_id,
            sm.created_at as campaign_created,
            smb.batch_number,
            smb.scheduled_for,
            smb.created_at as batch_created,
            smb.status
        FROM scheduled_messages sm
        JOIN scheduled_message_batches smb ON sm.id = smb.scheduled_message_id
        ORDER BY sm.id, smb.batch_number
    ");
    
    $current_schedule = null;
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        if ($current_schedule !== $row['schedule_id']) {
            $current_schedule = $row['schedule_id'];
            echo "\n🎯 Schedule #{$row['schedule_id']} (Created: {$row['campaign_created']}):\n";
        }
        echo "  └─ Delivery {$row['batch_number']}: {$row['scheduled_for']} [{$row['status']}]\n";
    }
    
    echo "\n🎉 ALL DATES UPDATED!\n";
    echo "✅ Campaign creation dates: Random Dec 11-16\n"; 
    echo "✅ Delivery schedule dates: Sequential Dec 11, 12, 13, 14...\n";
    echo "✅ Batch creation dates: Random Dec 11-16\n";
    
} catch (PDOException $e) {
    echo "❌ Database error: " . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>