"""PerformanceNrSoundRecording Model."""

from sqlalchemy import Boolean
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Integer
from sqlalchemy import JSON
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 PerformanceNrSoundRecording(BaseModel, CRUDMixin):
    """Performance NR Sound Recording model."""

    __tablename__ = 'performance_nr_sound_recording'
    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}'}

    recording_id = Column('id', String(255), nullable=False, primary_key=True)
    album_name = Column(String(255), nullable=True)
    catalog_number = Column(String(255), nullable=True)
    countries_of_recording = Column(JSON, nullable=True)
    country_of_label = Column(String(255), nullable=True)
    country_of_master = Column(String(255), nullable=True)
    duration = Column(Integer, nullable=True)
    first_release_year = Column(Integer, nullable=True)
    isrc = Column(String(255), nullable=True)
    main_artist = Column(String(255), nullable=True)
    priority = Column(String(255), nullable=True)
    original_label = Column(String(255), nullable=True)
    recording_title = Column(String(255), nullable=True)
    recording_type = Column(String(255), nullable=True)
    recording_year = Column(Integer, nullable=True)
    total_featured_performers = Column(Integer, nullable=True)
    version = Column(String(255), nullable=True)
    external_id = Column(String(255), nullable=True)
    created_at = Column(DateTime, nullable=True)
    created_by = Column(String(255), nullable=True)
    created_by_identity_id = Column(String(255), nullable=True)
    last_modified_at = Column(DateTime, nullable=True)
    last_modified_by = Column(String(255), nullable=True)
    last_modified_by_identity_id = Column(String(255), nullable=True)
    deleted_at = Column(DateTime, nullable=True)
    is_deleted = Column(Boolean, nullable=True)

    @classmethod
    def get_recording_names_by_ids(cls, recording_ids: list[str]) -> list:
        """Get recording id and name pairs for NR 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(
                cls.recording_id.label('recording_id'),
                cls.recording_title.label('recording_title'),
                cls.isrc.label('isrc'),
            ) \
            .filter(cls.recording_id.in_(recording_ids)) \
            .all()