#!/usr/bin/env python
"""
Comprehensive Migration Analyzer
Collects all migration errors and dependencies for complete fix
"""

import os
import sys
import django
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 get_all_migrations():
    """Get all migrations from database"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT app, name, applied 
            FROM django_migrations 
            ORDER BY app, name
        """)
        return cursor.fetchall()

def analyze_migration_dependencies():
    """Analyze all migration dependencies and conflicts"""
    print("=== COMPREHENSIVE MIGRATION ANALYSIS ===")
    print(f"Analysis Date: {datetime.now()}")
    print("=" * 50)
    
    migrations = get_all_migrations()
    
    # Group by app
    apps = {}
    for app, name, applied in migrations:
        if app not in apps:
            apps[app] = []
        apps[app].append((name, applied))
    
    print("\n📊 CURRENT MIGRATION STATUS:")
    print("-" * 30)
    
    for app in sorted(apps.keys()):
        print(f"\n🔹 {app.upper()} APP:")
        for name, applied in apps[app]:
            status = "✓" if applied else "✗"
            print(f"  {status} {name}")
    
    # Check for common problematic patterns
    print("\n🔍 DEPENDENCY ANALYSIS:")
    print("-" * 30)
    
    # Check for merge migrations that might have wrong dependencies
    merge_migrations = []
    for app, name, applied in migrations:
        if 'merge' in name.lower():
            merge_migrations.append((app, name, applied))
    
    if merge_migrations:
        print("\n⚠️  MERGE MIGRATIONS FOUND:")
        for app, name, applied in merge_migrations:
            status = "✓" if applied else "✗"
            print(f"  {status} {app}.{name}")
    
    # Check for migrations with numbers that might be out of order
    print("\n⚠️  POTENTIAL NUMBERING ISSUES:")
    for app in apps:
        app_migrations = apps[app]
        # Sort by migration number
        numbered_migrations = []
        for name, applied in app_migrations:
            if name[0].isdigit():
                try:
                    num = int(name.split('_')[0])
                    numbered_migrations.append((num, name, applied))
                except:
                    pass
        
        if numbered_migrations:
            numbered_migrations.sort()
            for i, (num, name, applied) in enumerate(numbered_migrations):
                if i > 0 and num <= numbered_migrations[i-1][0]:
                    status = "✓" if applied else "✗"
                    print(f"  {status} {app}.{name} - Number {num} may be out of order")
    
    # Check for cross-app dependencies
    print("\n🔗 CROSS-APP DEPENDENCIES:")
    print("Checking for migrations that depend on other apps...")
    
    # This is a simplified check - in reality, we'd need to parse the migration files
    # But we can identify common patterns
    cross_app_deps = []
    for app, name, applied in migrations:
        if any(keyword in name.lower() for keyword in ['user', 'auth', 'contenttype', 'session']):
            cross_app_deps.append((app, name, applied))
    
    if cross_app_deps:
        print("Found potential cross-app dependencies:")
        for app, name, applied in cross_app_deps:
            status = "✓" if applied else "✗"
            print(f"  {status} {app}.{name}")
    
    return apps, migrations

def check_database_schema():
    """Check database schema for common issues"""
    print("\n🗄️  DATABASE SCHEMA CHECK:")
    print("-" * 30)
    
    with connection.cursor() as cursor:
        # Check for common tables
        common_tables = [
            'django_migrations',
            'auth_user',
            'users_customuser',
            'loans_loan',
            'loans_loanapplication',
            'loans_rolloverrequest',
            'reports_notification',
            'utils_auditlog'
        ]
        
        for table in common_tables:
            try:
                cursor.execute(f"SELECT COUNT(*) FROM {table} LIMIT 1")
                count = cursor.fetchone()[0]
                print(f"  ✓ {table} - {count} records")
            except Exception as e:
                print(f"  ✗ {table} - Error: {e}")
        
        # Check for rollover_date column specifically
        try:
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.columns 
                WHERE table_schema = DATABASE() 
                AND table_name = 'rollover_requests' 
                AND column_name = 'rollover_date'
            """)
            has_rollover_date = cursor.fetchone()[0] > 0
            if has_rollover_date:
                print("  ✓ rollover_requests.rollover_date column exists")
            else:
                print("  ✗ rollover_requests.rollover_date column missing")
        except Exception as e:
            print(f"  ✗ Error checking rollover_date column: {e}")

def generate_fix_recommendations(apps, migrations):
    """Generate recommendations for fixing migration issues"""
    print("\n💡 FIX RECOMMENDATIONS:")
    print("-" * 30)
    
    recommendations = []
    
    # Check for apps with many migrations
    for app in apps:
        if len(apps[app]) > 10:
            recommendations.append(f"App '{app}' has {len(apps[app])} migrations - consider consolidation")
    
    # Check for unapplied migrations
    unapplied = []
    for app, name, applied in migrations:
        if not applied:
            unapplied.append(f"{app}.{name}")
    
    if unapplied:
        recommendations.append(f"Found {len(unapplied)} unapplied migrations")
        recommendations.append("Consider running: python manage.py migrate --fake-initial")
    
    # Check for merge migrations
    merge_count = sum(1 for app, name, applied in migrations if 'merge' in name.lower())
    if merge_count > 0:
        recommendations.append(f"Found {merge_count} merge migrations - may need dependency fixes")
    
    if not recommendations:
        recommendations.append("No obvious issues detected - migrations appear healthy")
    
    for i, rec in enumerate(recommendations, 1):
        print(f"  {i}. {rec}")
    
    return recommendations

def main():
    """Main analysis function"""
    try:
        setup_django()
        
        # Analyze migrations
        apps, migrations = analyze_migration_dependencies()
        
        # Check database schema
        check_database_schema()
        
        # Generate recommendations
        recommendations = generate_fix_recommendations(apps, migrations)
        
        print("\n" + "=" * 50)
        print("📋 ANALYSIS COMPLETE")
        print("=" * 50)
        print(f"Total Apps: {len(apps)}")
        print(f"Total Migrations: {len(migrations)}")
        print(f"Applied Migrations: {sum(1 for _, _, applied in migrations if applied)}")
        print(f"Unapplied Migrations: {sum(1 for _, _, applied in migrations if not applied)}")
        
        print("\n🎯 NEXT STEPS:")
        print("1. Review the analysis above")
        print("2. Run the comprehensive fix script")
        print("3. Test your application")
        
        return True
        
    except Exception as e:
        print(f"\n❌ Analysis failed: {e}")
        import traceback
        traceback.print_exc()
        return False

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