<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ -- NUCLEAR+++
// File: /includes/migrations/class-migration-037-product-catalog.php
// Description: Creates wp_amai_product_catalog and wp_amai_product_queue tables - NUCLEAR+++
// Last Edited: 2026-05-15
// ==================================================

namespace AutoMarketerAI\Migrations;

defined('ABSPATH') || exit;

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

        $charset = $wpdb->get_charset_collate();

        // Product catalog -- synced nightly from the active store provider
        $wpdb->query(
            "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}amai_product_catalog (
                id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                provider_id    VARCHAR(32)     NOT NULL,
                external_id    VARCHAR(255)    NOT NULL,
                name           VARCHAR(500)    NOT NULL DEFAULT '',
                description    TEXT            NOT NULL DEFAULT '',
                price          DECIMAL(10,2)   NULL,
                currency       VARCHAR(8)      NOT NULL DEFAULT 'USD',
                image_url      TEXT            NOT NULL DEFAULT '',
                product_url    TEXT            NOT NULL DEFAULT '',
                on_sale        TINYINT(1)      NOT NULL DEFAULT 0,
                sale_price     DECIMAL(10,2)   NULL,
                stock          INT             NULL,
                is_new         TINYINT(1)      NOT NULL DEFAULT 0,
                created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
                synced_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY provider_external (provider_id, external_id),
                KEY on_sale (on_sale),
                KEY is_new (is_new),
                KEY created_at (created_at)
            ) $charset"
        );

        // Product promotion queue -- tracks rotation state
        // One row, updated in place as the engine advances through the queue
        $wpdb->query(
            "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}amai_product_queue (
                id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
                last_catalog_id     BIGINT UNSIGNED NULL,
                queue_position      INT UNSIGNED    NOT NULL DEFAULT 0,
                sale_phase          VARCHAR(16)     NOT NULL DEFAULT '',
                sale_catalog_id     BIGINT UNSIGNED NULL,
                sale_phase_index    TINYINT         NOT NULL DEFAULT 0,
                last_synced_at      DATETIME        NULL,
                updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id)
            ) $charset"
        );

        // Seed the single queue row if it does not exist
        $wpdb->query(
            "INSERT IGNORE INTO {$wpdb->prefix}amai_product_queue (id) VALUES (1)"
        );
    }
}