<?php

/**
 * create the AI configuration and usage tables, adopting tnt_workflows' rows
 *
 * @author tnt
 * @created Wed, 05 Aug 2026 20:00:00 +0100
 */

declare(strict_types=1);

namespace Plugin\tnt_mcp\Migrations;

use JTL\Plugin\Migration;
use JTL\Update\IMigration;

/**
 * Class Migration_20260805200000
 *
 * `tnt_mcp_ai_config` + `tnt_mcp_ai_usage` — the shop's LLM credentials and spend counters, moved
 * here from tnt_workflows (`tnt_wf_ai_config` / `tnt_wf_ai_usage`).
 *
 * ## Why the tables move at all
 *
 * The API key, provider and budget are *connector* infrastructure — "this shop may talk to an LLM"
 * — and this plugin is already the trust boundary that owns OAuth, scopes, audit and the mode
 * guard. They lived in tnt_workflows only because the first AI consumer was a workflow action. That
 * left the setup story split across two plugins, which is the thing this move exists to end.
 *
 * ## Why the ciphertext survives a plain row copy
 *
 * `apiKeyCipher` is keyed by a value derived from the installation's `BLOWFISH_KEY`, not from the
 * plugin ({@see \Plugin\tnt_mcp\src\Ai\SecretBox}). The same installation therefore decrypts a row
 * written by either plugin, so the merchant does not have to re-enter a key they already rotated
 * once. Were the key plugin-derived, this migration would have to blank the credential instead.
 *
 * ## Why the source tables are left alone
 *
 * Both plugins are active during the rollout and tnt_workflows 1.25.x still reads its own tables.
 * Dropping them here would break a shop that installs tnt_mcp 0.20.0 before tnt_workflows 1.26.0 —
 * the documented and only supported order. tnt_workflows drops them in its own migration once
 * nothing reads them. Copying (rather than renaming) also means this migration is a no-op to undo.
 *
 * `INSERT IGNORE` rather than `REPLACE`: if a merchant has already configured AI on the tnt_mcp
 * side — possible when this migration re-runs after a failed update — that newer credential wins
 * over the inherited one. Never overwrite a secret a human typed.
 *
 * `down()` DROPs both tables. Correct for a secret: an encrypted key left behind for a plugin that
 * is gone is a liability with no upside, and the merchant still holds the key at the provider.
 */
class Migration20260805200000 extends Migration implements IMigration
{
    protected $author = 'tnt';
    // For backwards compatibility, the description property cant be strictly typed as a string
    protected $description = 'create the AI configuration and usage tables, adopting tnt_workflows rows';

    public function getAuthor(): ?string
    {
        return 'tnt';
    }

    public function getDescription(): string
    {
        return 'create the AI configuration and usage tables, adopting tnt_workflows rows';
    }

    /**
     * @inheritdoc
     */
    public function up(): void
    {
        $this->execute("CREATE TABLE IF NOT EXISTS `tnt_mcp_ai_config` (
              `id` TINYINT UNSIGNED NOT NULL,
              `provider` VARCHAR(32) NOT NULL DEFAULT '',
              `model` VARCHAR(128) NOT NULL DEFAULT '',
              `baseUrl` VARCHAR(255) NOT NULL DEFAULT '',
              `apiKeyCipher` TEXT NULL DEFAULT NULL,
              `apiKeyLast4` VARCHAR(8) NOT NULL DEFAULT '',
              `updated` DATETIME NULL DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

        $this->execute("CREATE TABLE IF NOT EXISTS `tnt_mcp_ai_usage` (
              `bucket` VARCHAR(24) NOT NULL,
              `runs` INT UNSIGNED NOT NULL DEFAULT 0,
              `costKnownRuns` INT UNSIGNED NOT NULL DEFAULT 0,
              `costMicros` BIGINT UNSIGNED NOT NULL DEFAULT 0,
              `promptTokens` BIGINT UNSIGNED NOT NULL DEFAULT 0,
              `completionTokens` BIGINT UNSIGNED NOT NULL DEFAULT 0,
              `updated` DATETIME NULL DEFAULT NULL,
              PRIMARY KEY (`bucket`),
              KEY `idx_updated` (`updated`)
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

        if ($this->tableExists('tnt_wf_ai_config')) {
            $this->execute('INSERT IGNORE INTO `tnt_mcp_ai_config`
                (`id`, `provider`, `model`, `baseUrl`, `apiKeyCipher`, `apiKeyLast4`, `updated`)
                SELECT `id`, `provider`, `model`, `baseUrl`, `apiKeyCipher`, `apiKeyLast4`, `updated`
                FROM `tnt_wf_ai_config`');
        }

        if ($this->tableExists('tnt_wf_ai_usage')) {
            $this->execute('INSERT IGNORE INTO `tnt_mcp_ai_usage`
                (`bucket`, `runs`, `costKnownRuns`, `costMicros`,
                 `promptTokens`, `completionTokens`, `updated`)
                SELECT `bucket`, `runs`, `costKnownRuns`, `costMicros`,
                       `promptTokens`, `completionTokens`, `updated`
                FROM `tnt_wf_ai_usage`');
        }
    }

    /**
     * @inheritdoc
     */
    public function down(): void
    {
        $this->execute('DROP TABLE IF EXISTS `tnt_mcp_ai_usage`');
        $this->execute('DROP TABLE IF EXISTS `tnt_mcp_ai_config`');
    }

    /**
     * Whether tnt_workflows ever created the table we want to adopt rows from.
     *
     * A shop that installed this plugin without ever installing tnt_workflows has no source table,
     * and `SELECT`ing from it would abort the whole migration.
     */
    private function tableExists(string $table): bool
    {
        return $this->fetchOne(
            "SHOW TABLES LIKE '" . $table . "'"
        ) !== null;
    }
}
