"""
This module contains the WorksheetPayableBalanceAfterTax model.

The model represents the payable balance after tax for a given account contract.
"""

import collections
from decimal import Decimal
from typing import Dict, List

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from abacus_common_logic.utils.users import get_flask_user_id
from sqlalchemy import and_, func, join, literal_column, Row, select, table, update

from payment.constants import constants
from payment.models import (
    PaymentGroupPaymentAccount as PaymentAccount,
    PaymentGroupPaymentAccountDetail as Detail,
)


class WorksheetPayableBalanceAfterTax(BaseModel):
    """Worksheet Account Contract Payable After Tax Model."""

    __tablename__ = 'worksheet_account_contract_payable_after_tax'

    worksheet_account_contract_payable_after_tax_id = db.Column(
        db.Integer, primary_key=True
    )
    worksheet_account_contract_closing_balance_id = db.Column(
        db.Integer, nullable=False
    )
    contract_id = db.Column(db.Integer, nullable=False)
    account_id = db.Column(db.Integer, nullable=False)
    statement_period_id = db.Column(db.Integer, nullable=False)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    payable_amount_pre_tax = db.Column(db.Numeric(20, 2), nullable=False)
    tax_withholding_amount = db.Column(db.Numeric(20, 2))
    vat_amount = db.Column(db.Numeric(20, 2), nullable=True)
    payable_amount_post_tax = db.Column(db.Numeric(20, 2), nullable=False)
    currency_code = db.Column(db.String(3), nullable=False)
    country_of_tax_residence = db.Column(db.String(3), nullable=False)
    country_of_tax_policy = db.Column(db.String(3))
    created_at = db.Column(db.DateTime, nullable=False)
    created_by = db.Column(db.String(255), nullable=False)
    last_modified = db.Column(db.DateTime, nullable=False)
    last_modified_by = db.Column(db.String(255), nullable=False)
    deleted_at = db.Column(db.DateTime)
    deleted_by = db.Column(db.String(255))

    @classmethod
    def get_by_id(cls, obj_id):
        """
        Get object from DB by ID property.

        :return: object
        """
        return (
            db.session.execute(
                cls.filter_active().where(
                    cls.worksheet_account_contract_payable_after_tax_id == obj_id
                )
            )
            .scalars()
            .first()
        )

    @classmethod
    def query_by_ids(cls, obj_ids):
        """
        Get object from DB by ID property.

        :return: query
        """
        return cls.filter_active().where(
            cls.worksheet_account_contract_payable_after_tax_id.in_(obj_ids)
        )

    @classmethod
    def get_by_ids_list(cls, obj_ids) -> list:
        """Execute query_by_ids and return list."""
        return db.session.execute(cls.query_by_ids(obj_ids)).scalars().all()

    @classmethod
    def filter_active(cls):
        """Filter active records — returns select() statement."""
        return select(cls).where(cls.deleted_at.is_(None))

    @classmethod
    def filter_by_contracts_statement_period_event_id(
        cls, contract_ids, statement_period_id, event_id
    ):
        """Filter by contract and statement period — returns select() statement."""
        return select(cls).where(
            cls.contract_id.in_(contract_ids),
            cls.statement_period_id == statement_period_id,
            cls.abacus_event_id == event_id,
            cls.deleted_at.is_(None),
        )

    @classmethod
    def exists_for_contracts_statement_period_event_id(
        cls, contract_ids, statement_period_id, event_id
    ) -> bool:
        """Return True if matching active records exist."""
        stmt = cls.filter_by_contracts_statement_period_event_id(
            contract_ids, statement_period_id, event_id
        )
        return bool(
            db.session.execute(
                select(func.count()).select_from(stmt.subquery())
            ).scalar_one()
        )

    @classmethod
    def filter_by_payment_group_payment_id(cls, payment_group_payment_id: int):
        """Query worksheets by payment id — returns select() statement."""
        events_query = (
            select(literal_column('ae.abacus_event_id'))
            .where(
                and_(
                    literal_column('ae.target_type')
                    == constants.ABACUS_EVENT_TARGET_TYPES.PAYMENT_GROUP_PAYMENT,
                    literal_column('ae.target_id') == payment_group_payment_id,
                )
            )
            .select_from(table('abacus_event').alias('ae'))
        )
        return cls.filter_active().where(cls.abacus_event_id.in_(events_query))

    @classmethod
    def get_by_payment_group_payment_and_account_id(
        cls, payment_group_payment_id: int, account_id: int
    ):
        """Get worksheet by payment and account id."""
        stmt = cls.filter_by_payment_group_payment_id(payment_group_payment_id).where(
            cls.account_id == account_id
        )
        return db.session.execute(stmt).scalars().all()

    @classmethod
    def get_by_payment_group_payment_and_account_ids(
        cls,
        payment_group_payment_id: int,
        account_ids: List[int],
        filter_by_payable: bool = False,
    ) -> Dict[int, List['WorksheetPayableBalanceAfterTax']]:
        """Get worksheets by payment and account ids list."""
        stmt = cls.filter_by_payment_group_payment_id(payment_group_payment_id).where(
            cls.account_id.in_(account_ids)
        )
        if filter_by_payable:
            stmt = stmt.where(cls.payable_amount_post_tax > 0)
        results = db.session.execute(stmt).scalars().all()

        accounts_worksheets = collections.defaultdict(list)
        for worksheet in results:
            accounts_worksheets[worksheet.account_id].append(worksheet)

        return accounts_worksheets

    @classmethod
    def filter_by_account_and_statement_period(
        cls, account_id: int, statement_period_id: int
    ):
        """
        Filter by account and statement period.

        :param account_id: int
        :param statement_period_id: int
        :return: query
        """
        return select(cls).where(
            cls.account_id == account_id,
            cls.statement_period_id == statement_period_id,
            cls.deleted_at.is_(None),
        )

    @classmethod
    def soft_delete_by_payment_group_payment(
        cls, payment_group_payment_id, commit: bool = True
    ):
        """Delete items by payment_group_payment_id.

        Args:
            payment_group_payment_id: ID of the payment group payment
            commit: whether to commit the transaction
        """
        events_query = (
            select(literal_column('ae.abacus_event_id'))
            .where(
                and_(
                    literal_column('ae.target_type')
                    == constants.ABACUS_EVENT_TARGET_TYPES.PAYMENT_GROUP_PAYMENT,
                    literal_column('ae.target_id') == payment_group_payment_id,
                )
            )
            .select_from(table('abacus_event').alias('ae'))
        )

        db.session.execute(
            update(cls)
            .where(cls.abacus_event_id.in_(events_query))
            .values(
                deleted_at=cls.current_timestamp(),
                deleted_by=get_flask_user_id(),
            )
            .execution_options(synchronize_session=False)
        )
        if commit:
            db.session.commit()

    @classmethod
    def soft_delete_by_payment_group_payment_account(
        cls, payment_group_payment_account_id
    ):
        """Delete items by payment_group_payment_account_id."""
        db.session.execute(
            update(cls)
            .where(
                cls.worksheet_account_contract_payable_after_tax_id.in_(
                    select(
                        Detail.worksheet_account_contract_payable_after_tax_id
                    ).where(
                        Detail.payment_group_payment_account_id
                        == payment_group_payment_account_id
                    )
                )
            )
            .values(
                deleted_at=cls.current_timestamp(),
                deleted_by=get_flask_user_id(),
            )
            .execution_options(synchronize_session=False)
        )
        db.session.commit()

    @classmethod
    def soft_delete_by_event_id(cls, event_id: int):
        """
        Soft delete items by event_id.

        Deletes only items not found in payment_group_payment_account_detail.

        :param int event_id: Event ID
        """
        filtering_subquery = (
            select(cls.worksheet_account_contract_payable_after_tax_id)
            .select_from(
                join(
                    cls,
                    Detail,
                    cls.worksheet_account_contract_payable_after_tax_id
                    == Detail.worksheet_account_contract_payable_after_tax_id,  # noqa
                    isouter=True,
                )
            )
            .where(
                and_(
                    Detail.worksheet_account_contract_payable_after_tax_id == None,  # noqa
                    cls.abacus_event_id == event_id,
                )
            )
            .alias('filtering_subquery')
        )

        db.session.execute(
            update(cls)
            .where(
                cls.worksheet_account_contract_payable_after_tax_id.in_(
                    select(
                        filtering_subquery.c.worksheet_account_contract_payable_after_tax_id
                    ).select_from(filtering_subquery)
                )
            )
            .values(
                deleted_at=cls.current_timestamp(),
                deleted_by=get_flask_user_id(),
            )
            .execution_options(synchronize_session=False)
        )
        db.session.commit()

    @classmethod
    def soft_delete_by_id(
        cls, worksheet_account_contract_payable_after_tax_id: int, commit: bool = True
    ):
        """
        Soft delete item by id.

        :param int worksheet_account_contract_payable_after_tax_id: ID
        :param bool commit: whether to commit the transaction
        """
        db.session.execute(
            update(cls)
            .where(
                cls.worksheet_account_contract_payable_after_tax_id
                == worksheet_account_contract_payable_after_tax_id,
                cls.deleted_at.is_(None),
            )
            .values(
                deleted_at=cls.current_timestamp(),
                deleted_by=get_flask_user_id(),
            )
            .execution_options(synchronize_session=False)
        )
        if commit:
            db.session.commit()

    @classmethod
    def get_overview_by_event_id(cls, event_id: int) -> Row:
        """Calculate aggregated overview for a given event_id."""
        return db.session.execute(
            select(
                func.coalesce(func.sum(cls.payable_amount_pre_tax), 0).label(
                    'payable_amount_pre_tax'
                ),
                func.coalesce(func.sum(cls.tax_withholding_amount), 0).label(
                    'tax_withholding_amount'
                ),
                func.coalesce(func.sum(cls.vat_amount), 0).label('vat_amount'),
                func.coalesce(func.sum(cls.payable_amount_post_tax), 0).label(
                    'payable_amount_post_tax'
                ),
            ).where(
                cls.abacus_event_id == event_id,
                cls.deleted_at.is_(None),
            )
        ).one()

    @classmethod
    def bulk_create(cls, instances: list) -> None:
        """Bulk create instances using add_all."""
        db.session.add_all(instances)
        db.session.commit()

    @classmethod
    def bulk_update(cls, updates: List[dict], commit: bool = True):
        """Bulk update worksheet payable balance after tax records.

        Args:
            updates: List of dictionaries containing worksheet_account_contract_payable_after_tax_id
                    and fields to update (tax_withholding_amount, vat_amount, payable_amount_post_tax)
            commit: Whether to commit the transaction (default: True)
        """
        for update_item in updates:
            update_item['last_modified'] = cls.current_timestamp()
            update_item['last_modified_by'] = get_flask_user_id()

        db.session.execute(update(cls), updates)
        if commit:
            db.session.commit()


