<?php

declare(strict_types=1);

/**
 * User-defined categories per Search Console property; tracked queries and pages
 * optionally reference one category (SET NULL when the category is deleted).
 *
 * @return callable(PDO, string): void
 */
return static function (\PDO $pdo, string $prefix): void {
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $prefix)) {
        throw new InvalidArgumentException('Invalid table prefix');
    }

    $cats = '`' . $prefix . 'tracked_categories`';
    $users = '`' . $prefix . 'users`';
    $props = '`' . $prefix . 'gsc_properties`';
    $tq = '`' . $prefix . 'tracked_queries`';
    $tp = '`' . $prefix . 'tracked_pages`';

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS {$cats} (
            `id` bigint unsigned NOT NULL AUTO_INCREMENT,
            `user_id` int unsigned NOT NULL,
            `property_id` bigint unsigned NOT NULL,
            `name` varchar(128) NOT NULL,
            `created_at` datetime NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_user_property_name` (`user_id`, `property_id`, `name`),
            KEY `idx_user_property` (`user_id`, `property_id`),
            CONSTRAINT `fk_tracked_categories_user`
                FOREIGN KEY (`user_id`) REFERENCES {$users} (`id`) ON DELETE CASCADE,
            CONSTRAINT `fk_tracked_categories_property`
                FOREIGN KEY (`property_id`) REFERENCES {$props} (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    $db = (string) ($pdo->query('SELECT DATABASE()')->fetchColumn());
    if ($db === '') {
        throw new \RuntimeException('No database selected for tracked_categories migration.');
    }

    $hasColumn = static function (\PDO $pdo, string $db, string $tableName, string $col): bool {
        $st = $pdo->prepare(
            'SELECT COUNT(*) FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?'
        );
        $st->execute([$db, $tableName, $col]);

        return (int) $st->fetchColumn() > 0;
    };

    $tqBare = $prefix . 'tracked_queries';
    $tpBare = $prefix . 'tracked_pages';

    if (!$hasColumn($pdo, $db, $tqBare, 'category_id')) {
        $pdo->exec(
            "ALTER TABLE {$tq}
                ADD COLUMN `category_id` bigint unsigned NULL AFTER `query`,
                ADD KEY `idx_tracked_queries_category` (`category_id`),
                ADD CONSTRAINT `fk_tracked_queries_category`
                    FOREIGN KEY (`category_id`) REFERENCES {$cats} (`id`) ON DELETE SET NULL"
        );
    }

    if (!$hasColumn($pdo, $db, $tpBare, 'category_id')) {
        $pdo->exec(
            "ALTER TABLE {$tp}
                ADD COLUMN `category_id` bigint unsigned NULL AFTER `page`,
                ADD KEY `idx_tracked_pages_category` (`category_id`),
                ADD CONSTRAINT `fk_tracked_pages_category`
                    FOREIGN KEY (`category_id`) REFERENCES {$cats} (`id`) ON DELETE SET NULL"
        );
    }
};
