"""Payment Group model."""

from abacus_common_logic.models.base import BaseModel, db
from sqlalchemy import func, select


class PaymentGroup(BaseModel):
    """Payment Group model."""

    __tablename__ = 'payment_group'
    payment_group_id = db.Column(db.Integer, primary_key=True)
    group_criteria = db.Column(db.JSON, nullable=False, default={})
    group_name = db.Column(db.String(180), nullable=False)
    is_reusable = db.Column(db.Boolean, nullable=False, default=False)
    deleted_at = db.Column(db.DateTime, nullable=True)
    deleted_by = db.Column(db.String(255), nullable=True)

    payment_group_payments = db.relationship(
        'PaymentGroupPayment', backref='payment_group', cascade='all, delete-orphan'
    )

    @classmethod
    def find_by_name(cls, group_name):
        """Override base model's find_by_name."""
        return (
            db.session.execute(select(cls).where(cls.group_name == group_name))
            .scalars()
            .first()
        )

    @classmethod
    def get_reusable_payment_groups(cls, limit: int, offset: int):
        """Filter payment groups based on the is_reusable column."""
        stmt = select(cls).where(cls.is_reusable == 1).order_by(cls.group_name.asc())
        items = db.session.execute(stmt.offset(offset).limit(limit)).scalars().all()
        count = db.session.execute(
            select(func.count()).select_from(stmt.subquery())
        ).scalar_one()
        return items, count