"""The link between a dailysplice account and a Spotify account.

One row per connected user (the primary key IS the user id — a user connects
exactly one Spotify account). Deleting the row is what "disconnect" means; the
tokens go with it.

Both tokens are stored ENCRYPTED (services/crypto.py). The columns are named
`*_encrypted` so that a query, a dump, or a `\\d` in psql makes it obvious that
the value is not a usable credential — and so that anyone tempted to write a
plaintext token into them has to rename the column first.
"""

from __future__ import annotations

from datetime import datetime

from sqlalchemy import DateTime, ForeignKey, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column

from .base import Base


class SpotifyAccount(Base):
    __tablename__ = "spotify_accounts"

    # One Spotify account per dailysplice user. ON DELETE CASCADE: removing a
    # user must not strand their third-party tokens in the database.
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
    )

    # Spotify's stable id for the account, and a display label for the UI.
    spotify_user_id: Mapped[str] = mapped_column(String(120), nullable=False)
    display_name: Mapped[str] = mapped_column(String(200), default="", nullable=False)
    # "premium" | "free" | "open" — recorded because some future capabilities
    # (playback control) are Premium-only, so it is worth knowing before
    # offering a feature that would fail for this user.
    product: Mapped[str] = mapped_column(String(32), default="", nullable=False)

    # Fernet ciphertext, never plaintext. See services/crypto.py.
    access_token_encrypted: Mapped[str] = mapped_column(Text, nullable=False)
    refresh_token_encrypted: Mapped[str] = mapped_column(Text, nullable=False)
    # When the ACCESS token expires (Spotify issues ~1h). The refresh token has
    # no expiry but can be revoked by the user at any time from their Spotify
    # account page — treat a refresh failure as "disconnected", not an error.
    access_expires_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )

    # The scopes Spotify actually granted (may be narrower than requested).
    # Stored so a later feature can check what it has instead of assuming.
    scopes: Mapped[str] = mapped_column(Text, default="", nullable=False)

    connected_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )