SET @custom_code_table_exists = (
    SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'custom_code_snippets'
);
SET @create_custom_code_table_sql = IF(
    @custom_code_table_exists = 0,
    'CREATE TABLE custom_code_snippets (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(191) NOT NULL,
        position VARCHAR(20) NOT NULL,
        code LONGTEXT NOT 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_custom_code_position_enabled_order (position, is_enabled, sort_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci',
    'SELECT 1'
);
PREPARE create_custom_code_table_stmt FROM @create_custom_code_table_sql;
EXECUTE create_custom_code_table_stmt;
DEALLOCATE PREPARE create_custom_code_table_stmt;

INSERT INTO custom_code_snippets (name, position, code, is_enabled, sort_order, created_at, updated_at)
SELECT
    'Imported from advertising settings',
    'head',
    s.value,
    1,
    0,
    NOW(),
    NOW()
FROM settings s
WHERE s.`key` = 'ads.head_code'
  AND s.value IS NOT NULL
  AND TRIM(s.value) <> ''
  AND NOT EXISTS (
      SELECT 1
      FROM custom_code_snippets c
      WHERE c.name = 'Imported from advertising settings'
  );
