"""AccountPayeeKycNotification model."""

from abacus_common_logic.models.base import BaseSoftDeleteModel, db
from abacus_common_logic.utils.users import get_flask_user_id
from sqlalchemy import update


class BasePayeeKycNotification(BaseSoftDeleteModel):
    """Base payee kyc notification model."""

    __abstract__ = True

    file_upload_link = db.Column(db.String(1000), nullable=False)
    requirement_id = db.Column(db.String(1000), nullable=True)
    requirement_type_id = db.Column(db.Integer, nullable=True)
    sub_requirement_id = db.Column(db.String(1000), nullable=True)
    possible_sub_requirement_types = db.Column(db.String(1000), nullable=True)
    sub_requirement_status_id = db.Column(db.Integer, nullable=True)
    entity_reference_id = db.Column(db.String(36), nullable=True)
    entity_reference_type_id = db.Column(db.Integer, nullable=True)

    @classmethod
    def get_latest_by_payee_id(cls, payee_id: int):
        """Get latest non-deleted payee's kyc notification record."""
        return (
            cls.query.filter(getattr(cls, cls.__payee_id__) == payee_id)
            .filter(cls.deleted_at.is_(None))
            .order_by(cls.created_at.desc())
            .first()
        )

    @classmethod
    def get_latest_by_payee_ids(cls, payee_ids: list[int]):
        """Get list of latest non-deleted payee's kyc notification record."""
        return (
            cls.query.filter(getattr(cls, cls.__payee_id__).in_(payee_ids))
            .filter(cls.deleted_at.is_(None))
            .order_by(cls.created_at.desc())
            .all()
        )

    @classmethod
    def soft_delete_by_payee_id(cls, payee_id: int) -> int:
        """Soft delete all active account payee's kyc notification records."""
        stmt = (
            update(cls)
            .where(getattr(cls, cls.__payee_id__) == payee_id)
            .where(cls.deleted_at.is_(None))
            .values(deleted_at=cls.current_timestamp(), deleted_by=get_flask_user_id())
        )
        result = db.session.execute(stmt)
        db.session.commit()
        return result.rowcount


class PayeeKycNotification(BasePayeeKycNotification):
    """Payee kyc notification model."""

    __tablename__ = 'payee_kyc_notification'
    __payee_id__ = 'payee_id'

    payee_kyc_notification_id = db.Column(db.Integer, primary_key=True)
    payee_id = db.Column(db.Integer, nullable=False)


class AccountPayeeKycNotification(BasePayeeKycNotification):
    """Account payee kyc notification model."""

    __tablename__ = 'account_payee_kyc_notification'
    __payee_id__ = 'account_payee_id'

    account_payee_kyc_notification_id = db.Column(db.Integer, primary_key=True)
    account_payee_id = db.Column(db.Integer, nullable=False)