-- EN vitrin: TR slug ile gelen istekler → localized slug (301).
-- Örnek: /en/iletisim → /en/contact (middleware locale=en, path=/iletisim).
-- Idempotent.

INSERT INTO routes (locale, path, type, entity_id, redirect_to, redirect_status, is_active, priority)
SELECT 'en', v.tr_path, 'page', NULL, v.en_path, 301, 1, 50
FROM (
  SELECT '/iletisim' AS tr_path, '/contact' AS en_path
  UNION ALL SELECT '/hakkimizda', '/about-us'
  UNION ALL SELECT '/gizlilik', '/privacy'
  UNION ALL SELECT '/kullanim', '/terms'
  UNION ALL SELECT '/mesafeli-satis', '/distance-selling'
  UNION ALL SELECT '/on-bilgilendirme', '/pre-contract-info'
  UNION ALL SELECT '/iade-kosullari', '/returns'
  UNION ALL SELECT '/rezervasyon', '/reservation'
) AS v
WHERE NOT EXISTS (SELECT 1 FROM routes r WHERE r.locale = 'en' AND r.path = v.tr_path);

UPDATE routes r
INNER JOIN (
  SELECT '/iletisim' AS tr_path, '/contact' AS en_path
  UNION ALL SELECT '/hakkimizda', '/about-us'
  UNION ALL SELECT '/gizlilik', '/privacy'
  UNION ALL SELECT '/kullanim', '/terms'
  UNION ALL SELECT '/mesafeli-satis', '/distance-selling'
  UNION ALL SELECT '/on-bilgilendirme', '/pre-contract-info'
  UNION ALL SELECT '/iade-kosullari', '/returns'
  UNION ALL SELECT '/rezervasyon', '/reservation'
) AS v ON r.locale = 'en' AND r.path = v.tr_path
SET r.redirect_to = v.en_path,
    r.redirect_status = 301,
    r.entity_id = NULL,
    r.type = 'page',
    r.is_active = 1,
    r.priority = 50,
    r.updated_at = CURRENT_TIMESTAMP
WHERE r.redirect_to IS NULL OR TRIM(r.redirect_to) <> v.en_path;

-- path_en birleşik şema: EN sayfa rotaları
SET @has_path_en = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_pages' AND COLUMN_NAME = 'path_en'
);

SET @sql_en_pages = IF(
  @has_path_en > 0,
  "INSERT INTO routes (locale, path, type, entity_id, redirect_to, is_active, priority, meta_title, meta_description)
   SELECT 'en', TRIM(sp.path_en), 'page', sp.id, NULL, sp.is_active, 50,
          NULLIF(TRIM(COALESCE(sp.meta_title_en, '')), ''),
          NULLIF(TRIM(COALESCE(sp.meta_description_en, '')), '')
   FROM site_pages sp
   WHERE sp.is_active = 1
     AND TRIM(COALESCE(sp.path_en, '')) NOT IN ('', '/')
     AND NOT EXISTS (SELECT 1 FROM routes r WHERE r.locale = 'en' AND r.path = TRIM(sp.path_en))",
  'SELECT 1'
);
PREPARE stmt_en_pages FROM @sql_en_pages;
EXECUTE stmt_en_pages;
DEALLOCATE PREPARE stmt_en_pages;
