#!/usr/bin/env python3
"""
Data migration: old Grazuri tables → new Django tables.

Migrates:
  borrowers        → users (role='borrower')
  loan_info        → loan_applications + loans
  payments         → repayments

Run on server: python migrate_data.py

Safe to run multiple times — uses get_or_create / skip-if-exists logic.
"""
import os
import sys
import uuid
import traceback
from datetime import datetime, date
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, transaction
from django.contrib.auth.hashers import make_password

from users.models import CustomUser
from loans.models import Loan, LoanApplication, LoanProduct, Repayment

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

def dec(val, default=Decimal('0.00')):
    """Safe decimal conversion."""
    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):
    """Convert various date/datetime formats to naive datetime or None (USE_TZ=False)."""
    if not val:
        return None
    if isinstance(val, datetime):
        # Strip timezone info — Django is configured with USE_TZ=False
        return val.replace(tzinfo=None)
    if isinstance(val, date):
        return datetime.combine(val, datetime.min.time())
    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 to_date(val):
    """Convert to date or None."""
    if not val:
        return None
    if isinstance(val, date):
        return val
    if isinstance(val, datetime):
        return val.date()
    if isinstance(val, str):
        for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%Y-%m-%d %H:%M:%S'):
            try:
                return datetime.strptime(val.strip(), fmt).date()
            except ValueError:
                continue
    return None

def clean_phone(phone):
    """Normalise phone to +254XXXXXXXXX format."""
    if not phone:
        return None
    p = str(phone).strip().replace(' ', '').replace('-', '')
    if p.startswith('0') and len(p) == 10:
        p = '+254' + p[1:]
    elif p.startswith('254') and not p.startswith('+'):
        p = '+' + p
    elif not p.startswith('+'):
        p = '+254' + p.lstrip('0')
    return p[:17] if len(p) <= 17 else None

def map_loan_status(disbursement_status, old_status):
    """Map old status values to new Django status choices."""
    ds = (disbursement_status or '').upper()
    os_ = (old_status or '').strip()
    if ds == 'DISBURSED':
        return 'active'
    if ds == 'LOAN_DECLINED':
        return 'defaulted'
    return 'active'  # default for migrated loans

def map_application_status(disbursement_status, old_status):
    """Map old status to loan_application status."""
    ds = (disbursement_status or '').upper()
    if ds == 'DISBURSED':
        return 'disbursed'
    if ds == 'LOAN_DECLINED':
        return 'rejected'
    if ds in ('PENDING_APPROVAL',):
        return 'pending'
    if ds in ('PENDING_DOC_REVIEW',):
        return 'under_review'
    if ds in ('PENDING_DISB',):
        return 'approved'
    return 'pending'

# ── Stats ─────────────────────────────────────────────────────
stats = {
    'borrowers_migrated': 0,
    'borrowers_skipped': 0,
    'borrowers_failed': 0,
    'products_created': 0,
    'loans_migrated': 0,
    'loans_skipped': 0,
    'loans_failed': 0,
    'payments_migrated': 0,
    'payments_skipped': 0,
    'payments_failed': 0,
}

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

# ════════════════════════════════════════════════════════════
# STEP 1: Migrate borrowers → users
# ════════════════════════════════════════════════════════════
print(f"\n[1] Migrating borrowers → users...")

with connection.cursor() as c:
    c.execute("""
        SELECT id, fname, middlename, lname, email, phone, telephone,
               addrs1, addrs2, district, country, date_of_birth, gender,
               id_number, employer, salary, occupation, status,
               date_time, branch, marital, nationality, city
        FROM borrowers
    """)
    borrowers = c.fetchall()
    cols = [d[0] for d in c.description]

borrower_id_map = {}  # old borrower int id → new UUID

