"""StatementPeriodAdjustmentFile Model.

It is a READ ONLY model for accessing data.
The real business logic lives in the ows-royalties repository.
"""

from abacus_common_logic.connectors.database import db
from sqlalchemy import select, text
from sqlalchemy.sql import literal_column, table


class StatementPeriodAdjustmentFile:
    """StatementPeriodAdjustmentFile Model."""

    @classmethod
    def get_adjustment_files_by_statement_period_id(
        cls,
        statement_period_id: int,
    ):
        """Get a statement_period_adjustment_files by statement_period_id.

        Args:
            statement_period_id (int): id of the statement_period.

        Returns:
            list of statement_period_adjustment_file ids
        """
        statement_period_adjustment_file_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    )
                ]
            )
            .where(
                text('spaf.statement_period_id = :statement_period_id'),
                literal_column('spaf.deleted_by').is_(None),
                literal_column('spaf.deleted_at').is_(None),
                literal_column('spaf.error_type').is_(None),
            )
            .select_from(table('statement_period_adjustment_file').alias('spaf'))
        )

        return db.session.execute(
            statement_period_adjustment_file_query,
            {'statement_period_id': statement_period_id},
        ).fetchall()

    @classmethod
    def get_statement_period_adjustment_file(
        cls,
        statement_period_adjustment_file_id: int,
    ):
        """Get a statement_period_adjustment_file by statement_period_adjustment_file_id.

        Args:
            statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file.

        Returns:
            a statement_period_adjustment_file
        """
        statement_period_adjustment_file_query = (
            select(
                [
                    literal_column(
                        'spaf.statement_period_adjustment_file_id',
                    ).label('statement_period_adjustment_file_id'),
                    literal_column(
                        'spaf.batch_type',
                    ).label('batch_type'),
                ]
            )
            .where(
                text(
                    'spaf.statement_period_adjustment_file_id = :statement_period_adjustment_file_id'
                ),
                literal_column('spaf.deleted_by').is_(None),
                literal_column('spaf.deleted_at').is_(None),
            )
            .select_from(table('statement_period_adjustment_file').alias('spaf'))
        )

        return db.session.execute(
            statement_period_adjustment_file_query,
            {
                'statement_period_adjustment_file_id': statement_period_adjustment_file_id
            },
        ).fetchone()