"""Logic for WorksheetAdjustment."""

import os
from decimal import Decimal
from typing import Type

import numpy as np
import pandas as pd
import sqlalchemy
from abacus_common_logic.connectors.database import db
from abacus_common_logic.constants.error import ERROR_ENTITY_DOES_NOT_EXIST
from marshmallow import ValidationError
from owsresponse import response
from sqlalchemy.exc import OperationalError

from abacus_worksheet.config import Config
from abacus_worksheet.connectors.s3 import download_file
from abacus_worksheet.constants.constants import (
    ADJUSTMENT_EXCEL_SHEET_HEADERS_MAPPING,
    DEFAULT_PAGE_LIMIT,
    DEFAULT_PAGE_OFFSET,
)
from abacus_worksheet.constants.error import (
    ERROR_BATCH_APPLIED,
    ERROR_DELETE_WORKSHEET_ADJUSTMENTS,
    ERROR_ENTRY_APPLIED,
    ERROR_FILE_SOFT_DELETED,
    ERROR_WORKSHEET_ADJUSTMENTS_ALREADY_DELETED,
    ERROR_WORKSHEET_ADJUSTMENTS_MULTIPLE_FILES,
    ERROR_WORKSHEET_ADJUSTMENTS_NOT_DELETED,
    ERROR_WORKSHEET_ADJUSTMENTS_NOT_FOUND,
)
from abacus_worksheet.logic.worksheet_adjustment_detail import (
    create_worksheet_adjustment_details,
)
from abacus_worksheet.models.reference_adjustment_type import ReferenceAdjustmentType
from abacus_worksheet.models.statement_period import StatementPeriod
from abacus_worksheet.models.statement_period_adjustment_file import (
    StatementPeriodAdjustmentFile,
)
from abacus_worksheet.models.worksheet_adjustment import WorksheetAdjustment
from abacus_worksheet.models.worksheet_adjustment_detail import (
    WorksheetAdjustmentDetail,
)
from abacus_worksheet.schemas.worksheet_adjustment import (
    PendingWorksheetAdjustmentsRequestParamsSchema,
    WorksheetAdjustmentAccountContractFilterSchema,
    WorksheetAdjustmentAccountsListSchema,
    WorksheetAdjustmentAndDetailFilterSchema,
    WorksheetAdjustmentAndDetailSchema,
    WorksheetAdjustmentContractsListSchema,
    WorksheetAdjustmentDeletedAggregateSchema,
    WorksheetAdjustmentListSchema,
)
from abacus_worksheet.utils.format_error import validation_error
from abacus_worksheet.utils.request import validate_pagination_params


def get_by_statement_period_adjustment_file_id(
    statement_period_adjustment_file_id: int, request_params: dict
) -> Type[response.Response]:
    """Get a list of worksheet adjustments by statement_period_adjustment_file_id.

    Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
        request_params (dict)(Optional): dict of query string passed to the url
            - limit(int): pagination limit
            - offset(int): pagination offset
    """
    try:
        limit = request_params.get('limit', DEFAULT_PAGE_LIMIT)
        offset = request_params.get('offset', DEFAULT_PAGE_OFFSET)
        pagination_params = validate_pagination_params(limit, offset)

        items, total_count = (
            WorksheetAdjustment.get_by_statement_period_adjustment_file_id(
                statement_period_adjustment_file_id, **pagination_params
            )
        )

        message = {
            'items': WorksheetAdjustmentListSchema(many=True).dump(items),
            'total_count': total_count,
        }
    except ValidationError as e:
        return validation_error(str(e))

    return response.Response(message=message, status=200)


def _create_worksheet_adjustments(worksheet_adjustments: list) -> list:
    """Create one or more worksheet adjustments.

    Args:
        worksheet_adjustments (list): a list of worksheet_adjustment data
    Returns:
        a list of new worksheet_adjustment records
    """
    worksheet_adjustment_records = []
    count = 0
    try:
        for worksheet_adjustment in worksheet_adjustments:
            record = WorksheetAdjustment.build(**worksheet_adjustment)
            worksheet_adjustment_records.append(record)
            count = count + 1

            if count >= Config.INSERTION_LIMIT:
                WorksheetAdjustment.commit_changes()
                count = 0

        if count <= Config.INSERTION_LIMIT:
            WorksheetAdjustment.commit_changes()
    except sqlalchemy.exc.SQLAlchemyError as e:
        print('Error while inserting worksheet adjustments.')
        db.session.rollback()
        raise e

    return worksheet_adjustment_records


