
-- database.sql
CREATE DATABASE IF NOT EXISTS sportsnews CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sportsnews;

-- Content tables
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(64) UNIQUE NOT NULL,
  name VARCHAR(128) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  excerpt TEXT,
  body MEDIUMTEXT,
  cover_image VARCHAR(255),
  author VARCHAR(128),
  published_at DATETIME,
  is_published TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS videos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  description TEXT,
  thumbnail VARCHAR(255),
  url VARCHAR(255) NOT NULL,
  duration_seconds INT DEFAULT 0,
  published_at DATETIME,
  is_published TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS shorts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  description TEXT,
  thumbnail VARCHAR(255),
  url VARCHAR(255) NOT NULL,
  duration_seconds INT DEFAULT 0,
  published_at DATETIME,
  is_published TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS live_streams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  description TEXT,
  thumbnail VARCHAR(255),
  hls_url VARCHAR(255) NOT NULL,
  is_live TINYINT(1) DEFAULT 0,
  starts_at DATETIME,
  ends_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Seed example data
INSERT IGNORE INTO categories (slug, name) VALUES
('football','Football'),
('basketball','Basketball'),
('volleyball','Volleyball');

INSERT IGNORE INTO articles (category_id, title, slug, excerpt, body, cover_image, author, published_at, is_published)
VALUES
(1, 'Opening Weekend Power Rankings', 'opening-weekend-power-rankings',
 'Quick look at who''s hot.', '<p>Fresh season, fresh takes. Here are the teams to watch…</p>',
 '/assets/sample-cover.jpg', 'Editorial Team', NOW(), 1);

INSERT IGNORE INTO videos (title, slug, description, thumbnail, url, duration_seconds, published_at, is_published) VALUES
('Top Plays Week 1', 'top-plays-week-1', 'Highlights you can''t miss.', '/assets/sample-thumb.jpg', 'https://www.w3schools.com/html/mov_bbb.mp4', 60, NOW(), 1);

INSERT IGNORE INTO shorts (title, slug, description, thumbnail, url, duration_seconds, published_at, is_published) VALUES
('Crazy Buzzer Beater', 'crazy-buzzer-beater', 'Unbelievable finish!', '/assets/sample-short.jpg', 'https://www.w3schools.com/html/mov_bbb.mp4', 15, NOW(), 1);

INSERT IGNORE INTO live_streams (title, slug, description, thumbnail, hls_url, is_live, starts_at, ends_at) VALUES
('Tonight Live: Finals', 'tonight-live-finals', 'Watch the finals live.', '/assets/sample-live.jpg', 'https://test-streams.mux.dev/x36xhzz/x36xhzz.m3u8', 1, NOW(), NULL);
