"""Account statement periods model."""

from sqlalchemy import asc
from sqlalchemy import Column
from sqlalchemy import desc
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import String

from moneyhub.constants.constants import OrderDirection
from moneyhub.models.snowflake_base import BaseModel


class AccountStatementPeriods(BaseModel):
    """Account statement periods model."""

    __tablename__ = 'account_statement_periods_dbt'

    account_id = Column(Integer, nullable=False, primary_key=True)
    contract_id = Column(Integer, nullable=False, primary_key=True)
    statement_period_id = Column(Integer, nullable=False, primary_key=True)
    statement_period_name = Column(String(100), nullable=False)
    statement_period_status = Column(String(100), nullable=False)
    currency_code = Column(String(10), nullable=False)
    statement_year = Column(Integer, nullable=True)
    statement_month = Column(Integer, nullable=True)

    @classmethod
    def get_by_account_id(
        cls,
        account_id: int,
        contract_id: int | None,
        limit: int | None = None,
        offset: int | None = None,
        order_dir: OrderDirection = OrderDirection.ASC,
    ) -> tuple[list, int]:
        """Get account activity statement periods.

        Args:
            account_id (int): Account to get periods for.
            contract_id (int | None): Optional contract to get periods for.
            order_dir (OrderDirection): Sort direction order.
            limit (int): Max number of records to return.
            offset (int): Number of records to skip.

        Returns:
            Tuple[list, int]: List of statement periods and total count.
        """
        filters = [cls.account_id == account_id]

        if contract_id:
            filters.append(cls.contract_id == contract_id)

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

        group_by = [
            cls.statement_period_id,
            cls.statement_period_name,
            cls.statement_period_status,
            cls.currency_code,
            cls.statement_year,
            cls.statement_month,
        ]

        base_query = cls.query \
            .with_entities(
                *group_by,
                func.count().over().label('total_records'),
            ) \
            .filter(*filters) \
            .group_by(*group_by) \
            .order_by(order_direction(cls.statement_period_id))

        records = base_query.limit(limit).offset(offset).all()
        total_records = int(records[0].total_records) if records else 0

        return records, total_records

    @classmethod
    def get_activity_statement_period_ids(
        cls,
        account_id: int,
        contract_id: int | None = None,
        limit: int | None = None,
        order_dir: OrderDirection = OrderDirection.ASC,
    ) -> list:
        """Get account activity statement period ids.

        Args:
            account_id (int): Account to get periods for.
            contract_id (int | None): Optional contract to get periods for.
            order_dir (OrderDirection): Sort direction order.
            limit (int): Max number of records to return.

        Returns:
            list: List of statement period ids.
        """
        filters = [cls.account_id == account_id]

        if contract_id:
            filters.append(cls.contract_id == contract_id)

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

        base_query = cls.query \
            .with_entities(
                cls.statement_period_id,
            ) \
            .distinct() \
            .filter(*filters) \
            .order_by(order_direction(cls.statement_period_id))

        results = base_query.limit(limit).all()

        return [item.statement_period_id for item in results]

    @classmethod
    def are_statement_period_ids_visible(
            cls, account_id: int, statement_period_ids: list[int]) -> bool:
        """Check if statement periods for an account are visible.

        Args:
            account_id (int): ID of Account.
            statement_period_ids (list): IDs of statement periods
        Returns:
            bool: Whether the statement periods are visible or not.
        """
        filters = [
            cls.account_id == account_id,
            cls.statement_period_id.in_(statement_period_ids)
        ]
        return bool(cls.query.filter(*filters).first())