#!/usr/bin/env python
"""
Fix expenses production issues:
1. Change id from UUID to BigInt (AutoField)
2. Fix branch dropdown to show all accessible branches
"""

import os
import sys
import django

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
django.setup()

from django.db import connection

def fix_expenses_id_field():
    """Fix the expenses table ID field to match database schema"""
    
    print("=" * 60)
    print("FIXING EXPENSES TABLE ID FIELD")
    print("=" * 60)
    
    with connection.cursor() as cursor:
        # Check current table structure
        print("\n1. Checking current expenses table structure...")
        cursor.execute("DESCRIBE expenses;")
        columns = cursor.fetchall()
        
        print("\nCurrent columns:")
        for col in columns:
            print(f"  - {col[0]}: {col[1]}")
        
        # Check if id is bigint or varchar
        cursor.execute("""
            SELECT COLUMN_TYPE, EXTRA 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'expenses' 
            AND COLUMN_NAME = 'id'
        """)
        result = cursor.fetchone()
        
        if result:
            col_type, extra = result
            print(f"\nCurrent id column: {col_type} {extra}")
            
            if 'bigint' in col_type.lower():
                print("✓ ID field is already bigint - correct!")
                
                # Ensure AUTO_INCREMENT is set
                if 'auto_increment' not in extra.lower():
                    print("\n2. Adding AUTO_INCREMENT to id column...")
                    cursor.execute("""
                        ALTER TABLE expenses 
                        MODIFY COLUMN id bigint NOT NULL AUTO_INCREMENT
                    """)
                    print("✓ AUTO_INCREMENT added")
                else:
                    print("✓ AUTO_INCREMENT already set")
                    
            elif 'char' in col_type.lower() or 'varchar' in col_type.lower():
                print("✗ ID field is varchar/char - needs to be changed to bigint!")
                print("\n2. Converting id column from varchar to bigint...")
                
                # First, check if there are any records
                cursor.execute("SELECT COUNT(*) FROM expenses")
                count = cursor.fetchone()[0]
                
                if count > 0:
                    print(f"⚠ Warning: Table has {count} records. This will delete them!")
                    print("Creating backup...")
                    
                    # Create backup table
                    cursor.execute("DROP TABLE IF EXISTS expenses_backup")
                    cursor.execute("CREATE TABLE expenses_backup AS SELECT * FROM expenses")
                    print("✓ Backup created as expenses_backup")
                    
                    # Truncate the table
                    cursor.execute("TRUNCATE TABLE expenses")
                    print("✓ Table truncated")
                
                # Modify the id column
                cursor.execute("""
                    ALTER TABLE expenses 
                    MODIFY COLUMN id bigint NOT NULL AUTO_INCREMENT
                """)
                print("✓ ID column changed to bigint AUTO_INCREMENT")
        
        print("\n3. Verifying changes...")
        cursor.execute("""
            SELECT COLUMN_TYPE, EXTRA 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'expenses' 
            AND COLUMN_NAME = 'id'
        """)
        result = cursor.fetchone()
        if result:
            col_type, extra = result
            print(f"✓ New id column: {col_type} {extra}")
    
    print("\n" + "=" * 60)
    print("EXPENSES ID FIELD FIX COMPLETE")
    print("=" * 60)


def check_branch_data():
    """Check branch data to diagnose dropdown issue"""
    
    print("\n" + "=" * 60)
    print("CHECKING BRANCH DATA")
    print("=" * 60)
    
    from users.models import Branch
    
    branches = Branch.objects.filter(is_active=True)
    print(f"\nTotal active branches: {branches.count()}")
    
    for branch in branches:
        print(f"\n  Branch: {branch.name}")
        print(f"    ID: {branch.id}")
        print(f"    Active: {branch.is_active}")
        print(f"    Is Default: {getattr(branch, 'is_default', 'N/A')}")
    
    print("\n" + "=" * 60)


if __name__ == '__main__':
    try:
        fix_expenses_id_field()
        check_branch_data()
        
        print("\n" + "=" * 60)
        print("NEXT STEPS:")
        print("=" * 60)
        print("1. Update the Expense model in expenses/models.py")
        print("   Change: id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)")
        print("   To:     id = models.BigAutoField(primary_key=True)")
        print("")
        print("2. Remove the uuid import if not used elsewhere")
        print("")
        print("3. Deploy the updated model to production")
        print("=" * 60)
        
    except Exception as e:
        print(f"\n✗ Error: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)