import sqlite3
from tmdbv3api import TMDb, Movie, TV
import time

# CONFIGURATION
TMDB_API_KEY = "3861797ddcdbe4c776f039479571f7bd"  # REPLACE THIS[citation:10]
DB_FILE = "metadata.db"
LANGUAGE = "de-DE"
MAX_PAGES = 500  # Pages to fetch per run. Reduce if hitting rate limits.
DELAY_SECONDS = 0.3  # Delay between API calls to be respectful

def setup_database():
    """Creates the necessary tables if they don't exist."""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # Table for Movies
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS movies (
            tmdb_id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            original_title TEXT,
            overview TEXT,
            release_date TEXT,
            popularity REAL,
            vote_average REAL,
            vote_count INTEGER,
            poster_path TEXT,
            backdrop_path TEXT,
            genre_ids TEXT, -- Storing as JSON string for simplicity
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # Table for TV Shows
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS tv_shows (
            tmdb_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            original_name TEXT,
            overview TEXT,
            first_air_date TEXT,
            popularity REAL,
            vote_average REAL,
            vote_count INTEGER,
            poster_path TEXT,
            backdrop_path TEXT,
            genre_ids TEXT,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    conn.commit()
    conn.close()
    print(f"[+] Database '{DB_FILE}' setup complete.")

def fetch_and_store_popular():
    """Fetches popular movies and TV shows and stores them in the DB."""
    # 1. Setup TMDB API
    tmdb = TMDb()
    tmdb.api_key = TMDB_API_KEY
    tmdb.language = LANGUAGE

    movie_api = Movie()
    tv_api = TV()

    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    print("[+] Starting to fetch popular movies...")
    # 2. Fetch Popular Movies
    for page in range(1, MAX_PAGES + 1):
        try:
            print(f"  -> Fetching movie page {page}/{MAX_PAGES}")
            popular_movies = movie_api.popular(page=page)
            time.sleep(DELAY_SECONDS)

            for item in popular_movies:
                # Convert genre list to a JSON string for storage
                genre_ids_str = str(item.get('genre_ids', []))
                cursor.execute("""
                    INSERT OR REPLACE INTO movies 
                    (tmdb_id, title, original_title, overview, release_date, 
                     popularity, vote_average, vote_count, poster_path, backdrop_path, genre_ids)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    item.id,
                    item.title,
                    item.original_title,
                    item.overview,
                    item.release_date,
                    item.popularity,
                    item.vote_average,
                    item.vote_count,
                    item.poster_path,
                    item.backdrop_path,
                    genre_ids_str
                ))
            conn.commit()
        except Exception as e:
            print(f"    [!] Error on movie page {page}: {e}")
            break

    print("[+] Starting to fetch popular TV shows...")
    # 3. Fetch Popular TV Shows
    for page in range(1, MAX_PAGES + 1):
        try:
            print(f"  -> Fetching TV page {page}/{MAX_PAGES}")
            popular_tv = tv_api.popular(page=page)
            time.sleep(DELAY_SECONDS)

            for item in popular_tv:
                genre_ids_str = str(item.get('genre_ids', []))
                cursor.execute("""
                    INSERT OR REPLACE INTO tv_shows 
                    (tmdb_id, name, original_name, overview, first_air_date, 
                     popularity, vote_average, vote_count, poster_path, backdrop_path, genre_ids)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    item.id,
                    item.name,
                    item.original_name,
                    item.overview,
                    item.first_air_date,
                    item.popularity,
                    item.vote_average,
                    item.vote_count,
                    item.poster_path,
                    item.backdrop_path,
                    genre_ids_str
                ))
            conn.commit()
        except Exception as e:
            print(f"    [!] Error on TV page {page}: {e}")
            break

    # 4. Summary
    cursor.execute("SELECT COUNT(*) FROM movies")
    movie_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM tv_shows")
    tv_count = cursor.fetchone()[0]

    conn.close()
    print(f"[+] Update complete! Database now contains {movie_count} movies and {tv_count} TV shows.")

def main():
    """Main function to run the update process."""
    print("TMDB Incremental Database Builder")
    print("=================================")
    setup_database()
    fetch_and_store_popular()
    print("[+] Script finished.")

if __name__ == "__main__":
    main()