from django.db import models
from django.utils import timezone
from django.contrib.auth import get_user_model
from django.core.exceptions import ValidationError
from organisations.models import Organisation, OrganisationBranch
from customers.models import Customer
from users.models import Staff
from ledgers.models import OrganisationSubAccount, SystemTransactions

User = get_user_model()

class ExternalLoanProduct(models.Model):
    """External loan products - separate from regular loan products"""
    INT_METHOD = (
        ('flat', 'Flat'),
        ('declining', 'Declining Balance'),
        ('amortization', 'Amortization'),
    )
    
    LOAN_PERIOD_TYPE = (
        ('d', 'Days'),
        ('m', 'Months'),
        ('w', 'Weeks'),
        ('bw', 'Bi-Weeks'),
        ('q', 'Quarterly'),
        ('y', 'Years'),
    )
    
    product_name = models.CharField(max_length=555)
    int_rate = models.FloatField(default=0.0)
    int_method = models.CharField(max_length=50, choices=INT_METHOD)
    loan_period = models.IntegerField(default=0)
    period_type = models.CharField(max_length=10, choices=LOAN_PERIOD_TYPE)
    max_loan_amt = models.BigIntegerField(default=0)
    min_loan_amt = models.BigIntegerField(default=0)
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='external_loan_products')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_product_added_by')
    is_active = models.BooleanField(default=True)
    is_deleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'external_loan_products'
    
    def __str__(self):
        return self.product_name

class ExternalLoanSector(models.Model):
    """External loan sectors - separate from regular loan sectors"""
    name = models.CharField(max_length=1555)
    description = models.TextField(null=True, blank=True)
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='external_loan_sectors')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_sector_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    is_active = models.BooleanField(default=True)

    class Meta:
        db_table = 'external_loan_sectors'
    
    def __str__(self):
        return self.name

class ExternalLoanApplication(models.Model):
    """External loan applications - loans applied for by organizations from a loan provider organization"""
    STATUS = (
        ('pending', 'Pending'),
        ('approved', 'Approved'),
        ('rejected', 'Rejected'),
        ('disbursed', 'Disbursed'),
        ('cleared_off', 'Cleared Off'),
        ('written_off', 'Written Off'),
    )
    
    PAYMENT_METHOD = (
        ('bank', 'Bank Transfer'),
    )
    
    INT_METHOD = (
        ('flat', 'Flat'),
        ('declining', 'Declining Balance'),
        ('amortization', 'Amortization'),
    )
    
    LOAN_PERIOD_TYPE = (
        ('d', 'Days'),
        ('m', 'Months'),
        ('w', 'Weeks'),
        ('bw', 'Bi-Weeks'),
        ('q', 'Quarterly'),
        ('y', 'Years'),
    )
    
    # Basic application info
    loan_amount = models.FloatField(default=0.0)
    loan_purpose = models.CharField(max_length=1555)
    application_date = models.DateTimeField(default=timezone.now)
    
    # Product and terms
    external_loan_product = models.ForeignKey(ExternalLoanProduct, on_delete=models.CASCADE, related_name='applications')
    int_rate = models.FloatField(default=0.0)
    int_method = models.CharField(max_length=50, choices=INT_METHOD)
    loan_period = models.IntegerField(default=0)
    period_type = models.CharField(max_length=10, choices=LOAN_PERIOD_TYPE)
    
    # Organization applying for the loan (instead of customer)
    applicant_organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='external_loan_applications', help_text="Organization applying for the loan", null=True, blank=True)
    organisation_branch = models.ForeignKey(OrganisationBranch, on_delete=models.CASCADE, related_name='external_loan_applications')
    loan_officer = models.ForeignKey(Staff, on_delete=models.CASCADE, related_name='external_loan_applications', null=True, blank=True, help_text="Loan officer assigned when loan is approved")
    external_loan_sector = models.ForeignKey(ExternalLoanSector, on_delete=models.CASCADE, related_name='applications', null=True, blank=True)
    
    # Loan provider organization (lender)
    loan_provider_organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='external_loans_provided', help_text="Organization providing the loan", null=True, blank=True)
    
    # Payment details (bank only)
    payment_method = models.CharField(max_length=50, choices=PAYMENT_METHOD, default='bank')
    bank_account_details = models.JSONField(null=True, blank=True)
    
    # Status and tracking
    status = models.CharField(max_length=100, choices=STATUS, default='pending')
    
    # Audit fields
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_applications_added')
    is_deleted = models.BooleanField(default=False)
    reason_for_delete = models.CharField(max_length=1555, null=True, blank=True)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_applications')
    deleted_at = models.DateTimeField(null=True, blank=True)
    
    # Additional fields
    reason = models.CharField(max_length=1555, null=True, blank=True)
    loan_number = models.CharField(max_length=200, null=True, blank=True)

    class Meta:
        db_table = 'external_loan_applications'

    def save(self, *args, **kwargs):
        if self.is_deleted and not self.deleted:
            self.deleted = True
        elif self.deleted and not self.is_deleted:
            self.is_deleted = True
        super().save(*args, **kwargs)
    
    def __str__(self):
        if self.applicant_organisation and self.loan_provider_organisation:
            return f"External Loan - {self.applicant_organisation.name} from {self.loan_provider_organisation.name} - {self.loan_amount}"
        return f"External Loan - {self.loan_amount}"

