-- Kurumsal i18n: locale satırlarını *_en sütunlarına birleştir, locale sütununu kaldır (adım 2).
USE faunamix;

SET @has_sp_locale := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_pages' AND COLUMN_NAME = 'locale'
);

-- site_pages: bilinen sistem eşlemeleri
SET @sql := IF(
  @has_sp_locale > 0,
  "UPDATE site_pages tr
   INNER JOIN site_pages en ON en.locale = 'en' AND TRIM(en.path) = '/about-us'
   SET tr.path_en = TRIM(en.path),
       tr.title_en = NULLIF(TRIM(en.title), ''),
       tr.short_title_en = NULLIF(TRIM(en.short_title), ''),
       tr.excerpt_en = en.excerpt,
       tr.body_html_en = en.body_html,
       tr.meta_title_en = NULLIF(TRIM(en.meta_title), ''),
       tr.meta_keywords_en = en.meta_keywords,
       tr.meta_description_en = en.meta_description,
       tr.layout_json_draft_en = en.layout_json_draft,
       tr.layout_json_published_en = en.layout_json_published
   WHERE COALESCE(tr.locale, '') = '' AND TRIM(tr.path) = '/hakkimizda'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @has_sp_locale > 0,
  "UPDATE site_pages tr
   INNER JOIN site_pages en ON en.locale = 'en' AND TRIM(en.path) = '/contact'
   SET tr.path_en = TRIM(en.path),
       tr.title_en = NULLIF(TRIM(en.title), ''),
       tr.short_title_en = NULLIF(TRIM(en.short_title), ''),
       tr.excerpt_en = en.excerpt,
       tr.body_html_en = en.body_html,
       tr.meta_title_en = NULLIF(TRIM(en.meta_title), ''),
       tr.meta_keywords_en = en.meta_keywords,
       tr.meta_description_en = en.meta_description,
       tr.layout_json_draft_en = en.layout_json_draft,
       tr.layout_json_published_en = en.layout_json_published
   WHERE COALESCE(tr.locale, '') = '' AND TRIM(tr.path) = '/iletisim'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @has_sp_locale > 0,
  "UPDATE site_pages tr
   INNER JOIN site_pages en ON en.locale = 'en' AND TRIM(en.path) = '/privacy'
   SET tr.path_en = TRIM(en.path), tr.title_en = NULLIF(TRIM(en.title), ''),
       tr.body_html_en = en.body_html, tr.layout_json_published_en = en.layout_json_published
   WHERE COALESCE(tr.locale, '') = '' AND TRIM(tr.path) = '/gizlilik'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @has_sp_locale > 0,
  "UPDATE site_pages tr
   INNER JOIN site_pages en ON en.locale = 'en' AND TRIM(en.path) IN ('/terms','/distance-selling','/pre-contract-info','/returns','/reservation')
   SET tr.path_en = TRIM(en.path),
       tr.title_en = COALESCE(NULLIF(TRIM(en.title), ''), tr.title_en),
       tr.body_html_en = COALESCE(en.body_html, tr.body_html_en),
       tr.layout_json_published_en = COALESCE(en.layout_json_published, tr.layout_json_published_en)
   WHERE COALESCE(tr.locale, '') = '' AND TRIM(tr.path) IN (
     '/kullanim','/mesafeli-satis','/on-bilgilendirme','/iade-kosullari','/rezervasyon'
   ) AND (
     (TRIM(tr.path) = '/kullanim' AND TRIM(en.path) = '/terms')
     OR (TRIM(tr.path) = '/mesafeli-satis' AND TRIM(en.path) = '/distance-selling')
     OR (TRIM(tr.path) = '/on-bilgilendirme' AND TRIM(en.path) = '/pre-contract-info')
     OR (TRIM(tr.path) = '/iade-kosullari' AND TRIM(en.path) = '/returns')
     OR (TRIM(tr.path) = '/rezervasyon' AND TRIM(en.path) = '/reservation')
   )",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- site_pages: parent_id + sort_order eşlemesi (henüz path_en boş TR satırlar)
SET @sql := IF(
  @has_sp_locale > 0,
  "UPDATE site_pages tr
   INNER JOIN site_pages en ON en.locale = 'en'
     AND COALESCE(en.parent_id, 0) = COALESCE(tr.parent_id, 0)
     AND en.sort_order = tr.sort_order
     AND en.id <> tr.id
   SET tr.path_en = COALESCE(NULLIF(TRIM(tr.path_en), ''), NULLIF(TRIM(en.path), '')),
       tr.title_en = COALESCE(NULLIF(TRIM(tr.title_en), ''), NULLIF(TRIM(en.title), '')),
       tr.short_title_en = COALESCE(NULLIF(TRIM(tr.short_title_en), ''), NULLIF(TRIM(en.short_title), '')),
       tr.excerpt_en = COALESCE(tr.excerpt_en, en.excerpt),
       tr.body_html_en = COALESCE(tr.body_html_en, en.body_html),
       tr.meta_title_en = COALESCE(NULLIF(TRIM(tr.meta_title_en), ''), NULLIF(TRIM(en.meta_title), '')),
       tr.meta_keywords_en = COALESCE(tr.meta_keywords_en, en.meta_keywords),
       tr.meta_description_en = COALESCE(tr.meta_description_en, en.meta_description),
       tr.layout_json_draft_en = COALESCE(tr.layout_json_draft_en, en.layout_json_draft),
       tr.layout_json_published_en = COALESCE(tr.layout_json_published_en, en.layout_json_published)
   WHERE COALESCE(tr.locale, '') = ''
     AND (tr.path_en IS NULL OR TRIM(tr.path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- path_en varsayılan: bilinen harita (EN satırı yoksa)
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 := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/contact' WHERE TRIM(path) = '/iletisim' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/about-us' WHERE TRIM(path) = '/hakkimizda' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/privacy' WHERE TRIM(path) = '/gizlilik' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/terms' WHERE TRIM(path) = '/kullanim' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/distance-selling' WHERE TRIM(path) = '/mesafeli-satis' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/pre-contract-info' WHERE TRIM(path) = '/on-bilgilendirme' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/returns' WHERE TRIM(path) = '/iade-kosullari' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @has_path_en > 0,
  "UPDATE site_pages SET path_en = '/reservation' WHERE TRIM(path) = '/rezervasyon' AND (path_en IS NULL OR TRIM(path_en) = '')",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_sp_locale > 0, "DELETE FROM site_pages WHERE locale = 'en'", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- nav_menu_items
SET @has_nav_locale := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'nav_menu_items' AND COLUMN_NAME = 'locale'
);

SET @sql := IF(
  @has_nav_locale > 0,
  "UPDATE nav_menu_items tr
   INNER JOIN nav_menu_items en ON en.locale = 'en'
     AND COALESCE(en.parent_id, 0) = COALESCE(tr.parent_id, 0)
     AND en.sort_order = tr.sort_order
   SET tr.title_en = NULLIF(TRIM(en.title), ''),
       tr.url_en = NULLIF(TRIM(en.url), '')
   WHERE COALESCE(tr.locale, '') = ''",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_nav_locale > 0, "DELETE FROM nav_menu_items WHERE locale = 'en'", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- footer_menu_items
SET @has_ft_locale := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'footer_menu_items' AND COLUMN_NAME = 'locale'
);

SET @sql := IF(
  @has_ft_locale > 0,
  "UPDATE footer_menu_items tr
   INNER JOIN footer_menu_items en ON en.locale = 'en'
     AND COALESCE(en.parent_id, 0) = COALESCE(tr.parent_id, 0)
     AND en.sort_order = tr.sort_order
   SET tr.title_en = NULLIF(TRIM(en.title), ''),
       tr.url_en = NULLIF(TRIM(en.url), '')
   WHERE COALESCE(tr.locale, '') = ''",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_ft_locale > 0, "DELETE FROM footer_menu_items WHERE locale = 'en'", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- home_hero_slides
SET @has_hero_locale := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'home_hero_slides' AND COLUMN_NAME = 'locale'
);

SET @sql := IF(
  @has_hero_locale > 0,
  "UPDATE home_hero_slides tr
   INNER JOIN home_hero_slides en ON en.locale = 'en' AND en.sort_order = tr.sort_order
   SET tr.baslik_1_en = NULLIF(TRIM(en.baslik_1), ''),
       tr.baslik_2_en = NULLIF(TRIM(en.baslik_2), ''),
       tr.baslik_3_en = NULLIF(TRIM(en.baslik_3), ''),
       tr.baslik_4_en = NULLIF(TRIM(en.baslik_4), ''),
       tr.alt_text_en = NULLIF(TRIM(en.alt_text), '')
   WHERE COALESCE(tr.locale, '') = ''",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_hero_locale > 0, "DELETE FROM home_hero_slides WHERE locale = 'en'", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- routes: EN satırlarını entity_id ile TR satırına bağla (site_pages merge sonrası)
SET @sql := IF(
  @has_sp_locale > 0,
  "UPDATE routes r_en
   INNER JOIN site_pages sp ON sp.id = r_en.entity_id AND r_en.type = 'page' AND r_en.locale = 'en'
   INNER JOIN routes r_tr ON r_tr.type = 'page' AND r_tr.locale = '' AND r_tr.entity_id = sp.id
   SET r_en.entity_id = sp.id
   WHERE TRIM(COALESCE(sp.path_en, '')) <> '' AND TRIM(r_en.path) = TRIM(sp.path_en)",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Drop locale columns + restore site_pages path unique
SET @uq_loc := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_pages' AND INDEX_NAME = 'uq_site_pages_locale_path'
);
SET @sql := IF(@uq_loc > 0, 'ALTER TABLE site_pages DROP INDEX uq_site_pages_locale_path', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_sp_locale > 0, 'ALTER TABLE site_pages DROP COLUMN locale', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @uq_path := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_pages' AND INDEX_NAME = 'uq_site_pages_path'
);
SET @sql := IF(@uq_path = 0, 'ALTER TABLE site_pages ADD UNIQUE KEY uq_site_pages_path (path)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_nav_locale > 0, 'ALTER TABLE nav_menu_items DROP COLUMN locale', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_ft_locale > 0, 'ALTER TABLE footer_menu_items DROP COLUMN locale', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_hero_locale > 0, 'ALTER TABLE home_hero_slides DROP COLUMN locale', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