for row in borrowers:
    b = dict(zip(cols, row))
    old_id = b['id']

    try:
        # Build a unique phone — required field
        phone = clean_phone(b.get('phone') or b.get('telephone'))

        # Build email — nullable but must be unique if set
        email = (b.get('email') or '').strip() or None

        # Check if already migrated by id_number or phone
        id_number = (b.get('id_number') or '').strip() or None

        existing = None
        if id_number:
            existing = CustomUser.objects.filter(id_number=id_number).first()
        if not existing and phone:
            existing = CustomUser.objects.filter(phone_number=phone).first()
        if not existing and email:
            existing = CustomUser.objects.filter(email=email).first()

        if existing:
            borrower_id_map[old_id] = existing.id
            stats['borrowers_skipped'] += 1
            continue

        # Generate a unique phone if missing
        if not phone:
            phone = f'+2540000{old_id:06d}'

        # Generate unique username
        base_username = f"borrower_{old_id}"
        username = base_username
        counter = 1
        while CustomUser.objects.filter(username=username).exists():
            username = f"{base_username}_{counter}"
            counter += 1

        full_name = ' '.join(filter(None, [
            b.get('fname', ''), b.get('middlename', ''), b.get('lname', '')
        ])).strip()
        first_name = (b.get('fname') or '').strip()
        last_name = ' '.join(filter(None, [
            b.get('middlename', ''), b.get('lname', '')
        ])).strip() or (b.get('lname') or '').strip()

        user = CustomUser(
            id=uuid.uuid4(),
            username=username,
            first_name=first_name[:150],
            last_name=last_name[:150],
            email=email,
            phone_number=phone,
            id_number=id_number,
            date_of_birth=to_date(b.get('date_of_birth')),
            gender='M' if str(b.get('gender', '')).upper() in ('M', 'MALE') else
                   'F' if str(b.get('gender', '')).upper() in ('F', 'FEMALE') else None,
            physical_address=(b.get('addrs1') or '') + ' ' + (b.get('addrs2') or ''),
            city=(b.get('city') or b.get('district') or '').strip()[:100] or None,
            country=(b.get('country') or 'Kenya').strip()[:100],
            employer=(b.get('employer') or '').strip()[:200] or None,
            monthly_income=dec(b.get('salary')),
            nationality=(b.get('nationality') or '').strip()[:50] or None,
            role='borrower',
            status='active',
            is_active=True,
            is_staff=False,
            is_superuser=False,
            is_verified=True,
            is_phone_verified=True,
            is_email_verified=bool(email),
            password=make_password(None),  # unusable password — they log in via phone/OTP
        )
        user.save()
        borrower_id_map[old_id] = user.id
        stats['borrowers_migrated'] += 1

        if stats['borrowers_migrated'] % 20 == 0:
            print(f"  ... {stats['borrowers_migrated']} borrowers migrated")

    except Exception as e:
        stats['borrowers_failed'] += 1
        print(f"  FAILED borrower id={old_id}: {e}")

print(f"  Borrowers: {stats['borrowers_migrated']} migrated, "
      f"{stats['borrowers_skipped']} skipped, "
      f"{stats['borrowers_failed']} failed")
print(f"  ID map has {len(borrower_id_map)} entries")

# ════════════════════════════════════════════════════════════
# STEP 2: Create loan products from loan_info.product values
# ════════════════════════════════════════════════════════════
print(f"\n[2] Creating loan products...")

with connection.cursor() as c:
    c.execute("SELECT DISTINCT product FROM loan_info WHERE product IS NOT NULL AND product != ''")
    product_names = [r[0] for r in c.fetchall()]

product_map = {}  # product name → LoanProduct instance

for name in product_names:
    name = str(name).strip()
    if not name:
        continue
    # Map old product names to the two canonical products
    if 'log' in name.lower() or 'logbook' in name.lower():
        ptype = 'logbook'
        canonical = 'Log Book Loan'
    else:
        ptype = 'biashara'
        canonical = 'Biashara Loan'
    # Use the canonical product — don't create new ones
    prod = LoanProduct.objects.filter(name=canonical).first()
    if not prod:
        prod, created = LoanProduct.objects.get_or_create(
            name=canonical,
            defaults={
                'product_type': ptype,
                'description': f'Migrated from Grazuri: {name}',
                'min_amount': Decimal('1000.00'),
                'max_amount': Decimal('1000000.00'),
                'min_duration': 7,
                'max_duration': 365,
                'interest_rate': Decimal('15.00'),
                'processing_fee': Decimal('0.00'),
                'is_active': True,
            }
        )
        if created:
            stats['products_created'] += 1
    product_map[name] = prod

# Default product for loans with no product name — use Biashara
default_product = LoanProduct.objects.filter(name='Biashara Loan').first()
if not default_product:
    default_product, _ = LoanProduct.objects.get_or_create(
        name='Biashara Loan',
        defaults={
            'product_type': 'biashara',
            'description': 'Business loan — weekly repayment, 15% flat rate on principal',
            'min_amount': Decimal('2000.00'),
            'max_amount': Decimal('200000.00'),
            'min_duration': 7,
            'max_duration': 378,
            'interest_rate': Decimal('15.00'),
            'processing_fee': Decimal('0.00'),
            'is_active': True,
        }
    )

print(f"  {stats['products_created']} products created, {len(product_map)} total available")

