"""contract_mechanical_deduction Model."""

from datetime import date

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseSoftDeleteModel
from sqlalchemy import Enum, and_, literal_column, or_, select, table, text
from sqlalchemy.dialects.mysql import SET

from abacus_contract.constants.constants import (
    MECHANICAL_DEDUCTION_ADMIN_TYPES,
    MECHANICAL_DEDUCTION_TERRITORIES,
    MECHANICAL_DEDUCTION_TYPES,
)


class ContractMechanicalDeduction(BaseSoftDeleteModel):
    """contract_mechanical_deduction Model."""

    __tablename__ = 'contract_mechanical_deduction'
    contract_mechanical_deduction_id = db.Column(db.Integer, primary_key=True)
    contract_id = db.Column(
        db.Integer, db.ForeignKey('contract.contract_id'), nullable=False
    )
    territory = db.Column(
        Enum(*MECHANICAL_DEDUCTION_TERRITORIES, name='territory', create_type=False),
        nullable=False,
    )
    mechanical_type = db.Column(SET(*list(MECHANICAL_DEDUCTION_TYPES)), nullable=False)
    admin_type = db.Column(
        Enum(*MECHANICAL_DEDUCTION_ADMIN_TYPES, name='admin_type', create_type=False),
        nullable=False,
    )
    admin_fee = db.Column(db.Numeric(5, 2), nullable=True)

    @staticmethod
    def get_active_contracts_with_mechanical_deductions(given_date: date):
        """Retrieve active contracts that have mechanical deductions as of a given date.

        Args:
            given_date (datetime): The reference date to check for active contracts.

        Returns: List[Row]
        """
        account = table('account').alias('a')
        account_contract = table('account_contract').alias('ac')
        contract_lifecycle = table('contract_lifecycle').alias('clc')
        contract_term = table('contract_term').alias('ct')
        contract_mechanical_deduction = table('contract_mechanical_deduction').alias(
            'cmd'
        )

        query = (
            select(
                [
                    literal_column('a.account_id').label('account_id'),
                    literal_column('ac.contract_id').label('contract_id'),
                    literal_column('ct.term_type').label('term_type'),
                    literal_column('ct.attachments').label('attachments'),
                    literal_column('cmd.mechanical_type').label('mechanical_type'),
                ]
            )
            .select_from(
                account.outerjoin(
                    account_contract, text('ac.account_id = a.account_id')
                )
                .outerjoin(
                    contract_lifecycle,
                    and_(
                        text('clc.contract_id = ac.contract_id'),
                        literal_column('clc.deleted_at').is_(None),
                        literal_column('clc.deleted_by').is_(None),
                        or_(
                            and_(
                                literal_column('clc.lifecycle_term_start')
                                <= given_date,
                                or_(
                                    literal_column('clc.lifecycle_term_end').is_(None),
                                    literal_column('clc.lifecycle_term_end')
                                    >= given_date,
                                ),
                            ),
                            and_(
                                literal_column('clc.termination_effective').isnot(None),
                                literal_column('clc.termination_effective')
                                >= given_date,
                            ),
                            and_(
                                literal_column('clc.termination_effective').isnot(None),
                                literal_column('clc.termination_effective')
                                < given_date,
                            ),
                        ),
                    ),
                )
                .outerjoin(
                    contract_term,
                    and_(
                        text('ct.contract_id = clc.contract_id'),
                        literal_column('ct.term_type').in_(
                            ['track', 'label', 'product']
                        ),
                        literal_column('ct.deleted_at').is_(None),
                        literal_column('ct.deleted_by').is_(None),
                    ),
                )
                .outerjoin(
                    contract_mechanical_deduction,
                    and_(
                        text('cmd.contract_id = ct.contract_id'),
                        literal_column('cmd.territory') == 'USA',
                        literal_column('cmd.deleted_at').is_(None),
                        literal_column('cmd.deleted_by').is_(None),
                    ),
                )
            )
            .where(
                and_(
                    literal_column('ac.contract_id').isnot(None),
                    literal_column('cmd.mechanical_type').isnot(None),
                )
            )
        )

        return db.session.execute(query).fetchall()