#!/usr/bin/env python
"""
gl_fix4.py - Clean slate: fix closing entries + P&L activity

The problem: closing entries (CLOSE-*) are dated Sep 1 2026, so they fall
inside the P&L period and make income look negative.

Fix:
1. Delete all closing entries
2. Verify income/expense account balances are correct
3. Post closing entry dated Dec 31 2025 (before all activity) so it never
   appears in 2026 P&L calculations
   Actually - better approach: DON'T use closing entries at all.
   Instead move the net income directly to RE via a balance adjustment
   dated Dec 31 of LAST year.
4. Fix the P&L view to exclude CLOSE-* reference numbers from activity calc

Run: python gl_fix4.py
"""
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')

sep(); p("  Haven Grazuri - GL Fix 4 (Clean Closing)"); p("  "+datetime.datetime.now().strftime('%H:%M:%S')); 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
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 ───────────────────────────────────
p("Step 1 - Deleting all closing entries...")
close_jes = JournalEntry.objects.filter(reference_number__startswith='CLOSE-')
ids = list(close_jes.values_list('id', flat=True))
if ids:
    GeneralLedger.objects.filter(journal_entry_id__in=ids).delete()
    JournalEntryLine.objects.filter(journal_entry_id__in=ids).delete()
    n = close_jes.delete()[0]
    p("  Deleted " + str(n) + " closing JEs and their GL/line rows.")
else:
    p("  No closing entries found.")
p("Step 1 done.")


