-- Theme sections and managed navigation menus.
--
-- Until now only the `homepage_main` section key was ever read, so the header,
-- footer and sidebars could not be composed from widgets. This migration seeds
-- the remaining section keys for the published layout and introduces a menu
-- table so the navigation links stop being hardcoded in the layout template.
--
-- Forward-only and safe to re-run: every insert is guarded, and no existing
-- row is modified.

-- ---------------------------------------------------------------------------
-- 1. Seed the page section keys for the published layout
-- ---------------------------------------------------------------------------

INSERT INTO layout_sections (layout_id, section_key, name, sort_order, created_at, updated_at)
SELECT l.id, seeded.section_key, seeded.name, seeded.sort_order, NOW(), NOW()
FROM layouts l
INNER JOIN (
    SELECT 'header_top' AS section_key, 'Header top' AS name, 10 AS sort_order
    UNION ALL SELECT 'nav_extra', 'Navigation extra', 20
    UNION ALL SELECT 'home_sidebar', 'Home sidebar', 30
    UNION ALL SELECT 'question_sidebar', 'Question sidebar', 40
    UNION ALL SELECT 'footer_col_1', 'Footer column 1', 50
    UNION ALL SELECT 'footer_col_2', 'Footer column 2', 60
    UNION ALL SELECT 'footer_col_3', 'Footer column 3', 70
    UNION ALL SELECT 'footer_bottom', 'Footer bottom', 80
) AS seeded
WHERE l.status = 'published'
  AND NOT EXISTS (
      SELECT 1 FROM layout_sections existing
      WHERE existing.layout_id = l.id AND existing.section_key = seeded.section_key
  );

-- ---------------------------------------------------------------------------
-- 2. Navigation menu items
-- ---------------------------------------------------------------------------

SET @menu_table_exists = (
    SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'menu_items'
);
SET @create_menu_table_sql = IF(
    @menu_table_exists = 0,
    'CREATE TABLE menu_items (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        menu_key VARCHAR(32) NOT NULL,
        label VARCHAR(191) NULL,
        translation_key VARCHAR(191) NULL,
        url VARCHAR(500) NOT NULL,
        audience VARCHAR(16) NOT NULL DEFAULT "any",
        locales VARCHAR(191) NOT NULL DEFAULT "",
        is_enabled TINYINT(1) NOT NULL DEFAULT 1,
        opens_new_tab TINYINT(1) NOT NULL DEFAULT 0,
        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_menu_items_key_enabled_order (menu_key, is_enabled, sort_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci',
    'SELECT 1'
);
PREPARE create_menu_table_stmt FROM @create_menu_table_sql;
EXECUTE create_menu_table_stmt;
DEALLOCATE PREPARE create_menu_table_stmt;

-- Seed the links the header template renders today, so an upgraded site keeps
-- exactly the same navigation. `translation_key` is preferred over `label` so
-- seeded items stay translated in every enabled language; an administrator who
-- edits an item supplies a literal label instead.
INSERT INTO menu_items (menu_key, label, translation_key, url, audience, is_enabled, sort_order, created_at, updated_at)
SELECT seeded.menu_key, NULL, seeded.translation_key, seeded.url, seeded.audience, 1, seeded.sort_order, NOW(), NOW()
FROM (
    SELECT 'primary' AS menu_key, 'common.home' AS translation_key, '/' AS url, 'any' AS audience, 10 AS sort_order
    UNION ALL SELECT 'primary', 'common.browse_questions', '/#latest-questions', 'any', 20
    UNION ALL SELECT 'primary', 'contact.title', '/contact', 'any', 30
    UNION ALL SELECT 'primary', 'questions.ask', '/ask', 'any', 40
) AS seeded
WHERE NOT EXISTS (
    SELECT 1 FROM menu_items existing
    WHERE existing.menu_key = seeded.menu_key AND existing.url = seeded.url
);
