CREATE TABLE IF NOT EXISTS users (
  id BIGINT PRIMARY KEY,
  username VARCHAR(191) NULL,
  first_name VARCHAR(191) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trend_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category ENUM('iranian','foreign','cartoon_anime') NOT NULL,
  media_type ENUM('movie','series','person','cartoon','anime','unknown') NOT NULL DEFAULT 'unknown',
  title VARCHAR(255) NOT NULL,
  normalized_title VARCHAR(255) NOT NULL,
  source VARCHAR(80) NOT NULL,
  source_key VARCHAR(255) NULL,
  trend_score DECIMAL(5,2) NOT NULL DEFAULT 0,
  growth_score DECIMAL(7,2) NULL,
  search_volume BIGINT NULL,
  timeframe VARCHAR(50) NULL,
  trend_state ENUM('hot','rising','steady','breakout','unknown') DEFAULT 'unknown',
  meta_json JSON NULL,
  observed_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cat_score (category, trend_score),
  INDEX idx_title (normalized_title),
  INDEX idx_observed (observed_at)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS published_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  normalized_title VARCHAR(255) NOT NULL,
  category ENUM('iranian','foreign','cartoon_anime') NOT NULL,
  media_type VARCHAR(32) NULL,
  published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(255) NULL,
  UNIQUE KEY uniq_user_title_cat (user_id, normalized_title, category)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS selections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  trend_item_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_item (user_id, trend_item_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trend_cache (
  cache_key VARCHAR(190) PRIMARY KEY,
  payload LONGTEXT NOT NULL,
  expires_at DATETIME NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;