-- Update existing scheduled dates to random dates between Dec 11-16, 2025
-- This will randomize all existing batch schedules

-- First, let's see what we have
SELECT 
    id,
    scheduled_message_id,
    batch_number,
    scheduled_for,
    status
FROM scheduled_message_batches 
ORDER BY scheduled_message_id, batch_number;

-- Update scheduled dates to sequential random dates between Dec 11-16, 2025
-- Ensures delivery dates progress forward (no going backwards)

-- Method 1: Sequential progression within each campaign
UPDATE scheduled_message_batches smb
JOIN (
    SELECT 
        scheduled_message_id,
        id,
        batch_number,
        ROW_NUMBER() OVER (PARTITION BY scheduled_message_id ORDER BY batch_number) as seq_num,
        COUNT(*) OVER (PARTITION BY scheduled_message_id) as total_batches
    FROM scheduled_message_batches
) ranked ON smb.id = ranked.id
SET smb.scheduled_for = CONCAT(
    '2025-12-',
    LPAD(10 + ranked.seq_num + FLOOR(RAND() * (7 - ranked.total_batches)), 2, '0'),  -- Sequential days starting from 11, with some randomness
    ' ',
    LPAD(9 + FLOOR(RAND() * 9), 2, '0'),   -- Random hour between 09-17
    ':',
    CASE WHEN RAND() > 0.5 THEN '30' ELSE '00' END,  -- Random minute (00 or 30)
    ':00'
);

-- Verify the changes
SELECT 
    id,
    scheduled_message_id,
    batch_number,
    scheduled_for,
    status
FROM scheduled_message_batches 
ORDER BY scheduled_message_id, batch_number;