"""Reference Signing Entity Model."""

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String

from moneyhub.connectors.mysql import db
from moneyhub.models.mysql_base import BaseModel


class ReferenceSigningEntity(BaseModel):
    """Reference Signing Entity Model."""

    __tablename__ = 'reference_signing_entity'
    reference_signing_entity_id = Column(Integer, primary_key=True)
    reference_payment_entity_id = Column(Integer)
    reference_sap_profit_center_id = Column(Integer)
    company_code = Column(String(8), nullable=False)
    tax_entity_company_code = Column(String(8), nullable=True)
    legal_name = Column(String(180), nullable=True)
    vat_number = Column(String(30), nullable=True)
    company_registration_number = Column(String(30), nullable=True)
    address = Column(String(180), nullable=True)

    @classmethod
    def get_by_contract_id(cls, contract_id) -> dict:
        """GET ReferenceSigningEntity by a contract's ID.

        Args:
            contract_id (int): the ID of a contract
        """
        from moneyhub.models.contract import Contract
        filters = [
            Contract.contract_id == contract_id,
        ]

        sql_query = select(
            ReferenceSigningEntity.reference_signing_entity_id,
            ReferenceSigningEntity.company_code,
            ReferenceSigningEntity.tax_entity_company_code,
            ReferenceSigningEntity.legal_name,
            ReferenceSigningEntity.vat_number,
            ReferenceSigningEntity.company_registration_number,
            ReferenceSigningEntity.address
        )\
            .select_from(ReferenceSigningEntity) \
            .join(
                Contract,
                Contract.reference_signing_entity_id
                == ReferenceSigningEntity.reference_signing_entity_id) \
            .where(*filters)

        return db.session.execute(sql_query).fetchone()