"""
Production IPN Diagnostic Script
Upload this to your production server and run it to diagnose why payments are manual.

Usage:
    python diagnose_production_ipn.py

This will check:
1. Whether IPNs are arriving from SasaPay
2. Why they're failing to create automatic payments
3. Phone number matching issues
4. Recent payment patterns
"""

import os
import sys
from pathlib import Path

# Set production database credentials BEFORE importing Django
os.environ['DB_NAME'] = 'xygbfpsg_loans'
os.environ['DB_USER'] = 'xygbfpsg_graz'
os.environ['DB_PASSWORD'] = 'j.ez-xy6##y.rllB'
os.environ['DB_HOST'] = 'localhost'
os.environ['DB_PORT'] = '3306'

# Prevent .env from overriding our credentials
os.environ['DJANGO_SETTINGS_MODULE'] = 'branch_system.settings'

# Now we can import Django
import django

# Setup Django with production database credentials
BASE_DIR = Path(__file__).resolve().parent
sys.path.insert(0, str(BASE_DIR))

# Monkey-patch load_dotenv to prevent override
import branch_system.settings as settings_module
original_load_dotenv = settings_module.load_dotenv
def no_override_dotenv(*args, **kwargs):
    kwargs['override'] = False
    return original_load_dotenv(*args, **kwargs)
settings_module.load_dotenv = no_override_dotenv

django.setup()

from django.utils import timezone
from datetime import timedelta
from payments.sasapay_models import SasaPayIPNLog
from loans.models import Repayment, Loan
from users.models import CustomUser
from django.db.models import Count, Q
from decimal import Decimal

