#!/usr/bin/env python3
"""
Fix Duplicate Column Error

This script fixes the "Duplicate column name 'unassigned_date'" error by
checking the database schema and handling the duplicate column issue.

Usage:
    python fix_duplicate_column.py
"""

import os
import sys
import django
import subprocess
from pathlib import Path

def setup_django():
    """Setup Django environment"""
    try:
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
        django.setup()
        print("[SUCCESS] Django environment setup successful")
        return True
    except Exception as e:
        print(f"[ERROR] Django setup failed: {str(e)}")
        return False

def check_table_structure(table_name):
    """Check the structure of a table"""
    try:
        from django.db import connection
        with connection.cursor() as cursor:
            cursor.execute(f"DESCRIBE {table_name}")
            columns = cursor.fetchall()
            return columns
    except Exception as e:
        print(f"[ERROR] Error checking table {table_name}: {str(e)}")
        return []

def fix_duplicate_column_issue():
    """Fix the duplicate column issue"""
    print("=" * 60)
    print("FIXING DUPLICATE COLUMN ISSUE")
    print("=" * 60)
    
    if not setup_django():
        return False
    
    # Check if portfolioassignment table exists and has unassigned_date column
    print("Checking portfolioassignment table structure...")
    columns = check_table_structure('portfolioassignment')
    
    if columns:
        column_names = [col[0] for col in columns]
        print(f"Existing columns: {column_names}")
        
        if 'unassigned_date' in column_names:
            print("[INFO] unassigned_date column already exists in portfolioassignment table")
            print("This is causing the duplicate column error.")
            
            # Mark the migration as fake (already applied)
            print("Marking the migration as fake...")
            try:
                result = subprocess.run([
                    'python', 'manage.py', 'migrate', 'users', '0023', '--fake'
                ], capture_output=True, text=True, cwd=Path.cwd())
                
                if result.returncode == 0:
                    print("[SUCCESS] Migration marked as fake")
                    print(f"Output: {result.stdout}")
                else:
                    print(f"[ERROR] Failed to mark migration as fake: {result.stderr}")
                    return False
                    
            except Exception as e:
                print(f"[ERROR] Error marking migration as fake: {str(e)}")
                return False
        else:
            print("[INFO] unassigned_date column does not exist, migration should proceed normally")
    else:
        print("[WARNING] Could not check portfolioassignment table structure")
    
    # Now try to apply migrations normally
    print("Applying migrations...")
    try:
        result = subprocess.run([
            'python', 'manage.py', 'migrate', 'users'
        ], capture_output=True, text=True, cwd=Path.cwd())
        
        if result.returncode == 0:
            print("[SUCCESS] Migrations applied successfully")
            print(f"Output: {result.stdout}")
        else:
            print(f"[ERROR] Failed to apply migrations: {result.stderr}")
            return False
            
    except Exception as e:
        print(f"[ERROR] Error applying migrations: {str(e)}")
        return False
    
    # Verify the fix
    print("Verifying the fix...")
    try:
        from django.db import connection
        with connection.cursor() as cursor:
            cursor.execute("SELECT COUNT(*) FROM user_permissions WHERE module = 'test'")
            result = cursor.fetchone()
            print("[SUCCESS] user_permissions table is working correctly")
            return True
    except Exception as e:
        print(f"[ERROR] Verification failed: {str(e)}")
        return False

def main():
    """Main function"""
    try:
        success = fix_duplicate_column_issue()
        
        if success:
            print("=" * 60)
            print("DUPLICATE COLUMN ISSUE FIXED SUCCESSFULLY!")
            print("The user_permissions table should now work correctly.")
            print("=" * 60)
        else:
            print("=" * 60)
            print("FAILED TO FIX DUPLICATE COLUMN ISSUE")
            print("Please check the error messages above.")
            print("=" * 60)
        
        return success
        
    except Exception as e:
        print(f"Script failed: {str(e)}")
        return False

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