-- ============================================================
-- JMS RADIO, PODCAST & CAMPUS MEDIA HUB
-- MASTER DATABASE SCHEMA
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- Compatible with MySQL 5.7+/8.0 and MariaDB 10.3+ (shared hosting)
--
-- This is the single source of truth for the full project.
-- Tables are grouped by the phase that activates their feature set,
-- but are all created now so later phases never require breaking
-- migrations. See docs/02-database-design.md for rationale.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------
-- PHASE 1: USERS & AUTHENTICATION
-- ----------------------------------------------------------------

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('student','editor','admin') NOT NULL DEFAULT 'student',
  status ENUM('pending_verification','active','suspended') NOT NULL DEFAULT 'pending_verification',
  email_verified_at DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  department VARCHAR(120) NULL,
  level VARCHAR(20) NULL,
  bio TEXT NULL,
  profile_photo VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ev_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ev_token (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_pr_token (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  identifier VARCHAR(180) NOT NULL COMMENT 'email attempted',
  ip_address VARCHAR(45) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_la_identifier (identifier, created_at),
  INDEX idx_la_ip (ip_address, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- PHASE 2 & 3: CATEGORIES, CONTENT, REVIEW WORKFLOW
-- ----------------------------------------------------------------

CREATE TABLE categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  type ENUM('news','blog','audio','video','podcast') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type ENUM('news','blog','audio','video') NOT NULL,
  title VARCHAR(220) NOT NULL,
  slug VARCHAR(250) NOT NULL UNIQUE,
  summary VARCHAR(500) NULL,
  body LONGTEXT NULL,
  category_id BIGINT UNSIGNED NULL,
  featured_image VARCHAR(255) NULL,
  status ENUM('draft','submitted','approved','rejected','revision_requested') NOT NULL DEFAULT 'draft',
  rejection_note TEXT NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  published_at DATETIME NULL,
  views_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_content_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_content_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_content_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_content_type_status (type, status),
  INDEX idx_content_published (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_audio (
  content_id BIGINT UNSIGNED PRIMARY KEY,
  audio_file_path VARCHAR(255) NOT NULL,
  duration_seconds INT UNSIGNED NULL,
  CONSTRAINT fk_caudio_content FOREIGN KEY (content_id) REFERENCES content(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_video (
  content_id BIGINT UNSIGNED PRIMARY KEY,
  youtube_url VARCHAR(255) NOT NULL,
  thumbnail VARCHAR(255) NULL,
  CONSTRAINT fk_cvideo_content FOREIGN KEY (content_id) REFERENCES content(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tags (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  slug VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_tags (
  content_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (content_id, tag_id),
  CONSTRAINT fk_ctag_content FOREIGN KEY (content_id) REFERENCES content(id) ON DELETE CASCADE,
  CONSTRAINT fk_ctag_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(60) NULL,
  target_id BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- PHASE 4: PODCAST SYSTEM (external storage URLs only)
-- ----------------------------------------------------------------

CREATE TABLE podcasts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(220) NOT NULL,
  slug VARCHAR(250) NOT NULL UNIQUE,
  description TEXT NULL,
  cover_image VARCHAR(255) NULL,
  audio_url VARCHAR(500) NOT NULL COMMENT 'External CDN URL: Cloudinary / Bunny / B2. No binary files stored on shared hosting.',
  category_id BIGINT UNSIGNED NULL,
  duration_seconds INT UNSIGNED NULL,
  status ENUM('draft','submitted','approved','rejected','revision_requested') NOT NULL DEFAULT 'draft',
  rejection_note TEXT NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  published_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_podcast_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_podcast_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_podcast_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_podcast_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE podcast_analytics (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  podcast_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('play','download') NOT NULL,
  listening_time_seconds INT UNSIGNED NULL,
  listener_hash VARCHAR(64) NULL COMMENT 'Salted hash of IP+UA, never raw PII',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_panalytics_podcast FOREIGN KEY (podcast_id) REFERENCES podcasts(id) ON DELETE CASCADE,
  INDEX idx_panalytics_podcast (podcast_id, event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- PHASE 5: LIVE RADIO SYSTEM (external stream only)
-- ----------------------------------------------------------------

CREATE TABLE radio_programs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  presenter_name VARCHAR(120) NOT NULL,
  day_of_week TINYINT UNSIGNED NOT NULL COMMENT '1=Sunday .. 7=Saturday',
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE radio_status (
  id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
  is_live TINYINT(1) NOT NULL DEFAULT 0,
  stream_url VARCHAR(500) NULL COMMENT 'Icecast / AzuraCast / Radio.co stream URL',
  current_presenter VARCHAR(120) NULL,
  listener_count INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT chk_radio_status_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE airtime_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  requested_date DATE NOT NULL,
  requested_time_slot VARCHAR(40) NOT NULL,
  topic VARCHAR(255) NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  review_note TEXT NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_airtime_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_airtime_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- PHASE 7: ENGAGEMENT (likes, comments, favorites, shares)
-- content_type/content_id are logical references resolved against
-- either `content` or `podcasts` in application code (see docs/03-erd.md)
-- ----------------------------------------------------------------

CREATE TABLE comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_type ENUM('news','blog','audio','video','podcast') NOT NULL,
  content_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  guest_name VARCHAR(100) NULL,
  parent_comment_id BIGINT UNSIGNED NULL,
  body TEXT NOT NULL,
  status ENUM('visible','hidden','flagged') NOT NULL DEFAULT 'visible',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_comment_parent FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE,
  INDEX idx_comment_target (content_type, content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE likes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_type ENUM('news','blog','audio','video','podcast') NOT NULL,
  content_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  guest_hash VARCHAR(64) NULL COMMENT 'Salted hash of IP+UA for anonymous like de-duplication',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_like_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_like_user (content_type, content_id, user_id),
  UNIQUE KEY uq_like_guest (content_type, content_id, guest_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE favorites (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  content_type ENUM('news','blog','audio','video','podcast') NOT NULL,
  content_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_fav (user_id, content_type, content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE shares (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_type ENUM('news','blog','audio','video','podcast') NOT NULL,
  content_id BIGINT UNSIGNED NOT NULL,
  platform ENUM('whatsapp','facebook','x','telegram') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- PHASE 8: NOTIFICATIONS
-- ----------------------------------------------------------------

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  body VARCHAR(500) NOT NULL,
  type ENUM('new_podcast','breaking_news','content_approved','live_radio','general') NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  endpoint VARCHAR(500) NOT NULL,
  p256dh VARCHAR(255) NOT NULL,
  auth_key VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_push_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_push_endpoint (endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- PHASE 9: AI JOURNALISM TOOLS
-- ----------------------------------------------------------------

CREATE TABLE ai_tool_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tool_name VARCHAR(80) NOT NULL UNIQUE,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  daily_limit_per_user INT UNSIGNED NOT NULL DEFAULT 10,
  api_provider ENUM('openai','claude','gemini') NOT NULL DEFAULT 'claude',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_tool_usage (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  tool_name VARCHAR(80) NOT NULL,
  request_count INT UNSIGNED NOT NULL DEFAULT 1,
  usage_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_aiusage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_ai_usage_day (user_id, tool_name, usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------
-- SEED DATA
-- Deliberately excludes any admin user — see docs/05-security-plan.md
-- (first admin is created via a one-time setup script in Phase 1,
-- never a shipped default credential).
-- ----------------------------------------------------------------

INSERT INTO categories (name, slug, type) VALUES
('Campus News', 'campus-news', 'news'),
('National News', 'national-news', 'news'),
('Opinion', 'opinion', 'blog'),
('Lifestyle', 'lifestyle', 'blog'),
('Investigative', 'investigative', 'news'),
('News', 'podcast-news', 'podcast'),
('Interviews', 'interviews', 'podcast'),
('Discussions', 'discussions', 'podcast'),
('Documentaries', 'documentaries', 'podcast'),
('Investigative Reports', 'investigative-reports', 'podcast'),
('Field Reports', 'field-reports', 'audio'),
('Campus Voices', 'campus-voices', 'audio'),
('Sports Audio', 'sports-audio', 'audio'),
('Audio Editorials', 'audio-editorials', 'audio'),
('Campus Events', 'campus-events-video', 'video'),
('Sports Highlights', 'sports-highlights', 'video'),
('Mini-Documentaries', 'mini-documentaries', 'video'),
('Explainers', 'explainers', 'video');

INSERT INTO ai_tool_settings (tool_name, is_enabled, daily_limit_per_user, api_provider) VALUES
('headline_generator', 1, 15, 'claude'),
('story_outline_generator', 1, 10, 'claude'),
('interview_question_generator', 1, 10, 'claude'),
('news_summary_generator', 1, 15, 'claude'),
('grammar_checker', 1, 20, 'claude'),
('seo_title_generator', 1, 15, 'claude'),
('article_improvement_suggestions', 1, 10, 'claude');

INSERT INTO radio_status (id, is_live, listener_count) VALUES (1, 0, 0);