"""
Fixes:
1. RCP-001618 collision — it's in receipts but not repayments, confusing the generator
2. Test loans (TEST-*) linked to real borrowers — deactivate them so real loans are used
3. Ensure real loans (GRZ-*, BSH-*) are active in Django for all borrowers
4. Clean up duplicate repayments created today

Upload to /home/xygbfpsg/phingrazuri/ and run: python fix_receipt_and_loans.py
"""
import sys, os, pymysql, json, uuid
from datetime import datetime, date, timedelta

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"❌ {e}"); sys.exit(1)

def q(sql, p=None):
    cur.execute(sql, p or ()); return cur.fetchall()
def run(sql, p=None):
    try: cur.execute(sql, p or ()); return True
    except Exception as e: return str(e)

# ── FIX 1: Remove RCP-001618 from receipts (it has no matching repayment) ─────
print("\n── FIX 1: Fix RCP-001618 collision ──────────────────────────────────────")
# Check if RCP-001618 is in repayments
in_repayments = q("SELECT id FROM repayments WHERE receipt_number='RCP-001618'")
in_receipts   = q("SELECT id FROM receipts WHERE receipt_number='RCP-001618'")
print(f"   RCP-001618 in repayments: {len(in_repayments)}")
print(f"   RCP-001618 in receipts  : {len(in_receipts)}")

if in_receipts and not in_repayments:
    # It's an orphan in receipts — remove it so the generator can use it
    r = run("DELETE FROM receipts WHERE receipt_number='RCP-001618'")
    print(f"   ✅ Deleted orphan RCP-001618 from receipts table")
elif in_receipts and in_repayments:
    print(f"   ✅ RCP-001618 exists in both tables — no action needed")
else:
    print(f"   ✅ RCP-001618 not in receipts — no collision")

# Also check RCP-002440 duplicates
dupes_002440 = q("SELECT id, mpesa_transaction_id, loan_id FROM repayments WHERE receipt_number='RCP-002440'")
print(f"\n   RCP-002440 repayments: {len(dupes_002440)}")
if len(dupes_002440) > 1:
    # Keep only the first, delete the rest
    keep = dupes_002440[0]
    for dup in dupes_002440[1:]:
        run("DELETE FROM receipts WHERE repayment_id=%s", [str(dup[0]).replace('-','')])
        run("DELETE FROM repayments WHERE id=%s", [str(dup[0]).replace('-','')])
    print(f"   ✅ Kept 1, deleted {len(dupes_002440)-1} duplicate RCP-002440 repayments")

# ── FIX 2: Deactivate TEST-* loans so real loans are matched ──────────────────
print("\n── FIX 2: Deactivate test loans ─────────────────────────────────────────")
test_loans = q("SELECT id, loan_number, borrower_id FROM loans WHERE loan_number LIKE 'TEST-%%'")
print(f"   Found {len(test_loans)} TEST-* loans")
for loan in test_loans:
    r = run("UPDATE loans SET status='paid' WHERE id=%s", [str(loan[0]).replace('-','')])
    print(f"   ✅ Marked as paid: {loan[1]}")

# ── FIX 3: Ensure real loans from old system are active in Django ──────────────
print("\n── FIX 3: Sync real active loans from old system ────────────────────────")

# Get all active loans from old loan_info that aren't in Django yet
old_active = q("""
    SELECT l.id, l.baccount, l.borrower, l.amount, l.balance, l.pay_date,
           b.phone, b.fname, b.lname
    FROM loan_info l
    JOIN borrowers b ON b.id = l.borrower
    WHERE l.status NOT IN ('P', 'Paid', 'paid')
    AND l.baccount NOT LIKE 'TEST-%%'
    AND l.baccount NOT LIKE 'LN-%%'
    ORDER BY l.id
""")
print(f"   Found {len(old_active)} active loans in old system")

# Get product id
prod = q("SELECT id FROM loan_products WHERE is_active=1 LIMIT 1")
product_id = prod[0][0] if prod else None

