"""
Video jobs database - AI video generation ko lagi database operations
Yo module le video baneko progress track garcha ra job management handle garcha
"""

import sqlite3
import os
import logging
from datetime import datetime
import sys
from database import get_cached_connection, with_db_connection, db_connection

# Parent directory lai path ma add garne database module import garna ko lagi
parent_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if parent_dir not in sys.path:
    sys.path.append(parent_dir)

# Main database module bata database connection import garne try
try:
    from database import get_cached_connection, with_db_connection
    USING_MYSQL = True
except ImportError:
    USING_MYSQL = False
    # Main database chaina bhane SQLite fallback use garne
    logging.warning("Main database module not available. Using SQLite for video jobs.")

# Logging setup
logger = logging.getLogger(__name__)

# SQLite database path
SQLITE_DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "data/video_jobs.db")

# Data directory exist garcha ki nai check garne
os.makedirs(os.path.dirname(SQLITE_DB_PATH), exist_ok=True)

def init_db():
    """Database lai initialize garne - tables haru banaune"""
    if USING_MYSQL:
        # Main database module ko MySQL use garne
        conn = get_cached_connection()
        try:
            cursor = conn.cursor()
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS video_jobs (
                job_id VARCHAR(36) PRIMARY KEY,
                user_id INT NOT NULL,
                subject TEXT NOT NULL,
                language VARCHAR(50) NOT NULL,
                status VARCHAR(20) NOT NULL,
                progress INT DEFAULT 0,
                video_path VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                completed_at TIMESTAMP NULL,
                error_message TEXT
            )
            ''')
            conn.commit()
        finally:
            cursor.close()
            conn.close()
    else:
        # SQLite fallback use garne
        conn = sqlite3.connect(SQLITE_DB_PATH)
        cursor = conn.cursor()
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS video_jobs (
            job_id TEXT PRIMARY KEY,
            user_id INTEGER NOT NULL,
            subject TEXT NOT NULL,
            language TEXT NOT NULL,
            status TEXT NOT NULL,
            progress INTEGER DEFAULT 0,
            video_path TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP,
            error_message TEXT
        )
        ''')
        conn.commit()
        conn.close()

# Module import huda nai database initialize garne
init_db()

def create_job(job_id, user_id, subject, language):
    """Naya video generation job database ma create garne"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                INSERT INTO video_jobs (job_id, user_id, subject, language, status, progress)
                VALUES (%s, %s, %s, %s, %s, %s)
                ''', (job_id, user_id, subject, language, 'processing', 0))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"Error creating job in MySQL: {e}")
            return False
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            cursor = conn.cursor()
            cursor.execute('''
            INSERT INTO video_jobs (job_id, user_id, subject, language, status, progress)
            VALUES (?, ?, ?, ?, ?, ?)
            ''', (job_id, user_id, subject, language, 'processing', 0))
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            logger.error(f"Error creating job in SQLite: {e}")
            return False

def update_job_progress(job_id, progress, message=None):
    """Job ko progress update garne database ma"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor()
                if message:
                    cursor.execute('''
                    UPDATE video_jobs SET progress = %s, updated_at = NOW(), error_message = %s
                    WHERE job_id = %s
                    ''', (progress, message, job_id))
                else:
                    cursor.execute('''
                    UPDATE video_jobs SET progress = %s, updated_at = NOW()
                    WHERE job_id = %s
                    ''', (progress, job_id))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"Error updating job progress in MySQL: {e}")
            return False
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            cursor = conn.cursor()
            if message:
                cursor.execute('''
                UPDATE video_jobs SET progress = ?, updated_at = CURRENT_TIMESTAMP, error_message = ?
                WHERE job_id = ?
                ''', (progress, message, job_id))
            else:
                cursor.execute('''
                UPDATE video_jobs SET progress = ?, updated_at = CURRENT_TIMESTAMP
                WHERE job_id = ?
                ''', (progress, job_id))
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            logger.error(f"Error updating job progress in SQLite: {e}")
            return False

