#!/usr/bin/env python3
"""
Database index optimization script for VFX Project Management System.
Adds missing indexes to improve query performance, especially for soft deletion filtering.
"""

import sqlite3
import sys
from pathlib import Path

def optimize_database_indexes():
    """Add missing indexes to optimize database performance."""
    
    # 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("=== Database Index Optimization ===\n")
        
        # Define indexes to create
        indexes_to_create = [
            # Task-related indexes for performance
            {
                "name": "idx_tasks_assigned_user_not_deleted",
                "table": "tasks",
                "columns": "assigned_user_id",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for tasks by assigned user (non-deleted)"
            },
            {
                "name": "idx_tasks_status_not_deleted", 
                "table": "tasks",
                "columns": "status",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for tasks by status (non-deleted)"
            },
            {
                "name": "idx_tasks_type_not_deleted",
                "table": "tasks", 
                "columns": "task_type",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for tasks by type (non-deleted)"
            },
            {
                "name": "idx_tasks_project_not_deleted",
                "table": "tasks",
                "columns": "project_id",
                "condition": "WHERE deleted_at IS NULL", 
                "description": "Optimize queries for tasks by project (non-deleted)"
            },
            {
                "name": "idx_tasks_episode_not_deleted",
                "table": "tasks",
                "columns": "episode_id", 
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for tasks by episode (non-deleted)"
            },
            
            # Shot-related indexes
            {
                "name": "idx_shots_episode_not_deleted",
                "table": "shots",
                "columns": "episode_id",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for shots by episode (non-deleted)"
            },
            {
                "name": "idx_shots_status_not_deleted",
                "table": "shots", 
                "columns": "status",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for shots by status (non-deleted)"
            },
            
            # Asset-related indexes
            {
                "name": "idx_assets_project_not_deleted",
                "table": "assets",
                "columns": "project_id", 
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for assets by project (non-deleted)"
            },
            {
                "name": "idx_assets_category_not_deleted",
                "table": "assets",
                "columns": "category",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for assets by category (non-deleted)"
            },
            {
                "name": "idx_assets_status_not_deleted",
                "table": "assets",
                "columns": "status", 
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for assets by status (non-deleted)"
            },
            
            # Submission-related indexes
            {
                "name": "idx_submissions_user_not_deleted",
                "table": "submissions",
                "columns": "user_id",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for submissions by user (non-deleted)"
            },
            {
                "name": "idx_submissions_submitted_at_not_deleted",
                "table": "submissions", 
                "columns": "submitted_at",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries ordering submissions by date (non-deleted)"
            },
            
            # Production notes indexes
            {
                "name": "idx_production_notes_user_not_deleted",
                "table": "production_notes",
                "columns": "user_id",
                "condition": "WHERE deleted_at IS NULL", 
                "description": "Optimize queries for notes by user (non-deleted)"
            },
            {
                "name": "idx_production_notes_created_at_not_deleted",
                "table": "production_notes",
                "columns": "created_at",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries ordering notes by date (non-deleted)"
            },
            
            # Task attachments indexes
            {
                "name": "idx_task_attachments_user_not_deleted",
                "table": "task_attachments",
                "columns": "user_id", 
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for attachments by user (non-deleted)"
            },
            {
                "name": "idx_task_attachments_type_not_deleted",
                "table": "task_attachments",
                "columns": "attachment_type",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for attachments by type (non-deleted)"
            },
            
            # Reviews indexes
            {
                "name": "idx_reviews_reviewer_not_deleted",
                "table": "reviews",
                "columns": "reviewer_id",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for reviews by reviewer (non-deleted)"
            },
            {
                "name": "idx_reviews_decision_not_deleted", 
                "table": "reviews",
                "columns": "decision",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for reviews by decision (non-deleted)"
            },
            
            # Foreign key indexes for better join performance
            {
                "name": "idx_project_members_user_id",
                "table": "project_members",
                "columns": "user_id",
                "condition": "",
                "description": "Optimize joins on project members by user"
            },
            {
                "name": "idx_project_members_project_id", 
                "table": "project_members",
                "columns": "project_id",
                "condition": "",
                "description": "Optimize joins on project members by project"
            },
            {
                "name": "idx_episodes_project_id",
                "table": "episodes",
                "columns": "project_id", 
                "condition": "",
                "description": "Optimize queries for episodes by project"
            },
            
            # Composite indexes for complex queries
            {
                "name": "idx_tasks_composite_not_deleted",
                "table": "tasks",
                "columns": "project_id, status, assigned_user_id",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize complex task queries (project + status + user)"
            },
            {
                "name": "idx_submissions_task_version_not_deleted",
                "table": "submissions", 
                "columns": "task_id, version_number",
                "condition": "WHERE deleted_at IS NULL",
                "description": "Optimize queries for latest submission versions"
            }
        ]
        
        created_count = 0
        skipped_count = 0
        
        for index_def in indexes_to_create:
            index_name = index_def["name"]
            table_name = index_def["table"]
            columns = index_def["columns"]
            condition = index_def["condition"]
            description = index_def["description"]
            
            # Check if index already exists
            cursor.execute(f"PRAGMA index_list({table_name})")
            existing_indexes = [idx[1] for idx in cursor.fetchall()]
            
            if index_name in existing_indexes:
                print(f"SKIP: Index {index_name} already exists")
                skipped_count += 1
                continue
            
            # Create the index
            try:
                sql = f"CREATE INDEX {index_name} ON {table_name} ({columns})"
                if condition:
                    sql += f" {condition}"
                
                print(f"Creating index: {index_name}")
                print(f"  Table: {table_name}")
                print(f"  Columns: {columns}")
                print(f"  Description: {description}")
                
                cursor.execute(sql)
                created_count += 1
                print(f"SUCCESS: Created index {index_name}")
                
            except sqlite3.Error as e:
                print(f"ERROR: Failed to create index {index_name}: {e}")
                continue
            
            print()
        
        # Commit all changes
        conn.commit()
        
        print(f"=== Index Optimization Complete ===")
        print(f"Created: {created_count} new indexes")
        print(f"Skipped: {skipped_count} existing indexes")
        
        return True
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"Unexpected error: {e}")
        if conn:
            conn.rollback()
        return False
    finally:
        if conn:
            conn.close()

