CREATE TABLE IF NOT EXISTS `{prefix}tiktok_account` (
  `open_id` VARCHAR(64) NOT NULL,
  `idsite` INT UNSIGNED NOT NULL DEFAULT 149,
  `username` VARCHAR(128) NOT NULL DEFAULT '',
  `display_name` VARCHAR(255) NOT NULL DEFAULT '',
  `avatar_url` TEXT NULL,
  `profile_deep_link` TEXT NULL,
  `bio_description` TEXT NULL,
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `first_synced_at` DATETIME NULL,
  `last_synced_at` DATETIME NULL,
  `last_sync_error` TEXT NULL,
  PRIMARY KEY (`open_id`),
  KEY `idx_idsite` (`idsite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `{prefix}tiktok_account_snapshot` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `open_id` VARCHAR(64) NOT NULL,
  `snapshot_date` DATE NOT NULL,
  `follower_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `following_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `likes_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `video_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `fetched_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_account_day` (`open_id`, `snapshot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `{prefix}tiktok_video` (
  `video_id` VARCHAR(64) NOT NULL,
  `open_id` VARCHAR(64) NOT NULL,
  `title` TEXT NULL,
  `video_description` TEXT NULL,
  `create_time` INT UNSIGNED NOT NULL DEFAULT 0,
  `duration` INT UNSIGNED NOT NULL DEFAULT 0,
  `cover_image_url` TEXT NULL,
  `share_url` TEXT NULL,
  `embed_link` TEXT NULL,
  `first_seen_at` DATETIME NOT NULL,
  `last_seen_at` DATETIME NOT NULL,
  PRIMARY KEY (`video_id`),
  KEY `idx_open_id` (`open_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `{prefix}tiktok_video_snapshot` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `video_id` VARCHAR(64) NOT NULL,
  `open_id` VARCHAR(64) NOT NULL,
  `snapshot_date` DATE NOT NULL,
  `view_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `like_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `comment_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `share_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `fetched_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_video_day` (`video_id`, `snapshot_date`),
  KEY `idx_account_day` (`open_id`, `snapshot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;