"""Contract model."""

import re

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import and_, bindparam, Enum, exists, func, or_, select, text
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.sql import literal_column, table


from abacus_contract.constants import queries
from abacus_contract.constants.constants import CONTRACT_LIFECYCLE_STATUSES
from abacus_contract.constants.constants import CONTRACT_TYPES
from abacus_contract.models import AccountContract
from abacus_contract.models.contract_lifecycle import ContractLifecycle
from abacus_contract.models.deleted_contract import DeletedContract
from abacus_contract.models.legacy_contract import LegacyContract
from abacus_contract.models.run_controller_contract import RunControllerContract


class Contract(BaseModel):
    """Contract model."""

    __tablename__ = 'contract'
    contract_id = db.Column(db.Integer, primary_key=True)
    reference_signing_entity_id = db.Column(
        db.Integer,
        db.ForeignKey('reference_signing_entity.reference_signing_entity_id'),
        nullable=False
    )
    contract_name = db.Column(db.String(255), nullable=False)
    term_start = db.Column(db.Date, nullable=True)
    term_end = db.Column(db.Date, nullable=True)
    contract_type = db.Column(
        Enum(
            *CONTRACT_TYPES,
            name='contract_type',
            create_type=False
        ),
        default=CONTRACT_TYPES.DISTRIBUTION,
        nullable=False
    )
    sap_created_at = db.Column(db.DateTime, nullable=True)
    initial_start_date = db.Column(db.Date, nullable=True)
    execution_date = db.Column(db.Date, nullable=True)
    is_excluded_from_accounting_run = db.Column(
        db.Boolean,
        nullable=False,
        default=False
    )
    summary_note = db.Column(LONGTEXT, nullable=True)
    general_note = db.Column(LONGTEXT, nullable=True)

    account_contract = db.relationship(
        'AccountContract',
        backref='contract',
        cascade='all, delete-orphan',
        uselist=False
    )

    run_controller_contract = db.relationship(
        'RunControllerContract',
        backref='contract',
        cascade='all, delete-orphan',
        uselist=False
    )

    contract_advance = db.relationship(
        'ContractAdvance',
        backref='contract',
        cascade='all, delete-orphan'
    )

    contract_terms = db.relationship(
        'ContractTerm',
        backref='contract',
        cascade='all, delete-orphan',
        primaryjoin='and_(ContractTerm.contract_id == Contract.contract_id, '
        'ContractTerm.deleted_at == None)'
    )

    contract_exclusion = db.relationship(
        'ContractExclusion',
        backref='contract',
        cascade='all, delete-orphan',
        uselist=False
    )

    contract_party = db.relationship(
        'ContractParty',
        backref='contract',
        cascade='all, delete-orphan'
    )

    contract_reserves = db.relationship(
        'ContractReserve',
        backref='contract',
        cascade='all, delete-orphan',
        lazy='dynamic',
    )

    legacy_contract = db.relationship(
        'LegacyContract',
        backref='contract',
        uselist=False,
        cascade='all, delete-orphan'
    )

    reference_signing_entity = db.relationship(
        'ReferenceSigningEntity'
    )

    contract_flowthroughs = db.relationship(
        'ContractFlowthrough',
        backref='contract',
        uselist=True
    )

    contract_lifecycle_schedules = db.relationship(
        'ContractLifecycleSchedule',
        backref='contract'
    )

    contract_lifecycle = db.relationship(
        'ContractLifecycle',
        backref='contract',
        uselist=False
    )

    contract_mechanical_deductions = db.relationship(
        'ContractMechanicalDeduction',
        backref='contract',
        cascade='all, delete-orphan',
        primaryjoin='and_(\
            ContractMechanicalDeduction.contract_id == Contract.contract_id, \
            ContractMechanicalDeduction.deleted_at == None)'
    )

    @property
    def account_id(self):
        """Get contract's account_id."""
        return self.account_contract.account_id

    @property
    def contract_reserve(self):
        """Get contract's active contract_reserve."""
        return self.contract_reserves.filter(
            text('contract_reserve.deleted_at IS NULL')).first()

    @classmethod
    def default_order(cls):
        """Override to customize default ordering."""
        return func.lower(cls.contract_name)

    @classmethod
    def _filter_by_search_term(cls, query, search_term):
        """Filter contracts by search term."""
        search_term_text = re \
            .sub(r'([\\%_])', r'\\\1', str(search_term)) \
            .lower()

        return query \
            .filter(
                or_(
                    cls.contract_name.ilike(f'%{search_term_text}%'),
                    cls.contract_id.ilike(f'%{search_term_text}%')
                )
            ) \
            .order_by(*cls.search_order()) \
            .params(search_term=search_term_text)

    @classmethod
    def find_by_name(cls, contract_name):
        """Override base model's find_by_name."""
        return cls.query.filter_by(contract_name=contract_name).first()

    @classmethod
    def get_filtered_query(
        cls,
        contract_name=None,
        search_term=None,
        account_ids=None,
        contract_type=None,
        is_excluded_from_accounting_run=None,
        contract_statuses=None,
        run_controller_ids=None
    ):
        """Get contracts by field values.

        To prevent filters from being injected, they're are applied to
        the contract search in an ad-hoc fashion.
        """
        query = cls.query.join(
            AccountContract,
            cls.contract_id == AccountContract.contract_id
        )
        # Acount IDs filter
        if account_ids is not None:
            query = query.filter(
                AccountContract.account_id.in_(account_ids)
            )

        # Must have at least one run_controller row
        base_exists = exists().where(
            RunControllerContract.contract_id == cls.contract_id
        )

        if run_controller_ids is None:
            query = query.filter(base_exists)
        else:
            run_controllers = run_controller_ids.split(',')
            query = query.filter(
                exists().where(
                    and_(
                        RunControllerContract.contract_id == cls.contract_id,
                        RunControllerContract.run_controller_id.in_(run_controllers)
                    )
                )
            )

        # Contract Name Filter
        if contract_name is not None:
            search_contract_name_text = \
                contract_name.replace('\\', '\\\\').replace('%', '\\%')
            query = query.filter(
                cls.contract_name.ilike(f'%{search_contract_name_text}%')
            )

        # Search term filter
        if search_term is not None:
            query = cls._filter_by_search_term(query, search_term)

        # Contract Type filter
        if contract_type is not None:
            query = query.filter(cls.contract_type == contract_type)

        # included/excluded from accounting run filter
        if (is_excluded_from_accounting_run is not None and is_excluded_from_accounting_run != ''):  # noqa:E501
            query = query.filter(
                cls.is_excluded_from_accounting_run == is_excluded_from_accounting_run
            )

        # contract_statuses filter
        if contract_statuses is not None:
            statuses = contract_statuses.split(',')
            filter_by_contract_statuses = [
                ContractLifecycle.lifecycle_status.in_(statuses)
            ]

            if (CONTRACT_LIFECYCLE_STATUSES.INIT in statuses):
                filter_by_contract_statuses.append(
                    ContractLifecycle.contract_lifecycle_id.is_(None)
                )

            query = query.outerjoin(
                ContractLifecycle,
                cls.contract_id == ContractLifecycle.contract_id
            ).filter(and_(
                ContractLifecycle.deleted_at.is_(None),
                ContractLifecycle.deleted_by.is_(None),
                or_(*filter_by_contract_statuses)
            ))

        return query

    @classmethod
    def get_by_ids(cls, contract_ids: list):
        """Get contracts by their contract_ids."""
        return cls.query.filter(cls.contract_id.in_(contract_ids)).all()

    @classmethod
    def get_by_accounts(cls, account_ids: list):
        """Get all contracts associated to the specified account_ids."""
        return cls.get_by_accounts_query(account_ids).all()

    @classmethod
    def get_by_accounts_query(cls, account_ids: list):
        """Query to filter contracts by list of account ids."""
        return cls.query.join(
            AccountContract,
            cls.contract_id == AccountContract.contract_id
        ).filter(AccountContract.account_id.in_(account_ids))

    @classmethod
    def get_by_legacy_contract_ids(cls, oa_contract_ids):
        """Get all contracts related to specified orchard admin contract ids."""
        return cls.query \
            .join(LegacyContract, cls.contract_id == LegacyContract.contract_id) \
            .filter(LegacyContract.oa_contract_id.in_(oa_contract_ids)).all()

    @staticmethod
    def get_contract_vat_info_by_contract_ids(contract_ids: list) -> list:
        """Retrieve compound VAT information by list of contract IDs."""
        return db.session.execute(
            queries.GET_CONTRACT_VAT_INFO_BY_CONTRACT_IDS,
            {'contract_ids': contract_ids}
        ).fetchall()

    @classmethod
    def search_order(cls):
        """Override to customize search ordering with fuzzy search."""
        inf_param = bindparam('inf', type_=db.Integer, value=999999999)
        search_term_param = bindparam('search_term', type_=db.String)

        # Match position: lower index = better
        match_position = text("""LEAST(
            IFNULL(NULLIF(POSITION(
                :search_term IN LOWER(contract.contract_name)
            ), 0), :inf),
            IFNULL(NULLIF(POSITION(
                :search_term IN CAST(contract.contract_id AS CHAR)
            ), 0), :inf)
        )""").bindparams(inf_param, search_term_param)

        # Match quality: shorter difference = better
        match_quality = text("""LEAST(
            IF(
                POSITION(:search_term IN LOWER(contract.contract_name)) > 0,
                LENGTH(contract.contract_name) - LENGTH(:search_term),
                :inf
            ),
            IF(
                POSITION(:search_term IN CAST(contract.contract_id AS CHAR)) > 0,
                LENGTH(CAST(contract.contract_id AS CHAR)) - LENGTH(:search_term),
                :inf
            )
        )""").bindparams(inf_param, search_term_param)

        return (match_quality, match_position)

    @classmethod
    def stream_all(cls, contract_ids=None):
        """Stream all contracts. Returns contract info with payee currency."""
        limitations = [
            literal_column('c.contract_id') == literal_column('ac.contract_id'),
            literal_column('apt.account_id') == literal_column('ac.account_id'),
            literal_column('c.contract_id') == literal_column('cl.contract_id'),
            literal_column('c.is_excluded_from_accounting_run').is_(False),
            literal_column('cl.lifecycle_status').in_([
                CONTRACT_LIFECYCLE_STATUSES.ACTIVE,
                CONTRACT_LIFECYCLE_STATUSES.TO_BE_TERMINATED,
                CONTRACT_LIFECYCLE_STATUSES.TERMINATED
            ]),
            literal_column('cl.deleted_at').is_(None),
            literal_column('cl.deleted_by').is_(None)
        ]

        if contract_ids:
            limitations.append(
                literal_column('c.contract_id').in_(contract_ids)
            )

        all_contracts = select([
            literal_column('c.contract_id').label('contract_id'),
            literal_column('ac.account_id').label('account_id'),
            literal_column('cl.lifecycle_term_start').label('term_start'),
            literal_column('cl.lifecycle_term_end').label('term_end'),
            literal_column('apt.currency_code').label('currency_code')
        ]).where(
            and_(
                *limitations
            )
        ) \
            .select_from(table('contract').alias('c')) \
            .select_from(table('contract_lifecycle').alias('cl')) \
            .select_from(table('account_contract').alias('ac')) \
            .select_from(table('account_payment_term').alias('apt'))

        return db.session.execute(all_contracts)

    @staticmethod
    def can_be_deleted(contract_id: int) -> bool:
        """Check if a contract can be deleted.

        We allow a contract to be deleted if it has no accounting activity, meaning:
            - No ledger entries
            - No advances
            - No adjustments
            - Has never been part of an accounting run
        """
        result = db.session.execute(
            queries.CAN_CONTRACT_BE_DELETED,
            {'contract_id': contract_id}
        ).one_or_none()

        return True if result is None else False

    @staticmethod
    def delete(contract_id: int):
        """Delete a contract.

        1. Get the contract data
        2. Insert a record into `deleted_contract`
        3. Delete the `contract` record
        """
        contract_data = db.session.execute(
            queries.GET_CONTRACT_DATA,
            {'contract_id': contract_id}
        ).one()

        # NOTE: The `.build()` method adds the object to the session without committing
        DeletedContract.build(
            contract_id=contract_id,
            contract_data=contract_data._asdict()
        )

        db.session.execute(
            'DELETE FROM contract WHERE contract_id = :contract_id',
            {'contract_id': contract_id}
        )

        db.session.commit()

    @classmethod
    def get_sap_profit_center_by_contract_id(cls, contract_id: int) -> dict:
        """Get sap profit center data by contract id.

        Args:
            contract_id (int): id of the contract
        Return:
            a dict having contract and
        """
        from abacus_contract.models import (
            ReferenceSapProfitCenter,
            ReferenceSigningEntity,
        )

        query = (
            db.session.query(
                Contract.contract_id,
                AccountContract.account_id,
                Contract.contract_name,
                Contract.contract_type,
                Contract.term_start,
                Contract.term_end,
                ReferenceSapProfitCenter.company_code.label('Bukrs'),
                ReferenceSapProfitCenter.profit_center.label('Prctr'),
            )
            .join(
                AccountContract,
                AccountContract.contract_id == Contract.contract_id,
            )
            .join(
                ReferenceSigningEntity,
                ReferenceSigningEntity.reference_signing_entity_id
                == Contract.reference_signing_entity_id,
            )
            .join(
                ReferenceSapProfitCenter,
                ReferenceSapProfitCenter.reference_sap_profit_center_id
                == ReferenceSigningEntity.reference_sap_profit_center_id,
            )
            .filter(Contract.contract_id == contract_id)
            .limit(1)
        )
        result = query.first()
        return result if result else None