<?php
defined('BASEPATH') OR exit('No direct script access allowed');

// Load CodeIgniter to use database configuration
require_once('index.php');

// Get CI instance
$CI =& get_instance();
$CI->load->database();

echo "Updating scheduled delivery dates to sequential progression...\n\n";

// Show current data
echo "BEFORE UPDATE:\n";
$query = $CI->db->query("
    SELECT 
        smb.id,
        smb.scheduled_message_id,
        smb.batch_number,
        smb.scheduled_for,
        smb.status,
        sm.message_content
    FROM scheduled_message_batches smb
    LEFT JOIN scheduled_messages sm ON smb.scheduled_message_id = sm.id
    ORDER BY smb.scheduled_message_id, smb.batch_number
");

foreach ($query->result() as $row) {
    echo "Batch {$row->batch_number} (ID: {$row->id}): {$row->scheduled_for}\n";
}

echo "\n" . str_repeat("-", 50) . "\n";
echo "EXECUTING UPDATE...\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 = $CI->db->query($update_sql);

if ($result) {
    $affected_rows = $CI->db->affected_rows();
    echo "SUCCESS: Updated {$affected_rows} delivery batches\n\n";
    
    // Show updated data
    echo "AFTER UPDATE:\n";
    $query2 = $CI->db->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
    ");
    
    foreach ($query2->result() as $row) {
        echo "Batch {$row->batch_number} (ID: {$row->id}): {$row->scheduled_for} [{$row->status}]\n";
    }
    
    echo "\n✅ All delivery dates updated successfully!\n";
    echo "📅 Dates now progress sequentially: Dec 11, 12, 13, 14, 15, 16...\n";
    
} else {
    echo "❌ ERROR: Failed to update delivery dates\n";
    echo "Database error: " . $CI->db->error()['message'] . "\n";
}

echo "\nDone.\n";
?>