#!/usr/bin/env python
"""
Haven Grazuri - Fix 2: Correct income recognition + balance SFP

Issues to fix:
1. Delete lump-sum interest income entries (INT-*) posted on last repayment date
2. Re-post interest income distributed per-repayment proportionally
3. Close income to Retained Earnings so SFP balances
4. Recalculate all running balances
5. Clear cache + restart

Run: python gl_fix2.py [--dry-run]
"""
import sys, os, datetime, pathlib
from decimal import Decimal, ROUND_HALF_UP

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 ok(msg): print("[OK]  " + str(msg), flush=True)
def inf(msg):print("[..]  " + str(msg), flush=True)
def sep():   print("-"*60, flush=True)

DRY  = '--dry-run' in sys.argv
ZERO = Decimal('0.00')

sep()
p("  Haven Grazuri - Fix 2: Income Recognition + SFP Balance")
p("  " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
if DRY: p("  DRY RUN")
sep()

from accounting.models import Account, GeneralLedger, JournalEntry, JournalEntryLine, AccountBalance
from loans.models import Loan, Repayment
from django.db import transaction as TX
from django.db.models import Sum, Count
from django.utils import timezone as tz
from django.contrib.auth import get_user_model
User = get_user_model()
su = User.objects.filter(is_superuser=True).order_by('date_joined').first()
now = tz.now()


# ── STEP 1: Delete existing lump-sum INT-* entries ────────────────────────
inf("Step 1 - Removing lump-sum interest entries...")
int_jes = JournalEntry.objects.filter(reference_number__startswith='INT-')
count = int_jes.count()
p("  Found " + str(count) + " INT-* journal entries to remove")
if not DRY and count > 0:
    # Delete GL rows first, then lines, then JEs
    je_ids = list(int_jes.values_list('id', flat=True))
    gl_del, _ = GeneralLedger.objects.filter(journal_entry_id__in=je_ids).delete()
    ln_del, _ = JournalEntryLine.objects.filter(journal_entry_id__in=je_ids).delete()
    je_del, _ = int_jes.delete()
    p("  Deleted: " + str(gl_del) + " GL rows, " + str(ln_del) + " lines, " + str(je_del) + " JEs")
ok("Step 1 done.")


# ── STEP 2: Post interest income per repayment ────────────────────────────
inf("Step 2 - Posting per-repayment interest income...")

try:
    inc_acc  = Account.objects.get(code='4010')
    cash_acc = Account.objects.get(code='1010')
except Account.DoesNotExist as e:
    p("  FAIL: " + str(e)); sys.exit(1)

# For each loan: total_collected > principal → interest = excess
# Distribute proportionally across repayments by amount
je_specs = []  # (ref, date, loan, amount)

existing_int = set(JournalEntry.objects.filter(
    reference_number__startswith='INT-').values_list('reference_number', flat=True))

for loan in Loan.objects.filter(is_deleted=False, disbursement_date__isnull=False):
    principal = loan.principal_amount or ZERO
    reps = list(Repayment.objects.filter(loan=loan).order_by('payment_date'))
    if not reps:
        continue
    total_repaid = sum(r.amount for r in reps)
    total_interest = total_repaid - principal
    if total_interest <= ZERO:
        continue

    # Distribute interest proportionally across repayments
    distributed = ZERO
    for i, rep in enumerate(reps):
        ref = 'INT-' + str(rep.receipt_number or rep.id)
        if ref in existing_int:
            continue
        if i == len(reps) - 1:
            # Last repayment gets the remainder to avoid rounding drift
            int_amt = total_interest - distributed
        else:
            # Proportional share
            share = (rep.amount / total_repaid) if total_repaid > 0 else ZERO
            int_amt = (total_interest * share).quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
            distributed += int_amt
        if int_amt <= ZERO:
            continue
        tx_date = rep.payment_date.date() if hasattr(rep.payment_date, 'date') else rep.payment_date
        je_specs.append((ref, tx_date, loan, int_amt))

p("  Interest entries to post: " + str(len(je_specs)))

if not DRY and je_specs:
    BATCH = 500
    total_posted = 0
    for i in range(0, len(je_specs), BATCH):
        batch = je_specs[i:i+BATCH]
        with TX.atomic():
            # Insert JEs
            JournalEntry.objects.bulk_create([
                JournalEntry(reference_number=ref, transaction_date=tx,
                             description='Interest income - ' + loan.loan_number,
                             status='posted', loan=loan,
                             created_by=su, posted_by=su, posted_at=now)
                for ref, tx, loan, amt in batch], batch_size=BATCH)

            # Re-fetch PKs
            refs = [s[0] for s in batch]
            je_map = {je.reference_number: je.id for je in
                      JournalEntry.objects.filter(reference_number__in=refs).only('id','reference_number')}

            # Insert lines
            lines = []
            for ref, tx, loan, amt in batch:
                jid = je_map[ref]
                lines.append(JournalEntryLine(journal_entry_id=jid, account=cash_acc,
                    description='Dr Cash', debit_amount=amt, credit_amount=ZERO, line_number=1))
                lines.append(JournalEntryLine(journal_entry_id=jid, account=inc_acc,
                    description='Cr Interest Income', debit_amount=ZERO, credit_amount=amt, line_number=2))
            JournalEntryLine.objects.bulk_create(lines, batch_size=1000)

            # Re-fetch line PKs
            jids = list(je_map.values())
            line_map = {(ln.journal_entry_id, ln.line_number): ln.id
                        for ln in JournalEntryLine.objects.filter(
                            journal_entry_id__in=jids).only('id','journal_entry_id','line_number')}

            # Insert GL rows
            gl_rows = []
            for ref, tx, loan, amt in batch:
                jid = je_map[ref]
                gl_rows.append(GeneralLedger(
                    account=cash_acc, journal_entry_id=jid,
                    journal_entry_line_id=line_map.get((jid,1)),
                    transaction_date=tx, description='Dr Cash - interest',
                    reference_number=ref, debit_amount=amt, credit_amount=ZERO,
                    balance=ZERO, branch=None, posted_at=now, posted_by=su))
                gl_rows.append(GeneralLedger(
                    account=inc_acc, journal_entry_id=jid,
                    journal_entry_line_id=line_map.get((jid,2)),
                    transaction_date=tx, description='Cr Interest Income',
                    reference_number=ref, debit_amount=ZERO, credit_amount=amt,
                    balance=ZERO, branch=None, posted_at=now, posted_by=su))
            GeneralLedger.objects.bulk_create(gl_rows, batch_size=1000)
            total_posted += len(batch)
            p("  Batch " + str(i//BATCH+1) + ": posted " + str(len(batch)) + " entries")

    p("  Total interest entries posted: " + str(total_posted))
elif DRY:
    total_int = sum(s[3] for s in je_specs)
    p("  DRY: would post " + str(len(je_specs)) + " entries, total KES " + str(total_int))
ok("Step 2 done.")


# ── STEP 3: Close net income to Retained Earnings ─────────────────────────
inf("Step 3 - Closing income/expense to Retained Earnings...")
try:
    re_acc = Account.objects.get(code='3020')   # Retained Earnings
except Account.DoesNotExist:
    p("  WARN: Retained Earnings account 3020 not found - skipping close")
    re_acc = None

if re_acc:
    # Calculate net income = total income credits - total expense debits
    from decimal import Decimal
    today = datetime.date.today()

    total_income  = ZERO
    total_expense = ZERO
    for acc in Account.objects.filter(account_type='income', is_active=True):
        last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
        if last: total_income += last.balance

    for acc in Account.objects.filter(account_type='expense', is_active=True):
        last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
        if last: total_expense += last.balance

    net_income = total_income - total_expense
    p("  Total income: " + str(total_income) + "  Total expenses: " + str(total_expense))
    p("  Net income to close to RE: " + str(net_income))

    ref_close = 'CLOSE-NET-INCOME-' + str(today.year)
    if JournalEntry.objects.filter(reference_number=ref_close).exists():
        p("  SKIP: closing entry already exists")
    elif net_income != ZERO and not DRY:
        # Dr Income accounts (reduce to 0) / Cr Retained Earnings
        # We post a single closing entry: Dr Income Clearing / Cr Retained Earnings
        # Using summary: Dr 4010 (income) credit side = debit to close
        # Simple approach: post Dr Cash dummy... no.
        # Correct: Dr each income account for its balance / Cr Retained Earnings
        # We'll post one summary entry
        with TX.atomic():
            je_close = JournalEntry.objects.create(
                reference_number=ref_close,
                transaction_date=today,
                description='Year-end income close to retained earnings ' + str(today.year),
                status='posted', created_by=su, posted_by=su, posted_at=now)

            line_num = 1
            # Debit each income account to zero it out
            income_lines = []
            for acc in Account.objects.filter(account_type='income', is_active=True):
                last = GeneralLedger.objects.filter(account=acc).order_by('-transaction_date','-id').first()
                if last and last.balance > ZERO:
                    income_lines.append((acc, last.balance))

            for acc, bal in income_lines:
                JournalEntryLine.objects.create(
                    journal_entry=je_close, account=acc,
                    description='Close income to RE',
                    debit_amount=bal, credit_amount=ZERO, line_number=line_num)
                line_num += 1

            # Credit retained earnings with net income
            JournalEntryLine.objects.create(
                journal_entry=je_close, account=re_acc,
                description='Net income to retained earnings',
                debit_amount=ZERO, credit_amount=net_income, line_number=line_num)

            # Create GL rows for each line
            for ln in JournalEntryLine.objects.filter(journal_entry=je_close):
                GeneralLedger.objects.create(
                    account=ln.account, journal_entry=je_close,
                    journal_entry_line=ln,
                    transaction_date=today,
                    description=ln.description,
                    reference_number=ref_close,
                    debit_amount=ln.debit_amount,
                    credit_amount=ln.credit_amount,
                    balance=ZERO,
                    branch=None, posted_at=now, posted_by=su)

        p("  Posted closing entry: KES " + str(net_income) + " to Retained Earnings")
    elif DRY:
        p("  DRY: would post closing entry of KES " + str(net_income) + " to RE")
ok("Step 3 done.")


# ── STEP 4: Recalculate ALL running balances ──────────────────────────────
inf("Step 4 - Recalculating all GL running balances...")
if not DRY:
    fixed_total = 0
    for acc in Account.objects.filter(ledger_entries__isnull=False).distinct():
        entries = list(GeneralLedger.objects.filter(account=acc)
                       .order_by('transaction_date', 'posted_at', 'id'))
        if not entries: continue
        running = ZERO; to_update = []
        for gl in entries:
            if acc.account_type in ('asset', 'expense'):
                running = running + gl.debit_amount - gl.credit_amount
            else:
                running = running + gl.credit_amount - gl.debit_amount
            if gl.balance != running:
                gl.balance = running; to_update.append(gl)
        if to_update:
            GeneralLedger.objects.bulk_update(to_update, ['balance'], batch_size=500)
            fixed_total += len(to_update)
    p("  Total rows fixed: " + str(fixed_total))
ok("Step 4 done.")


# ── STEP 5: Clear cache + restart ────────────────────────────────────────
inf("Step 5 - Clearing cache...")
try:
    from django.core.cache import cache; cache.clear()
    p("  Cache cleared.")
except Exception as e: p("  Warn: " + str(e))
try:
    n,_ = AccountBalance.objects.all().delete()
    p("  Deleted " + str(n) + " AccountBalance rows.")
except Exception: pass
wsgi = pathlib.Path(PROJECT_ROOT) / 'passenger_wsgi.py'
if wsgi.exists():
    if not DRY: wsgi.touch()
    p("  Touched passenger_wsgi.py")
ok("Step 5 done.")


# ── SUMMARY ───────────────────────────────────────────────────────────────
sep()
p("FINAL STATE  " + datetime.datetime.now().strftime('%H:%M:%S'))
sep()
from accounting.services.accounting_service import AccountingService
svc = AccountingService()
today2 = datetime.date.today()

def _sum(atype):
    tot = ZERO
    for a in Account.objects.filter(account_type=atype, is_active=True):
        try: tot += svc.calculate_account_balance(a, today2, None)
        except Exception: pass
    return tot

ta = _sum('asset'); tl = _sum('liability'); te = _sum('equity'); ti = _sum('income')
p("Total Assets:      KES " + "{:>15,.2f}".format(ta))
p("Total Liabilities: KES " + "{:>15,.2f}".format(tl))
p("Total Equity:      KES " + "{:>15,.2f}".format(te))
p("Total Income (YTD):KES " + "{:>15,.2f}".format(ti))
diff = ta - tl - te
p("SFP Balanced:      " + ("YES" if abs(diff) < Decimal('0.01') else "NO  diff=" + str(diff)))

# P&L YTD check (Jan 1 to today)
jan1 = datetime.date(today2.year, 1, 1)
from datetime import timedelta
total_rev = ZERO
for acc in Account.objects.filter(account_type='income', is_active=True):
    try:
        closing = svc.calculate_account_balance(acc, today2, None)
        opening = svc.calculate_account_balance(acc, jan1 - timedelta(days=1), None)
        total_rev += closing - opening
    except Exception: pass
p("YTD Revenue:       KES " + "{:>15,.2f}".format(total_rev))
sep()
ok("DONE")
sep()