#!/usr/bin/env python3
"""
Migrate payments → repayments (standalone script).

The old `payments` table links to loans via the `account` field
(e.g. 'BSH/202501/00003'), which corresponds to `loan_info.baccount`.
The migrate_data.py script missed this because it tried to match by
a loan int-ID that doesn't exist in the payments table.

This script:
  1. Builds a map: baccount → Loan UUID  (from already-migrated loans)
  2. Reads every row from the old `payments` table
  3. Creates a Repayment for each one (skips duplicates)

Safe to run multiple times — uses skip-if-exists logic.
"""
import os
import sys
import traceback
from datetime import datetime
from decimal import Decimal, InvalidOperation

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')

try:
    from dotenv import load_dotenv
    load_dotenv(os.path.join(os.path.dirname(os.path.abspath(__file__)), '.env'), override=True)
except ImportError:
    pass

import django
django.setup()

from django.db import connection
from loans.models import Loan, Repayment

# ── Helpers ───────────────────────────────────────────────────

def dec(val, default=Decimal('0.00')):
    try:
        if val is None or val == '':
            return default
        return Decimal(str(val)).quantize(Decimal('0.01'))
    except (InvalidOperation, ValueError):
        return default

def to_dt(val):
    if not val:
        return None
    if isinstance(val, datetime):
        return val.replace(tzinfo=None)
    if isinstance(val, str):
        for fmt in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d', '%d/%m/%Y'):
            try:
                return datetime.strptime(val.strip(), fmt)
            except ValueError:
                continue
    return None

def map_method(method_code):
    """Map old payment_method codes to new choices."""
    m = str(method_code or '').strip().lower()
    if m in ('01', 'mpesa', 'mobile money', 'mobile'):
        return 'mpesa'
    if m in ('03', 'bank', 'bank transfer', 'cheque'):
        return 'bank'
    # '02' = SasaPay (treat as mpesa/mobile)
    if m in ('02', 'sasapay'):
        return 'mpesa'
    return 'cash'

# ── Stats ─────────────────────────────────────────────────────
stats = {
    'migrated': 0,
    'skipped_no_loan': 0,
    'skipped_duplicate': 0,
    'skipped_zero_amount': 0,
    'failed': 0,
}

print(f"\n{'='*60}")
print(f"  Repayments Migration  {datetime.now():%Y-%m-%d %H:%M:%S}")
print(f"{'='*60}")

# ── Step 1: Check payments table exists ───────────────────────
with connection.cursor() as c:
    c.execute("SHOW TABLES LIKE 'payments'")
    if not c.fetchone():
        print("\nERROR: 'payments' table not found in the database.")
        print("Make sure you are running this against the correct database")
        print("(the one that has the old Grazuri data).")
        sys.exit(1)

    c.execute("SELECT COUNT(*) FROM payments")
    total_payments = c.fetchone()[0]

print(f"\nFound {total_payments} rows in payments table")
print(f"Current repayments in Django DB: {Repayment.objects.count()}")

if total_payments == 0:
    print("\nNo payments to migrate.")
    sys.exit(0)

# ── Step 2: Build baccount → Loan map ─────────────────────────
print("\n[1] Building loan account map from loan_info...")

# Check if loan_info table exists (old table)
with connection.cursor() as c:
    c.execute("SHOW TABLES LIKE 'loan_info'")
    has_loan_info = bool(c.fetchone())

baccount_to_loan = {}  # 'BSH/202501/00003' → Loan instance

if has_loan_info:
    with connection.cursor() as c:
        c.execute("SELECT id, baccount FROM loan_info WHERE baccount IS NOT NULL AND baccount != ''")
        loan_info_rows = c.fetchall()

    for old_id, baccount in loan_info_rows:
        baccount = str(baccount).strip()
        loan_number = f"GRZ-{old_id:06d}"
        try:
            loan = Loan.objects.get(loan_number=loan_number)
            baccount_to_loan[baccount] = loan
        except Loan.DoesNotExist:
            pass  # loan wasn't migrated yet

    print(f"  Mapped {len(baccount_to_loan)} loan accounts from loan_info")
