#!/usr/bin/env python
"""gl_check.py - Show what's in the GL and verify numbers make sense"""
import sys, os, datetime
from decimal import Decimal

PROJECT_ROOT = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, PROJECT_ROOT)
os.environ['DB_NAME']='xygbfpsg_loans'; os.environ['DB_USER']='xygbfpsg_graz'
os.environ['DB_PASSWORD']='j.ez-xy6##y.rllB'; os.environ['DB_HOST']='localhost'
os.environ['DB_PORT']='3306'; os.environ['DJANGO_SETTINGS_MODULE']='branch_system.settings'
try:
    import dotenv as _d; _d.load_dotenv = lambda *a,**kw: None
except ImportError: pass
import django; django.setup()
from django.conf import settings as _s
_s.DATABASES['default'].update({'NAME':'xygbfpsg_loans','USER':'xygbfpsg_graz',
    'PASSWORD':'j.ez-xy6##y.rllB','HOST':'localhost','PORT':'3306'})
from django import db as _db; _db.connections.close_all()

def p(m): print(str(m), flush=True)
def sep(): print("-"*60, flush=True)
ZERO = Decimal('0.00')

from accounting.models import Account, GeneralLedger, JournalEntry
from loans.models import Loan, Repayment
from django.db.models import Sum, Count

sep(); p("GL CHECK REPORT  "+datetime.datetime.now().strftime('%H:%M:%S')); sep()

# 1. Journal entry type summary
p("\n1. JOURNAL ENTRY TYPES (by reference prefix):")
from django.db.models import Count
prefixes = {}
for je in JournalEntry.objects.all():
    pre = je.reference_number.split('-')[0] if je.reference_number else 'OTHER'
    prefixes[pre] = prefixes.get(pre, 0) + 1
for pre, cnt in sorted(prefixes.items(), key=lambda x: -x[1]):
    p("   " + pre.ljust(20) + str(cnt))

# 2. GL balances for all accounts with activity
p("\n2. ALL ACCOUNTS WITH GL ENTRIES:")
for acc in Account.objects.filter(is_active=True).order_by('account_type','code'):
    cnt = GeneralLedger.objects.filter(account=acc).count()
    if cnt == 0: continue
    last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
    total_dr = GeneralLedger.objects.filter(account=acc).aggregate(t=Sum('debit_amount'))['t'] or ZERO
    total_cr = GeneralLedger.objects.filter(account=acc).aggregate(t=Sum('credit_amount'))['t'] or ZERO
    p("   " + acc.code.ljust(10) + acc.account_type.ljust(10) + acc.name[:28].ljust(30) +
      " Dr=" + str(total_dr).rjust(12) + " Cr=" + str(total_cr).rjust(12) +
      " LastBal=" + str(last.balance if last else 0).rjust(12))

# 3. Source of 401002 balance
p("\n3. 401002 FEE INCOME - HOW DID IT GET 42051?")
acc_fee = Account.objects.get(code='401002')
for gl in GeneralLedger.objects.filter(account=acc_fee).order_by('transaction_date')[:20]:
    p("   " + str(gl.transaction_date) + " Dr=" + str(gl.debit_amount) +
      " Cr=" + str(gl.credit_amount) + " Bal=" + str(gl.balance) +
      " ref=" + str(gl.reference_number))

# 4. Source of 4010 interest income
p("\n4. 4010 INTEREST INCOME - SAMPLE ENTRIES:")
acc_int = Account.objects.get(code='4010')
total_int = GeneralLedger.objects.filter(account=acc_int).aggregate(t=Sum('credit_amount'))['t'] or ZERO
p("   Total credits to 4010: " + str(total_int))
for gl in GeneralLedger.objects.filter(account=acc_int).order_by('transaction_date')[:5]:
    p("   " + str(gl.transaction_date) + " Cr=" + str(gl.credit_amount) + " ref=" + str(gl.reference_number))

# 5. Cash balance reconciliation
p("\n5. CASH RECONCILIATION:")
acc_cash = Account.objects.get(code='1010')
total_cash_dr = GeneralLedger.objects.filter(account=acc_cash).aggregate(t=Sum('debit_amount'))['t'] or ZERO
total_cash_cr = GeneralLedger.objects.filter(account=acc_cash).aggregate(t=Sum('credit_amount'))['t'] or ZERO
p("   Cash total debits:  " + str(total_cash_dr))
p("   Cash total credits: " + str(total_cash_cr))
p("   Cash net balance:   " + str(total_cash_dr - total_cash_cr))

p("\n6. LOAN PORTFOLIO CHECK:")
acc_lp = Account.objects.get(code='1020')
total_lp_dr = GeneralLedger.objects.filter(account=acc_lp).aggregate(t=Sum('debit_amount'))['t'] or ZERO
total_lp_cr = GeneralLedger.objects.filter(account=acc_lp).aggregate(t=Sum('credit_amount'))['t'] or ZERO
p("   LP total debits (disbursed): " + str(total_lp_dr))
p("   LP total credits (repaid):   " + str(total_lp_cr))
p("   LP net balance:              " + str(total_lp_dr - total_lp_cr))
total_principal = Loan.objects.filter(is_deleted=False).aggregate(t=Sum('principal_amount'))['t'] or ZERO
total_repaid = Repayment.objects.aggregate(t=Sum('amount'))['t'] or ZERO
p("   Actual loans disbursed: " + str(total_principal))
p("   Actual repayments:      " + str(total_repaid))
p("   Outstanding (expected): " + str(total_principal - total_repaid))

p("\n7. EXPENSES CHECK:")
try:
    from expenses.models import Expense
    approved_total = Expense.objects.filter(status='approved').aggregate(t=Sum('amount'))['t'] or ZERO
    p("   Approved expenses total: " + str(approved_total))
    acc_exp = Account.objects.get(code='502001')
    exp_gl_total = GeneralLedger.objects.filter(account=acc_exp).aggregate(t=Sum('debit_amount'))['t'] or ZERO
    p("   GL expense 502001 total debits: " + str(exp_gl_total))
except Exception as e:
    p("   Error: " + str(e))

sep(); p("DONE"); sep()