from django.db import models
from django.core.validators import MinValueValidator, MaxValueValidator
from django.utils import timezone
from utils.datetime_utils import get_current_datetime, make_datetime_compatible
from django.contrib.auth import get_user_model
import uuid
from decimal import Decimal

User = get_user_model()


# New models for Grazuri schema compatibility


class ActiveLoanManager(models.Manager):
    """Manager that excludes soft-deleted loans"""
    def get_queryset(self):
        return super().get_queryset().filter(is_deleted=False)


class LoanProduct(models.Model):
    """
    Loan product definitions with interest rates and terms
    """
    PRODUCT_TYPES = [
        ('biashara', 'Biashara Loan'),
        ('logbook', 'Log Book Loan'),
    ]

    # Grazuri account type codes as stored in the Grazuri database
    GRAZURI_ACCOUNT_TYPES = [
        ('B', 'Business (Biashara)'),
        ('P', 'Personal (Log Book)'),
    ]
    
    REPAYMENT_METHODS = [
        ('daily', 'Daily'),
        ('weekly', 'Weekly'),
        ('monthly', 'Monthly'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=200)
    product_type = models.CharField(max_length=20, choices=PRODUCT_TYPES)
    description = models.TextField()

    # Grazuri schema compatibility fields
    gl_code = models.CharField(
        max_length=20,
        blank=True,
        default='',
        help_text="GL (General Ledger) account code used in Grazuri system (e.g. 11002)"
    )
    grazuri_account_type = models.CharField(
        max_length=5,
        choices=GRAZURI_ACCOUNT_TYPES,
        blank=True,
        default='',
        help_text="Account type code from Grazuri DB: 'B' for Biashara, 'P' for Log Book"
    )
    
    # Loan amounts
    min_amount = models.DecimalField(max_digits=12, decimal_places=2)
    max_amount = models.DecimalField(max_digits=12, decimal_places=2)
    
    # Interest and fees
    interest_rate = models.DecimalField(
        max_digits=5, 
        decimal_places=2,
        validators=[MinValueValidator(0), MaxValueValidator(100)],
        help_text="Monthly interest rate in percentage"
    )
    processing_fee = models.DecimalField(
        max_digits=5, 
        decimal_places=2,
        validators=[MinValueValidator(0), MaxValueValidator(100)],
        help_text="Processing fee as percentage of loan amount"
    )
    late_payment_penalty = models.DecimalField(
        max_digits=5, 
        decimal_places=2,
        default=5.0,
        help_text="Penalty rate percentage (applied per frequency period)"
    )
    penalty_mode = models.CharField(
        max_length=10,
        choices=[('auto', 'Automatic'), ('manual', 'Manual')],
        default='auto',
        help_text="Auto: Penalties applied automatically daily. Manual: Admin must apply penalties manually."
    )
    penalty_frequency = models.CharField(
        max_length=10,
        choices=[('daily', 'Daily'), ('weekly', 'Weekly'), ('monthly', 'Monthly')],
        default='daily',
        help_text="How often penalties are calculated and applied"
    )
    
    # Terms
    duration_months = models.PositiveIntegerField(help_text="Duration in months", default=1)
    min_duration = models.PositiveIntegerField(help_text="Minimum duration in days")
    max_duration = models.PositiveIntegerField(help_text="Maximum duration in days")
    
    # Duration options for flexible products (especially Boost Plus)
    available_durations = models.JSONField(
        default=list,
        blank=True,
        help_text="Available duration options in days (e.g., [7, 14, 30, 60, 90]). Leave empty to use min/max range."
    )
    
    # Repayment methods
    available_repayment_methods = models.JSONField(
        default=list,
        help_text="Available repayment methods for this product"
    )
    
    # Requirements
    requires_guarantor = models.BooleanField(default=False)
    requires_collateral = models.BooleanField(default=False)
    minimum_income = models.DecimalField(max_digits=12, decimal_places=2, blank=True, null=True)
    
    # Rollover settings
    rollover_fee_percentage = models.DecimalField(
        max_digits=5,
        decimal_places=2,
        default=5.0,
        help_text="Rollover fee as percentage of outstanding amount"
    )
    max_rollover_count = models.PositiveIntegerField(
        default=3,
        help_text="Maximum number of times a loan can be rolled over"
    )
    max_rollover_days = models.PositiveIntegerField(
        default=30,
        help_text="Maximum number of days for each rollover"
    )
    
    # Status
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'loan_products'
        ordering = ['name']
    
    def __str__(self):
        return f"{self.name} ({self.get_product_type_display()})"
    
    def get_interest_rate(self):
        """Get interest rate from settings or fallback to model value"""
        from utils.models import SystemSetting
        setting_key = f"{self.product_type}_interest_rate"
        return SystemSetting.get_float(setting_key, self.interest_rate)
    
    def get_processing_fee(self):
        """Get processing fee from settings or fallback to model value"""
        from utils.models import SystemSetting
        setting_key = f"{self.product_type}_processing_fee"
        return SystemSetting.get_float(setting_key, self.processing_fee)
    
    def get_min_amount(self):
        """Get minimum amount from settings or fallback to model value"""
        from utils.models import SystemSetting
        setting_key = f"{self.product_type}_min_amount"
        return SystemSetting.get_float(setting_key, float(self.min_amount))
    
    def get_max_amount(self):
        """Get maximum amount from settings or fallback to model value"""
        from utils.models import SystemSetting
        setting_key = f"{self.product_type}_max_amount"
        return SystemSetting.get_float(setting_key, float(self.max_amount))
    
    def get_late_payment_penalty(self):
        """Get late payment penalty from settings or fallback to model value"""
        from utils.models import SystemSetting
        setting_key = f"{self.product_type}_late_payment_penalty"
        return SystemSetting.get_float(setting_key, self.late_payment_penalty)
    
    def calculate_interest(self, amount, months):
        """
        Calculate interest for given amount and duration in months.

        Grazuri formula (Requirements 3.2, 3.5, 9.2):
          - Interest = principal × monthly_rate × months
          - Biashara Loan: simple monthly interest on the principal
          - Log Book Loan: simple monthly interest on the principal
          - All products use flat (simple) monthly interest, NOT compound

        Validates: Requirements 3.2, 3.5, 9.2, 9.4, 9.5
        """
        monthly_rate = Decimal(str(self.get_interest_rate())) / Decimal('100')
        result = Decimal(str(amount)) * monthly_rate * Decimal(str(months))
        # Round to 2 decimal places
        return result.quantize(Decimal('0.01'))

    def calculate_interest_from_days(self, amount, duration_days):
        """
        Calculate interest for given amount and duration in days.

        Converts days to months (30-day month basis) then applies flat interest.
        Minimum 1 month is enforced to avoid zero-interest short loans.

        Grazuri uses a 30-day month convention matching the Grazuri PHP system.
        Validates: Requirements 3.2, 3.5, 9.2
        """
        months = Decimal(str(duration_days)) / Decimal('30')
        # Minimum 1 month as per Grazuri policy
        months = max(Decimal('1'), months)
        return self.calculate_interest(amount, months)

    def calculate_processing_fee(self, amount, months=1):
        """
        Calculate processing fee for given amount and duration.

        Grazuri fee structure (Requirements 3.3, 3.7, 9.3):
          - Biashara Loan: one-time percentage fee on principal (charged upfront)
          - Log Book Loan: one-time percentage fee on principal (charged upfront)
          - Boost Plus legacy product: fee charged monthly (preserved for backward compat.)
          - All other products: one-time fee on principal

        Validates: Requirements 3.3, 3.7, 9.3, 9.4, 9.5
        """
        fee_rate = Decimal(str(self.get_processing_fee())) / Decimal('100')
        base_fee = Decimal(str(amount)) * fee_rate

        # For Boost Plus (legacy), processing fee is charged monthly like interest
        if self.product_type == 'boost_plus':
            result = base_fee * Decimal(str(months))
        else:
            # For Grazuri products (biashara, logbook) and all other products:
            # processing fee is a one-time upfront charge
            result = base_fee

        # Round to 2 decimal places
        return result.quantize(Decimal('0.01'))

    def calculate_total_loan_amount(self, principal, duration_days):
        """
        Calculate the total repayment amount for a Grazuri loan.

        Total = principal + interest + processing_fee

        This is the canonical calculation matching the Grazuri PHP system.
        Validates: Requirements 3.2, 3.3, 3.5, 9.1, 9.5
        """
        months = Decimal(str(duration_days)) / Decimal('30')
        months = max(Decimal('1'), months)
        interest = self.calculate_interest(principal, months)
        fee = self.calculate_processing_fee(principal, months)
        total = Decimal(str(principal)) + interest + fee
        return total.quantize(Decimal('0.01'))
    
    def get_repayment_methods_display(self):
        """Get human-readable repayment methods"""
        if not self.available_repayment_methods:
            return "Monthly"
        
        method_names = []
        for method in self.available_repayment_methods:
            method_names.append(dict(self.REPAYMENT_METHODS).get(method, method))
        return ", ".join(method_names)
    
    def is_boost_product(self):
        """Check if this is a Boost product"""
        return self.product_type == 'boost'

    def is_boost_plus_product(self):
        """Check if this is a Boost Plus product"""
        return self.product_type == 'boost_plus'

    def is_mwamba_product(self):
        """Check if this is a Mwamba product"""
        return self.product_type == 'mwamba'

    def is_imara_product(self):
        """Check if this is an Imara product"""
        return self.product_type == 'imara'

    def is_biashara_product(self):
        """Check if this is a Biashara Loan (Grazuri type 'B')"""
        return self.product_type == 'biashara'

    def is_logbook_product(self):
        """Check if this is a Log Book Loan (Grazuri type 'P')"""
        return self.product_type == 'logbook'

    def is_grazuri_product(self):
        """Check if this is one of the two canonical Grazuri Haven products"""
        return self.product_type in ('biashara', 'logbook')
    
    def validate_amount(self, amount):
        """Validate if amount is within product limits"""
        min_amount = Decimal(str(self.get_min_amount()))
        max_amount = Decimal(str(self.get_max_amount()))
        amount = Decimal(str(amount))
        
        if amount < min_amount:
            raise ValueError(f"Amount must be at least KES {min_amount:,.2f}")
        if amount > max_amount:
            raise ValueError(f"Amount cannot exceed KES {max_amount:,.2f}")
        return True
    
    def get_min_duration(self):
        """Get minimum duration from settings or fallback to model value"""
        from utils.models import SystemSetting
        if self.is_imara_product():
            setting_key = 'imara_min_duration'
            return SystemSetting.get_int(setting_key, self.min_duration)
        if self.is_grazuri_product():
            setting_key = f'{self.product_type}_min_duration'
            return SystemSetting.get_int(setting_key, self.min_duration)
        return self.min_duration

    def get_max_duration(self):
        """Get maximum duration from settings or fallback to model value"""
        from utils.models import SystemSetting
        if self.is_imara_product():
            setting_key = 'imara_max_duration'
            return SystemSetting.get_int(setting_key, self.max_duration)
        if self.is_grazuri_product():
            setting_key = f'{self.product_type}_max_duration'
            return SystemSetting.get_int(setting_key, self.max_duration)
        return self.max_duration
    
    def validate_duration(self, duration_days):
        """Validate if duration is within product limits and available options"""
        # First check basic min/max limits
        if duration_days < self.get_min_duration():
            raise ValueError(f"Duration must be at least {self.get_min_duration()} days")
        if duration_days > self.get_max_duration():
            raise ValueError(f"Duration cannot exceed {self.get_max_duration()} days")
        
        # If specific duration options are defined and this is a fixed-option product, check against them
        # Grazuri products (biashara, logbook) and Imara allow flexible durations within min/max range
        if self.available_durations and not self.is_imara_product() and not self.is_grazuri_product():
            if duration_days not in self.available_durations:
                available_options = ", ".join(map(str, sorted(self.available_durations)))
                raise ValueError(f"Duration must be one of: {available_options} days")

        return True
    
    def get_available_durations(self):
        """Get available duration options for this product"""
        # Grazuri products and Imara allow any duration within min-max range
        if self.is_imara_product() or self.is_grazuri_product():
            return list(range(self.get_min_duration(), self.get_max_duration() + 1))
        elif self.available_durations:
            return sorted(self.available_durations)
        else:
            # Return a range from min to max if no specific options are set
            return list(range(self.get_min_duration(), self.get_max_duration() + 1))

    def get_duration_options_display(self):
        """Get human-readable duration options"""
        if self.is_imara_product() or self.is_grazuri_product():
            return f"Any duration between {self.get_min_duration()}-{self.get_max_duration()} days"
        elif self.available_durations:
            options = sorted(self.available_durations)
            return ", ".join([f"{days} days" for days in options])
        else:
            return f"{self.get_min_duration()}-{self.get_max_duration()} days"
    
    def validate_repayment_method(self, method):
        """Validate if repayment method is available for this product"""
        if method not in self.available_repayment_methods:
            raise ValueError(f"Repayment method {method} is not available for this product")
        return True

    def to_dict(self):
        """Get product details with current settings values"""
        return {
            'id': str(self.id),
            'name': self.name,
            'product_type': self.product_type,
            'description': self.description,
            'gl_code': self.gl_code,
            'grazuri_account_type': self.grazuri_account_type,
            'min_amount': float(self.get_min_amount()),
            'max_amount': float(self.get_max_amount()),
            'interest_rate': float(self.get_interest_rate()),
            'processing_fee': float(self.get_processing_fee()),
            'late_payment_penalty': float(self.get_late_payment_penalty()),
            'duration_months': self.duration_months,
            'min_duration': self.get_min_duration(),
            'max_duration': self.get_max_duration(),
            'available_durations': self.available_durations,
            'available_repayment_methods': self.available_repayment_methods,
            'requires_guarantor': self.requires_guarantor,
            'requires_collateral': self.requires_collateral,
            'is_active': self.is_active,
            'is_grazuri_product': self.is_grazuri_product(),
        }


class LoanApplication(models.Model):
    """
    Loan application model with approval workflow
    """
    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('under_review', 'Under Review'),
        ('approved', 'Approved'),
        ('rejected', 'Rejected'),
        ('disbursed', 'Disbursed'),
        ('cancelled', 'Cancelled'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    application_number = models.CharField(max_length=20, unique=True)
    
    # Applicant
    borrower = models.ForeignKey(User, on_delete=models.CASCADE, related_name='loan_applications')
    loan_product = models.ForeignKey(LoanProduct, on_delete=models.CASCADE)
    
    # Staff references
    loan_officer = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='assigned_applications')
    approved_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='approved_applications')
    disbursed_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='disbursed_applications')
    
    # Loan details
    requested_amount = models.DecimalField(max_digits=12, decimal_places=2)
    requested_duration = models.PositiveIntegerField(help_text="Duration in days")
    purpose = models.TextField()
    repayment_method = models.CharField(max_length=20, choices=[('daily', 'Daily'), ('weekly', 'Weekly'), ('monthly', 'Monthly')], default='monthly')
    
    # Calculated fields
    interest_amount = models.DecimalField(max_digits=12, decimal_places=2, blank=True, null=True)
    processing_fee_amount = models.DecimalField(max_digits=12, decimal_places=2, blank=True, null=True)
    total_amount = models.DecimalField(max_digits=12, decimal_places=2, blank=True, null=True)
    
    # Status and workflow
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    submitted_at = models.DateTimeField(auto_now_add=True)
    reviewed_by = models.ForeignKey(
        User, 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True, 
        related_name='reviewed_applications'
    )
    reviewed_at = models.DateTimeField(blank=True, null=True)
    approval_notes = models.TextField(blank=True, null=True)
    
    # Documents
    supporting_documents = models.FileField(upload_to='loan_documents/', blank=True, null=True)
    
    # Auto-approval
    auto_approved = models.BooleanField(default=False)
    credit_score = models.PositiveIntegerField(blank=True, null=True)
    
    class Meta:
        db_table = 'loan_applications'
        ordering = ['-submitted_at']
    
    def __str__(self):
        return f"Application {self.application_number} - {self.borrower.get_full_name()}"
    
    def save(self, *args, **kwargs):
        if not self.application_number:
            # Generate application number
            last_app = LoanApplication.objects.order_by('-submitted_at').first()
            if last_app and last_app.application_number:
                try:
                    last_num = int(last_app.application_number.split('-')[1])
                    self.application_number = f"APP-{last_num + 1:06d}"
                except (IndexError, ValueError):
                    # If there's an error parsing the last number, generate a timestamp-based number
                    self.application_number = f"APP-{int(get_current_datetime().timestamp()):06d}"
            else:
                self.application_number = "APP-000001"
        
        # Calculate amounts if not set
        if not self.interest_amount:
            # Convert days to months for interest calculation
            months = max(1, self.requested_duration / 30)  # Convert days to months, minimum 1
            self.interest_amount = self.loan_product.calculate_interest(
                self.requested_amount, 
                months
            )
        
        if not self.processing_fee_amount:
            # Calculate months for processing fee
            months = max(1, self.requested_duration / 30)  # Convert days to months, minimum 1
            self.processing_fee_amount = self.loan_product.calculate_processing_fee(
                self.requested_amount, months
            )
        
        if not self.total_amount:
            # Total amount excludes registration fee (now handled at client level)
            self.total_amount = self.requested_amount + self.interest_amount + self.processing_fee_amount
        
        super().save(*args, **kwargs)
    
    def approve(self, approved_by, notes="", disbursement_date=None):
        """Approve a loan application and create a loan record"""
        from django.db import transaction
        
        # Check if borrower is blacklisted, suspended, or inactive
        if self.borrower.status in ['blacklisted', 'suspended', 'inactive']:
            raise ValueError(f'Cannot approve loan application for {self.borrower.status} client')
        
        # Check if already approved and has a loan
        if self.status == 'approved':
            try:
                existing_loan = self.loan
                return existing_loan  # Return existing loan if it exists
            except Loan.DoesNotExist:
                # Application is approved but no loan exists, continue to create loan
                pass
        
        # Use transaction to ensure atomicity
        with transaction.atomic():
            # Update application status
            self.status = 'approved'
            self.reviewed_by = approved_by
            self.reviewed_at = get_current_datetime()
            self.approval_notes = notes
            self.save()
            
            # Use provided disbursement date or default to now
            if disbursement_date is None:
                disbursement_date = get_current_datetime()
            
            # Create loan record
            # Always calculate total_amount to ensure it's correct (don't trust application.total_amount)
            calculated_total = self.requested_amount + self.interest_amount + self.processing_fee_amount
            
            loan = Loan.objects.create(
                application=self,
                borrower=self.borrower,
                principal_amount=self.requested_amount,
                interest_amount=self.interest_amount,
                processing_fee=self.processing_fee_amount,
                total_amount=calculated_total,  # Use calculated total, not self.total_amount
                disbursement_date=disbursement_date,
                due_date=disbursement_date + timezone.timedelta(days=self.requested_duration),
                duration_days=self.requested_duration,
                status='active'  # Set initial status as active
            )
            
            return loan
    
    def reject(self, rejected_by, notes=""):
        self.status = 'rejected'
        self.reviewed_by = rejected_by
        self.reviewed_at = get_current_datetime()
        self.approval_notes = notes
        self.save()
    
    def calculate_loan_amounts(self):
        """Recalculate interest, processing fee, and total amounts based on current values"""
        # Use custom rates if set, otherwise use product rates
        interest_rate = getattr(self, 'custom_interest_rate', self.loan_product.interest_rate)
        processing_fee_rate = getattr(self, 'custom_processing_fee', self.loan_product.processing_fee)
        
        # Calculate interest amount
        self.interest_amount = (self.requested_amount * interest_rate / Decimal('100'))
        
        # Calculate processing fee amount
        self.processing_fee_amount = (self.requested_amount * processing_fee_rate / Decimal('100'))
        
        # Calculate total amount
        self.total_amount = self.requested_amount + self.interest_amount + self.processing_fee_amount

    def calculate_risk_score(self):
        """Calculate a risk score from 0-100 based on various factors"""
        score = Decimal('0')
        max_score = Decimal('0')
        
        # Credit Score (max 30 points)
        if self.credit_score:
            max_score += Decimal('30')
            if self.credit_score >= 700:
                score += Decimal('30')
            elif self.credit_score >= 600:
                score += Decimal('20')
            elif self.credit_score >= 500:
                score += Decimal('10')
        
        # Payment History (max 30 points)
        total_loans = self.borrower.loans.count()
        if total_loans > 0:
            max_score += Decimal('30')
            paid_loans = self.borrower.loans.filter(status='paid').count()
            payment_ratio = Decimal(str(paid_loans)) / Decimal(str(total_loans))
            if payment_ratio == Decimal('1'):
                score += Decimal('30')
            elif payment_ratio >= Decimal('0.7'):
                score += Decimal('20')
            elif payment_ratio >= Decimal('0.5'):
                score += Decimal('10')
        
        # Debt-to-Income Ratio (max 20 points)
        if self.borrower.monthly_income:
            max_score += Decimal('20')
            dti_ratio = (self.total_amount / self.borrower.monthly_income) * Decimal('100')
            if dti_ratio <= Decimal('40'):
                score += Decimal('20')
            elif dti_ratio <= Decimal('50'):
                score += Decimal('10')
        
        # Loan Amount vs Income (max 10 points)
        if self.borrower.monthly_income:
            max_score += Decimal('10')
            amount_ratio = self.requested_amount / self.borrower.monthly_income
            if amount_ratio <= Decimal('2'):
                score += Decimal('10')
            elif amount_ratio <= Decimal('3'):
                score += Decimal('5')
        
        # Previous Defaults (max 10 points)
        max_score += Decimal('10')
        if not self.borrower.loans.filter(status='defaulted').exists():
            score += Decimal('10')
        
        # Return normalized score
        return (score / max_score * Decimal('100')) if max_score > Decimal('0') else Decimal('0')

    def get_risk_assessment(self):
        """Get a comprehensive risk assessment"""
        score = self.calculate_risk_score()
        
        assessment = {
            'score': score,
            'level': 'low' if score >= Decimal('80') else 'medium' if score >= Decimal('60') else 'high',
            'factors': []
        }
        
        # Credit Score
        if self.credit_score:
            assessment['factors'].append({
                'name': 'Credit Score',
                'value': self.credit_score,
                'status': 'good' if self.credit_score >= 700 else 'fair' if self.credit_score >= 600 else 'poor'
            })
        
        # Payment History
        total_loans = self.borrower.loans.count()
        if total_loans > 0:
            paid_loans = self.borrower.loans.filter(status='paid').count()
            payment_ratio = Decimal(str(paid_loans)) / Decimal(str(total_loans))
            assessment['factors'].append({
                'name': 'Payment History',
                'value': f"{paid_loans}/{total_loans} loans paid",
                'status': 'good' if payment_ratio == Decimal('1') else 'fair' if payment_ratio >= Decimal('0.7') else 'poor'
            })
        
        # Debt-to-Income Ratio
        if self.borrower.monthly_income:
            dti_ratio = (self.total_amount / self.borrower.monthly_income) * Decimal('100')
            assessment['factors'].append({
                'name': 'Debt-to-Income Ratio',
                'value': f"{dti_ratio:.1f}%",
                'status': 'good' if dti_ratio <= Decimal('40') else 'fair' if dti_ratio <= Decimal('50') else 'poor'
            })
        
        # Previous Defaults
        defaults = self.borrower.loans.filter(status='defaulted').count()
        if defaults > 0:
            assessment['factors'].append({
                'name': 'Previous Defaults',
                'value': defaults,
                'status': 'poor'
            })
        
        return assessment


