"""reference_sap_profit_center Model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import and_, asc, case, desc, func, or_
from sqlalchemy.engine import Row

from abacus_contract.utils.request import escape_like_pattern


class ReferenceSapProfitCenter(BaseModel):
    """reference_sap_profit_center Model."""

    __tablename__ = 'reference_sap_profit_center'

    reference_sap_profit_center_id = db.Column(db.Integer, primary_key=True)
    profit_center = db.Column(db.String(10), nullable=False)
    company_code = db.Column(db.String(4), nullable=False)
    business_group = db.Column(db.String(3), nullable=False)
    display_name = db.Column(db.String(180), nullable=False)

    reference_signing_entities = db.relationship(
        'ReferenceSigningEntity',
        back_populates='reference_sap_profit_center',
        cascade='all, delete-orphan',
        lazy='dynamic',
    )

    @classmethod
    def get_default(cls):
        """Get default reference_sap_profit_center entry."""
        return cls.query.filter(cls.business_group == 'ORC').first()

    @classmethod
    def get_authorized_for_signing_entities(
        cls,
        reference_signing_entity_ids: list[int],
        limit: int,
        offset: int,
        search_term: str | None = None,
    ) -> tuple[list[dict], int]:
        """Get profit centers authorized for a signing entity (live mappings only).

        Returns each junction row with the PC nested inline. Optional
        ``search_term`` matches case-insensitively against ``display_name`` and
        ``profit_center`` with LIKE wildcards escaped via ``escape_like_pattern``.

        Returns:
            tuple of (items, total_count). Each ``items`` entry is a dict
            shaped for ``SigningEntitySapProfitCenterWithSapProfitCenterSchema``:
            ``{signing_entity_sap_profit_center_id, created_at, sap_profit_center: PC}``.
        """
        # Local import to avoid a circular dependency with the junction model.
        from abacus_contract.models.signing_entity_sap_profit_center import (
            SigningEntitySapProfitCenter,
        )

        query = (
            db.session.query(SigningEntitySapProfitCenter, cls)
            .join(
                cls,
                cls.reference_sap_profit_center_id
                == SigningEntitySapProfitCenter.reference_sap_profit_center_id,
            )
            .filter(
                SigningEntitySapProfitCenter.reference_signing_entity_id.in_(
                    reference_signing_entity_ids
                ),
                SigningEntitySapProfitCenter.deleted_at.is_(None),
                SigningEntitySapProfitCenter.deleted_by.is_(None),
            )
        )

        if search_term:
            pattern = f'%{escape_like_pattern(search_term)}%'
            query = query.filter(
                or_(cls.display_name.ilike(pattern), cls.profit_center.ilike(pattern))
            )

        total_count = query.count()
        rows = query.order_by(cls.display_name).limit(limit).offset(offset).all()
        items = [
            {
                'signing_entity_sap_profit_center_id': (
                    junction.signing_entity_sap_profit_center_id
                ),
                'reference_signing_entity_id': junction.reference_signing_entity_id,
                'created_at': junction.created_at,
                'sap_profit_center': profit_center,
            }
            for junction, profit_center in rows
        ]
        return items, total_count

    @classmethod
    def get_reference_sap_profit_centers(
        cls,
        limit: int,
        offset: int,
        sort_by: str,
        sort_order: str,
        search_term: str = None,
        orphan: bool = None,
        signing_entity_ids: str = None,
    ) -> tuple:
        """Get the list of reference-sap-profit-centers.

        Args:
            limit (int): The maximum number of records to return per page.
            offset (int): The number of records to skip before starting to return results.
            sort_by (str): The column name used to sort the results.
            sort_order (str): The sort direction, either "asc" or "desc".
            search_term (str): Free-text search filter matched against `display_name` and `profit_center`.
            orphan (bool): If True, filters the results to only return records that lack an active junction/mapping entry.
            signing_entity_ids (str): Comma separated signing entity ids.

        Returns:
            A tuple contains the fields items and total_count
        """
        from abacus_contract.models.signing_entity_sap_profit_center import (
            SigningEntitySapProfitCenter,
        )

        query = cls.query.outerjoin(
            SigningEntitySapProfitCenter,
            cls.reference_sap_profit_center_id
            == SigningEntitySapProfitCenter.reference_sap_profit_center_id,
        )

        active_link_cond = and_(
            SigningEntitySapProfitCenter.reference_sap_profit_center_id.is_not(None),
            SigningEntitySapProfitCenter.deleted_at.is_(None),
            SigningEntitySapProfitCenter.deleted_by.is_(None),
        )

        if orphan is True:
            query = query.group_by(cls.reference_sap_profit_center_id).having(
                func.count(case((active_link_cond, 1))) == 0
            )

        if orphan is False:
            query = query.group_by(cls.reference_sap_profit_center_id).having(
                func.count(case((active_link_cond, 1))) > 0
            )

        if search_term:
            pattern = f'%{escape_like_pattern(search_term)}%'
            query = query.filter(
                or_(cls.display_name.ilike(pattern), cls.profit_center.ilike(pattern))
            )

        if signing_entity_ids and orphan is not True:
            query = query.filter(
                active_link_cond,
                SigningEntitySapProfitCenter.reference_signing_entity_id.in_(
                    [int(x.strip()) for x in signing_entity_ids.split(',')]
                ),
            )

        query = query.distinct()

        sort_column = sort_by
        if sort_by == 'reference_sap_profit_center_id':
            sort_column = cls.reference_sap_profit_center_id

        items = (
            query.order_by(
                desc(sort_column) if sort_order == 'desc' else asc(sort_column)
            )
            .limit(limit)
            .offset(offset)
            .all()
        )
        total_count = query.count()

        return items, total_count

    @classmethod
    def get_by_ids(
        cls,
        profit_center_ids: list[int],
    ) -> list[Row]:
        """Get the SAP profit centers by ids.

        Args:
            profit_center_ids (list[int]): A list of profit center IDs.

        Returns:
            list[Row]: A list of SAP profit centers
        """
        if not profit_center_ids:
            return []

        unique_ids = list(set(profit_center_ids))

        items = (
            cls.query.filter(cls.reference_sap_profit_center_id.in_(unique_ids))
            .order_by(cls.reference_sap_profit_center_id)
            .all()
        )

        return items