"""Statement period model."""

from datetime import date

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import NormalizedDateTime
from abacus_common_logic.models.base import CRUDMixin, get_flask_user_id
from sqlalchemy import case, literal_column, outerjoin, select, table, text
from sqlalchemy.orm import lazyload

from royalties.constants.constants import (
    ACCOUNTING_PERIOD_STATUSES,
    CLOSED_STATEMENT_PERIODS_COUNT,
    OPEN_STATEMENT_PERIODS_COUNT,
    STATEMENT_PERIOD_STATUSES,
)
from royalties.features import is_statement_period_query_optimization_ff_enabled
from royalties.models.exchange_rate import ExchangeRate


class StatementPeriod(db.Model, CRUDMixin):
    """Statement period model."""

    __tablename__ = 'statement_period'

    statement_period_id = db.Column(db.Integer, primary_key=True)
    statement_period_name = db.Column(db.String(180), nullable=False)
    statement_period_status = db.Column(
        db.Enum(
            *STATEMENT_PERIOD_STATUSES,
            name='statement_period_status',
            create_type=False,
        ),
        default=STATEMENT_PERIOD_STATUSES.OPEN,
        nullable=False,
    )
    statement_month = db.Column(db.Integer, default=date.today().month, nullable=True)
    statement_year = db.Column(db.Integer, default=date.today().year, nullable=True)
    closed_date = db.Column(NormalizedDateTime(), nullable=True)
    closed_by = db.Column(db.String(180), nullable=True)
    _is_recent_period = False

    @property
    def exchange_rates_delivered(self) -> bool:
        """Check if the statement period has exchange rates delivered."""
        return (
            False
            if is_statement_period_query_optimization_ff_enabled()
            and not self._is_recent_period
            else bool(self.exchange_rates)
        )

    @property
    def all_balances_closed(self) -> bool:
        """Check if all close_balance states are complete.

        Validates `close_balance` abacus state actions state for the related
        `statement_period_payment_entity` records to be in `complete` status.
        """
        query = (
            select(
                [
                    literal_column('sppe.statement_period_payment_entity_id').label(
                        'statement_period_payment_entity_id'
                    ),
                    literal_column('ast.abacus_state_id').label('abacus_state_id'),
                ]
            )
            .where(
                literal_column('sppe.statement_period_id') == self.statement_period_id
            )
            .select_from(
                outerjoin(
                    table('statement_period_payment_entity').alias('sppe'),
                    table('abacus_state').alias('ast'),
                    text("""
                sppe.statement_period_payment_entity_id = ast.parent_table_id
                AND ast.parent_table_name = 'statement_period_payment_entity'
                AND ast.action_name = 'close_balance'
                AND ast.action_status = 'complete'
            """),
                )
            )
        )
        return all(
            row['abacus_state_id'] for row in db.session.execute(query).fetchall()
        )

    def close_period(self):
        """Actions to close a statement period."""
        self.statement_period_status = STATEMENT_PERIOD_STATUSES.CLOSED
        self.closed_date = CRUDMixin.current_timestamp()
        self.closed_by = get_flask_user_id()

    def has_active_accounting_periods(self):
        """Check whether there are acc periods not in closed status."""
        active_periods = [
            period
            for period in self.accounting_period
            if period.accounting_period_status != ACCOUNTING_PERIOD_STATUSES.CLOSED
        ]
        return len(active_periods) > 0

    @classmethod
    def get_recent_periods(cls):
        """Get the current and last closed periods."""
        status_order = case(
            (cls.statement_period_status == STATEMENT_PERIOD_STATUSES.CURRENT, 0),
            (cls.statement_period_status == STATEMENT_PERIOD_STATUSES.CLOSED, 1),
            else_=2,
        )

        response = (
            cls.query.filter(
                cls.statement_period_status.in_(
                    (
                        STATEMENT_PERIOD_STATUSES.CURRENT,
                        STATEMENT_PERIOD_STATUSES.CLOSED,
                    )
                )
            )
            .order_by(status_order, cls.statement_period_id.desc())
            .limit(CLOSED_STATEMENT_PERIODS_COUNT + 1)
            .all()
        )

        if is_statement_period_query_optimization_ff_enabled():
            for res in response:
                res._is_recent_period = True

        return response

    @classmethod
    def get_upcoming_periods(cls):
        """Get upcoming open periods."""
        return (
            cls.query.filter(
                cls.statement_period_status == STATEMENT_PERIOD_STATUSES.OPEN
            )
            .order_by(cls.statement_period_id.asc())
            .limit(OPEN_STATEMENT_PERIODS_COUNT)
            .all()
        )

    @classmethod
    def get_current_statement_period(cls):
        """Return currently open statement period if there is one."""
        return cls.query.filter_by(
            statement_period_status=STATEMENT_PERIOD_STATUSES.CURRENT
        ).first()

    def stream_all_fx_rates(self):
        """Stream exchange rates."""
        return (
            ExchangeRate.query.options(lazyload('*'))
            .filter_by(statement_period_id=self.statement_period_id)
            .yield_per(100)
        )

    def is_accepting_file_attachments(self) -> bool:
        """Check if the statement period accepts file attachments."""
        valid_statuses = [STATEMENT_PERIOD_STATUSES.CURRENT]
        return not self.closed_date and self.statement_period_status in valid_statuses

    @classmethod
    def default_order(cls):
        """Override default ordering in BaseModel."""
        return cls.statement_period_id.asc()

    @classmethod
    def get_by_ids(cls, statement_period_ids: list) -> list:
        """Get statement periods by a list of IDs.

        Args:
            statement_period_ids(list): a list of statement period IDs
        Returns:
            a list of statement periods.
        """
        return cls.query.filter(cls.statement_period_id.in_(statement_period_ids)).all()

    @classmethod
    def get_by_statement_years(cls, statement_years: list) -> list:
        """Get statement periods by a list of statement_years.

        Args:
            statement_years(list): a list of statement years
        Returns:
            a list of statement periods.
        """
        return cls.query.filter(cls.statement_year.in_(statement_years)).all()