-- Firsatlar sayfasi + urun indirim eslesmeleri.
-- Tek urune en fazla bir aktif indirim kaydi tutulur (UNIQUE product_id).
USE faunamix;

CREATE TABLE IF NOT EXISTS product_discount_offers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_page_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  discount_percent DECIMAL(5,2) NOT NULL,
  starts_at DATETIME NULL DEFAULT NULL,
  ends_at DATETIME NULL DEFAULT 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_discount_offers_product (product_id),
  KEY idx_product_discount_offers_page_active (site_page_id, is_active),
  CONSTRAINT fk_product_discount_offers_page
    FOREIGN KEY (site_page_id) REFERENCES site_pages(id) ON DELETE CASCADE,
  CONSTRAINT fk_product_discount_offers_product
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- "Firsatlar" vitrindeki kampanya sayfasi olarak varsayilan kayit.
-- NOT: `FROM site_pages` ile tek satir uretilemez; coklu satir duplicate hatasina yol acardi.
INSERT INTO site_pages (
  parent_id,
  sort_order,
  path,
  title,
  short_title,
  excerpt,
  body_html,
  meta_title,
  meta_keywords,
  meta_description,
  is_active
)
SELECT
  NULL,
  COALESCE((SELECT MAX(sort_order) FROM site_pages WHERE parent_id IS NULL), -1) + 1,
  '/firsatlar',
  'Firsatlar',
  'Kampanya',
  'Indirimdeki urunleri bu sayfada inceleyebilirsiniz.',
  '<p>Guncel kampanyalarimizdan yararlanmak icin bize ulasin.</p>',
  'Firsatlar | Faunamix',
  'firsatlar, indirim, kampanya',
  'Faunamix firsatlar sayfasi: indirimli urunlerin guncel listesi.',
  1
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM site_pages WHERE path = '/firsatlar');
