"""
Processes today's real IPN payments cleanly now that RCP-001618 is blacklisted.
Upload to /home/xygbfpsg/phingrazuri/ and run: python process_today_payments.py
"""
import sys, os, pymysql, json

DB = dict(host='localhost', user='xygbfpsg_graz',
          password="+MvX9&%PV']]pW}", database='xygbfpsg_loans',
          port=3306, charset='utf8mb4', autocommit=True)

try:
    conn = pymysql.connect(**DB)
    cur  = conn.cursor()
    print("Connected to production DB")
except Exception as e:
    print(f"FAIL: {e}"); sys.exit(1)

def q(sql, p=None):
    cur.execute(sql, p or ()); return cur.fetchall()

# ── Set up Django ─────────────────────────────────────────────────────────────
os.environ['DB_NAME']     = 'xygbfpsg_loans'
os.environ['DB_USER']     = 'xygbfpsg_graz'
os.environ['DB_PASSWORD'] = "+MvX9&%PV']]pW}"
os.environ['DB_HOST']     = 'localhost'
os.environ['DB_PORT']     = '3306'
os.environ['SITE_URL']    = 'https://grazuri.uzuriapps.xyz'
os.environ['AT_USERNAME'] = 'Uzuriapps'
os.environ['AT_API_KEY']  = 'atsk_e810a145ccd3a1089769cd17887da70ae76124ff044fa0ab949429e6b388954e6db55009'
os.environ['SMS_SENDER_ID']         = 'HavGrazuri'
os.environ['SASAPAY_CLIENT_ID']     = 'e63utew7Vk3RmwKxjuMY3jocycFb5eoMZkzyKrPQ'
os.environ['SASAPAY_CLIENT_SECRET'] = '6ysF7fWBESaCNYfsh4aJOWxijTFx4JFIu5LqGFWnMx7TJxKiwaysaiNwe14Wgj9FupB6fhtRFZUas2geZZLjHwbHiShbX8w5bvWjy061Bq1SHX9EQtsBCtnuTLgaOmDV'
os.environ['SASAPAY_MERCHANT_CODE'] = '1122'
os.environ['SASAPAY_NETWORK_CODE']  = '63902'
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')

import django
from django.conf import settings
settings.DATABASES['default'].update({
    'NAME': 'xygbfpsg_loans', 'USER': 'xygbfpsg_graz',
    'PASSWORD': "+MvX9&%PV']]pW}", 'HOST': 'localhost', 'PORT': '3306',
})
django.setup()
from django.db import connections; connections['default'].close()
from payments.sasapay_service import process_ipn_callback
from loans.models import Repayment

# ── Get today's unique IPN payloads ──────────────────────────────────────────
print("\n-- Today's IPN logs --")
today_ipns = q("""
    SELECT DISTINCT raw_data FROM sasapay_ipn_logs
    WHERE DATE(created_at) = CURDATE()
    ORDER BY created_at ASC
""")
print(f"   Found {len(today_ipns)} IPN records today")

# Deduplicate by TransID
seen = {}
for row in today_ipns:
    data = row[0] if isinstance(row[0], dict) else json.loads(row[0])
    tid  = data.get('TransID', '')
    if tid and tid not in seen:
        seen[tid] = data

print(f"   Unique TransIDs: {len(seen)}")

# ── Process each unique payment ───────────────────────────────────────────────
print("\n-- Processing payments --")
for trans_id, data in seen.items():
    amount   = data.get('TransAmount', '0')
    bill_ref = data.get('BillRefNumber', '')

    # Check if already properly saved (not rolled back)
    existing = Repayment.objects.filter(mpesa_transaction_id=trans_id)
    if existing.exists():
        rep = existing.first()
        # Verify it has a valid receipt (not a blacklisted one)
        if rep.receipt_number and rep.receipt_number not in ('RCP-001618', 'RCP-002440', ''):
            print(f"   SKIP {trans_id} -> already has {rep.receipt_number} ({rep.loan.loan_number if rep.loan else 'no loan'})")
            continue
        else:
            # Bad receipt — delete and reprocess
            print(f"   DELETE bad repayment for {trans_id} (receipt={rep.receipt_number})")
            existing.delete()

    print(f"   Processing {trans_id} | KES {amount} | BillRef:{bill_ref}")
    result = process_ipn_callback(data)
    if result.get('success'):
        print(f"   OK -> Receipt:{result.get('receipt_number')} Loan:{result.get('loan_number')}")
    else:
        print(f"   FAIL -> {result.get('message')}")

# ── Final check ───────────────────────────────────────────────────────────────
print("\n-- Final state --")
today_count = Repayment.objects.filter(created_at__date=__import__('datetime').date.today()).count()
total_count = Repayment.objects.count()
print(f"   Today's repayments : {today_count}")
print(f"   Total repayments   : {total_count}")

print("\n-- Today's repayments --")
from django.utils import timezone
today_reps = Repayment.objects.filter(
    created_at__date=timezone.now().date()
).select_related('loan', 'loan__borrower').order_by('-created_at')
for r in today_reps:
    b = r.loan.borrower if r.loan else None
    print(f"   {r.receipt_number} | KES {r.amount} | {r.mpesa_transaction_id} | "
          f"{r.loan.loan_number if r.loan else 'N/A'} | "
          f"{b.get_full_name() if b else 'N/A'}")

cur.close(); conn.close()
print("\nDone. Refresh https://grazuri.uzuriapps.xyz/loans/repayments/")