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

Uses raw SQL INSERT to bypass Repayment.save() entirely, avoiding the
receipt-number race condition and FK constraint errors that occur when
saving 2500+ records rapidly through the ORM.

After all repayments are inserted, a second pass creates Receipt records
sequentially with guaranteed-unique numbers.

Safe to run multiple times — uses skip-if-exists logic.
"""
import os
import sys
import uuid
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
from utils.models import Receipt

# ── 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):
    m = str(method_code or '').strip().lower()
    if m in ('02', 'sasapay', 'mpesa', 'mobile money', 'mobile', '01'):
        return 'mpesa'
    if m in ('03', 'bank', 'bank transfer', 'cheque'):
        return 'bank'
    return 'cash'

def new_uuid():
    return uuid.uuid4().hex  # 32-char hex, matches char(32) in DB

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

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

# ── Step 0: Verify payments table exists ──────────────────────
with connection.cursor() as c:
    c.execute("SHOW TABLES LIKE 'payments'")
    if not c.fetchone():
        print("\nERROR: 'payments' table not found.")
        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 DB: {Repayment.objects.count()}")
print(f"Current receipts in DB  : {Receipt.objects.count()}")

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

# ── Step 0b: Clean up any broken records from previous failed run ──
print("\n[0] Cleaning up any broken records from previous run...")

with connection.cursor() as c:
    # Delete receipts linked to TMP- repayments
    c.execute("DELETE FROM receipts WHERE repayment_id IN (SELECT id FROM repayments WHERE receipt_number LIKE 'TMP-%')")
    deleted_receipts = c.rowcount

    # Delete repayments with TMP- receipt numbers (the broken ones)
    c.execute("DELETE FROM repayments WHERE receipt_number LIKE 'TMP-%'")
    deleted_repayments = c.rowcount

if deleted_repayments > 0 or deleted_receipts > 0:
    print(f"  Cleaned up {deleted_repayments} broken repayments, {deleted_receipts} broken receipts")
else:
    print("  Nothing to clean up")

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

baccount_to_loan = {}

with connection.cursor() as c:
    c.execute("SHOW TABLES LIKE 'loan_info'")
    if c.fetchone():
        c.execute("SELECT id, baccount FROM loan_info WHERE baccount IS NOT NULL AND baccount != ''")
        for old_id, baccount in c.fetchall():
            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

print(f"  Mapped {len(baccount_to_loan)} loan accounts")
print(f"  Total loans in Django DB: {Loan.objects.count()}")

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

with connection.cursor() as c:
    c.execute("""
        SELECT id, account, pay_date, amount_to_pay,
               payment_method, reference, tr_id, 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 3: Build dedup sets ───────────────────────────────────
# Existing mpesa_transaction_ids already in DB
existing_tx_ids = set(
    Repayment.objects.exclude(mpesa_transaction_id__isnull=True)
                     .exclude(mpesa_transaction_id='')
                     .values_list('mpesa_transaction_id', flat=True)
)

# ── Step 4: Raw INSERT repayments (bypass save() entirely) ────
print("\n[3] Inserting repayments via raw SQL (bypasses receipt generation)...")

now_str = datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')