class ExternalLoanApproval(models.Model):
    """External loan approvals"""
    APPROVAL_STATUS = (
        ('pending', 'Pending'),
        ('approved', 'Approved'),
        ('rejected', 'Rejected'),
    )
    
    external_loan_application = models.OneToOneField(ExternalLoanApplication, on_delete=models.CASCADE, related_name='approval', null=True, blank=True)
    approved_amount = models.FloatField(default=0.0)
    approved_duration = models.IntegerField(default=0)
    approved_purpose = models.CharField(max_length=500)
    approval_notes = models.TextField(null=True, blank=True)
    approval_status = models.CharField(max_length=20, choices=APPROVAL_STATUS, default='pending')
    
    # Approval details
    approved_interest_rate = models.FloatField(null=True, blank=True)
    approved_grace_period = models.IntegerField(default=0)
    approved_payment_frequency = models.CharField(max_length=20, null=True, blank=True)
    
    # Audit fields
    approved_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_approvals')
    approval_date = models.DateTimeField(default=timezone.now)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_approvals')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_approvals'

class ExternalLoanDisbursement(models.Model):
    """External loan disbursements with proper account tracking"""
    DISBURSEMENT_METHOD = (
        ('bank', 'Bank Transfer'),
    )
    
    APPROVAL_STATUS = (
        ('pending', 'Pending'),
        ('approved', 'Approved'),
        ('rejected', 'Rejected'),
        ('closed', 'Closed'),
        ('reversed', 'Reversed'),
    )
    
    external_loan_application = models.OneToOneField(ExternalLoanApplication, on_delete=models.CASCADE, related_name='disbursement', null=True, blank=True)
    disbursed_amount = models.FloatField(default=0.0)
    disbursement_method = models.CharField(max_length=20, choices=DISBURSEMENT_METHOD, default='bank')
    payment_reference = models.CharField(max_length=100, null=True, blank=True)
    
    # Source account (lending organization's account)
    source_account = models.ForeignKey(OrganisationSubAccount, on_delete=models.CASCADE, related_name='external_loan_disbursements_source', help_text="Account from which money is disbursed", null=True, blank=True)
    
    # Destination account details (borrowing organization's account)
    destination_bank_name = models.CharField(max_length=255, null=True, blank=True)
    destination_account_name = models.CharField(max_length=255, null=True, blank=True)
    destination_account_number = models.CharField(max_length=255, null=True, blank=True)
    destination_account_details = models.JSONField(null=True, blank=True, help_text="Additional destination account details")
    
    disbursement_notes = models.TextField(null=True, blank=True)
    disbursement_date = models.DateTimeField(default=timezone.now)
    
    # Approval workflow
    approval_status = models.CharField(max_length=20, choices=APPROVAL_STATUS, default='pending')
    approved_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='approved_external_disbursements')
    date_approved = models.DateTimeField(null=True, blank=True)
    rejection_comment = models.TextField(null=True, blank=True)
    closure_comment = models.TextField(null=True, blank=True)
    
    # System transaction reference for proper ledger tracking
    system_transaction = models.ForeignKey(SystemTransactions, on_delete=models.SET_NULL, null=True, blank=True)
    
    # Audit fields
    disbursed_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_disbursements')
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    # Soft delete
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_disbursements')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_disbursements'
    
    def __str__(self):
        return f"Disbursement - {self.external_loan_application.loan_number} - {self.disbursed_amount}"

