"""
Recover payments that were received via SasaPay IPN but never saved to the
repayments table (transaction rollback bug — now fixed).

Reads sasapay_ipn_logs, re-runs process_ipn_callback() for any IPN that
has no matching repayment, skipping duplicates safely.

Usage:
    python manage.py recover_ipn_payments           # dry run (no changes)
    python manage.py recover_ipn_payments --commit  # actually save
"""
from django.core.management.base import BaseCommand
from django.db import connection
import json


class Command(BaseCommand):
    help = 'Recover lost IPN payments by replaying sasapay_ipn_logs'

    def add_arguments(self, parser):
        parser.add_argument(
            '--commit',
            action='store_true',
            default=False,
            help='Actually save repayments. Without this flag, runs as dry-run.',
        )

    def handle(self, *args, **options):
        commit = options['commit']
        w = self.stdout.write
        c = connection.cursor()

        w('=' * 70)
        w(f'RECOVER IPN PAYMENTS  — {"COMMIT MODE" if commit else "DRY RUN (pass --commit to save)"}')
        w('=' * 70)

        # Get all IPN logs
        c.execute("""
            SELECT id, created_at, raw_data
            FROM sasapay_ipn_logs
            ORDER BY created_at ASC
        """)
        all_ipns = c.fetchall()
        w(f'\nTotal IPN logs: {len(all_ipns)}')

        recovered = 0
        skipped_duplicate = 0
        skipped_no_loan = 0
        errors = 0

        for ipn_id, created_at, raw_data in all_ipns:
            try:
                data = json.loads(raw_data) if isinstance(raw_data, str) else raw_data
            except Exception:
                w(f'  [SKIP] {ipn_id} — invalid JSON')
                continue

            trans_id = data.get('TransID', '')
            mpesa_ref = data.get('ThirdPartyTransID', '')
            bill_ref  = data.get('BillRefNumber', '')
            amount    = data.get('TransAmount', 0)
            fname     = data.get('FirstName', '')
            lname     = data.get('LastName', '')

            # Check if repayment already exists for this TransID
            c.execute("""
                SELECT COUNT(*) FROM repayments
                WHERE mpesa_transaction_id = %s
            """, [trans_id])
            already_exists = c.fetchone()[0] > 0

            if already_exists:
                skipped_duplicate += 1
                w(f'  [SKIP] {trans_id} — repayment already exists')
                continue

            w(f'\n  [PROCESS] {created_at} | TransID={trans_id} | BillRef={bill_ref} | KES {amount} | {fname} {lname}')

            if not commit:
                # Dry run — just check if borrower/loan can be matched
                from payments.sasapay_service import _phone_variants
                from users.models import CustomUser
                from loans.models import Loan

                borrower = None
                for variant in _phone_variants(bill_ref):
                    try:
                        borrower = CustomUser.objects.get(
                            phone_number=variant, role='borrower', is_active=True
                        )
                        w(f'    Borrower found: {borrower.get_full_name()} (phone variant: {variant})')
                        break
                    except (CustomUser.DoesNotExist, CustomUser.MultipleObjectsReturned):
                        continue

                if borrower is None:
                    w(f'    ✗ No borrower match for BillRef={bill_ref}')
                    skipped_no_loan += 1
                    continue

                loan = Loan.active_objects.filter(
                    borrower=borrower, status='active'
                ).order_by('-created_at').first()

                if loan:
                    w(f'    Loan found: {loan.loan_number} (outstanding: {loan.outstanding_amount})')
                    w(f'    → WOULD create repayment for KES {amount}')
                    recovered += 1
                else:
                    w(f'    ✗ No active loan for {borrower.get_full_name()}')
                    skipped_no_loan += 1
            else:
                # Commit mode — actually re-process the IPN
                try:
                    from payments.sasapay_service import process_ipn_callback
                    result = process_ipn_callback(data)
                    if result.get('success'):
                        w(f'    ✓ Saved: receipt={result.get("receipt_number")} loan={result.get("loan_number")}')
                        recovered += 1
                    else:
                        w(f'    ✗ Failed: {result.get("message")}')
                        skipped_no_loan += 1
                except Exception as e:
                    w(f'    ✗ Error: {e}')
                    errors += 1

        w('\n' + '=' * 70)
        w('SUMMARY')
        w('=' * 70)
        w(f'  {"Would recover" if not commit else "Recovered"}:   {recovered}')
        w(f'  Already existed:  {skipped_duplicate}')
        w(f'  No match:         {skipped_no_loan}')
        w(f'  Errors:           {errors}')
        if not commit:
            w('\n  Run with --commit to actually save the repayments.')
        w('=' * 70)