# ════════════════════════════════════════════════════════════
# STEP 3: Migrate loan_info → loan_applications + loans
# ════════════════════════════════════════════════════════════
print(f"\n[3] Migrating loan_info → loans...")

with connection.cursor() as c:
    c.execute("""
        SELECT id, borrower, baccount, reason, amount, application_date,
               agent, loan_product, repayment_remark, amount_todisburse,
               amount_topay, pay_date, balance, status, disbursement_status,
               loan_num_of_repayments, loan_payment_scheme, loan_duration_period,
               loan_duration, loan_interest_period, loan_interest, loan_interest_type,
               loan_interest_method, date_release, loan_disbursed_by_id,
               loan_maturity, modified_date, fees, branch, loan_repayment_method,
               disbursed_amount, interest_value, penalty, product,
               disbursement_date, maturity_date, loan_officer, num_repayments,
               interest, instalment_no, total_repayable, monthly_instalment,
               added_by, tid, processing_fee, approved_by, approved_date,
               purpose, notes
        FROM loan_info
    """)
    loans_raw = c.fetchall()
    loan_cols = [d[0] for d in c.description]

loan_id_map = {}  # old loan int id → new UUID

admin_user = CustomUser.objects.filter(is_superuser=True).first()

for row in loans_raw:
    li = dict(zip(loan_cols, row))
    old_id = li['id']

    try:
        # Skip if already migrated (check by loan_number)
        loan_number = f"GRZ-{old_id:06d}"
        if Loan.objects.filter(loan_number=loan_number).exists():
            loan_id_map[old_id] = Loan.objects.get(loan_number=loan_number).id
            stats['loans_skipped'] += 1
            continue

        # Find the borrower
        old_borrower_id = li.get('borrower')
        borrower_uuid = borrower_id_map.get(old_borrower_id)
        if not borrower_uuid:
            # Try to find by baccount
            stats['loans_failed'] += 1
            print(f"  SKIP loan id={old_id}: borrower {old_borrower_id} not in map")
            continue

        try:
            borrower = CustomUser.objects.get(id=borrower_uuid)
        except CustomUser.DoesNotExist:
            stats['loans_failed'] += 1
            continue

        # Loan product
        product_name = str(li.get('product') or li.get('loan_product') or '').strip()
        loan_product = product_map.get(product_name, default_product)

        # Amounts
        principal = dec(li.get('amount') or li.get('disbursed_amount'))
        interest = dec(li.get('interest_value') or li.get('loan_interest'))
        processing_fee = dec(li.get('processing_fee'))
        total = dec(li.get('amount_topay') or li.get('total_repayable'))
        if total == Decimal('0.00'):
            total = principal + interest + processing_fee

        # Dates
        app_date = to_dt(li.get('application_date') or li.get('modified_date')) or datetime.now()
        disb_date = to_dt(li.get('disbursement_date') or li.get('date_release')) or app_date
        due_date = to_dt(li.get('maturity_date') or li.get('loan_maturity')) or disb_date

        # Duration
        try:
            duration = int(li.get('loan_duration') or 30)
        except (ValueError, TypeError):
            duration = 30

        # Repayment method
        rm = str(li.get('loan_repayment_method') or li.get('repayment_remark') or 'monthly').lower()
        if 'daily' in rm:
            repayment_method = 'daily'
        elif 'week' in rm:
            repayment_method = 'weekly'
        else:
            repayment_method = 'monthly'

        # Statuses
        app_status = map_application_status(li.get('disbursement_status'), li.get('status'))
        loan_status = map_loan_status(li.get('disbursement_status'), li.get('status'))

        app_number = f"APP-GRZ-{old_id:06d}"

        with transaction.atomic():
            # Create LoanApplication
            app = LoanApplication(
                id=uuid.uuid4(),
                application_number=app_number,
                borrower=borrower,
                loan_product=loan_product,
                requested_amount=principal,
                requested_duration=duration,
                purpose=(li.get('purpose') or li.get('reason') or 'Migrated loan')[:500],
                repayment_method=repayment_method,
                interest_amount=interest,
                processing_fee_amount=processing_fee,
                total_amount=total,
                status=app_status,
                submitted_at=app_date,
                auto_approved=True,
            )
            if app_status in ('approved', 'disbursed'):
                app.reviewed_at = to_dt(li.get('approved_date')) or app_date
                app.approval_notes = 'Migrated from Grazuri system'
            app.save()

            # Create Loan
            loan = Loan(
                id=uuid.uuid4(),
                loan_number=loan_number,
                application=app,
                borrower=borrower,
                principal_amount=principal,
                interest_amount=interest,
                processing_fee=processing_fee,
                registration_fee=Decimal('0.00'),
                total_amount=total,
                disbursement_date=disb_date,
                due_date=due_date,
                duration_days=duration,
                status=loan_status,
                is_deleted=False,
                created_at=app_date,
            )
            loan.save()

        loan_id_map[old_id] = loan.id
        stats['loans_migrated'] += 1

        if stats['loans_migrated'] % 20 == 0:
            print(f"  ... {stats['loans_migrated']} loans migrated")

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

