#!/usr/bin/env python
"""
Script to update contact information in the database for Haven Grazuri Investment Limited
This script updates:
1. M-Pesa shortcode in system_settings table
2. Any stored email addresses with old domain
3. Any stored phone numbers in configuration
"""
import os
import django
import uuid

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection
from reports.models import SystemSettings

# Grazuri Contact Information
GRAZURI_CONTACTS = {
    'phone_numbers': ['+254112941830', '+254114457516', '+254115451752'],
    'whatsapp': '+254112941830',
    'email': 'info@havengrazuri.co.ke',
    'sms_sender_id': 'HavGrazuri',
    'mpesa_shortcode': '4159523',
    'company_name': 'Haven Grazuri Investment Limited',
}

def update_system_settings():
    """Update or create system settings with new M-Pesa shortcode"""
    print("\n" + "=" * 80)
    print("UPDATING SYSTEM SETTINGS")
    print("=" * 80)
    
    try:
        # Check if any system settings exist
        settings = SystemSettings.objects.first()
        
        if settings:
            print(f"\nFound existing system settings record (ID: {settings.id})")
            old_shortcode = settings.mpesa_business_shortcode
            
            # Update M-Pesa shortcode
            settings.mpesa_business_shortcode = GRAZURI_CONTACTS['mpesa_shortcode']
            settings.save()
            
            print(f"✓ Updated M-Pesa shortcode: {old_shortcode} → {GRAZURI_CONTACTS['mpesa_shortcode']}")
        else:
            print("\nNo existing system settings found. Creating new record...")
            
            # Create new system settings with Grazuri information
            settings = SystemSettings.objects.create(
                id=uuid.uuid4().hex,
                default_interest_rate=15.0,
                default_processing_fee=5.0,
                default_late_penalty=5.0,
                rollover_fee_percentage=10.0,
                max_rollover_days=30,
                max_rollover_count=3,
                auto_approval_enabled=True,
                auto_approval_min_score=60,
                auto_approval_max_amount=50000.00,
                email_notifications_enabled=True,
                sms_notifications_enabled=True,
                mpesa_business_shortcode=GRAZURI_CONTACTS['mpesa_shortcode'],
                mpesa_passkey='',  # To be configured separately
                mpesa_environment='sandbox'
            )
            
            print(f"✓ Created new system settings with M-Pesa shortcode: {GRAZURI_CONTACTS['mpesa_shortcode']}")
        
        return True
        
    except Exception as e:
        print(f"✗ Error updating system settings: {str(e)}")
        return False


def check_user_emails():
    """Check for any user emails with old domain"""
    print("\n" + "=" * 80)
    print("CHECKING USER EMAILS")
    print("=" * 80)
    
    try:
        with connection.cursor() as cursor:
            # Check for emails with old domain patterns
            cursor.execute("""
                SELECT username, email, phone_number 
                FROM users 
                WHERE email LIKE '%branchbusinessadvance%' 
                   OR email LIKE '%branch%business%advance%'
                LIMIT 20
            """)
            
            old_domain_users = cursor.fetchall()
            
            if old_domain_users:
                print(f"\n⚠ Found {len(old_domain_users)} users with old domain emails:")
                for username, email, phone in old_domain_users:
                    print(f"  - {username}: {email}")
                print("\nNote: These should be updated manually or with user consent")
                return False
            else:
                print("\n✓ No users with old domain emails found")
                return True
                
    except Exception as e:
        print(f"✗ Error checking user emails: {str(e)}")
        return False


def check_stored_contact_info():
    """Check for any stored contact information in text fields"""
    print("\n" + "=" * 80)
    print("CHECKING STORED CONTACT INFORMATION")
    print("=" * 80)
    
    old_phone_patterns = [
        '+254720246513',  # Example old phone number
        '0720246513',
    ]
    
    old_email_patterns = [
        'branchbusinessadvance',
        'branch-business-advance',
    ]
    
    try:
        with connection.cursor() as cursor:
            # Check for old phone numbers in various tables
            tables_to_check = ['users', 'loans', 'loan_applications']
            
            found_issues = False
            
            for table in tables_to_check:
                # Check if table exists
                cursor.execute(f"SHOW TABLES LIKE '{table}'")
                if not cursor.fetchone():
                    continue
                
                # Get all text/varchar columns
                cursor.execute(f"DESCRIBE {table}")
                columns = cursor.fetchall()
                
                text_columns = [col[0] for col in columns 
                               if 'char' in col[1].lower() or 'text' in col[1].lower()]
                
                for column in text_columns:
                    for pattern in old_phone_patterns + old_email_patterns:
                        try:
                            cursor.execute(f"""
                                SELECT COUNT(*) 
                                FROM {table} 
                                WHERE {column} LIKE %s
                            """, (f'%{pattern}%',))
                            
                            count = cursor.fetchone()[0]
                            if count > 0:
                                print(f"⚠ Found {count} records in {table}.{column} containing '{pattern}'")
                                found_issues = True
                        except Exception:
                            # Skip columns that can't be searched
                            pass
            
            if not found_issues:
                print("\n✓ No old contact information found in database text fields")
                return True
            else:
                print("\nNote: Manual review recommended for the above findings")
                return False
                
    except Exception as e:
        print(f"✗ Error checking stored contact info: {str(e)}")
        return False


def generate_report():
    """Generate a summary report"""
    print("\n" + "=" * 80)
    print("CONTACT INFORMATION UPDATE SUMMARY")
    print("=" * 80)
    
    print("\nNew Grazuri Contact Information:")
    print(f"  Company Name: {GRAZURI_CONTACTS['company_name']}")
    print(f"  Phone Numbers: {', '.join(GRAZURI_CONTACTS['phone_numbers'])}")
    print(f"  WhatsApp: {GRAZURI_CONTACTS['whatsapp']}")
    print(f"  Email: {GRAZURI_CONTACTS['email']}")
    print(f"  SMS Sender ID: {GRAZURI_CONTACTS['sms_sender_id']}")
    print(f"  M-Pesa Shortcode: {GRAZURI_CONTACTS['mpesa_shortcode']}")
    
    print("\nUpdates Applied:")
    print("  ✓ System settings M-Pesa shortcode updated")
    print("  ✓ Database contact information verified")
    
    print("\nNote: Configuration files (settings.py, .env) should be updated separately")
    print("=" * 80)


def main():
    """Main execution function"""
    print("\n" + "=" * 80)
    print("HAVEN GRAZURI INVESTMENT LIMITED")
    print("DATABASE CONTACT INFORMATION UPDATE")
    print("=" * 80)
    
    # Update system settings
    success1 = update_system_settings()
    
    # Check user emails
    success2 = check_user_emails()
    
    # Check stored contact info
    success3 = check_stored_contact_info()
    
    # Generate report
    generate_report()
    
    if success1 and success2 and success3:
        print("\n✓ All database contact information updates completed successfully!")
        return 0
    else:
        print("\n⚠ Some updates completed with warnings. Please review the output above.")
        return 1


if __name__ == '__main__':
    exit(main())
