-- ============================================================
-- JMS Media Hub v2.0 — Database Delta
-- Run AFTER the existing Phase 10 schema.
-- All additions are purely additive — no existing tables altered
-- except two nullable column additions to content and podcasts.
-- ============================================================

CREATE TABLE IF NOT EXISTS broadcast_sessions (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title            VARCHAR(220) NOT NULL,
  presenter_id     BIGINT UNSIGNED NOT NULL,
  stream_url       VARCHAR(500) NULL,
  status           ENUM('live','ended') NOT NULL DEFAULT 'live',
  started_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at         DATETIME NULL,
  duration_seconds INT UNSIGNED NULL,
  recording_file   VARCHAR(255) NULL,
  listener_peak    INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_bs_presenter FOREIGN KEY (presenter_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS broadcast_logs (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id   BIGINT UNSIGNED NOT NULL,
  event_type   VARCHAR(60) NOT NULL,
  event_detail TEXT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bl_session FOREIGN KEY (session_id) REFERENCES broadcast_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS broadcast_presenters (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  status     ENUM('pending','approved','rejected','left') NOT NULL DEFAULT 'pending',
  joined_at  DATETIME NULL,
  left_at    DATETIME NULL,
  CONSTRAINT fk_bp_session FOREIGN KEY (session_id) REFERENCES broadcast_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_bp_user    FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS jingles (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uploaded_by      BIGINT UNSIGNED NOT NULL,
  title            VARCHAR(160) NOT NULL,
  category         ENUM('station_id','breaking_news','sponsor','commercial','intro','outro','weather','sports','general') NOT NULL DEFAULT 'general',
  filename         VARCHAR(255) NOT NULL,
  duration_seconds INT UNSIGNED NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_jingle_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_tracks (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uploaded_by      BIGINT UNSIGNED NOT NULL,
  title            VARCHAR(220) NOT NULL,
  artist           VARCHAR(160) NULL,
  filename         VARCHAR(255) NOT NULL,
  duration_seconds INT UNSIGNED NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_music_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_trending (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_type VARCHAR(20) NOT NULL,
  content_id   BIGINT UNSIGNED NOT NULL,
  score_today  DECIMAL(10,2) NOT NULL DEFAULT 0,
  score_week   DECIMAL(10,2) NOT NULL DEFAULT 0,
  score_month  DECIMAL(10,2) NOT NULL DEFAULT 0,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_trending (content_type, content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reading_history (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      BIGINT UNSIGNED NOT NULL,
  content_type VARCHAR(20) NOT NULL,
  content_id   BIGINT UNSIGNED NOT NULL,
  viewed_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_history (user_id, content_type, content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS saved_searches (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  query      VARCHAR(255) NOT NULL,
  name       VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ss_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_publications (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_type ENUM('news','blog','audio','video','podcast') NOT NULL,
  content_id   BIGINT UNSIGNED NOT NULL,
  scheduled_at DATETIME NOT NULL,
  timezone     VARCHAR(60) NOT NULL DEFAULT 'UTC',
  status       ENUM('scheduled','published','cancelled') NOT NULL DEFAULT 'scheduled',
  created_by   BIGINT UNSIGNED NOT NULL,
  published_at DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sp_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Nullable scheduled_at columns on existing tables (safe, additive)
ALTER TABLE content  ADD COLUMN IF NOT EXISTS scheduled_at DATETIME NULL AFTER published_at;
ALTER TABLE podcasts ADD COLUMN IF NOT EXISTS scheduled_at DATETIME NULL AFTER published_at;