-- ============================================================
-- MANŞET Haber CMS — Veritabanı Şeması
-- MySQL 5.7+ / MariaDB 10.3+  ·  utf8mb4
-- ============================================================
SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ---------- Kullanıcılar (yazar / editör / admin) ----------
CREATE TABLE users (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(120)  NOT NULL,
  email         VARCHAR(190)  NOT NULL,
  password_hash VARCHAR(255)  NOT NULL,
  role          ENUM('admin','editor','author') NOT NULL DEFAULT 'author',
  title         VARCHAR(120)  DEFAULT NULL,           -- ör. "Ekonomi Editörü"
  bio           TEXT          DEFAULT NULL,
  avatar        VARCHAR(255)  DEFAULT NULL,
  slug          VARCHAR(150)  NOT NULL,
  is_active     TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Kategoriler ----------
CREATE TABLE categories (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(120) NOT NULL,
  slug       VARCHAR(150) NOT NULL,
  color      VARCHAR(20)  NOT NULL DEFAULT '#D3122A',  -- modül renk kodu
  parent_id  INT UNSIGNED DEFAULT NULL,
  sort_order INT          NOT NULL DEFAULT 0,
  is_active  TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cat_slug (slug),
  KEY idx_cat_parent (parent_id),
  CONSTRAINT fk_cat_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Haberler ----------
CREATE TABLE articles (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title         VARCHAR(255) NOT NULL,
  slug          VARCHAR(255) NOT NULL,
  summary       TEXT         DEFAULT NULL,            -- spot / özet
  body          MEDIUMTEXT   NOT NULL,                -- HTML gövde
  cover_image   VARCHAR(255) DEFAULT NULL,
  cover_caption VARCHAR(255) DEFAULT NULL,
  category_id   INT UNSIGNED NOT NULL,
  author_id     INT UNSIGNED NOT NULL,
  status        ENUM('draft','published','scheduled') NOT NULL DEFAULT 'draft',
  is_breaking   TINYINT(1)   NOT NULL DEFAULT 0,      -- son dakika
  is_featured   TINYINT(1)   NOT NULL DEFAULT 0,      -- manşet
  view_count    INT UNSIGNED NOT NULL DEFAULT 0,
  reading_time  TINYINT UNSIGNED DEFAULT NULL,        -- dk
  meta_title    VARCHAR(255) DEFAULT NULL,
  meta_desc     VARCHAR(320) DEFAULT NULL,
  published_at  DATETIME     DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_art_slug (slug),
  KEY idx_art_cat (category_id),
  KEY idx_art_author (author_id),
  KEY idx_art_status_pub (status, published_at),
  KEY idx_art_featured (is_featured, published_at),
  FULLTEXT KEY ft_art (title, summary, body),
  CONSTRAINT fk_art_cat FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT fk_art_author FOREIGN KEY (author_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Etiketler ----------
CREATE TABLE tags (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(150) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tag_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE article_tag (
  article_id INT UNSIGNED NOT NULL,
  tag_id     INT UNSIGNED NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  KEY idx_at_tag (tag_id),
  CONSTRAINT fk_at_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  CONSTRAINT fk_at_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Yorumlar ----------
CREATE TABLE comments (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  article_id INT UNSIGNED NOT NULL,
  parent_id  INT UNSIGNED DEFAULT NULL,
  author_name VARCHAR(120) NOT NULL,
  author_email VARCHAR(190) DEFAULT NULL,
  body       TEXT NOT NULL,
  status     ENUM('pending','approved','spam') NOT NULL DEFAULT 'pending',
  ip_address VARBINARY(16) DEFAULT NULL,
  up_votes   INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cm_article (article_id, status),
  KEY idx_cm_parent (parent_id),
  CONSTRAINT fk_cm_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Bülten aboneleri ----------
CREATE TABLE subscribers (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email      VARCHAR(190) NOT NULL,
  is_active  TINYINT(1)   NOT NULL DEFAULT 1,
  token      VARCHAR(64)  NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sub_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Reklam alanları ----------
CREATE TABLE ad_slots (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slot_key   VARCHAR(60)  NOT NULL,     -- ör. 'home_970x90', 'sidebar_300x250'
  title      VARCHAR(120) NOT NULL,
  html_code  TEXT         DEFAULT NULL, -- reklam kodu / iframe
  is_active  TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ad_key (slot_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Site ayarları (anahtar-değer) ----------
CREATE TABLE settings (
  skey  VARCHAR(80) NOT NULL,
  sval  TEXT DEFAULT NULL,
  PRIMARY KEY (skey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Giriş denemesi (rate limit) ----------
CREATE TABLE login_attempts (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip_address VARBINARY(16) NOT NULL,
  email      VARCHAR(190)  DEFAULT NULL,
  attempted_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_la_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;