class ExternalLoanWriteOff(models.Model):
    """External loan write-offs"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='writeoffs')
    writeoff_amount = models.FloatField(default=0.0)
    interest_at_writeoff = models.FloatField(default=0.0)
    writeoff_date = models.DateTimeField(default=timezone.now)
    description = models.CharField(max_length=1555, default='Failed to pay')
    system_transaction = models.ForeignKey(SystemTransactions, on_delete=models.CASCADE, related_name='external_loan_writeoffs', null=True, blank=True)
    
    # Audit fields
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_writeoffs_added')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_writeoffs')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_writeoffs'

class ExternalLoanTransfer(models.Model):
    """External loan case load transfers"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='transfers')
    from_officer = models.ForeignKey(Staff, on_delete=models.CASCADE, related_name='external_loan_transfers_from')
    to_officer = models.ForeignKey(Staff, on_delete=models.CASCADE, related_name='external_loan_transfers_to')
    transfer_reason = models.CharField(max_length=1555)
    transfer_date = models.DateTimeField(default=timezone.now)
    
    # Audit fields
    transferred_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_transfers')
    date_added = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_transfers')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_transfers'

class ExternalLoanSettings(models.Model):
    """Settings for external loan operations"""
    PAYMENT_METHODS = (
        ('bank', 'Bank Transfer'),
    )
    
    organisation = models.OneToOneField(Organisation, on_delete=models.CASCADE, related_name='external_loan_settings')
    external_loan_chart = models.ForeignKey(OrganisationSubAccount, on_delete=models.CASCADE, related_name='external_loan_chart')
    bank_account_chart = models.ForeignKey(OrganisationSubAccount, on_delete=models.CASCADE, related_name='external_bank_chart', null=True, blank=True)
    default_payment_method = models.CharField(max_length=20, choices=PAYMENT_METHODS, default='bank')
    is_active = models.BooleanField(default=True)
    
    # Audit fields
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name='external_loan_settings_created')
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name='external_loan_settings_updated')

    class Meta:
        db_table = 'external_loan_settings'
    
    def __str__(self):
        return f"External Loan Settings - {self.organisation.name}"

# Additional models to match loans functionality exactly

class ExternalLoanProductCharges(models.Model):
    """External loan product charges - matches LoanProductCharges"""
    APPLY_TYPE = (('percent', "Percentage"),
        ('flat', 'Flat'),
        )
    LIMIT_TYPE = (('below', "Below"),
        ('range', 'Range'),
        ('above', 'Above'),
        )
    name = models.CharField(max_length=555)
    apply_type = models.CharField(max_length=100, choices=APPLY_TYPE)
    amount = models.FloatField(default=0.0)
    below_limit_amount = models.FloatField(default=0.0)
    above_limit_amount = models.FloatField(default=0.0)
    range_min_amount = models.FloatField(default=0.0)
    range_max_amount = models.FloatField(default=0.0)
    limit_type = models.CharField(max_length=50, choices=LIMIT_TYPE, default='above')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    external_loan_product = models.ForeignKey(ExternalLoanProduct, on_delete=models.CASCADE, related_name='external_loan_product_charges')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_charge_added_by')
    chart = models.ForeignKey(OrganisationSubAccount, on_delete=models.CASCADE, related_name='external_loan_product_charge_chart')
    is_deleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'external_loan_product_charges'

