"""
Reads ALL payment SMS logs and creates repayment records for any that
don't already have one. Runs via direct SQL — no Django ORM, no atomic
blocks, no receipt generator crashes.

Safe to run multiple times — skips already-processed payments.

Upload to /home/xygbfpsg/phingrazuri/ and run: python sync_sms_to_repayments.py
Also add this as a cron job to run every 5 minutes:
  */5 * * * * /home/xygbfpsg/virtualenv/phingrazuri/3.13/bin/python /home/xygbfpsg/phingrazuri/sync_sms_to_repayments.py >> /home/xygbfpsg/phingrazuri/sync.log 2>&1
"""
import sys, re, pymysql, uuid
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()
except Exception as e:
    print(f"FAIL: {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)

def to_uuid(h):
    h = str(h).replace('-','')
    return f"{h[0:8]}-{h[8:12]}-{h[12:16]}-{h[16:20]}-{h[20:32]}" if len(h)==32 else h

# ── Get next available receipt number ────────────────────────────────────────
def next_receipt_num():
    rows = q("""
        SELECT CAST(SUBSTRING(receipt_number,5) AS UNSIGNED) as n
        FROM repayments
        WHERE receipt_number REGEXP '^RCP-[0-9]+$'
        ORDER BY n DESC LIMIT 1
    """)
    max_n = rows[0][0] if rows else 2446
    rows2 = q("""
        SELECT CAST(SUBSTRING(receipt_number,5) AS UNSIGNED) as n
        FROM receipts
        WHERE receipt_number REGEXP '^RCP-[0-9]+$'
        ORDER BY n DESC LIMIT 1
    """)
    max_n2 = rows2[0][0] if rows2 else 0
    n = max(max_n, max_n2) + 1
    # Skip blacklisted
    while n in (1618, 2440):
        n += 1
    return f"RCP-{n:06d}"

# ── Parse payment SMS messages ────────────────────────────────────────────────
# Pattern: "<ref> Confirmed. KES <amount> paid to HAVEN GRAZURI by <name>
#           on <date> HRS for loan No <loan_number>. New Loan Bal: KES <bal>"
PATTERN = re.compile(
    r'(\w+)\s+Confirmed\.\s+KES\s+([\d,\.]+)\s+paid to HAVEN GRAZURI by\s+(.+?)\s+on\s+'
    r'(\d{2}/\d{2}/\d{4}\s+\d{2}:\d{2}:\d{2})\s+HRS\s+for loan No\s+(\S+)\.',
    re.IGNORECASE
)

# ── Get all payment SMS logs ──────────────────────────────────────────────────
sms_rows = q("""
    SELECT id, message, recipients, created_at
    FROM sms_logs
    WHERE sms_type = 'payment_confirmation'
    AND status = 'sent'
    ORDER BY created_at ASC
""")
print(f"Found {len(sms_rows)} sent payment SMS logs")

created = skipped = failed = 0

for sms_id, message, recipients, sms_created in sms_rows:
    m = PATTERN.search(message)
    if not m:
        continue

    mpesa_ref  = m.group(1)   # e.g. UF10169ZK7
    amount_str = m.group(2).replace(',','')
    borrower_name = m.group(3).strip()
    pay_date_str  = m.group(4)
    loan_number   = m.group(5)

    try:
        amount   = float(amount_str)
        pay_date = datetime.strptime(pay_date_str, '%d/%m/%Y %H:%M:%S')
    except:
        continue

    # Extract customer phone from recipients (last number = customer)
    phones = [p.strip() for p in recipients.split(',') if p.strip()]
    customer_phone = phones[-1] if phones else None

    # ── Check if repayment already exists ────────────────────────────────────
    existing = q("SELECT id FROM repayments WHERE mpesa_transaction_id=%s", [mpesa_ref])
    if existing:
        skipped += 1
        continue

    # ── Find the loan ─────────────────────────────────────────────────────────
    loan = q("SELECT id, borrower_id, total_amount FROM loans WHERE loan_number=%s LIMIT 1",
             [loan_number])
    if not loan:
        print(f"  SKIP {mpesa_ref}: loan {loan_number} not found")
        failed += 1
        continue

    loan_id     = to_uuid(str(loan[0][0]))
    borrower_id = to_uuid(str(loan[0][1])) if loan[0][1] else None

    # ── Get next receipt number ───────────────────────────────────────────────
    receipt_num = next_receipt_num()
    # Ensure truly unique
    while (q("SELECT 1 FROM repayments WHERE receipt_number=%s", [receipt_num]) or
           q("SELECT 1 FROM receipts WHERE receipt_number=%s", [receipt_num])):
        receipt_num = next_receipt_num()

    # ── Insert repayment ──────────────────────────────────────────────────────
    rep_id = str(uuid.uuid4())
    r1 = run("""
        INSERT INTO repayments
            (id, loan_id, amount, payment_method, payment_source,
             mpesa_transaction_id, mpesa_phone_number,
             receipt_number, payment_date, created_at)
        VALUES (%s, %s, %s, 'mpesa', 'automatic', %s, %s, %s, %s, %s)
    """, [rep_id, loan_id, amount, mpesa_ref,
          (customer_phone or '')[:17], receipt_num, pay_date, sms_created])

    if r1 is not True:
        print(f"  FAIL {mpesa_ref}: {r1}")
        failed += 1
        continue

    # ── Insert receipt ────────────────────────────────────────────────────────
    rec_id = str(uuid.uuid4())
    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, %s)
    """, [rec_id, receipt_num, rep_id, loan_id, amount, pay_date, sms_created])

    # ── Update loan amount_paid cache ─────────────────────────────────────────
    run("""
        UPDATE loans SET
            amount_paid = COALESCE((
                SELECT SUM(amount) FROM repayments WHERE loan_id=%s
            ), 0),
            last_payment_date = %s,
            updated_at = NOW()
        WHERE id = %s
    """, [loan_id, pay_date, loan_id])

    created += 1
    print(f"  DONE {mpesa_ref} -> {receipt_num} | {loan_number} | KES {amount}")

# ── Summary ───────────────────────────────────────────────────────────────────
print(f"\nCreated: {created} | Skipped (exist): {skipped} | Failed: {failed}")
total = q("SELECT COUNT(*) FROM repayments")[0][0]
today = q("SELECT COUNT(*) FROM repayments WHERE DATE(created_at)=CURDATE()")[0][0]
print(f"Total repayments: {total} | Today: {today}")

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