CREATE TABLE IF NOT EXISTS youtube_creators (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel_id VARCHAR(64) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL DEFAULT '',
  description MEDIUMTEXT NOT NULL,
  thumbnail_url TEXT NULL,
  channel_url TEXT NOT NULL,
  subscribers BIGINT UNSIGNED NULL,
  video_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  public_email VARCHAR(255) NOT NULL DEFAULT '',
  website_url TEXT NULL,
  other_urls_json JSON NULL,
  source_query VARCHAR(255) NOT NULL DEFAULT '',
  lifecycle_status ENUM('observed','shortlisted','draft_ready','excluded') NOT NULL DEFAULT 'observed',
  legacy_status VARCHAR(40) NOT NULL DEFAULT '',
  score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  analysis_json JSON NULL,
  analyzed_at DATETIME NULL,
  notes MEDIUMTEXT NOT NULL,
  imported_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX(score), INDEX(lifecycle_status), INDEX(subscribers), INDEX(source_query)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS youtube_searches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  query VARCHAR(255) NOT NULL,
  criteria_json JSON NOT NULL,
  result_count INT UNSIGNED NOT NULL DEFAULT 0,
  executed_at DATETIME NOT NULL,
  INDEX(executed_at), INDEX(query)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
