#!/usr/bin/env python
"""
Fix is_default migration issue and manually create unconfirmed_payments table
This script:
1. Checks and removes is_default column if it exists (causing migration errors)
2. Manually creates unconfirmed_payments table
3. Marks the migration as applied
"""
import os
import sys
import django
from pathlib import Path

def setup_django():
    """Setup Django environment"""
    script_dir = Path(__file__).resolve().parent
    
    manage_py = script_dir / 'manage.py'
    if not manage_py.exists():
        manage_py = script_dir.parent / 'manage.py'
    
    if manage_py.exists():
        with open(manage_py, 'r') as f:
            content = f.read()
            if 'DJANGO_SETTINGS_MODULE' in content:
                import re
                match = re.search(r"os\.environ\.setdefault\(['\"]DJANGO_SETTINGS_MODULE['\"],\s*['\"]([^'\"]+)['\"]", content)
                if match:
                    os.environ.setdefault('DJANGO_SETTINGS_MODULE', match.group(1))
    
    settings_modules = [
        'config.settings',
        'settings',
        'project.settings',
        'branchsystem.settings',
        'branch_system.settings',
    ]
    
    for settings_module in settings_modules:
        try:
            os.environ.setdefault('DJANGO_SETTINGS_MODULE', settings_module)
            django.setup()
            print(f"✓ Django setup successful with settings: {settings_module}")
            return True
        except Exception as e:
            continue
    
    print("✗ Error: Could not setup Django. Please set DJANGO_SETTINGS_MODULE")
    return False