def _create_worksheet_adjustments_and_details(expense_details: list) -> list:
    """Create one or more worksheet adjustments and details.

    Args:
        expense_details (list): a list of dict containing
            worksheet_adjustment and worksheet_adjustment_detail records
    Returns:
        a list of new worksheet_adjustment records
    """
    worksheet_adjustment_records = []

    try:
        for expense_detail in expense_details:
            worksheet_adjustment = WorksheetAdjustment.build(
                **expense_detail['worksheet_adjustment']
            )
            worksheet_adjustment_records.append(worksheet_adjustment)
            db.session.flush()

            create_worksheet_adjustment_details(
                expense_detail['worksheet_adjustment_detail'],
                worksheet_adjustment.worksheet_adjustment_id,
            )

            WorksheetAdjustment.commit_changes()
    except sqlalchemy.exc.SQLAlchemyError as e:
        print('Error while inserting account expenses.')
        db.session.rollback()
        raise e

    return worksheet_adjustment_records


def create_worksheet_adjustments_and_details(
    statement_period_adjustment_file_id: int, abacus_event_id: int
) -> Type[response.Response]:
    """Create worksheet adjustments and worksheet adjustments details.

    - Gets the statement_period_adjustment_file record by ID.
    - Then, download the adjustment file from the S3 bucket.
    - Reads the adjustment file and inserts the data into the
      worksheet_adjustment/worksheet_adjustment_detail table.

    Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
        abacus_event_id (int): id of the abacus event
    """
    statement_period_adjustment_file = (
        StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
            statement_period_adjustment_file_id
        )
    )

    if (not statement_period_adjustment_file) or (
        statement_period_adjustment_file['deleted_by'] is not None
        and statement_period_adjustment_file['deleted_at'] is not None
    ):
        return response.create_error_response(
            code='error',
            status=404,
            message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                object_type='StatementPeriodAdjustmentFile',
                object_id=statement_period_adjustment_file_id,
            ),
        )

    s3_file_location = statement_period_adjustment_file['valid_file_location']

    if not s3_file_location:
        return response.create_error_response(
            code='error', status=404, message='Invalid adjustment file path'
        )

    s3_file_path = s3_file_location.split(Config.S3_ADJUSTMENTS_BUCKET + '/')

    adjustment_file = download_file(
        Config.S3_ACCOUNT_ID, Config.S3_ADJUSTMENTS_BUCKET, s3_file_path[1]
    )

    df = pd.read_excel(adjustment_file, na_filter=False)
    # below code will remove the empty rows
    df = df.astype(str).replace(r'^\s*$', np.nan, regex=True)
    df = df.dropna(how='all')
    df = df.astype(object).where(pd.notnull(df), None)

    if df.empty:
        return response.create_error_response(
            code='error',
            status=404,
            message='No adjustments records found in excel sheet',
        )

    df.rename(columns=ADJUSTMENT_EXCEL_SHEET_HEADERS_MAPPING, inplace=True)

    activity_years = set(df.loc[:, 'activity_year'].dropna())
    statement_years = set(df.loc[:, 'statement_year'].dropna())

    years = activity_years.union(statement_years)
    years = list(map(int, years))

    statement_periods = get_formatted_statement_periods(years)
    reference_adjustment_types = get_formatted_reference_adjustment_types()

    adjustments = []
    adjustment_details = []

    for _, row in df.iterrows():
        if row['upc'] and row['distribution_type']:
            adjustment_detail = formatted_worksheet_adjustment_detail(
                row,
                reference_adjustment_types,
                statement_periods,
                statement_period_adjustment_file_id,
            )
            adjustment_details.append(adjustment_detail)
        else:
            adjustment = formatted_worksheet_adjustment(
                abacus_event_id,
                row,
                reference_adjustment_types,
                statement_periods,
                statement_period_adjustment_file_id,
            )
            adjustments.append(adjustment)

    _create_worksheet_adjustments(adjustments)

    if adjustment_details:
        expense_details = get_expense_details_records(
            statement_period_adjustment_file_id, abacus_event_id, adjustment_details
        )
        _create_worksheet_adjustments_and_details(expense_details)

    if os.path.exists(adjustment_file):
        os.remove(adjustment_file)

    return response.Response(
        message={'message': 'Records inserted succesfully.'}, status=201
    )


