import os import sys import django from datetime import datetime # Setup Django os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') django.setup() from django.db import connection, transaction from users.models import CustomUser, Branch from utils.models import SystemSetting def log_message(message, level='INFO'): """Log messages with timestamp""" timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S') print(f"[{timestamp}] {level}: {message}") def check_table_exists(table_name): """Check if a table exists in the database""" try: with connection.cursor() as cursor: cursor.execute(f""" SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '{table_name}' """) return cursor.fetchone()[0] > 0 except Exception as e: log_message(f"Error checking table {table_name}: {e}", 'ERROR') return False def check_column_exists(table_name, column_name): """Check if a column exists in a table""" try: with connection.cursor() as cursor: cursor.execute(f""" SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '{table_name}' AND COLUMN_NAME = '{column_name}' """) return cursor.fetchone()[0] > 0 except Exception as e: log_message(f"Error checking column {table_name}.{column_name}: {e}", 'ERROR') return False def fix_users_table_structure(): """Ensure users table has all required columns and indexes""" try: log_message("Fixing users table structure...") with connection.cursor() as cursor: # Check and add missing columns required_columns = { 'branch_id': "ADD COLUMN `branch_id` char(32) NULL", 'phone_number': "ADD COLUMN `phone_number` varchar(20) NULL", 'email': "MODIFY COLUMN `email` varchar(254) NULL", 'id_number': "ADD COLUMN `id_number` varchar(50) NULL", 'business_name': "ADD COLUMN `business_name` varchar(200) NULL", 'business_type': "ADD COLUMN `business_type` varchar(50) NULL", 'business_address': "ADD COLUMN `business_address` longtext NULL", 'physical_address': "ADD COLUMN `physical_address` longtext NULL", 'city': "ADD COLUMN `city` varchar(100) NULL", 'postal_code': "ADD COLUMN `postal_code` varchar(20) NULL", 'nationality': "ADD COLUMN `nationality` varchar(50) NULL DEFAULT 'Kenyan'", 'marital_status': "ADD COLUMN `marital_status` varchar(20) NULL", 'gender': "ADD COLUMN `gender` varchar(10) NULL", 'date_of_birth': "ADD COLUMN `date_of_birth` date NULL", 'status': "ADD COLUMN `status` varchar(20) NULL DEFAULT 'active'", 'role': "ADD COLUMN `role` varchar(20) NULL DEFAULT 'borrower'" } for column, alter_sql in required_columns.items(): if not check_column_exists('users', column): try: cursor.execute(f"ALTER TABLE `users` {alter_sql}") log_message(f"✅ Added/Modified column: {column}") except Exception as e: if "Duplicate column name" not in str(e): log_message(f"Warning adding column {column}: {e}", 'WARNING') else: log_message(f"Column {column} already exists") # Add indexes for better performance indexes_to_add = [ ("idx_users_phone", "CREATE INDEX `idx_users_phone` ON `users` (`phone_number`)"), ("idx_users_email", "CREATE INDEX `idx_users_email` ON `users` (`email`)"), ("idx_users_id_number", "CREATE INDEX `idx_users_id_number` ON `users` (`id_number`)"), ("idx_users_branch", "CREATE INDEX `idx_users_branch` ON `users` (`branch_id`)"), ("idx_users_role", "CREATE INDEX `idx_users_role` ON `users` (`role`)"), ("idx_users_status", "CREATE INDEX `idx_users_status` ON `users` (`status`)") ] for index_name, create_sql in indexes_to_add: try: cursor.execute(create_sql) log_message(f"✅ Created index: {index_name}") except Exception as e: if "Duplicate key name" not in str(e): log_message(f"Warning creating index {index_name}: {e}", 'WARNING') # Add foreign key constraint for branch_id if it doesn't exist if check_table_exists('users_branch'): try: cursor.execute(""" ALTER TABLE `users` ADD CONSTRAINT `fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`) ON DELETE SET NULL """) log_message("✅ Added foreign key constraint for branch_id") except Exception as e: if "Duplicate foreign key constraint name" not in str(e): log_message(f"Warning adding foreign key: {e}", 'WARNING') return True except Exception as e: log_message(f"Error fixing users table structure: {e}", 'ERROR') return False def clean_duplicate_data(): """Clean up duplicate and inconsistent data""" try: log_message("Cleaning duplicate data...") with connection.cursor() as cursor: # Find and fix duplicate phone numbers cursor.execute(""" SELECT phone_number, COUNT(*) as count FROM users WHERE phone_number IS NOT NULL AND phone_number != '' GROUP BY phone_number HAVING count > 1 """) duplicates = cursor.fetchall() if duplicates: log_message(f"Found {len(duplicates)} duplicate phone numbers") for phone, count in duplicates: log_message(f"Fixing duplicate phone number: {phone} ({count} records)") # Get all users with this phone number cursor.execute( "SELECT id, username, email, first_name, last_name, date_joined FROM users WHERE phone_number = %s ORDER BY date_joined", [phone] ) users = cursor.fetchall() # Keep the first user, modify others for i, user in enumerate(users[1:], 1): user_id, username, email, first_name, last_name, date_joined = user new_phone = f"{phone}_{i}" new_username = f"{username}_{i}" if username else f"user_{user_id}_{i}" cursor.execute( "UPDATE users SET phone_number = %s, username = %s WHERE id = %s", [new_phone, new_username, user_id] ) log_message(f" Updated user {user_id}: phone {phone} -> {new_phone}") # Find and fix duplicate emails cursor.execute(""" SELECT email, COUNT(*) as count FROM users WHERE email IS NOT NULL AND email != '' GROUP BY email HAVING count > 1 """) email_duplicates = cursor.fetchall() if email_duplicates: log_message(f"Found {len(email_duplicates)} duplicate emails") for email, count in email_duplicates: log_message(f"Fixing duplicate email: {email} ({count} records)") cursor.execute( "SELECT id, username, phone_number, first_name, last_name, date_joined FROM users WHERE email = %s ORDER BY date_joined", [email] ) users = cursor.fetchall() # Keep the first user, modify others for i, user in enumerate(users[1:], 1): user_id, username, phone_number, first_name, last_name, date_joined = user email_parts = email.split('@') new_email = f"{email_parts[0]}_{i}@{email_parts[1]}" cursor.execute( "UPDATE users SET email = %s WHERE id = %s", [new_email, user_id] ) log_message(f" Updated user {user_id}: email {email} -> {new_email}") # Find and fix duplicate ID numbers cursor.execute(""" SELECT id_number, COUNT(*) as count FROM users WHERE id_number IS NOT NULL AND id_number != '' GROUP BY id_number HAVING count > 1 """) id_duplicates = cursor.fetchall() if id_duplicates: log_message(f"Found {len(id_duplicates)} duplicate ID numbers") for id_number, count in id_duplicates: log_message(f"Fixing duplicate ID number: {id_number} ({count} records)") cursor.execute( "SELECT id, username, phone_number, email, first_name, last_name, date_joined FROM users WHERE id_number = %s ORDER BY date_joined", [id_number] ) users = cursor.fetchall() # Keep the first user, modify others for i, user in enumerate(users[1:], 1): user_id = user[0] new_id_number = f"{id_number}_{i}" cursor.execute( "UPDATE users SET id_number = %s WHERE id = %s", [new_id_number, user_id] ) log_message(f" Updated user {user_id}: ID {id_number} -> {new_id_number}") return True except Exception as e: log_message(f"Error cleaning duplicate data: {e}", 'ERROR') return False def ensure_branch_system(): """Ensure branch system is properly set up""" try: log_message("Setting up branch system...") # Create branch table if it doesn't exist if not check_table_exists('users_branch'): log_message("Creating branch table...") with connection.cursor() as cursor: cursor.execute(""" CREATE TABLE `users_branch` ( `id` char(32) NOT NULL, `name` varchar(100) NOT NULL, `code` varchar(20) NOT NULL UNIQUE, `address` longtext, `phone_number` varchar(20), `email` varchar(254), `is_main_branch` tinyint(1) NOT NULL DEFAULT 0, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime(6) NOT NULL, `updated_at` datetime(6) NOT NULL, PRIMARY KEY (`id`), KEY `idx_branch_active` (`is_active`), KEY `idx_branch_main` (`is_main_branch`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) log_message("✅ Branch table created") # Create accessible_branches table if it doesn't exist if not check_table_exists('users_customuser_accessible_branches'): log_message("Creating accessible_branches table...") with connection.cursor() as cursor: cursor.execute(""" CREATE TABLE `users_customuser_accessible_branches` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `customuser_id` char(32) NOT NULL, `branch_id` char(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `users_customuser_accessible_customuser_id_branch_id_unique` (`customuser_id`,`branch_id`), KEY `users_customuser_accessible_branch_id_fk` (`branch_id`), CONSTRAINT `users_customuser_accessible_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `users_customuser_accessible_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) log_message("✅ Accessible branches table created") # Create default branch if none exists from users.models import Branch if not Branch.objects.filter(is_active=True).exists(): log_message("Creating default branch...") branch = Branch.objects.create( name="Main Branch", code="MAIN", address="Head Office", phone_number="+254700000000", email="info@branchbusinessadvance.co.ke", is_main_branch=True, is_active=True ) log_message(f"✅ Created default branch: {branch.name}") # Assign users without branch to default branch users_updated = CustomUser.objects.filter(branch__isnull=True).update(branch=branch) log_message(f"✅ Assigned {users_updated} users to default branch") return True except Exception as e: log_message(f"Error setting up branch system: {e}", 'ERROR') return False def fix_user_validation(): """Fix user validation issues""" try: log_message("Fixing user validation issues...") with connection.cursor() as cursor: # Fix NULL usernames cursor.execute(""" UPDATE users SET username = CONCAT('user_', id) WHERE username IS NULL OR username = '' """) affected = cursor.rowcount if affected > 0: log_message(f"✅ Fixed {affected} NULL usernames") # Fix users without proper role cursor.execute(""" UPDATE users SET role = 'borrower' WHERE role IS NULL OR role = '' """) affected = cursor.rowcount if affected > 0: log_message(f"✅ Fixed {affected} users without role") # Fix users without proper status cursor.execute(""" UPDATE users SET status = 'active' WHERE status IS NULL OR status = '' """) affected = cursor.rowcount if affected > 0: log_message(f"✅ Fixed {affected} users without status") # Ensure all users have first_name cursor.execute(""" UPDATE users SET first_name = 'User' WHERE (first_name IS NULL OR first_name = '') AND role = 'borrower' """) affected = cursor.rowcount if affected > 0: log_message(f"✅ Fixed {affected} users without first_name") return True except Exception as e: log_message(f"Error fixing user validation: {e}", 'ERROR') return False def test_client_creation(): """Test client creation functionality""" try: log_message("Testing client creation...") # Test data test_phone = "+254700999888" test_email = "testclient@example.com" test_id = "99999999" # Clean up any existing test data CustomUser.objects.filter(phone_number=test_phone).delete() CustomUser.objects.filter(email=test_email).delete() CustomUser.objects.filter(id_number=test_id).delete() # Get a branch branch = Branch.objects.filter(is_active=True).first() if not branch: log_message("❌ No active branch found for testing", 'ERROR') return False # Create test user user = CustomUser.objects.create_user( username=test_phone, email=test_email, phone_number=test_phone, first_name="Test", last_name="Client", date_of_birth="1990-01-01", gender="male", id_number=test_id, nationality="Kenyan", physical_address="Test Address", city="Nairobi", business_name="Test Business", business_type="retail", business_address="Test Business Address", role='borrower', status='active', branch=branch ) log_message(f"✅ Successfully created test client: {user.get_full_name()}") # Test duplicate detection try: duplicate_user = CustomUser.objects.create_user( username=f"{test_phone}_dup", email=f"dup_{test_email}", phone_number=test_phone, # Same phone number first_name="Duplicate", last_name="Test", role='borrower' ) log_message("❌ Duplicate detection failed - created user with same phone", 'ERROR') duplicate_user.delete() except Exception as e: log_message("✅ Duplicate detection working correctly") # Clean up test data user.delete() log_message("✅ Test client cleaned up") return True except Exception as e: log_message(f"❌ Client creation test failed: {e}", 'ERROR') return False def optimize_database(): """Optimize database performance""" try: log_message("Optimizing database...") with connection.cursor() as cursor: # Analyze and optimize tables tables_to_optimize = ['users', 'users_branch', 'users_customuser_accessible_branches'] for table in tables_to_optimize: if check_table_exists(table): try: cursor.execute(f"ANALYZE TABLE `{table}`") cursor.execute(f"OPTIMIZE TABLE `{table}`") log_message(f"✅ Optimized table: {table}") except Exception as e: log_message(f"Warning optimizing {table}: {e}", 'WARNING') return True except Exception as e: log_message(f"Error optimizing database: {e}", 'ERROR') return False def main(): """Main execution function""" log_message("=" * 80) log_message("COMPREHENSIVE CLIENT CREATION FIX SCRIPT") log_message("=" * 80) success_count = 0 total_steps = 6 # Step 1: Fix users table structure log_message("\n" + "=" * 50) log_message("STEP 1: FIXING USERS TABLE STRUCTURE") log_message("=" * 50) if fix_users_table_structure(): success_count += 1 log_message("✅ Users table structure fixed") else: log_message("❌ Failed to fix users table structure") # Step 2: Clean duplicate data log_message("\n" + "=" * 50) log_message("STEP 2: CLEANING DUPLICATE DATA") log_message("=" * 50) if clean_duplicate_data(): success_count += 1 log_message("✅ Duplicate data cleaned") else: log_message("❌ Failed to clean duplicate data") # Step 3: Ensure branch system log_message("\n" + "=" * 50) log_message("STEP 3: SETTING UP BRANCH SYSTEM") log_message("=" * 50) if ensure_branch_system(): success_count += 1 log_message("✅ Branch system set up") else: log_message("❌ Failed to set up branch system") # Step 4: Fix user validation log_message("\n" + "=" * 50) log_message("STEP 4: FIXING USER VALIDATION") log_message("=" * 50) if fix_user_validation(): success_count += 1 log_message("✅ User validation fixed") else: log_message("❌ Failed to fix user validation") # Step 5: Test client creation log_message("\n" + "=" * 50) log_message("STEP 5: TESTING CLIENT CREATION") log_message("=" * 50) if test_client_creation(): success_count += 1 log_message("✅ Client creation test passed") else: log_message("❌ Client creation test failed") # Step 6: Optimize database log_message("\n" + "=" * 50) log_message("STEP 6: OPTIMIZING DATABASE") log_message("=" * 50) if optimize_database(): success_count += 1 log_message("✅ Database optimized") else: log_message("❌ Failed to optimize database") # Final summary log_message("\n" + "=" * 80) log_message("FINAL SUMMARY") log_message("=" * 80) log_message(f"Completed {success_count}/{total_steps} steps successfully") if success_count == total_steps: log_message("🎉 ALL FIXES COMPLETED SUCCESSFULLY!") log_message("\nNext steps:") log_message("1. Restart your Django development server") log_message("2. Clear browser cache and cookies") log_message("3. Try creating a client again") log_message("4. The duplicate phone number error should be resolved") else: log_message(f"⚠️ {total_steps - success_count} steps failed - manual intervention may be needed") log_message("\n" + "=" * 80) log_message("SCRIPT COMPLETED") log_message("=" * 80) if __name__ == '__main__': main()