#!/usr/bin/env python
"""
Compare IPNs received vs manual payments to identify missing IPNs
This will help identify the pattern of why some SasaPay payments don't send IPNs
"""

import pymysql
from datetime import datetime, timedelta
import json

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

def analyze_missing_ipns():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    seven_days = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
    
    print("\n" + "="*80)
    print("MISSING IPN PATTERN ANALYSIS")
    print("="*80)
    
    # Get all payments (manual + auto) with borrower details
    cursor.execute("""
        SELECT 
            r.receipt_number,
            r.amount,
            r.payment_date,
            r.payment_source,
            r.mpesa_transaction_id,
            r.mpesa_phone_number,
            l.loan_number,
            CONCAT(u.first_name, ' ', u.last_name) as borrower_name,
            u.phone_number as borrower_phone,
            u.id_number
        FROM repayments r
        JOIN loans l ON r.loan_id = l.id
        JOIN users u ON l.borrower_id = u.id
        WHERE r.payment_date >= %s
        ORDER BY r.payment_date DESC
    """, (seven_days,))
    
    all_payments = cursor.fetchall()
    
    manual_payments = [p for p in all_payments if p['payment_source'] == 'manual']
    auto_payments = [p for p in all_payments if p['payment_source'] == 'automatic']
    
    print(f"\n📊 Payment Summary (Last 7 days):")
    print(f"   Total Payments: {len(all_payments)}")
    print(f"   ✅ Automatic (IPN): {len(auto_payments)}")
    print(f"   ⚠️  Manual (No IPN): {len(manual_payments)}")
    
    if manual_payments:
        print(f"\n{'='*80}")
        print(f"⚠️  {len(manual_payments)} PAYMENTS MISSING IPNs")
        print("="*80)
        print("\nThese payments went through SasaPay but NO IPN was sent:\n")
        
        # Analyze patterns
        phone_formats = {}
        loan_prefixes = {}
        amounts = []
        times = []
        
        for i, pay in enumerate(manual_payments, 1):
            print(f"{i}. {pay['payment_date']} | KES {pay['amount']}")
            print(f"   Borrower: {pay['borrower_name']}")
            print(f"   Phone: {pay['borrower_phone']}")
            print(f"   Loan: {pay['loan_number']}")
            
            # Collect patterns
            phone = pay['borrower_phone']
            if phone:
                if phone.startswith('+254'):
                    phone_formats['+254 format'] = phone_formats.get('+254 format', 0) + 1
                elif phone.startswith('0'):
                    phone_formats['0 format'] = phone_formats.get('0 format', 0) + 1
                elif phone.startswith('7'):
                    phone_formats['7 format'] = phone_formats.get('7 format', 0) + 1
                else:
                    phone_formats['other format'] = phone_formats.get('other format', 0) + 1
            
            # Loan number pattern
            loan_prefix = pay['loan_number'].split('/')[0] if '/' in pay['loan_number'] else pay['loan_number'].split('-')[0]
            loan_prefixes[loan_prefix] = loan_prefixes.get(loan_prefix, 0) + 1
            
            amounts.append(float(pay['amount']))
            times.append(pay['payment_date'].hour)
            
            # Check if IPN exists in unknown_payments
            cursor.execute("""
                SELECT id, amount, paid_by, notes
                FROM sasapay_unknown_payments
                WHERE ABS(amount - %s) < 1
                AND created_at BETWEEN DATE_SUB(%s, INTERVAL 5 MINUTE) AND DATE_ADD(%s, INTERVAL 5 MINUTE)
                LIMIT 1
            """, (pay['amount'], pay['payment_date'], pay['payment_date']))
            
            unknown = cursor.fetchone()
            if unknown:
                print(f"   🔍 Found in unknown_payments: {unknown['paid_by']}")
                print(f"      Note: {unknown['notes']}")
            else:
                print(f"   ❌ No IPN received at all (not even in unknown_payments)")
            
            print()
        
        # Pattern analysis
        print(f"\n{'='*80}")
        print("PATTERN ANALYSIS")
        print("="*80)
        
        print(f"\n📱 Phone Number Formats:")
        for fmt, count in phone_formats.items():
            print(f"   {fmt}: {count}")
        
        print(f"\n🏷️  Loan Number Prefixes:")
        for prefix, count in sorted(loan_prefixes.items(), key=lambda x: x[1], reverse=True):
            print(f"   {prefix}: {count}")
        
        print(f"\n💰 Amount Range:")
        print(f"   Min: KES {min(amounts)}")
        print(f"   Max: KES {max(amounts)}")
        print(f"   Avg: KES {sum(amounts)/len(amounts):.2f}")
        
        print(f"\n🕐 Time of Day:")
        from collections import Counter
        time_dist = Counter(times)
        for hour, count in sorted(time_dist.items()):
            print(f"   {hour:02d}:00 - {hour:02d}:59: {count} payments")
    
    # Compare with successful IPNs
    print(f"\n{'='*80}")
    print("SUCCESSFUL IPN PATTERNS (for comparison)")
    print("="*80)
    
    if auto_payments:
        print(f"\nThese {len(auto_payments)} payments DID receive IPNs:\n")
        
        auto_phone_formats = {}
        auto_loan_prefixes = {}
        
        for pay in auto_payments[:5]:  # Show first 5
            print(f"   ✅ {pay['borrower_name']}")
            print(f"      Phone: {pay['borrower_phone']}")
            print(f"      Loan: {pay['loan_number']}")
            print(f"      Amount: KES {pay['amount']}")
            print()
            
            phone = pay['borrower_phone']
            if phone:
                if phone.startswith('+254'):
                    auto_phone_formats['+254 format'] = auto_phone_formats.get('+254 format', 0) + 1
                elif phone.startswith('0'):
                    auto_phone_formats['0 format'] = auto_phone_formats.get('0 format', 0) + 1
            
            loan_prefix = pay['loan_number'].split('/')[0] if '/' in pay['loan_number'] else pay['loan_number'].split('-')[0]
            auto_loan_prefixes[loan_prefix] = auto_loan_prefixes.get(loan_prefix, 0) + 1
        
        print(f"\n📱 Phone formats in successful IPNs:")
        for fmt, count in auto_phone_formats.items():
            print(f"   {fmt}: {count}")
    
    # Check SasaPay IPN logs for any clues
    print(f"\n{'='*80}")
    print("IPN WEBHOOK LOGS")
    print("="*80)
    
    cursor.execute("""
        SELECT 
            DATE(created_at) as date,
            COUNT(*) as total,
            SUM(CASE WHEN processing_status = 'processed' THEN 1 ELSE 0 END) as processed,
            SUM(CASE WHEN processing_status != 'processed' THEN 1 ELSE 0 END) as failed
        FROM sasapay_ipn_logs
        WHERE created_at >= %s
        GROUP BY DATE(created_at)
        ORDER BY date DESC
    """, (seven_days,))
    
    daily_ipns = cursor.fetchall()
    
    print("\nIPNs received per day:")
    for day in daily_ipns:
        print(f"   {day['date']}: {day['total']} IPNs ({day['processed']} processed, {day['failed']} failed)")
    
    # Get total payments per day
    cursor.execute("""
        SELECT 
            DATE(payment_date) as date,
            COUNT(*) as total,
            SUM(CASE WHEN payment_source = 'automatic' THEN 1 ELSE 0 END) as automatic,
            SUM(CASE WHEN payment_source = 'manual' THEN 1 ELSE 0 END) as manual
        FROM repayments
        WHERE payment_date >= %s
        GROUP BY DATE(payment_date)
        ORDER BY date DESC
    """, (seven_days,))
    
    daily_payments = cursor.fetchall()
    
    print("\nPayments recorded per day:")
    for day in daily_payments:
        print(f"   {day['date']}: {day['total']} payments ({day['automatic']} auto, {day['manual']} manual)")
    
    print(f"\n{'='*80}")
    print("DIAGNOSIS")
    print("="*80)
    
    if manual_payments:
        print(f"""
⚠️  ISSUE CONFIRMED: SasaPay is NOT sending IPNs for all payments

Evidence:
- {len(manual_payments)} payments visible in SasaPay dashboard
- These payments manually entered (no M-Pesa ref stored)
- NO corresponding IPNs in sasapay_ipn_logs table
- IPNs ARE working for some payments ({len(auto_payments)} successful)

This is a SASAPAY CONFIGURATION issue:

POSSIBLE CAUSES:
1. IPN URL configured but not for ALL transaction types
2. Some payments going through different merchant code/channel
3. IPN delivery failures (SasaPay trying but failing to reach webhook)
4. Rate limiting or throttling on SasaPay side
5. IPN only configured for certain payment methods

IMMEDIATE ACTIONS:
1. Contact SasaPay Support with this data
2. Ask them to check IPN delivery logs for missing transactions
3. Verify IPN URL is configured for ALL transaction types
4. Request IPN retry for failed deliveries

TEMPORARY WORKAROUND:
- Check SasaPay dashboard regularly
- Manually enter payments not auto-recorded
- Monitor /payments/sasapay/ipn-gaps/ for patterns
        """)
    else:
        print("\n✅ All payments receiving IPNs - system working correctly")
    
    print("\n" + "="*80 + "\n")
    
    cursor.close()
    conn.close()

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