def get_formatted_statement_periods(statement_years: list) -> dict:
    """Get a dict of formatted statement periods.

    Args:
        statement_years (list): a list of statement years
    Returns:
        A dict containing statement_period_ids mapped with statement month and year
        eg. {2023:{ 11: 299, 12: 300}, 2024: {1: 301}}
    """
    statement_periods = StatementPeriod.get_statement_periods(statement_years)
    formatted_statement_periods = dict()

    for statement_period in statement_periods:
        if statement_period['statement_year'] not in formatted_statement_periods.keys():
            formatted_statement_periods.update(
                {int(statement_period['statement_year']): dict()}
            )

        formatted_statement_periods[int(statement_period['statement_year'])].update(
            {
                int(statement_period['statement_month']): int(
                    statement_period['statement_period_id']
                )
            }
        )

    return formatted_statement_periods


def get_formatted_reference_adjustment_types() -> dict:
    """Get a dict of formatted adjustment types.

    Returns:
        a dict containing reference_adjustment_type_ids mapped with type_name
        eg. {'Label Earnings': 1}
    """
    reference_adjustment_types = ReferenceAdjustmentType.get_adjustment_types()
    adjustment_types = dict()

    for adjustment_type in reference_adjustment_types:
        adjustment_types[adjustment_type['type_name'].lower()] = adjustment_type[
            'reference_adjustment_type_id'
        ]

    return adjustment_types


def formatted_worksheet_adjustment_detail(
    adjustment_row: dict,
    reference_adjustment_types: dict,
    statement_periods: dict,
    statement_period_adjustment_file_id: int,
) -> dict:
    """Format worksheet_adjustment_detail record.

    Args:
        adjustment_row (dict): a dict of adjustment data
        reference_adjustment_types (dict): a dict of formatted adjustment types
        statement_periods (dict): a dict of formatted statement period
        statement_period_adjustment_file_id (int): id of the related statement_period_adjustment_file
    Returns:
        a dict of worksheet_adjustment_detail record
    """
    row = adjustment_row
    return {
        'statement_period_adjustment_file_id': statement_period_adjustment_file_id,
        'account_id': row['account_id'],
        'contract_id': row['contract_id'],
        'activity_statement_period_id': statement_periods[int(row['activity_year'])][
            int(row['activity_month'])
        ],
        'apply_to_statement_period_id': statement_periods[int(row['statement_year'])][
            int(row['statement_month'])
        ],
        'reference_adjustment_type_id': reference_adjustment_types[
            row['adjustment_type'].lower()
        ],
        'amount': Decimal(row['amount']),
        'currency_code': row['currency'],
        'upc': row['upc'],
        'distribution_type': row['distribution_type'].lower()
        if row['distribution_type']
        else '',
        'note': row['client_facing_comments'],
        'internal_note': row['internal_note'],
    }


def formatted_worksheet_adjustment(
    abacus_event_id: int,
    adjustment_row: dict,
    reference_adjustment_types: dict,
    statement_periods: dict,
    statement_period_adjustment_file_id: int,
) -> dict:
    """Format worksheet_adjustment record.

    Args:
        abacus_event_id (int): id of the related abacus_event
        adjustment_row (dict): a dict of adjustment data
        reference_adjustment_types (dict): a dict of formatted adjustment types
        statement_periods (dict): a dict of formatted statement period
        statement_period_adjustment_file_id (int): id of the related statement_period_adjustment_file
    Returns:
        a dict of worksheet_adjustment record
    """
    row = adjustment_row

    return {
        'statement_period_adjustment_file_id': statement_period_adjustment_file_id,
        'abacus_event_id': abacus_event_id,
        'account_id': row['account_id'],
        'contract_id': row['contract_id'],
        'activity_statement_period_id': statement_periods[int(row['activity_year'])][
            int(row['activity_month'])
        ],
        'apply_to_statement_period_id': statement_periods[int(row['statement_year'])][
            int(row['statement_month'])
        ],
        'reference_adjustment_type_id': reference_adjustment_types[
            row['adjustment_type'].lower()
        ],
        'adjustment_amount': Decimal(row['amount']),
        'adjustment_currency_code': row['currency'],
        'note': row['client_facing_comments'],
        'internal_note': row['internal_note'],
    }


