<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/016_x_connection.php
// Description: Creates amai_x_connection table for X (Twitter) OAuth channel - NUCLEAR+++
// Last Edited: 2026-04-16
// ==================================================

namespace AutoMarketerAI\Migrations;

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

final class Migration_016_XConnection
{
    // Migration version identifier -- never change once shipped
    public const VERSION = '016';
    public const DESCRIPTION = 'Create amai_x_connection table';

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

        $charset = $wpdb->get_charset_collate();

        // --------------------------------------------------
        // amai_x_connection
        // Stores the connected X (Twitter) account.
        // access_token_enc and refresh_token_enc are both
        // AES-256-CBC encrypted at rest via KeyVault.
        // x_user_id holds the numeric X user ID string.
        // One row per tenant -- same pattern as TikTok connection.
        // --------------------------------------------------
        $table = $wpdb->prefix . 'amai_x_connection';

        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tenant_id           VARCHAR(64)     NOT NULL DEFAULT 'default',
            x_user_id           VARCHAR(64)     NOT NULL DEFAULT '',
            username            VARCHAR(64)     NOT NULL DEFAULT '',
            display_name        VARCHAR(255)    NOT NULL DEFAULT '',
            profile_image_url   VARCHAR(512)    NULL,
            followers_count     INT UNSIGNED    NULL,
            access_token_enc    TEXT            NOT NULL,
            refresh_token_enc   TEXT            NULL,
            token_expires_at    DATETIME        NULL,
            refresh_expires_at  DATETIME        NULL,
            connected_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_verified_at    DATETIME        NULL,
            status              ENUM('active','expired','revoked','error') NOT NULL DEFAULT 'active',
            created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_tenant (tenant_id),
            KEY idx_status (status)
        ) {$charset};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    public function down(): void
    {
        // Forward-only migrations -- down() documents intent only.
        // Never call this in production.
        // To rollback: DROP TABLE {prefix}amai_x_connection;
    }
}