class Loan(models.Model):
    """
    Active loan after approval and disbursement
    """
    STATUS_CHOICES = [
        ('active', 'Active'),
        ('paid', 'Paid'),
        ('defaulted', 'Defaulted'),
        ('rolled_over', 'Rolled Over'),
        ('written_off', 'Written Off'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    loan_number = models.CharField(max_length=20, unique=True)
    
    # Application reference
    application = models.OneToOneField(LoanApplication, on_delete=models.CASCADE)
    borrower = models.ForeignKey(User, on_delete=models.CASCADE, related_name='loans')
    
    # Staff references
    loan_officer = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='managed_loans')
    approved_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='approved_loans')
    disbursed_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='disbursed_loans')
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='created_loans')
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='updated_loans')
    
    # Loan details
    principal_amount = models.DecimalField(max_digits=12, decimal_places=2)
    interest_amount = models.DecimalField(max_digits=12, decimal_places=2)
    processing_fee = models.DecimalField(max_digits=12, decimal_places=2)
    registration_fee = models.DecimalField(max_digits=12, decimal_places=2, default=0)
    total_amount = models.DecimalField(max_digits=12, decimal_places=2)
    outstanding_balance = models.DecimalField(max_digits=10, decimal_places=2, blank=True, null=True)
    
    # Terms
    disbursement_date = models.DateTimeField()
    due_date = models.DateTimeField()
    duration_days = models.PositiveIntegerField()
    
    # Status
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='active')
    
    # Soft delete
    is_deleted = models.BooleanField(default=False)
    deleted_at = models.DateTimeField(blank=True, null=True)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_loans')
    
    # Repayment tracking
    # Note: amount_paid is now a calculated property (see below)
    # Keeping this field for backward compatibility but it's updated automatically
    _amount_paid_cache = models.DecimalField(max_digits=12, decimal_places=2, default=0, db_column='amount_paid')
    last_payment_date = models.DateTimeField(blank=True, null=True)
    
    # Rollover
    is_rolled_over = models.BooleanField(default=False)
    original_loan = models.ForeignKey('self', on_delete=models.SET_NULL, null=True, blank=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    # Custom managers
    objects = models.Manager()  # Default manager (includes deleted)
    active_objects = ActiveLoanManager()  # Excludes deleted loans
    
    class Meta:
        db_table = 'loans'
        ordering = ['-created_at']
    
    def __str__(self):
        return f"Loan {self.loan_number} - {self.borrower.get_full_name()}"
    
    def soft_delete(self, deleted_by=None):
        """Soft delete the loan"""
        self.is_deleted = True
        self.deleted_at = get_current_datetime()
        self.deleted_by = deleted_by
        self.save()
    
    def restore(self):
        """Restore a soft-deleted loan"""
        self.is_deleted = False
        self.deleted_at = None
        self.deleted_by = None
        self.save()
    
    def validate_status_transition(self, new_status):
        """Validate if status transition is allowed"""
        valid_transitions = {
            'active': ['paid', 'defaulted', 'rolled_over', 'written_off'],
            'paid': ['active'],  # Paid loans can be changed back to active (e.g., payment reversal)
            'defaulted': ['paid', 'active', 'written_off'],  # Defaulted loans can be paid off, reactivated, or written off
            'rolled_over': [],  # Rolled over loans cannot change status - they are final
            'written_off': []  # Written off loans cannot change status
        }
        
        if self.status and new_status != self.status:
            allowed_statuses = valid_transitions.get(self.status, [])
            if new_status not in allowed_statuses:
                raise ValueError(
                    f'Invalid status transition from {self.status} to {new_status}. '
                    f'Allowed transitions: {allowed_statuses}'
                )
                
        # Prevent loans from being both active and rolled over
        if new_status == 'active' and self.is_rolled_over:
            raise ValueError("A rolled over loan cannot be set back to active status")
            
        return True

    def save(self, *args, **kwargs):
        from django.db import transaction, IntegrityError
        import time
        import random
        
        # Validate status transition if status is being changed
        if self.pk:  # Only validate for existing loans
            try:
                old_loan = Loan.objects.get(pk=self.pk)
                if old_loan.status != self.status:
                    self.validate_status_transition(self.status)
            except Loan.DoesNotExist:
                pass  # New loan, no validation needed
        
        if not self.loan_number:
            # Generate loan number with retry logic to handle race conditions
            max_attempts = 10
            for attempt in range(max_attempts):
                try:
                    with transaction.atomic():
                        # Get all existing loan numbers and find the next available
                        existing_numbers = set()
                        for loan_number in Loan.objects.values_list('loan_number', flat=True):
                            if loan_number and loan_number.startswith('LOAN-'):
                                try:
                                    num = int(loan_number.split('-')[1])
                                    existing_numbers.add(num)
                                except (IndexError, ValueError):
                                    continue
                        
                        # Find the next available number
                        next_num = 1
                        while next_num in existing_numbers:
                            next_num += 1
                        
                        self.loan_number = f"LOAN-{next_num:06d}"
                        
                        # Try to save with the generated loan number
                        super().save(*args, **kwargs)
                        break  # Success, exit the retry loop
                        
                except IntegrityError as e:
                    if 'loan_number' in str(e) and attempt < max_attempts - 1:
                        # Loan number collision, wait a bit and retry
                        time.sleep(0.1 + random.uniform(0, 0.1))  # Random backoff
                        continue
                    else:
                        # Different integrity error or max attempts reached
                        raise
        else:
            # Loan number already set, just save
            super().save(*args, **kwargs)
    
    @property
    def calculated_total_amount(self):
        """Calculate total amount using current system rates (principal + interest + processing fee)"""
        from decimal import Decimal
        current_interest = self.get_display_interest_amount()
        current_processing_fee = self.get_display_processing_fee_amount()
        return self.principal_amount + current_interest + current_processing_fee
    
    @property
    def amount_paid(self):
        """
        Calculate the total amount paid on this loan using the calculation service.
        
        This property sums all repayment records for accurate tracking.
        Validates: Requirements 3.1, 3.3, 13.3
        """
        from reports.calculation_service import LoanCalculationService
        return LoanCalculationService.calculate_amount_paid(self)
    
    @property
    def outstanding_amount(self):
        """
        Calculate the outstanding amount on this loan using the calculation service.
        
        Outstanding = Total Amount + Total Penalties - Amount Paid
        Validates: Requirements 3.4, 13.2
        """
        from reports.calculation_service import LoanCalculationService
        return LoanCalculationService.calculate_outstanding_amount(self)
    
    @property
    def is_overdue(self):
        """Check if loan is overdue based on repayment method"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.is_loan_in_arrears(self)
    
    @property
    def days_overdue(self):
        """Get days overdue based on repayment method"""
        from .repayment_scheduler import RepaymentScheduler
        arrears_summary = RepaymentScheduler.get_arrears_summary(self)
        return arrears_summary['days_overdue']
    
    def get_simple_days_overdue(self):
        """
        Calculate simple days overdue as (current_date - due_date).days
        
        This is a straightforward calculation for display purposes.
        Validates: Requirements 5.5
        
        Returns:
            int: Number of days overdue, or 0 if not overdue
        """
        from django.utils import timezone
        
        current_date = timezone.now().date()
        due_date = self.due_date.date() if hasattr(self.due_date, 'date') else self.due_date
        
        if due_date >= current_date:
            return 0
        
        if self.status != 'active' or self.outstanding_amount <= 0:
            return 0
        
        return (current_date - due_date).days
    
    @property
    def days_missed(self):
        """Get number of days with missed payments"""
        from .repayment_scheduler import RepaymentScheduler
        arrears_summary = RepaymentScheduler.get_arrears_summary(self)
        return arrears_summary.get('days_missed', 0)
    
    @property
    def repayment_method(self):
        """Get the repayment method for this loan"""
        if hasattr(self, 'application') and self.application:
            return self.application.repayment_method
        return 'monthly'  # Default fallback
    
    @property
    def expected_payment_amount(self):
        """Get expected payment amount per period"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.calculate_expected_payment_amount(self)
    
    @property
    def payment_frequency_days(self):
        """Get payment frequency in days"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.get_payment_period_days(self.repayment_method)
    
    @property
    def next_payment_due_date(self):
        """Get next payment due date"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.get_next_payment_due_date(self)
    
    @property
    def payment_status(self):
        """Get current payment status"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.get_payment_status(self)
    
    def get_arrears_summary(self):
        """Get comprehensive arrears summary"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.get_arrears_summary(self)
    
    def is_in_arrears(self):
        """
        Determine if this loan is in arrears.
        
        A loan is in arrears if:
        - Due date is in the past (due_date < current_date)
        - Outstanding amount is greater than zero
        
        Validates: Requirements 14.1
        
        Returns:
            bool: True if loan is in arrears, False otherwise
        """
        from django.utils import timezone
        from decimal import Decimal
        
        # Get current date
        current_date = timezone.now().date()
        
        # Get due date
        due_date = self.due_date.date() if hasattr(self.due_date, 'date') else self.due_date
        
        # Check if past due date
        if due_date >= current_date:
            return False
        
        # Check if outstanding amount > 0
        outstanding = self.outstanding_amount
        if outstanding <= Decimal('0.00'):
            return False
        
        return True
    
    def get_display_interest_rate(self):
        """Get current interest rate from system settings for display"""
        return self.application.loan_product.get_interest_rate()
    
    def get_effective_interest_rate(self):
        """Calculate the effective interest rate based on actual interest amount stored"""
        from decimal import Decimal
        if self.principal_amount == 0:
            return Decimal('0.00')
        
        # Calculate months
        months = max(1, self.duration_days / 30)
        
        # Effective rate = (interest_amount / principal_amount) / months * 100
        effective_rate = (self.interest_amount / self.principal_amount) / Decimal(str(months)) * Decimal('100')
        return effective_rate.quantize(Decimal('0.01'))
    
    def get_effective_processing_fee_rate(self):
        """Calculate the effective processing fee rate based on actual processing fee stored"""
        from decimal import Decimal
        if self.principal_amount == 0:
            return Decimal('0.00')
        
        # For Boost Plus, processing fee is monthly
        if self.application.loan_product.product_type == 'boost_plus':
            months = max(1, self.duration_days / 30)
            effective_rate = (self.processing_fee / self.principal_amount) / Decimal(str(months)) * Decimal('100')
        else:
            # For other products, processing fee is one-time
            effective_rate = (self.processing_fee / self.principal_amount) * Decimal('100')
        
        return effective_rate.quantize(Decimal('0.01'))
    
    def get_display_processing_fee_rate(self):
        """Get current processing fee rate from system settings for display"""
        return self.application.loan_product.get_processing_fee()
    
    def get_display_processing_fee_amount(self):
        """Calculate processing fee using current system rate for display"""
        from decimal import Decimal
        current_rate = self.get_display_processing_fee_rate()
        months = max(1, self.duration_days / 30)
        
        # Calculate base processing fee
        base_fee = self.principal_amount * (Decimal(str(current_rate)) / Decimal('100'))
        
        # For Boost Plus, processing fee is charged monthly like interest
        if self.application.loan_product.product_type == 'boost_plus':
            return base_fee * Decimal(str(months))
        
        # For other products, processing fee is one-time
        return base_fee
    
    def get_display_interest_amount(self):
        """Calculate interest using current system rate for display"""
        from decimal import Decimal
        current_rate = self.get_display_interest_rate()
        months = max(1, self.duration_days / 30)
        return self.principal_amount * (Decimal(str(current_rate)) / Decimal('100')) * Decimal(str(months))
    
    def get_payment_schedule(self):
        """Get payment schedule for this loan"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.generate_payment_schedule(self)
    
    def get_missed_payment_periods(self):
        """Get missed payment periods"""
        from .repayment_scheduler import RepaymentScheduler
        return RepaymentScheduler.get_missed_payment_periods(self)
    
    @property
    def total_penalties(self):
        """Get total penalty charges applied to this loan"""
        return self.penalty_charges.aggregate(
            total=models.Sum('amount')
        )['total'] or Decimal('0.00')
    
    def calculate_penalty(self):
        """
        Calculate penalty for overdue payments.
        
        Validates: Requirements 9.4, 9.5
        """
        if self.is_overdue:
            daily_penalty_rate = Decimal(str(self.application.loan_product.get_late_payment_penalty())) / Decimal('100')
            result = self.outstanding_amount * daily_penalty_rate * self.days_overdue
            # Round to 2 decimal places
            return result.quantize(Decimal('0.01'))
        return Decimal('0.00')
    
    def update_status_based_on_payments(self):
        """Update loan status based on current payment status"""
        if self.status != 'active':
            return  # Only update active loans
        
        # Update the cache field for amount_paid (the property calculates it dynamically)
        from django.db.models import Sum
        total_paid = self.repayments.aggregate(
            total=Sum('amount')
        )['total'] or Decimal('0.00')
        
        self._amount_paid_cache = total_paid
        
        # Check if fully paid using the property
        if self.amount_paid >= self.total_amount:
            self.status = 'paid'
        
        self.save()
    
    def mark_as_defaulted(self, reason=""):
        """Mark loan as defaulted with validation"""
        if self.status != 'active':
            raise ValueError(f'Cannot mark {self.status} loan as defaulted')
        
        self.status = 'defaulted'
        self.save()
        
        # Log the action
        from utils.models import AuditLog
        try:
            AuditLog.objects.create(
                action='status_change',
                model_name='Loan',
                object_id=str(self.id),
                description=f'Loan {self.loan_number} marked as defaulted. Reason: {reason}'
            )
        except:
            pass  # Don't fail if audit log creation fails
    
    def write_off(self, reason=""):
        """Write off a loan with validation"""
        if self.status not in ['active', 'defaulted']:
            raise ValueError(f'Cannot write off {self.status} loan')
        
        self.status = 'written_off'
        self.save()
        
        # Log the action
        from utils.models import AuditLog
        try:
            AuditLog.objects.create(
                action='status_change',
                model_name='Loan',
                object_id=str(self.id),
                description=f'Loan {self.loan_number} written off. Reason: {reason}'
            )
        except:
            pass  # Don't fail if audit log creation fails

    def recalculate_amounts(self):
        """
        Recalculate total_amount and outstanding_amount based on current values.

        This method ensures data integrity by:
        1. Recalculating total_amount = principal_amount + interest_amount + processing_fee
        2. Outstanding_amount is automatically recalculated via the property

        Note: This method does NOT save the model. Call save() separately if needed.
        The pre_save signal will also recalculate amounts automatically.

        Validates: Requirements 3.11, 9.1, 9.2, 9.6, 9.7, 9.8
        """
        from decimal import Decimal

        # Recalculate total_amount
        self.total_amount = self.principal_amount + self.interest_amount + self.processing_fee

        # Round to 2 decimal places for consistency
        self.total_amount = self.total_amount.quantize(Decimal('0.01'))

        # Note: outstanding_amount is a property and will be recalculated automatically
        # Do NOT save here - let the caller decide when to save

    
    def get_amortization_schedule(self):
        """Generate amortization schedule for the loan"""
        from decimal import Decimal
        from datetime import datetime, timedelta
        import calendar
        
        schedule = []
        principal = self.principal_amount
        # Use current display methods for accurate calculations
        interest_amount = self.get_display_interest_amount()
        processing_fee_amount = self.get_display_processing_fee_amount()
        
        # Total amount includes principal + interest + processing fee
        total_amount = principal + interest_amount + processing_fee_amount
        
        # Calculate payment frequency
        repayment_method = self.repayment_method
        if repayment_method == 'daily':
            frequency_days = 1
        elif repayment_method == 'weekly':
            frequency_days = 7
        else:  # monthly
            frequency_days = 30
        
        # Calculate number of payments
        total_days = self.duration_days
        num_payments = max(1, int(total_days / frequency_days))
        
        # Calculate per-payment amounts
        interest_per_payment = interest_amount / num_payments
        principal_per_payment = principal / num_payments
        
        # Processing fee: spread evenly across all payments OR one-time on first payment
        # For Boost product, it's typically one-time on first payment
        processing_fee_per_payment = processing_fee_amount if num_payments == 1 else Decimal('0.00')
        if num_payments > 1:
            # One-time processing fee on first payment
            processing_fee_first = processing_fee_amount
        else:
            processing_fee_first = processing_fee_amount
        
        current_date = self.disbursement_date
        remaining_balance = total_amount
        cumulative_interest = Decimal('0.00')
        cumulative_processing_fee = Decimal('0.00')
        
        for payment_num in range(1, num_payments + 1):
            # Calculate payment date - start from NEXT period, not disbursement date
            if repayment_method == 'daily':
                payment_date = current_date + timedelta(days=payment_num)
            elif repayment_method == 'weekly':
                payment_date = current_date + timedelta(weeks=payment_num)
            else:  # monthly
                # Add months properly - start from next month
                year = current_date.year
                month = current_date.month + payment_num
                while month > 12:
                    month -= 12
                    year += 1
                # Get the last day of the month if the original day doesn't exist
                last_day = calendar.monthrange(year, month)[1]
                day = min(current_date.day, last_day)
                payment_date = datetime(year, month, day, current_date.hour, current_date.minute, current_date.second)
            
            # Calculate amounts for this payment
            if payment_num == num_payments:
                # Last payment - adjust for rounding
                interest_this_payment = interest_amount - cumulative_interest
                processing_fee_this_payment = processing_fee_amount - cumulative_processing_fee
                principal_this_payment = remaining_balance - interest_this_payment - processing_fee_this_payment
            elif payment_num == 1:
                # First payment includes processing fee
                interest_this_payment = interest_per_payment
                principal_this_payment = principal_per_payment
                processing_fee_this_payment = processing_fee_first
            else:
                # Regular payments
                interest_this_payment = interest_per_payment
                principal_this_payment = principal_per_payment
                processing_fee_this_payment = Decimal('0.00')
            
            # Calculate total payment
            total_payment = principal_this_payment + interest_this_payment + processing_fee_this_payment
            
            # Update remaining balance
            remaining_balance -= total_payment
            cumulative_interest += interest_this_payment
            cumulative_processing_fee += processing_fee_this_payment
            
            schedule.append({
                'payment_number': payment_num,
                'payment_date': payment_date,
                'beginning_balance': remaining_balance + total_payment,
                'scheduled_payment': total_payment,
                'principal': principal_this_payment,
                'interest': interest_this_payment,
                'processing_fee': processing_fee_this_payment,
                'ending_balance': remaining_balance,
                'cumulative_interest': cumulative_interest,
            })
        
        return schedule


class PenaltyCharge(models.Model):
    """
    Track penalty charges applied to loans
    """
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    loan = models.ForeignKey(Loan, on_delete=models.CASCADE, related_name='penalty_charges')
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    penalty_rate = models.DecimalField(max_digits=5, decimal_places=2, help_text="Penalty rate used")
    days_overdue = models.PositiveIntegerField(help_text="Number of days overdue when penalty was applied")
    outstanding_amount = models.DecimalField(max_digits=12, decimal_places=2, help_text="Outstanding amount when penalty was calculated")
    
    # Manual vs Automatic
    is_automatic = models.BooleanField(default=True, help_text="Whether penalty was applied automatically")
    applied_by = models.ForeignKey(
        User,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='penalties_applied',
        help_text="User who applied manual penalty"
    )
    reason = models.TextField(blank=True, help_text="Reason for manual penalty or additional notes")
    
    # Dates
    penalty_date = models.DateField(help_text="Date the penalty applies to", null=True, blank=True)
    
    # Timestamps
    applied_date = models.DateTimeField(auto_now_add=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        db_table = 'penalty_charges'
        ordering = ['-applied_date']
    
    def __str__(self):
        return f"Penalty {self.amount} for loan {self.loan.loan_number}"


class Repayment(models.Model):
    """
    Loan repayment tracking
    """
    PAYMENT_METHODS = [
        ('mpesa', 'M-Pesa'),
        ('bank', 'Bank Transfer'),
        ('cash', 'Cash'),
        ('cheque', 'Cheque'),
    ]
    
    PAYMENT_SOURCES = [
        ('automatic', 'Automatic M-Pesa'),
        ('manual', 'Manual Entry'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    loan = models.ForeignKey(Loan, on_delete=models.CASCADE, related_name='repayments')
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    payment_method = models.CharField(max_length=20, choices=PAYMENT_METHODS)
    payment_source = models.CharField(max_length=20, choices=PAYMENT_SOURCES, default='manual')
    
    # M-Pesa details
    mpesa_transaction_id = models.CharField(max_length=50, blank=True, null=True)
    mpesa_phone_number = models.CharField(max_length=17, blank=True, null=True)
    
    # Receipt
    receipt_number = models.CharField(max_length=20, unique=True)
    
    # Timestamps
    payment_date = models.DateTimeField(default=timezone.now)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        db_table = 'repayments'
        ordering = ['-payment_date']
    
    def __str__(self):
        return f"Repayment {self.receipt_number} - {self.loan.loan_number}"
    
    def save(self, *args, **kwargs):
        from django.db import transaction, IntegrityError
        import time
        import random
        
        if not self.receipt_number:
            # Generate receipt number with retry logic to handle race conditions
            max_attempts = 10
            from utils.models import Receipt
            
            for attempt in range(max_attempts):
                try:
                    # Use a database-level approach to get next number atomically
                    with transaction.atomic():
                        # CRITICAL: Check BOTH Repayment and Receipt tables for existing numbers
                        existing_numbers = set()
                        existing_receipt_numbers = set()  # For exact string matches (timestamp formats)
                        
                        # Check Repayment table
                        for receipt_num in Repayment.objects.values_list('receipt_number', flat=True):
                            if receipt_num and receipt_num.startswith('RCP-'):
                                existing_receipt_numbers.add(receipt_num)  # Track exact matches
                                try:
                                    num = int(receipt_num.split('-')[1])
                                    if num < 1000000:  # Only sequential numbers (6 digits or less)
                                        existing_numbers.add(num)
                                except (IndexError, ValueError, OverflowError):
                                    continue
                        
                        # Check Receipt table (this was missing and causing duplicates!)
                        for receipt_num in Receipt.objects.values_list('receipt_number', flat=True):
                            if receipt_num and receipt_num.startswith('RCP-'):
                                existing_receipt_numbers.add(receipt_num)  # Track exact matches
                                try:
                                    num = int(receipt_num.split('-')[1])
                                    if num < 1000000:  # Only sequential numbers (6 digits or less)
                                        existing_numbers.add(num)
                                except (IndexError, ValueError, OverflowError):
                                    continue
                        
                        # Find the next available sequential number
                        next_num = 1
                        while next_num in existing_numbers:
                            next_num += 1
                        
                        proposed_receipt_number = f"RCP-{next_num:06d}"
                        
                        # BLACKLIST: Known problematic receipt numbers that must NEVER be used
                        BLACKLISTED_RECEIPT_NUMBERS = ['RCP-20251002073658']
                        
                        # Double-check: ensure this exact receipt number doesn't exist (handles any format)
                        # Also check Receipt table directly to catch any format
                        # CRITICAL: Also explicitly avoid blacklisted receipt numbers
                        while (proposed_receipt_number in existing_receipt_numbers or
                               Receipt.objects.filter(receipt_number=proposed_receipt_number).exists() or
                               proposed_receipt_number in BLACKLISTED_RECEIPT_NUMBERS):
                            next_num += 1
                            proposed_receipt_number = f"RCP-{next_num:06d}"
                        
                        # One final check before assigning
                        if proposed_receipt_number in BLACKLISTED_RECEIPT_NUMBERS:
                            # Force skip blacklisted numbers
                            next_num += 1
                            proposed_receipt_number = f"RCP-{next_num:06d}"
                        
                        self.receipt_number = proposed_receipt_number
                        
                        # Try to save with the generated receipt number
                        super().save(*args, **kwargs)
                        break  # Success, exit the retry loop
                        
                except IntegrityError as e:
                    if 'receipt_number' in str(e) and attempt < max_attempts - 1:
                        # Receipt number collision, wait a bit and retry
                        time.sleep(0.1 + random.uniform(0, 0.1))  # Random backoff
                        continue
                    else:
                        # Different integrity error or max attempts reached
                        raise
        else:
            # Receipt number already set - but verify it doesn't exist in Receipt table
            # This handles cases where repayment was created with a duplicate number from a previous attempt
            from utils.models import Receipt
            if Receipt.objects.filter(receipt_number=self.receipt_number).exists():
                # Receipt number already exists - clear it and regenerate
                self.receipt_number = None
                # Recursively call save to regenerate (but limit recursion)
                if not kwargs.get('_regenerating_receipt', False):
                    kwargs['_regenerating_receipt'] = True
                    self.save(*args, **kwargs)
                    return
            super().save(*args, **kwargs)
        
        # Create or update Receipt record
        self.create_or_update_receipt()
        
        # Update loan payment tracking cache by recalculating total from all repayments
        from django.db.models import Sum
        self.loan._amount_paid_cache = self.loan.repayments.aggregate(
            total=Sum('amount')
        )['total'] or Decimal('0.00')
        self.loan.last_payment_date = self.payment_date
        
        # Only update status if loan is currently active (use property for amount_paid)
        if self.loan.status == 'active' and self.loan.amount_paid >= self.loan.total_amount:
            self.loan.status = 'paid'
        
        self.loan.save()
    
    def create_or_update_receipt(self):
        """Create or update receipt for this repayment with correct individual balance calculation"""
        from utils.models import Receipt
        from decimal import Decimal
        from django.db import IntegrityError
        import time
        
        try:
            # Get all previous repayments for this loan (chronologically before this one)
            previous_repayments = self.loan.repayments.filter(
                payment_date__lt=self.payment_date
            ).order_by('payment_date', 'created_at')
            
            # Calculate total amount paid before this repayment (ensure Decimal)
            previous_total = Decimal('0')
            for rep in previous_repayments:
                if isinstance(rep.amount, Decimal):
                    previous_total += rep.amount
                else:
                    previous_total += Decimal(str(rep.amount))
            
            # Calculate balances for THIS specific repayment
            previous_balance = self.loan.total_amount - previous_total
            new_balance = previous_balance - self.amount
            
            # Check if receipt already exists for this repayment
            try:
                receipt = Receipt.objects.get(repayment=self)
                # Update existing receipt with correct balances
                receipt.previous_balance = previous_balance
                receipt.new_balance = new_balance
                receipt.amount_paid = self.amount
                receipt.payment_method = self.payment_method
                receipt.save()
                return receipt
            except Receipt.DoesNotExist:
                pass
            
            # Generate a unique receipt number (max 20 characters)
            # Format: RCP-{sequential_number} (e.g., RCP-000001, RCP-000123)
            # This ensures it fits in 20 chars and is unique
            max_attempts = 10
            from django.utils import timezone
            import random
            
            # Generate a unique sequential receipt number
            def generate_unique_receipt_number():
                """Generate a unique receipt number that fits in 20 characters"""
                # Try to find the highest sequential number
                try:
                    # Get all receipt numbers that match RCP-{6digits} format
                    existing_receipts = Receipt.objects.filter(
                        receipt_number__startswith='RCP-'
                    ).values_list('receipt_number', flat=True)
                    
                    max_num = 0
                    for rec_num in existing_receipts:
                        try:
                            parts = rec_num.split('-')
                            if len(parts) == 2:
                                num = int(parts[1])
                                if num > max_num:
                                    max_num = num
                        except (ValueError, IndexError):
                            continue
                    
                    # Start from max + 1, or 1 if no receipts exist
                    next_num = max_num + 1
                    
                    # Ensure it fits in 20 chars: "RCP-" (4) + up to 6 digits = max 10 chars
                    # But we'll use 6 digits to be safe: RCP-000001 = 10 chars
                    proposed = f"RCP-{next_num:06d}"
                    
                    # CRITICAL: Double-check that this exact receipt number doesn't exist
                    # (handles cases where receipt numbers are in different formats)
                    if Receipt.objects.filter(receipt_number=proposed).exists():
                        # If it exists, find next available
                        while Receipt.objects.filter(receipt_number=proposed).exists():
                            next_num += 1
                            proposed = f"RCP-{next_num:06d}"
                    
                    return proposed
                    
                except Exception:
                    # Fallback: use timestamp with shorter format
                    # RCP-{YYMMDDHHMMSS} = 4 + 12 = 16 chars (safe)
                    timestamp = timezone.now().strftime('%y%m%d%H%M%S')
                    return f"RCP-{timestamp}"
            
            # CRITICAL: IGNORE the repayment's receipt_number completely
            # Always generate a fresh, unique receipt number every time
            # This prevents any possibility of reusing a duplicate number
            # The repayment's receipt_number might be 'RCP-20251002073658' which already exists
            # So we MUST generate a new one and never use the repayment's existing number
            
            # BLACKLIST: Known problematic receipt numbers that must NEVER be used
            BLACKLISTED_RECEIPT_NUMBERS = ['RCP-20251002073658']
            
            # Explicitly clear any receipt_number to ensure we never accidentally use it
            original_receipt_number = self.receipt_number  # Store for reference only
            
            # CRITICAL: If the repayment's receipt_number is blacklisted, clear it immediately
            if original_receipt_number in BLACKLISTED_RECEIPT_NUMBERS:
                self.receipt_number = None
                original_receipt_number = None
            
            receipt_number = None  # Start with None to force generation
            
            for attempt in range(max_attempts):
                try:
                    # CRITICAL: Always generate a fresh receipt number to avoid any conflicts
                    # Don't trust the repayment's receipt_number - generate a new one every time
                    # This ensures we never reuse a receipt number that might already exist
                    from django.db import transaction as db_transaction
                    
                    with db_transaction.atomic():
                        # CRITICAL: Use UUID-based receipt numbers to guarantee uniqueness
                        # This avoids any possibility of generating duplicate timestamp-based numbers
                        import uuid
                        receipt_number = f"RCP-{str(uuid.uuid4()).replace('-', '')[:16]}"
                        
                        # Ensure it doesn't exist and isn't the problematic number
                        while (Receipt.objects.filter(receipt_number=receipt_number).exists() or
                               Repayment.objects.filter(receipt_number=receipt_number).exclude(id=self.id).exists() or
                               receipt_number in BLACKLISTED_RECEIPT_NUMBERS or
                               receipt_number == original_receipt_number):
                            receipt_number = f"RCP-{str(uuid.uuid4()).replace('-', '')[:16]}"
                        
                        # UUID is already generated above, so we just need to ensure it's unique
                        # The while loop above already handles this, so no additional checks needed here
                        
                        # Ensure receipt number is max 20 characters
                        if len(receipt_number) > 20:
                            receipt_number = receipt_number[:20]
                        
                        # FINAL CHECK: One more time before creating - this is the absolute last check
                        # Also explicitly avoid blacklisted receipt numbers
                        if (Receipt.objects.filter(receipt_number=receipt_number).exists() or 
                            receipt_number in BLACKLISTED_RECEIPT_NUMBERS or
                            receipt_number == original_receipt_number):
                            # Still exists or is blacklisted - generate UUID
                            import uuid
                            receipt_number = f"RCP-{str(uuid.uuid4()).replace('-', '')[:16]}"
                            while (Receipt.objects.filter(receipt_number=receipt_number).exists() or 
                                   receipt_number in BLACKLISTED_RECEIPT_NUMBERS or
                                   receipt_number == original_receipt_number):
                                receipt_number = f"RCP-{str(uuid.uuid4()).replace('-', '')[:16]}"
                        
                        # Update repayment's receipt_number to match (in memory only for now)
                        self.receipt_number = receipt_number
                        
                        # ABSOLUTE FINAL CHECK: Right before creating, verify one more time
                        # This is the absolute last safeguard
                        if receipt_number in BLACKLISTED_RECEIPT_NUMBERS:
                            raise ValueError(f"Cannot create receipt with blacklisted receipt number: {receipt_number}")
                        
                        # Try to create receipt - this will raise IntegrityError if duplicate
                        receipt = Receipt.objects.create(
                            repayment=self,
                            loan=self.loan,
                            borrower=self.loan.borrower,
                            receipt_number=receipt_number,  # Use our freshly generated number - NEVER use self.receipt_number
                            amount_paid=self.amount,
                            payment_method=self.payment_method,
                            payment_date=self.payment_date,
                            previous_balance=previous_balance,
                            new_balance=new_balance,
                        )
                        
                        # Now save the repayment's receipt_number to keep them in sync
                        # Only update if repayment has been saved to database (has pk)
                        if self.pk:
                            try:
                                self.save(update_fields=['receipt_number'])
                            except Exception:
                                # If save fails, that's okay - the receipt is created and that's what matters
                                pass
                        
                        return receipt
                    
                except IntegrityError as e:
                    if 'receipt_number' in str(e) and attempt < max_attempts - 1:
                        # Receipt number conflict - wait and generate completely new UUID-based number
                        time.sleep(0.2 * (attempt + 1))
                        # Generate completely new UUID-based receipt number for next attempt
                        import uuid
                        receipt_number = f"RCP-{str(uuid.uuid4()).replace('-', '')[:16]}"
                        # Ensure it doesn't exist and isn't blacklisted
                        while (Receipt.objects.filter(receipt_number=receipt_number).exists() or
                               receipt_number in BLACKLISTED_RECEIPT_NUMBERS or
                               receipt_number == original_receipt_number):
                            receipt_number = f"RCP-{str(uuid.uuid4()).replace('-', '')[:16]}"
                        continue
                    else:
                        # Different error or max attempts - log and return None
                        import logging
                        logger = logging.getLogger(__name__)
                        logger.error(f"Error creating receipt for repayment {self.id}: {str(e)}")
                        return None
                except Exception as e:
                    # Other errors - log and return None
                    import logging
                    logger = logging.getLogger(__name__)
                    logger.error(f"Error creating receipt for repayment {self.id}: {str(e)}")
                    return None
            
            # Max attempts reached
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"Failed to create receipt for repayment {self.id} after {max_attempts} attempts")
            return None
                
        except Exception as e:
            # Log the error but don't fail the repayment save
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"Error creating/updating receipt for repayment {self.id}: {str(e)}")
            return None


class RolloverRequest(models.Model):
    """
    Enhanced loan rollover requests with full loan parameters
    """
    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('approved', 'Approved'),
        ('rejected', 'Rejected'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    loan = models.ForeignKey(Loan, on_delete=models.CASCADE, related_name='rollover_requests')
    borrower = models.ForeignKey(User, on_delete=models.CASCADE)
    
    # Enhanced rollover details (like a new loan application)
    requested_amount = models.DecimalField(max_digits=12, decimal_places=2, help_text="New loan amount")
    requested_duration = models.PositiveIntegerField(help_text="New loan duration in days")
    requested_interest_rate = models.DecimalField(max_digits=5, decimal_places=2, blank=True, null=True, help_text="Custom interest rate %")
    requested_processing_fee = models.DecimalField(max_digits=12, decimal_places=2, blank=True, null=True, help_text="Custom processing fee")
    rollover_date = models.DateField(blank=True, null=True, help_text="Preferred rollover date")
    reason = models.TextField()
    rollover_fee = models.DecimalField(max_digits=12, decimal_places=2, blank=True, null=True)
    
    # Status
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    reviewed_by = models.ForeignKey(
        User, 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True, 
        related_name='reviewed_rollovers'
    )
    reviewed_at = models.DateTimeField(blank=True, null=True)
    review_notes = models.TextField(blank=True, null=True)
    
    # Timestamps
    requested_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        db_table = 'rollover_requests'
        ordering = ['-requested_at']
    
    def __str__(self):
        return f"Rollover for {self.loan.loan_number} - {self.status}"
    
    def approve(self, approved_by, notes="", new_amount=None, new_duration=None, new_interest_rate=None, new_processing_fee=None, disbursement_date=None):
        """Approve rollover and create a completely new active loan, removing the old one"""
        self.status = 'approved'
        self.reviewed_by = approved_by
        self.reviewed_at = get_current_datetime()
        self.review_notes = notes
        self.save()
        
        # Get rollover parameters
        rollover_fee = self.rollover_fee or Decimal('0.00')
        principal_amount = new_amount if new_amount is not None else getattr(self, 'requested_amount', self.loan.outstanding_amount)
        duration = new_duration if new_duration is not None else self.requested_duration
        
        # Calculate interest and fees for the new loan
        loan_product = self.loan.application.loan_product
        months = max(1, duration / 30)  # Convert days to months
        
        if new_interest_rate is not None:
            interest_amount = principal_amount * (new_interest_rate / Decimal('100')) * Decimal(str(months))
        else:
            interest_amount = loan_product.calculate_interest(principal_amount, months)
        
        if new_processing_fee is not None:
            processing_fee = new_processing_fee
        else:
            processing_fee = loan_product.calculate_processing_fee(principal_amount, months)
        
        total_amount = principal_amount + interest_amount + processing_fee
        
        # Create a new loan application for the rollover
        new_application = LoanApplication.objects.create(
            borrower=self.loan.borrower,
            loan_product=loan_product,
            requested_amount=principal_amount,
            requested_duration=duration,
            purpose=f'Rollover for loan {self.loan.loan_number}',
            repayment_method=self.loan.application.repayment_method,
            interest_amount=interest_amount,
            processing_fee_amount=processing_fee,
            total_amount=total_amount,
            status='active',  # New loan is active, not just approved
            reviewed_by=approved_by,
            reviewed_at=get_current_datetime(),
            approval_notes=f'Rollover approved for loan {self.loan.loan_number}. {notes}'
        )
        
        # Prioritize admin-entered disbursement date, then rollover_date from request, then default to now
        if disbursement_date is None:
            if self.rollover_date:
                # Use the rollover_date from the request as the disbursement date
                disbursement_date = timezone.datetime.combine(self.rollover_date, timezone.datetime.min.time())
            else:
                disbursement_date = get_current_datetime()
        
        # Ensure disbursement_date is timezone-aware if USE_TZ is True, naive if False
        from django.conf import settings
        if settings.USE_TZ and not timezone.is_aware(disbursement_date):
            disbursement_date = timezone.make_aware(disbursement_date)
        elif not settings.USE_TZ and timezone.is_aware(disbursement_date):
            disbursement_date = timezone.make_naive(disbursement_date)
        
        # Create the new active loan
        new_loan = Loan.objects.create(
            application=new_application,
            borrower=self.loan.borrower,
            principal_amount=principal_amount,
            interest_amount=interest_amount,
            processing_fee=processing_fee + rollover_fee,  # Include rollover fee in processing fee
            total_amount=total_amount,
            disbursement_date=disbursement_date,
            due_date=disbursement_date + timezone.timedelta(days=duration),
            duration_days=duration,
            status='active',  # New loan is active - exactly like other active loans
            # Don't link to original loan - this is a completely new loan
        )
        
        # Mark the original loan as rolled over for accounting purposes
        # This keeps it for historical/accounting records but removes it from active operations
        self.loan.status = 'rolled_over'
        self.loan.is_rolled_over = True
        self.loan.save()
        
        return new_loan
    
    def reject(self, rejected_by, notes=""):
        self.status = 'rejected'
        self.reviewed_by = rejected_by
        self.reviewed_at = get_current_datetime()
        self.review_notes = notes
        self.save()



class MpesaTransaction(models.Model):
    """
    Enhanced M-Pesa transaction logs with C2B callback support
    """
    TRANSACTION_TYPES = [
        ('c2b', 'Customer to Business'),
        ('b2c', 'Business to Customer'),
        ('stk_push', 'STK Push'),
        ('Pay Bill', 'Pay Bill'),
        ('Buy Goods', 'Buy Goods'),
    ]
    
    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('validated', 'Validated'),
        ('confirmed', 'Confirmed'),
        ('success', 'Success'),
        ('processed', 'Processed'),
        ('pending_approval', 'Pending Approval'),
        ('failed', 'Failed'),
        ('cancelled', 'Cancelled'),
        ('rejected', 'Rejected'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    transaction_type = models.CharField(max_length=20, choices=TRANSACTION_TYPES)
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    phone_number = models.CharField(max_length=17)
    
    # M-Pesa response fields
    mpesa_transaction_id = models.CharField(max_length=50, blank=True, null=True, help_text="M-Pesa transaction ID")
    merchant_request_id = models.CharField(max_length=50, blank=True, null=True)
    checkout_request_id = models.CharField(max_length=50, blank=True, null=True)
    
    # C2B callback fields
    trans_id = models.CharField(max_length=50, blank=True, null=True, help_text="M-Pesa C2B transaction ID")
    trans_time = models.CharField(max_length=20, blank=True, null=True, help_text="Transaction timestamp from M-Pesa")
    business_short_code = models.CharField(max_length=10, blank=True, null=True)
    bill_ref_number = models.CharField(max_length=50, blank=True, null=True, help_text="Account number used")
    invoice_number = models.CharField(max_length=50, blank=True, null=True)
    org_account_balance = models.DecimalField(max_digits=15, decimal_places=2, blank=True, null=True)
    third_party_trans_id = models.CharField(max_length=50, blank=True, null=True)
    
    # Customer details from C2B
    msisdn = models.CharField(max_length=20, blank=True, null=True, help_text="Customer phone number (masked)")
    first_name = models.CharField(max_length=100, blank=True, null=True)
    middle_name = models.CharField(max_length=100, blank=True, null=True)
    last_name = models.CharField(max_length=100, blank=True, null=True)
    
    # Status
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    result_code = models.CharField(max_length=10, blank=True, null=True)
    result_description = models.TextField(blank=True, null=True)
    
    # System processing
    borrower = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, help_text="Matched borrower")
    processing_notes = models.TextField(blank=True, null=True)
    processed_at = models.DateTimeField(blank=True, null=True)
    processed_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='processed_mpesa_transactions')
    
    # Raw callback data
    raw_validation_data = models.JSONField(blank=True, null=True)
    raw_confirmation_data = models.JSONField(blank=True, null=True)
    
    # Related loan/repayment
    loan = models.ForeignKey(Loan, on_delete=models.SET_NULL, null=True, blank=True, related_name='mpesa_transactions')
    repayment = models.ForeignKey(Repayment, on_delete=models.SET_NULL, null=True, blank=True, related_name='mpesa_transactions')
    
    # Payment source tracking
    is_automatic = models.BooleanField(default=True, help_text="True if payment was processed automatically via M-Pesa callback")
    payment_source = models.CharField(
        max_length=20, 
        choices=[('automatic', 'Automatic M-Pesa'), ('manual', 'Manual Entry')], 
        default='automatic'
    )
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'mpesa_transactions'
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['trans_id']),
            models.Index(fields=['mpesa_transaction_id']),
            models.Index(fields=['phone_number']),
            models.Index(fields=['msisdn']),
            models.Index(fields=['bill_ref_number']),
            models.Index(fields=['status']),
            models.Index(fields=['borrower']),
        ]
    
    def __str__(self):
        trans_id = self.trans_id or self.mpesa_transaction_id or 'Unknown'
        return f"M-Pesa {trans_id} - KES {self.amount}"
    
    def get_customer_name(self):
        """Get full customer name from C2B callback"""
        if self.first_name or self.middle_name or self.last_name:
            names = [self.first_name, self.middle_name, self.last_name]
            return ' '.join([name for name in names if name])
        return None
    
    def get_phone_number(self):
        """Get clean phone number from either field"""
        phone = self.msisdn or self.phone_number
        if phone:
            # Remove country code and format for display
            if phone.startswith('254'):
                phone = '0' + phone[3:]
            # Handle masked numbers like 25470****149
            if '*' in phone:
                return phone
            return phone
        return None
    
    def match_borrower(self):
        """Match this transaction to a borrower by ID number or loan number
        
        Since M-Pesa phone numbers are hashed, we match by:
        1. ID number (bill_ref_number = client's id_number)
        2. Loan number (bill_ref_number = loan_number, which gives us the borrower)
        
        The ID number is used as the account number during payment and matches the client's ID number during registration.
        Some customers may also use their loan number as the account number.
        """
        if self.borrower:
            return self.borrower
        
        if not self.bill_ref_number:
            self.processing_notes = "No bill reference number provided"
            self.save()
            return None
        
        # Normalize bill_ref_number: remove all spaces and normalize case
        bill_ref_clean = self.bill_ref_number.strip()
        bill_ref_normalized = ''.join(bill_ref_clean.split()).upper()  # Remove all spaces and uppercase
        
        # Strategy 1: Try to match by ID number (with space normalization)
        try:
            # Try exact match first
            borrower_by_id = User.objects.get(id_number=bill_ref_clean, role='borrower')
            self.borrower = borrower_by_id
            self.processing_notes = f"Matched by ID number ({bill_ref_clean})"
            self.save()
            return borrower_by_id
        except User.DoesNotExist:
            # Try case-insensitive and space-normalized match
            try:
                borrowers = User.objects.filter(role='borrower').exclude(id_number__isnull=True).exclude(id_number='')
                for borrower in borrowers:
                    if borrower.id_number:
                        borrower_id_normalized = ''.join(borrower.id_number.strip().split()).upper()
                        if borrower_id_normalized == bill_ref_normalized:
                            self.borrower = borrower
                            self.processing_notes = f"Matched by ID number ({bill_ref_clean}) - normalized match"
                            self.save()
                            return borrower
            except:
                pass
        
        # Strategy 2: Try to match by loan number (bill_ref might be a loan number)
        # If we find a loan with this number, we get the borrower from that loan
        try:
            from loans.models import Loan
            loan = Loan.active_objects.filter(loan_number=bill_ref_clean).first()
            if not loan:
                # Try normalized loan number (remove spaces)
                loans = Loan.active_objects.all()
                for l in loans:
                    if l.loan_number:
                        loan_number_normalized = ''.join(l.loan_number.strip().split()).upper()
                        if loan_number_normalized == bill_ref_normalized:
                            loan = l
                            break
            
            if loan and loan.borrower:
                self.borrower = loan.borrower
                self.processing_notes = f"Matched by loan number ({bill_ref_clean}) - borrower: {loan.borrower.get_full_name()}"
                self.save()
                return loan.borrower
        except Exception as e:
            # If loan matching fails, continue to next strategy
            pass
        
        # No match found
        self.processing_notes = f"No borrower found with ID number or loan number: {self.bill_ref_number or 'N/A'}"
        self.save()
        return None
    
    def match_loan(self):
        """Smart loan matching logic with priority for loans closest to deadline"""
        if self.loan or not self.borrower:
            return self.loan
        
        # Get all active loans for this borrower, ordered by due date (closest deadline first)
        active_loans = Loan.active_objects.filter(
            borrower=self.borrower,
            status='active'
        ).order_by('due_date')
        
        if not active_loans.exists():
            self.processing_notes = "No active loans found for this borrower"
            self.save()
            return None
        
        # Strategy 1: If bill_ref_number matches loan number
        if self.bill_ref_number:
            # Try to match by loan number first
            loan_by_ref = active_loans.filter(loan_number=self.bill_ref_number).first()
            if loan_by_ref:
                self.loan = loan_by_ref
                self.processing_notes = f"Matched by loan number: {self.bill_ref_number}"
                self.save()
                return loan_by_ref
        
        # Strategy 2: If only one active loan, use it
        if active_loans.count() == 1:
            loan = active_loans.first()
            self.loan = loan
            self.processing_notes = "Matched to single active loan"
            self.save()
            return loan
        
        # Strategy 3: Match by amount (exact or close match) - but prioritize by deadline
        for loan in active_loans:
            outstanding = loan.outstanding_amount
            
            # Exact match
            if self.amount == outstanding:
                self.loan = loan
                self.processing_notes = f"Matched by exact outstanding amount: KES {outstanding}"
                self.save()
                return loan
            
            # Close match (within 10 KES)
            if abs(self.amount - outstanding) <= Decimal('10.00'):
                self.loan = loan
                self.processing_notes = f"Matched by close amount match. Outstanding: KES {outstanding}, Paid: KES {self.amount}"
                self.save()
                return loan
        
        # Strategy 4: Use loan closest to deadline (already ordered by due_date)
        closest_deadline_loan = active_loans.first()
        self.loan = closest_deadline_loan
        self.processing_notes = f"Matched to loan closest to deadline due on {closest_deadline_loan.due_date.strftime('%Y-%m-%d')}"
        self.save()
        return closest_deadline_loan
    
    def create_repayment(self):
        """Create repayment record(s) from this M-Pesa transaction with multi-loan support"""
        if self.repayment or not self.borrower:
            return self.repayment
        
        try:
            # Get all active loans for this borrower, ordered by due date (closest deadline first)
            active_loans = Loan.active_objects.filter(
                borrower=self.borrower,
                status='active'
            ).order_by('due_date')
            
            if not active_loans.exists():
                self.status = 'failed'
                self.processing_notes = "No active loans found for this borrower"
                self.save()
                return None
            
            remaining_amount = self.amount
            repayments_created = []
            
            # Process payments across multiple loans, starting with the one closest to deadline
            from django.db import IntegrityError
            import time
            
            for loan in active_loans:
                if remaining_amount <= 0:
                    break
                
                outstanding = loan.outstanding_amount
                payment_amount = min(remaining_amount, outstanding)
                
                # Create repayment for this loan with retry logic for receipt number conflicts
                repayment = None
                max_attempts = 5
                for attempt in range(max_attempts):
                    try:
                        repayment = Repayment.objects.create(
                            loan=loan,
                            amount=payment_amount,
                            payment_method='mpesa',
                            payment_source='automatic',
                            mpesa_transaction_id=self.trans_id or self.mpesa_transaction_id,
                            mpesa_phone_number=self.msisdn or self.phone_number,
                            payment_date=self.get_transaction_datetime()
                        )
                        # Verify repayment was actually saved
                        repayment.refresh_from_db()
                        break  # Success
                    except IntegrityError as e:
                        if 'receipt_number' in str(e) and attempt < max_attempts - 1:
                            # Receipt number conflict - wait and retry
                            time.sleep(0.1 * (attempt + 1))
                            continue
                        else:
                            # Different error or max attempts - raise it
                            self.status = 'failed'
                            self.processing_notes = f"Failed to create repayment due to IntegrityError: {str(e)}"
                            self.save()
                            raise
                    except Exception as e:
                        # Other errors - raise them
                        self.status = 'failed'
                        self.processing_notes = f"Failed to create repayment: {str(e)}"
                        self.save()
                        raise
                
                if not repayment:
                    # Failed to create after all attempts
                    self.status = 'failed'
                    self.processing_notes = "Failed to create repayment after multiple attempts (receipt number conflicts)"
                    self.save()
                    return None
                
                # Verify repayment exists in database
                if not Repayment.objects.filter(id=repayment.id).exists():
                    self.status = 'failed'
                    self.processing_notes = f"Repayment object created but not found in database (ID: {repayment.id})"
                    self.save()
                    return None
                
                repayments_created.append(repayment)
                remaining_amount -= payment_amount
                
                # If this is the first repayment, link it to the transaction immediately
                if not self.repayment:
                    # Ensure repayment is saved before linking
                    repayment.refresh_from_db()
                    self.repayment = repayment
                    self.loan = loan
                    # Save transaction immediately after linking first repayment
                    self.save()
            
            # Only mark as processed if repayments were actually created
            if len(repayments_created) == 0:
                self.status = 'failed'
                self.processing_notes = "No repayments created - all loans may have zero outstanding amount or payment amount was zero"
                self.save()
                return None
            
            # Ensure repayment is linked if it wasn't already
            if not self.repayment and repayments_created:
                # Refresh all repayments from DB to ensure they exist
                for r in repayments_created:
                    r.refresh_from_db()
                self.repayment = repayments_created[0]
                self.loan = repayments_created[0].loan
            
            # Update transaction status - only if repayments were created
            self.status = 'processed'
            self.processed_at = get_current_datetime()
            
            if len(repayments_created) == 1:
                self.processing_notes = f"Repayment created successfully. Receipt: {repayments_created[0].receipt_number}"
            else:
                receipt_numbers = [r.receipt_number for r in repayments_created]
                self.processing_notes = f"Multiple repayments created across {len(repayments_created)} loans. Receipts: {', '.join(receipt_numbers)}"
            
            # Save transaction with repayment link
            self.save()
            
            # Verify repayment is properly linked and exists in database
            self.refresh_from_db()
            if not self.repayment:
                self.status = 'failed'
                self.processing_notes = "Repayment was created but not properly linked to transaction"
                self.save()
                return None
            
            # Final verification: repayment must exist in database and be accessible
            try:
                # Get repayment ID from the foreign key field directly (before accessing relationship)
                repayment_id = self.repayment_id if hasattr(self, 'repayment_id') else (self.repayment.id if self.repayment else None)
                
                if not repayment_id:
                    self.status = 'failed'
                    self.processing_notes = "Repayment ID not found in transaction"
                    self.save()
                    return None
                
                # Verify repayment exists in database
                repayment_check = Repayment.objects.get(id=repayment_id)
                # Verify it's actually linked correctly
                if repayment_check.loan != self.loan:
                    self.processing_notes = (self.processing_notes or "") + f" | Warning: Repayment loan mismatch"
                
                # Try to access the relationship to ensure it's valid
                try:
                    _ = self.repayment.id
                except Exception as rel_error:
                    # Relationship is broken - try to fix it
                    self.repayment = repayment_check
                    self.save()
                    self.refresh_from_db()
                    
            except Repayment.DoesNotExist:
                # Repayment doesn't exist - clear the broken link
                broken_repayment_id = repayment_id if 'repayment_id' in locals() else None
                self.status = 'failed'
                self.repayment = None
                self.processing_notes = f"Repayment ID {broken_repayment_id} does not exist in database - broken relationship cleared"
                self.save()
                return None
            except Exception as e:
                # Other error accessing repayment - might be a relationship issue
                # Try to fix by re-linking the repayment
                try:
                    repayment_id = self.repayment_id if hasattr(self, 'repayment_id') else None
                    if repayment_id:
                        repayment_fix = Repayment.objects.get(id=repayment_id)
                        self.repayment = repayment_fix
                        self.save()
                        self.refresh_from_db()
                except:
                    self.status = 'failed'
                    self.processing_notes = f"Error accessing repayment: {str(e)}"
                    self.save()
                    return None
            
            return self.repayment
            
        except Exception as e:
            self.status = 'failed'
            self.processing_notes = f"Failed to create repayment: {str(e)}"
            self.save()
            return None
    
    def get_transaction_datetime(self):
        """Convert M-Pesa timestamp to Django datetime"""
        if self.trans_time:
            try:
                # M-Pesa format: YYYYMMDDHHMMSS
                from datetime import datetime
                return make_datetime_compatible(datetime.strptime(self.trans_time, '%Y%m%d%H%M%S'))
            except (ValueError, TypeError):
                pass
        return get_current_datetime()
    
    def process_payment(self, force_rematch=False):
        """Complete payment processing pipeline with multi-loan support
        
        Automatically processes payments when matched by national ID number.
        No pending approval system - payments are processed immediately if ID matches.
        
        Args:
            force_rematch: If True, clears borrower/loan to force re-matching.
                          Useful when ID is added after payment was made.
        """
        try:
            # If force_rematch is True, clear previous matching to allow re-matching
            # This is useful when ID is added to system after payment was made
            if force_rematch or (self.status in ['failed', 'pending', 'pending_approval'] and not self.repayment):
                self.borrower = None
                self.loan = None
                # Don't clear processing_notes completely, but reset if it's an old failure
                if 'No borrower found' in (self.processing_notes or ''):
                    self.processing_notes = ''
                self.save()
            
            # Step 1: Match borrower by ID number only
            borrower = self.match_borrower()
            if not borrower:
                # No borrower found with matching ID number - mark as failed
                # But don't overwrite status if already processed
                if not self.repayment:
                    self.status = 'failed'
                    self.processing_notes = (self.processing_notes or "") + " | No borrower found with matching ID number"
                    self.save()
                return False
            
            # Step 2: Create repayment(s) - this handles multi-loan allocation automatically
            repayment = self.create_repayment()
            if not repayment:
                # Reset status if repayment creation failed
                if self.status == 'processed':
                    self.status = 'failed'
                    self.processing_notes = (self.processing_notes or "") + " | Repayment creation failed but status was set to processed"
                    self.save()
                return False
            
            # Verify repayment is properly linked
            self.refresh_from_db()
            if not self.repayment:
                # Repayment was created but not linked - fix it
                self.repayment = repayment
                self.loan = repayment.loan
                self.status = 'processed'
                self.save()
            
            # Final verification - ensure repayment exists in database
            try:
                repayment.refresh_from_db()
            except Exception:
                # Repayment doesn't exist - mark as failed
                self.status = 'failed'
                self.processing_notes = (self.processing_notes or "") + " | Repayment was created but does not exist in database"
                self.repayment = None
                self.save()
                return False
            
            # Step 3: Send notifications
            self.send_payment_notifications()
            
            return True
            
        except Exception as e:
            self.status = 'failed'
            self.processing_notes = f"Processing error: {str(e)}"
            self.save()
            return False
    
    def send_payment_notifications(self):
        """Send payment confirmation notifications"""
        if not self.borrower or not self.loan or not self.repayment:
            return
        
        try:
            from utils.models import Notification
            
            # Create notification for borrower
            Notification.create_notification(
                user=self.borrower,
                notification_type='payment_received',
                title='Payment Received',
                message=f'Your payment of KES {self.amount:,.2f} for loan {self.loan.loan_number} has been received. Outstanding balance: KES {self.loan.outstanding_amount:,.2f}',
                priority='medium',
                action_url=f'/loans/{self.loan.id}/',
                icon='fa-money-bill'
            )
            
            # Notify loan officer if assigned
            if hasattr(self.borrower, 'portfolio_manager') and self.borrower.portfolio_manager:
                Notification.create_notification(
                    user=self.borrower.portfolio_manager,
                    notification_type='payment_received',
                    title='Client Payment Received',
                    message=f'{self.borrower.get_full_name()} made a payment of KES {self.amount:,.2f} for loan {self.loan.loan_number}',
                    priority='low',
                    action_url=f'/loans/{self.loan.id}/',
                    icon='fa-money-bill'
                )
                
        except Exception as e:
            # Don't fail the whole process if notifications fail
            pass
# =======================
# AUTO-STATUS UPDATE SIGNAL for OVERPAID LOANS
# Add this to loans/models.py after the Loan model definition
# =======================

from django.db.models.signals import pre_save
from django.dispatch import receiver

@receiver(pre_save, sender=Loan)
def auto_update_paid_status(sender, instance, **kwargs):
    """
    Automatically update loan status to 'paid' when outstanding amount is <= 0
    Only applies to active loans that are not rolled over
    """
    # Only check for existing loans (not new ones)
    if instance.pk:
        # Don't change status if already rolled over or already paid
        if instance.status in ['rolled_over', 'paid']:
            return
        
        # Check if loan is fully paid (outstanding <= 0)
        try:
            outstanding = instance.outstanding_amount
            if outstanding <= 0 and instance.status == 'active':
                instance.status = 'paid'
                # Note: This will be saved automatically by the pre_save signal
        except:
            # If there's any error calculating outstanding, don't change status
            pass


# ============================================================================
# Grazuri Schema Compatibility Models
# ============================================================================
# These models are added to ensure compatibility with the target Grazuri schema
# as defined in xygbfpsg_loans.sql


class BureauRecord(models.Model):
    """
    Credit bureau records for borrowers
    Maps to: bureau_records table in Grazuri schema
    """
    id = models.AutoField(primary_key=True)
    borrower = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='bureau_records',
        db_column='borrower',
        help_text="Reference to borrower (CustomUser)"
    )
    baccount = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='bureau_records',
        db_column='baccount',
        help_text="Reference to loan account"
    )
    
    # Bureau record fields
    bureau_name = models.CharField(max_length=100, blank=True, null=True)
    record_date = models.DateTimeField(blank=True, null=True)
    credit_score = models.IntegerField(blank=True, null=True)
    report_data = models.TextField(blank=True, null=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'bureau_records'
        ordering = ['-record_date']
        verbose_name = 'Bureau Record'
        verbose_name_plural = 'Bureau Records'
    
    def __str__(self):
        return f"Bureau Record for {self.borrower.get_full_name()} - Loan {self.baccount.loan_number}"


class LoanDisbursement(models.Model):
    """
    Loan disbursement tracking
    Maps to: loan_disbursements table in Grazuri schema
    """
    id = models.AutoField(primary_key=True)
    loan = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='disbursements',
        db_column='loan',
        help_text="Reference to loan"
    )
    
    # Disbursement details
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    disbursement_date = models.DateTimeField()
    disbursement_method = models.CharField(
        max_length=50,
        blank=True,
        null=True,
        help_text="Method of disbursement (M-Pesa, Bank Transfer, etc.)"
    )
    reference_number = models.CharField(max_length=100, blank=True, null=True)
    notes = models.TextField(blank=True, null=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'loan_disbursements'
        ordering = ['-disbursement_date']
        verbose_name = 'Loan Disbursement'
        verbose_name_plural = 'Loan Disbursements'
    
    def __str__(self):
        return f"Disbursement for Loan {self.loan.loan_number} - KES {self.amount:,.2f}"


class LoanFee(models.Model):
    """
    Loan fees tracking
    Maps to: loan_fees table in Grazuri schema
    """
    id = models.AutoField(primary_key=True)
    loan = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='fees',
        db_column='loan',
        help_text="Reference to loan"
    )
    
    # Fee details
    fee_type = models.CharField(
        max_length=50,
        help_text="Type of fee (processing, late payment, etc.)"
    )
    fee_name = models.CharField(max_length=100)
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    is_paid = models.BooleanField(default=False)
    paid_date = models.DateTimeField(blank=True, null=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'loan_fees'
        ordering = ['-created_at']
        verbose_name = 'Loan Fee'
        verbose_name_plural = 'Loan Fees'
    
    def __str__(self):
        return f"{self.fee_name} for Loan {self.loan.loan_number} - KES {self.amount:,.2f}"


class LoanGuarantor(models.Model):
    """
    Loan guarantors
    Maps to: loan_guarantors table in Grazuri schema
    """
    id = models.AutoField(primary_key=True)
    borrower = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='guarantor_records',
        db_column='borrower',
        help_text="Reference to borrower (CustomUser)"
    )
    
    # Guarantor details
    guarantor_name = models.CharField(max_length=200)
    guarantor_phone = models.CharField(max_length=17)
    guarantor_email = models.EmailField(blank=True, null=True)
    guarantor_id_number = models.CharField(max_length=50, blank=True, null=True)
    relationship = models.CharField(max_length=100, blank=True, null=True)
    
    # Status
    is_active = models.BooleanField(default=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'loan_guarantors'
        ordering = ['-created_at']
        verbose_name = 'Loan Guarantor'
        verbose_name_plural = 'Loan Guarantors'
    
    def __str__(self):
        return f"Guarantor {self.guarantor_name} for {self.borrower.get_full_name()}"


class LoanStatus(models.Model):
    """
    Loan status history tracking
    Maps to: loan_statuses table in Grazuri schema
    """
    id = models.AutoField(primary_key=True)
    loan = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='status_history',
        db_column='loan',
        help_text="Reference to loan"
    )
    
    # Status details
    status = models.CharField(max_length=50)
    status_date = models.DateTimeField(default=timezone.now)
    changed_by = models.ForeignKey(
        User,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='loan_status_changes'
    )
    notes = models.TextField(blank=True, null=True)
    
    # Timestamps
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        db_table = 'loan_statuses'
        ordering = ['-status_date']
        verbose_name = 'Loan Status'
        verbose_name_plural = 'Loan Statuses'
    
    def __str__(self):
        return f"Loan {self.loan.loan_number} - {self.status} on {self.status_date.strftime('%Y-%m-%d')}"
