#!/usr/bin/env python3
"""
Database Update Script
Populates container_format for existing records in the database
"""

import sqlite3
import json
import subprocess
from pathlib import Path
import sys

def get_container_format(filepath):
    """Get container format using ffprobe"""
    try:
        result = subprocess.run(
            ['ffprobe', '-v', 'quiet', '-print_format', 'json', '-show_format', str(filepath)],
            capture_output=True,
            text=True,
            timeout=30
        )

        if result.returncode == 0:
            info = json.loads(result.stdout)
            if 'format' in info:
                format_name = info['format'].get('format_name', '')
                return format_name.split(',')[0] if format_name else None
    except Exception as e:
        print(f"Error getting container format for {filepath}: {e}")

    return None

def update_database(db_path):
    """Update database with container format for existing records"""

    print(f"Opening database: {db_path}")
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    # Get all files without container_format
    cursor.execute("""
        SELECT id, filepath, container_format
        FROM files
        WHERE container_format IS NULL OR container_format = ''
    """)

    files = cursor.fetchall()
    total = len(files)

    if total == 0:
        print("No files need updating!")
        conn.close()
        return

    print(f"Found {total} files to update")

    updated = 0
    failed = 0

    for i, file in enumerate(files, 1):
        filepath = Path(file['filepath'])

        # Check if file exists
        if not filepath.exists():
            print(f"[{i}/{total}] Skipping (file not found): {filepath.name}")
            failed += 1
            continue

        # Get container format
        container = get_container_format(filepath)

        if container:
            cursor.execute("""
                UPDATE files
                SET container_format = ?
                WHERE id = ?
            """, (container, file['id']))

            print(f"[{i}/{total}] Updated: {filepath.name} -> {container}")
            updated += 1
        else:
            print(f"[{i}/{total}] Failed to get format: {filepath.name}")
            failed += 1

        # Commit every 10 files
        if i % 10 == 0:
            conn.commit()

    # Final commit
    conn.commit()
    conn.close()

    print(f"\nUpdate complete!")
    print(f"  Updated: {updated}")
    print(f"  Failed: {failed}")
    print(f"  Total: {total}")

if __name__ == '__main__':
    # Check if ffprobe is available
    try:
        subprocess.run(['ffprobe', '-version'], capture_output=True, check=True)
    except (subprocess.CalledProcessError, FileNotFoundError):
        print("Error: ffprobe not found. Please install ffmpeg.")
        sys.exit(1)

    # Get database path from command line or use default
    if len(sys.argv) > 1:
        db_path = sys.argv[1]
    else:
        # Try to find database in common locations
        possible_paths = [
            Path('data/state.db'),
            Path('C:/Users/ckoch/OneDrive/Documents/development/encoderPro/data/state.db'),
        ]

        db_path = None
        for p in possible_paths:
            if p.exists():
                db_path = p
                break

        if not db_path:
            print("Error: Could not find database file.")
            print("Usage: python update-database.py [path/to/state.db]")
            sys.exit(1)

    update_database(db_path)