print(f"  Loans: {stats['loans_migrated']} migrated, "
      f"{stats['loans_skipped']} skipped, "
      f"{stats['loans_failed']} failed")

# ════════════════════════════════════════════════════════════
# STEP 4: Migrate payments → repayments
#
# The old `payments` table links to loans via the `account` field
# (e.g. 'BSH/202501/00003'), which is the loan's baccount from loan_info.
# We build a baccount → Loan map from the loan_id_map we already have.
# ════════════════════════════════════════════════════════════
print(f"\n[4] Migrating payments → repayments...")

# Check payments table exists
with connection.cursor() as c:
    c.execute("SHOW TABLES LIKE 'payments'")
    if not c.fetchone():
        print("  payments table not found — skipping repayments migration")
        stats['payments_skipped'] = 0
        stats['payments_migrated'] = 0
        stats['payments_failed'] = 0
    else:
        # Build baccount → Loan map using loan_info.baccount + loan_id_map
        baccount_to_loan = {}
        with connection.cursor() as c2:
            c2.execute("SELECT id, baccount FROM loan_info WHERE baccount IS NOT NULL AND baccount != ''")
            for old_id, baccount in c2.fetchall():
                baccount = str(baccount).strip()
                loan_uuid = loan_id_map.get(old_id)
                if loan_uuid:
                    try:
                        baccount_to_loan[baccount] = Loan.objects.get(id=loan_uuid)
                    except Loan.DoesNotExist:
                        pass

        print(f"  baccount→loan map: {len(baccount_to_loan)} entries")

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

        print(f"  {len(payments_raw)} payment rows to process")

        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['payments_skipped'] += 1
                    continue

                amount = dec(p.get('amount_to_pay'))
                if amount <= Decimal('0.00'):
                    stats['payments_skipped'] += 1
                    continue

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

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

                # Transaction reference
                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 by mpesa transaction id
                if transaction_ref and Repayment.objects.filter(
                    mpesa_transaction_id=transaction_ref
                ).exists():
                    stats['payments_skipped'] += 1
                    continue

                # Map payment method: '02' = SasaPay/M-Pesa, '01' = cash, '03' = bank
                method_code = str(p.get('payment_method') or '').strip().lower()
                if method_code in ('02', 'sasapay', 'mpesa', 'mobile'):
                    method = 'mpesa'
                elif method_code in ('03', 'bank', 'cheque'):
                    method = 'bank'
                else:
                    method = 'cash'

                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,
                )
                repayment.save()
                stats['payments_migrated'] += 1

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

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

print(f"  Payments: {stats['payments_migrated']} migrated, "
      f"{stats['payments_skipped']} skipped, "
      f"{stats['payments_failed']} failed")

# ════════════════════════════════════════════════════════════
# FINAL SUMMARY
# ════════════════════════════════════════════════════════════
print(f"\n{'='*60}")
print(f"  MIGRATION COMPLETE")
print(f"{'='*60}")
print(f"  Borrowers migrated : {stats['borrowers_migrated']}")
print(f"  Borrowers skipped  : {stats['borrowers_skipped']} (already existed)")
print(f"  Borrowers failed   : {stats['borrowers_failed']}")
print(f"  Products created   : {stats['products_created']}")
print(f"  Loans migrated     : {stats['loans_migrated']}")
print(f"  Loans skipped      : {stats['loans_skipped']} (already existed)")
print(f"  Loans failed       : {stats['loans_failed']}")
print(f"  Payments migrated  : {stats['payments_migrated']}")
print(f"  Payments skipped   : {stats['payments_skipped']}")
print(f"  Payments failed    : {stats['payments_failed']}")

print(f"\n  Verifying dashboard counts...")
print(f"  users (borrowers)  : {CustomUser.objects.filter(role='borrower').count()}")
print(f"  loans (total)      : {Loan.objects.count()}")
print(f"  loans (active)     : {Loan.objects.filter(status='active').count()}")
print(f"  loan_applications  : {LoanApplication.objects.count()}")
print(f"  repayments         : {Repayment.objects.count()}")
print(f"\n  Restart your app in cPanel — the dashboard should now show data.")
print(f"{'='*60}\n")