#!/usr/bin/env python3
"""
Comprehensive script to fix all missing columns in portfolio_assignments table
This handles is_active, unassigned_date, and any other missing columns
"""

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 check_and_add_columns():
    """Check and add all missing columns to portfolio_assignments table"""
    
    # Define all required columns with their SQL definitions
    required_columns = {
        'is_active': 'BOOLEAN DEFAULT TRUE',
        'unassigned_date': 'DATETIME NULL',
        'reason': 'TEXT NULL'
    }
    
    print("🔧 Checking and adding missing columns to portfolio_assignments table...")
    
    with connection.cursor() as cursor:
        # Get existing columns
            cursor.execute("""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'portfolio_assignments'
        """)
    existing_columns = [row[0] for row in cursor.fetchall()]
        
    print(f"Existing columns: {existing_columns}")
        
        # Add missing columns one by one with separate cursor connections
    for column_name, column_definition in required_columns.items():
            if column_name not in existing_columns:
                print(f"Adding missing column: {column_name}")
                try:
                    with connection.cursor() as add_cursor:
                        add_cursor.execute(f"""
                            ALTER TABLE portfolio_assignments 
                            ADD COLUMN {column_name} {column_definition}
                        """)
                    print(f"✅ Added column: {column_name}")
                except Exception as e:
                    print(f"❌ Error adding {column_name}: {e}")
                else:
                    print(f"✅ Column already exists: {column_name}")

def fake_migrations():
    """Mark all relevant migrations as applied"""
    print("\n🔧 Marking migrations as applied...")
    
    try:
        from django.core.management import call_command
        
        # Mark the migration as applied
        call_command('migrate', 'users', '0018', '--fake', verbosity=0)
        print("✅ Migration 0018 marked as applied!")
        
    except Exception as e:
        print(f"❌ Error marking migration: {e}")
        return False
    
    return True

def main():
    """Main function to fix all database schema issues"""
    print("🔧 Fixing ALL database schema issues for cPanel production...")
    print("=" * 60)
    
    try:
        # Setup Django
        setup_django()
        print("✅ Django environment setup complete")
        
        # Check and add all missing columns
        check_and_add_columns()
        
        # Mark migrations as applied
        if fake_migrations():
            print("\n🎉 ALL database schema fixes completed successfully!")
            print("The client assignment interface should now work properly.")
            print("\nFixed columns:")
            print("- is_active (BOOLEAN)")
            print("- unassigned_date (DATETIME)")
            print("- reason (TEXT)")
        else:
            print("\n❌ Failed to mark migrations as applied")
            sys.exit(1)
            
    except Exception as e:
        print(f"\n❌ Error: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()