else:
    print("  loan_info table not found — will try matching by loan_number directly")

# Also try matching by loan_number directly (for any loans already in Django)
# This handles cases where loans were migrated but baccount map is incomplete
all_loans = {loan.loan_number: loan for loan in Loan.objects.all()}
print(f"  Total loans in Django DB: {len(all_loans)}")

# ── Step 3: Read payments table ───────────────────────────────
print("\n[2] Reading payments table...")

with connection.cursor() as c:
    c.execute("""
        SELECT id, tid, account, balance, customer, loan,
               pay_date, amount_to_pay, remarks, payment_method,
               reference, tr_id, gl_code, status, tx_id
        FROM payments
        ORDER BY id
    """)
    payments_raw = c.fetchall()
    pay_cols = [d[0] for d in c.description]

print(f"  Loaded {len(payments_raw)} payment rows")

# ── Step 4: Migrate each payment ──────────────────────────────
print("\n[3] Migrating payments → repayments...")

for row in payments_raw:
    p = dict(zip(pay_cols, row))
    old_id = p['id']

    try:
        # Find the loan via account (baccount)
        account = str(p.get('account') or '').strip()
        loan = baccount_to_loan.get(account)

        if not loan:
            # Try direct loan_number match (fallback)
            loan = all_loans.get(account)

        if not loan:
            stats['skipped_no_loan'] += 1
            if stats['skipped_no_loan'] <= 5:
                print(f"  SKIP payment id={old_id}: no loan found for account='{account}'")
            continue

        # Amount — use amount_to_pay (what was actually paid this transaction)
        amount = dec(p.get('amount_to_pay'))
        if amount <= Decimal('0.00'):
            stats['skipped_zero_amount'] += 1
            continue

        pay_date = to_dt(p.get('pay_date')) or datetime.now()

        # Transaction reference — prefer reference (M-Pesa code), then tr_id, then tx_id
        transaction_ref = (
            str(p.get('reference') or '').strip() or
            str(p.get('tr_id') or '').strip() or
            str(p.get('tx_id') or '').strip() or
            ''
        )

        # Dedup: same loan + same amount + same date (within 1 second)
        if Repayment.objects.filter(
            loan=loan,
            amount=amount,
            payment_date=pay_date,
        ).exists():
            stats['skipped_duplicate'] += 1
            continue

        # Also dedup by mpesa_transaction_id if we have one
        if transaction_ref and Repayment.objects.filter(
            mpesa_transaction_id=transaction_ref
        ).exists():
            stats['skipped_duplicate'] += 1
            continue

        method = map_method(p.get('payment_method'))

        repayment = Repayment(
            loan=loan,
            amount=amount,
            payment_date=pay_date,
            payment_method=method,
            payment_source='manual',
            mpesa_transaction_id=transaction_ref[:50] if transaction_ref else None,
            notes=str(p.get('remarks') or 'Migrated from Grazuri')[:500],
        )
        repayment.save()  # auto-generates receipt_number
        stats['migrated'] += 1

        if stats['migrated'] % 50 == 0:
            print(f"  ... {stats['migrated']} repayments migrated")

    except Exception as e:
        stats['failed'] += 1
        print(f"  FAILED payment id={old_id}: {e}")
        if stats['failed'] <= 3:
            traceback.print_exc()

# ── Summary ───────────────────────────────────────────────────
print(f"\n{'='*60}")
print(f"  REPAYMENTS MIGRATION COMPLETE")
print(f"{'='*60}")
print(f"  Migrated          : {stats['migrated']}")
print(f"  Skipped (no loan) : {stats['skipped_no_loan']}")
print(f"  Skipped (dup)     : {stats['skipped_duplicate']}")
print(f"  Skipped (zero amt): {stats['skipped_zero_amount']}")
print(f"  Failed            : {stats['failed']}")
print(f"\n  Total repayments in DB now: {Repayment.objects.count()}")
print(f"{'='*60}\n")
