<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/010_channel_column.php
// Description: Add channel column to wp_amai_scheduled_posts to distinguish facebook vs instagram posts - NUCLEAR+++
// Last Edited: 2026-04-09
// ==================================================
namespace AutoMarketerAI\Migrations;
defined('ABSPATH') || exit;
final class Migration_010_ChannelColumn
{
    public const VERSION     = '010';
    public const DESCRIPTION = 'Add channel column to wp_amai_scheduled_posts';
    public function up(): void
    {
        global $wpdb;
        $table = $wpdb->prefix . 'amai_scheduled_posts';
        $exists = $wpdb->get_results(
            "SHOW COLUMNS FROM `{$table}` LIKE 'channel'"
        );
        if (empty($exists)) {
            $wpdb->query(
                "ALTER TABLE `{$table}`
                 ADD COLUMN `channel` VARCHAR(32) NOT NULL DEFAULT 'facebook'
                 AFTER `tenant_id`"
            );
        }
        // Backfill existing rows — all historical posts are Facebook
        $wpdb->query(
            "UPDATE `{$table}` SET channel = 'facebook' WHERE channel = '' OR channel IS NULL"
        );
        // Rebuild unique key to include channel — without it, Facebook and Instagram
        // posts of the same type in the same batch would collide on INSERT
        $old_key = $wpdb->get_results(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = 'uq_tenant_batch_type'"
        );
        if (!empty($old_key)) {
            $wpdb->query("ALTER TABLE `{$table}` DROP INDEX `uq_tenant_batch_type`");
        }
        $new_key = $wpdb->get_results(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = 'uq_tenant_channel_batch_type'"
        );
        if (empty($new_key)) {
            $wpdb->query(
                "ALTER TABLE `{$table}`
                 ADD UNIQUE KEY `uq_tenant_channel_batch_type`
                 (`tenant_id`, `channel`, `generation_batch`, `post_type`)"
            );
        }
    }
    public function down(): void
    {
        global $wpdb;
        $table = $wpdb->prefix . 'amai_scheduled_posts';
        // Restore original unique key
        $new_key = $wpdb->get_results(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = 'uq_tenant_channel_batch_type'"
        );
        if (!empty($new_key)) {
            $wpdb->query("ALTER TABLE `{$table}` DROP INDEX `uq_tenant_channel_batch_type`");
        }
        $old_key = $wpdb->get_results(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = 'uq_tenant_batch_type'"
        );
        if (empty($old_key)) {
            $wpdb->query(
                "ALTER TABLE `{$table}`
                 ADD UNIQUE KEY `uq_tenant_batch_type` (`tenant_id`, `generation_batch`, `post_type`)"
            );
        }
        // Drop channel column
        $exists = $wpdb->get_results(
            "SHOW COLUMNS FROM `{$table}` LIKE 'channel'"
        );
        if (!empty($exists)) {
            $wpdb->query("ALTER TABLE `{$table}` DROP COLUMN `channel`");
        }
    }
}