def get_expense_details_records(
    statement_period_adjustment_file_id: int,
    abacus_event_id: int,
    worksheet_adjustment_details: list,
) -> dict:
    """Format and get expense details records.

    Args:
        abacus_event_id (int): id of the related abacus_event
        statement_period_adjustment_file_id (int): id of the related statement_period_adjustment_file
        worksheet_adjustment_details (list): a list of worksheet_adjustment_detail's
    Returns:
        a list of dict containing worksheet_adjustment and worksheet_adjustment_detail records
    """
    expenses = []
    expense_details = pd.DataFrame(worksheet_adjustment_details)

    grouped_expense_details = (
        expense_details.groupby(
            [
                'account_id',
                'contract_id',
                'currency_code',
                'activity_statement_period_id',
                'apply_to_statement_period_id',
            ]
        )['amount']
        .sum()
        .reset_index()
    )

    for index, expense in grouped_expense_details.iterrows():
        adjustment_details = expense_details.loc[
            (expense_details['account_id'] == expense['account_id'])
            & (expense_details['contract_id'] == expense['contract_id'])
            & (expense_details['currency_code'] == expense['currency_code'])
            & (
                expense_details['activity_statement_period_id']
                == expense['activity_statement_period_id']
            )
            & (
                expense_details['apply_to_statement_period_id']
                == expense['apply_to_statement_period_id']
            )
        ]
        expenses.append(
            {
                'worksheet_adjustment': {
                    'statement_period_adjustment_file_id': statement_period_adjustment_file_id,
                    'abacus_event_id': abacus_event_id,
                    'account_id': expense['account_id'],
                    'contract_id': expense['contract_id'],
                    'activity_statement_period_id': expense[
                        'activity_statement_period_id'
                    ],
                    'apply_to_statement_period_id': expense[
                        'apply_to_statement_period_id'
                    ],
                    'reference_adjustment_type_id': 65,  # "Account Expense" category id
                    'adjustment_amount': expense['amount'],
                    'adjustment_currency_code': expense['currency_code'],
                },
                'worksheet_adjustment_detail': list(
                    adjustment_details.T.to_dict().values()
                ),
            }
        )

    return expenses


def soft_delete_worksheet_adjustments_and_details(
    statement_period_adjustment_file_id: int,
):
    """Soft delete worksheet adjustments and details by statement_period_adjustment_file_id.

    Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
    """
    try:
        statement_period_adjustment_file = (
            StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
                statement_period_adjustment_file_id
            )
        )

        if not statement_period_adjustment_file:
            return response.create_error_response(
                code='error',
                status=404,
                message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                    object_type='StatementPeriodAdjustmentFile',
                    object_id=statement_period_adjustment_file_id,
                ),
            )

        if (
            statement_period_adjustment_file
            and statement_period_adjustment_file['deleted_by'] is None
            and statement_period_adjustment_file['deleted_at'] is None
        ):
            db.session.rollback()
            return response.create_error_response(
                code='error',
                status=400,
                message=ERROR_DELETE_WORKSHEET_ADJUSTMENTS.format(
                    statement_period_adjustment_file_id
                ),
            )

        applied = WorksheetAdjustment.get_applied_ids_for_file(
            statement_period_adjustment_file_id
        )
        if applied:
            db.session.rollback()
            return response.create_error_response(
                code='error',
                status=409,
                message=ERROR_ENTRY_APPLIED.format(sorted(applied)),
            )
        WorksheetAdjustmentDetail.soft_delete_worksheet_adjustment_details(
            statement_period_adjustment_file_id
        )
        WorksheetAdjustment.soft_delete_worksheet_adjustments(
            statement_period_adjustment_file_id
        )
        db.session.commit()
    except OperationalError as e:
        db.session.rollback()
        return _handle_applied_trigger_or_raise(e)
    except sqlalchemy.exc.SQLAlchemyError as e:
        print(
            'Error while deleting worksheet adjustments or worksheet adjustment details.'
        )
        db.session.rollback()
        raise e

    return response.Response(status=204)


