"""This module contains the WorksheetAccountContractTaxableRevenue model."""

import typing

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 Enum, exc, func, select, update

from payment.constants import constants


class WorksheetAccountContractTaxableRevenue(BaseModel):
    """Worksheet Account Contract Taxable Revenue Model."""

    __tablename__ = 'worksheet_account_contract_taxable_revenue'

    account_contract_taxable_revenue_id = db.Column(db.Integer, primary_key=True)
    worksheet_account_contract_closing_balance_id = db.Column(db.Integer, nullable=True)
    contract_id = db.Column(db.Integer, nullable=False)
    account_id = db.Column(db.Integer, nullable=False)
    reference_payment_entity_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)
    amount = db.Column(db.Numeric(20, 2), nullable=False)
    currency_code = db.Column(db.String(3), nullable=False)
    revenue_transaction_type = db.Column(
        Enum(
            *constants.REVENUE_TRANSACTION_TYPES,
            name='revenue_transaction_type',
            create_type=False,
        ),
        nullable=False,
    )
    is_us_revenue = db.Column(db.Boolean, nullable=False, default=False)
    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 bulk_create(cls, instances: list) -> None:
        """Bulk create instances using add_all."""
        db.session.add_all(instances)
        db.session.commit()

    @classmethod
    def soft_delete_by_event_id(cls, event_id: int) -> None:
        """Soft delete by event_id."""
        db.session.execute(
            update(cls)
            .where(
                cls.abacus_event_id == event_id,
                cls.deleted_at.is_(None),
            )
            .values(
                deleted_at=cls.current_timestamp(),
                deleted_by=get_flask_user_id(),
            )
            .execution_options(synchronize_session=False)
        )
        db.session.commit()

    @classmethod
    def get_filtered_items(
        cls,
        limit: int,
        offset: int,
        statement_period_id: typing.Optional[int] = None,
        contract_ids: typing.Optional[typing.List[int]] = None,
        closing_balance_ids: typing.Optional[typing.List[int]] = None,
    ) -> typing.Tuple[typing.List[typing.Self], int]:
        """
        Get filtered items.

        Args:
            limit (int): records limit
            offset (int): records offset
            statement_period_id (int): optional statement period
            contract_ids (List[int]): optional list of contract ids
            closing_balance_ids (List[int]): optional list of closing balance ids

        Returns:
            tuple of records list and total count
        """
        stmt = select(cls).where(cls.deleted_at.is_(None))

        if statement_period_id:
            stmt = stmt.where(cls.statement_period_id == statement_period_id)

        if contract_ids:
            stmt = stmt.where(cls.contract_id.in_(contract_ids))

        if closing_balance_ids:
            stmt = stmt.where(
                cls.worksheet_account_contract_closing_balance_id.in_(
                    closing_balance_ids
                )
            )

        total_count = db.session.execute(
            select(func.count()).select_from(stmt.subquery())
        ).scalar_one()
        items = (
            db.session.execute(stmt.limit(limit).offset(offset)).scalars().all()
            if limit > 0
            else []
        )

        return items, total_count