import sqlite3
import json
import urllib.request

def ingest():
    db_path = '/Users/moeali/lectures.db'
    json_url = 'https://scripts.zahraamohammedali.poke.site/final_lecture_roster.json'
    
    # Fetch the JSON data from the URL
    with urllib.request.urlopen(json_url) as response:
        data = json.loads(response.read().decode())
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    cursor.execute("DROP TABLE IF EXISTS lectures")
    cursor.execute("DROP TABLE IF EXISTS series")
    
    cursor.execute('''
        CREATE TABLE series (
            series_id INTEGER PRIMARY KEY AUTOINCREMENT,
            series_title TEXT NOT NULL,
            external_playlist_id TEXT UNIQUE,
            difficulty TEXT
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE lectures (
            lecture_id INTEGER PRIMARY KEY AUTOINCREMENT,
            series_id INTEGER,
            title TEXT NOT NULL,
            video_id TEXT UNIQUE,
            transcription_status INTEGER DEFAULT 0,
            FOREIGN KEY (series_id) REFERENCES series (series_id)
        )
    ''')
    
    for entry in data:
        cursor.execute('''
            INSERT INTO series (series_title, external_playlist_id, difficulty)
            VALUES (?, ?, ?)
        ''', (entry['title'], entry['playlist_id'], entry['difficulty']))
        
        series_id = cursor.lastrowid
        
        if entry['videos']:
            lecture_batch = [
                (series_id, lec['title'], lec['video_id']) 
                for lec in entry['videos']
            ]
            cursor.executemany('''
                INSERT INTO lectures (series_id, title, video_id)
                VALUES (?, ?, ?)
            ''', lecture_batch)
            
    conn.commit()
    conn.close()
    print(f"Database rebuilt with {len(data)} real series at {db_path}")

if __name__ == "__main__":
    ingest()