def _guard_live_batch_entries(worksheet_adjustment_ids, locked):
    """Validate the id set and batch state for a delete/restore request.

    Returns an error Response if the id set is invalid or the batch is not a
    live, un-applied file; otherwise None. Does not roll back or commit.
    """
    found_ids = {wa.worksheet_adjustment_id for wa in locked}
    missing = set(worksheet_adjustment_ids) - found_ids
    if missing:
        return response.create_error_response(
            code='error',
            status=404,
            message=ERROR_WORKSHEET_ADJUSTMENTS_NOT_FOUND.format(sorted(missing)),
        )

    file_ids = {wa.statement_period_adjustment_file_id for wa in locked}
    if len(file_ids) != 1:
        return response.create_error_response(
            code='error',
            status=400,
            message=ERROR_WORKSHEET_ADJUSTMENTS_MULTIPLE_FILES,
        )

    file_id = file_ids.pop()
    spaf = StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
        file_id
    )
    if spaf is None:
        return response.create_error_response(
            code='error',
            status=404,
            message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                object_type='StatementPeriodAdjustmentFile', object_id=file_id
            ),
        )
    if spaf['deleted_at'] is not None:
        return response.create_error_response(
            code='error',
            status=400,
            message=ERROR_FILE_SOFT_DELETED.format(file_id),
        )
    if StatementPeriodAdjustmentFile.is_apply_complete(file_id):
        return response.create_error_response(
            code='error',
            status=409,
            message=ERROR_BATCH_APPLIED.format(file_id),
        )
    return None


TRIGGER_APPLIED_SIGNAL_ERRNO = 1644


def _handle_applied_trigger_or_raise(error):
    """Map the ledger-applied trigger signal to a clean 409, or re-raise.

    The trigger raises SIGNAL SQLSTATE '45000' (MySQL errno 1644); that case
    returns a 409, any other OperationalError is re-raised. The session must
    already be rolled back by the caller.
    """
    # Errno 1644 is assumed to come from the ledger-applied guard trigger, the
    # only user-defined SIGNAL on worksheet_adjustment today. If other 45000
    # triggers are added later, this mapping should also match on message/SQLSTATE.
    orig = getattr(error, 'orig', None)
    if orig is not None and orig.args and orig.args[0] == TRIGGER_APPLIED_SIGNAL_ERRNO:
        return response.create_error_response(
            code='error',
            status=409,
            message=ERROR_ENTRY_APPLIED.format('[applied during request]'),
        )
    raise error


def delete_worksheet_adjustments(worksheet_adjustment_ids):
    """Soft-delete a set of worksheet adjustments and their details.

    Runs in one transaction. All-or-nothing: any failed guard rejects the
    whole request.
    """
    try:
        locked = WorksheetAdjustment.select_for_update_by_ids(worksheet_adjustment_ids)

        guard = _guard_live_batch_entries(worksheet_adjustment_ids, locked)
        if guard is not None:
            db.session.rollback()
            return guard

        applied = WorksheetAdjustment.get_applied_ids(worksheet_adjustment_ids)
        if applied:
            db.session.rollback()
            return response.create_error_response(
                code='error',
                status=409,
                message=ERROR_ENTRY_APPLIED.format(sorted(applied)),
            )

        already_deleted = {
            wa.worksheet_adjustment_id for wa in locked if wa.deleted_at is not None
        }
        if already_deleted:
            db.session.rollback()
            return response.create_error_response(
                code='error',
                status=400,
                message=ERROR_WORKSHEET_ADJUSTMENTS_ALREADY_DELETED.format(
                    sorted(already_deleted)
                ),
            )

        WorksheetAdjustmentDetail.soft_delete_by_adjustment_ids(
            worksheet_adjustment_ids
        )
        WorksheetAdjustment.soft_delete_by_ids(worksheet_adjustment_ids)
        db.session.commit()
    except OperationalError as e:
        db.session.rollback()
        return _handle_applied_trigger_or_raise(e)
    except sqlalchemy.exc.SQLAlchemyError:
        db.session.rollback()
        raise

    return response.Response(status=204)


