YouTube Channel Weekly Digest Pipeline

import sqlite3
import requests
from datetime import date, timedelta
import time

DB_PATH = "youtube_digest.db"
SCRAVIO_KEY = "YOUR_API_KEY"

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS topics (topic_name TEXT, search_query TEXT);
        CREATE TABLE IF NOT EXISTS videos (
            video_id TEXT PRIMARY KEY, topic TEXT, title TEXT,
            channel TEXT, view_count INTEGER, published_date TEXT, url TEXT
        );
    """)
    conn.commit()
    return conn

def search_youtube(query: str) -> list:
    resp = requests.post(
        "https://api.scavio.dev/api/v1/search",
        headers={"x-api-key": SCRAVIO_KEY},
        json={"query": query, "platform": "youtube", "num": 20, "time_range": "week"}
    )
    resp.raise_for_status()
    return resp.json().get("results", [])

def run():
    conn = init_db()
    week_ago = (date.today() - timedelta(days=7)).isoformat()
    topics = conn.execute("SELECT topic_name, search_query FROM topics").fetchall()
    digest_lines = [f"# YouTube Digest - Week of {date.today().isoformat()}\n"]
    for topic_name, search_query in topics:
        results = search_youtube(search_query)
        new_videos = []
        for r in results:
            vid_id = r.get("video_id") or r.get("url", "").split("v=")[-1]
            existing = conn.execute("SELECT 1 FROM videos WHERE video_id=?", (vid_id,)).fetchone()
            if existing:
                continue
            pub_date = r.get("published_date", "")
            if pub_date and pub_date < week_ago:
                continue
            conn.execute("INSERT OR IGNORE INTO videos VALUES (?,?,?,?,?,?,?)",
                         (vid_id, topic_name, r.get("title"), r.get("channel"),
                          r.get("view_count", 0), pub_date, r.get("url")))
            new_videos.append(r)
        conn.commit()
        new_videos.sort(key=lambda x: x.get("view_count", 0), reverse=True)
        digest_lines.append(f"\n## {topic_name} ({len(new_videos)} new videos)")
        for v in new_videos[:5]:
            views = f"{v.get('view_count', 0):,}"
            digest_lines.append(f"- [{v.get('title')}]({v.get('url')}) | {v.get('channel')} | {views} views")
        time.sleep(0.3)
    digest = "\n".join(digest_lines)
    print(digest)
    # Send via email or Slack here

if __name__ == "__main__":
    run()

const Database = require('better-sqlite3');
const fetch = require('node-fetch');

const DB_PATH = 'youtube_digest.db';
const SCRAVIO_KEY = 'YOUR_API_KEY';

const db = new Database(DB_PATH);
db.exec(`
  CREATE TABLE IF NOT EXISTS topics (topic_name TEXT, search_query TEXT);
  CREATE TABLE IF NOT EXISTS videos (
    video_id TEXT PRIMARY KEY, topic TEXT, title TEXT,
    channel TEXT, view_count INTEGER, published_date TEXT, url TEXT
  );
`);

async function searchYoutube(query) {
  const res = await fetch('https://api.scavio.dev/api/v1/search', {
    method: 'POST',
    headers: { 'x-api-key': SCRAVIO_KEY, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query, platform: 'youtube', num: 20, time_range: 'week' })
  });
  return (await res.json()).results || [];
}

async function run() {
  const weekAgo = new Date(Date.now() - 7 * 86400000).toISOString().slice(0, 10);
  const topics = db.prepare('SELECT topic_name, search_query FROM topics').all();
  const lines = [`# YouTube Digest - ${new Date().toISOString().slice(0, 10)}`];
  for (const { topic_name, search_query } of topics) {
    const results = await searchYoutube(search_query);
    const newVideos = [];
    for (const r of results) {
      const vid_id = r.video_id || (r.url || '').split('v=')[1]?.slice(0, 11);
      if (!vid_id) continue;
      const exists = db.prepare('SELECT 1 FROM videos WHERE video_id=?').get(vid_id);
      if (exists) continue;
      if (r.published_date && r.published_date < weekAgo) continue;
      db.prepare('INSERT OR IGNORE INTO videos VALUES (?,?,?,?,?,?,?)').run(
        vid_id, topic_name, r.title, r.channel, r.view_count || 0, r.published_date, r.url
      );
      newVideos.push(r);
    }
    newVideos.sort((a, b) => (b.view_count || 0) - (a.view_count || 0));
    lines.push(`\n## ${topic_name} (${newVideos.length} new videos)`);
    newVideos.slice(0, 5).forEach(v => {
      lines.push(`- [${v.title}](${v.url}) | ${v.channel} | ${(v.view_count||0).toLocaleString()} views`);
    });
    await new Promise(r => setTimeout(r, 300));
  }
  console.log(lines.join('\n'));
}

run().catch(console.error);