"""contract Model."""

import re

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import BaseModel, NormalizedDate, NormalizedDateTime
from sqlalchemy import (
    Enum,
    String,
    and_,
    bindparam,
    case,
    exists,
    func,
    or_,
    select,
    text,
)
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.sql import literal_column, table
from sqlalchemy.sql.expression import cast

from abacus_contract.constants import queries
from abacus_contract.constants.constants import (
    CONTRACT_LIFECYCLE_STATUSES,
    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.reference_sap_profit_center import ReferenceSapProfitCenter
from abacus_contract.models.reference_signing_entity import ReferenceSigningEntity
from abacus_contract.utils.features import (
    is_single_supply_chain_company_codes_enabled,
)
from royalties.models.earnings_transfer import EarningsTransfer
from royalties.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,
    )
    reference_sap_profit_center_id = db.Column(
        db.Integer,
        db.ForeignKey('reference_sap_profit_center.reference_sap_profit_center_id'),
        nullable=True,
    )
    contract_name = db.Column(db.String(255), nullable=False)
    term_start = db.Column(NormalizedDate(), nullable=True)
    term_end = db.Column(NormalizedDate(), nullable=True)
    contract_type = db.Column(
        Enum(*CONTRACT_TYPES, name='contract_type', create_type=False),
        default=CONTRACT_TYPES.DISTRIBUTION,
        nullable=False,
    )
    is_primary_contract = db.Column(db.Boolean, nullable=False, default=False)
    sap_created_at = db.Column(NormalizedDateTime(), nullable=True)
    initial_start_date = db.Column(NormalizedDate(), nullable=True)
    execution_date = db.Column(NormalizedDate(), nullable=True)
    is_excluded_from_accounting_run = db.Column(
        db.Boolean, nullable=False, default=False
    )
    is_paythrough_contract = 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,
    )

    earnings_transfer_from_contract = db.relationship(
        'EarningsTransfer',
        foreign_keys='[EarningsTransfer.from_contract_id]',
        backref='from_contract',
        cascade='all, delete-orphan',
    )

    earnings_transfer_to_contract = db.relationship(
        'EarningsTransfer',
        foreign_keys='[EarningsTransfer.to_contract_id]',
        backref='to_contract',
        cascade='all, delete-orphan',
    )

    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,
        primaryjoin='and_(\
            ContractLifecycle.contract_id == Contract.contract_id, \
            ContractLifecycle.deleted_at == None)',
    )

    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()

    @property
    def run_controller_id(self):
        """Get contract's run_controller_id."""
        if self.run_controller_contract:
            return self.run_controller_contract.run_controller_id

    @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}%', escape='\\'),
                    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: str):
        """Override base model's find_by_name."""
        return cls.query.filter(
            cls.contract_name.ilike(contract_name, escape='\\')
        ).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(
            select(1).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}%', escape='\\')
            )

        # 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 != ''
        ):
            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)
        search_term_len = func.length(search_term_param)

        contract_id = cast(cls.contract_id, String)
        contract_id_idx = func.instr(contract_id, search_term_param)
        contract_id_len = func.length(contract_id)

        contract_name = func.lower(cls.contract_name)
        contract_name_idx = func.instr(contract_name, search_term_param)
        contract_name_len = func.length(cls.contract_name)

        # Match quality (difference): smaller = better
        qual_name = case(
            (contract_name_idx > 0, contract_name_len - search_term_len),
            else_=inf_param,
        )
        qual_id = case(
            (contract_id_idx > 0, contract_id_len - search_term_len),
            else_=inf_param,
        )
        match_quality = case((qual_name <= qual_id, qual_name), else_=qual_id)

        # Match position: smaller = better
        pos_name = func.ifnull(func.nullif(contract_name_idx, 0), inf_param)
        pos_id = func.ifnull(func.nullif(contract_id_idx, 0), inf_param)
        match_position = func.IF(pos_name <= pos_id, pos_name, pos_id)

        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', type_=NormalizedDate()
                    ).label('term_start'),
                    literal_column(
                        'cl.lifecycle_term_end', type_=NormalizedDate()
                    ).label('term_end'),
                    literal_column('apt.currency_code').label('currency_code'),
                ]
            )
            .where(and_(*limitations))
            .order_by(literal_column('c.contract_id').asc())
            .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)

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

        Dual-path under SINGLE_SUPPLY_CHAIN_COMPANY_CODES:
        - FF OFF (default): resolve PC via the legacy SE→PC join.
        - FF ON: resolve PC directly from ``contract.reference_sap_profit_center_id``.

        Both branches return the same column schema.

        Args:
            contract_id (int): id of the contract
        Return:
            a dict having contract and
        """
        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,
        )

        if is_single_supply_chain_company_codes_enabled():
            query = query.join(
                ReferenceSapProfitCenter,
                ReferenceSapProfitCenter.reference_sap_profit_center_id
                == Contract.reference_sap_profit_center_id,
            )
        else:
            query = query.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,
            )

        result = query.filter(Contract.contract_id == contract_id).limit(1).first()
        return result if result else None

    @staticmethod
    def can_be_deleted(contract_id: int) -> bool:
        """Check if a contract can be deleted (no accounting activity)."""
        return Contract.can_be_deleted_by_ids([contract_id])[contract_id]

    @staticmethod
    def can_be_deleted_by_ids(contract_ids: list[int]) -> dict[int, bool]:
        """Map each contract id to whether it can be deleted (no accounting activity)."""
        if not contract_ids:
            return {}
        stmt = text(queries.CAN_CONTRACTS_BE_DELETED).bindparams(
            bindparam('contract_ids', expanding=True)
        )
        rows = db.session.execute(stmt, {'contract_ids': list(contract_ids)}).all()
        with_activity = {row[0] for row in rows}
        return {cid: cid not in with_activity for cid in contract_ids}

    @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_primary_contract_by_account_id_and_contract_type(
        cls, account_id: int, contract_type: str
    ) -> dict:
        """Get the primary contract by account_id and contract_type.

        Args:
            contract_type (str): type of contract (i.e. 'distribution')
            account_id (int): ID of the account to which the new contract belongs
        Returns:
            a contract
        """
        return (
            cls.query.join(
                AccountContract, cls.contract_id == AccountContract.contract_id
            )
            .join(
                ContractLifecycle,
                cls.contract_id == ContractLifecycle.contract_id,
                isouter=True,
            )
            .filter(
                and_(
                    cls.contract_type == contract_type,
                    AccountContract.account_id == account_id,
                    cls.is_primary_contract == 1,
                    or_(
                        ContractLifecycle.contract_lifecycle_id.is_(None),
                        and_(
                            ContractLifecycle.lifecycle_status.in_(
                                [
                                    CONTRACT_LIFECYCLE_STATUSES.INIT,
                                    CONTRACT_LIFECYCLE_STATUSES.ACTIVE,
                                    CONTRACT_LIFECYCLE_STATUSES.TO_BE_TERMINATED,
                                    CONTRACT_LIFECYCLE_STATUSES.TERMINATED,
                                ]
                            ),
                            ContractLifecycle.deleted_by.is_(None),
                            ContractLifecycle.deleted_at.is_(None),
                        ),
                    ),
                )
            )
            .one_or_none()
        )