"""contract_advance Model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import BaseModel, NormalizedDate, NormalizedDateTime
from sqlalchemy import (
    Enum,
    and_,
    func,
    join,
    literal_column,
    not_,
    or_,
    select,
    table,
    text,
)

from abacus_contract.constants.constants import (
    ACTION_STATUSES,
    ADVANCE_STATUSES,
    ADVANCE_STATUSES_PENDING_MARK,
    COMMIT_TO_SUBLEDGER_EVENT_NAME,
    CONFIRM_ADVANCE_PAYMENT_EVENT_NAME,
    MILESTONES,
    SEND_PAYMENT_STATE_NAME,
)


class ContractAdvance(BaseModel):
    """contract_advance Model."""

    __tablename__ = 'contract_advance'
    contract_advance_id = db.Column(db.Integer, primary_key=True)
    contract_id = db.Column(
        db.Integer, db.ForeignKey('contract.contract_id'), nullable=False
    )
    advance_description = db.Column(db.String(180), nullable=False)
    amount = db.Column(db.Numeric(20, 2), nullable=False)
    vat_amount = db.Column(db.Numeric(20, 2), nullable=True)
    withholding_tax_amount = db.Column(db.Numeric(20, 2), nullable=True)
    amount_after_withholding_and_vat = db.Column(db.Numeric(20, 2), nullable=True)
    us_source_income_rate = db.Column(db.Numeric(9, 6), nullable=True)
    currency_code = db.Column(db.String(3), nullable=False)
    milestone = db.Column(
        Enum(*MILESTONES, name='milestone', create_type=False), nullable=False
    )
    milestone_description = db.Column(db.Text, nullable=False)
    milestone_date = db.Column(NormalizedDate(), nullable=True)
    advance_status = db.Column(
        Enum(*ADVANCE_STATUSES, name='advance_status', create_type=False),
        default=ADVANCE_STATUSES.NOT_QUALIFIED,
        nullable=False,
    )
    note = db.Column(db.String(255), nullable=True)
    reference_payment_type_id = db.Column(
        db.Integer,
        db.ForeignKey('reference_payment_type.reference_payment_type_id'),
        nullable=True,
    )
    deleted_at = db.Column(NormalizedDateTime(), nullable=True)
    deleted_by = db.Column(db.String(180), nullable=True)

    reference_payment_type = db.relationship(
        'ReferencePaymentType', backref='contract_advance'
    )

    @classmethod
    def get_by_contract_id(
        cls,
        contract_id: int,
        limit: int,
        offset: int,
        contract_advance_status: str | None = None,
        reference_payment_type_id: int | None = None,
    ) -> tuple:
        """Get contract advances for specified contract_id.

        The query returns all contract_advances if contract_advance_status is None.
        Arg:
            contract_id(int): id of the contract
            limit (int): pagination limit
            offset (int): pagination offset
            contract_advance_status(str)(Optional):
                status of the contract advance is either
                pending, not_qualified, qualified, paid,
                in_review, approved or deleted
            reference_payment_type_id(int)(Optional):
        Returns:
            A tuple containing items and total count
        """
        if contract_advance_status == ADVANCE_STATUSES.PAID:
            query = cls._query_get_paid_contract_advances(contract_id)
            items = db.session.execute(query.limit(limit).offset(offset)).fetchall()
            query = select(func.count()).select_from(query.order_by(None).subquery())
            total_count = db.session.execute(query).scalar_one()

            return items, total_count

        query = cls.query.filter(
            cls.contract_id == contract_id, cls.deleted_at.is_(None)
        ).order_by(cls.created_at.desc())

        if contract_advance_status == ADVANCE_STATUSES_PENDING_MARK:
            pending_statuses = [
                ADVANCE_STATUSES.NOT_QUALIFIED,
                ADVANCE_STATUSES.QUALIFIED,
                ADVANCE_STATUSES.APPROVED,
            ]
            query = query.filter(cls.advance_status.in_(pending_statuses))
        elif contract_advance_status is not None:
            query = query.filter(cls.advance_status == contract_advance_status)

        if reference_payment_type_id:
            query = query.filter(
                cls.reference_payment_type_id == reference_payment_type_id
            )

        items = query.limit(limit).offset(offset).all()
        total_count = query.count()

        return items, total_count

    @staticmethod
    def _query_get_paid_contract_advances(contract_id: int):
        """Build a query to get paid contract advances.

        Arg:
            contract_id(int): id of the contract
        """
        query = (
            select(
                [
                    literal_column('ca.*'),
                    literal_column('ae.event_date').label('date_paid'),
                    literal_column('lcap.statement_period_id').label(
                        'statement_period_id'
                    ),
                    literal_column('lcap.advance_amount_payee_currency').label(
                        'advance_amount_payee_currency'
                    ),
                    literal_column('lcap.advance_payee_currency_code').label(
                        'advance_payee_currency_code'
                    ),
                ]
            )
            .where(
                and_(
                    literal_column('ca.contract_id') == contract_id,
                    literal_column('ca.advance_status').in_(
                        [ADVANCE_STATUSES.PENDING_PAYMENT, ADVANCE_STATUSES.PAID]
                    ),
                    literal_column('ca.deleted_at').is_(None),
                    # don't duplicate results for previously failed payments
                    or_(
                        literal_column('ast.action_status').is_(None),
                        literal_column('ast.action_status').in_(
                            [ACTION_STATUSES.RUNNING, ACTION_STATUSES.COMPLETE]
                        ),
                    ),
                    not_(
                        and_(
                            literal_column('ae.event_date').is_(None),
                            literal_column('lcap.statement_period_id').isnot(None),
                        )
                    ),
                )
            )
            .order_by(literal_column('ae.event_date').desc())
            .select_from(
                join(
                    table('contract_advance').alias('ca'),
                    table('ledger_contract_advance_applied').alias('lcap'),
                    text('ca.contract_advance_id=lcap.contract_advance_id'),
                    isouter=True,
                )
                .join(
                    table('abacus_event').alias('ae'),
                    and_(
                        text('lcap.abacus_event_id=ae.abacus_event_id'),
                        literal_column('ae.event_name').in_(
                            [
                                CONFIRM_ADVANCE_PAYMENT_EVENT_NAME,
                                COMMIT_TO_SUBLEDGER_EVENT_NAME,
                            ]
                        ),
                    ),
                    isouter=True,
                )
                .join(
                    table('abacus_state').alias('ast'),
                    and_(
                        text(
                            'lcap.worksheet_payment_contract_advance_id='
                            'ast.parent_table_id'
                        ),
                        literal_column('ast.parent_table_name')
                        == 'worksheet_payment_contract_advance',
                        literal_column('ast.action_name') == SEND_PAYMENT_STATE_NAME,
                    ),
                    isouter=True,
                )
            )
        )
        return query