"""Statement Attachment model."""

from sqlalchemy import and_
from sqlalchemy import asc
from sqlalchemy import Column
from sqlalchemy import desc
from sqlalchemy import Enum
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import or_
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy import text
from sqlalchemy.sql import literal_column
from sqlalchemy.sql import table

from moneyhub.constants.constants import OrderDirection
from moneyhub.constants.constants import StatementPeriodStatus
from moneyhub.models.mysql_base import BaseModel


class StatementPeriod(BaseModel):
    """Statement period model."""

    __tablename__ = 'statement_period'

    statement_period_id = Column(Integer, primary_key=True)
    statement_period_name = Column(String(180), nullable=True)
    statement_period_status = Column(
        Enum(
            *StatementPeriodStatus,
            name='statement_period_status',
            create_type=False
        ),
        nullable=True
    )

    @classmethod
    def get_for_account_activity(
        cls,
        account_id: int,
        first_statement_period: int,
        contract_id: int | None = None,
        limit: int | None = 50,
        offset: int | None = 0,
        order_dir: OrderDirection = OrderDirection.ASC,
    ) -> tuple[list, int]:
        """Get statement period range where an account has had activity.

        Args:
            account_id (int): Account to get periods for.
            first_statement_period (int): First statement period for the vendor.
            contract_id (int | None): Optional contract to get periods for.
            order_dir (OrderDirection): Sort direction order.
            limit (int): Max number of items to return.
            offset (int): Number of items to skip.
        Returns:
            Tuple[list, int]: Tuple consisting of a list of statement periods and total count.
        """
        # prevents circular dependencies
        from moneyhub.models.statement_period_payment_entity import StatementPeriodPaymentEntity

        fallback_filter = [
            literal_column('ledger_account_contract.account_id') == account_id
        ]

        if contract_id:
            fallback_filter.append(
                literal_column('ledger_account_contract.contract_id') == contract_id
            )

        # Subquery for the first statement period that has ledger_account_contract entry
        fallback_first_period = select([
            func.min(literal_column('abacus_event.statement_period_id')).label('statement_period_id')  # noqa: E501
        ]) \
            .select_from(table('ledger_account_contract')) \
            .join(
            table('abacus_event'),
            text('ledger_account_contract.abacus_event_id = abacus_event.abacus_event_id')
        ) \
            .filter(
            *fallback_filter,
            literal_column('abacus_event.statement_period_id') >= first_statement_period
        ) \
            .scalar_subquery()

        # Subquery for the reference_payment_entity_id
        payment_entity_id = select([
            literal_column('account_payment_term.payment_entity_id')
        ]) \
            .select_from(table('account_payment_term')) \
            .filter(literal_column('account_payment_term.account_id') == account_id) \
            .scalar_subquery()

        filter_condition = [
            or_(
                cls.statement_period_status == StatementPeriodStatus.CLOSED,
                and_(
                    StatementPeriodPaymentEntity.is_visible_to_customer == 1,
                    StatementPeriodPaymentEntity.reference_payment_entity_id == payment_entity_id
                )
            ),
            cls.statement_period_id >= fallback_first_period
        ]

        order_direction = asc if order_dir == OrderDirection.ASC else desc

        base_query = cls.query \
            .with_entities(cls, func.count().over().label('total_records')) \
            .join(StatementPeriodPaymentEntity, isouter=True) \
            .filter(*filter_condition) \
            .group_by(cls.statement_period_id) \
            .order_by(order_direction(cls.statement_period_id))

        rows = base_query.limit(limit).offset(offset).all()
        total_records = int(rows[0].total_records) if rows else 0
        items = [row[0] for row in rows]

        return items, total_records

    @classmethod
    def get_by_ids(cls, ids: list[int]) -> list:
        """Get a list of periods by their IDs.

        Args:
            ids (list): IDs to fetch by
        Returns:
            list: list of statement periods
        """
        return cls.query.filter(
            cls.statement_period_id.in_(ids)
        ).order_by(
            cls.statement_period_id.asc()
        ).all()