#!/usr/bin/env python
"""
gl_fix3.py - Fix SFP imbalance + backfill expenses into GL

1. Delete bad closing entries and repost correctly (zero income accounts)
2. Backfill approved expenses from expenses.Expense into GL
3. Recalculate all balances
4. Clear cache + restart

Run: python gl_fix3.py [--dry-run]
"""
import sys, os, datetime, pathlib
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')
DRY = '--dry-run' in sys.argv

sep(); p("  Haven Grazuri - GL Fix 3"); p("  "+datetime.datetime.now().strftime('%H:%M:%S'))
if DRY: p("  DRY RUN")
sep()

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


# ── STEP 1: Delete ALL closing entries and repost correctly ───────────────
p("Step 1 - Resetting income closing entries...")
close_jes = JournalEntry.objects.filter(reference_number__startswith='CLOSE-NET-INCOME-')
ids = list(close_jes.values_list('id', flat=True))
if ids and not DRY:
    GeneralLedger.objects.filter(journal_entry_id__in=ids).delete()
    JournalEntryLine.objects.filter(journal_entry_id__in=ids).delete()
    close_jes.delete()
    p("  Deleted " + str(len(ids)) + " closing entries.")
p("Step 1 done.")


# ── STEP 2: Recalculate all balances FIRST ────────────────────────────────
p("Step 2 - Recalculating all GL running balances (before close)...")
if not DRY:
    fixed = 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_upd = []
        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_upd.append(gl)
        if to_upd:
            GeneralLedger.objects.bulk_update(to_upd, ['balance'], batch_size=500)
            fixed += len(to_upd)
    p("  Fixed " + str(fixed) + " rows.")
p("Step 2 done.")


