CREATE DATABASE IF NOT EXISTS story_cms
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE story_cms;

CREATE TABLE admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE stories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  synopsis TEXT,
  cover_image VARCHAR(255),
  type ENUM('series', 'one_shot') NOT NULL DEFAULT 'series',
  status ENUM('ongoing', 'completed', 'hiatus') NOT NULL DEFAULT 'ongoing',
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_stories_published_updated (is_published, updated_at),
  INDEX idx_stories_type_status (type, status)
) ENGINE=InnoDB;

CREATE TABLE chapters (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  story_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  slug VARCHAR(220) NOT NULL,
  chapter_number DECIMAL(8,2) NOT NULL DEFAULT 1.00,
  content LONGTEXT NOT NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_chapters_story_slug (story_id, slug),
  UNIQUE KEY uq_chapters_story_number (story_id, chapter_number),
  INDEX idx_chapters_story_published (story_id, is_published, chapter_number),
  CONSTRAINT fk_chapters_story
    FOREIGN KEY (story_id) REFERENCES stories(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE genres (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE tags (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE story_genres (
  story_id BIGINT UNSIGNED NOT NULL,
  genre_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (story_id, genre_id),
  CONSTRAINT fk_story_genres_story
    FOREIGN KEY (story_id) REFERENCES stories(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_story_genres_genre
    FOREIGN KEY (genre_id) REFERENCES genres(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE story_tags (
  story_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (story_id, tag_id),
  CONSTRAINT fk_story_tags_story
    FOREIGN KEY (story_id) REFERENCES stories(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_story_tags_tag
    FOREIGN KEY (tag_id) REFERENCES tags(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE ads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  spot ENUM('header', 'sidebar', 'between_chapters') NOT NULL,
  name VARCHAR(120) NOT NULL,
  code TEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ads_spot_active (spot, is_active, sort_order)
) ENGINE=InnoDB;