"""WorksheetAccountContractPayableDetails repository."""

from typing import List

from abacus_common_logic.connectors.database import db
from abacus_common_logic.utils.users import get_flask_user_id
from sqlalchemy import and_, func, literal_column, select, table, update

from payment.constants import constants
from payment.models import (
    PaymentGroupPaymentAccountDetail as Detail,
    WorksheetAccountContractPayableDetails as PayableDetails,
    WorksheetPayableBalanceAfterTax as BalanceAfterTax,
)


def get_filtered_active_records(
    statement_period_id: int,
    worksheet_after_tax_ids: List[int] = None,
    detail_groups: List[str] = None,
    limit: int = None,
    offset: int = None,
) -> (List['PayableDetails'], int):
    """Return worksheet_account_contract_payable_details by statement_period_id."""

    stmt = select(PayableDetails).where(
        PayableDetails.statement_period_id == statement_period_id,
        PayableDetails.deleted_at.is_(None),
    )

    if detail_groups:
        detail_group_subquery = (
            select(literal_column('rpdt.reference_payable_detail_type_id'))
            .where(literal_column('rpdt.detail_group_name').in_(detail_groups))
            .select_from(table('reference_payable_detail_type').alias('rpdt'))
        )

        stmt = stmt.where(
            PayableDetails.payable_detail_type_id.in_(detail_group_subquery)
        )

    if worksheet_after_tax_ids:
        stmt = stmt.where(
            PayableDetails.worksheet_account_contract_payable_after_tax_id.in_(
                worksheet_after_tax_ids
            )
        )

    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)

    return db.session.execute(stmt).scalars().all(), total_count


def soft_delete_by_payment_group_payment_account(payment_group_payment_account_id: int):
    """Soft delete worksheet_account_contract_payable_details."""
    db.session.execute(
        update(PayableDetails)
        .where(
            PayableDetails.worksheet_account_contract_payable_after_tax_id.in_(
                select(Detail.worksheet_account_contract_payable_after_tax_id).where(
                    Detail.payment_group_payment_account_id
                    == payment_group_payment_account_id
                )
            )
        )
        .values(
            deleted_at=PayableDetails.current_timestamp(),
            deleted_by=get_flask_user_id(),
        )
        .execution_options(synchronize_session=False)
    )
    db.session.commit()


def soft_delete_by_payment_group_payment(
    payment_group_payment_id: int, commit: bool = True
):
    """Soft delete worksheet_account_contract_payable_details.

    Args:
        payment_group_payment_id: ID of the payment group payment
        commit: whether to commit the transaction
    """
    events_query = (
        select(literal_column('ae.abacus_event_id'))
        .where(
            and_(
                literal_column('ae.target_type')
                == constants.ABACUS_EVENT_TARGET_TYPES.PAYMENT_GROUP_PAYMENT,
                literal_column('ae.target_id') == payment_group_payment_id,
            )
        )
        .select_from(table('abacus_event').alias('ae'))
    )

    db.session.execute(
        update(PayableDetails)
        .where(
            PayableDetails.worksheet_account_contract_payable_after_tax_id.in_(
                select(
                    BalanceAfterTax.worksheet_account_contract_payable_after_tax_id
                ).where(BalanceAfterTax.abacus_event_id.in_(events_query))
            )
        )
        .values(
            deleted_at=PayableDetails.current_timestamp(),
            deleted_by=get_flask_user_id(),
        )
        .execution_options(synchronize_session=False)
    )
    if commit:
        db.session.commit()


def get_by_payment_group_payment_account_ids(
    payment_group_payment_account_ids: List[int],
    payable_detail_type_ids: List[int] = None,
    limit: int = None,
    offset: int = None,
) -> (List['PayableDetails'], int):
    """Return worksheet_account_contract_payable_details by payment_group_payment_account_ids.

    Args:
        payment_group_payment_account_ids: List of payment_group_payment_account_ids
        payable_detail_type_ids: Optional list of payable_detail_type_ids to filter by
        limit: Maximum number of records to return
        offset: Number of records to skip

    Returns:
        Tuple of (list of PayableDetails, total_count)
    """
    stmt = (
        select(PayableDetails)
        .join(
            Detail,
            PayableDetails.worksheet_account_contract_payable_after_tax_id
            == Detail.worksheet_account_contract_payable_after_tax_id,
        )
        .where(
            Detail.payment_group_payment_account_id.in_(
                payment_group_payment_account_ids
            ),
            Detail.deleted_at.is_(None),
            PayableDetails.deleted_at.is_(None),
        )
    )

    if payable_detail_type_ids:
        stmt = stmt.where(
            PayableDetails.payable_detail_type_id.in_(payable_detail_type_ids)
        )

    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)

    return db.session.execute(stmt).scalars().all(), total_count