def complete_job(job_id, video_path):
    """Job lai completed mark garne with video path"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                UPDATE video_jobs SET 
                    status = %s, 
                    progress = %s, 
                    video_path = %s,
                    completed_at = NOW(),
                    updated_at = NOW()
                WHERE job_id = %s
                ''', ('completed', 100, video_path, job_id))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"Error completing job in MySQL: {e}")
            return False
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            cursor = conn.cursor()
            cursor.execute('''
            UPDATE video_jobs SET 
                status = ?, 
                progress = ?, 
                video_path = ?,
                completed_at = CURRENT_TIMESTAMP,
                updated_at = CURRENT_TIMESTAMP
            WHERE job_id = ?
            ''', ('completed', 100, video_path, job_id))
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            logger.error(f"Error completing job in SQLite: {e}")
            return False

def fail_job(job_id, error_message):
    """Job lai failed mark garne with error message"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                UPDATE video_jobs SET 
                    status = %s, 
                    error_message = %s,
                    updated_at = NOW()
                WHERE job_id = %s
                ''', ('failed', error_message, job_id))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"Error marking job as failed in MySQL: {e}")
            return False
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            cursor = conn.cursor()
            cursor.execute('''
            UPDATE video_jobs SET 
                status = ?, 
                error_message = ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE job_id = ?
            ''', ('failed', error_message, job_id))
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            logger.error(f"Error marking job as failed in SQLite: {e}")
            return False

def cancel_job(job_id):
    """Job lai canceled mark garne"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                UPDATE video_jobs SET 
                    status = %s,
                    updated_at = NOW()
                WHERE job_id = %s
                ''', ('canceled', job_id))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"Error canceling job in MySQL: {e}")
            return False
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            cursor = conn.cursor()
            cursor.execute('''
            UPDATE video_jobs SET 
                status = ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE job_id = ?
            ''', ('canceled', job_id))
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            logger.error(f"Error canceling job in SQLite: {e}")
            return False

def get_job(job_id):
    """Get job details by ID"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor(dictionary=True)  # Use dictionary cursor
                cursor.execute('SELECT * FROM video_jobs WHERE job_id = %s', (job_id,))
                job = cursor.fetchone()
                return job
        except Exception as e:
            logger.error(f"Error getting job from MySQL: {e}")
            return None
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            conn.row_factory = sqlite3.Row  # Use Row factory for dict-like results
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM video_jobs WHERE job_id = ?', (job_id,))
            job = cursor.fetchone()
            if job:
                # Convert row to dictionary
                job_dict = {key: job[key] for key in job.keys()}
                conn.close()
                return job_dict
            conn.close()
            return None
        except Exception as e:
            logger.error(f"Error getting job from SQLite: {e}")
            return None

def get_user_jobs(user_id, status=None):
    """Get all jobs for a specific user, optionally filtered by status"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor(dictionary=True)  # Use dictionary cursor
                if status:
                    cursor.execute('''
                    SELECT * FROM video_jobs 
                    WHERE user_id = %s AND status = %s
                    ORDER BY created_at DESC
                    ''', (user_id, status))
                else:
                    cursor.execute('''
                    SELECT * FROM video_jobs 
                    WHERE user_id = %s
                    ORDER BY created_at DESC
                    ''', (user_id,))
                jobs = cursor.fetchall()
                return jobs
        except Exception as e:
            logger.error(f"Error getting user jobs from MySQL: {e}")
            return []
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            conn.row_factory = sqlite3.Row  # Use Row factory for dict-like results
            cursor = conn.cursor()
            if status:
                cursor.execute('''
                SELECT * FROM video_jobs 
                WHERE user_id = ? AND status = ?
                ORDER BY created_at DESC
                ''', (user_id, status))
            else:
                cursor.execute('''
                SELECT * FROM video_jobs 
                WHERE user_id = ?
                ORDER BY created_at DESC
                ''', (user_id,))
            
            jobs = []
            for row in cursor.fetchall():
                # Convert each row to dictionary
                job_dict = {key: row[key] for key in row.keys()}
                jobs.append(job_dict)
            
            conn.close()
            return jobs
        except Exception as e:
            logger.error(f"Error getting user jobs from SQLite: {e}")
            return []

def cleanup_old_jobs(days=7):
    """Clean up old completed and failed jobs"""
    if USING_MYSQL:
        try:
            with db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                DELETE FROM video_jobs
                WHERE status IN ('completed', 'failed') 
                AND created_at < DATE_SUB(NOW(), INTERVAL %s DAY)
                ''', (days,))
                deleted = cursor.rowcount
                conn.commit()
                return deleted
        except Exception as e:
            logger.error(f"Error cleaning up old jobs from MySQL: {e}")
            return 0
    else:
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            cursor = conn.cursor()
            cursor.execute('''
            DELETE FROM video_jobs
            WHERE status IN ('completed', 'failed') 
            AND datetime(created_at) < datetime('now', '-%s days')
            ''', (days,))
            deleted = cursor.rowcount
            conn.commit()
            conn.close()
            return deleted
        except Exception as e:
            logger.error(f"Error cleaning up old jobs from SQLite: {e}")
            return 0 