CREATE DATABASE IF NOT EXISTS shivercast CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS 'shivercast_user'@'%' IDENTIFIED BY 'TEMP_PASSWORD_1';
GRANT ALL PRIVILEGES ON shivercast.* TO 'shivercast_user'@'%';
FLUSH PRIVILEGES;
USE shivercast;

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  description VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(role_id, permission_id),
  FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  email VARCHAR(190) NULL UNIQUE,
  password_hash VARCHAR(255) NULL,
  display_name VARCHAR(160) NOT NULL,
  avatar_url VARCHAR(500) NULL,
  bio TEXT NULL,
  status ENUM('active','pending','disabled') DEFAULT 'active',
  is_guest TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(user_id, role_id),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS follows (
  follower_id BIGINT UNSIGNED NOT NULL,
  followed_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(follower_id, followed_id),
  FOREIGN KEY(follower_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(followed_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS media_assets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT UNSIGNED NULL,
  title VARCHAR(220) NULL,
  original_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(160) NULL,
  file_size BIGINT UNSIGNED DEFAULT 0,
  media_type ENUM('image','video','audio','pdf','document','story','archive','other') DEFAULT 'other',
  summary TEXT NULL,
  meta_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX(media_type), INDEX(owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS books (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  creator_id BIGINT UNSIGNED NULL,
  title VARCHAR(240) NOT NULL,
  slug VARCHAR(260) NOT NULL UNIQUE,
  subtitle VARCHAR(240) NULL,
  summary TEXT NULL,
  cover_asset_id BIGINT UNSIGNED NULL,
  scare_level TINYINT UNSIGNED DEFAULT 2,
  age_min TINYINT UNSIGNED DEFAULT 8,
  age_max TINYINT UNSIGNED DEFAULT 14,
  status ENUM('draft','published','archived') DEFAULT 'draft',
  visibility ENUM('public','unlisted','private') DEFAULT 'public',
  tags_json JSON NULL,
  metrics_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(creator_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(cover_asset_id) REFERENCES media_assets(id) ON DELETE SET NULL,
  INDEX(status), INDEX(visibility), INDEX(scare_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  book_id BIGINT UNSIGNED NULL,
  creator_id BIGINT UNSIGNED NULL,
  title VARCHAR(240) NOT NULL,
  slug VARCHAR(260) NOT NULL UNIQUE,
  start_node VARCHAR(120) DEFAULT 'start',
  story_json JSON NULL,
  version VARCHAR(40) DEFAULT '0.1.0',
  status ENUM('draft','testing','published','archived') DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(book_id) REFERENCES books(id) ON DELETE SET NULL,
  FOREIGN KEY(creator_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS story_nodes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  story_id BIGINT UNSIGNED NOT NULL,
  node_key VARCHAR(120) NOT NULL,
  title VARCHAR(240) NULL,
  body MEDIUMTEXT NULL,
  mood VARCHAR(160) NULL,
  image_asset_id BIGINT UNSIGNED NULL,
  data_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_story_node(story_id, node_key),
  FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE CASCADE,
  FOREIGN KEY(image_asset_id) REFERENCES media_assets(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS story_progress (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  guest_key VARCHAR(120) NULL,
  story_id BIGINT UNSIGNED NULL,
  current_node VARCHAR(120) NULL,
  inventory_json JSON NULL,
  flags_json JSON NULL,
  endings_json JSON NULL,
  history_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE CASCADE,
  INDEX(guest_key), INDEX(story_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS projects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT UNSIGNED NULL,
  title VARCHAR(240) NOT NULL,
  slug VARCHAR(260) NOT NULL UNIQUE,
  summary TEXT NULL,
  cover_asset_id BIGINT UNSIGNED NULL,
  visibility ENUM('public','unlisted','private') DEFAULT 'public',
  status ENUM('active','paused','archived') DEFAULT 'active',
  meta_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(cover_asset_id) REFERENCES media_assets(id) ON DELETE SET NULL,
  INDEX(visibility), INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS project_members (
  project_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role VARCHAR(80) DEFAULT 'member',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(project_id,user_id),
  FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS articles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NULL,
  title VARCHAR(260) NOT NULL,
  slug VARCHAR(280) NOT NULL UNIQUE,
  excerpt TEXT NULL,
  body MEDIUMTEXT NULL,
  cover_asset_id BIGINT UNSIGNED NULL,
  status ENUM('draft','published','archived') DEFAULT 'draft',
  visibility ENUM('public','unlisted','private') DEFAULT 'public',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(cover_asset_id) REFERENCES media_assets(id) ON DELETE SET NULL,
  INDEX(status), INDEX(visibility)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS posts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NULL,
  project_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(60) DEFAULT 'post',
  entity_id BIGINT UNSIGNED NULL,
  title VARCHAR(240) NULL,
  body TEXT NULL,
  cover_asset_id BIGINT UNSIGNED NULL,
  visibility ENUM('public','followers','private') DEFAULT 'public',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE SET NULL,
  FOREIGN KEY(cover_asset_id) REFERENCES media_assets(id) ON DELETE SET NULL,
  INDEX(entity_type, entity_id), INDEX(visibility), INDEX(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(parent_id) REFERENCES comments(id) ON DELETE CASCADE,
  INDEX(entity_type, entity_id), INDEX(parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  guest_key VARCHAR(120) NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  reaction_type ENUM('like','love','bookmark','share','view','finish','follow') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_user_reaction(user_id, entity_type, entity_id, reaction_type),
  INDEX(guest_key), INDEX(entity_type, entity_id), INDEX(reaction_type),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS view_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  guest_key VARCHAR(120) NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(120) NULL,
  user_agent VARCHAR(255) NULL,
  ip_hash VARCHAR(128) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX(entity_type, entity_id), INDEX(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_rooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT UNSIGNED NULL,
  title VARCHAR(240) NOT NULL,
  room_type ENUM('direct','group','project','story') DEFAULT 'group',
  project_id BIGINT UNSIGNED NULL,
  story_id BIGINT UNSIGNED NULL,
  visibility ENUM('private','team','public') DEFAULT 'team',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE SET NULL,
  FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_members (
  room_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role VARCHAR(80) DEFAULT 'member',
  last_read_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(room_id,user_id),
  FOREIGN KEY(room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  guest_name VARCHAR(160) NULL,
  body TEXT NULL,
  packet_id VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX(room_id), INDEX(packet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  message_id BIGINT UNSIGNED NOT NULL,
  media_asset_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(message_id) REFERENCES chat_messages(id) ON DELETE CASCADE,
  FOREIGN KEY(media_asset_id) REFERENCES media_assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ingest_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  source_asset_id BIGINT UNSIGNED NULL,
  ingest_type ENUM('book','story','media','article','project','atom','other') DEFAULT 'other',
  status ENUM('queued','processing','done','failed') DEFAULT 'queued',
  title VARCHAR(260) NULL,
  summary TEXT NULL,
  extracted_text MEDIUMTEXT NULL,
  output_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY(source_asset_id) REFERENCES media_assets(id) ON DELETE SET NULL,
  INDEX(status), INDEX(ingest_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_packets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  packet_id VARCHAR(140) NOT NULL UNIQUE,
  packet_type VARCHAR(80) NOT NULL,
  project_id BIGINT UNSIGNED NULL,
  room_id BIGINT UNSIGNED NULL,
  payload_json JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(packet_type), INDEX(project_id), INDEX(room_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS story_timeline_branches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  story_id BIGINT UNSIGNED NULL,
  parent_branch_id BIGINT UNSIGNED NULL,
  branch_key VARCHAR(140) NOT NULL,
  title VARCHAR(240) NOT NULL,
  source_node_key VARCHAR(120) NULL,
  choice_label VARCHAR(240) NULL,
  sort_order INT DEFAULT 0,
  data_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_story_branch(story_id, branch_key),
  FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE CASCADE,
  FOREIGN KEY(parent_branch_id) REFERENCES story_timeline_branches(id) ON DELETE SET NULL,
  INDEX(branch_key), INDEX(source_node_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS story_timeline_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  story_id BIGINT UNSIGNED NULL,
  branch_id BIGINT UNSIGNED NULL,
  event_key VARCHAR(140) NOT NULL,
  node_key VARCHAR(120) NULL,
  event_type ENUM('start','node','choice','media','ending','cue','custom') DEFAULT 'node',
  title VARCHAR(240) NOT NULL,
  description TEXT NULL,
  start_ms INT UNSIGNED DEFAULT 0,
  duration_ms INT UNSIGNED DEFAULT 0,
  sort_order INT DEFAULT 0,
  data_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_story_event(story_id, event_key),
  FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE CASCADE,
  FOREIGN KEY(branch_id) REFERENCES story_timeline_branches(id) ON DELETE SET NULL,
  INDEX(node_key), INDEX(event_type), INDEX(start_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS story_timeline_media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  media_asset_id BIGINT UNSIGNED NOT NULL,
  role ENUM('image','video','audio','pdf','document','cover','background','attachment','other') DEFAULT 'attachment',
  display_mode ENUM('inline','card','background','lightbox','timeline','reader') DEFAULT 'reader',
  caption VARCHAR(500) NULL,
  position_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(event_id) REFERENCES story_timeline_events(id) ON DELETE CASCADE,
  FOREIGN KEY(media_asset_id) REFERENCES media_assets(id) ON DELETE CASCADE,
  INDEX(role), INDEX(display_mode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS metrics_daily (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  metric_date DATE NOT NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  views_count BIGINT UNSIGNED DEFAULT 0,
  likes_count BIGINT UNSIGNED DEFAULT 0,
  shares_count BIGINT UNSIGNED DEFAULT 0,
  comments_count BIGINT UNSIGNED DEFAULT 0,
  completions_count BIGINT UNSIGNED DEFAULT 0,
  UNIQUE KEY unique_metric(metric_date, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;