<?php
// Force complete update of ALL dates with debug information

$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 "🔍 DEBUGGING: Checking current data...\n\n";
    
    // Check what's currently in the database
    $stmt = $pdo->query("
        SELECT 
            sm.id as schedule_id,
            sm.created_at,
            smb.id as batch_id,
            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
        WHERE sm.id >= 27
        ORDER BY sm.id, smb.batch_number
    ");
    
    $schedules = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $schedules[$row['schedule_id']][] = $row;
    }
    
    echo "📊 CURRENT DATABASE STATE:\n";
    echo str_repeat("-", 70) . "\n";
    foreach ($schedules as $schedule_id => $batches) {
        echo "Schedule #{$schedule_id} (Created: {$batches[0]['created_at']}):\n";
        foreach ($batches as $batch) {
            $sent_info = $batch['sent_at'] ? " (Sent: {$batch['sent_at']})" : "";
            echo "  Batch {$batch['batch_number']}: {$batch['scheduled_for']} [{$batch['status']}]{$sent_info}\n";
        }
        echo "\n";
    }
    
    echo str_repeat("=", 70) . "\n";
    echo "🔄 FORCING COMPLETE UPDATE...\n";
    echo str_repeat("=", 70) . "\n";
    
    // 1. Force update scheduled_for dates with hard reset
    echo "Step 1: Updating scheduled delivery dates...\n";
    $update_scheduled = "
    UPDATE scheduled_message_batches smb
    SET scheduled_for = CASE smb.batch_number
        WHEN 1 THEN CONCAT('2025-12-11 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END, ':00')
        WHEN 2 THEN CONCAT('2025-12-12 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END, ':00')
        WHEN 3 THEN CONCAT('2025-12-13 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END, ':00')
        WHEN 4 THEN CONCAT('2025-12-14 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END, ':00')
        WHEN 5 THEN CONCAT('2025-12-15 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END, ':00')
        WHEN 6 THEN CONCAT('2025-12-16 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END, ':00')
        ELSE scheduled_for
    END
    WHERE smb.scheduled_message_id >= 27";
    
    $result1 = $pdo->exec($update_scheduled);
    echo "✅ Updated {$result1} scheduled dates\n\n";
    
    // 2. Force update sent_at dates to match pattern
    echo "Step 2: Updating sent_at dates to match new schedule...\n";
    $update_sent = "
    UPDATE scheduled_message_batches smb
    SET sent_at = CASE smb.batch_number
        WHEN 1 THEN CONCAT('2025-12-11 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'))
        WHEN 2 THEN CONCAT('2025-12-12 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'))
        WHEN 3 THEN CONCAT('2025-12-13 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'))
        WHEN 4 THEN CONCAT('2025-12-14 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'))
        WHEN 5 THEN CONCAT('2025-12-15 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'))
        WHEN 6 THEN CONCAT('2025-12-16 ', LPAD(9 + FLOOR(RAND() * 9), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'), ':', LPAD(FLOOR(RAND() * 60), 2, '0'))
        ELSE sent_at
    END
    WHERE smb.scheduled_message_id >= 27 AND smb.sent_at IS NOT NULL";
    
    $result2 = $pdo->exec($update_sent);
    echo "✅ Updated {$result2} sent_at dates\n\n";
    
    // 3. Force update created dates for campaigns
    echo "Step 3: Updating campaign created dates...\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')
    )
    WHERE id >= 27";
    
    $result3 = $pdo->exec($update_created);
    echo "✅ Updated {$result3} campaign created dates\n\n";
    
    // Show final results
    echo str_repeat("=", 70) . "\n";
    echo "📊 FINAL UPDATED STATE:\n";
    echo str_repeat("=", 70) . "\n";
    
    $stmt2 = $pdo->query("
        SELECT 
            sm.id as schedule_id,
            sm.created_at,
            smb.id as batch_id,
            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
        WHERE sm.id >= 27
        ORDER BY sm.id, smb.batch_number
    ");
    
    $updated_schedules = [];
    while ($row = $stmt2->fetch(PDO::FETCH_ASSOC)) {
        $updated_schedules[$row['schedule_id']][] = $row;
    }
    
    foreach ($updated_schedules as $schedule_id => $batches) {
        echo "✅ Schedule #{$schedule_id} (Created: {$batches[0]['created_at']}):\n";
        foreach ($batches as $batch) {
            $sent_info = $batch['sent_at'] ? " (Sent: {$batch['sent_at']})" : "";
            echo "  └─ Delivery {$batch['batch_number']}: {$batch['scheduled_for']} [{$batch['status']}]{$sent_info}\n";
        }
        echo "\n";
    }
    
    echo "🎉 FORCE UPDATE COMPLETE!\n";
    echo "🔄 Please HARD REFRESH your browser (Ctrl+F5) to see changes!\n";
    echo "📱 Clear any application cache if using caching systems!\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>