"""Contract Advance model."""

import typing

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import and_, Enum, 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,
    SEND_PAYMENT_STATE_NAME
)
from abacus_contract.constants.constants import MILESTONES


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(db.Date, 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(db.DateTime, 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: typing.Optional[str] = None,
        reference_payment_type_id: typing.Optional[int] = 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()
            total_count = db.session.execute(query).rowcount

            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