#!/usr/bin/env python3
"""
Verification script to check that all soft deletion columns and indexes are properly created.
"""

import sqlite3
from pathlib import Path

def verify_soft_deletion_schema():
    """Verify that all soft deletion columns and indexes exist."""
    
    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:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()
        
        # Tables to check
        tables = [
            "shots",
            "assets", 
            "tasks",
            "submissions",
            "task_attachments",
            "production_notes",
            "reviews"
        ]
        
        print("=== Soft Deletion Schema Verification ===\n")
        
        all_verified = True
        
        for table_name in tables:
            print(f"Checking {table_name} table:")
            
            # Check table structure
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = {column[1]: column[2] for column in cursor.fetchall()}
            
            # Check for soft deletion columns
            has_deleted_at = 'deleted_at' in columns
            has_deleted_by = 'deleted_by' in columns
            
            if has_deleted_at and has_deleted_by:
                print(f"  SUCCESS: Soft deletion columns present")
            else:
                print(f"  ERROR: Missing soft deletion columns")
                if not has_deleted_at:
                    print(f"    - deleted_at column missing")
                if not has_deleted_by:
                    print(f"    - deleted_by column missing")
                all_verified = False
            
            # Check for partial index
            cursor.execute(f"PRAGMA index_list({table_name})")
            indexes = [index[1] for index in cursor.fetchall()]
            
            expected_index = f"idx_{table_name}_not_deleted"
            if expected_index in indexes:
                print(f"  SUCCESS: Partial index {expected_index} exists")
            else:
                print(f"  ERROR: Partial index {expected_index} missing")
                all_verified = False
            
            print()
        
        if all_verified:
            print("SUCCESS: All soft deletion schema changes verified!")
            print("\nSummary:")
            print("- All tables have deleted_at and deleted_by columns")
            print("- All tables have partial indexes for efficient querying")
            print("- Schema is ready for soft deletion implementation")
        else:
            print("ERROR: Some schema changes are missing!")
        
        return all_verified
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return False
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    verify_soft_deletion_schema()