#!/usr/bin/env python3
"""
Query performance monitoring script for VFX Project Management System.
Analyzes query execution plans and provides performance insights.
"""

import sqlite3
import sys
import time
from pathlib import Path

def monitor_query_performance():
    """Monitor and analyze query performance for key operations."""
    
    # Database path
    db_path = Path(__file__).parent / "vfx_project_management.db"
    
    if not db_path.exists():
        print(f"Database file not found at {db_path}")
        return False
    
    try:
        # Connect to database
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()
        
        print("=== Query Performance Monitoring ===\n")
        
        # Define key queries to test
        test_queries = [
            {
                "name": "Get active tasks by user",
                "query": """
                    SELECT t.id, t.name, t.status, t.task_type
                    FROM tasks t 
                    WHERE t.assigned_user_id = 1 
                    AND t.deleted_at IS NULL
                    ORDER BY t.created_at DESC
                    LIMIT 50
                """,
                "description": "Common query for user task dashboard"
            },
            {
                "name": "Get shots by episode (non-deleted)",
                "query": """
                    SELECT s.id, s.name, s.status, s.frame_start, s.frame_end
                    FROM shots s
                    WHERE s.episode_id = 1 
                    AND s.deleted_at IS NULL
                    ORDER BY s.name
                """,
                "description": "Common query for shot browser"
            },
            {
                "name": "Get assets by project (non-deleted)",
                "query": """
                    SELECT a.id, a.name, a.category, a.status
                    FROM assets a
                    WHERE a.project_id = 1 
                    AND a.deleted_at IS NULL
                    ORDER BY a.category, a.name
                """,
                "description": "Common query for asset browser"
            },
            {
                "name": "Get task submissions with reviews",
                "query": """
                    SELECT s.id, s.file_name, s.version_number, s.submitted_at,
                           r.decision, r.feedback
                    FROM submissions s
                    LEFT JOIN reviews r ON s.id = r.submission_id AND r.deleted_at IS NULL
                    WHERE s.task_id = 1 
                    AND s.deleted_at IS NULL
                    ORDER BY s.version_number DESC
                """,
                "description": "Common query for task detail panel"
            },
            {
                "name": "Get project tasks with status filter",
                "query": """
                    SELECT t.id, t.name, t.task_type, t.status, 
                           u.first_name, u.last_name,
                           COALESCE(s.name, a.name) as parent_name
                    FROM tasks t
                    LEFT JOIN users u ON t.assigned_user_id = u.id
                    LEFT JOIN shots s ON t.shot_id = s.id AND s.deleted_at IS NULL
                    LEFT JOIN assets a ON t.asset_id = a.id AND a.deleted_at IS NULL
                    WHERE t.project_id = 1 
                    AND t.status IN ('in_progress', 'pending_review')
                    AND t.deleted_at IS NULL
                    ORDER BY t.created_at DESC
                """,
                "description": "Complex query for project task overview"
            },
            {
                "name": "Get recent activity feed",
                "query": """
                    SELECT a.type, a.description, a.created_at,
                           u.first_name, u.last_name
                    FROM activities a
                    LEFT JOIN users u ON a.user_id = u.id
                    WHERE a.project_id = 1
                    ORDER BY a.created_at DESC
                    LIMIT 20
                """,
                "description": "Common query for activity feed"
            },
            {
                "name": "Get user workload summary",
                "query": """
                    SELECT u.id, u.first_name, u.last_name,
                           COUNT(t.id) as task_count,
                           COUNT(CASE WHEN t.status = 'in_progress' THEN 1 END) as active_tasks
                    FROM users u
                    LEFT JOIN tasks t ON u.id = t.assigned_user_id AND t.deleted_at IS NULL
                    WHERE u.role = 'artist'
                    GROUP BY u.id, u.first_name, u.last_name
                    ORDER BY task_count DESC
                """,
                "description": "Query for workload management"
            }
        ]
        
        print("Testing query performance...\n")
        
        for test in test_queries:
            print(f"Query: {test['name']}")
            print(f"Description: {test['description']}")
            
            # Get query plan
            explain_query = f"EXPLAIN QUERY PLAN {test['query']}"
            cursor.execute(explain_query)
            plan = cursor.fetchall()
            
            print("Execution Plan:")
            for step in plan:
                print(f"  {step[0]}: {step[3]}")
            
            # Measure execution time
            start_time = time.time()
            cursor.execute(test['query'])
            results = cursor.fetchall()
            end_time = time.time()
            
            execution_time = (end_time - start_time) * 1000  # Convert to milliseconds
            
            print(f"Execution Time: {execution_time:.2f}ms")
            print(f"Result Count: {len(results)}")
            
            # Check if indexes are being used
            plan_text = " ".join([step[3] for step in plan])
            if "USING INDEX" in plan_text:
                print("✓ Index usage detected")
            else:
                print("⚠ No index usage detected")
            
            print("-" * 60)
        
        return True
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return False
    except Exception as e:
        print(f"Unexpected error: {e}")
        return False
    finally:
        if conn:
            conn.close()