def fix_is_default_column():
    """Check and remove is_default column if it exists in role_permissions"""
    from django.db import connection
    
    print("\n" + "="*70)
    print("FIXING is_default COLUMN ISSUE")
    print("="*70)
    
    try:
        with connection.cursor() as cursor:
            # Check if is_default column exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'role_permissions'
                AND COLUMN_NAME = 'is_default'
            """)
            
            exists = cursor.fetchone()[0] > 0
            
            if exists:
                print("⚠ Column 'is_default' exists in role_permissions. Removing it...")
                
                # Check if there are any indexes on this column first
                cursor.execute("""
                    SELECT INDEX_NAME 
                    FROM information_schema.STATISTICS 
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'role_permissions'
                    AND COLUMN_NAME = 'is_default'
                """)
                indexes = cursor.fetchall()
                
                # Drop indexes first
                for (index_name,) in indexes:
                    try:
                        cursor.execute(f"ALTER TABLE `role_permissions` DROP INDEX `{index_name}`")
                        print(f"  ✓ Dropped index: {index_name}")
                    except Exception as e:
                        print(f"  ⚠ Could not drop index {index_name}: {e}")
                
                # Drop the column
                cursor.execute("ALTER TABLE `role_permissions` DROP COLUMN `is_default`")
                print("✓ Column 'is_default' removed successfully")
            else:
                print("✓ Column 'is_default' does not exist (already removed or never existed)")
        
        return True
        
    except Exception as e:
        print(f"⚠ Error checking/fixing is_default column: {e}")
        # Continue anyway, it might not be critical
        return True

def create_unconfirmed_payments_table():
    """Manually create unconfirmed_payments table"""
    from django.db import connection
    from django.conf import settings
    import uuid
    
    print("\n" + "="*70)
    print("CREATING unconfirmed_payments TABLE")
    print("="*70)
    
    try:
        with connection.cursor() as cursor:
            # Check if table exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'unconfirmed_payments'
            """)
            
            exists = cursor.fetchone()[0] > 0
            
            if exists:
                print("✓ Table 'unconfirmed_payments' already exists")
                
                # Verify all required columns exist
                cursor.execute("""
                    SELECT COLUMN_NAME 
                    FROM information_schema.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'unconfirmed_payments'
                """)
                existing_columns = {row[0] for row in cursor.fetchall()}
                
                required_columns = {
                    'id', 'mpesa_transaction_id', 'payment_phone', 'payment_id_number',
                    'suggested_borrower_id', 'match_type', 'status', 'approved_by_id',
                    'approved_at', 'rejection_reason', 'admin_notes', 'notes',
                    'created_at', 'updated_at'
                }
                
                missing = required_columns - existing_columns
                if missing:
                    print(f"⚠ Missing columns: {', '.join(missing)}")
                    print("  Table exists but may be incomplete. Please check manually.")
                else:
                    print("✓ All required columns exist")
                
                return True
            
            print("⚠ Table 'unconfirmed_payments' does not exist. Creating it...")
            
            # Get actual table names from Django models
            from django.apps import apps
            try:
                MpesaTransaction = apps.get_model('loans', 'MpesaTransaction')
                mpesa_table = MpesaTransaction._meta.db_table
                print(f"  → M-Pesa transaction table: {mpesa_table}")
            except Exception as e:
                print(f"  ⚠ Could not get MpesaTransaction table name: {e}")
                # Try common table names
                mpesa_table = 'loans_mpesatransaction'
            
            # Get user table name
            from django.contrib.auth import get_user_model
            try:
                User = get_user_model()
                user_table = User._meta.db_table
                print(f"  → User table: {user_table}")
            except Exception as e:
                print(f"  ⚠ Could not get User table name: {e}")
                user_table = 'users_customuser'
            
            # Verify the referenced tables exist
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = %s
            """, [mpesa_table])
            mpesa_exists = cursor.fetchone()[0] > 0
            
            if not mpesa_exists:
                print(f"  ⚠ Warning: Referenced table '{mpesa_table}' does not exist")
                print("  → Creating table WITHOUT foreign key constraints (will add them later)")
                create_constraints = False
            else:
                print(f"  ✓ Referenced table '{mpesa_table}' exists")
                create_constraints = True
            
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = %s
            """, [user_table])
            user_exists = cursor.fetchone()[0] > 0
            
            if not user_exists:
                print(f"  ⚠ Warning: Referenced table '{user_table}' does not exist")
                create_constraints = False
            else:
                print(f"  ✓ Referenced table '{user_table}' exists")
            
            # Get actual column types from referenced tables (including collation)
            mpesa_id_type = 'char(36)'  # Default UUID type
            user_id_type = 'char(36)'   # Default UUID type
            
            if mpesa_exists:
                cursor.execute("""
                    SELECT COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
                    FROM information_schema.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = %s
                    AND COLUMN_NAME = 'id'
                """, [mpesa_table])
                result = cursor.fetchone()
                if result:
                    column_type, charset, collation = result
                    mpesa_id_type = column_type  # Use full COLUMN_TYPE which includes collation
                    print(f"  → M-Pesa id column type: {mpesa_id_type} (charset: {charset}, collation: {collation})")
            
            if user_exists:
                cursor.execute("""
                    SELECT COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
                    FROM information_schema.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = %s
                    AND COLUMN_NAME = 'id'
                """, [user_table])
                result = cursor.fetchone()
                if result:
                    column_type, charset, collation = result
                    user_id_type = column_type  # Use full COLUMN_TYPE which includes collation
                    print(f"  → User id column type: {user_id_type} (charset: {charset}, collation: {collation})")
            
            # Create the table - first without foreign keys if tables don't exist
            if create_constraints:
                create_table_sql = f"""
                CREATE TABLE `unconfirmed_payments` (
                    `id` {user_id_type} NOT NULL PRIMARY KEY,
                    `mpesa_transaction_id` {mpesa_id_type} NOT NULL UNIQUE,
                    `payment_phone` varchar(17) NULL,
                    `payment_id_number` varchar(50) NULL,
                    `suggested_borrower_id` {user_id_type} NULL,
                    `match_type` varchar(20) NOT NULL DEFAULT 'none',
                    `status` varchar(20) NOT NULL DEFAULT 'pending',
                    `approved_by_id` {user_id_type} NULL,
                    `approved_at` datetime(6) NULL,
                    `rejection_reason` longtext NULL,
                    `admin_notes` longtext NULL,
                    `notes` longtext NULL,
                    `created_at` datetime(6) NOT NULL,
                    `updated_at` datetime(6) NOT NULL,
                    KEY `unconfirmed_payments_mpesa_transaction_id_idx` (`mpesa_transaction_id`),
                    KEY `unconfirmed_payments_suggested_borrower_id_idx` (`suggested_borrower_id`),
                    KEY `unconfirmed_payments_approved_by_id_idx` (`approved_by_id`),
                    KEY `unconfirmed_payments_status_idx` (`status`),
                    KEY `unconfirmed_payments_match_type_idx` (`match_type`),
                    CONSTRAINT `unconfirmed_payments_mpesa_transaction_id_fk` 
                        FOREIGN KEY (`mpesa_transaction_id`) REFERENCES `{mpesa_table}` (`id`) ON DELETE CASCADE,
                    CONSTRAINT `unconfirmed_payments_suggested_borrower_id_fk` 
                        FOREIGN KEY (`suggested_borrower_id`) REFERENCES `{user_table}` (`id`) ON DELETE SET NULL,
                    CONSTRAINT `unconfirmed_payments_approved_by_id_fk` 
                        FOREIGN KEY (`approved_by_id`) REFERENCES `{user_table}` (`id`) ON DELETE SET NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                """
            else:
                # Create without foreign keys (safer)
                create_table_sql = f"""
                CREATE TABLE `unconfirmed_payments` (
                    `id` {user_id_type} NOT NULL PRIMARY KEY,
                    `mpesa_transaction_id` {mpesa_id_type} NOT NULL UNIQUE,
                    `payment_phone` varchar(17) NULL,
                    `payment_id_number` varchar(50) NULL,
                    `suggested_borrower_id` {user_id_type} NULL,
                    `match_type` varchar(20) NOT NULL DEFAULT 'none',
                    `status` varchar(20) NOT NULL DEFAULT 'pending',
                    `approved_by_id` {user_id_type} NULL,
                    `approved_at` datetime(6) NULL,
                    `rejection_reason` longtext NULL,
                    `admin_notes` longtext NULL,
                    `notes` longtext NULL,
                    `created_at` datetime(6) NOT NULL,
                    `updated_at` datetime(6) NOT NULL,
                    KEY `unconfirmed_payments_mpesa_transaction_id_idx` (`mpesa_transaction_id`),
                    KEY `unconfirmed_payments_suggested_borrower_id_idx` (`suggested_borrower_id`),
                    KEY `unconfirmed_payments_approved_by_id_idx` (`approved_by_id`),
                    KEY `unconfirmed_payments_status_idx` (`status`),
                    KEY `unconfirmed_payments_match_type_idx` (`match_type`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                """
            
            try:
                cursor.execute(create_table_sql)
                print("✓ Table 'unconfirmed_payments' created successfully")
                table_created_with_fks = create_constraints
            except Exception as fk_error:
                # If foreign key creation failed, try creating without them
                if create_constraints and ('foreign key' in str(fk_error).lower() or '3780' in str(fk_error) or 'incompatible' in str(fk_error).lower()):
                    print(f"  ⚠ Foreign key creation failed: {fk_error}")
                    print("  → Retrying without foreign key constraints...")
                    # Create without foreign keys
                    create_table_sql_no_fk = f"""
                    CREATE TABLE `unconfirmed_payments` (
                        `id` {user_id_type} NOT NULL PRIMARY KEY,
                        `mpesa_transaction_id` {mpesa_id_type} NOT NULL UNIQUE,
                        `payment_phone` varchar(17) NULL,
                        `payment_id_number` varchar(50) NULL,
                        `suggested_borrower_id` {user_id_type} NULL,
                        `match_type` varchar(20) NOT NULL DEFAULT 'none',
                        `status` varchar(20) NOT NULL DEFAULT 'pending',
                        `approved_by_id` {user_id_type} NULL,
                        `approved_at` datetime(6) NULL,
                        `rejection_reason` longtext NULL,
                        `admin_notes` longtext NULL,
                        `notes` longtext NULL,
                        `created_at` datetime(6) NOT NULL,
                        `updated_at` datetime(6) NOT NULL,
                        KEY `unconfirmed_payments_mpesa_transaction_id_idx` (`mpesa_transaction_id`),
                        KEY `unconfirmed_payments_suggested_borrower_id_idx` (`suggested_borrower_id`),
                        KEY `unconfirmed_payments_approved_by_id_idx` (`approved_by_id`),
                        KEY `unconfirmed_payments_status_idx` (`status`),
                        KEY `unconfirmed_payments_match_type_idx` (`match_type`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                    """
                    cursor.execute(create_table_sql_no_fk)
                    print("✓ Table 'unconfirmed_payments' created successfully (without foreign keys)")
                    table_created_with_fks = False
                else:
                    # Re-raise if it's a different error
                    raise
            
            # If we created without foreign keys, try to add them now if tables exist
            if not table_created_with_fks and mpesa_exists and user_exists:
                print("  → Attempting to add foreign key constraints...")
                try:
                    # Add foreign key for mpesa_transaction
                    cursor.execute(f"""
                        ALTER TABLE `unconfirmed_payments`
                        ADD CONSTRAINT `unconfirmed_payments_mpesa_transaction_id_fk`
                        FOREIGN KEY (`mpesa_transaction_id`) REFERENCES `{mpesa_table}` (`id`) ON DELETE CASCADE
                    """)
                    print("  ✓ Added foreign key for mpesa_transaction_id")
                except Exception as e:
                    print(f"  ⚠ Could not add foreign key for mpesa_transaction_id: {e}")
                
                try:
                    # Add foreign key for suggested_borrower
                    cursor.execute(f"""
                        ALTER TABLE `unconfirmed_payments`
                        ADD CONSTRAINT `unconfirmed_payments_suggested_borrower_id_fk`
                        FOREIGN KEY (`suggested_borrower_id`) REFERENCES `{user_table}` (`id`) ON DELETE SET NULL
                    """)
                    print("  ✓ Added foreign key for suggested_borrower_id")
                except Exception as e:
                    print(f"  ⚠ Could not add foreign key for suggested_borrower_id: {e}")
                
                try:
                    # Add foreign key for approved_by
                    cursor.execute(f"""
                        ALTER TABLE `unconfirmed_payments`
                        ADD CONSTRAINT `unconfirmed_payments_approved_by_id_fk`
                        FOREIGN KEY (`approved_by_id`) REFERENCES `{user_table}` (`id`) ON DELETE SET NULL
                    """)
                    print("  ✓ Added foreign key for approved_by_id")
                except Exception as e:
                    print(f"  ⚠ Could not add foreign key for approved_by_id: {e}")
            
            return True
            
    except Exception as e:
        print(f"✗ Error creating unconfirmed_payments table: {e}")
        import traceback
        traceback.print_exc()
        return False

