-- Üst menü öğesini site_pages kaydına bağla (URL otomatik senkron)
USE faunamix;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'nav_menu_items' AND COLUMN_NAME = 'site_page_id'
);

SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE nav_menu_items ADD COLUMN site_page_id INT UNSIGNED NULL DEFAULT NULL AFTER url',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'nav_menu_items'
    AND CONSTRAINT_NAME = 'fk_nav_menu_site_page'
);

SET @sql_fk = IF(
  @fk_exists = 0,
  'ALTER TABLE nav_menu_items ADD CONSTRAINT fk_nav_menu_site_page FOREIGN KEY (site_page_id) REFERENCES site_pages(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt2 FROM @sql_fk;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

UPDATE nav_menu_items n
INNER JOIN site_pages sp ON sp.path = '/hakkimizda' AND sp.is_active = 1
SET n.site_page_id = sp.id, n.url = sp.path
WHERE n.url IN ('/hakkimizda', '/Hakkimizda')
  AND (n.site_page_id IS NULL OR n.site_page_id = sp.id);

UPDATE nav_menu_items n
INNER JOIN site_pages sp ON sp.path = '/iletisim' AND sp.is_active = 1
SET n.site_page_id = sp.id, n.url = sp.path
WHERE n.title = 'İletişim' AND n.url NOT LIKE 'http%' AND n.url NOT LIKE 'mailto:%'
  AND (n.site_page_id IS NULL OR n.site_page_id = sp.id);
