"""WorksheetTaxCorrectionVAT repository."""

from typing import List, Optional, Tuple

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

from payment.constants import constants
from payment.models import (
    PaymentGroupPaymentAccountDetail,
    WorksheetAccountContractPayableDetails as PayableDetails,
    WorksheetPayableBalanceAfterTax as PayableBalanceAfterTax,
    WorksheetTaxCorrectionVAT as TaxCorrection,
)


def get_filtered_worksheet_tax_corrections_vat(
    correction_status: str,
    correction_statement_period_id: Optional[int] = None,
    contract_ids: Optional[List[int]] = None,
    worksheet_tax_correction_vat_ids: Optional[List[int]] = None,
    limit: Optional[int] = constants.DEFAULT_PAGE_LIMIT,
    offset: Optional[int] = constants.DEFAULT_PAGE_OFFSET,
) -> Tuple[List[TaxCorrection], int]:
    """
    Get worksheet_tax_corrections_vat by complex filtering criteria.

    Args:
        correction_status (str)
        correction_statement_period_id (Optional[int])
        contract_ids: (Optional[List[int]])
        limit: (Optional[int])
        offset: (Optional[int])
    """
    status_subquery = (
        select(TaxCorrection.worksheet_tax_correction_vat_id)
        .select_from(TaxCorrection)
        .join(
            PayableDetails,
            and_(
                TaxCorrection.__tablename__ == PayableDetails.target_table,
                TaxCorrection.worksheet_tax_correction_vat_id
                == PayableDetails.target_id,
                PayableDetails.deleted_at == None,  # noqa
            ),
        )
        .join(
            PayableBalanceAfterTax,
            and_(
                PayableDetails.worksheet_account_contract_payable_after_tax_id  # noqa
                == PayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id,  # noqa
                PayableBalanceAfterTax.deleted_at == None,  # noqa
            ),
        )
        .join(
            PaymentGroupPaymentAccountDetail,
            and_(
                PayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id  # noqa
                == PaymentGroupPaymentAccountDetail.worksheet_account_contract_payable_after_tax_id,  # noqa
                PaymentGroupPaymentAccountDetail.deleted_at == None,  # noqa
            ),
        )
    )

    payable_detail_type_id = constants.CORRECTION_TYPE_TO_PAYABLE_DETAIL_TYPE_ID.get(
        constants.CORRECTION_TYPES.VAT
    )

    stmt = select(TaxCorrection).where(
        TaxCorrection.payable_detail_type_id == payable_detail_type_id,
        TaxCorrection.deleted_at == None,  # noqa
    )

    if correction_statement_period_id:
        stmt = stmt.where(
            TaxCorrection.correction_statement_period_id
            == correction_statement_period_id
        )

    if contract_ids:
        stmt = stmt.where(TaxCorrection.contract_id.in_(contract_ids))

    if worksheet_tax_correction_vat_ids:
        stmt = stmt.where(
            TaxCorrection.worksheet_tax_correction_vat_id.in_(
                worksheet_tax_correction_vat_ids
            )
        )

    if correction_status == constants.CORRECTION_STATUSES.PENDING:
        stmt = stmt.where(
            not_(TaxCorrection.worksheet_tax_correction_vat_id.in_(status_subquery))
        )
    elif correction_status == constants.CORRECTION_STATUSES.ACTIVE:
        stmt = stmt.where(
            TaxCorrection.worksheet_tax_correction_vat_id.in_(status_subquery)
        )

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

    if limit:
        stmt = stmt.limit(limit)

    if offset:
        stmt = stmt.offset(offset)

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

    return items, total_count