#!/usr/bin/env python """ Check Production Database - Non-interactive with hardcoded credentials """ import os import sys # Set production database credentials db_name = 'xygbfpsg_graz' db_user = 'xygbfpsg_graz' db_password = ',qdN3O_!}oC67(]W' db_host = 'localhost' db_port = '3306' print("\n" + "="*60) print("PRODUCTION DATABASE CHECK") print("="*60) print(f"Database: {db_name}") print(f"User: {db_user}") print(f"Host: {db_host}") print("="*60) # Set environment variables for Django os.environ['DB_NAME'] = db_name os.environ['DB_USER'] = db_user os.environ['DB_PASSWORD'] = db_password os.environ['DB_HOST'] = db_host os.environ['DB_PORT'] = db_port # Now setup Django import django from pathlib import Path BASE_DIR = Path(__file__).resolve().parent sys.path.insert(0, str(BASE_DIR)) os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') try: django.setup() print("✓ Django setup successful\n") except Exception as e: print(f"✗ Django setup failed: {e}\n") sys.exit(1) from django.db import connection from users.models import CustomUser from loans.models import Loan from loans.models import Repayment def check_database(): """Check database contents""" try: # Test connection print("[1/10] Testing Database Connection...") with connection.cursor() as cursor: cursor.execute("SELECT VERSION()") version = cursor.fetchone()[0] print(f"✓ Connected to MySQL/MariaDB: {version}\n") # Check users print("[2/10] Checking Users...") total_users = CustomUser.objects.count() print(f" Total Users: {total_users}") admin_users = CustomUser.objects.filter(role='admin').count() print(f" Admin Users: {admin_users}") loan_officers = CustomUser.objects.filter(role='loan_officer').count() print(f" Loan Officers: {loan_officers}") borrowers = CustomUser.objects.filter(role='borrower').count() print(f" Borrowers: {borrowers}") active_borrowers = CustomUser.objects.filter(role='borrower', status='active').count() print(f" Active Borrowers: {active_borrowers}\n") # Check portfolio managers print("[3/10] Checking Portfolio Manager Assignments...") borrowers_with_pm = CustomUser.objects.filter( role='borrower', portfolio_manager__isnull=False ).count() print(f" Borrowers WITH Portfolio Manager: {borrowers_with_pm}") borrowers_without_pm = CustomUser.objects.filter( role='borrower', portfolio_manager__isnull=True ).count() print(f" Borrowers WITHOUT Portfolio Manager: {borrowers_without_pm}") if borrowers_without_pm > 0: print(f" ⚠️ WARNING: {borrowers_without_pm} borrowers have no portfolio manager!\n") else: print(f" ✓ All borrowers have portfolio managers\n") # Check loans print("[4/10] Checking Loans...") total_loans = Loan.objects.count() print(f" Total Loans: {total_loans}") active_loans = Loan.objects.filter(status='active').count() print(f" Active Loans: {active_loans}") completed_loans = Loan.objects.filter(status='completed').count() print(f" Completed Loans: {completed_loans}") defaulted_loans = Loan.objects.filter(status='defaulted').count() print(f" Defaulted Loans: {defaulted_loans}\n") # Check loan amounts print("[5/10] Checking Loan Amounts...") from django.db.models import Sum, F total_disbursed = Loan.objects.aggregate( total=Sum('total_amount') )['total'] or 0 print(f" Total Disbursed: KES {total_disbursed:,.2f}") # Calculate outstanding manually active_loans_list = Loan.objects.filter(status='active') total_outstanding = 0 for loan in active_loans_list: # Outstanding = total_amount - amount_paid (if field exists) try: outstanding = loan.total_amount - (loan._amount_paid_cache or 0) total_outstanding += outstanding except: total_outstanding += loan.total_amount print(f" Total Outstanding: KES {total_outstanding:,.2f}\n") # Check repayments print("[6/10] Checking Repayments...") total_repayments = Repayment.objects.count() print(f" Total Repayments: {total_repayments}") total_repaid = Repayment.objects.aggregate( total=Sum('amount') )['total'] or 0 print(f" Total Repaid: KES {total_repaid:,.2f}\n") # Check branches print("[7/10] Checking Branches...") try: from utils.models import Branch total_branches = Branch.objects.count() print(f" Total Branches: {total_branches}") if total_branches > 0: branches = Branch.objects.all()[:5] for branch in branches: branch_borrowers = CustomUser.objects.filter( role='borrower', branch=branch ).count() print(f" - {branch.name}: {branch_borrowers} borrowers") except Exception as e: print(f" ⚠️ Branch table not found or not accessible") print() # Sample borrowers print("[8/10] Sample Borrowers (First 5)...") sample_borrowers = CustomUser.objects.filter(role='borrower')[:5] if sample_borrowers.exists(): for borrower in sample_borrowers: pm_name = borrower.portfolio_manager.get_full_name() if borrower.portfolio_manager else "None" loan_count = Loan.objects.filter(borrower=borrower).count() print(f" - {borrower.get_full_name()}") print(f" ID: {borrower.id}") print(f" Portfolio Manager: {pm_name}") print(f" Loans: {loan_count}") print(f" Status: {borrower.status}") else: print(" ⚠️ No borrowers found!") print() # Check tables exist print("[9/10] Checking Critical Tables...") with connection.cursor() as cursor: cursor.execute("SHOW TABLES") tables = [row[0] for row in cursor.fetchall()] critical_tables = [ 'users_customuser', 'loans_loan', 'loans_loanrepayment', 'payments_mpesamessage', 'utils_branch', ] for table in critical_tables: if table in tables: print(f" ✓ {table}") else: print(f" ✗ {table} MISSING!") print() # Summary print("[10/10] Summary...") print("="*60) if total_users == 0: print("❌ DATABASE IS EMPTY - No users found!") elif borrowers == 0: print("⚠️ NO BORROWERS - Only admin/staff users exist") elif total_loans == 0: print("⚠️ NO LOANS - Database has users but no loans") elif borrowers_without_pm > 0: print(f"⚠️ {borrowers_without_pm} borrowers need portfolio managers assigned") print("\n To fix, run: python assign_portfolio_managers.py") else: print("✅ DATABASE LOOKS GOOD!") print(f"\n {borrowers} borrowers") print(f" {total_loans} loans") print(f" KES {total_outstanding:,.2f} outstanding") print("="*60) except Exception as e: print(f"\n❌ ERROR: {e}") import traceback traceback.print_exc() return False return True if __name__ == "__main__": check_database()