<?php

declare(strict_types=1);

namespace Plugin\tnt_stabila_shop\Migrations;

use JTL\Plugin\Migration;
use JTL\Update\IMigration;
use Plugin\tnt_stabila_shop\src\Enums\PluginSettings;

/**
 * Class Migration20260730210000
 *  Make stabila_shop_settings.settingName UNIQUE and backfill every missing
 *  PluginSettings default row.
 *
 * Root cause it fixes: DatabaseRepository::setSetting only UPDATEd, so any
 * PluginSettings case whose row was never seeded (sentryDsn, alertMinSeverity,
 * salesAnomalyThreshold, channelMailMinSeverity, channelWebhookUrl,
 * channelWebhookMinSeverity) could not be saved at all — the "Speichern" button
 * silently did nothing. Paired with the switch of setSetting() to upsert(), a
 * UNIQUE settingName lets any setting be inserted-or-updated in one call, so no
 * future per-setting seed migration is ever needed again.
 *
 * @author tnt
 * @created Wed, 30 Jul 2026 21:00:00 +0100
 */
class Migration20260730210000 extends Migration implements IMigration
{
    protected $author      = 'tnt';
    protected $description = 'settingName UNIQUE + backfill missing setting defaults';

    private const TABLE     = 'stabila_shop_settings';
    private const OLD_INDEX = 'stabila_shop_KEY_settings_idx';
    private const UQ_INDEX  = 'stabila_shop_UQ_settings_name';

    public function getAuthor(): ?string
    {
        return $this->author;
    }

    public function getDescription(): string
    {
        return 'settingName UNIQUE + backfill missing setting defaults';
    }

    public function up(): void
    {
        // 1. Collapse any pre-existing duplicate settingName rows (keep the lowest id)
        //    so the UNIQUE index can be added.
        $this->execute(
            'DELETE s1 FROM `' . self::TABLE . '` s1 '
            . 'INNER JOIN `' . self::TABLE . '` s2 '
            . 'ON s1.settingName = s2.settingName AND s1.id > s2.id'
        );

        // 2. Replace the non-unique key with a UNIQUE index on settingName.
        if ($this->indexExists(self::OLD_INDEX)) {
            $this->execute('ALTER TABLE `' . self::TABLE . '` DROP INDEX `' . self::OLD_INDEX . '`');
        }
        if (!$this->indexExists(self::UQ_INDEX)) {
            $this->execute(
                'ALTER TABLE `' . self::TABLE . '` ADD UNIQUE INDEX `' . self::UQ_INDEX . '` (`settingName`)'
            );
        }

        // 3. Backfill a default row for every PluginSettings case still missing one
        //    (existing installs never seeded the settings added after their install).
        foreach (PluginSettings::cases() as $case) {
            try {
                $setting = \json_decode($case->value, false, 512, \JSON_THROW_ON_ERROR);
            } catch (\JsonException) {
                continue;
            }
            /** @var object{name: string, type: string, defaultValue: string} $setting */
            $existing = $this->db->getSingleObject(
                'SELECT settingName FROM `' . self::TABLE . '` WHERE settingName = :name',
                ['name' => $setting->name]
            );
            if ($existing !== null) {
                continue;
            }
            $this->db->insertRow(self::TABLE, (object)[
                'settingType'  => $setting->type,
                'settingName'  => $setting->name,
                'settingValue' => $setting->defaultValue,
            ]);
        }
    }

    public function down(): void
    {
        if ($this->indexExists(self::UQ_INDEX)) {
            $this->execute('ALTER TABLE `' . self::TABLE . '` DROP INDEX `' . self::UQ_INDEX . '`');
        }
        if (!$this->indexExists(self::OLD_INDEX)) {
            $this->execute(
                'ALTER TABLE `' . self::TABLE . '` ADD INDEX `' . self::OLD_INDEX . '` (`settingName`)'
            );
        }
    }

    private function indexExists(string $indexName): bool
    {
        return $this->db->getSingleObject(
            'SELECT 1 AS present FROM information_schema.statistics '
            . 'WHERE table_schema = DATABASE() AND table_name = :t AND index_name = :i LIMIT 1',
            ['t' => self::TABLE, 'i' => $indexName]
        ) !== null;
    }
}
