SET @categories_seo_title_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'categories' AND COLUMN_NAME = 'seo_title'
);
SET @categories_seo_title_sql = IF(
    @categories_seo_title_exists = 0,
    'ALTER TABLE categories ADD COLUMN seo_title VARCHAR(255) NULL AFTER sort_order',
    'SELECT 1'
);
PREPARE categories_seo_title_stmt FROM @categories_seo_title_sql;
EXECUTE categories_seo_title_stmt;
DEALLOCATE PREPARE categories_seo_title_stmt;

SET @categories_seo_description_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'categories' AND COLUMN_NAME = 'seo_description'
);
SET @categories_seo_description_sql = IF(
    @categories_seo_description_exists = 0,
    'ALTER TABLE categories ADD COLUMN seo_description VARCHAR(191) NULL AFTER seo_title',
    'SELECT 1'
);
PREPARE categories_seo_description_stmt FROM @categories_seo_description_sql;
EXECUTE categories_seo_description_stmt;
DEALLOCATE PREPARE categories_seo_description_stmt;

SET @categories_canonical_url_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'categories' AND COLUMN_NAME = 'canonical_url'
);
SET @categories_canonical_url_sql = IF(
    @categories_canonical_url_exists = 0,
    'ALTER TABLE categories ADD COLUMN canonical_url VARCHAR(500) NULL AFTER seo_description',
    'SELECT 1'
);
PREPARE categories_canonical_url_stmt FROM @categories_canonical_url_sql;
EXECUTE categories_canonical_url_stmt;
DEALLOCATE PREPARE categories_canonical_url_stmt;

SET @categories_seo_index_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'categories' AND COLUMN_NAME = 'seo_index'
);
SET @categories_seo_index_sql = IF(
    @categories_seo_index_exists = 0,
    'ALTER TABLE categories ADD COLUMN seo_index VARCHAR(16) NOT NULL DEFAULT ''inherit'' AFTER canonical_url',
    'SELECT 1'
);
PREPARE categories_seo_index_stmt FROM @categories_seo_index_sql;
EXECUTE categories_seo_index_stmt;
DEALLOCATE PREPARE categories_seo_index_stmt;

SET @tags_description_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND COLUMN_NAME = 'description'
);
SET @tags_description_sql = IF(
    @tags_description_exists = 0,
    'ALTER TABLE tags ADD COLUMN description TEXT NULL AFTER name',
    'SELECT 1'
);
PREPARE tags_description_stmt FROM @tags_description_sql;
EXECUTE tags_description_stmt;
DEALLOCATE PREPARE tags_description_stmt;

SET @tags_seo_title_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND COLUMN_NAME = 'seo_title'
);
SET @tags_seo_title_sql = IF(
    @tags_seo_title_exists = 0,
    'ALTER TABLE tags ADD COLUMN seo_title VARCHAR(255) NULL AFTER content_locale',
    'SELECT 1'
);
PREPARE tags_seo_title_stmt FROM @tags_seo_title_sql;
EXECUTE tags_seo_title_stmt;
DEALLOCATE PREPARE tags_seo_title_stmt;

SET @tags_seo_description_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND COLUMN_NAME = 'seo_description'
);
SET @tags_seo_description_sql = IF(
    @tags_seo_description_exists = 0,
    'ALTER TABLE tags ADD COLUMN seo_description VARCHAR(191) NULL AFTER seo_title',
    'SELECT 1'
);
PREPARE tags_seo_description_stmt FROM @tags_seo_description_sql;
EXECUTE tags_seo_description_stmt;
DEALLOCATE PREPARE tags_seo_description_stmt;

SET @tags_canonical_url_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND COLUMN_NAME = 'canonical_url'
);
SET @tags_canonical_url_sql = IF(
    @tags_canonical_url_exists = 0,
    'ALTER TABLE tags ADD COLUMN canonical_url VARCHAR(500) NULL AFTER seo_description',
    'SELECT 1'
);
PREPARE tags_canonical_url_stmt FROM @tags_canonical_url_sql;
EXECUTE tags_canonical_url_stmt;
DEALLOCATE PREPARE tags_canonical_url_stmt;

SET @tags_seo_index_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tags' AND COLUMN_NAME = 'seo_index'
);
SET @tags_seo_index_sql = IF(
    @tags_seo_index_exists = 0,
    'ALTER TABLE tags ADD COLUMN seo_index VARCHAR(16) NOT NULL DEFAULT ''inherit'' AFTER canonical_url',
    'SELECT 1'
);
PREPARE tags_seo_index_stmt FROM @tags_seo_index_sql;
EXECUTE tags_seo_index_stmt;
DEALLOCATE PREPARE tags_seo_index_stmt;

CREATE TABLE IF NOT EXISTS tag_synonyms (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag_id BIGINT UNSIGNED NOT NULL,
    synonym VARCHAR(120) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tag_synonyms_synonym (synonym),
    KEY idx_tag_synonyms_tag_id (tag_id),
    CONSTRAINT fk_tag_synonyms_tag FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