def analyze_index_usage():
    """Analyze which indexes are being used effectively."""
    
    db_path = Path(__file__).parent / "vfx_project_management.db"
    
    try:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()
        
        print("\n=== Index Usage Analysis ===\n")
        
        # Get all indexes
        cursor.execute("""
            SELECT name, tbl_name 
            FROM sqlite_master 
            WHERE type = 'index' 
            AND name NOT LIKE 'sqlite_%'
            ORDER BY tbl_name, name
        """)
        indexes = cursor.fetchall()
        
        print(f"Total custom indexes: {len(indexes)}")
        print("\nIndexes by table:")
        
        current_table = None
        for index_name, table_name in indexes:
            if table_name != current_table:
                print(f"\n{table_name}:")
                current_table = table_name
            print(f"  - {index_name}")
        
        # Analyze soft deletion indexes
        print("\n=== Soft Deletion Index Analysis ===")
        
        soft_deletion_tables = ['shots', 'assets', 'tasks', 'submissions', 
                               'task_attachments', 'production_notes', 'reviews']
        
        for table in soft_deletion_tables:
            cursor.execute(f"PRAGMA index_list({table})")
            table_indexes = cursor.fetchall()
            
            soft_del_indexes = [idx for idx in table_indexes if 'not_deleted' in idx[1]]
            
            print(f"{table}: {len(soft_del_indexes)} soft deletion indexes")
            for idx in soft_del_indexes:
                print(f"  - {idx[1]}")
        
        return True
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return False
    finally:
        if conn:
            conn.close()

def benchmark_soft_deletion_queries():
    """Benchmark queries with and without soft deletion filtering."""
    
    db_path = Path(__file__).parent / "vfx_project_management.db"
    
    try:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()
        
        print("\n=== Soft Deletion Query Benchmarks ===\n")
        
        # Test queries with soft deletion filtering
        benchmark_queries = [
            {
                "name": "Tasks with soft deletion filter",
                "query": "SELECT COUNT(*) FROM tasks WHERE deleted_at IS NULL"
            },
            {
                "name": "Tasks without filter (all records)",
                "query": "SELECT COUNT(*) FROM tasks"
            },
            {
                "name": "Shots with soft deletion filter", 
                "query": "SELECT COUNT(*) FROM shots WHERE deleted_at IS NULL"
            },
            {
                "name": "Shots without filter (all records)",
                "query": "SELECT COUNT(*) FROM shots"
            },
            {
                "name": "Assets with soft deletion filter",
                "query": "SELECT COUNT(*) FROM assets WHERE deleted_at IS NULL"
            },
            {
                "name": "Assets without filter (all records)",
                "query": "SELECT COUNT(*) FROM assets"
            }
        ]
        
        for benchmark in benchmark_queries:
            # Measure execution time
            start_time = time.time()
            cursor.execute(benchmark['query'])
            result = cursor.fetchone()[0]
            end_time = time.time()
            
            execution_time = (end_time - start_time) * 1000
            
            print(f"{benchmark['name']}: {result} records ({execution_time:.2f}ms)")
        
        return True
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return False
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    print("Query Performance Monitoring Tool")
    print("=" * 50)
    
    success = monitor_query_performance()
    
    if success:
        analyze_index_usage()
        benchmark_soft_deletion_queries()
        print("\nSUCCESS: Query performance analysis completed!")
    else:
        print("\n❌ Query performance analysis failed!")
        sys.exit(1)