"""
Import YouTube links from elmeg as Video entities
"""
import csv
import sys
sys.path.insert(0, '/Users/ten/DEV/fediversion/backend')

import psycopg2
from datetime import datetime

# Connect to fediversion database via SSH tunnel
# Run: ssh -L 5433:localhost:5432 nexus-vector
conn = psycopg2.connect(
    host="localhost",
    port=5433,
    database="fediversion",
    user="fediversion",
    password="fediversion_password"
)
conn.autocommit = True
cur = conn.cursor()

# Get Goose vertical_id
cur.execute("SELECT id FROM vertical WHERE slug = 'goose'")
goose_vertical_id = cur.fetchone()[0]
print(f"Goose vertical_id: {goose_vertical_id}")

# Read CSV
videos_created = 0
links_created = 0
skipped = 0

with open('/tmp/perf_youtube_full.csv', 'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        youtube_url = row['youtube_link']
        show_slug = row['show_slug']
        song_title = row['song_title']
        show_date = row['show_date']
        
        # Check if video already exists
        cur.execute("SELECT id FROM video WHERE url = %s", (youtube_url,))
        existing = cur.fetchone()
        
        if existing:
            video_id = existing[0]
        else:
            # Create video
            cur.execute("""
                INSERT INTO video (url, title, platform, video_type, vertical_id, created_at)
                VALUES (%s, %s, 'youtube', 'single_song', %s, NOW())
                RETURNING id
            """, (youtube_url, f"{song_title} - {show_date}", goose_vertical_id))
            video_id = cur.fetchone()[0]
            videos_created += 1
        
        # Find the show in fediversion
        cur.execute("SELECT id FROM show WHERE slug = %s AND vertical_id = %s", (show_slug, goose_vertical_id))
        show_result = cur.fetchone()
        
        if show_result:
            show_id = show_result[0]
            # Link video to show
            cur.execute("""
                INSERT INTO videoshow (video_id, show_id)
                VALUES (%s, %s)
                ON CONFLICT DO NOTHING
            """, (video_id, show_id))
            links_created += 1
        else:
            skipped += 1

print(f"\nResults:")
print(f"  Videos created: {videos_created}")
print(f"  Show links created: {links_created}")
print(f"  Shows not found: {skipped}")

cur.close()
conn.close()