LinkDesk/backend/analyze_database_indexes.py

#!/usr/bin/env python3
"""
Script to analyze current database indexes and identify optimization opportunities.
"""

import sqlite3
import sys
from pathlib import Path

def analyze_database_indexes():
    """Analyze current database indexes and suggest optimizations."""
    
    # 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("=== Current Database Schema Analysis ===\n")
        
        # Get all tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = [row[0] for row in cursor.fetchall()]
        
        for table in tables:
            print(f"Table: {table}")
            
            # Get table info
            cursor.execute(f"PRAGMA table_info({table})")
            columns = cursor.fetchall()
            
            print("  Columns:")
            for col in columns:
                col_name, col_type, not_null, default, pk = col[1], col[2], col[3], col[4], col[5]
                pk_str = " (PRIMARY KEY)" if pk else ""
                print(f"    - {col_name}: {col_type}{pk_str}")
            
            # Get existing indexes
            cursor.execute(f"PRAGMA index_list({table})")
            indexes = cursor.fetchall()
            
            if indexes:
                print("  Existing Indexes:")
                for idx in indexes:
                    idx_name, unique, origin = idx[1], idx[2], idx[3]
                    unique_str = " (UNIQUE)" if unique else ""
                    print(f"    - {idx_name}{unique_str}")
                    
                    # Get index details
                    cursor.execute(f"PRAGMA index_info({idx_name})")
                    idx_info = cursor.fetchall()
                    if idx_info:
                        cols = [info[2] for info in idx_info]
                        print(f"      Columns: {', '.join(cols)}")
            else:
                print("  No indexes found")
            
            print()
        
        # Analyze foreign key relationships for index optimization
        print("=== Foreign Key Analysis ===\n")
        
        for table in tables:
            cursor.execute(f"PRAGMA foreign_key_list({table})")
            fks = cursor.fetchall()
            
            if fks:
                print(f"Table: {table}")
                for fk in fks:
                    from_col, to_table, to_col = fk[3], fk[2], fk[4]
                    print(f"  FK: {from_col} -> {to_table}.{to_col}")
                print()
        
        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 suggest_index_optimizations():
    """Suggest additional indexes for performance optimization."""
    
    print("=== Suggested Index Optimizations ===\n")
    
    suggestions = [
        {
            "table": "tasks",
            "index": "idx_tasks_assigned_user",
            "columns": ["assigned_user_id"],
            "reason": "Optimize queries filtering tasks by assigned user"
        },
        {
            "table": "tasks", 
            "index": "idx_tasks_status",
            "columns": ["status"],
            "reason": "Optimize queries filtering tasks by status"
        },
        {
            "table": "tasks",
            "index": "idx_tasks_type",
            "columns": ["task_type"],
            "reason": "Optimize queries filtering tasks by type"
        },
        {
            "table": "submissions",
            "index": "idx_submissions_created_at",
            "columns": ["created_at"],
            "reason": "Optimize queries ordering submissions by creation date"
        },
        {
            "table": "activities",
            "index": "idx_activities_entity",
            "columns": ["entity_type", "entity_id"],
            "reason": "Optimize activity queries by entity"
        },
        {
            "table": "activities",
            "index": "idx_activities_created_at",
            "columns": ["created_at"],
            "reason": "Optimize activity feed queries by date"
        },
        {
            "table": "shots",
            "index": "idx_shots_episode",
            "columns": ["episode_id"],
            "reason": "Optimize queries filtering shots by episode"
        },
        {
            "table": "assets",
            "index": "idx_assets_project",
            "columns": ["project_id"],
            "reason": "Optimize queries filtering assets by project"
        },
        {
            "table": "tasks",
            "index": "idx_tasks_composite",
            "columns": ["shot_id", "asset_id", "status"],
            "reason": "Optimize complex queries filtering by parent and status"
        }
    ]
    
    for suggestion in suggestions:
        print(f"Table: {suggestion['table']}")
        print(f"  Suggested Index: {suggestion['index']}")
        print(f"  Columns: {', '.join(suggestion['columns'])}")
        print(f"  Reason: {suggestion['reason']}")
        print()

if __name__ == "__main__":
    print("Database Index Analysis Tool")
    print("=" * 50)
    
    if analyze_database_indexes():
        suggest_index_optimizations()
    else:
        print("Failed to analyze database")
        sys.exit(1)