class ExternalLoanRepaymentSchedule(models.Model):
    """External loan repayment schedules with enhanced tracking"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_loan_schedules')
    principal_expected = models.FloatField(default=0.0)
    interest_expected = models.FloatField(default=0.0)
    total_payment = models.FloatField(default=0.0)
    expected_date = models.DateTimeField(null=True, blank=True)
    ending_balance = models.FloatField(default=0.0)
    starting_balance = models.FloatField(default=0.0)
    payment_number = models.IntegerField(default=0)
    
    # Enhanced tracking fields
    principal_paid = models.FloatField(default=0.0, help_text="Total principal paid for this schedule")
    interest_paid = models.FloatField(default=0.0, help_text="Total interest paid for this schedule")
    penalty_paid = models.FloatField(default=0.0, help_text="Total penalty paid for this schedule")
    penalty_amount = models.FloatField(default=0.0, help_text="Total penalty charged for this schedule")
    
    # Payment status tracking
    is_fully_paid = models.BooleanField(default=False, help_text="Whether this schedule is fully paid")
    is_overdue = models.BooleanField(default=False, help_text="Whether this schedule is overdue")
    days_overdue = models.IntegerField(default=0, help_text="Number of days overdue")
    
    ref_no = models.CharField(max_length=555, null=True, blank=True)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_schedule_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    status = models.CharField(max_length=50, default='active')
    reschedule_id = models.IntegerField(default=0)

    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_schedules')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        unique_together = ('external_loan_application', 'payment_number', 'status')
        db_table = 'external_loan_repayment_schedules'
    
    def __str__(self):
        return f"Schedule {self.payment_number} - {self.external_loan_application.loan_number}"
    
    @property
    def remaining_principal(self):
        return max(0, self.principal_expected - self.principal_paid)
    
    @property
    def remaining_interest(self):
        return max(0, self.interest_expected - self.interest_paid)
    
    @property
    def remaining_penalty(self):
        return max(0, self.penalty_amount - self.penalty_paid)
    
    @property
    def total_remaining(self):
        return self.remaining_principal + self.remaining_interest + self.remaining_penalty
    
    @property
    def payment_progress_percentage(self):
        if self.total_payment == 0:
            return 0
        paid = self.principal_paid + self.interest_paid + self.penalty_paid
        return min(100, (paid / self.total_payment) * 100)

class ExternalLoanPaymentTransaction(models.Model):
    """External loan payment transactions - matches LoanPaymentTransaction"""
    TRANSACTION_STATUS = (('normal', 'Normal'),
        ('reversed', 'Reversed')
    )
    amount = models.FloatField(default=0.0)
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_payment_transactions')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_payment_transaction_added_by', null=True, blank=True)
    date_added = models.DateTimeField(default=timezone.now)
    payment_date = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    transaction_type = models.CharField(max_length=55, default='manual')
    transaction_status = models.CharField(max_length=50, choices=TRANSACTION_STATUS, default='normal')
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_payment_transactions')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_payment_transactions'

class ExternalLoanGuarantors(models.Model):
    """External loan guarantors - can be individuals or organizations"""
    GUARANTOR_TYPE = (
        ('organization', 'Organization'),
        ('individual', 'Individual'),
    )
    
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='guarantors')
    guarantor_type = models.CharField(max_length=20, choices=GUARANTOR_TYPE, default='individual')
    
    # For individual guarantors (keeping original functionality)
    guarantor_customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='external_loan_guarantees', null=True, blank=True)
    
    # For organization guarantors
    guarantor_organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='external_loan_guarantees_given', null=True, blank=True)
    
    # Non-member individual guarantor details
    guarantor_name = models.CharField(max_length=255, null=True, blank=True, help_text="Name for non-member guarantors")
    guarantor_phone = models.CharField(max_length=20, null=True, blank=True, help_text="Phone for non-member guarantors")
    guarantor_address = models.TextField(null=True, blank=True, help_text="Address for non-member guarantors")
    guarantor_gender = models.CharField(max_length=10, null=True, blank=True, help_text="Gender for non-member guarantors")
    guarantor_dob = models.DateField(null=True, blank=True, help_text="Date of birth for non-member guarantors")
    guarantor_identity_no = models.CharField(max_length=50, null=True, blank=True, help_text="Identity number for non-member guarantors")
    guarantor_identity_type = models.CharField(max_length=20, null=True, blank=True, help_text="Identity type for non-member guarantors")
    guarantor_nationality = models.CharField(max_length=50, null=True, blank=True, help_text="Nationality for non-member guarantors")
    guarantor_occupation = models.CharField(max_length=100, null=True, blank=True, help_text="Occupation for non-member guarantors")
    
    contract_file = models.CharField(null=True, blank=True, max_length=11555)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_guarantor_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_guarantors')
    deleted_at = models.DateTimeField(null=True, blank=True)
    
    class Meta:
        db_table = 'external_loan_guarantors'
    
    def clean(self):
        if self.guarantor_type == 'individual':
            if not self.guarantor_customer and not self.guarantor_name:
                raise ValidationError('Individual guarantor must have either a customer selected or guarantor details provided')
        if self.guarantor_type == 'organization' and not self.guarantor_organisation:
            raise ValidationError('Organization guarantor must have an organization selected')
    
    def __str__(self):
        if self.guarantor_type == 'individual':
            if self.guarantor_customer:
                return f"Individual Guarantor: {self.guarantor_customer.name}"
            elif self.guarantor_name:
                return f"Non-member Guarantor: {self.guarantor_name}"
        elif self.guarantor_type == 'organization' and self.guarantor_organisation:
            return f"Organization Guarantor: {self.guarantor_organisation.name}"
        return f"Guarantor for {self.external_loan_application.loan_number}"

class ExternalLoanGuarantorsSecurity(models.Model):
    """External loan guarantors security"""
    external_loan_guarantor = models.ForeignKey(ExternalLoanGuarantors, on_delete=models.CASCADE, related_name='securities')
    description = models.CharField(max_length=555)
    value = models.FloatField(default=0.0)
    buying_date = models.DateTimeField(null=True, blank=True)
    depreciation_rate = models.FloatField(default=0.0)
    pledge_file = models.CharField(null=True, blank=True, max_length=11555)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_guarantor_security_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    
    class Meta:
        db_table = 'external_loan_guarantors_security'

class ExternalLoanApplicationSecurity(models.Model):
    """External loan application security/collateral"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='securities')
    description = models.CharField(max_length=555)
    value = models.FloatField(default=0.0)
    buying_date = models.DateTimeField(null=True, blank=True)
    depreciation_rate = models.FloatField(default=0.0)
    pledge_file = models.CharField(null=True, blank=True, max_length=11555)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_app_security_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_securities')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_application_security'

