-- Advertisements become rows instead of settings keys.
--
-- Each advertisement previously lived in the generic settings table as
-- ads.{placement}.code -- one string per placement -- so a second
-- advertisement in the same placement was not a missing feature but a
-- structural impossibility. This table makes several advertisements per
-- placement, weighted rotation and per-advertisement targeting possible.
--
-- There is deliberately no `type` column: one advertisement holds both a code
-- and an image, and the renderer prefers the code when both are present. That
-- keeps "paste your ad tag here, or fill the image fields instead" a single
-- form with no mode selector to get wrong.
--
-- Forward-only and safe to re-run. Existing configuration is copied across and
-- the settings rows are left untouched.

SET @ad_creatives_exists = (
    SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ad_creatives'
);
SET @create_ad_creatives_sql = IF(
    @ad_creatives_exists = 0,
    'CREATE TABLE ad_creatives (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(191) NOT NULL,
        placement VARCHAR(32) NOT NULL,
        code LONGTEXT NULL,
        image_url VARCHAR(500) NOT NULL DEFAULT "",
        link_url VARCHAR(500) NOT NULL DEFAULT "",
        alt VARCHAR(200) NOT NULL DEFAULT "",
        image_width INT NOT NULL DEFAULT 0,
        image_height INT NOT NULL DEFAULT 0,
        weight INT NOT NULL DEFAULT 1,
        target_devices VARCHAR(64) NOT NULL DEFAULT "",
        target_locales VARCHAR(191) NOT NULL DEFAULT "",
        target_audience VARCHAR(16) NOT NULL DEFAULT "any",
        starts_at DATETIME NULL,
        ends_at DATETIME NULL,
        is_enabled TINYINT(1) NOT NULL DEFAULT 1,
        sort_order INT NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        KEY idx_ad_creatives_placement_enabled (placement, is_enabled, sort_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci',
    'SELECT 1'
);
PREPARE create_ad_creatives_stmt FROM @create_ad_creatives_sql;
EXECUTE create_ad_creatives_stmt;
DEALLOCATE PREPARE create_ad_creatives_stmt;

-- Carry across whatever the operator already configured, one creative per
-- placement whose mode was not "disabled". Device flags become a device list;
-- an empty list means every device, which is what "all three enabled" meant.
INSERT INTO ad_creatives (
    name, placement, code, image_url, link_url, alt, image_width, image_height,
    weight, target_devices, target_locales, target_audience, is_enabled, sort_order, created_at, updated_at
)
SELECT
    CONCAT('Imported ', p.placement),
    p.placement,
    COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.code')), ''),
    COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.image_url')), ''),
    COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.link_url')), ''),
    COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.alt')), ''),
    COALESCE((SELECT CAST(s.value AS UNSIGNED) FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.image_width')), 0),
    COALESCE((SELECT CAST(s.value AS UNSIGNED) FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.image_height')), 0),
    1,
    TRIM(BOTH ',' FROM CONCAT_WS(',',
        IF(COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.show_desktop')), '1') = '1', 'desktop', NULL),
        IF(COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.show_tablet')), '1') = '1', 'tablet', NULL),
        IF(COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.show_mobile')), '1') = '1', 'mobile', NULL)
    )),
    '',
    'any',
    1,
    0,
    NOW(),
    NOW()
FROM (
    SELECT 'header' AS placement
    UNION ALL SELECT 'footer'
    UNION ALL SELECT 'homepage'
    UNION ALL SELECT 'after_question'
    UNION ALL SELECT 'between_answers'
) AS p
WHERE COALESCE((SELECT s.value FROM settings s WHERE s.key = CONCAT('ads.', p.placement, '.mode')), 'disabled') <> 'disabled'
  AND NOT EXISTS (SELECT 1 FROM ad_creatives existing WHERE existing.placement = p.placement);
