"""DimTrack Model."""

from sqlalchemy import cast
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Integer
from sqlalchemy import String

from moneyhub.config import Config
from moneyhub.constants.constants import Environment
from moneyhub.models.mysql_base import CRUDMixin
from moneyhub.models.snowflake_base import BaseModel


class DimTrack(BaseModel, CRUDMixin):
    """Dim Track model."""

    __tablename__ = 'dim_track'
    if Config.ENVIRONMENT != Config.TEST_ENVIRONMENT:
        env = Environment.QA if Config.ENVIRONMENT in [Environment.QA, Environment.DEV] else (
            Environment.PROD)
        __table_args__ = {'schema': f'FACTS.{env.value}'}

    trackid = Column(Integer, nullable=False, primary_key=True)
    track_unique_id = Column(Integer, nullable=False)
    trackname = Column(String(2000), nullable=True)
    upc = Column(Integer, nullable=True)
    cd = Column(Integer, nullable=True)
    track_id = Column(Integer, nullable=True)
    isrc = Column(String(255), nullable=True)
    p_line = Column(String(255), nullable=True)
    track_type = Column(String(255), nullable=True)
    version = Column(String(255), nullable=True)
    source = Column(String(255), nullable=True)
    last_updated = Column(DateTime, nullable=True)
    date_created = Column(DateTime, nullable=True)
    labelid = Column(Integer, nullable=True)
    vendor_track_identifier = Column(String(255), nullable=True)
    isrcid = Column(Integer, nullable=True)
    third_party_publisher = Column(String(255), nullable=True)
    resolution = Column(String(255), nullable=True)
    duration = Column(Integer, nullable=True)
    offer_type = Column(String(255), nullable=True)

    @classmethod
    def get_recording_names_by_ids(cls, recording_ids: list[str]) -> list:
        """Get recording id and name pairs for distro recordings.

        Args:
            recording_ids (list[str]): List of recording IDs to look up
        Returns:
            list: List of rows with recording_id, recording_title and isrc
        """
        return cls.query \
            .with_entities(
                cast(cls.track_unique_id, String).label('recording_id'),
                cls.trackname.label('recording_title'),
                cls.isrc.label('isrc'),
            ) \
            .filter(cls.track_unique_id.in_(recording_ids)) \
            .all()