<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/003_scheduled_posts_utc.php
// Description: Adds scheduled_utc column to amai_scheduled_posts for timezone-aware posting - NUCLEAR+++
// Last Edited: 2026-03-28
// ==================================================

namespace AutoMarketerAI\Migrations;

// No direct access
defined('ABSPATH') || exit;

final class Migration_003_ScheduledPostsUtc
{
    // Migration version identifier — never change once shipped
    public const VERSION     = '003';
    public const DESCRIPTION = 'Add scheduled_utc column to amai_scheduled_posts';

    public function up(): void
    {
        global $wpdb;

        $table = $wpdb->prefix . 'amai_scheduled_posts';

        // Check whether the column already exists — dbDelta does not handle
        // ALTER TABLE reliably for adding columns, so we guard manually.
        $exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = %s
                   AND COLUMN_NAME  = 'scheduled_utc'",
                $table
            )
        );

        if ((int) $exists > 0) {
            return;
        }

        // Add scheduled_utc — computed from scheduled_date + scheduled_time
        // converted to UTC at generation time.  The Posting Engine compares
        // this column directly against UTC now, eliminating any per-request
        // timezone math.
        $wpdb->query(
            "ALTER TABLE {$table}
             ADD COLUMN scheduled_utc DATETIME NULL DEFAULT NULL
             AFTER scheduled_time,
             ADD KEY idx_posting_engine (status, scheduled_utc)"
        );
    }

    public function down(): void
    {
        // Forward-only migrations — down() documents intent only.
        // Never call this in production.
        global $wpdb;
        $table = $wpdb->prefix . 'amai_scheduled_posts';
        $wpdb->query("ALTER TABLE {$table} DROP KEY idx_posting_engine, DROP COLUMN scheduled_utc");
    }
}