SET @uniq_tags_name_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND INDEX_NAME = 'uniq_tags_name'
);
SET @drop_uniq_tags_name_sql = IF(
    @uniq_tags_name_exists > 0,
    'ALTER TABLE tags DROP INDEX uniq_tags_name',
    'SELECT 1'
);
PREPARE drop_uniq_tags_name_stmt FROM @drop_uniq_tags_name_sql;
EXECUTE drop_uniq_tags_name_stmt;
DEALLOCATE PREPARE drop_uniq_tags_name_stmt;

SET @uniq_tags_locale_name_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND INDEX_NAME = 'uniq_tags_locale_name'
);
SET @add_uniq_tags_locale_name_sql = IF(
    @uniq_tags_locale_name_exists = 0,
    'ALTER TABLE tags ADD UNIQUE KEY uniq_tags_locale_name (content_locale, name)',
    'SELECT 1'
);
PREPARE add_uniq_tags_locale_name_stmt FROM @add_uniq_tags_locale_name_sql;
EXECUTE add_uniq_tags_locale_name_stmt;
DEALLOCATE PREPARE add_uniq_tags_locale_name_stmt;

-- Keep the existing globally unique slug contract because public tag URLs are /t/{slug}.
SET @uniq_tags_slug_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND INDEX_NAME = 'uniq_tags_slug'
);
SET @add_uniq_tags_slug_sql = IF(
    @uniq_tags_slug_exists = 0,
    'ALTER TABLE tags ADD UNIQUE KEY uniq_tags_slug (slug)',
    'SELECT 1'
);
PREPARE add_uniq_tags_slug_stmt FROM @add_uniq_tags_slug_sql;
EXECUTE add_uniq_tags_slug_stmt;
DEALLOCATE PREPARE add_uniq_tags_slug_stmt;