class ExternalLoanIncomeSource(models.Model):
    """External loan income sources"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='income_sources')
    source_name = models.CharField(max_length=555)
    estimated_value = models.FloatField(default=0.0)
    description = models.CharField(max_length=555)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_income_source_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_income_sources')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_income_source'

class ExternalLoanMainTransactions(models.Model):
    """External loan main transactions"""
    TRANSACTION_TYPES = (
        ('ExternalLoanDisbursement', 'External Loan Disbursement'),
        ('ExternalLoanPayment', 'External Loan Payment'),
        ('ExternalInterestPayment', 'External Interest Payment'),
        ('ExternalPenaltyPayment', 'External Penalty Payment'),
        ('ExternalLoanPrincipalPayment', 'External Loan Principal Payment'),
        ('ExternalLoanWriteOff', 'External Loan Write Off'),
        ('ExternalLoanPenaltyWaivered', 'External Loan Penalty Waivered'),
        ('ExternalLoanInterestWaivered', 'External Loan Interest Waivered'),
        ('ExternalLoanCustomCharge', 'External Loan custom charge'),
        ('ExternalLoanRecovery', 'External Loan Recovered')
    )
    
    TRANSACTION_STATUS = (
        ('normal', 'Normal'),
        ('reversed', 'Reversed')
    )
    
    heading = models.CharField(max_length=1555)
    amount = models.FloatField(default=0.0)
    payment_method = models.CharField(max_length=100)
    cheque = models.CharField(max_length=555, null=True, blank=True)
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='main_transactions')
    ref_no = models.CharField(max_length=555, null=True, blank=True)
    voucher_no = models.CharField(max_length=555, null=True, blank=True)
    transaction_type = models.CharField(max_length=50, choices=TRANSACTION_TYPES, default='ExternalLoanPayment')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_main_transaction_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    payment_date = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    system_transaction = models.ForeignKey(SystemTransactions, on_delete=models.CASCADE, related_name='external_loan_main_transaction', null=True)
    transaction_status = models.CharField(max_length=50, choices=TRANSACTION_STATUS, default='normal')
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_main_transactions')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_main_transactions'

class ExternalLoanRecovery(models.Model):
    """External loan recovery"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='recoveries')
    recovery_added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_recovery_added_by')
    external_loan_main_transaction = models.ForeignKey(ExternalLoanMainTransactions, on_delete=models.CASCADE, related_name='external_loan_recovery_main_transaction')
    recovered_amount = models.FloatField(default=0.0)
    recovery_date = models.DateTimeField(default=timezone.now)
    income_account = models.ForeignKey(OrganisationSubAccount, on_delete=models.CASCADE, related_name='external_loan_recovery_account', null=True, blank=True)
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_recoveries')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_recovery'

