from datetime import datetime, timezone
from app.extensions import db
from app.utils.logger import logger

class RepaymentsData(db.Model):
    __tablename__ = 'repayments_data'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    transaction_id = db.Column(db.String(50), nullable=False)
    added_date = db.Column(db.DateTime(timezone=True), default=datetime.now(timezone.utc), nullable=False)
    response_code = db.Column(db.String(10), nullable=True)
    response_descr = db.Column(db.String(255), nullable=True)
    fbn_transaction_id = db.Column(db.String(255),nullable=True)
    account_id = db.Column(db.String(50), nullable=True)
    customer_id = db.Column(db.String(50), nullable=True)
    repayment_amount = db.Column(db.Float, nullable=True)
    amount_collected = db.Column(db.Float, nullable=True)
    balance = db.Column(db.Float, nullable=True, default=0.0)

    def to_dict(self):
        return {
            "id": self.id,
            "transaction_id": self.transaction_id,
            "added_date": self.added_date.isoformat() if self.added_date else None,
            "response_code": self.response_code,
            "response_descr": self.response_descr,
            "customerId": self.customer_id,
            "accountId": self.account_id,
            "fbnTransactionId": self.fbn_transaction_id,
            "repaymentAmount": self.repayment_amount,
            "amountCollected": self.amount_collected,
            "balance": self.balance
        }


    def __repr__(self):
        return f"<RepaymentsData id={self.id}, transaction_id={self.transaction_id}>"
    
    @classmethod
    def get_all_repayment_data(cls,  customer_id=None, account_id=None, transaction_id=None, fbn_transaction_id=None,
                          added_date=None,  page=1, limit=20):
        """
        Get all repayment data with optional filtering

        Args:
            customer_id (str, optional): Filter by customer ID
            account_id (str, optional): Filter by account ID
            added_date (datetime, optional): Filter by added date
            transaction_id (str, optional): Filter by transaction ID
            fbn_transaction_id (str, optional): Filter by FBN transaction ID
            page (int, optional): Page number for pagination
            limit (int, optional): Number of items per page

        Returns:
            tuple: (list of Repayment objects, total count)
        """
        query = cls.query
        if customer_id:
            query = query.filter(cls.customer_id == customer_id)
        if account_id:
            query = query.filter(cls.account_id == account_id)
        if transaction_id:
            query = query.filter(cls.transaction_id == transaction_id)
        if fbn_transaction_id:
            query = query.filter(cls.fbn_transaction_id == fbn_transaction_id)
        
        if added_date:
            query = query.filter(cls.added_date >= added_date)

        # Get total count before pagination
        total_count = query.count()
        
        #order
        query = query.order_by(cls.added_date.desc())

        # Apply pagination
        offset = (page - 1) * limit
        query = query.limit(limit).offset(offset)

        return query.all(), total_count