# We'll collect (repayment_id, loan, amount, payment_date) for receipt pass
inserted_repayments = []

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

    try:
        account = str(p.get('account') or '').strip()
        loan = baccount_to_loan.get(account)
        if not loan:
            stats['skipped_no_loan'] += 1
            continue

        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()
        pay_date_str = pay_date.strftime('%Y-%m-%d %H:%M:%S.%f')

        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
            ''
        )[:50]

        # Dedup by loan + amount + date
        with connection.cursor() as c:
            c.execute(
                "SELECT COUNT(*) FROM repayments WHERE loan_id=%s AND amount=%s AND payment_date=%s",
                [loan.id.hex, str(amount), pay_date_str]
            )
            if c.fetchone()[0] > 0:
                stats['skipped_duplicate'] += 1
                continue

        # Dedup by transaction id
        if transaction_ref and transaction_ref in existing_tx_ids:
            stats['skipped_duplicate'] += 1
            continue

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

        # Raw INSERT — no receipt_number yet (will fill in pass 2)
        # receipt_number has a NOT NULL unique constraint, so we use a temp placeholder
        temp_receipt = f"TMP-{rep_id[:14]}"  # unique per row, 19 chars max

        with connection.cursor() as c:
            c.execute("""
                INSERT INTO repayments
                    (id, loan_id, amount, payment_method, payment_source,
                     mpesa_transaction_id, mpesa_phone_number,
                     receipt_number, payment_date, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, [
                rep_id,
                loan.id.hex,
                str(amount),
                method,
                'manual',
                transaction_ref or None,
                None,
                temp_receipt,
                pay_date_str,
                now_str,
            ])

        if transaction_ref:
            existing_tx_ids.add(transaction_ref)

        inserted_repayments.append({
            'id': rep_id,
            'loan': loan,
            'amount': amount,
            'payment_date': pay_date,
        })
        stats['migrated'] += 1

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

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

print(f"  Inserted {stats['migrated']} repayments")

# ── Step 5: Generate proper receipt numbers & create receipts ──
print("\n[4] Creating receipts and assigning receipt numbers...")

# Find the highest existing sequential receipt number across both tables
existing_rcp_nums = set()
for rn in Receipt.objects.values_list('receipt_number', flat=True):
    if rn and rn.startswith('RCP-'):
        try:
            existing_rcp_nums.add(int(rn.split('-')[1]))
        except (IndexError, ValueError):
            pass
for rn in Repayment.objects.exclude(receipt_number__startswith='TMP-').values_list('receipt_number', flat=True):
    if rn and rn.startswith('RCP-'):
        try:
            existing_rcp_nums.add(int(rn.split('-')[1]))
        except (IndexError, ValueError):
            pass

next_rcp = max(existing_rcp_nums, default=0) + 1

for rep_info in inserted_repayments:
    try:
        rep_id   = rep_info['id']
        loan     = rep_info['loan']
        amount   = rep_info['amount']
        pay_date = rep_info['payment_date']

        # Assign the next sequential receipt number
        receipt_number = f"RCP-{next_rcp:06d}"
        next_rcp += 1

        # Update repayment row with proper receipt number
        with connection.cursor() as c:
            c.execute(
                "UPDATE repayments SET receipt_number=%s WHERE id=%s",
                [receipt_number, rep_id]
            )

        # Calculate balances for the receipt
        pay_date_str = pay_date.strftime('%Y-%m-%d %H:%M:%S.%f')
        with connection.cursor() as c:
            c.execute("""
                SELECT COALESCE(SUM(amount), 0)
                FROM repayments
                WHERE loan_id=%s AND payment_date < %s
            """, [loan.id.hex, pay_date_str])
            prev_paid = dec(c.fetchone()[0])

        previous_balance = loan.total_amount - prev_paid
        new_balance = previous_balance - amount

        receipt_id = new_uuid()
        now_str2 = datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')

        with connection.cursor() as c:
            c.execute("""
                INSERT INTO receipts
                    (id, repayment_id, loan_id, borrower_id,
                     receipt_number, amount_paid, payment_method,
                     payment_date, previous_balance, new_balance,
                     pdf_file, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, [
                receipt_id,
                rep_id,
                loan.id.hex,
                loan.borrower.id.hex,
                receipt_number,
                str(amount),
                'mpesa',   # default; method stored on repayment
                pay_date_str,
                str(previous_balance),
                str(new_balance),
                '',
                now_str2,
            ])

        stats['receipts_created'] += 1

        if stats['receipts_created'] % 100 == 0:
            print(f"  ... {stats['receipts_created']} receipts created")

    except Exception as e:
        stats['receipts_failed'] += 1
        if stats['receipts_failed'] <= 5:
            print(f"  RECEIPT FAILED rep_id={rep_info['id']}: {e}")

# ── Step 6: Update loan totals ─────────────────────────────────
print("\n[5] Updating loan payment totals...")

updated_loans = set(r['loan'].id for r in inserted_repayments)
for loan_id in updated_loans:
    try:
        loan = Loan.objects.get(id=loan_id)
        from django.db.models import Sum
        total_paid = loan.repayments.aggregate(t=Sum('amount'))['t'] or Decimal('0.00')
        loan._amount_paid_cache = total_paid
        if loan.status == 'active' and total_paid >= loan.total_amount:
            loan.status = 'paid'
        # Use update() to avoid triggering save() hooks
        Loan.objects.filter(id=loan_id).update(
            status=loan.status,
        )
    except Exception as e:
        pass  # non-critical

# ── Summary ───────────────────────────────────────────────────
print(f"\n{'='*60}")
print(f"  REPAYMENTS MIGRATION COMPLETE")
print(f"{'='*60}")
print(f"  Migrated (repayments) : {stats['migrated']}")
print(f"  Receipts created      : {stats['receipts_created']}")
print(f"  Skipped (no loan)     : {stats['skipped_no_loan']}")
print(f"  Skipped (duplicate)   : {stats['skipped_duplicate']}")
print(f"  Skipped (zero amount) : {stats['skipped_zero_amount']}")
print(f"  Failed                : {stats['failed']}")
print(f"  Receipt failures      : {stats['receipts_failed']}")
print(f"\n  Total repayments in DB : {Repayment.objects.count()}")
print(f"  Total receipts in DB   : {Receipt.objects.count()}")
print(f"{'='*60}\n")