<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Adds the unique indexes that make menu seeding idempotent
 * (MenuSeeder relies on ON DUPLICATE KEY UPDATE).
 *
 * This migration is SELF-HEALING: before the unique indexes are created it
 * de-duplicates any rows that would otherwise violate them. This protects
 * hosts whose menus were seeded more than once while no unique constraint was
 * in place (a non-idempotent seeder against pre-3.1.2 schema could create
 * duplicate "Main Menu" rows). Without the dedupe step the ADD UNIQUE INDEX
 * would abort with a duplicate-key error and leave the host un-migratable.
 *
 * Dedupe rule (documented):
 *   - menus:      natural key = (name). Keep the lowest id per name; re-point
 *                 every menu_links.mid that referenced a superseded menu to the
 *                 surviving (canonical) menu id, then delete the superseded rows.
 *   - menu_links: natural key = (title, mid, parentId). Keep the lowest id per
 *                 group; re-point any menu_links.parentId that referenced a
 *                 superseded link to the surviving link id (preserving the
 *                 child hierarchy), then delete the superseded rows.
 *
 * Menus are deduped first so that link mid values are consolidated onto the
 * canonical menu before menu_links are grouped by their natural key.
 *
 * The index creation is guarded by hasIndexByName() so that a re-run after a
 * partial failure (MySQL DDL is non-transactional) is safe.
 */
final class AddUniqueIndexesToMenuTables extends AbstractMigration
{
    public function up(): void
    {
        $this->dedupeMenus();
        $this->dedupeMenuLinks();

        if (!$this->getAdapter()->hasIndexByName('menus', 'unique_menu_name')) {
            $menus = $this->table('menus');
            if ($this->getAdapter()->hasIndex('menus', ['name'])) {
                $menus->removeIndex(['name']);
            }
            $menus->addIndex(
                ['name'],
                [
                    'unique' => true,
                    'name' => 'unique_menu_name',
                ]
            )->update();
        }

        if (!$this->getAdapter()->hasIndexByName('menu_links', 'unique_menu_link_title_mid_parent')) {
            $links = $this->table('menu_links');
            if ($this->getAdapter()->hasIndex('menu_links', ['title'])) {
                $links->removeIndex(['title']);
            }
            $links->addIndex(
                ['title', 'mid', 'parentId'],
                [
                    'unique' => true,
                    'name' => 'unique_menu_link_title_mid_parent',
                ]
            )->update();
        }
    }

    public function down(): void
    {
        if ($this->getAdapter()->hasIndexByName('menus', 'unique_menu_name')) {
            $this->table('menus')
                ->removeIndexByName('unique_menu_name')
                ->addIndex(['name'])
                ->update();
        }

        if ($this->getAdapter()->hasIndexByName('menu_links', 'unique_menu_link_title_mid_parent')) {
            $this->table('menu_links')
                ->removeIndexByName('unique_menu_link_title_mid_parent')
                ->addIndex(['title'])
                ->update();
        }
    }

    /**
     * Collapse duplicate menus (natural key = name) onto the lowest-id row and
     * re-point child menu_links.mid at the survivor before deleting the rest.
     */
    private function dedupeMenus(): void
    {
        $this->execute(<<<'SQL'
            UPDATE menu_links ml
            JOIN menus dup ON dup.id = ml.mid
            JOIN (
                SELECT name, MIN(id) AS keep_id
                FROM menus
                GROUP BY name
            ) canon ON canon.name = dup.name
            SET ml.mid = canon.keep_id
            WHERE ml.mid <> canon.keep_id
        SQL);

        $this->execute(<<<'SQL'
            DELETE dup FROM menus dup
            JOIN (
                SELECT name, MIN(id) AS keep_id
                FROM menus
                GROUP BY name
            ) canon ON canon.name = dup.name
            WHERE dup.id <> canon.keep_id
        SQL);
    }

    /**
     * Collapse duplicate menu_links (natural key = title, mid, parentId) onto
     * the lowest-id row, re-pointing any child links at the survivor first.
     */
    private function dedupeMenuLinks(): void
    {
        $this->execute(<<<'SQL'
            UPDATE menu_links child
            JOIN menu_links dup ON dup.id = child.parentId
            JOIN (
                SELECT title, mid, parentId, MIN(id) AS keep_id
                FROM menu_links
                GROUP BY title, mid, parentId
            ) canon
                ON canon.title = dup.title
               AND canon.mid = dup.mid
               AND canon.parentId = dup.parentId
            SET child.parentId = canon.keep_id
            WHERE dup.id <> canon.keep_id
        SQL);

        $this->execute(<<<'SQL'
            DELETE dup FROM menu_links dup
            JOIN (
                SELECT title, mid, parentId, MIN(id) AS keep_id
                FROM menu_links
                GROUP BY title, mid, parentId
            ) canon
                ON canon.title = dup.title
               AND canon.mid = dup.mid
               AND canon.parentId = dup.parentId
            WHERE dup.id <> canon.keep_id
        SQL);
    }
}