class ExternalLoanPayments(models.Model):
    """External loan payments - matches LoanPayments"""
    TRANSACTION_STATUS = (('normal', 'Normal'),
        ('reversed', 'Reversed')
    )
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_loan_payments')
    external_loan_repayment_schedule = models.ForeignKey(ExternalLoanRepaymentSchedule, on_delete=models.CASCADE, related_name='external_schedule_payments', null=True, blank=True)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_payments_added_by', null=True, blank=True)
    external_loan_main_transaction = models.ForeignKey(ExternalLoanMainTransactions, on_delete=models.CASCADE, related_name='external_payment_main_transaction')
    int_paid = models.FloatField(default=0.0)
    princ_paid = models.FloatField(default=0.0)
    penalty_paid = models.FloatField(default=0.0)
    payment_date = models.DateTimeField(default=timezone.now)
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    external_loan_payment_transaction = models.ForeignKey(ExternalLoanPaymentTransaction, on_delete=models.CASCADE, related_name='external_loan_all_payment_transaction', null=True, blank=True)
    payment_status = models.CharField(max_length=50, choices=TRANSACTION_STATUS, default='normal')
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_payments')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_payments'

class ExternalLoanPenalty(models.Model):
    """External loan penalties - matches LoanPenalty"""
    comment = models.CharField(max_length=1555, null=True, blank=True)
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_loan_penalties')
    ref_no = models.CharField(max_length=555, null=True, blank=True)
    voucher_no = models.CharField(max_length=555, null=True, blank=True)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_penalty_added_by', null=True, blank=True)
    external_loan_repayment_schedule = models.ForeignKey(ExternalLoanRepaymentSchedule, on_delete=models.CASCADE, related_name='external_schedule_penalties')
    amount = models.FloatField(default=0.0)
    status = models.CharField(max_length=50, default='auto')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    date_from = models.DateTimeField(null=True, blank=True)
    date_to = models.DateTimeField(null=True, blank=True)
    principal_arrears = models.FloatField(default=0.0)
    interest_arrears = models.FloatField(default=0.0)
    penalty_rate = models.FloatField(default=0.0)
    penalty_period_type = models.CharField(max_length=10, null=True, blank=True)
    expected_pay_date = models.DateTimeField(null=True, blank=True)
    arrear_days = models.IntegerField(default=0)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_penalties')
    deleted_at = models.DateTimeField(null=True, blank=True)
    
    class Meta:
        db_table = 'external_loan_penalties'

class ExternalLoanPenaltyWaivered(models.Model):
    """External loan penalty waivers - matches LoanPenaltyWaivered"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_penalty_waivers')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_penalty_waivered_added_by')
    external_loan_main_transaction = models.ForeignKey(ExternalLoanMainTransactions, on_delete=models.CASCADE, related_name='external_penalty_waivered_main_transaction', null=True, blank=True)
    external_loan_repayment_schedule = models.ForeignKey(ExternalLoanRepaymentSchedule, on_delete=models.CASCADE, related_name='external_schedule_penalty_waiver')
    amount = models.FloatField(default=0.0)
    comment = models.CharField(max_length=1555, null=True, blank=True)
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_penalty_waivers')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:   
        db_table = 'external_loan_penalty_waivered'

class ExternalLoanInterestWaivered(models.Model):
    """External loan interest waivers - matches LoanInterestWaivered"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_interest_waivers')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_interest_waivered_added_by')
    external_loan_main_transaction = models.ForeignKey(ExternalLoanMainTransactions, on_delete=models.CASCADE, related_name='external_interest_waivered_main_transaction', null=True, blank=True)
    external_loan_repayment_schedule = models.ForeignKey(ExternalLoanRepaymentSchedule, on_delete=models.CASCADE, related_name='external_schedule_interest_waiver')
    amount = models.FloatField(default=0.0)
    comment = models.CharField(max_length=1555, null=True, blank=True)
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_interest_waivers')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_interest_waivered'

