<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/023_competitor_radar.php
// Description: Creates wp_amai_competitor_pages and wp_amai_competitor_posts tables - NUCLEAR+++
// Last Edited: 2026-04-22
// ==================================================

namespace AutoMarketerAI\Migrations;

defined('ABSPATH') || exit;

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

        $charset = $wpdb->get_charset_collate();

        $wpdb->query(
            "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}amai_competitor_pages` (" .
            "  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT," .
            "  `tenant_id`       VARCHAR(64)  NOT NULL DEFAULT 'default'," .
            "  `platform`        VARCHAR(32)  NOT NULL DEFAULT 'facebook'," .
            "  `page_url`        VARCHAR(512) NOT NULL DEFAULT ''," .
            "  `page_id`         VARCHAR(128) NOT NULL DEFAULT ''," .
            "  `page_name`       VARCHAR(255) NOT NULL DEFAULT ''," .
            "  `last_fetched_at` DATETIME     NULL DEFAULT NULL," .
            "  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP," .
            "  PRIMARY KEY (`id`)," .
            "  UNIQUE KEY `uq_tenant_platform_page` (`tenant_id`, `platform`, `page_id`)" .
            ") {$charset}"
        );

        $wpdb->query(
            "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}amai_competitor_posts` (" .
            "  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT," .
            "  `competitor_id`    INT UNSIGNED NOT NULL," .
            "  `platform_post_id` VARCHAR(128) NOT NULL DEFAULT ''," .
            "  `post_text`        TEXT         NOT NULL," .
            "  `posted_at`        DATETIME     NULL DEFAULT NULL," .
            "  `fetched_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP," .
            "  PRIMARY KEY (`id`)," .
            "  UNIQUE KEY `uq_competitor_post` (`competitor_id`, `platform_post_id`)" .
            ") {$charset}"
        );
    }
}
