"""reference_signing_entity Model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import CRUDMixin
from sqlalchemy import asc, desc
from sqlalchemy.engine import Row

from abacus_contract.utils.request import escape_like_pattern


class ReferenceSigningEntity(db.Model, CRUDMixin):
    """reference_signing_entity Model."""

    __tablename__ = 'reference_signing_entity'

    reference_signing_entity_id = db.Column(db.Integer, primary_key=True)
    reference_sap_profit_center_id = db.Column(
        db.Integer,
        db.ForeignKey('reference_sap_profit_center.reference_sap_profit_center_id'),
        nullable=False,
    )
    reference_payment_entity_id = db.Column(
        db.Integer,
        db.ForeignKey('reference_payment_entity.reference_payment_entity_id'),
        nullable=False,
    )
    company_code = db.Column(db.String(8), nullable=False)
    tax_entity_company_code = db.Column(db.String(8), nullable=True)
    legal_name = db.Column(db.String(180), nullable=False)
    vat_number = db.Column(db.String(30), nullable=True)
    company_registration_number = db.Column(db.String(30), nullable=True)
    address = db.Column(db.String(180), nullable=True)
    deleted_at = db.Column(db.DateTime, nullable=True)
    deleted_by = db.Column(db.String(180), nullable=True)

    reference_sap_profit_center = db.relationship(
        'ReferenceSapProfitCenter', back_populates='reference_signing_entities'
    )

    @classmethod
    def get_authorized_for_sap_profit_centers(
        cls,
        reference_sap_profit_center_ids: list[int],
        limit: int,
        offset: int,
        search_term: str | None = None,
    ) -> tuple[list[dict], int]:
        """Get signing entities authorized for a SAP profit center (live mappings only).

        Returns each junction row with the SE nested inline. The junction id
        is needed by the admin drawer so revoking a mapping can issue a
        targeted DELETE. Optional ``search_term`` matches case-insensitively
        against ``legal_name`` with LIKE wildcards escaped.

        Returns:
            tuple of (items, total_count). Each ``items`` entry is a dict
            shaped for ``SigningEntitySapProfitCenterWithSigningEntitySchema``:
            ``{signing_entity_sap_profit_center_id, created_at, signing_entity: SE}``.
        """
        # 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_signing_entity_id
                == SigningEntitySapProfitCenter.reference_signing_entity_id,
            )
            .filter(
                SigningEntitySapProfitCenter.reference_sap_profit_center_id.in_(
                    reference_sap_profit_center_ids
                ),
                SigningEntitySapProfitCenter.deleted_at.is_(None),
                SigningEntitySapProfitCenter.deleted_by.is_(None),
            )
        )

        if search_term:
            pattern = f'%{escape_like_pattern(search_term)}%'
            query = query.filter(cls.legal_name.ilike(pattern))

        total_count = query.count()
        rows = query.order_by(cls.legal_name).limit(limit).offset(offset).all()
        items = [
            {
                'signing_entity_sap_profit_center_id': (
                    junction.signing_entity_sap_profit_center_id
                ),
                'reference_sap_profit_center_id': junction.reference_sap_profit_center_id,
                'created_at': junction.created_at,
                'signing_entity': signing_entity,
            }
            for junction, signing_entity in rows
        ]
        return items, total_count

    @classmethod
    def get_by_ids(
        cls,
        signing_entity_ids: list[int],
    ) -> list[Row]:
        """Get the signing entities by ids.

        Args:
            signing_entity_ids (list[int]): A list of signing entity IDs.

        Returns:
            list[Row]: A list of signing entities
        """
        if not signing_entity_ids:
            return []

        unique_ids = list(set(signing_entity_ids))

        items = (
            cls.query.filter(
                cls.reference_signing_entity_id.in_(unique_ids),
                # Exclude soft-deleted rows, matching the single-id GET and list
                # reads; a batched consumer must not resurrect deleted entities.
                cls.deleted_at.is_(None),
                cls.deleted_by.is_(None),
            )
            .order_by(cls.reference_signing_entity_id)
            .all()
        )

        return items

    @classmethod
    def get_reference_signing_entities(
        cls,
        limit: int,
        offset: int,
        sort_by: str,
        sort_order: str,
    ) -> tuple:
        """Get the list of reference-signing-entities.

        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".

        Returns:
            A tuple contains the fields items and total_count
        """
        query = cls.query.filter(cls.deleted_at.is_(None), cls.deleted_by.is_(None))

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

        return items, total_count