CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  legacy_mongo_id VARCHAR(64) NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_email_verified TINYINT(1) NOT NULL DEFAULT 0,
  email_verified_at DATETIME NULL,
  last_login_at DATETIME NULL,
  verification_token_hash CHAR(64) NULL,
  reset_token_hash CHAR(64) NULL,
  reset_token_expires_at DATETIME NULL,
  preferred_audio_type ENUM('sub', 'dub') NULL,
  sub_watch_count INT UNSIGNED NOT NULL DEFAULT 0,
  dub_watch_count INT UNSIGNED NOT NULL DEFAULT 0,
  playback_quality_mode ENUM('auto', 'manual') NOT NULL DEFAULT 'auto',
  playback_quality_height INT UNSIGNED NULL,
  coins INT NOT NULL DEFAULT 0,
  is_test_account TINYINT(1) NOT NULL DEFAULT 0,
  coin_activity_remainder_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  last_coin_activity_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_unique (email),
  UNIQUE KEY users_legacy_mongo_id_unique (legacy_mongo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE auth_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY auth_sessions_token_hash_unique (token_hash),
  KEY auth_sessions_user_id_index (user_id),
  CONSTRAINT auth_sessions_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE watch_progress (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  anime_id VARCHAR(190) NOT NULL,
  anime_name VARCHAR(255) NOT NULL,
  anime_image TEXT NULL,
  episode_id VARCHAR(190) NOT NULL,
  episode_name VARCHAR(255) NOT NULL,
  audio_mode ENUM('sub', 'dub') NULL,
  timestamp_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  duration_seconds INT UNSIGNED NULL,
  status ENUM('watching', 'completed') NOT NULL DEFAULT 'watching',
  last_watched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  legacy_mongo_id VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY watch_progress_user_episode_unique (user_id, episode_id),
  KEY watch_progress_user_anime_index (user_id, anime_id),
  KEY watch_progress_legacy_mongo_id_index (legacy_mongo_id),
  CONSTRAINT watch_progress_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE watchlist_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  anime_id VARCHAR(190) NOT NULL,
  title VARCHAR(255) NOT NULL,
  image TEXT NULL,
  release_date VARCHAR(80) NULL,
  legacy_mongo_id VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY watchlist_user_anime_unique (user_id, anime_id),
  KEY watchlist_legacy_mongo_id_index (legacy_mongo_id),
  CONSTRAINT watchlist_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE coin_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  delta INT NOT NULL,
  balance_after INT NOT NULL,
  reason VARCHAR(120) NOT NULL,
  metadata_json JSON NULL,
  created_by_admin_id BIGINT UNSIGNED NULL,
  legacy_mongo_id VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY coin_ledger_user_id_index (user_id),
  KEY coin_ledger_legacy_mongo_id_index (legacy_mongo_id),
  CONSTRAINT coin_ledger_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supporters (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  legacy_mongo_id VARCHAR(64) NULL,
  donor_name VARCHAR(160) NOT NULL,
  is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
  amount DECIMAL(12, 2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'INR',
  source VARCHAR(80) NOT NULL,
  payment_id VARCHAR(190) NULL,
  verification_status VARCHAR(40) NOT NULL DEFAULT 'verified',
  contributed_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,
  PRIMARY KEY (id),
  UNIQUE KEY supporters_legacy_mongo_id_unique (legacy_mongo_id),
  KEY supporters_published_at_index (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  legacy_mongo_id VARCHAR(64) NULL,
  username VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT 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,
  PRIMARY KEY (id),
  UNIQUE KEY admin_users_username_unique (username),
  UNIQUE KEY admin_users_legacy_mongo_id_unique (legacy_mongo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE legacy_migration_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source VARCHAR(120) NOT NULL DEFAULT 'mongodb',
  source_snapshot VARCHAR(190) NULL,
  users_count INT UNSIGNED NOT NULL DEFAULT 0,
  watch_progress_count INT UNSIGNED NOT NULL DEFAULT 0,
  watchlist_count INT UNSIGNED NOT NULL DEFAULT 0,
  supporters_count INT UNSIGNED NOT NULL DEFAULT 0,
  coin_ledger_count INT UNSIGNED NOT NULL DEFAULT 0,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  notes TEXT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;