def main():
    print("\n" + "="*80)
    print("PRODUCTION SASAPAY IPN DIAGNOSTIC")
    print("="*80)
    
    # Date range for analysis
    start_date = timezone.now() - timedelta(days=7)
    
    # 1. Check IPN logs
    print(f"\n1️⃣  IPN LOGS (Last 7 days)")
    print("-" * 80)
    
    ipns = SasaPayIPNLog.objects.filter(created_at__gte=start_date)
    total_ipns = ipns.count()
    
    print(f"Total IPNs received: {total_ipns}")
    
    if total_ipns == 0:
        print("\n❌ CRITICAL: NO IPNs received from SasaPay!")
        print("\nThis means SasaPay is NOT sending payment notifications.")
        print("\n🔧 SOLUTION:")
        print("   1. Login to SasaPay Merchant Portal: https://merchant.sasapay.co.ke/")
        print("   2. Navigate to: Settings > IPN Configuration")
        print("   3. Set IPN URL to: https://grazuri.uzuriapps.xyz/payments/sasapay/ipn/")
        print("   4. Save and verify the URL")
        print("   5. Make a test payment to confirm")
    else:
        # Status breakdown
        print("\nIPN Processing Status:")
        statuses = ipns.values('processing_status').annotate(count=Count('id')).order_by('-count')
        for s in statuses:
            status = s['processing_status'].upper()
            count = s['count']
            pct = (count * 100) // total_ipns
            print(f"   {status}: {count} ({pct}%)")
        
        # Show recent problematic IPNs
        failed_ipns = ipns.exclude(
            processing_status__in=['processed', 'duplicate']
        ).order_by('-created_at')[:15]
        
        if failed_ipns.exists():
            print(f"\n⚠️  FAILED IPNs (Last 15):")
            print("=" * 80)
            
            for ipn in failed_ipns:
                data = 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', '')
                
                print(f"\nTransID: {trans_id}")
                print(f"  Amount: KES {amount}")
                print(f"  BillRef: {bill_ref}")
                print(f"  Name: {first_name} {last_name}")
                print(f"  MSISDN: {msisdn}")
                print(f"  Status: {ipn.processing_status}")
                print(f"  Note: {ipn.processing_note}")
                print(f"  Time: {ipn.created_at.strftime('%Y-%m-%d %H:%M:%S')}")
                
                # Try to diagnose the specific issue
                if ipn.processing_status == 'no_match':
                    print(f"  🔍 Diagnosis: Checking borrower matching...")
                    
                    # Try to find borrower by various methods
                    found = []
                    
                    # By phone
                    phone_variants = [
                        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,
                    ]
                    
                    for variant in phone_variants:
                        if variant:
                            user = CustomUser.objects.filter(
                                phone_number=variant, 
                                role='borrower',
                                is_active=True
                            ).first()
                            if user:
                                found.append(f"Phone {variant} → {user.get_full_name()}")
                    
                    # By ID number
                    if bill_ref:
                        user = CustomUser.objects.filter(
                            id_number=bill_ref,
                            role='borrower',
                            is_active=True
                        ).first()
                        if user:
                            found.append(f"ID {bill_ref} → {user.get_full_name()}")
                    
                    # By loan number
                    if bill_ref:
                        loan = Loan.objects.filter(loan_number=bill_ref).first()
                        if loan:
                            found.append(f"Loan {bill_ref} → {loan.borrower.get_full_name()}")
                    
                    if found:
                        print(f"  💡 Found borrower via: {', '.join(found)}")
                        print(f"  ⚠️  But IPN still failed - check phone format in system")
                    else:
                        print(f"  ❌ No borrower found with BillRef: {bill_ref}")
                        print(f"  💡 Action: Check if this borrower exists in system")
    
    # 2. Payment source analysis
    print(f"\n\n2️⃣  PAYMENT SOURCE ANALYSIS (Last 7 days)")
    print("-" * 80)
    
    payments = Repayment.objects.filter(created_at__gte=start_date)
    total_payments = payments.count()
    auto_payments = payments.filter(payment_source='automatic').count()
    manual_payments = payments.filter(payment_source='manual').count()
    
    print(f"Total Payments: {total_payments}")
    print(f"  ⚡ Automatic (SasaPay IPN): {auto_payments} ({auto_payments*100//total_payments if total_payments else 0}%)")
    print(f"  ✋ Manual (Staff Entry): {manual_payments} ({manual_payments*100//total_payments if total_payments else 0}%)")
    
    if manual_payments > auto_payments and total_ipns > 0:
        print(f"\n⚠️  WARNING: More manual than automatic payments!")
        print(f"    IPNs are arriving ({total_ipns}) but failing to process.")
        print(f"    Check failed IPNs above for reasons.")
    elif manual_payments > auto_payments and total_ipns == 0:
        print(f"\n❌ CRITICAL: All payments are manual because NO IPNs are arriving!")
        print(f"    Staff must enter all payments manually.")
    
    # 3. Show recent manual payments with SMS messages
    print(f"\n\n3️⃣  RECENT MANUAL PAYMENTS (Last 10)")
    print("-" * 80)
    print("These should have been automatic if IPNs were working...\n")
    
    manual_recent = Repayment.objects.filter(
        payment_source='manual',
        created_at__gte=start_date
    ).select_related('loan__borrower').order_by('-created_at')[:10]
    
    for rep in manual_recent:
        print(f"\n{rep.created_at.strftime('%Y-%m-%d %H:%M')} | {rep.receipt_number}")
        print(f"  Borrower: {rep.loan.borrower.get_full_name()}")
        print(f"  Phone: {rep.loan.borrower.phone_number}")
        print(f"  Loan: {rep.loan.loan_number}")
        print(f"  Amount: KES {rep.amount}")
        print(f"  M-Pesa Ref: {rep.mpesa_transaction_id or 'N/A'}")
        
        # Check if there's a matching IPN for this payment
        if rep.mpesa_transaction_id:
            matching_ipn = SasaPayIPNLog.objects.filter(
                Q(trans_id=rep.mpesa_transaction_id) |
                Q(raw_data__ThirdPartyTransID=rep.mpesa_transaction_id)
            ).first()
            
            if matching_ipn:
                print(f"  🔍 Found IPN: Status={matching_ipn.processing_status}")
                print(f"     Reason: {matching_ipn.processing_note}")
            else:
                print(f"  ❌ No IPN found for this M-Pesa transaction!")
                print(f"     This confirms SasaPay is NOT sending IPNs")
    
    # 4. Recent automatic payments (proof system works when IPNs arrive)
    print(f"\n\n4️⃣  RECENT AUTOMATIC PAYMENTS (Last 10)")
    print("-" * 80)
    print("These succeeded because IPNs arrived and were processed...\n")
    
    auto_recent = Repayment.objects.filter(
        payment_source='automatic'
    ).select_related('loan__borrower').order_by('-created_at')[:10]
    
    if auto_recent.exists():
        for rep in auto_recent:
            print(f"\n{rep.created_at.strftime('%Y-%m-%d %H:%M')} | {rep.receipt_number}")
            print(f"  Borrower: {rep.loan.borrower.get_full_name()}")
            print(f"  Phone: {rep.loan.borrower.phone_number}")
            print(f"  Loan: {rep.loan.loan_number}")
            print(f"  Amount: KES {rep.amount}")
            print(f"  TransID: {rep.mpesa_transaction_id}")
    else:
        print("  No automatic payments found")
        print("  ⚠️  This suggests IPNs have NEVER worked or were recently broken")
    
    # 5. Configuration check
    print(f"\n\n5️⃣  CONFIGURATION CHECK")
    print("-" * 80)
    
    from django.conf import settings
    
    print(f"Site URL: {settings.SITE_URL}")
    print(f"IPN Endpoint: {settings.SITE_URL}/payments/sasapay/ipn/")
    print(f"Merchant Code: {settings.SASAPAY_MERCHANT_CODE}")
    print(f"Network Code: {settings.SASAPAY_NETWORK_CODE}")
    print(f"Client ID: {settings.SASAPAY_CLIENT_ID[:20]}...")
    
    # Test webhook accessibility
    print(f"\n🔍 Testing IPN webhook accessibility...")
    try:
        import requests
        url = f"{settings.SITE_URL}/payments/sasapay/ipn/"
        response = requests.get(url, timeout=10)
        print(f"  Status: {response.status_code}")
        if response.status_code == 200:
            print(f"  ✅ Webhook is accessible from internet")
        else:
            print(f"  ⚠️  Webhook returned non-200 status")
    except Exception as e:
        print(f"  ❌ Webhook NOT accessible: {e}")
    
    # 6. Summary and recommendations
    print(f"\n\n" + "="*80)
    print("SUMMARY & RECOMMENDATIONS")
    print("="*80)
    
    if total_ipns == 0:
        print("""
🚨 PRIMARY ISSUE: SasaPay is NOT sending IPNs to your server

ROOT CAUSE:
  - IPN URL not registered in SasaPay Merchant Portal
  - OR IPN URL is incorrect
  - OR SasaPay cannot reach your server

IMMEDIATE ACTIONS:
  1. Login to SasaPay Merchant Portal
  2. Go to Settings > IPN/Callback Configuration
  3. Set IPN URL: https://grazuri.uzuriapps.xyz/payments/sasapay/ipn/
  4. Save and verify
  5. Make a test payment
  6. Run this script again to verify IPNs arrive

TEMPORARY WORKAROUND:
  - Staff must continue entering payments manually
  - Use "Manual" payment source until IPN is fixed
        """)
    elif failed_ipns.exists():
        print(f"""
⚠️  ISSUE: IPNs are arriving but {failed_ipns.count()} failed to process

COMMON REASONS:
  1. Phone number mismatch (BillRef doesn't match borrower phone)
  2. Borrower not found in system
  3. No active loan for borrower
  4. Loan already fully paid

ACTIONS:
  1. Review failed IPNs above
  2. Check borrower phone numbers in system
  3. Ensure BillRef contains correct phone/ID/loan number
  4. Visit admin panel: {settings.SITE_URL}/payments/sasapay/ipn-gaps/
  5. Manually resolve unknown payments: {settings.SITE_URL}/payments/sasapay/unknown-payments/
  
PHONE FORMAT TIPS:
  - System tries: +254XXXXXXXXX, 07XXXXXXXX, 7XXXXXXXX
  - Ensure borrower phone in system matches BillRef format
  - BillRef should be borrower's phone, ID, or loan number
        """)
    else:
        print("""
✅ SYSTEM IS WORKING CORRECTLY!

  - IPNs are being received from SasaPay
  - Payments are being processed automatically
  - No issues detected

If you're still seeing manual payments:
  - Those are genuine manual entries by staff
  - Not related to IPN automation
  - Staff may be entering payments for other payment methods (cash, bank, etc.)
        """)
    
    print("\n" + "="*80)
    print(f"Report generated: {timezone.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("="*80 + "\n")

if __name__ == '__main__':
    main()