def restore_worksheet_adjustments(worksheet_adjustment_ids):
    """Restore a set of soft-deleted worksheet adjustments and their details.

    Runs in one transaction. All-or-nothing.
    """
    try:
        locked = WorksheetAdjustment.select_for_update_by_ids(worksheet_adjustment_ids)

        guard = _guard_live_batch_entries(worksheet_adjustment_ids, locked)
        if guard is not None:
            db.session.rollback()
            return guard

        not_deleted = {
            wa.worksheet_adjustment_id for wa in locked if wa.deleted_at is None
        }
        if not_deleted:
            db.session.rollback()
            return response.create_error_response(
                code='error',
                status=400,
                message=ERROR_WORKSHEET_ADJUSTMENTS_NOT_DELETED.format(
                    sorted(not_deleted)
                ),
            )

        WorksheetAdjustment.restore_by_ids(worksheet_adjustment_ids)
        WorksheetAdjustmentDetail.restore_by_adjustment_ids(worksheet_adjustment_ids)
        db.session.commit()
    except sqlalchemy.exc.SQLAlchemyError:
        db.session.rollback()
        raise

    return response.Response(status=204)


def get_worksheet_adjustments_and_details_by_file_id(
    statement_period_adjustment_file_id: int, request_params: dict
) -> Type[response.Response]:
    """Get a list of worksheet adjustments and details for a specified statement_period_adjustment_file_id.

    Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
        request_params (dict)(Optional): dict of query string passed to the url
            - limit(int): pagination limit
            - offset(int): pagination offset
            - account_ids(str): comma separated list of account ids
            - contract_ids(str): comma separated list of contract ids
            - apply_to_flowthrough_payment (str): comma separate list of filter values i.e 0, 1 or null
            - is_deleted(str): false (active only, default), true (deleted only), or all
    Returns:
        a list of worksheet adjustments and details.
    """
    try:
        statement_period_adjustment_file = (
            StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
                statement_period_adjustment_file_id
            )
        )

        if (not statement_period_adjustment_file) or (
            statement_period_adjustment_file['deleted_by'] is not None
            and statement_period_adjustment_file['deleted_at'] is not None
        ):
            return response.create_error_response(
                code='error',
                status=404,
                message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                    object_type='StatementPeriodAdjustmentFile',
                    object_id=statement_period_adjustment_file_id,
                ),
            )

        params = WorksheetAdjustmentAndDetailFilterSchema().load(request_params)

        items, total_count = WorksheetAdjustment.get_worksheet_adjustments_and_details(
            statement_period_adjustment_file_id, **params
        )

        message = {
            'items': WorksheetAdjustmentAndDetailSchema(many=True).dump(items),
            'total_count': total_count,
        }
    except ValidationError as e:
        return validation_error(str(e))

    return response.Response(message=message, status=200)


def get_worksheet_adjustments_deleted_aggregate_by_file_id(
    statement_period_adjustment_file_id: int,
) -> Type[response.Response]:
    """Get the deleted-entry aggregate for a statement_period_adjustment_file."""
    statement_period_adjustment_file = (
        StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
            statement_period_adjustment_file_id
        )
    )
    if (not statement_period_adjustment_file) or (
        statement_period_adjustment_file['deleted_by'] is not None
        and statement_period_adjustment_file['deleted_at'] is not None
    ):
        return response.create_error_response(
            code='error',
            status=404,
            message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                object_type='StatementPeriodAdjustmentFile',
                object_id=statement_period_adjustment_file_id,
            ),
        )
    message = WorksheetAdjustmentDeletedAggregateSchema().dump(
        WorksheetAdjustment.get_worksheet_adjustments_deleted_aggregate(
            statement_period_adjustment_file_id
        )
    )
    return response.Response(message=message, status=200)


