from datetime import datetime, timedelta, timezone from app.extensions import db from app.utils.logger import logger from sqlalchemy.exc import SQLAlchemyError from sqlalchemy import or_ from app.enums.repayment_schedule_status import RepaymentScheduleStatus from app.config import settings from decimal import Decimal, ROUND_HALF_UP # from dateutil.relativedelta import relativedelta class LoanRepaymentSchedule(db.Model): __tablename__ = 'loan_repayment_schedules' id = db.Column(db.Integer, primary_key=True, autoincrement=True) loan_id = db.Column(db.Integer, nullable=False) transaction_id = db.Column(db.String(50), nullable=True) product_id = db.Column(db.String(20), nullable=True) installment_number = db.Column(db.Integer, nullable=False) due_date = db.Column(db.DateTime, nullable=False) installment_amount= db.Column(db.Float, default=0.0) total_repayment_amount = db.Column(db.Float, default=0.0) paid = db.Column(db.Boolean, default=False) paid_at = db.Column(db.DateTime, nullable=True) due_process_date = db.Column(db.DateTime, nullable=True) due_process_count = db.Column(db.Integer, default=0) paid_status = db.Column(db.String(20), nullable=True) repay_description = db.Column(db.String(255), nullable=True) partial_balance = db.Column(db.Float, default=0.0) created_at = db.Column(db.DateTime, default=datetime.now(timezone.utc)) updated_at = db.Column(db.DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc)) penal_charge = db.Column(db.Float, default=0.0) penal_count = db.Column(db.Integer, default=0) last_penal_date = db.Column(db.DateTime, nullable=True) def to_dict(self): return { 'id': self.id, 'loan_id': self.loan_id, 'product_id': self.product_id, 'transaction_id': self.transaction_id, 'installment_number': self.installment_number, 'due_date': self.due_date.isoformat() if self.due_date else None, 'installment_amount': self.installment_amount, 'total_repayment_amount': self.total_repayment_amount, 'paid': self.paid, 'due_process_date': self.due_process_date.isoformat() if self.due_process_date else None, 'due_process_count': self.due_process_count, 'paid_status': self.paid_status, 'repay_description': self.repay_description, 'partial_balance': self.partial_balance, 'paid_at': self.paid_at.isoformat() if self.paid_at else None, 'created_at': self.created_at.isoformat() if self.created_at else None, 'updated_at': self.updated_at.isoformat() if self.updated_at else None, 'penal_charge': self.penal_charge, 'penal_count': self.penal_count, 'last_penal_date': self.last_penal_date.isoformat() if self.last_penal_date else None } def __repr__(self): return f'' @classmethod def get_repayment_schedule_by_loan_id(cls, loan_id, include_paid=True): """ Get repayment schedules by loan ID. :param loan_id: Loan ID to filter by :param include_paid: If True, include all schedules. If False, only unpaid ones. :return: List of repayment schedules ordered by due_date """ try: query = cls.query.filter_by(loan_id=loan_id) if not include_paid: query = query.filter_by(paid=False) schedules = query.order_by(cls.due_date.asc()).all() return schedules except Exception as e: logger.error(f"Error fetching repayment schedules for loan {loan_id}: {e}") raise @classmethod def get_repayment_schedule_by_id_and_transaction_id(cls, id, transaction_id): """ Get repayment schedule by ID and transaction ID """ try: return cls.query.filter_by(id=id, transaction_id=transaction_id).first() except Exception as e: logger.error(f"Error fetching repayment schedule for id={id}, transaction_id={transaction_id}: {e}") return None @classmethod def get_overdue_repayment_schedule(cls): """ Get all overdue repayment schedules that are not repaid. """ try: return cls.query.filter(cls.due_date < datetime.now(timezone.utc), cls.paid == False).order_by(cls.due_date.asc()).all() except Exception as e: logger.error(f"Error fetching overdue repayment schedules: {e}") return [] @classmethod def get_active_overdue_repayment_schedule(cls): """ Get all overdue repayment schedules that are active. """ try: return ( cls.query .filter( cls.due_date < datetime.now(timezone.utc), cls.paid_status == RepaymentScheduleStatus.ACTIVE ) .order_by(cls.due_date.asc()) .all() ) except Exception as e: logger.error(f"Error fetching active overdue repayment schedules: {e}") return [] @classmethod def get_overdue_repayment_schedule_with_grace_period(cls, grace_period_days, limit=None): try: now = datetime.now(timezone.utc) grace_period_date = now - timedelta(days=grace_period_days) penal_interval = timedelta(days=settings.PENAL_CHARGE_INTERVAL_DAYS) return cls.query.filter( cls.due_date < grace_period_date, cls.paid == False, or_( cls.last_penal_date == None, # never penalized before cls.last_penal_date < now - penal_interval ) ).order_by(cls.due_date.asc()).limit(limit).all() except Exception as e: logger.error(f"Error fetching overdue repayment schedules with grace period: {e}") return [] @classmethod def get_partially_paid_overdue_repayment_schedule(cls): """ Get all overdue repayment schedules that are partially paid. """ try: return ( cls.query .filter( cls.due_date < datetime.now(timezone.utc), cls.paid_status == RepaymentScheduleStatus.PARTIALLY_PAID ) .order_by(cls.due_date.asc()) .all() ) except Exception as e: logger.error(f"Error fetching partially paid overdue repayment schedules: {e}") return [] @classmethod def get_repayment_schedule_by_transaction_id(cls, transaction_id): """ Get repayment schedule by transaction ID """ return cls.query.filter_by(transaction_id=transaction_id).all() @classmethod def update_repayment_schedule_description(cls, schedule_id, description): """ Update the repayment description for a specific schedule. """ try: schedule = cls.query.get(schedule_id) if not schedule: raise ValueError(f"Schedule with ID {schedule_id} does not exist.") schedule.repay_description = description schedule.updated_at = datetime.now(timezone.utc) db.session.commit() logger.info(f"Updated repayment description for schedule ID {schedule_id}") return schedule.to_dict() except Exception as e: db.session.rollback() logger.error(f"Error updating repayment description for schedule {schedule_id}: {e}") raise @classmethod def update_repayment_schedule_status(cls, schedule_id): """ Mark a repayment schedule as fully repaid when the parent loan is fully repaid. This function does not take amount_collected because the loan is already cleared. """ try: # Fetch schedule schedule = cls.query.get(schedule_id) if not schedule: raise ValueError(f"Schedule with ID {schedule_id} does not exist.") # Force balance to 0 schedule.partial_balance = 0.0 schedule.paid_status = RepaymentScheduleStatus.REPAID schedule.paid = True schedule.paid_at = datetime.now(timezone.utc) # Track due processing if schedule.due_process_count is None: schedule.due_process_count = 0 schedule.due_process_count += 1 schedule.due_process_date = datetime.now(timezone.utc) # Update timestamp schedule.updated_at = datetime.now(timezone.utc) # Commit changes db.session.commit() logger.info(f"Schedule {schedule_id} marked as REPAID since parent loan is fully repaid.") return schedule.to_dict() except Exception as e: db.session.rollback() logger.error(f"Error updating repayment schedule {schedule_id} after loan repayment: {e}") raise @classmethod def update_repayment_schedule_status_to_active(cls, schedule_id): """ Update repayment schedule status to ACTIVE. """ try: schedule = cls.query.get(schedule_id) if not schedule: raise ValueError(f"Schedule with ID {schedule_id} does not exist.") schedule.paid_status = RepaymentScheduleStatus.ACTIVE schedule.updated_at = datetime.now(timezone.utc) db.session.commit() logger.info(f"Updated repayment schedule ID {schedule_id} status to ACTIVE") return schedule.to_dict() except Exception as e: db.session.rollback() logger.error(f"Error updating repayment schedule status for schedule {schedule_id}: {e}") raise @classmethod def update_repayment_schedule_balance(cls, schedule_id, amount_collected): """ Apply repayment to a loan schedule: - Deduct from partial balance if partially paid. - Otherwise deduct from installment amount. - Update partial balance, paid status, timestamps, etc. """ try: schedule = cls.query.get(schedule_id) if not schedule: raise ValueError(f"Schedule with ID {schedule_id} does not exist.") # Normalize amount amount_collected = Decimal(str(amount_collected)).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP) if amount_collected <= Decimal("0.00"): logger.info("Repayment amount must be greater than zero.") return schedule.to_dict() # Determine current balance if schedule.paid_status == RepaymentScheduleStatus.PARTIALLY_PAID and (schedule.partial_balance or 0) > 0: balance = Decimal(str(schedule.partial_balance)) else: balance = Decimal(str(schedule.installment_amount)) # Deduct repayment new_balance = balance - amount_collected if new_balance < 0: new_balance = Decimal("0.00") # prevent negatives # Update schedule fields schedule.partial_balance = float(new_balance) if new_balance > 0 else 0.0 schedule.updated_at = datetime.now(timezone.utc) if new_balance == 0: schedule.paid_status = RepaymentScheduleStatus.REPAID schedule.paid = True schedule.paid_at = datetime.now(timezone.utc) else: schedule.paid_status = RepaymentScheduleStatus.PARTIALLY_PAID schedule.paid = False # not fully paid yet # Track due processing if schedule.due_process_count is None: schedule.due_process_count = 0 schedule.due_process_count += 1 schedule.due_process_date = datetime.now(timezone.utc) # Commit db.session.commit() logger.info(f"Repayment applied for schedule ID {schedule_id}. Remaining balance: {schedule.partial_balance}") return schedule.to_dict() except Exception as e: db.session.rollback() logger.error(f"Error applying repayment for schedule {schedule_id}: {e}") raise from decimal import Decimal @classmethod def apply_penal_to_schedule(cls, schedule_id, penal_amount): schedule = cls.query.get(schedule_id) now = datetime.now(timezone.utc) penal_amount = Decimal(str(penal_amount)) current_penal = Decimal(str(schedule.penal_charge)) if schedule.penal_charge else Decimal("0") schedule.penal_count = (schedule.penal_count or 0) + 1 schedule.penal_charge = current_penal + penal_amount schedule.last_penal_date = now schedule.due_process_date = now schedule.updated_at = now db.session.commit() # Calculate penal charge @classmethod def calculate_penal_charge(cls, schedule): if schedule.paid_status == RepaymentScheduleStatus.PARTIALLY_PAID: outstanding = Decimal(str(schedule.partial_balance)) else: outstanding = Decimal(str(schedule.installment_amount)) rate = Decimal(str(settings.PENAL_CHARGE_PERCENTAGE)) / 100 penal_charge = (outstanding * rate).quantize( Decimal("0.01"), rounding=ROUND_HALF_UP ) return penal_charge