#!/usr/bin/env python3
"""
Script to fix column types in portfolio_assignments table
Changes integer columns to CHAR(36) for UUID compatibility
"""

import os
import sys
import django
from django.db import connection

def setup_django():
    """Setup Django environment"""
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
    django.setup()

def fix_column_types():
    """Fix column types to support UUID"""
    
    print("🔧 Fixing column types in portfolio_assignments table...")
    
    # Define columns that need to be changed from INT to CHAR(36) for UUID
    uuid_columns = [
        'client_id',
        'portfolio_manager_id', 
        'assigned_by_id'
    ]
    
    with connection.cursor() as cursor:
        # Check current column types
        cursor.execute("""
            SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'portfolio_assignments'
            AND COLUMN_NAME IN ('client_id', 'portfolio_manager_id', 'assigned_by_id')
        """)
        
        current_columns = cursor.fetchall()
        print("Current column types:")
        for col_name, data_type, column_type in current_columns:
            print(f"  {col_name}: {column_type}")
        
        # Fix each column type
        for column_name in uuid_columns:
            print(f"\nFixing column: {column_name}")
            try:
                with connection.cursor() as fix_cursor:
                    # Change column type to CHAR(36) for UUID
                    fix_cursor.execute(f"""
                        ALTER TABLE portfolio_assignments 
                        MODIFY COLUMN {column_name} CHAR(36) NULL
                    """)
                print(f"✅ Fixed column: {column_name}")
            except Exception as e:
                print(f"❌ Error fixing {column_name}: {e}")

def main():
    """Main function to fix column types"""
    print("🔧 Fixing column types for UUID compatibility...")
    print("=" * 50)
    
    try:
        # Setup Django
        setup_django()
        print("✅ Django environment setup complete")
        
        # Fix column types
        fix_column_types()
        
        print("\n🎉 Column type fixes completed!")
        print("The portfolio assignment should now work with UUID fields.")
        
    except Exception as e:
        print(f"\n❌ Error: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()