-- Dinamik routing: tek doğruluk kaynağı + çözümleyici cache anahtarları.
-- idempotent

CREATE TABLE IF NOT EXISTS routes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  locale VARCHAR(16) NOT NULL DEFAULT '',
  path VARCHAR(512) NOT NULL,
  type VARCHAR(32) NOT NULL,
  entity_id BIGINT UNSIGNED NULL DEFAULT NULL,
  redirect_to VARCHAR(512) NULL DEFAULT NULL,
  redirect_status SMALLINT UNSIGNED NOT NULL DEFAULT 301,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  priority INT NOT NULL DEFAULT 0,
  meta_title VARCHAR(255) NULL DEFAULT NULL,
  meta_description TEXT NULL,
  canonical_path VARCHAR(512) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_routes_locale_path (locale, path),
  KEY idx_routes_type_entity (type, entity_id),
  KEY idx_routes_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mevcut varlıklardan ilk rota kümesi (çakışmada önce gelen satır korunur; NOT EXISTS ile atlanır)

INSERT INTO routes (locale, path, type, entity_id, redirect_to, is_active, priority)
SELECT '', '/listing', 'listing', NULL, NULL, 1, 100
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM routes WHERE locale = '' AND path = '/listing');

INSERT INTO routes (locale, path, type, entity_id, redirect_to, is_active, priority, meta_title, meta_description)
SELECT '', TRIM(sp.path), 'page', sp.id, NULL, 1, 50, sp.meta_title, sp.meta_description
FROM site_pages sp
WHERE sp.is_active = 1
  AND TRIM(sp.path) NOT IN ('', '/')
  AND NOT EXISTS (SELECT 1 FROM routes r WHERE r.locale = '' AND r.path = TRIM(sp.path));

INSERT INTO routes (locale, path, type, entity_id, redirect_to, is_active, priority)
SELECT '', CONCAT('/urun/', p.slug), 'product', p.id, NULL, 1, 30
FROM products p
WHERE COALESCE(p.is_active, 1) = 1
  AND TRIM(COALESCE(p.slug, '')) <> ''
  AND NOT EXISTS (SELECT 1 FROM routes r WHERE r.locale = '' AND r.path = CONCAT('/urun/', p.slug));

INSERT INTO routes (locale, path, type, entity_id, redirect_to, is_active, priority, meta_title, meta_description)
SELECT '', CONCAT('/', pc.slug), 'category', pc.id, NULL, 1, 40, NULLIF(TRIM(pc.page_title), ''), pc.meta_description
FROM product_categories pc
WHERE pc.is_active = 1
  AND TRIM(pc.slug) <> ''
  AND NOT EXISTS (SELECT 1 FROM routes r WHERE r.locale = '' AND r.path = CONCAT('/', pc.slug));
