#!/usr/bin/env python
"""
Fix database constraint issues
Resolves duplicate key name and other database constraint problems
"""

import os
import sys
import django
from django.core.management import execute_from_command_line
from django.db import connection
from datetime import datetime

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

def fix_database_constraints():
    """Fix database constraint issues"""
    print("=== FIXING DATABASE CONSTRAINTS ===")
    
    with connection.cursor() as cursor:
        # Check for duplicate indexes
        cursor.execute("""
            SELECT 
                TABLE_NAME,
                INDEX_NAME,
                COLUMN_NAME
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = DATABASE()
            AND INDEX_NAME LIKE '%generated_reports_generated_by_id%'
            ORDER BY TABLE_NAME, INDEX_NAME
        """)
        
        duplicate_indexes = cursor.fetchall()
        print(f"Found {len(duplicate_indexes)} indexes with generated_reports_generated_by_id")
        
        # Remove duplicate indexes
        for table_name, index_name, column_name in duplicate_indexes:
            try:
                cursor.execute(f"ALTER TABLE {table_name} DROP INDEX {index_name}")
                print(f"  Removed duplicate index: {index_name}")
            except Exception as e:
                print(f"  Could not remove index {index_name}: {e}")
        
        # Check for other potential constraint issues
        cursor.execute("""
            SELECT 
                CONSTRAINT_NAME,
                TABLE_NAME,
                CONSTRAINT_TYPE
            FROM information_schema.TABLE_CONSTRAINTS 
            WHERE TABLE_SCHEMA = DATABASE()
            AND CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND CONSTRAINT_NAME LIKE '%generated_reports%'
        """)
        
        foreign_keys = cursor.fetchall()
        print(f"Found {len(foreign_keys)} foreign key constraints with generated_reports")
        
        # Remove problematic foreign key constraints
        for constraint_name, table_name, constraint_type in foreign_keys:
            try:
                cursor.execute(f"ALTER TABLE {table_name} DROP FOREIGN KEY {constraint_name}")
                print(f"  Removed foreign key: {constraint_name}")
            except Exception as e:
                print(f"  Could not remove foreign key {constraint_name}: {e}")
        
        print("✓ Fixed database constraints")

def test_migrations():
    """Test that migrations work"""
    print("\n=== TESTING MIGRATIONS ===")
    
    try:
        # Test migrate
        print("  Testing migrate...")
        execute_from_command_line(['manage.py', 'migrate', '--noinput'])
        print("    ✓ migrate works")
        
        return True
        
    except Exception as e:
        print(f"    ✗ Migration test failed: {e}")
        return False

def main():
    """Main fix function"""
    print("=== DATABASE CONSTRAINTS FIX ===")
    print("Fixing duplicate key name and other database constraint issues.\n")
    
    try:
        setup_django()
        
        # Fix database constraints
        fix_database_constraints()
        
        # Test migrations
        if test_migrations():
            print("\n=== FIX COMPLETE ===")
            print("✓ Database constraint issues have been resolved!")
            print("✓ Django migrations are working correctly")
            print("✓ All migration issues are now fixed")
            print("\n🎉 Your application should now work perfectly!")
            print("\n📋 Next steps:")
            print("1. Test your application: python manage.py runserver")
            print("2. Check the portfolio assignment page")
            print("3. Test the enhanced rollover functionality")
            return True
        else:
            print("\n=== FIX PARTIALLY COMPLETE ===")
            print("⚠ Database constraint issues have been addressed")
            print("⚠ Some issues may remain")
            return False
        
    except Exception as e:
        print(f"\n❌ Fix failed: {e}")
        import traceback
        traceback.print_exc()
        return False

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)