api_tool.py

import requests
import sqlite3
import json
import os
import argparse
import time
from pathlib import Path
from dotenv import load_dotenv

# Load environment variables
load_dotenv(".env.local")
BASE_URL = os.getenv("BASE_URL", "https://api.themoviedb.org/3")
API_KEY = os.getenv("API_KEY")

# Directories
CACHE_DIR = Path('./cache')
CACHE_DIR.mkdir(exist_ok=True)
OUTPUT_DIR = Path('./output')
OUTPUT_DIR.mkdir(exist_ok=True)
DB_PATH = 'data.db'

# ----------------- Database Setup -----------------
def setup_database():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS movies (
            id INTEGER PRIMARY KEY,
            title TEXT,
            release_date TEXT,
            overview TEXT,
            popularity REAL,
            vote_average REAL,
            vote_count INTEGER
        )
    ''')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_title ON movies(title)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_release_date ON movies(release_date)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_popularity ON movies(popularity)')
    conn.commit()
    conn.close()
    print("Database setup complete.")

# ----------------- API Fetch -----------------
def fetch_movie_page(page=1, retries=3, backoff=2):
    url = f"{BASE_URL}/movie/popular?api_key={API_KEY}&page={page}"
    attempt = 0
    while attempt < retries:
        try:
            response = requests.get(url, timeout=10) ## to optimize this use  session.get()
            if response.status_code == 200:
                return response.json()
            else:
                print(f"Error fetching page {page}: {response.status_code}")
                return None
        except requests.exceptions.RequestException as e:
            attempt += 1
            print(f"Request failed (attempt {attempt}/{retries}) for page {page}: {e}")
            time.sleep(backoff ** attempt)
    print(f"Failed to fetch page {page} after {retries} retries.")
    return None

# ----------------- Cache & DB -----------------
def cache_movie_data(movie_id, data):
    cache_file = CACHE_DIR / f'movie_{movie_id}.json' # the file path for savingthe cache
    with open(cache_file, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=4) # writes the data inot the file objects
    print(f"Cached movie ID {movie_id} data.")
    return cache_file

def save_movie_db(movie_data):
    conn = sqlite3.connect(DB_PATH) 
    cursor = conn.cursor()## VALUES ({id}, '{title}')
    cursor.execute('''
        INSERT OR REPLACE INTO movies (id, title, release_date, overview, popularity, vote_average, vote_count)
        VALUES (?, ?, ?, ?, ?, ?, ?) 
    ''', (
        movie_data['id'],
        movie_data['title'],
        movie_data.get('release_date', ''),
        movie_data.get('overview', ''),
        movie_data.get('popularity', 0.0),
        movie_data.get('vote_average', 0.0),
        movie_data.get('vote_count', 0)
    ))
    conn.commit()
    conn.close()
    print(f"Saved movie ID {movie_data['id']} to database.")

# ----------------- Filter Query -----------------
def filter_movies(filter_params=None, limit=5):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    query = "SELECT * FROM movies"
    conditions = []
    values = []

    if filter_params:
        for key, value in filter_params.items():
            if key == 'title':
                conditions.append("title LIKE ?")
                values.append(f"%{value}%")
            elif key == 'release_date':
                conditions.append("release_date = ?")
                values.append(value)
            elif key == 'popularity_gt':
                conditions.append("popularity > ?")
                values.append(value)
            elif key == 'vote_average_gt':
                conditions.append("vote_average > ?")
                values.append(value)

    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    query += " ORDER BY popularity DESC LIMIT ?"
    values.append(limit)

    cursor.execute(query, values)
    results = cursor.fetchall()
    conn.close()

    movies = []
    for row in results:
        movies.append({
            'id': row[0],
            'title': row[1],
            'release_date': row[2],
            'overview': row[3],
            'popularity': row[4],
            'vote_average': row[5],
            'vote_count': row[6]
        })

    output_file = OUTPUT_DIR / f'filtered_movies_{int(time.time())}.json'
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump(movies, f, ensure_ascii=False, indent=4)
    print(f"Filtered movies saved to {output_file}")
    return output_file

# ----------------- CLI Commands -----------------
def command_fetch(args):
    print("Fetch operation started...")
    setup_database()
    total_pages = args.pages

    for page in range(1, total_pages + 1):
        data = fetch_movie_page(page)
        if data and "results" in data:
            for movie in data["results"]:
                cache_movie_data(movie["id"], movie)
                save_movie_db(movie)
        else:
            print(f"No data fetched for page {page}")
        time.sleep(1)  # small delay to respect rate limits

def command_filter(args):
    print("Querying database...")
    filter_params = {}
    if args.title:
        filter_params['title'] = args.title
    if args.release_date:
        filter_params['release_date'] = args.release_date
    if args.popularity_gt:
        filter_params['popularity_gt'] = args.popularity_gt
    if args.vote_average_gt:
        filter_params['vote_average_gt'] = args.vote_average_gt

    result_file = filter_movies(filter_params, limit=args.limit)
    print(f"Filtered results saved to {result_file}")

# ----------------- Main -----------------
def main():
    parser = argparse.ArgumentParser(description="TMDB Movie Data Tool")
    subparsers = parser.add_subparsers(dest='command')

    # Fetch
    fetch_parser = subparsers.add_parser('fetch', help='Fetch popular movies from TMDB API')
    fetch_parser.add_argument('--pages', type=int, default=1, help='Number of pages to fetch')
    fetch_parser.set_defaults(func=command_fetch)

    # Filter
    filter_parser = subparsers.add_parser('filter', help='Filter movies from the database')
    filter_parser.add_argument('--title', type=str, help='Filter by title')
    filter_parser.add_argument('--release_date', type=str, help='Filter by release date (YYYY-MM-DD)')
    filter_parser.add_argument('--popularity_gt', type=float, help='Filter by popularity greater than value')
    filter_parser.add_argument('--vote_average_gt', type=float, help='Filter by vote_average greater than value')
    filter_parser.add_argument('--limit', type=int, default=5, help='Limit number of results')
    filter_parser.set_defaults(func=command_filter)

    args = parser.parse_args()
    if hasattr(args, 'func'):
        args.func(args)
    else:
        parser.print_help()

if __name__ == '__main__':
    main()