#!/usr/bin/env python3
"""
Initialize encoderPro Database
================================
Creates the database schema if it doesn't exist.
Run this before starting the dashboard if the database doesn't exist.
"""

import os
import sqlite3
from pathlib import Path


def init_database(db_path: Path):
    """Initialize database with schema"""
    print(f"Initializing database at: {db_path}")

    # Create directory if needed
    db_path.parent.mkdir(parents=True, exist_ok=True)

    # Connect to database (creates file if doesn't exist)
    conn = sqlite3.connect(str(db_path))
    cursor = conn.cursor()

    # Create files table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filepath TEXT UNIQUE NOT NULL,
            relative_path TEXT NOT NULL,
            state TEXT NOT NULL,
            has_subtitles BOOLEAN,
            original_size INTEGER,
            encoded_size INTEGER,
            subtitle_count INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            started_at TIMESTAMP,
            completed_at TIMESTAMP,
            error_message TEXT,
            profile_name TEXT,
            encoder_used TEXT,
            encode_time_seconds REAL,
            fps REAL
        )
    """)

    # Create processing_history table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS processing_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_id INTEGER NOT NULL,
            profile_name TEXT,
            encoder_used TEXT,
            started_at TIMESTAMP,
            completed_at TIMESTAMP,
            success BOOLEAN,
            error_message TEXT,
            original_size INTEGER,
            encoded_size INTEGER,
            encode_time_seconds REAL,
            fps REAL,
            FOREIGN KEY (file_id) REFERENCES files (id)
        )
    """)

    # Create indices
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_state ON files(state)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_filepath ON files(filepath)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_profile ON files(profile_name)")

    conn.commit()
    conn.close()

    print(f"✅ Database initialized successfully at: {db_path}")


def main():
    """Main entry point"""
    # Get database path from environment or use default
    db_path = Path(os.getenv('STATE_DB', '/db/state.db'))

    if db_path.exists():
        print(f"Database already exists at: {db_path}")
        print("Run 'python3 reencode-v3.py -c config.yaml --scan-only' to populate it.")
    else:
        init_database(db_path)
        print()
        print("Next steps:")
        print("1. Run: python3 reencode-v3.py -c /config/config.yaml --scan-only")
        print("2. Start dashboard: python3 dashboard.py")


if __name__ == '__main__':
    main()