-- Idempotent production bootstrap for Railway/MySQL.
-- Şema + boş site_settings satırları; vitrin içeriği panelden (ürün, menü, footer, site_pages).
-- Yerel veya staging demo verisi için isteğe bağlı: api/scripts/dev-demo-seed.sql
-- (prod-bootstrap.sh bu dosyayı çalıştırmaz; elle veya ayrı pipeline ile uygulayın).

CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sort_order INT NOT NULL DEFAULT 0,
  slug VARCHAR(191) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  content_html MEDIUMTEXT NULL,
  image VARCHAR(512) NOT NULL,
  category_slug VARCHAR(64) NOT NULL,
  price_try DECIMAL(12, 2) NOT NULL DEFAULT 0,
  stock_quantity INT UNSIGNED NOT NULL DEFAULT 0,
  show_on_home TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_products_slug (slug),
  KEY idx_category_slug (category_slug),
  KEY idx_products_sort (sort_order, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sort_order INT NOT NULL DEFAULT 0,
  title VARCHAR(128) NOT NULL,
  slug VARCHAR(64) NOT NULL,
  page_title VARCHAR(255) NOT NULL DEFAULT '',
  meta_keywords VARCHAR(512) NOT NULL DEFAULT '',
  meta_description TEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  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_product_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nav_menu_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NULL DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  title VARCHAR(128) NOT NULL,
  url VARCHAR(512) NOT NULL,
  open_new_tab TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  show_in_nav TINYINT(1) NOT NULL DEFAULT 1,
  variant ENUM('link', 'button') NOT NULL DEFAULT 'link',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_nav_parent_sort (parent_id, sort_order),
  CONSTRAINT fk_nav_menu_parent FOREIGN KEY (parent_id) REFERENCES nav_menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS footer_menu_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NULL DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  title VARCHAR(128) NOT NULL,
  url VARCHAR(512) NOT NULL,
  open_new_tab TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  show_in_footer TINYINT(1) NOT NULL DEFAULT 1,
  variant ENUM('link', 'button') NOT NULL DEFAULT 'link',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_footer_parent_sort (parent_id, sort_order),
  CONSTRAINT fk_footer_menu_parent FOREIGN KEY (parent_id) REFERENCES footer_menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_pages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NULL DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  path VARCHAR(255) NOT NULL,
  title VARCHAR(255) NOT NULL,
  short_title VARCHAR(255) NULL DEFAULT NULL,
  excerpt TEXT NULL,
  image_url VARCHAR(512) NULL DEFAULT NULL,
  cover_url VARCHAR(512) NULL DEFAULT NULL,
  body_html MEDIUMTEXT NULL,
  meta_title VARCHAR(255) NULL DEFAULT NULL,
  meta_keywords VARCHAR(512) NULL DEFAULT NULL,
  meta_description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  legal_review_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  legal_review_note VARCHAR(1024) NOT NULL DEFAULT '',
  legal_review_confirmed_at DATETIME 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_site_pages_path (path),
  KEY idx_site_pages_parent_sort (parent_id, sort_order),
  CONSTRAINT fk_site_pages_parent FOREIGN KEY (parent_id) REFERENCES site_pages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
  section_key VARCHAR(64) NOT NULL,
  data_json JSON NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (section_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO site_settings (section_key, data_json) VALUES
  ('general', JSON_OBJECT()),
  ('hero', JSON_OBJECT()),
  ('seo', JSON_OBJECT()),
  ('contact', JSON_OBJECT());

CREATE TABLE IF NOT EXISTS ecommerce_order_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(32) NOT NULL,
  to_status VARCHAR(32) NOT NULL,
  source VARCHAR(32) NOT NULL DEFAULT 'system',
  note VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_order_events_order (order_id),
  CONSTRAINT fk_ecommerce_order_events_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  product_slug VARCHAR(191) NOT NULL,
  title VARCHAR(255) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price_try DECIMAL(12, 2) NOT NULL,
  line_total_try DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_ecommerce_order_items_order (order_id),
  CONSTRAINT fk_ecommerce_order_items_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_order_addresses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  kind ENUM('billing', 'shipping') NOT NULL DEFAULT 'shipping',
  full_name VARCHAR(255) NOT NULL,
  phone VARCHAR(64) NOT NULL,
  email VARCHAR(255) NOT NULL,
  national_id VARCHAR(11) NOT NULL DEFAULT '',
  country VARCHAR(64) NOT NULL DEFAULT 'TR',
  city VARCHAR(128) NOT NULL,
  district VARCHAR(128) NOT NULL DEFAULT '',
  address_line TEXT NOT NULL,
  postal_code VARCHAR(16) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_order_addr (order_id, kind),
  CONSTRAINT fk_ecommerce_order_addr_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(32) NOT NULL DEFAULT 'iyzico',
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  amount_try DECIMAL(12, 2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  provider_payment_id VARCHAR(128) NULL DEFAULT NULL,
  provider_transaction_id VARCHAR(128) NULL DEFAULT NULL,
  raw_payload_json JSON NULL,
  idempotency_key VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_payment_idempotency (idempotency_key),
  KEY idx_ecommerce_payments_order (order_id),
  CONSTRAINT fk_ecommerce_payments_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_stock_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL DEFAULT NULL,
  qty_delta INT NOT NULL,
  reason ENUM('sale', 'refund', 'adjust', 'reserve', 'release') NOT NULL,
  note VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_stock_product (product_id),
  KEY idx_ecommerce_stock_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_coupons (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(32) NOT NULL,
  percent_off TINYINT UNSIGNED NOT NULL,
  max_uses INT UNSIGNED NULL DEFAULT NULL,
  uses_count INT UNSIGNED NOT NULL DEFAULT 0,
  valid_from DATE NULL DEFAULT NULL,
  valid_to DATE NULL DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ecommerce_coupon_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_shipments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  carrier VARCHAR(128) NULL DEFAULT NULL,
  tracking_number VARCHAR(128) NULL DEFAULT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_shipments_order (order_id),
  CONSTRAINT fk_ecommerce_shipments_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_refunds (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  payment_id BIGINT UNSIGNED NOT NULL,
  amount_try DECIMAL(12, 2) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  reason VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ecommerce_refunds_order (order_id),
  CONSTRAINT fk_ecommerce_refunds_order FOREIGN KEY (order_id) REFERENCES ecommerce_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_ecommerce_refunds_payment FOREIGN KEY (payment_id) REFERENCES ecommerce_payments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ecommerce_kv (
  k VARCHAR(64) NOT NULL,
  v TEXT NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vitrin rotaları + site_pages layout kolonları: api/scripts/prod-bootstrap-routing.sql (ayrı faz; ana dosya yarım kalırsa da uygulanır).

-- Menü başlıkları mojibake onarımı (mysql istemcisi yanlış karakter setiyle seed edildiyse).
-- database/migration_menu_mojibake_repair.sql ile aynı mantık; yalnızca title LIKE '%Ã%' + bilinen url.
UPDATE nav_menu_items SET title = 'Anasayfa' WHERE url = '/' AND title LIKE '%Ã%';
UPDATE nav_menu_items SET title = 'Ürünler' WHERE url = '/listing' AND title LIKE '%Ã%';
UPDATE nav_menu_items SET title = 'İletişim' WHERE url = '/iletisim' AND title LIKE '%Ã%';
UPDATE nav_menu_items SET title = 'Hakkımızda' WHERE url = '/hakkimizda' AND title LIKE '%Ã%';
UPDATE footer_menu_items SET title = 'Hakkımızda' WHERE url = '/hakkimizda' AND title LIKE '%Ã%';
UPDATE footer_menu_items SET title = 'İletişim' WHERE url = '/iletisim' AND title LIKE '%Ã%';
UPDATE footer_menu_items SET title = 'Ürünler' WHERE url = '/listing' AND title LIKE '%Ã%';
UPDATE footer_menu_items SET title = 'Ana Sayfa' WHERE url = '/' AND parent_id IS NOT NULL AND title LIKE '%Ã%';
UPDATE footer_menu_items SET title = 'Kurumsal'
WHERE parent_id IS NULL AND url = '#' AND sort_order = 0 AND title LIKE '%Ã%';
UPDATE footer_menu_items SET title = 'Hızlı Erişim'
WHERE parent_id IS NULL AND url = '#' AND sort_order = 1 AND title LIKE '%Ã%';