def mark_migration_applied():
    """Mark the 0002_unconfirmedpayment migration as applied"""
    from django.db import connection
    from datetime import datetime
    
    print("\n" + "="*70)
    print("MARKING MIGRATION AS APPLIED")
    print("="*70)
    
    try:
        with connection.cursor() as cursor:
            # First, check if the dependency migration is applied
            cursor.execute("""
                SELECT COUNT(*) 
                FROM django_migrations 
                WHERE app = 'loans' 
                AND name = '0020_alter_mpesatransaction_status'
            """)
            dependency_exists = cursor.fetchone()[0] > 0
            
            if not dependency_exists:
                print("⚠ Dependency migration 'loans.0020_alter_mpesatransaction_status' not found")
                print("  → Marking dependency as applied first...")
                cursor.execute("""
                    INSERT INTO django_migrations (app, name, applied) 
                    VALUES ('loans', '0020_alter_mpesatransaction_status', %s)
                """, [datetime.now()])
                print("  ✓ Dependency migration marked as applied")
            else:
                print("✓ Dependency migration 'loans.0020_alter_mpesatransaction_status' is already applied")
            
            # Check if migration is already marked
            cursor.execute("""
                SELECT COUNT(*) 
                FROM django_migrations 
                WHERE app = 'payments' 
                AND name = '0002_unconfirmedpayment'
            """)
            
            exists = cursor.fetchone()[0] > 0
            
            if exists:
                print("✓ Migration '0002_unconfirmedpayment' is already marked as applied")
            else:
                print("⚠ Marking migration '0002_unconfirmedpayment' as applied...")
                cursor.execute("""
                    INSERT INTO django_migrations (app, name, applied) 
                    VALUES ('payments', '0002_unconfirmedpayment', %s)
                """, [datetime.now()])
                print("✓ Migration marked as applied")
            
            return True
            
    except Exception as e:
        print(f"⚠ Error marking migration: {e}")
        return True  # Not critical if this fails

def main():
    """Main function"""
    print("="*70)
    print("FIX is_default ISSUE AND CREATE unconfirmed_payments TABLE")
    print("="*70)
    
    if not setup_django():
        sys.exit(1)
    
    # Fix is_default column issue
    fix_is_default_column()
    
    # Create unconfirmed_payments table
    if not create_unconfirmed_payments_table():
        print("\n✗ Failed to create unconfirmed_payments table")
        sys.exit(1)
    
    # Mark migration as applied
    mark_migration_applied()
    
    print("\n" + "="*70)
    print("FIX COMPLETED")
    print("="*70)
    print("\nNEXT STEPS:")
    print("  1. Try running migrations again: python manage.py migrate")
    print("  2. Restart your Django application in cPanel")
    print("  3. Test M-Pesa payment matching at /payments/unconfirmed-payments/")
    print("\n" + "="*70)

if __name__ == '__main__':
    main()