"""Statement Period Adjustment File model."""

from typing import Optional

from abacus_common_logic.connectors.database import db
from abacus_common_logic.constants.error import ERROR_ENTITY_DOES_NOT_EXIST
from abacus_common_logic.models import BaseModel, NormalizedDateTime
from flask import abort
from sqlalchemy import (
    Enum,
    and_,
    asc,
    case,
    desc,
    distinct,
    func,
    join,
    literal_column,
    or_,
    select,
    table,
    text,
)
from sqlalchemy.engine.row import Row

from abacus_file_upload.models import FileUpload
from royalties.constants.constants import (
    STATEMENT_PERIOD_ADJUSTMENT_FILE_BATCH_TYPES,
    STATEMENT_PERIOD_ADJUSTMENT_FILE_ERROR_TYPES,
    STATEMENT_PERIOD_ADJUSTMENT_FILE_USER_ACTIONS,
)
from royalties.features import (
    is_abacus_auto_generate_adjustments_flowthrough_ff_enabled,
)


class StatementPeriodAdjustmentFile(BaseModel):
    """Accounting Period Adjustment File model."""

    __tablename__ = 'statement_period_adjustment_file'
    statement_period_adjustment_file_id = db.Column(db.Integer, primary_key=True)
    statement_period_id = db.Column(
        db.Integer,
        db.ForeignKey('statement_period.statement_period_id'),
        nullable=False,
    )
    source_file_upload_id = db.Column(
        db.Integer,
        db.ForeignKey('file_upload.file_upload_id'),
        nullable=True,
    )
    file_name = db.Column(db.String(180), nullable=False)
    batch_type = db.Column(
        Enum(
            *STATEMENT_PERIOD_ADJUSTMENT_FILE_BATCH_TYPES,
            name='batch_type',
            create_type=False,
        ),
        nullable=False,
        default=STATEMENT_PERIOD_ADJUSTMENT_FILE_BATCH_TYPES.UPLOAD,
    )
    valid_file_location = db.Column(db.String(180), nullable=True)
    invalid_file_location = db.Column(db.String(180), nullable=True)
    valid_row_count = db.Column(db.Integer, nullable=True)
    invalid_row_count = db.Column(db.Integer, nullable=True)
    total_file_amount_multicurrency = db.Column(db.Numeric(25, 12), nullable=True)
    total_rounded_amount_multicurrency = db.Column(db.Numeric(20, 2), nullable=True)
    md5sum = db.Column(db.String(35), nullable=True)
    error_type = db.Column(
        Enum(
            *STATEMENT_PERIOD_ADJUSTMENT_FILE_ERROR_TYPES,
            name='error_type',
            create_type=False,
        ),
        nullable=True,
    )
    deleted_at = db.Column(NormalizedDateTime(), nullable=True)
    deleted_by = db.Column(db.String(180), nullable=True)

    statement_period = db.relationship(
        'StatementPeriod', backref='statement_period_adjustment_file', uselist=False
    )
    source_file_upload = db.relationship('FileUpload')

    @classmethod
    def default_order(cls):
        """Customize default order."""
        return cls.statement_period_adjustment_file_id.desc()

    @classmethod
    def filter_deleted_records(cls):
        """Return query with non deleted items."""
        return cls.query.filter(
            cls.deleted_at.is_(None),
            cls.deleted_by.is_(None),
        )

    @classmethod
    def get_by_id_or_error(cls, obj_id, error_status=400):
        """Override get_by_id_or_error method."""
        obj = cls.get_by_id(obj_id)
        if not obj or obj.deleted_at is not None and obj.deleted_by is not None:
            abort(
                code=error_status,
                description=ERROR_ENTITY_DOES_NOT_EXIST.format(
                    object_type=cls.get_class_name(), object_id=obj_id
                ),
            )
        return obj

    @classmethod
    def get_by_source_file_key(cls, file_key: str):
        """Get a StatementPeriodAdjustmentFile by source file_key.

        Args:
            file_key (str): file_key from file_upload table.

        Returns:
            StatementPeriodAdjustmentFile: The matching entity or None if not found.
        """
        return (
            cls.query.join(FileUpload)
            .filter(
                FileUpload.file_key == file_key,
                cls.deleted_at.is_(None),
                cls.deleted_by.is_(None),
            )
            .first()
        )

    @classmethod
    def get_statement_period_adjustment_files(
        cls,
        limit: int,
        offset: int,
        sort_by: str,
        sort_order: str,
        statement_period_adjustment_file_id: int = None,
        statement_period_id: int = None,
        file_name: str = None,
        created_by: str = None,
        status: str = None,
    ) -> tuple:
        """Get a list of statement period adjustment files.

        Args:
            limit(int): the size of page
            offset(int): the number of items to skip before returning results
            sort_by (str): column name by which the results should be sorted
            sort_order (str): order direction (asc or desc)
            statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
            statement_period_id (int): id of the statement_period
            file_name (str): name of the statement_period_adjustment_file
            status (str): status of the adjustment file can either be approved, not_approved, or applied
            created_by (str): identity id or name of the user who uploaded the statement_period_adjustment_file
        Returns:
            A tuple contains the fields items and total_count
        """
        filter_by = [
            literal_column('spaf.deleted_at').is_(None),
            literal_column('spaf.deleted_by').is_(None),
        ]
        status_filter = list()

        if statement_period_adjustment_file_id:
            filter_by.append(
                literal_column('spaf.statement_period_adjustment_file_id')
                == statement_period_adjustment_file_id
            )

        if statement_period_id:
            filter_by.append(
                literal_column('spaf.statement_period_id') == statement_period_id
            )

        if file_name:
            file_name_text = file_name.replace('\\', '\\\\').replace('%', '\\%')
            filter_by.append(
                literal_column('spaf.file_name').ilike(f'%{file_name_text}%')
            )

        if created_by:
            filter_by.append(literal_column('spaf.created_by') == created_by)

        if status:
            statuses = list(map(str.strip, status.split(',')))
            if 'approved' in statuses:
                status_filter.append(
                    text("""
                        approve_file_sub_query.action_status = "complete"
                        AND (apply_file_sub_query.action_status IS NULL OR
                        apply_file_sub_query.action_status NOT IN ("complete"))
                    """)
                )

            if 'applied' in statuses:
                status_filter.append(
                    text("""
                        apply_file_sub_query.action_status = "complete"
                        AND approve_file_sub_query.action_status = "complete"
                        AND upload_file_sub_query.action_status = "complete"
                    """)
                )

            if 'not_approved' in statuses:
                status_filter.append(
                    text("""
                        upload_file_sub_query.action_status = "complete"
                        AND (approve_file_sub_query.action_status IS NULL OR
                        approve_file_sub_query.action_status NOT IN ("complete"))
                    """)
                )

            if 'failed_to_generate' in statuses:
                status_filter.append(
                    or_(
                        text('upload_file_sub_query.action_status = "error"'),
                        text('validate_file_sub_query.action_status = "error"'),
                        text('import_file_sub_query.action_status = "error"'),
                    )
                )

            filter_by.append(or_(*status_filter))

        if is_abacus_auto_generate_adjustments_flowthrough_ff_enabled():
            sql = cls._query_to_get_statement_period_adjustment_files_new_ff(filter_by)
        else:
            sql = cls._query_to_get_statement_period_adjustment_files(filter_by)

        query = (
            sql.order_by(desc(sort_by) if sort_order == 'desc' else asc(sort_by))
            .offset(offset)
            .limit(limit)
        )

        items = db.engine.execute(query).fetchall()

        query = select(func.count()).select_from(
            sql.order_by(None).limit(None).offset(None).subquery()
        )
        total_count = db.engine.execute(query).scalar_one()

        return items, total_count

    @staticmethod
    def _query_to_get_statement_period_adjustment_files(filter_by: list):
        """Build a query to get a list of statement period adjustment files.

        For Abacus states "upload_file" and "apply_pending_adjustments", subqueries are created
        and then joined with statement_period_adjustment_file table.

        The file status is
        - "Applied" if action_status of "apply_file" abacus_state is complete.
        - "Approved" if action_status of "approve_file" abacus_state is complete.
        - "Not Approved" if action_status of "upload_file" abacus_state is complete.
        """
        # check if adjustments are imported in worksheet_adjustment table
        worksheet_adjustment_subquery = select(
            [distinct(literal_column('statement_period_adjustment_file_id'))]
        ).select_from(table('worksheet_adjustment'))
        filter_by.append(
            literal_column('spaf.statement_period_adjustment_file_id').in_(
                worksheet_adjustment_subquery
            )
        )

        upload_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'upload_file',
                    literal_column('astate.action_status') == 'complete',
                )
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('upload_file_sub_query')
        )

        approve_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'approve_file',
                    literal_column('astate.action_status') == 'complete',
                )
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('approve_file_sub_query')
        )

        apply_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'apply_file',
                    literal_column('astate.action_status') == 'complete',
                ),
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('apply_file_sub_query')
        )

        abacus_event_sub_query = (
            select(
                [
                    func.max(literal_column('aevent.abacus_event_id')).label(
                        'abacus_event_id'
                    ),
                    literal_column('aevent.target_id').label('target_id'),
                ]
            )
            .where(
                and_(
                    literal_column('aevent.target_type')
                    == 'statement_period_adjustment_file',
                    literal_column('aevent.event_name') == 'apply_pending_adjustments',
                ),
            )
            .select_from(table('abacus_event').alias('aevent'))
            .group_by(literal_column('aevent.target_id'))
            .alias('abacus_event_sub_query')
        )

        apply_file_event_sub_query = (
            select(
                [
                    literal_column('aevent2.abacus_event_id').label('abacus_event_id'),
                    literal_column('aevent2.event_date').label('event_date'),
                    literal_column('aevent2.created_by').label('created_by'),
                ]
            )
            .select_from(table('abacus_event').alias('aevent2'))
            .alias('apply_file_event_sub_query')
        )

        return (
            select(
                [
                    literal_column(
                        """
                    CASE
                        WHEN apply_file_sub_query.action_name = 'apply_file'
                            AND apply_file_sub_query.action_status = 'complete'
                            AND approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                            AND upload_file_sub_query.action_name = 'upload_file'
                            AND upload_file_sub_query.action_status = 'complete'
                        THEN 'applied'
                        WHEN approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                            AND upload_file_sub_query.action_name = 'upload_file'
                            AND upload_file_sub_query.action_status = 'complete'
                        THEN 'approved'
                        WHEN upload_file_sub_query.action_name = 'upload_file'
                            AND upload_file_sub_query.action_status = 'complete'
                        THEN 'not_approved'
                        ELSE NULL
                    END
                """
                    ).label('status'),
                    # We need to explicitly list columns to allow sorting
                    # "Total adjustments and expenses" column on UI
                    literal_column('spaf.valid_row_count').label('valid_row_count'),
                    # "Batch Id" column on UI
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    # "Date Added" column on UI
                    literal_column('spaf.created_at').label('created_at'),
                    # "Added By" column on UI
                    literal_column('spaf.created_by').label('created_by'),
                    literal_column('spaf.file_name').label('file_name'),
                    literal_column('spaf.total_rounded_amount_multicurrency').label(
                        'total_rounded_amount_multicurrency'
                    ),
                    literal_column('spaf.statement_period_id').label(
                        'statement_period_id'
                    ),
                    literal_column(
                        """
                    CASE
                        WHEN approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                        THEN approve_file_sub_query.last_modified
                        ELSE NULL
                    END
                """
                    ).label('date_approved'),
                    literal_column(
                        """
                    CASE
                        WHEN approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                        THEN approve_file_sub_query.last_modified_by
                        ELSE NULL
                    END
                """
                    ).label('approved_by'),
                    literal_column(
                        """
                    CASE
                        WHEN apply_file_sub_query.action_name = 'apply_file'
                            AND apply_file_sub_query.action_status = 'complete'
                        THEN apply_file_event_sub_query.event_date
                        ELSE NULL
                    END
                """
                    ).label('date_applied'),
                    literal_column(
                        """
                    CASE
                        WHEN apply_file_sub_query.action_name = 'apply_file'
                            AND apply_file_sub_query.action_status = 'complete'
                        THEN apply_file_event_sub_query.created_by
                        ELSE NULL
                    END
                """
                    ).label('applied_by'),
                ]
            )
            .where(and_(*filter_by))
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    upload_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=upload_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    approve_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=approve_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    apply_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=apply_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    abacus_event_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=abacus_event_sub_query.target_id'
                    ),
                )
                .outerjoin(
                    apply_file_event_sub_query,
                    text(
                        'abacus_event_sub_query.abacus_event_id=apply_file_event_sub_query.abacus_event_id'
                    ),
                )
            )
        )

    @staticmethod
    def _query_to_get_statement_period_adjustment_files_new_ff(filter_by: list):
        """Build a query to get a list of statement period adjustment files.

        For Abacus states "upload_file" and "apply_pending_adjustments", subqueries are created
        and then joined with statement_period_adjustment_file table.

        The file status is
        - "Failed To Generate" if batch_type is auto and action_status of 'upload_file' or
           'validate_file' or 'import_file' is in error state.
        - "No Records" if batch_type is auto and action_status of 'upload_file' is complete
           'validate_file' or 'import_file' is in init state and file is uploaded to s3 bucket.
        - "Applied" if action_status of "apply_file" abacus_state is complete.
        - "Approved" if action_status of "approve_file" abacus_state is complete.
        - "Not Approved" if action_status of "upload_file" abacus_state is complete.
        """
        # check if adjustments are imported in worksheet_adjustment table
        worksheet_adjustment_subquery = select(
            [distinct(literal_column('statement_period_adjustment_file_id'))]
        ).select_from(table('worksheet_adjustment'))
        filter_by.append(
            or_(
                literal_column('spaf.statement_period_adjustment_file_id').in_(
                    worksheet_adjustment_subquery
                ),
                text('spaf.batch_type="auto"'),
            )
        )

        upload_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'upload_file',
                    or_(
                        literal_column('astate.action_status') == 'complete',
                        text('spaf.batch_type="auto"'),
                    ),
                )
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('upload_file_sub_query')
        )

        approve_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'approve_file',
                    literal_column('astate.action_status') == 'complete',
                )
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('approve_file_sub_query')
        )

        validate_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'validate_file',
                    text('spaf.batch_type="auto"'),
                )
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('validate_file_sub_query')
        )

        import_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'import_file',
                )
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('import_file_sub_query')
        )

        apply_file_sub_query = (
            select(
                [
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    literal_column('astate.*'),
                ]
            )
            .where(
                and_(
                    literal_column('astate.parent_table_name')
                    == 'statement_period_adjustment_file',
                    literal_column('astate.action_name') == 'apply_file',
                    literal_column('astate.action_status') == 'complete',
                ),
            )
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    table('abacus_state').alias('astate'),
                    text(
                        'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                    ),
                )
            )
            .alias('apply_file_sub_query')
        )

        abacus_event_sub_query = (
            select(
                [
                    func.max(literal_column('aevent.abacus_event_id')).label(
                        'abacus_event_id'
                    ),
                    literal_column('aevent.target_id').label('target_id'),
                ]
            )
            .where(
                and_(
                    literal_column('aevent.target_type')
                    == 'statement_period_adjustment_file',
                    literal_column('aevent.event_name') == 'apply_pending_adjustments',
                ),
            )
            .select_from(table('abacus_event').alias('aevent'))
            .group_by(literal_column('aevent.target_id'))
            .alias('abacus_event_sub_query')
        )

        apply_file_event_sub_query = (
            select(
                [
                    literal_column('aevent2.abacus_event_id').label('abacus_event_id'),
                    literal_column('aevent2.event_date').label('event_date'),
                    literal_column('aevent2.created_by').label('created_by'),
                ]
            )
            .select_from(table('abacus_event').alias('aevent2'))
            .alias('apply_file_event_sub_query')
        )

        return (
            select(
                [
                    literal_column(
                        """
                    CASE
                        WHEN apply_file_sub_query.action_name = 'apply_file'
                            AND apply_file_sub_query.action_status = 'complete'
                            AND approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                            AND upload_file_sub_query.action_name = 'upload_file'
                            AND upload_file_sub_query.action_status = 'complete'
                        THEN 'applied'
                        WHEN approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                            AND upload_file_sub_query.action_name = 'upload_file'
                            AND upload_file_sub_query.action_status = 'complete'
                        THEN 'approved'
                        WHEN spaf.batch_type = 'auto' THEN
                        CASE
                            WHEN
                                (upload_file_sub_query.action_name = 'upload_file' AND upload_file_sub_query.action_status = 'error') OR
                                (validate_file_sub_query.action_name = 'validate_file' AND validate_file_sub_query.action_status = 'error') OR
                                (import_file_sub_query.action_name = 'import_file' AND import_file_sub_query.action_status = 'error')
                            THEN 'failed_to_generate'
                            WHEN
                                (upload_file_sub_query.action_name = 'upload_file' AND upload_file_sub_query.action_status = 'running') OR
                                (validate_file_sub_query.action_name = 'validate_file' AND validate_file_sub_query.action_status = 'running') OR
                                (import_file_sub_query.action_name = 'import_file' AND import_file_sub_query.action_status = 'running')
                            THEN 'generating'
                            WHEN
                                (upload_file_sub_query.action_name = 'upload_file' AND upload_file_sub_query.action_status = 'complete') AND
                                (validate_file_sub_query.action_name = 'validate_file' AND validate_file_sub_query.action_status = 'init') AND
                                (import_file_sub_query.action_name = 'import_file' AND import_file_sub_query.action_status = 'init') AND
                                spaf.valid_file_location is null
                            THEN 'no_records'
                            WHEN
                                (upload_file_sub_query.action_name = 'upload_file' AND upload_file_sub_query.action_status = 'complete') AND
                                (validate_file_sub_query.action_name = 'validate_file' AND validate_file_sub_query.action_status = 'complete') AND
                                (import_file_sub_query.action_name = 'import_file' AND import_file_sub_query.action_status = 'complete')
                            THEN 'not_approved'
                        END
                        WHEN upload_file_sub_query.action_name = 'upload_file'
                            AND upload_file_sub_query.action_status = 'complete'
                        THEN 'not_approved'
                        ELSE NULL
                    END
                """
                    ).label('status'),
                    # We need to explicitly list columns to allow sorting
                    # "Total adjustments and expenses" column on UI
                    literal_column('spaf.valid_row_count').label('valid_row_count'),
                    # "Batch Id" column on UI
                    literal_column('spaf.statement_period_adjustment_file_id').label(
                        'statement_period_adjustment_file_id'
                    ),
                    # "Date Added" column on UI
                    literal_column('spaf.created_at').label('created_at'),
                    # "Added By" column on UI
                    literal_column('spaf.created_by').label('created_by'),
                    literal_column('spaf.file_name').label('file_name'),
                    literal_column('spaf.total_rounded_amount_multicurrency').label(
                        'total_rounded_amount_multicurrency'
                    ),
                    literal_column('spaf.statement_period_id').label(
                        'statement_period_id'
                    ),
                    literal_column('spaf.batch_type').label('batch_type'),
                    literal_column(
                        """
                    CASE
                        WHEN approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                        THEN approve_file_sub_query.last_modified
                        ELSE NULL
                    END
                """
                    ).label('date_approved'),
                    literal_column(
                        """
                    CASE
                        WHEN approve_file_sub_query.action_name = 'approve_file'
                            AND approve_file_sub_query.action_status = 'complete'
                        THEN approve_file_sub_query.last_modified_by
                        ELSE NULL
                    END
                """
                    ).label('approved_by'),
                    literal_column(
                        """
                    CASE
                        WHEN apply_file_sub_query.action_name = 'apply_file'
                            AND apply_file_sub_query.action_status = 'complete'
                        THEN apply_file_event_sub_query.event_date
                        ELSE NULL
                    END
                """
                    ).label('date_applied'),
                    literal_column(
                        """
                    CASE
                        WHEN apply_file_sub_query.action_name = 'apply_file'
                            AND apply_file_sub_query.action_status = 'complete'
                        THEN apply_file_event_sub_query.created_by
                        ELSE NULL
                    END
                """
                    ).label('applied_by'),
                ]
            )
            .where(and_(*filter_by))
            .select_from(
                join(
                    table('statement_period_adjustment_file').alias('spaf'),
                    upload_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=upload_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    validate_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=validate_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    import_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=import_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    approve_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=approve_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    apply_file_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=apply_file_sub_query.parent_table_id'
                    ),
                )
                .outerjoin(
                    abacus_event_sub_query,
                    text(
                        'spaf.statement_period_adjustment_file_id=abacus_event_sub_query.target_id'
                    ),
                )
                .outerjoin(
                    apply_file_event_sub_query,
                    text(
                        'abacus_event_sub_query.abacus_event_id=apply_file_event_sub_query.abacus_event_id'
                    ),
                )
            )
        )

    @classmethod
    def get_statement_period_adjustment_file_users(cls, user_action: str):
        """Get the list of users by user action.

        Args:
            user_action (str): can be "uploaded-file"
        Returns:
            a list of users.
        """
        if user_action == STATEMENT_PERIOD_ADJUSTMENT_FILE_USER_ACTIONS.UPLOADED_FILE:
            query = (
                select([literal_column('spaf.created_by').label('created_by')])
                .where(
                    and_(
                        literal_column('astate.parent_table_name')
                        == 'statement_period_adjustment_file',
                        literal_column('astate.action_name') == 'approve_file',
                        literal_column('astate.action_status') == 'complete',
                        literal_column('spaf.deleted_at').is_(None),
                        literal_column('spaf.deleted_by').is_(None),
                    )
                )
                .select_from(
                    join(
                        table('statement_period_adjustment_file').alias('spaf'),
                        table('abacus_state').alias('astate'),
                        text(
                            'spaf.statement_period_adjustment_file_id=astate.parent_table_id'
                        ),
                    )
                )
                .distinct()
                .order_by(asc(text('created_by')))
            )

            items = db.engine.execute(query).fetchall()

            return items, len(items)

        return (list(), 0)

    @classmethod
    def get_in_progress_auto_generated_adjustments(
        cls, statement_period_id: int, identity_id: str
    ) -> Optional[Row]:
        """Get the auto-generated adjustment file that are currently being processed or have encountered an error.

        Args:
            statement_period_id (int): id of the statement period
            identity_id (str): identity id of the individual who generated the adjustments

        Returns:
            in progress/error 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'),
                    literal_column('spaf.statement_period_id').label(
                        'statement_period_id'
                    ),
                    literal_column("""
                    CASE
                        WHEN
                            astate_upload.action_status = 'error' OR
                            astate_validate.action_status = 'error' OR
                            astate_import.action_status = 'error'
                        THEN 'failed_to_generate'
                        WHEN
                            astate_upload.action_status = 'running' OR
                            astate_validate.action_status = 'running' OR
                            astate_import.action_status = 'running'
                        THEN 'generating'
                        ELSE NULL
                    END
                """).label('status'),
                ]
            )
            .select_from(
                table('statement_period_adjustment_file')
                .alias('spaf')
                .outerjoin(
                    table('abacus_state').alias('astate_upload'),
                    text("""
                        astate_upload.parent_table_name = 'statement_period_adjustment_file' AND
                        astate_upload.action_name = 'upload_file' AND
                        spaf.statement_period_adjustment_file_id = astate_upload.parent_table_id
                    """),
                )
                .outerjoin(
                    table('abacus_state').alias('astate_validate'),
                    text("""
                        astate_validate.parent_table_name = 'statement_period_adjustment_file' AND
                        astate_validate.action_name = 'validate_file' AND
                        spaf.statement_period_adjustment_file_id = astate_validate.parent_table_id
                    """),
                )
                .outerjoin(
                    table('abacus_state').alias('astate_import'),
                    text("""
                        astate_import.parent_table_name = 'statement_period_adjustment_file' AND
                        astate_import.action_name = 'import_file' AND
                        spaf.statement_period_adjustment_file_id = astate_import.parent_table_id
                    """),
                )
            )
            .where(
                and_(
                    or_(
                        literal_column('astate_upload.action_status').in_(
                            ['running', 'error']
                        ),
                        literal_column('astate_validate.action_status').in_(
                            ['running', 'error']
                        ),
                        literal_column('astate_import.action_status').in_(
                            ['running', 'error']
                        ),
                    ),
                    literal_column('spaf.batch_type') == 'auto',
                    literal_column('spaf.statement_period_id') == statement_period_id,
                    literal_column('spaf.deleted_at').is_(None),
                    literal_column('spaf.deleted_by').is_(None),
                    literal_column('spaf.created_by') == identity_id,
                    or_(
                        literal_column('astate_upload.last_modified')
                        >= func.utc_timestamp() - text('INTERVAL 5 MINUTE'),
                        literal_column('astate_validate.last_modified')
                        >= func.utc_timestamp() - text('INTERVAL 5 MINUTE'),
                        literal_column('astate_import.last_modified')
                        >= func.utc_timestamp() - text('INTERVAL 5 MINUTE'),
                    ),
                )
            )
            .order_by(desc(literal_column('spaf.statement_period_adjustment_file_id')))
        )

        items = db.engine.execute(query).fetchall()

        return items