import sqlite3

conn = sqlite3.connect("data/hiffi_catalog.db")
cur = conn.cursor()

cur.execute("""
    WITH latest AS (
        SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.artist_id ORDER BY t.last_synced_at DESC, t.telemetry_id DESC) as rn
        FROM telemetry_metrics t
    )
    SELECT a.artist_id, a.artist_name, a.youtube_url, l.subscribers_count
    FROM artists a
    JOIN latest l ON a.artist_id = l.artist_id
    WHERE a.image_verified_flag = 1 AND (l.total_views = 0 OR l.total_views IS NULL)
    ORDER BY a.artist_name ASC
""")

rows = cur.fetchall()
print(f"=== REMAINING ZERO-VIEW ARTISTS ({len(rows)}) ===")
for i, r in enumerate(rows, 1):
    subs = f"{r[3]:,}" if r[3] is not None else "0"
    print(f"{i:2d}. {r[1]:<24} | Subs: {subs:>10} | URL: {r[2]}")

conn.close()