from __future__ import annotations

from datetime import datetime
from typing import TYPE_CHECKING, Optional

from sqlalchemy import FetchedValue, ForeignKey, Index, PrimaryKeyConstraint, UniqueConstraint, func, text
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.types import String

from src.core.models.base import Base
from src.core.models.utils import BooleanBit, IntEnum

from ..enums import FieldCopyMode, FlagType, MusicService, ServiceType


if TYPE_CHECKING:
    from src.core.models import ApplicationInstance
    from src.service_account.models import ServiceAccount


__all__ = [
    "PlaylistSynchronization",
    "PlaylistSynchronizationFlaggedChannel",
    "PlaylistSynchronizationInsertMedia",
    "PlaylistSynchronizationLog",
    "TracksCache",
]


class PlaylistSynchronization(Base):
    __tablename__ = "tblPlaylistSynchronization"

    id: Mapped[int] = mapped_column("ID", primary_key=True)
    application_id: Mapped[int] = mapped_column(
        "ApplicationId", ForeignKey("tblApplicationInstance.intApplicationInstanceID"), nullable=True
    )
    from_playlist_id: Mapped[str] = mapped_column("FromPlaylistId", String(255), nullable=True)
    from_service_type: Mapped[ServiceType] = mapped_column("FromServiceType", IntEnum(ServiceType), nullable=True)
    from_music_service: Mapped[MusicService] = mapped_column("FromMusicServiceId", IntEnum(MusicService), nullable=True)
    to_playlist_id: Mapped[str] = mapped_column("ToPlaylistId", String(255), unique=True, nullable=True)
    to_service_account_id: Mapped[int] = mapped_column(
        "ToServiceAccountId", ForeignKey("tblServiceAccount.Id"), nullable=True
    )
    title: Mapped[Optional[str]] = mapped_column("Title", String(255), nullable=True)
    description: Mapped[Optional[str]] = mapped_column("Description", String(2000), nullable=True)
    updated_at: Mapped[datetime] = mapped_column(
        "LastUpdated",
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
        server_onupdate=FetchedValue(),
        nullable=True,
    )
    active: Mapped[bool] = mapped_column("Active", nullable=True)
    title_copy_mode: Mapped[FieldCopyMode] = mapped_column("TitleCopyMode", IntEnum(FieldCopyMode), nullable=True)
    description_copy_mode: Mapped[FieldCopyMode] = mapped_column(
        "DescriptionCopyMode", IntEnum(FieldCopyMode), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column("CreatedAt", server_default=func.now(), nullable=True)
    source_title: Mapped[str] = mapped_column("SourceTitle", String(255), nullable=True)
    source_image: Mapped[str] = mapped_column("SourceImage", String(100), nullable=True)
    source_track_count: Mapped[int] = mapped_column("SourceTrackCount", nullable=True)
    synchronized_track_count: Mapped[int] = mapped_column("SynchronizedTrackCount", nullable=True)
    append_track_list: Mapped[bool] = mapped_column("AppendTrackList", BooleanBit, server_default=text("0"))
    error: Mapped[bool] = mapped_column("Error", BooleanBit, server_default=text("0"))
    source_service_account_id: Mapped[str] = mapped_column("SourceServiceAccountId", String(255), nullable=True)
    source_service_account_name: Mapped[str] = mapped_column("SourceServiceAccountName", String(255), nullable=True)
    to_playlist_title: Mapped[str] = mapped_column("ToPlaylistTitle", String(255), nullable=True)
    to_playlist_image: Mapped[str] = mapped_column("ToPlaylistImage", String(255), nullable=True)

    application: Mapped["ApplicationInstance"] = relationship(back_populates="playlist_synchronizations", lazy="raise")
    service_account: Mapped["ServiceAccount"] = relationship(back_populates="playlist_synchronizations", lazy="raise")
    insert_media: Mapped[list["PlaylistSynchronizationInsertMedia"]] = relationship(
        back_populates="playlist_synchronization", lazy="noload"
    )
    latest_log: Mapped["PlaylistSynchronizationLog"] = relationship(
        back_populates="playlist_synchronization", lazy="raise"
    )

    __table_args__ = (UniqueConstraint("FromPlaylistId", "ToPlaylistId"),)


class PlaylistSynchronizationFlaggedChannel(Base):
    __tablename__ = "tblPlaylistSynchronizationFlaggedChannel"

    id: Mapped[int] = mapped_column("Id", primary_key=True)
    channel_id: Mapped[str] = mapped_column("ChannelId", String(255))
    flag_type: Mapped[int] = mapped_column("FlagType", IntEnum(FlagType))


class PlaylistSynchronizationInsertMedia(Base):
    __tablename__ = "tblPlaylistSynchronizationInsertMedia"

    id: Mapped[int] = mapped_column("Id", primary_key=True)
    media_id: Mapped[str] = mapped_column("MediaId", String(255))
    insert_position: Mapped[int] = mapped_column("InsertPosition")
    playlist_synchronization_id: Mapped[int] = mapped_column(
        "PlaylistSynchronizationId", ForeignKey("tblPlaylistSynchronization.ID")
    )

    playlist_synchronization: Mapped["PlaylistSynchronization"] = relationship(
        back_populates="insert_media", lazy="raise"
    )


class PlaylistSynchronizationLog(Base):
    __tablename__ = "tblPlaylistSynchronizationLog"

    playlist_synchronization_id: Mapped[int] = mapped_column(
        "PlaylistSynchronizationId", ForeignKey("tblPlaylistSynchronization.ID")
    )
    timestamp: Mapped[datetime] = mapped_column("Time", server_default=func.now())
    made_change: Mapped[bool] = mapped_column("MadeChanges")
    added_tracks: Mapped[int] = mapped_column("AddedTracks")
    deleted_tracks: Mapped[int] = mapped_column("DeletedTracks")
    deleted_duplicates: Mapped[int] = mapped_column("DeletedDuplicates")
    source_tracks: Mapped[int] = mapped_column("SourceTracks")
    target_track_count: Mapped[int] = mapped_column("TargetTrackCount")
    error_text: Mapped[str] = mapped_column("ErrorMessage", String(1000))
    triggered_manually: Mapped[bool] = mapped_column("TriggeredManually")
    error_type: Mapped[str] = mapped_column("Error", String(256))

    playlist_synchronization: Mapped["PlaylistSynchronization"] = relationship(
        back_populates="latest_log", lazy="raise"
    )

    __table_args__ = (PrimaryKeyConstraint("PlaylistSynchronizationId", "Time", name="IDX_PlaylistSyncIdTime"),)


class TracksCache(Base):
    __tablename__ = "tblPlaylistSynchronizationTrack"

    id: Mapped[int] = mapped_column("Id", primary_key=True)
    isrc: Mapped[Optional[str]] = mapped_column("Isrc", String(20))
    service_type: Mapped[ServiceType] = mapped_column("ServiceType", IntEnum(ServiceType))
    track_id: Mapped[str] = mapped_column("TrackId", String(100))
    match_date: Mapped[datetime] = mapped_column("MatchDate", default=func.now())
    source: Mapped[int] = mapped_column("Source", server_default=text("0"))  # TODO: figure out what is this

    # TODO: make this index unique
    __table_args__ = (Index("index2", "ServiceType", "Isrc"),)