class ExternalRescheduledLoans(models.Model):
    """External rescheduled loans - matches RescheduledLoans"""
    LOAN_PERIOD_TYPE = (('d', 'Days'),
        ('m', 'Months'),
        ('w', 'Weeks'),
        ('bw', 'Bi-Weeks'),
        ('q', 'Quarterly'),
        ('y', 'Years'),
        )
    INT_METHOD = (('flat', 'Flat'),
        ('declining', 'Declining Balance'),
        ('amortization', 'Amortization'),
        )
        
    GRACE_PERIOD_TYPE = (('pay_i', 'Pay Only Interest'),
        ('pay_p', 'Pay Only Principal'),
        ('pay_none', 'Pay None'),
        )

    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_rescheduled_loans')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_reschedule_added_by')
    principal_amount = models.FloatField(default=0.0)
    interest_expected = models.FloatField(default=0.0)
    total_payment = models.FloatField(default=0.0)
    loan_period = models.IntegerField()
    period_type = models.CharField(max_length=10, choices=LOAN_PERIOD_TYPE)
    frequency_type = models.CharField(max_length=10, choices=LOAN_PERIOD_TYPE)
    frequency = models.IntegerField(default=0)
    grace_period = models.IntegerField(default=0)
    grace_period_type = models.CharField(max_length=50, choices=GRACE_PERIOD_TYPE)
    int_rate = models.FloatField(default=0.0)
    int_method = models.CharField(max_length=50, choices=INT_METHOD)
    reschedule_date = models.DateTimeField(default=timezone.now)
    comment = models.CharField(max_length=1555, null=True, blank=True)
    status = models.CharField(max_length=50, default='active')
    organisation_branch = models.ForeignKey(OrganisationBranch, on_delete=models.CASCADE, related_name='external_loan_reschedule_organisation_branch')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_rescheduled_loans')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_rescheduled_loans'

class ExternalLoanTopUp(models.Model):
    """External loan top-ups - matches LoanTopUp"""
    LOAN_DISBURSE_METHOD = (('bank', 'Bank'),
        ('cash', 'Cash'),
        ('credit', 'Credit'),
        )
    LOAN_PERIOD_TYPE = (('d', 'Days'),
        ('m', 'Months'),
        ('w', 'Weeks'),
        ('bw', 'Bi-Weeks'),
        ('q', 'Quarterly'),
        ('y', 'Years'),
        )
    GRACE_PERIOD_TYPE = (('pay_i', 'Pay Only Interest'),
        ('pay_p', 'Pay Only Principal'),
        ('pay_none', 'Pay None'),
        )
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='external_loan_topups')
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_topup_added_by')
    topup_amount = models.FloatField(default=0.0)
    organisation_branch = models.ForeignKey(OrganisationBranch, on_delete=models.CASCADE, related_name='external_loan_topup_organisation_branch')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    disburse_method = models.CharField(max_length=10, choices=LOAN_DISBURSE_METHOD)
    disbursement_account_id = models.IntegerField(null=True, blank=True)
    auto_payment = models.BooleanField(default=False)
    loan_period = models.IntegerField(default=0)
    period_type = models.CharField(max_length=10, choices=LOAN_PERIOD_TYPE)
    frequency = models.IntegerField(default=0)
    frequency_type = models.CharField(max_length=10, choices=LOAN_PERIOD_TYPE)
    int_rate = models.FloatField(default=0.0)
    approval_disb_date = models.DateTimeField(default=timezone.now)
    grace_period = models.IntegerField(default=0)
    grace_period_type = models.CharField(max_length=10, choices=GRACE_PERIOD_TYPE)
    comment = models.CharField(max_length=1555, null=True, blank=True)
    principal_bal = models.FloatField(default=0.0)
    total_balance = models.FloatField(default=0.0)
    interest_due = models.FloatField(default=0.0)
    penalty = models.FloatField(default=0.0)
    interest_waived = models.FloatField(default=0.0)
    send_sms = models.BooleanField(default=False)
    voucher_no = models.CharField(max_length=155, null=True, blank=True)
    deleted = models.BooleanField(default=False)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_external_loan_topups')
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = 'external_loan_topups'

class ExternalLoanApplicationMember(models.Model):
    """Members list for external loan applications - CSV upload support"""
    external_loan_application = models.ForeignKey(ExternalLoanApplication, on_delete=models.CASCADE, related_name='members')
    member_name = models.CharField(max_length=255, help_text="Member name from CSV")
    member_number = models.CharField(max_length=100, help_text="Member number from CSV")
    loan_amount = models.FloatField(help_text="Individual loan amount from CSV")
    loan_period = models.IntegerField(help_text="Individual loan period from CSV")
    customer = models.ForeignKey(Customer, on_delete=models.SET_NULL, null=True, blank=True, related_name='external_loan_memberships', help_text="Matched customer if found")
    added_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='external_loan_member_added_by')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated = models.DateTimeField(default=timezone.now)
    is_active = models.BooleanField(default=True)

    class Meta:
        db_table = 'external_loan_application_members'
        unique_together = ('external_loan_application', 'member_number')
    
    def __str__(self):
        return f"{self.member_name} ({self.member_number}) - {self.external_loan_application.loan_number}"