#!/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()
