-- Scope custom code snippets.
--
-- Snippets previously ran on every page for every visitor with no way to
-- narrow them, so an operator could not, for example, load a conversion pixel
-- only on the question pages or an analytics tag only for signed-out visitors.
--
-- Forward-only and safe to re-run. Existing snippets keep their current
-- behaviour: the defaults below mean "everywhere, every language, everyone".

SET @pages_column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'custom_code_snippets' AND COLUMN_NAME = 'pages'
);
SET @add_pages_sql = IF(
    @pages_column_exists = 0,
    'ALTER TABLE custom_code_snippets ADD COLUMN pages VARCHAR(255) NOT NULL DEFAULT "" AFTER code',
    'SELECT 1'
);
PREPARE add_pages_stmt FROM @add_pages_sql;
EXECUTE add_pages_stmt;
DEALLOCATE PREPARE add_pages_stmt;

SET @locales_column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'custom_code_snippets' AND COLUMN_NAME = 'locales'
);
SET @add_locales_sql = IF(
    @locales_column_exists = 0,
    'ALTER TABLE custom_code_snippets ADD COLUMN locales VARCHAR(191) NOT NULL DEFAULT "" AFTER pages',
    'SELECT 1'
);
PREPARE add_locales_stmt FROM @add_locales_sql;
EXECUTE add_locales_stmt;
DEALLOCATE PREPARE add_locales_stmt;

SET @audience_column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'custom_code_snippets' AND COLUMN_NAME = 'audience'
);
SET @add_audience_sql = IF(
    @audience_column_exists = 0,
    'ALTER TABLE custom_code_snippets ADD COLUMN audience VARCHAR(16) NOT NULL DEFAULT "any" AFTER locales',
    'SELECT 1'
);
PREPARE add_audience_stmt FROM @add_audience_sql;
EXECUTE add_audience_stmt;
DEALLOCATE PREPARE add_audience_stmt;