def get_payments_matching_closing_balances(worksheet_payable_after_tax_ids: List[int]):
    """Get matching closed balance payments.

    Fetch existing payments that are attached to the same closing balance entries.
    Essentially when generating payments we need to make sure we're not
    paying the same closing balance twice. This takes the list of worksheets
    used for the payment and sees if there are any existing payments
    that match the closing balance data.

    Args:
        worksheet_payable_after_tax_ids: the payable_after_tax_ids
        by which you get the closing balance ids.
    """
    subquery = select(
        WorksheetPayableBalanceAfterTax.worksheet_account_contract_closing_balance_id
    ).where(
        WorksheetPayableBalanceAfterTax.deleted_at.is_(None),
        WorksheetPayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id.in_(
            worksheet_payable_after_tax_ids
        ),
    )
    query = (
        select(
            *PaymentAccount.__table__.columns,
            WorksheetPayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id,
        )
        .join(
            Detail,
            Detail.payment_group_payment_account_id
            == PaymentAccount.payment_group_payment_account_id,
        )
        .join(
            WorksheetPayableBalanceAfterTax,
            WorksheetPayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id
            == Detail.worksheet_account_contract_payable_after_tax_id,
        )
        .join(
            table('abacus_state').alias('pgpa_st'),
            and_(
                literal_column('pgpa_st.parent_table_id')
                == PaymentAccount.payment_group_payment_account_id,
                literal_column('pgpa_st.parent_table_name')
                == PaymentAccount.__tablename__,
                literal_column('pgpa_st.action_name')
                == constants.PAYMENT_GROUP_PAYMENT_ACTIONS.SEND_PAYMENTS,
                literal_column('pgpa_st.action_status')
                == constants.ACTION_STATUSES.REJECTED,
            ),
            isouter=True,
        )
        .where(
            PaymentAccount.deleted_at.is_(None),
            PaymentAccount.prior_payment_group_payment_id.is_(None),
            Detail.deleted_at.is_(None),
            literal_column('pgpa_st.abacus_state_id').is_(None),
            WorksheetPayableBalanceAfterTax.worksheet_account_contract_closing_balance_id.in_(
                subquery
            ),
            WorksheetPayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id.not_in(
                worksheet_payable_after_tax_ids
            ),
        )
    )
    return db.session.execute(query).mappings().all()