"""Statement Period Payment Entity model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import CRUDMixin
from sqlalchemy import and_, select
from sqlalchemy.sql import literal_column, table


class StatementPeriodPaymentEntity(db.Model, CRUDMixin):
    """Statement Period Payment Entity model."""

    __tablename__ = 'statement_period_payment_entity'
    statement_period_payment_entity_id = db.Column(db.Integer, primary_key=True)
    statement_period_id = db.Column(
        db.Integer,
        db.ForeignKey('statement_period.statement_period_id'),
        nullable=False,
    )

    reference_payment_entity_id = db.Column(db.Integer, nullable=False)
    is_visible_to_customer = db.Column(db.Boolean, default=False, nullable=False)

    statement_period = db.relationship(
        'StatementPeriod', backref='statement_period_payment_entities', uselist=False
    )

    @classmethod
    def get_by_statement_period_and_payment_entity(
        cls, statement_period_id, payment_entity_id
    ):
        """Find object by statement_period_id and payment_entity_id."""
        return cls.query.filter_by(
            statement_period_id=statement_period_id,
            reference_payment_entity_id=payment_entity_id,
        ).first()

    @classmethod
    def get_states_by_statement_period(cls, statement_period_id: int):
        """Get abacus_state for specified statement_period.

        Args:
            statement_period_id (int): ID of parent statement_period
        """
        query = (
            select(
                [
                    literal_column('sppe.statement_period_payment_entity_id').label(
                        'statement_period_payment_entity_id'
                    ),
                    literal_column('sppe.reference_payment_entity_id').label(
                        'reference_payment_entity_id'
                    ),
                    literal_column('sppe.statement_period_id').label(
                        'statement_period_id'
                    ),
                    literal_column('st.abacus_state_id').label('abacus_state_id'),
                    literal_column('st.action_name').label('action_name'),
                    literal_column('st.action_status').label('action_status'),
                ]
            )
            .where(
                and_(
                    literal_column('st.parent_table_name') == cls.__tablename__,
                    literal_column('st.parent_table_id')
                    == literal_column('sppe.statement_period_payment_entity_id'),
                    literal_column('sppe.statement_period_id') == statement_period_id,
                )
            )
            .select_from(table('statement_period_payment_entity').alias('sppe'))
            .select_from(table('abacus_state').alias('st'))
        )

        return db.session.execute(query).fetchall()