#!/usr/bin/env python3
"""
Database Management Utility for encoderPro
Provides convenient commands for inspecting and managing the state database
"""

import argparse
import sqlite3
import sys
from pathlib import Path
from typing import Dict, List


class DatabaseManager:
    """Utility for managing the state database"""
    
    def __init__(self, db_path: Path):
        self.db_path = db_path
        self.conn = None
        
        if not db_path.exists():
            print(f"ERROR: Database not found: {db_path}")
            sys.exit(1)
        
        self.conn = sqlite3.connect(str(db_path))
        self.conn.row_factory = sqlite3.Row
    
    def close(self):
        if self.conn:
            self.conn.close()
    
    def stats(self):
        """Show database statistics"""
        cursor = self.conn.cursor()
        
        print("\n" + "="*70)
        print("DATABASE STATISTICS")
        print("="*70)
        
        # Count by state
        cursor.execute("""
            SELECT state, COUNT(*) as count 
            FROM files 
            GROUP BY state 
            ORDER BY state
        """)
        
        total = 0
        for row in cursor.fetchall():
            state = row['state'].upper()
            count = row['count']
            total += count
            print(f"{state:15} {count:6}")
        
        print("-"*70)
        print(f"{'TOTAL':15} {total:6}")
        
        # Space statistics
        cursor.execute("""
            SELECT 
                SUM(original_size) as orig,
                SUM(encoded_size) as enc
            FROM files 
            WHERE state = 'completed'
        """)
        row = cursor.fetchone()
        
        if row and row['orig']:
            orig = row['orig']
            enc = row['enc'] or 0
            saved = orig - enc
            
            print("\n" + "="*70)
            print("SPACE STATISTICS (Completed Files)")
            print("="*70)
            print(f"Original size: {self._human_size(orig)}")
            print(f"Encoded size:  {self._human_size(enc)}")
            print(f"Space saved:   {self._human_size(saved)} ({saved/orig*100:.1f}%)")
        
        print("="*70 + "\n")
    
    def list_files(self, state: str = None, limit: int = None):
        """List files, optionally filtered by state"""
        cursor = self.conn.cursor()
        
        query = "SELECT * FROM files"
        params = []
        
        if state:
            query += " WHERE state = ?"
            params.append(state)
        
        query += " ORDER BY updated_at DESC"
        
        if limit:
            query += " LIMIT ?"
            params.append(limit)
        
        cursor.execute(query, params)
        rows = cursor.fetchall()
        
        if not rows:
            print("No files found")
            return
        
        print(f"\nFound {len(rows)} file(s):\n")
        print(f"{'State':<12} {'Size':<12} {'Path'}")
        print("-" * 80)
        
        for row in rows:
            state = row['state']
            size = self._human_size(row['original_size']) if row['original_size'] else 'N/A'
            path = row['relative_path']
            
            # Truncate long paths
            if len(path) > 50:
                path = path[:47] + "..."
            
            print(f"{state:<12} {size:<12} {path}")
    
    def failed_files(self):
        """Show failed files with error messages"""
        cursor = self.conn.cursor()
        cursor.execute("""
            SELECT relative_path, error_message, updated_at 
            FROM files 
            WHERE state = 'failed'
            ORDER BY updated_at DESC
        """)
        
        rows = cursor.fetchall()
        
        if not rows:
            print("No failed files")
            return
        
        print(f"\n{len(rows)} FAILED FILE(S):\n")
        print("="*80)
        
        for row in rows:
            print(f"File: {row['relative_path']}")
            print(f"Error: {row['error_message']}")
            print(f"Date: {row['updated_at']}")
            print("-"*80)
    
    def reset_state(self, from_state: str, to_state: str = 'pending'):
        """Reset files from one state to another"""
        cursor = self.conn.cursor()
        
        # Count affected
        cursor.execute("SELECT COUNT(*) FROM files WHERE state = ?", (from_state,))
        count = cursor.fetchone()[0]
        
        if count == 0:
            print(f"No files in state '{from_state}'")
            return
        
        print(f"Found {count} file(s) in state '{from_state}'")
        response = input(f"Reset to '{to_state}'? (yes/no): ")
        
        if response.lower() != 'yes':
            print("Cancelled")
            return
        
        cursor.execute("""
            UPDATE files 
            SET state = ?, error_message = NULL, updated_at = CURRENT_TIMESTAMP
            WHERE state = ?
        """, (to_state, from_state))
        
        self.conn.commit()
        print(f"✓ Reset {cursor.rowcount} file(s) to '{to_state}'")
    
    def reset_file(self, filepath: str, to_state: str = 'pending'):
        """Reset a specific file to a given state"""
        cursor = self.conn.cursor()
        
        # Check if exists
        cursor.execute("SELECT * FROM files WHERE filepath = ? OR relative_path = ?", 
                      (filepath, filepath))
        row = cursor.fetchone()
        
        if not row:
            print(f"File not found: {filepath}")
            return
        
        print(f"File: {row['relative_path']}")
        print(f"Current state: {row['state']}")
        response = input(f"Reset to '{to_state}'? (yes/no): ")
        
        if response.lower() != 'yes':
            print("Cancelled")
            return
        
        cursor.execute("""
            UPDATE files 
            SET state = ?, error_message = NULL, updated_at = CURRENT_TIMESTAMP
            WHERE filepath = ? OR relative_path = ?
        """, (to_state, filepath, filepath))
        
        self.conn.commit()
        print(f"✓ Reset to '{to_state}'")
    
    def search(self, pattern: str):
        """Search for files by path pattern"""
        cursor = self.conn.cursor()
        cursor.execute("""
            SELECT state, relative_path, original_size, has_subtitles
            FROM files 
            WHERE relative_path LIKE ?
            ORDER BY relative_path
        """, (f'%{pattern}%',))
        
        rows = cursor.fetchall()
        
        if not rows:
            print(f"No files matching '{pattern}'")
            return
        
        print(f"\nFound {len(rows)} file(s) matching '{pattern}':\n")
        print(f"{'State':<12} {'Subs':<6} {'Size':<12} {'Path'}")
        print("-" * 80)
        
        for row in rows:
            state = row['state']
            subs = 'Yes' if row['has_subtitles'] else 'No'
            size = self._human_size(row['original_size']) if row['original_size'] else 'N/A'
            path = row['relative_path']
            
            if len(path) > 45:
                path = path[:42] + "..."
            
            print(f"{state:<12} {subs:<6} {size:<12} {path}")
    
    def vacuum(self):
        """Vacuum (optimize) the database"""
        print("Vacuuming database...")
        self.conn.execute("VACUUM")
        print("✓ Database optimized")
    
    def _human_size(self, size: int) -> str:
        """Convert bytes to human readable format"""
        if not size:
            return "0 B"
        
        for unit in ['B', 'KB', 'MB', 'GB', 'TB']:
            if size < 1024.0:
                return f"{size:.2f} {unit}"
            size /= 1024.0
        return f"{size:.2f} PB"


