`src/stages/15-backfill-video-metadata.js`

Code101 lines5.6 KBsrc/
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { getVideoInfo } from "../youtube-channel.js";
import { recordVideoSnapshot } from "../observations.js";

const db = getDb();
const limitArg = process.argv.find(value => value.startsWith("--limit="));
const limit = limitArg ? Math.max(1, Number(limitArg.slice(8)) || 1) : 25;
const videoArg = process.argv.find(value => value.startsWith("--video-id="));
const onlyVideoId = videoArg ? videoArg.slice("--video-id=".length) : null;
const force = process.argv.includes("--force");
const fullInfo = process.argv.includes("--full-info");

console.log("[video-metadata] Fetch mode:", fullInfo ? "FULL getInfo (watch-next parser may warn)" : "BASIC getBasicInfo (recommended; avoids watch-next parser mismatch)");

const videos = db.prepare(`
  SELECT video_id, title, thumbnail_url, duration_seconds, duration_precision, view_count,
    view_count_precision, published_at, description, likes_count, category, video_channel_id
  FROM videos
  WHERE (@videoId IS NULL OR video_id=@videoId)
    AND (@force=1 OR thumbnail_url IS NULL OR duration_seconds IS NULL
      OR duration_precision IS NULL OR duration_precision NOT IN ('exact','displayed_seconds')
      OR view_count IS NULL OR view_count_precision IS NULL OR view_count_precision NOT IN ('exact','exact_displayed')
      OR published_at IS NULL OR description IS NULL
      OR likes_count IS NULL OR category IS NULL OR video_channel_id IS NULL)
  ORDER BY
    CASE WHEN thumbnail_url IS NULL THEN 0 ELSE 1 END,
    CASE WHEN view_count IS NULL THEN 0 ELSE 1 END,
    discovered_at
  LIMIT @limit
`).all({ videoId: onlyVideoId, force: force ? 1 : 0, limit });
videos.splice(0, videos.length, ...filterQualifiedRows(db, videos, "video", { label: "15-backfill-video-metadata.js" }));

const updateVideo = db.prepare(`
  UPDATE videos SET
    title=COALESCE(NULLIF(@title,''), title),
    url=COALESCE(@url, url),
    channel_name=COALESCE(@channel_name, channel_name),
    video_channel_id=COALESCE(@video_channel_id, video_channel_id),
    duration=COALESCE(@duration, duration),
    duration_seconds=COALESCE(@duration_seconds, duration_seconds),
    duration_precision=CASE WHEN @duration_seconds IS NULL THEN duration_precision
      ELSE COALESCE(@duration_precision, duration_precision) END,
    view_count=COALESCE(@view_count, view_count),
    view_count_text=CASE WHEN @view_count IS NULL THEN view_count_text ELSE COALESCE(@view_count_text, view_count_text) END,
    view_count_precision=CASE WHEN @view_count IS NULL THEN view_count_precision
      ELSE COALESCE(@view_count_precision, view_count_precision) END,
    published_at=COALESCE(@published_at, published_at),
    published_at_text=CASE WHEN @published_at IS NULL AND published_at IS NOT NULL
      THEN published_at_text ELSE COALESCE(@published_at_text, published_at_text) END,
    published_at_precision=CASE WHEN @published_at IS NULL AND published_at IS NOT NULL
      THEN published_at_precision ELSE COALESCE(@published_at_precision, published_at_precision) END,
    likes_count=COALESCE(@likes_count, likes_count),
    category=COALESCE(@category, category),
    description=COALESCE(@description, description),
    thumbnail_url=COALESCE(@thumbnail_url, thumbnail_url),
    metadata_observed_at=@metadata_observed_at,
    metadata_source=@metadata_source,
    raw_metadata_json=COALESCE(@raw_metadata_json, raw_metadata_json)
  WHERE video_id=@video_id
`);

let fetched = 0, skipped = 0, failed = 0;
for (const [index, existing] of videos.entries()) {
  console.log(`\n[video-metadata] ${index + 1}/${videos.length}: ${existing.title}\n[video-metadata] ID: ${existing.video_id}`);
  try {
    const info = await getVideoInfo(existing.video_id, { fullInfo });
    const observedAt = new Date().toISOString();
    const record = { ...info, video_id: existing.video_id, metadata_observed_at: observedAt };
    db.transaction(() => {
      const { channel_url, ...updateRecord } = record;
      updateVideo.run(updateRecord);
      recordVideoSnapshot(record, info.metadata_source ?? "youtubei_video_info", observedAt);
    })();
    console.log("[video-metadata] Saved:", {
      thumbnail: Boolean(info.thumbnail_url), views: info.view_count,
      viewPrecision: info.view_count_precision, likes: info.likes_count,
      durationSeconds: info.duration_seconds, published: info.published_at_text,
      category: info.category, descriptionChars: info.description?.length ?? 0,
      channelId: info.video_channel_id
    });
    fetched++;
  } catch (error) {
    failed++;
    console.error(`[video-metadata] Failed video=${existing.video_id}:`, error instanceof Error ? error.message : error);
  }
  if (config.stage1_5.requestDelayMs > 0) await new Promise(resolve => setTimeout(resolve, config.stage1_5.requestDelayMs));
}
console.log("\n[video-metadata] Summary:", {
  selected: videos.length, fetched, skipped, failed,
  fetchMode: fullInfo ? "getInfo" : "getBasicInfo",
  snapshots: db.prepare("SELECT COUNT(*) AS count FROM video_metric_snapshots").get().count,
  withExactOrReportedViews: db.prepare("SELECT COUNT(*) AS count FROM videos WHERE view_count IS NOT NULL").get().count,
  withThumbnails: db.prepare("SELECT COUNT(*) AS count FROM videos WHERE thumbnail_url IS NOT NULL").get().count,
  withDurationSeconds: db.prepare("SELECT COUNT(*) AS count FROM videos WHERE duration_seconds IS NOT NULL").get().count,
  withDescriptions: db.prepare("SELECT COUNT(*) AS count FROM videos WHERE description IS NOT NULL").get().count
});
if (!videos.length) console.log("[video-metadata] No matching videos. Use --force to refresh existing fields, or --video-id=ID for a single video.");
Shown as it is in the repository at build time (2026-11-03); the home directory appears as <home>. If this page and the file differ, the file is right. Back to the catalogue.