"""
Final fixes:
1. Fix RCP-001618 duplicate — insert missing receipt records so the generator skips them
2. Install africastalking in the correct virtualenv
3. Re-process the 4 real payments so they appear in the system with correct receipts

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

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: Sync receipts table with all existing repayment receipt numbers ────
print("\n── FIX 1: Sync receipts table ───────────────────────────────────────────")

# Get all receipt numbers already in repayments table
repayment_receipts = q("""
    SELECT r.id, r.receipt_number, r.loan_id, r.amount, r.payment_date
    FROM repayments r
    WHERE r.receipt_number IS NOT NULL AND r.receipt_number != ''
""")
print(f"   Found {len(repayment_receipts)} repayments with receipt numbers")

# Get receipt numbers already in receipts table
existing_in_receipts = {row[0] for row in q("SELECT receipt_number FROM receipts")}
print(f"   Already in receipts table: {len(existing_in_receipts)}")

# Insert missing ones
inserted = 0
for rep_id, receipt_num, loan_id, amount, pay_date in repayment_receipts:
    if receipt_num in existing_in_receipts:
        continue
    new_id = uuid.uuid4().hex
    pay_date_val = pay_date if pay_date else datetime.now()
    r = run("""
        INSERT INTO receipts
            (id, receipt_number, repayment_id, loan_id, amount_paid,
             payment_method, payment_date, previous_balance, new_balance, created_at)
        VALUES (%s, %s, %s, %s, %s, 'mpesa', %s, 0.00, 0.00, NOW())
    """, [new_id, receipt_num, str(rep_id).replace('-',''),
          str(loan_id).replace('-','') if loan_id else None,
          float(amount), pay_date_val])
    if r is True:
        inserted += 1
    # else: silently skip (might be FK issue on loan_id, not critical)

print(f"   ✅ Inserted {inserted} missing receipt records")
print(f"   Total in receipts table now: {q('SELECT COUNT(*) FROM receipts')[0][0]}")

# ── FIX 2: Install africastalking ─────────────────────────────────────────────
print("\n── FIX 2: Install africastalking ────────────────────────────────────────")
try:
    import africastalking
    print(f"   ✅ africastalking already installed")
except ImportError:
    # Try the virtualenv pip directly
    pip_paths = [
        '/home/xygbfpsg/virtualenv/phingrazuri/3.13/bin/pip',
        '/home/xygbfpsg/virtualenv/phingrazuri/3.13/bin/pip3',
        sys.executable.replace('python', 'pip').replace('python3', 'pip3'),
    ]
    installed = False
    for pip in pip_paths:
        if os.path.exists(pip):
            print(f"   Trying: {pip} install africastalking")
            result = subprocess.run(
                [pip, 'install', 'africastalking'],
                capture_output=True, text=True
            )
            if result.returncode == 0:
                print(f"   ✅ africastalking installed successfully")
                installed = True
                break
            else:
                print(f"   ⚠️  {result.stderr[:100]}")

    if not installed:
        # Try with sys.executable -m pip
        result = subprocess.run(
            [sys.executable, '-m', 'pip', 'install', 'africastalking'],
            capture_output=True, text=True
        )
        if result.returncode == 0:
            print(f"   ✅ africastalking installed via {sys.executable}")
        else:
            print(f"   ❌ Could not install: {result.stderr[:200]}")
            print(f"   Run manually: {sys.executable} -m pip install africastalking")

# ── FIX 3: Delete duplicate RCP-002440 repayments and re-process correctly ───
print("\n── FIX 3: Clean up duplicate repayments ─────────────────────────────────")

# The 4 payments all got receipt RCP-002440 — that's wrong, they should each get unique receipts
# Find and remove the duplicates (keep only the first one)
dupes = q("""
    SELECT id, receipt_number, mpesa_transaction_id, amount, created_at
    FROM repayments
    WHERE receipt_number = 'RCP-002440'
    ORDER BY created_at ASC
""")
print(f"   Found {len(dupes)} repayments with RCP-002440")

if len(dupes) > 1:
    # Keep the first, delete the rest
    keep_id = dupes[0][0]
    for rep in dupes[1:]:
        r = run("DELETE FROM repayments WHERE id=%s", [str(rep[0]).replace('-','')])
        r2 = run("DELETE FROM receipts WHERE receipt_number='RCP-002440' AND repayment_id=%s",
                 [str(rep[0]).replace('-','')])
        print(f"   Deleted duplicate: {rep[2]} (id={rep[0]})")
    print(f"   Kept: {dupes[0][2]} (id={keep_id})")

# ── FIX 4: Re-process the real payments with correct receipt numbers ──────────
print("\n── FIX 4: Re-process real payments ──────────────────────────────────────")

# 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

# The 4 real IPN payloads to process
real_payments = q("""
    SELECT raw_data FROM sasapay_ipn_logs
    WHERE created_at >= '2026-06-01 11:00:00'
    ORDER BY created_at ASC
""")

for row in real_payments:
    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', '')

    # Skip if already properly processed (has unique receipt, not RCP-002440)
    existing = Repayment.objects.filter(mpesa_transaction_id=trans_id).exclude(receipt_number='RCP-002440')
    if existing.exists():
        rep = existing.first()
        print(f"   ✅ Already processed: {trans_id} → {rep.receipt_number}")
        continue

    # Delete any RCP-002440 duplicate for this trans_id
    bad = Repayment.objects.filter(mpesa_transaction_id=trans_id, receipt_number='RCP-002440')
    if bad.exists():
        bad.delete()
        print(f"   Deleted bad duplicate for {trans_id}")

    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 STATE ───────────────────────────────────────────────────────────────
print("\n── Final state ──────────────────────────────────────────────────────────")
total = q("SELECT COUNT(*) FROM repayments")[0][0]
today = q("SELECT COUNT(*) FROM repayments WHERE DATE(created_at)=CURDATE()")[0][0]
sms   = q("SELECT status, COUNT(*) FROM sms_logs GROUP BY status")
print(f"   Total repayments : {total}")
print(f"   Today's repayments: {today}")
print(f"   SMS breakdown    : {dict(sms)}")

# Check africastalking now
try:
    import importlib
    importlib.invalidate_caches()
    import africastalking
    africastalking.initialize('Uzuriapps',
        'atsk_e810a145ccd3a1089769cd17887da70ae76124ff044fa0ab949429e6b388954e6db55009')
    print(f"   ✅ africastalking SMS ready — sender: HavGrazuri")
except Exception as e:
    print(f"   ⚠️  africastalking: {e}")
    print(f"   Run: {sys.executable} -m pip install africastalking")

cur.close(); conn.close()
print("\nDone.")