# ── STEP 3: Backfill approved expenses into GL ────────────────────────────
p("Step 3 - Backfilling expenses into GL...")
try:
    from expenses.models import Expense

    # Map expense categories to GL expense account codes
    # Using 'other_expenses' catch-all if no specific account exists
    CATEGORY_MAP = {
        'operational':  '502001',   # Office Rent / operational
        'staff':        '501001',   # Salaries and Wages
        'marketing':    '506001',   # Marketing & Advertising
        'utilities':    '502002',   # Electricity
        'office':       '505006',   # Office Supplies
        'transport':    '507001',   # Fuel & Mileage
        'maintenance':  '502005',   # Office Cleaning & Maintenance
        'loan_related': '509002',   # Interest Expense on Borrowings
        'other':        '509005',   # Miscellaneous Expenses
    }

    # Load expense accounts (only ones that exist)
    exp_accounts = {}
    for cat, code in CATEGORY_MAP.items():
        try:
            exp_accounts[cat] = Account.objects.get(code=code, is_active=True)
        except Account.DoesNotExist:
            pass

    # Fallback: use account 501001 if nothing else found
    fallback_acc = None
    for code in ['509005','501001','501007']:
        try:
            fallback_acc = Account.objects.get(code=code, is_active=True)
            break
        except Account.DoesNotExist:
            pass

    cash_acc = Account.objects.get(code='1010')

    # Get approved expenses not yet in GL
    existing_exp_refs = set(JournalEntry.objects.filter(
        reference_number__startswith='EXP-').values_list('reference_number', flat=True))

    expenses = Expense.objects.filter(status='approved').order_by('expense_date')
    p("  Total approved expenses: " + str(expenses.count()))

    exp_specs = []
    skipped = 0
    for exp in expenses:
        ref = 'EXP-' + str(exp.id)
        if ref in existing_exp_refs:
            skipped += 1
            continue
        acc = exp_accounts.get(exp.category, fallback_acc)
        if not acc:
            p("  WARN: no account for category '" + exp.category + "' - skipping exp " + str(exp.id))
            continue
        tx_date = exp.expense_date
        exp_specs.append((ref, tx_date, exp.title, acc, exp.amount))

    p("  Skipped (already in GL): " + str(skipped))
    p("  To post: " + str(len(exp_specs)))

    if exp_specs and not DRY:
        BATCH = 500
        total_exp_posted = 0
        for i in range(0, len(exp_specs), BATCH):
            batch = exp_specs[i:i+BATCH]
            with TX.atomic():
                JournalEntry.objects.bulk_create([
                    JournalEntry(reference_number=ref, transaction_date=tx,
                                 description='Expense: ' + desc[:100],
                                 status='posted', created_by=su, posted_by=su, posted_at=now)
                    for ref, tx, desc, acc, amt in batch], batch_size=BATCH)

                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')}

                lines = []
                for ref, tx, desc, acc, amt in batch:
                    jid = je_map[ref]
                    lines.append(JournalEntryLine(journal_entry_id=jid, account=acc,
                        description='Dr Expense: '+desc[:80],
                        debit_amount=amt, credit_amount=ZERO, line_number=1))
                    lines.append(JournalEntryLine(journal_entry_id=jid, account=cash_acc,
                        description='Cr Cash',
                        debit_amount=ZERO, credit_amount=amt, line_number=2))
                JournalEntryLine.objects.bulk_create(lines, batch_size=1000)

                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')}

                gl_rows = []
                for ref, tx, desc, acc, amt in batch:
                    jid = je_map[ref]
                    gl_rows.append(GeneralLedger(
                        account=acc, journal_entry_id=jid,
                        journal_entry_line_id=line_map.get((jid,1)),
                        transaction_date=tx, description='Dr Expense: '+desc[:80],
                        reference_number=ref, debit_amount=amt, credit_amount=ZERO,
                        balance=ZERO, branch=None, posted_at=now, posted_by=su))
                    gl_rows.append(GeneralLedger(
                        account=cash_acc, journal_entry_id=jid,
                        journal_entry_line_id=line_map.get((jid,2)),
                        transaction_date=tx, description='Cr Cash - expense',
                        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_exp_posted += len(batch)
                p("  Batch " + str(i//BATCH+1) + ": " + str(len(batch)) + " expenses posted")

        p("  Total expense entries posted: " + str(total_exp_posted))
    elif DRY:
        total_amt = sum(s[4] for s in exp_specs)
        p("  DRY: would post " + str(len(exp_specs)) + " expense entries, KES " + str(total_amt))

except Exception as e:
    p("  WARN: Expense backfill error: " + str(e))
    import traceback; traceback.print_exc()
p("Step 3 done.")


# ── STEP 4: Recalculate ALL balances again (after expenses) ───────────────
p("Step 4 - Recalculating all GL running balances...")
if not DRY:
    fixed = 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_upd = []
        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_upd.append(gl)
        if to_upd:
            GeneralLedger.objects.bulk_update(to_upd, ['balance'], batch_size=500)
            fixed += len(to_upd)
    p("  Fixed " + str(fixed) + " rows.")
p("Step 4 done.")


# ── STEP 5: Close net income (income - expenses) to Retained Earnings ─────
p("Step 5 - Closing net income to Retained Earnings...")
re_acc = Account.objects.get(code='3020')

total_income  = ZERO
total_expense = ZERO
income_lines  = []
expense_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:
        total_income += last.balance
        income_lines.append((acc, 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 and last.balance > ZERO:
        total_expense += last.balance
        expense_lines.append((acc, last.balance))

net_income = total_income - total_expense
p("  Total income:  KES " + str(total_income))
p("  Total expense: KES " + str(total_expense))
p("  Net income:    KES " + str(net_income))

if not DRY and net_income != ZERO:
    ref_close = 'CLOSE-NET-INCOME-' + str(today.year) + '-FINAL'
    with TX.atomic():
        je_c = JournalEntry.objects.create(
            reference_number=ref_close, transaction_date=today,
            description='Year-end income close to retained earnings',
            status='posted', created_by=su, posted_by=su, posted_at=now)

        line_num = 1
        # Debit all income accounts (zero them out)
        for acc, bal in income_lines:
            JournalEntryLine.objects.create(journal_entry=je_c, account=acc,
                description='Close to RE', debit_amount=bal, credit_amount=ZERO, line_number=line_num)
            line_num += 1
        # Credit all expense accounts (zero them out) — debit normal, so credit to close
        for acc, bal in expense_lines:
            JournalEntryLine.objects.create(journal_entry=je_c, account=acc,
                description='Close to RE', debit_amount=ZERO, credit_amount=bal, line_number=line_num)
            line_num += 1
        # Net goes to Retained Earnings
        if net_income > ZERO:
            JournalEntryLine.objects.create(journal_entry=je_c, account=re_acc,
                description='Net income to RE', debit_amount=ZERO, credit_amount=net_income, line_number=line_num)
        else:
            JournalEntryLine.objects.create(journal_entry=je_c, account=re_acc,
                description='Net loss to RE', debit_amount=abs(net_income), credit_amount=ZERO, line_number=line_num)

        # GL rows for closing entry
        for ln in JournalEntryLine.objects.filter(journal_entry=je_c):
            GeneralLedger.objects.create(
                account=ln.account, journal_entry=je_c, 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: " + ref_close)
elif DRY:
    p("  DRY: would close KES " + str(net_income) + " to Retained Earnings")
p("Step 5 done.")


# ── STEP 6: Final balance recalculation ──────────────────────────────────
p("Step 6 - Final balance recalculation...")
if not DRY:
    fixed = 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_upd = []
        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_upd.append(gl)
        if to_upd:
            GeneralLedger.objects.bulk_update(to_upd, ['balance'], batch_size=500)
            fixed += len(to_upd)
    p("  Fixed " + str(fixed) + " rows.")
p("Step 6 done.")


# ── STEP 7: Clear cache + restart ────────────────────────────────────────
try:
    from django.core.cache import cache; cache.clear(); p("Cache cleared.")
except Exception: pass
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")


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

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

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

# P&L YTD
from datetime import timedelta
jan1 = datetime.date(today.year, 1, 1)
ytd_rev = ZERO; ytd_exp = ZERO
for acc in Account.objects.filter(account_type='income', is_active=True):
    try:
        c = svc.calculate_account_balance(acc, today, None)
        o = svc.calculate_account_balance(acc, jan1-timedelta(days=1), None)
        ytd_rev += c-o
    except Exception: pass
for acc in Account.objects.filter(account_type='expense', is_active=True):
    try:
        c = svc.calculate_account_balance(acc, today, None)
        o = svc.calculate_account_balance(acc, jan1-timedelta(days=1), None)
        ytd_exp += c-o
    except Exception: pass
p("YTD Revenue:       KES " + "{:>14,.2f}".format(ytd_rev))
p("YTD Expenses:      KES " + "{:>14,.2f}".format(ytd_exp))
p("YTD Net Income:    KES " + "{:>14,.2f}".format(ytd_rev-ytd_exp))
sep()
p("DONE")
sep()