<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/008_batch_unique_key.php
// Description: Swap unique key on scheduled posts from (tenant,date,type) to (tenant,batch,type) - NUCLEAR+++
// Last Edited: 2026-04-02
// ==================================================
namespace AutoMarketerAI\Migrations;

defined('ABSPATH') || exit;

final class Migration_008_BatchUniqueKey
{
    public const VERSION     = '008';
    public const DESCRIPTION = 'Swap unique key on scheduled posts from (tenant,date,type) to (tenant,batch,type)';

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

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

        // Drop old unique key if it exists
        $old_key = $wpdb->get_results(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = 'uq_tenant_date_type'"
        );
        if (!empty($old_key)) {
            $wpdb->query("ALTER TABLE `{$table}` DROP INDEX `uq_tenant_date_type`");
        }

        // Add new unique key on (tenant_id, generation_batch, post_type) if not already present
        $new_key = $wpdb->get_results(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = 'uq_tenant_batch_type'"
        );
        if (empty($new_key)) {
            $wpdb->query(
                "ALTER TABLE `{$table}`
                 ADD UNIQUE KEY `uq_tenant_batch_type` (`tenant_id`, `generation_batch`, `post_type`)"
            );
        }
    }

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

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

        $wpdb->query("ALTER TABLE `{$table}` DROP INDEX IF EXISTS `uq_tenant_batch_type`");

        $wpdb->query(
            "ALTER TABLE `{$table}`
             ADD UNIQUE KEY `uq_tenant_date_type` (`tenant_id`, `scheduled_date`, `post_type`)"
        );
    }
}