<?php
// Direct database connection script for updating delivery dates

$host = '162.215.11.244';
$username = 'ac3_marketing_portal';
$password = '=Y^3GIyrG;J)TV]z';
$database = 'ac3_marketing_portal';
$port = 3306;

try {
    // Connect to database
    $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";
    
    // Show current data
    echo "CURRENT DELIVERY SCHEDULES:\n";
    echo str_repeat("-", 60) . "\n";
    
    $stmt = $pdo->query("
        SELECT 
            smb.id,
            smb.scheduled_message_id,
            smb.batch_number,
            smb.scheduled_for,
            smb.status
        FROM scheduled_message_batches smb
        ORDER BY smb.scheduled_message_id, smb.batch_number
    ");
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Campaign {$row['scheduled_message_id']} - Batch {$row['batch_number']}: {$row['scheduled_for']} [{$row['status']}]\n";
    }
    
    echo "\n" . str_repeat("=", 60) . "\n";
    echo "UPDATING TO SEQUENTIAL DATES (Dec 11, 12, 13, 14...)...\n";
    echo str_repeat("=", 60) . "\n";
    
    // Execute the update
    $update_sql = "
    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'
    )";
    
    $result = $pdo->exec($update_sql);
    
    echo "✅ SUCCESS: Updated {$result} delivery batches\n\n";
    
    // Show updated data
    echo "UPDATED DELIVERY SCHEDULES:\n";
    echo str_repeat("-", 60) . "\n";
    
    $stmt2 = $pdo->query("
        SELECT 
            smb.id,
            smb.scheduled_message_id,
            smb.batch_number,
            smb.scheduled_for,
            smb.status
        FROM scheduled_message_batches smb
        ORDER BY smb.scheduled_message_id, smb.batch_number
    ");
    
    while ($row = $stmt2->fetch(PDO::FETCH_ASSOC)) {
        echo "Campaign {$row['scheduled_message_id']} - Batch {$row['batch_number']}: {$row['scheduled_for']} [{$row['status']}]\n";
    }
    
    echo "\n🎉 ALL DONE!\n";
    echo "📅 Delivery dates now progress sequentially without going backwards!\n";
    echo "⏰ Times are randomized between 09:00-17:30\n";
    
} catch (PDOException $e) {
    echo "❌ Database connection failed: " . $e->getMessage() . "\n";
    exit(1);
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}
?>