"""Worksheet account contract closing balance."""

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


class WorksheetAccountContractClosingBalance(BaseModel):
    """Worksheet Account Contract Closing Balance Model."""

    __tablename__ = 'worksheet_account_contract_closing_balance'

    worksheet_account_contract_closing_balance_id = db.Column(
        db.Integer, primary_key=True
    )

    """
    The absence of db.ForeignKey('account.account_id')
    is because there's a requirement to specify the `account` model.
    Integrity constraints are enforced at the database level.
    """
    account_id = db.Column(db.Integer, nullable=False)

    """
    The absence of db.ForeignKey('contract.contract_id')
    is because there's a requirement to specify the `contract` model.
    Integrity constraints are enforced at the database level.
    """
    contract_id = db.Column(db.Integer, nullable=False)

    """
    The absence of db.ForeignKey('reference_payment_entity.reference_payment_entity_id')
    is because there's a requirement to specify the `reference_payment_entity` model.
    Integrity constraints are enforced at the database level.
    """
    reference_payment_entity_id = db.Column(
        db.Integer,
        nullable=False,
    )

    """
    The absence of db.ForeignKey('abacus_event.abacus_event_id')
    is because there's a requirement to specify the `abacus_event` model.
    Integrity constraints are enforced at the database level.
    """
    abacus_event_id = db.Column(db.Integer, nullable=False)

    """
    The absence of db.ForeignKey('statement_period.statement_period_idd')
    is because there's a requirement to specify the `statement_period` model.
    Integrity constraints are enforced at the database level.
    """
    statement_period_id = db.Column(
        db.Integer,
        nullable=False,
    )

    """
    The absence of db.ForeignKey('ledger_account_contract.ledger_account_contract_id')
    is because there's a requirement to specify the `ledger_account_contract` model.
    Integrity constraints are enforced at the database level.
    """
    ledger_account_contract_id = db.Column(db.Integer, nullable=False)

    currency_code = db.Column(db.String(3), nullable=False)
    amount = db.Column(db.Numeric(20, 2), nullable=False)
    includes_tax_adjustments = 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, nullable=True)
    deleted_by = db.Column(db.String(255), nullable=True)

    @classmethod
    def filter_active(cls):
        """Return select statement for non-deleted items."""
        return select(cls).where(cls.deleted_at.is_(None))

    @classmethod
    def count_active(cls) -> int:
        """Return count of non-deleted items."""
        return db.session.execute(
            select(func.count()).select_from(cls.filter_active().subquery())
        ).scalar_one()

    @classmethod
    def bulk_create(cls, instances: list) -> None:
        """Bulk create instances using add_all."""
        try:
            db.session.add_all(instances)
            db.session.commit()
        except exc.IntegrityError:
            db.session.rollback()
            raise

    @classmethod
    def exists_active_for_statement_period_and_contracts(
        cls, statement_period_id: int, contract_ids: list
    ) -> bool:
        """Return True if active records exist for given period and contracts."""
        stmt = cls.filter_active().where(
            cls.statement_period_id == statement_period_id,
            cls.contract_id.in_(contract_ids),
        )
        return bool(
            db.session.execute(
                select(func.count()).select_from(stmt.subquery())
            ).scalar_one()
        )

    @classmethod
    def soft_delete_by_event_id(cls, event_id: int):
        """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_by_statement_period_id(
        cls,
        statement_period_id: int,
        account_ids: list = None,
        contract_ids: list = None,
        limit: int = None,
        offset: int = None,
    ) -> (typing.List['WorksheetAccountContractClosingBalance'], int):
        """Return worksheet_account_contract_closing_balance by statement_period_id."""
        stmt = select(cls).where(
            cls.statement_period_id == statement_period_id,
            cls.deleted_at.is_(None),
        )

        if account_ids:
            stmt = stmt.where(cls.account_id.in_(account_ids))

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

        total_count = db.session.execute(
            select(func.count()).select_from(stmt.subquery())
        ).scalar_one()

        if limit:
            stmt = stmt.limit(limit)

        if offset:
            stmt = stmt.offset(offset)

        return db.session.execute(stmt).scalars().all(), total_count

    @classmethod
    def get_by_ids(
        cls,
        worksheet_closing_balance_ids: typing.List[int] = None,
        limit: int = None,
        offset: int = None,
    ) -> typing.Tuple[typing.List['WorksheetAccountContractClosingBalance'], int]:
        """Return worksheet_account_contract_closing_balance by list of IDs."""
        stmt = cls.filter_active()
        if worksheet_closing_balance_ids is not None:
            stmt = stmt.where(
                cls.worksheet_account_contract_closing_balance_id.in_(
                    worksheet_closing_balance_ids
                )
            )
        stmt = stmt.order_by(cls.worksheet_account_contract_closing_balance_id)
        total_count = db.session.execute(
            select(func.count()).select_from(stmt.subquery())
        ).scalar_one()
        if limit is not None:
            stmt = stmt.limit(limit)
        if offset is not None:
            stmt = stmt.offset(offset)
        return db.session.execute(stmt).scalars().all(), total_count

    @classmethod
    def get_by_abacus_event_id(cls, abacus_event_id: int):
        """Return worksheet_account_contract_closing_balance by abacus_event_id."""
        return (
            db.session.execute(
                select(cls).where(
                    cls.abacus_event_id == abacus_event_id,
                    cls.deleted_at.is_(None),
                )
            )
            .scalars()
            .all()
        )