#!/usr/bin/env python
"""
Haven Grazuri - Financial Pages Fix
Fixes:
  1. P&L default date range -> Jan 1 of data year (not first of current month)
  2. Deactivate old duplicate accounts 202001 / 202003
  3. Post interest income GL entries from loan interest data
  4. Fix running balances for ALL accounts
  5. Clear cache + restart

Run: python gl_page_fix.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(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 Financial Pages Fix")
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, Min, Max
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()


# ── FIX 1: Edit the views file to use Jan-1 default ──────────────────────
inf("Fix 1 - Patching profit_and_loss default date range...")
views_path = os.path.join(PROJECT_ROOT, 'reports', 'financial_reports_views.py')
with open(views_path, encoding='utf-8') as f:
    src = f.read()

OLD_LINE = '    default_start = today.replace(day=1)'
NEW_LINES = (
    '    # Default to Jan 1 of earliest GL year so report shows real data\n'
    '    try:\n'
    '        from accounting.models import GeneralLedger as _GL\n'
    '        _ed = _GL.objects.order_by("transaction_date").values_list("transaction_date", flat=True).first()\n'
    '        default_start = datetime.date(_ed.year, 1, 1) if _ed else datetime.date(today.year, 1, 1)\n'
    '    except Exception:\n'
    '        default_start = datetime.date(today.year, 1, 1)'
)

# Only patch the profit_and_loss occurrence (second occurrence in the file)
# Find position of profit_and_loss function first
pl_pos = src.find('def profit_and_loss(')
if pl_pos == -1:
    p("  WARN: profit_and_loss function not found in views file")
else:
    # Find OLD_LINE after that position
    patch_pos = src.find(OLD_LINE, pl_pos)
    if patch_pos == -1:
        p("  INFO: default_start already patched or not found - skipping")
    else:
        if not DRY:
            src = src[:patch_pos] + NEW_LINES + src[patch_pos + len(OLD_LINE):]
            with open(views_path, 'w', encoding='utf-8') as f:
                f.write(src)
            p("  Patched: default start date now uses Jan 1 of earliest GL year")
        else:
            p("  DRY: would patch default_start in profit_and_loss")
ok("Fix 1 done.")


# ── FIX 2: Deactivate old duplicate accounts 202001, 202003 ──────────────
inf("Fix 2 - Deactivating old duplicate accounts (202001, 202003)...")
for code in ['202001', '202003']:
    try:
        acc = Account.objects.get(code=code)
        if acc.is_active:
            if not DRY:
                acc.is_active = False
                acc.save(update_fields=['is_active'])
                p("  Deactivated: " + code + " - " + acc.name)
            else:
                p("  DRY: would deactivate " + code + " - " + acc.name)
        else:
            p("  Already inactive: " + code)
    except Account.DoesNotExist:
        p("  Not found: " + code)
ok("Fix 2 done.")


# ── FIX 3: Post interest income entries ───────────────────────────────────
inf("Fix 3 - Posting interest income from loans...")

# Load income account
try:
    inc_acc  = Account.objects.get(code='4010')   # Interest Income - Loans
    cash_acc = Account.objects.get(code='1010')   # Cash
except Account.DoesNotExist as e:
    p("  SKIP Fix 3: " + str(e)); inc_acc = None

if inc_acc and not DRY:
    now = tz.now()
    existing = set(JournalEntry.objects.filter(
        reference_number__startswith='INT-').values_list('reference_number', flat=True))

    gl_objects = []; je_list = []; int_count = 0

    # For each loan, interest = total_repaid - principal_amount (if positive)
    for loan in Loan.objects.filter(is_deleted=False, disbursement_date__isnull=False):
        total_repaid = Repayment.objects.filter(loan=loan).aggregate(t=Sum('amount'))['t'] or ZERO
        principal    = loan.principal_amount or ZERO
        interest     = total_repaid - principal
        if interest <= ZERO:
            continue
        ref = 'INT-' + loan.loan_number
        if ref in existing:
            continue
        # Date = date of last repayment for this loan
        last_rep = Repayment.objects.filter(loan=loan).order_by('-payment_date').first()
        tx_date = last_rep.payment_date.date() if last_rep and hasattr(last_rep.payment_date,'date') else datetime.date.today()
        je_list.append((ref, tx_date, loan, interest))

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

    if je_list:
        with TX.atomic():
            # Bulk create JEs
            je_objects_db = [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 je_list]
            JournalEntry.objects.bulk_create(je_objects_db, batch_size=500)
            p("  Inserted " + str(len(je_objects_db)) + " interest JEs.")

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

            # Bulk create lines
            lines = []
            for ref, tx, loan, amt in je_list:
                jid = je_map[ref]
                lines.append(JournalEntryLine(journal_entry_id=jid, account=cash_acc,
                    description='Dr Cash - interest', 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)
            p("  Inserted " + str(len(lines)) + " interest lines.")

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

            # Bulk create GL rows
            gl_rows = []
            for ref, tx, loan, amt in je_list:
                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)
            p("  Inserted " + str(len(gl_rows)) + " interest GL rows.")

        int_count = len(je_list)
    p("  Posted " + str(int_count) + " interest income entries.")

elif DRY and inc_acc:
    total_interest = ZERO
    for loan in Loan.objects.filter(is_deleted=False):
        r = Repayment.objects.filter(loan=loan).aggregate(t=Sum('amount'))['t'] or ZERO
        diff = r - (loan.principal_amount or ZERO)
        if diff > 0: total_interest += diff
    p("  DRY: would post interest entries, total interest KES " + str(total_interest))

ok("Fix 3 done.")


# ── FIX 4: Recalculate ALL account running balances ───────────────────────
inf("Fix 4 - Recalculating running balances for ALL accounts...")
if not DRY:
    ZERO = Decimal('0.00')
    fixed_total = 0
    accs_with_gl = Account.objects.filter(
        ledger_entries__isnull=False).distinct()
    for acc in accs_with_gl:
        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)
            p("  Fixed " + str(len(to_update)) + " rows for " + acc.code + " (" + acc.name[:30] + ")")
            fixed_total += len(to_update)
    p("  Total balance rows fixed: " + str(fixed_total))
ok("Fix 4 done.")


# ── FIX 5: Clear cache + restart ─────────────────────────────────────────
inf("Fix 5 - Clearing cache...")
try:
    from django.core.cache import cache; cache.clear(); p("  Cache cleared.")
except Exception as e: p("  Cache 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("Fix 5 done.")


# ── FINAL SUMMARY ─────────────────────────────────────────────────────────
sep()
p("FINAL STATE")
sep()
from accounting.services.accounting_service import AccountingService
svc = AccountingService()
today = datetime.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:
        bal = svc.calculate_account_balance(acc, today, None)
        if bal != ZERO: nonzero += 1
    p(atype.ljust(10) + ": " + str(accs.count()) + " accounts, " + str(nonzero) + " with balance")

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')
p("")
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:      KES " + "{:>15,.2f}".format(ti))
diff = ta - tl - te
p("SFP Balanced:      " + ("YES" if abs(diff) < Decimal('0.01') else "NO  diff=" + str(diff)))
sep()
ok("DONE")
sep()