# ── STEP 2: Recalculate balances (no closing entries now) ─────────────────
p("Step 2 - Recalculating all GL running balances...")
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: Show true P&L state ───────────────────────────────────────────
p("Step 3 - Calculating true P&L...")
total_income = ZERO; total_expense = ZERO
income_details = []; expense_details = []

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_details.append("    " + acc.code + " " + acc.name[:30] + ": " + str(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_details.append("    " + acc.code + " " + acc.name[:30] + ": " + str(last.balance))

net_income = total_income - total_expense
p("  INCOME ACCOUNTS:")
for d in income_details: p(d)
p("  EXPENSE ACCOUNTS:")
for d in expense_details: p(d)
p("  Total Income:    KES " + str(total_income))
p("  Total Expenses:  KES " + str(total_expense))
p("  Net Income:      KES " + str(net_income))
p("Step 3 done.")


# ── STEP 4: Post a SINGLE correct closing entry dated Dec 31 LAST year ────
# This puts the prior-year retained earnings into equity WITHOUT touching
# the current-year P&L activity. The 2026 income/expense accounts stay open
# and the P&L shows the correct YTD figures.
# For a company that started in 2026 with no prior year, net_income IS the
# current year figure. We do NOT close current year income/expenses.
# Instead: post the opening Share Capital injection as the equity basis.
# The SFP equation: Assets = Liabilities + Equity + (current year net income)
# Since the SFP IS balanced (yes from fix3), we just need the P&L to show
# correct ACTIVITY figures.

p("Step 4 - Verifying SFP equation...")
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')
diff = ta - tl - te
p("  Assets=" + str(ta) + " Liabilities=" + str(tl) + " Equity=" + str(te))
p("  Income=" + str(ti) + " Expenses=" + str(tx2))
p("  SFP diff (Assets - L - E): " + str(diff))

# If diff != 0, post a balancing entry to RE
re_acc = Account.objects.get(code='3020')
if abs(diff) >= Decimal('0.01'):
    p("  Posting SFP balancing entry of " + str(diff) + " to Retained Earnings...")
    ref_bal = 'SFP-BALANCE-ADJ-' + str(today)
    if not JournalEntry.objects.filter(reference_number=ref_bal).exists():
        # Use a dummy asset account to balance if needed
        cash_acc = Account.objects.get(code='1010')
        with TX.atomic():
            je_b = JournalEntry.objects.create(
                reference_number=ref_bal, transaction_date=today,
                description='SFP balance adjustment',
                status='posted', created_by=su, posted_by=su, posted_at=now)
            if diff > ZERO:
                # Assets > L+E: credit RE to increase equity
                ln1 = JournalEntryLine.objects.create(journal_entry=je_b, account=re_acc,
                    description='SFP balance adj', debit_amount=ZERO, credit_amount=diff, line_number=1)
                ln2 = JournalEntryLine.objects.create(journal_entry=je_b, account=re_acc,
                    description='SFP balance adj offset', debit_amount=diff, credit_amount=ZERO, line_number=2)
            else:
                ln1 = JournalEntryLine.objects.create(journal_entry=je_b, account=re_acc,
                    description='SFP balance adj', debit_amount=abs(diff), credit_amount=ZERO, line_number=1)
                ln2 = JournalEntryLine.objects.create(journal_entry=je_b, account=re_acc,
                    description='SFP balance adj offset', debit_amount=ZERO, credit_amount=abs(diff), line_number=2)
        p("  This approach won't work - the real fix is in the view.")
        JournalEntry.objects.filter(reference_number=ref_bal).delete()
    p("  The SFP imbalance = net income not yet in equity.")
    p("  This is CORRECT accounting: income flows to equity via retained earnings.")
    p("  The SFP view needs to ADD net income to equity total.")
else:
    p("  SFP is balanced - no adjustment needed.")
p("Step 4 done.")


# ── STEP 5: Fix the SFP view to include net income in equity ──────────────
p("Step 5 - Patching financial_statements view to include net income in equity...")
views_path = os.path.join(PROJECT_ROOT, 'reports', 'financial_reports_views.py')
with open(views_path, encoding='utf-8') as f:
    src = f.read()

# The financial_statements view sums equity accounts. We need to also add
# YTD net income. Find where total_equity is computed and add net income.
# Look for total_equity calculation
OLD_EQUITY = (
    "total_equity      = sum(Decimal(str(i['amount'] or 0)) for i in equity_items)"
)
NEW_EQUITY = (
    "total_equity      = sum(Decimal(str(i['amount'] or 0)) for i in equity_items)\n"
    "    # Add current-year net income to equity (not yet closed to retained earnings)\n"
    "    try:\n"
    "        from accounting.services.accounting_service import AccountingService as _AS\n"
    "        from accounting.services.report_service import ReportService as _RS\n"
    "        _svc = _AS()\n"
    "        _ytd_income = sum(_svc.calculate_account_balance(a, as_of_date, None)\n"
    "            for a in Account.objects.filter(account_type='income', is_active=True))\n"
    "        _ytd_expense = sum(_svc.calculate_account_balance(a, as_of_date, None)\n"
    "            for a in Account.objects.filter(account_type='expense', is_active=True))\n"
    "        _net_income = _ytd_income - _ytd_expense\n"
    "        if _net_income != Decimal('0.00'):\n"
    "            equity_items.append({'code': 'NET_INCOME', 'name': 'Current Year Net Income',\n"
    "                'note': '', 'amount': _net_income,\n"
    "                'subtype': '', 'non_current': False})\n"
    "            total_equity += _net_income\n"
    "    except Exception:\n"
    "        pass"
)

if OLD_EQUITY in src:
    src = src.replace(OLD_EQUITY, NEW_EQUITY)
    with open(views_path, 'w', encoding='utf-8') as f:
        f.write(src)
    p("  Patched: financial_statements now includes net income in equity.")
else:
    p("  WARN: Could not find total_equity line in views - patch manually.")
    p("  Looking for: " + OLD_EQUITY[:60])
    # Show what's there
    import re
    matches = [l for l in src.split('\n') if 'total_equity' in l and 'sum(' in l]
    for m in matches[:3]: p("  Found: " + m.strip())
p("Step 5 done.")


# ── STEP 6: 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(): wsgi.touch(); p("Touched passenger_wsgi.py")


# ── FINAL SUMMARY ────────────────────────────────────────────────────────
sep(); p("FINAL STATE  "+datetime.datetime.now().strftime('%H:%M:%S')); sep()
ta=_sum('asset'); tl=_sum('liability'); te=_sum('equity')
ti=_sum('income'); tx2=_sum('expense')
net = ti - tx2
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))
p("Net Income:        KES " + "{:>14,.2f}".format(net))
p("Assets = L+E+NetInc: " + ("YES" if abs(ta-tl-te-net) < Decimal('0.01') else "NO diff="+str(ta-tl-te-net)))
p("  (SFP will balance once view adds net income to equity)")
sep()
p("DONE")
sep()