def main():
    parser = argparse.ArgumentParser(
        description='Database Management Utility for encoderPro',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Examples:
  %(prog)s stats                          # Show statistics
  %(prog)s list -s completed -n 10        # List 10 most recent completed files
  %(prog)s failed                         # Show failed files with errors
  %(prog)s reset -f failed -t pending     # Reset all failed files to pending
  %(prog)s reset-file movie.mkv           # Reset specific file to pending
  %(prog)s search "Avatar"                # Search for files with "Avatar" in path
  %(prog)s vacuum                         # Optimize database
        """
    )
    
    parser.add_argument(
        '-d', '--database',
        type=Path,
        default=Path('/var/lib/encoderpro/state.db'),
        help='Path to database file (default: /var/lib/encoderpro/state.db)'
    )
    
    subparsers = parser.add_subparsers(dest='command', help='Command to run')
    
    # Stats command
    subparsers.add_parser('stats', help='Show database statistics')
    
    # List command
    list_parser = subparsers.add_parser('list', help='List files')
    list_parser.add_argument('-s', '--state', help='Filter by state')
    list_parser.add_argument('-n', '--limit', type=int, help='Limit number of results')
    
    # Failed command
    subparsers.add_parser('failed', help='Show failed files with errors')
    
    # Reset command
    reset_parser = subparsers.add_parser('reset', help='Reset files from one state to another')
    reset_parser.add_argument('-f', '--from-state', required=True, help='Source state')
    reset_parser.add_argument('-t', '--to-state', default='pending', help='Target state (default: pending)')
    
    # Reset file command
    reset_file_parser = subparsers.add_parser('reset-file', help='Reset a specific file')
    reset_file_parser.add_argument('filepath', help='File path (absolute or relative)')
    reset_file_parser.add_argument('-t', '--to-state', default='pending', help='Target state (default: pending)')
    
    # Search command
    search_parser = subparsers.add_parser('search', help='Search for files by path pattern')
    search_parser.add_argument('pattern', help='Search pattern (case-insensitive)')
    
    # Vacuum command
    subparsers.add_parser('vacuum', help='Optimize database')
    
    args = parser.parse_args()
    
    if not args.command:
        parser.print_help()
        return 1
    
    # Create manager
    db = DatabaseManager(args.database)
    
    try:
        # Execute command
        if args.command == 'stats':
            db.stats()
        
        elif args.command == 'list':
            db.list_files(args.state, args.limit)
        
        elif args.command == 'failed':
            db.failed_files()
        
        elif args.command == 'reset':
            db.reset_state(args.from_state, args.to_state)
        
        elif args.command == 'reset-file':
            db.reset_file(args.filepath, args.to_state)
        
        elif args.command == 'search':
            db.search(args.pattern)
        
        elif args.command == 'vacuum':
            db.vacuum()
    
    finally:
        db.close()
    
    return 0


if __name__ == '__main__':
    sys.exit(main())