-- Add locale-specific widget titles and textual content while preserving the
-- existing widgets.title/config_json columns as the fallback representation.

SET @widget_translations_sql = IF(
    EXISTS(
        SELECT 1 FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'widget_translations'
    ),
    'SELECT 1',
    'CREATE TABLE widget_translations (
        widget_id BIGINT UNSIGNED NOT NULL,
        locale VARCHAR(16) NOT NULL,
        title VARCHAR(255) NULL,
        content_json LONGTEXT NOT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        PRIMARY KEY (widget_id, locale),
        KEY idx_widget_translations_locale (locale),
        CONSTRAINT fk_widget_translations_widget FOREIGN KEY (widget_id) REFERENCES widgets(id) ON DELETE CASCADE,
        CONSTRAINT fk_widget_translations_language FOREIGN KEY (locale) REFERENCES languages(code) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
);
PREPARE widget_translations_statement FROM @widget_translations_sql;
EXECUTE widget_translations_statement;
DEALLOCATE PREPARE widget_translations_statement;
