"""WorksheetPaymentCustom repository."""

from typing import List, Optional, Tuple

from abacus_common_logic.connectors.database import db
from sqlalchemy import func, select

from payment.constants import constants
from payment.models import WorksheetPaymentCustom


def get_filtered_worksheet_payment_customs(
    worksheet_payment_custom_ids: Optional[List[int]] = None,
    limit: Optional[int] = constants.DEFAULT_PAGE_LIMIT,
    offset: Optional[int] = constants.DEFAULT_PAGE_OFFSET,
) -> Tuple[List[WorksheetPaymentCustom], int]:
    """
    Get worksheet_payment_customs by filtering criteria.

    Args:
        worksheet_payment_custom_ids: (Optional[List[int]])
        limit: (Optional[int])
        offset: (Optional[int])

    Returns:
        Tuple of (items, total_count)
    """
    # Base statement filtering out deleted records
    stmt = select(WorksheetPaymentCustom).where(
        WorksheetPaymentCustom.deleted_at == None  # noqa
    )

    # Apply filters
    filters = WorksheetPaymentCustom.filter_for(
        {'worksheet_payment_custom_ids': worksheet_payment_custom_ids or None}
    )
    if filters:
        stmt = stmt.where(*filters)

    # Get total count
    total_count = db.session.execute(
        select(func.count()).select_from(stmt.subquery())
    ).scalar_one()

    # Apply ordering
    stmt = stmt.order_by(WorksheetPaymentCustom.default_order())

    # Apply pagination
    if offset:
        stmt = stmt.offset(offset)

    if limit:
        stmt = stmt.limit(limit)

    items: List[WorksheetPaymentCustom] = db.session.execute(stmt).scalars().all()

    return items, total_count