#!/usr/bin/env python
"""
Check which IPNs failed and why - comparing to your manual payment list
"""

import pymysql
from datetime import datetime, timedelta
import json

# Production database credentials
DB_CONFIG = {
    'host': 'localhost',
    'user': 'xygbfpsg_graz',
    'password': 'j.ez-xy6##y.rllB',
    'database': 'xygbfpsg_loans',
    'charset': 'utf8mb4',
}

def check_failed_ipns():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # Get last 7 days of data
    seven_days = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
    
    print("\n" + "="*80)
    print("FAILED IPN ANALYSIS - Last 7 Days")
    print("="*80)
    
    # Total IPNs by status
    cursor.execute("""
        SELECT processing_status, COUNT(*) as count 
        FROM sasapay_ipn_logs 
        WHERE created_at >= %s
        GROUP BY processing_status
        ORDER BY count DESC
    """, (seven_days,))
    
    print("\n📊 IPN Status Breakdown:")
    statuses = cursor.fetchall()
    total_ipns = sum(s['count'] for s in statuses)
    for s in statuses:
        pct = (s['count'] * 100) // total_ipns if total_ipns else 0
        print(f"   {s['processing_status'].upper()}: {s['count']} ({pct}%)")
    
    # Get failed IPNs (not processed or duplicate)
    cursor.execute("""
        SELECT 
            id, trans_id, created_at, processing_status, processing_note, raw_data
        FROM sasapay_ipn_logs 
        WHERE created_at >= %s
        AND processing_status NOT IN ('processed', 'duplicate')
        ORDER BY created_at DESC
    """, (seven_days,))
    
    failed_ipns = cursor.fetchall()
    
    if not failed_ipns:
        print("\n✅ No failed IPNs found!")
        print("   All IPNs processed successfully")
    else:
        print(f"\n❌ FAILED IPNs: {len(failed_ipns)}")
        print("="*80)
        
        for ipn in failed_ipns:
            data = json.loads(ipn['raw_data']) if isinstance(ipn['raw_data'], str) else ipn['raw_data']
            
            trans_id = ipn['trans_id'] or 'N/A'
            amount = data.get('TransAmount', 'N/A')
            bill_ref = data.get('BillRefNumber', 'N/A')
            first_name = data.get('FirstName', '')
            last_name = data.get('LastName', '')
            msisdn = data.get('MSISDN', '')
            mpesa_ref = data.get('ThirdPartyTransID', '')
            
            print(f"\n{'='*80}")
            print(f"⚠️  TransID: {trans_id}")
            print(f"   M-Pesa Ref: {mpesa_ref}")
            print(f"   Amount: KES {amount}")
            print(f"   Time: {ipn['created_at']}")
            print(f"   Status: {ipn['processing_status']}")
            print(f"   BillRef: {bill_ref}")
            print(f"   Name: {first_name} {last_name}")
            print(f"   MSISDN: {msisdn}")
            print(f"   Reason: {ipn['processing_note']}")
            
            # Try to find matching borrower
            print(f"\n   🔍 Checking for borrower match...")
            
            # Try various phone formats
            phone_checks = []
            if bill_ref and len(bill_ref) > 5:
                phone_checks.extend([
                    bill_ref,
                    f"+254{bill_ref[1:]}" if bill_ref.startswith('0') else None,
                    f"+254{bill_ref}" if bill_ref.startswith('7') else None,
                    f"0{bill_ref[4:]}" if bill_ref.startswith('+254') else None,
                    f"0{bill_ref[3:]}" if bill_ref.startswith('254') else None,
                ])
            
            phone_checks = [p for p in phone_checks if p]
            
            found_user = None
            for phone in phone_checks:
                cursor.execute("""
                    SELECT id, first_name, last_name, phone_number, id_number
                    FROM users 
                    WHERE phone_number = %s AND role = 'borrower' AND is_active = 1
                    LIMIT 1
                """, (phone,))
                
                user = cursor.fetchone()
                if user:
                    found_user = user
                    print(f"   ✓ Found by phone '{phone}': {user['first_name']} {user['last_name']}")
                    
                    # Check for active loan
                    cursor.execute("""
                        SELECT loan_number, status, principal_amount, amount_paid
                        FROM loans
                        WHERE borrower_id = %s AND status = 'active'
                        ORDER BY id DESC
                        LIMIT 1
                    """, (user['id'],))
                    
                    loan = cursor.fetchone()
                    if loan:
                        outstanding = float(loan['principal_amount']) - float(loan['amount_paid'])
                        print(f"   ✓ Active loan: {loan['loan_number']}")
                        print(f"   ✓ Outstanding: KES {outstanding}")
                    else:
                        print(f"   ✗ No active loan found")
                    break
            
            # Try by ID number
            if not found_user and bill_ref:
                cursor.execute("""
                    SELECT id, first_name, last_name, phone_number, id_number
                    FROM users 
                    WHERE id_number = %s AND role = 'borrower' AND is_active = 1
                    LIMIT 1
                """, (bill_ref,))
                
                user = cursor.fetchone()
                if user:
                    found_user = user
                    print(f"   ✓ Found by ID number: {user['first_name']} {user['last_name']}")
                    print(f"   → Phone in system: {user['phone_number']}")
            
            # Try by loan number
            if not found_user and bill_ref:
                cursor.execute("""
                    SELECT l.loan_number, l.status, u.first_name, u.last_name, u.phone_number
                    FROM loans l
                    JOIN users u ON l.borrower_id = u.id
                    WHERE l.loan_number = %s
                    LIMIT 1
                """, (bill_ref,))
                
                loan_match = cursor.fetchone()
                if loan_match:
                    print(f"   ✓ Found by loan number: {loan_match['first_name']} {loan_match['last_name']}")
                    print(f"   → Phone in system: {loan_match['phone_number']}")
                    print(f"   → Loan status: {loan_match['status']}")
            
            if not found_user and not loan_match:
                print(f"   ✗ No borrower found in system!")
                print(f"   → BillRef '{bill_ref}' doesn't match any phone/ID/loan")
                print(f"\n   💡 SOLUTION:")
                print(f"      1. Check if borrower exists with different phone format")
                print(f"      2. Or add borrower with phone: {bill_ref}")
    
    # Show recent manual payments to compare
    print(f"\n\n{'='*80}")
    print("📋 RECENT MANUAL PAYMENTS (Last 7 days)")
    print("="*80)
    
    cursor.execute("""
        SELECT 
            r.receipt_number, r.amount, r.payment_date, r.mpesa_transaction_id,
            l.loan_number, 
            CONCAT(u.first_name, ' ', u.last_name) as borrower_name,
            u.phone_number
        FROM repayments r
        JOIN loans l ON r.loan_id = l.id
        JOIN users u ON l.borrower_id = u.id
        WHERE r.created_at >= %s
        AND r.payment_source = 'manual'
        ORDER BY r.payment_date DESC
    """, (seven_days,))
    
    manual_payments = cursor.fetchall()
    
    if manual_payments:
        print(f"\nFound {len(manual_payments)} manual payments:\n")
        for pay in manual_payments:
            print(f"   {pay['payment_date']} | {pay['receipt_number']}")
            print(f"   Borrower: {pay['borrower_name']} ({pay['phone_number']})")
            print(f"   Loan: {pay['loan_number']}")
            print(f"   Amount: KES {pay['amount']}")
            print(f"   M-Pesa Ref: {pay['mpesa_transaction_id'] or 'N/A'}")
            
            # Check if there's a matching failed IPN
            if pay['mpesa_transaction_id']:
                cursor.execute("""
                    SELECT processing_status, processing_note
                    FROM sasapay_ipn_logs
                    WHERE trans_id = %s OR raw_data LIKE %s
                    LIMIT 1
                """, (pay['mpesa_transaction_id'], f'%{pay["mpesa_transaction_id"]}%'))
                
                matching_ipn = cursor.fetchone()
                if matching_ipn:
                    print(f"   🔍 IPN Status: {matching_ipn['processing_status']}")
                    print(f"   🔍 IPN Note: {matching_ipn['processing_note']}")
                else:
                    print(f"   ⚠️  No IPN found for this transaction!")
            print()
    else:
        print("\n   No manual payments in last 7 days")
    
    print("\n" + "="*80)
    print("SUMMARY & RECOMMENDATIONS")
    print("="*80)
    
    if failed_ipns:
        # Analyze failure patterns
        no_match = sum(1 for ipn in failed_ipns if 'no_match' in ipn['processing_status'])
        no_loan = sum(1 for ipn in failed_ipns if 'no_loan' in ipn['processing_status'])
        no_balance = sum(1 for ipn in failed_ipns if 'no_balance' in ipn['processing_status'])
        
        print(f"\n📊 Failure Pattern:")
        if no_match > 0:
            print(f"   ⚠️  {no_match} failures: Borrower not found (phone/ID/loan mismatch)")
        if no_loan > 0:
            print(f"   ℹ️  {no_loan} failures: No active loan (likely processing fees)")
        if no_balance > 0:
            print(f"   ℹ️  {no_balance} failures: Loan fully paid")
        
        print(f"\n🔧 ACTION ITEMS:")
        if no_match > 0:
            print(f"""
   1. PHONE FORMAT STANDARDIZATION:
      - Review borrower phone numbers in system
      - Ensure all use format: +254XXXXXXXXX
      - BillRef must match exactly
      
   2. INSTRUCT CUSTOMERS:
      - When paying via M-Pesa
      - Enter Account/BillRef as their phone number: +254XXXXXXXXX
      - OR their loan number (e.g., BSH/202603/00178)
      - OR their ID number
            """)
        
        if no_loan > 0:
            print(f"""
   3. PROCESSING FEES:
      - {no_loan} payments are likely processing fees (before loan approval)
      - These are EXPECTED to fail with 'no_loan' status
      - Check: https://grazuri.uzuriapps.xyz/payments/sasapay/unknown-payments/
      - Manually assign to borrowers after loan approval
            """)
    else:
        print("\n✅ No failed IPNs - system working correctly!")
    
    print("\n" + "="*80 + "\n")
    
    cursor.close()
    conn.close()

if __name__ == '__main__':
    try:
        check_failed_ipns()
    except Exception as e:
        print(f"\n❌ Error: {e}\n")
        import traceback
        traceback.print_exc()