def verify_index_optimization():
    """Verify that the index optimization was successful."""
    
    db_path = Path(__file__).parent / "vfx_project_management.db"
    
    try:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()
        
        print("\n=== Verifying Index Optimization ===\n")
        
        # Count total indexes
        cursor.execute("""
            SELECT COUNT(*) 
            FROM sqlite_master 
            WHERE type = 'index' AND name NOT LIKE 'sqlite_%'
        """)
        total_indexes = cursor.fetchone()[0]
        
        print(f"Total indexes in database: {total_indexes}")
        
        # Check key performance indexes
        key_indexes = [
            "idx_tasks_assigned_user_not_deleted",
            "idx_tasks_status_not_deleted", 
            "idx_shots_episode_not_deleted",
            "idx_assets_project_not_deleted",
            "idx_submissions_submitted_at_not_deleted"
        ]
        
        print("\nKey performance indexes:")
        for index_name in key_indexes:
            cursor.execute("""
                SELECT name FROM sqlite_master 
                WHERE type = 'index' AND name = ?
            """, (index_name,))
            
            if cursor.fetchone():
                print(f"  ✓ {index_name}")
            else:
                print(f"  ✗ {index_name} (missing)")
        
        return True
        
    except sqlite3.Error as e:
        print(f"Verification error: {e}")
        return False
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    print("Database Index Optimization Tool")
    print("=" * 50)
    
    success = optimize_database_indexes()
    
    if success:
        verify_index_optimization()
        print("\nSUCCESS: Database index optimization completed!")
    else:
        print("\n❌ Database index optimization failed!")
        sys.exit(1)