synced = skipped = 0
for row in old_active:
    old_id, baccount, old_bor_id, amount, balance, pay_date, phone, fname, lname = row

    # Check if already in Django
    if q("SELECT 1 FROM loans WHERE loan_number=%s", [baccount]):
        skipped += 1
        continue

    # Find the Django user for this borrower
    phone = str(phone or '').strip()
    norm  = ('+254'+phone[1:]) if phone.startswith('0') else \
            ('+'+phone) if phone.startswith('254') else phone

    user = q("SELECT id FROM users WHERE phone_number IN (%s,%s) AND role='borrower' LIMIT 1",
             [norm, phone])
    if not user:
        continue

    bor_hex = str(user[0][0]).replace('-','')
    amount  = float(amount or 0)
    due     = pay_date if pay_date else (date.today() + timedelta(days=30))

    app_id  = uuid.uuid4().hex
    loan_id = uuid.uuid4().hex

    r1 = run("""INSERT INTO loan_applications
        (id,application_number,requested_amount,requested_duration,purpose,
         status,submitted_at,auto_approved,borrower_id,loan_product_id,repayment_method)
        VALUES (%s,%s,%s,30,'Business working capital','approved',NOW(),0,%s,%s,'mpesa')""",
        [app_id, f"APP-{app_id[:12].upper()}", amount, bor_hex, product_id])

    r2 = run("""INSERT INTO loans
        (id,loan_number,principal_amount,interest_amount,processing_fee,total_amount,
         disbursement_date,due_date,duration_days,status,is_rolled_over,is_deleted,
         amount_paid,registration_fee,borrower_id,application_id,created_at,updated_at)
        VALUES (%s,%s,%s,0,0,%s,CURDATE(),%s,'active',0,0,0,0,%s,%s,NOW(),NOW())""",
        [loan_id, baccount, amount, amount, due, bor_hex, app_id])

    if r1 is True and r2 is True:
        synced += 1
    # else silently skip

print(f"   Created: {synced} | Already existed: {skipped}")

# ── FIX 4: Delete today's bad repayments (wrong loan, wrong receipt) ──────────
print("\n── FIX 4: Clean up today's bad repayments ───────────────────────────────")
today_bad = q("""
    SELECT r.id, r.receipt_number, r.mpesa_transaction_id, r.loan_id, r.amount
    FROM repayments r
    WHERE DATE(r.created_at) = CURDATE()
    AND r.loan_id IN (SELECT id FROM loans WHERE loan_number LIKE 'TEST-%%')
""")
print(f"   Found {len(today_bad)} bad repayments linked to TEST-* loans today")
for rep in today_bad:
    run("DELETE FROM receipts WHERE repayment_id=%s", [str(rep[0]).replace('-','')])
    run("DELETE FROM repayments WHERE id=%s", [str(rep[0]).replace('-','')])
    print(f"   Deleted: {rep[2]} → {rep[1]}")

# ── FIX 5: Re-process today's real IPN payments against correct loans ──────────
print("\n── FIX 5: Re-process real IPN payments ──────────────────────────────────")

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 IPN logs
today_ipns = q("""
    SELECT raw_data FROM sasapay_ipn_logs
    WHERE DATE(created_at) = CURDATE()
    ORDER BY created_at ASC
""")

seen_trans = set()
for row in today_ipns:
    data     = row[0] if isinstance(row[0], dict) else json.loads(row[0])
    trans_id = data.get('TransID', '')
    amount   = data.get('TransAmount', '0')
    bill_ref = data.get('BillRefNumber', '')

    if trans_id in seen_trans:
        continue
    seen_trans.add(trans_id)

    # Skip if already properly processed (not linked to a TEST- loan)
    existing = Repayment.objects.filter(mpesa_transaction_id=trans_id).exclude(
        loan__loan_number__startswith='TEST-'
    )
    if existing.exists():
        rep = existing.first()
        print(f"   ✅ Already OK: {trans_id} → {rep.receipt_number} ({rep.loan.loan_number})")
        continue

    # Delete any bad repayment for this trans_id (linked to TEST- loan)
    bad = Repayment.objects.filter(mpesa_transaction_id=trans_id)
    if bad.exists():
        bad.delete()

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

# ── FINAL SUMMARY ─────────────────────────────────────────────────────────────
print("\n── Final summary ────────────────────────────────────────────────────────")
total = q("SELECT COUNT(*) FROM repayments")[0][0]
today_count = q("SELECT COUNT(*) FROM repayments WHERE DATE(created_at)=CURDATE()")[0][0]
active_loans = q("SELECT COUNT(*) FROM loans WHERE status='active' AND loan_number NOT LIKE 'TEST-%%'")[0][0]
print(f"   Total repayments      : {total}")
print(f"   Today's repayments    : {today_count}")
print(f"   Active real loans     : {active_loans}")

cur.close(); conn.close()
print("\nDone. Refresh https://grazuri.uzuriapps.xyz/loans/repayments/ to see today's payments.")