def get_pending_worksheet_adjustments(request_params: dict):
    """Get a list of pending worksheet adjustments with optional filter.

    Args:
        request_params (dict)(Optional): dict of query string passed to the url
            - limit(int): pagination limit
            - offset(int): pagination offset
            - statement_period_id(int): ID of the statement period
            - reference_payment_entity_id: ID of the reference payment entity
    Returns:
        a list of pending worksheet adjustments.
    """
    try:
        validated_params = PendingWorksheetAdjustmentsRequestParamsSchema().load(
            request_params
        )
        items, total_count = WorksheetAdjustment.get_pending_worksheet_adjustments(
            **validated_params
        )
        message = {
            'items': WorksheetAdjustmentListSchema(many=True).dump(items),
            'total_count': total_count,
        }
    except ValidationError as e:
        return validation_error(str(e))

    return response.Response(message=message, status=200)


def get_worksheet_adjustments_contracts_by_file_id(
    statement_period_adjustment_file_id: int, request_params: dict
) -> Type[response.Response]:
    """Get a list of contracts for worksheet adjustments associated with a specified statement_period_adjustment_file_id.

    Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
        request_params (dict)(Optional): dict of query string passed to the url
            - limit(int): pagination limit
            - offset(int): pagination offset
            - contract_search_term(str): either contract_id or contract_name
    """
    try:
        statement_period_adjustment_file = (
            StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
                statement_period_adjustment_file_id
            )
        )

        if (not statement_period_adjustment_file) or (
            statement_period_adjustment_file['deleted_by'] is not None
            and statement_period_adjustment_file['deleted_at'] is not None
        ):
            return response.create_error_response(
                code='error',
                status=404,
                message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                    object_type='StatementPeriodAdjustmentFile',
                    object_id=statement_period_adjustment_file_id,
                ),
            )

        params = WorksheetAdjustmentAccountContractFilterSchema(
            exclude=['account_search_term']
        ).load(request_params)

        items, total_count = (
            WorksheetAdjustment.get_worksheet_adjustments_contracts_by_file_id(
                statement_period_adjustment_file_id, **params
            )
        )

        message = {
            'items': WorksheetAdjustmentContractsListSchema(many=True).dump(items),
            'total_count': total_count,
        }
    except ValidationError as e:
        return validation_error(str(e))

    return response.Response(message=message, status=200)


def get_worksheet_adjustments_accounts_by_file_id(
    statement_period_adjustment_file_id: int, request_params: dict
) -> Type[response.Response]:
    """Get a list of accounts for worksheet adjustments associated with a specified statement_period_adjustment_file_id.

    Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
        request_params (dict)(Optional): dict of query string passed to the url
            - limit(int): pagination limit
            - offset(int): pagination offset
            - account_search_term(str): either account_id or account_name
    """
    try:
        statement_period_adjustment_file = (
            StatementPeriodAdjustmentFile.get_statement_period_adjustment_file_by_id(
                statement_period_adjustment_file_id
            )
        )

        if (not statement_period_adjustment_file) or (
            statement_period_adjustment_file['deleted_by'] is not None
            and statement_period_adjustment_file['deleted_at'] is not None
        ):
            return response.create_error_response(
                code='error',
                status=404,
                message=ERROR_ENTITY_DOES_NOT_EXIST.format(
                    object_type='StatementPeriodAdjustmentFile',
                    object_id=statement_period_adjustment_file_id,
                ),
            )

        params = WorksheetAdjustmentAccountContractFilterSchema(
            exclude=['contract_search_term']
        ).load(request_params)

        items, total_count = (
            WorksheetAdjustment.get_worksheet_adjustments_accounts_by_file_id(
                statement_period_adjustment_file_id, **params
            )
        )

        message = {
            'items': WorksheetAdjustmentAccountsListSchema(many=True).dump(items),
            'total_count': total_count,
        }
    except ValidationError as e:
        return validation_error(str(e))

    return response.Response(message=message, status=200)


def get_adjustments_by_period_and_type_id(period_id: int, type_id: int):
    """Get worksheet adjustments by period and type id."""
    try:
        items, total_count, total_adjustment_amount = (
            WorksheetAdjustment.get_adjustments_by_period_and_type_id(
                period_id, type_id
            )
        )

        message = {
            'items': WorksheetAdjustmentListSchema(many=True).dump(items),
            'total_count': total_count,
            'currency_agnostic_total_amount': total_adjustment_amount,
        }
    except ValidationError as e:
        return validation_error(str(e))

    return response.Response(message=message, status=200)