#!/usr/bin/env python
"""
Haven Grazuri - GL Diagnosis + Full Balance Fix
Run: python gl_diagnose.py
"""
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(msg): print(str(msg), flush=True)
def sep():  print("-"*60, flush=True)
ZERO = Decimal('0.00')

from accounting.models import Account, GeneralLedger, JournalEntry, AccountBalance
from accounting.services.accounting_service import AccountingService
from django.db.models import Count
from datetime import date

sep()
p("GL DIAGNOSIS  " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
sep()

# ── 1. GL overview ────────────────────────────────────────────────────────
p("\n1. GL OVERVIEW")
p("   Total GL rows:         " + str(GeneralLedger.objects.count()))
p("   GL rows balance==0:    " + str(GeneralLedger.objects.filter(balance=ZERO).count()))
p("   GL rows balance!=0:    " + str(GeneralLedger.objects.exclude(balance=ZERO).count()))
p("   Total JournalEntries:  " + str(JournalEntry.objects.count()))
for row in JournalEntry.objects.values('status').annotate(n=Count('id')).order_by('status'):
    p("   JE status='" + str(row['status']) + "': " + str(row['n']))

# ── 2. Balance check per account type ────────────────────────────────────
p("\n2. ACCOUNT BALANCES (via last GL balance field)")
svc = AccountingService()
today = date.today()
for atype in ['asset','income','expense','liability','equity']:
    accs = Account.objects.filter(account_type=atype, is_active=True)
    nonzero = 0
    for acc in accs:
        last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
        if last and last.balance != ZERO:
            nonzero += 1
    p("   " + atype.ljust(10) + " accounts: " + str(accs.count()) + " total, " + str(nonzero) + " with non-zero balance")

# ── 3. Show non-zero accounts ─────────────────────────────────────────────
p("\n3. ALL ACCOUNTS WITH NON-ZERO GL BALANCE")
for acc in Account.objects.filter(is_active=True).order_by('account_type','code'):
    last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
    if last and last.balance != ZERO:
        p("   " + acc.code.ljust(8) + " " + acc.account_type.ljust(10) + " " + acc.name[:35].ljust(36) + " bal=" + str(last.balance))

# ── 4. Income accounts with GL entries ───────────────────────────────────
p("\n4. INCOME ACCOUNTS WITH GL ENTRIES")
for acc in Account.objects.filter(account_type='income', is_active=True):
    cnt = GeneralLedger.objects.filter(account=acc).count()
    if cnt > 0:
        last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
        p("   " + acc.code + " " + acc.name[:30] + "  rows=" + str(cnt) + " last_bal=" + str(last.balance if last else 0))

# ── 5. P&L date range check ───────────────────────────────────────────────
p("\n5. WHAT THE P&L VIEW WOULD FIND FOR SEPT 2026")
from datetime import date as d
start = d(2026,9,1); end = d(2026,9,30)
for acc in Account.objects.filter(account_type='income', is_active=True):
    from accounting.models import GeneralLedger as GL
    from django.db.models import Sum
    agg = GL.objects.filter(account=acc, transaction_date__gte=start,
                             transaction_date__lte=end).aggregate(
        dr=Sum('debit_amount'), cr=Sum('credit_amount'))
    if agg['cr'] or agg['dr']:
        p("   " + acc.code + " " + acc.name[:30] + "  debits=" + str(agg['dr']) + " credits=" + str(agg['cr']))

sep()
p("DIAGNOSIS COMPLETE")
sep()