"""baseline clean schema

Revision ID: b7c3e1a9d4f2
Revises:
Create Date: 2026-06-26 12:00:00.000000+00:00
"""

from collections.abc import Sequence

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "b7c3e1a9d4f2"
down_revision: str | Sequence[str] | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS dsp_client (
            id           BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 1) PRIMARY KEY,
            dsp_id       TEXT    NOT NULL,
            name         TEXT    NOT NULL UNIQUE,
            display_name TEXT    NOT NULL,
            status       TEXT    NOT NULL DEFAULT 'active',
            nominal_rps  INTEGER
        )
    """)

    op.execute("""
        INSERT INTO dsp_client (dsp_id, name, display_name, status, nominal_rps) VALUES
            ('spotify', 'spotify_songwhip', 'Spotify: Songwhip',         'paused', 8),
            ('spotify', 'spotify_smf_sme',  'Spotify: SMF - SME',         'paused', 20),
            ('spotify', 'spotify_smf_orch', 'Spotify: SMF - The Orchard', 'paused', 20)
        ON CONFLICT (name) DO NOTHING
    """)

    op.execute("""
        CREATE TABLE IF NOT EXISTS fan_connection (
            fan_id               TEXT      NOT NULL,
            dsp_id               TEXT      NOT NULL,
            dsp_client_id        BIGINT    NOT NULL,
            token_encrypted      TEXT      NOT NULL,
            token_refreshed_at   TIMESTAMP WITH TIME ZONE,
            status               TEXT      NOT NULL DEFAULT 'active',
            created_at           TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
            last_connected_at    TIMESTAMP WITH TIME ZONE,
            last_collected_at    TIMESTAMP WITH TIME ZONE,
            last_dispatched_at   TIMESTAMP WITH TIME ZONE,
            consecutive_failures INTEGER,
            PRIMARY KEY (fan_id, dsp_id, dsp_client_id)
        )
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_fan_connection_dsp_client_id_fan_id
            ON fan_connection (dsp_client_id, fan_id)
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_fan_connection_created_at
            ON fan_connection (created_at)
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_fan_connection_planner
            ON fan_connection (dsp_client_id, status, last_collected_at NULLS FIRST)
            INCLUDE (last_connected_at, last_dispatched_at, consecutive_failures)
    """)

    op.execute("""
        CREATE TABLE IF NOT EXISTS fan_collection_state (
            fan_id                        TEXT      NOT NULL,
            dsp_id                        TEXT      NOT NULL,
            last_dsp_client_id            BIGINT    NOT NULL,
            last_collected_at             TIMESTAMP WITH TIME ZONE NOT NULL,
            last_collection_error         TEXT,
            consecutive_failures          INTEGER   NOT NULL DEFAULT 0,
            profile_collected_at          TIMESTAMP WITH TIME ZONE,
            top_artists_collected_at      TIMESTAMP WITH TIME ZONE,
            top_tracks_collected_at       TIMESTAMP WITH TIME ZONE,
            recently_played_collected_at  TIMESTAMP WITH TIME ZONE,
            playlists_collected_at        TIMESTAMP WITH TIME ZONE,
            saved_albums_collected_at     TIMESTAMP WITH TIME ZONE,
            saved_tracks_collected_at     TIMESTAMP WITH TIME ZONE,
            followed_artists_collected_at TIMESTAMP WITH TIME ZONE,
            PRIMARY KEY (fan_id, dsp_id)
        )
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_fan_collection_state_last_collected_at
            ON fan_collection_state (last_collected_at)
    """)

    op.execute("""
        CREATE TABLE IF NOT EXISTS task_fanout (
            id              UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
            started_at      TIMESTAMP WITH TIME ZONE NOT NULL,
            finished_at     TIMESTAMP WITH TIME ZONE,
            source          TEXT    NOT NULL,
            status          TEXT    NOT NULL,
            finished_reason TEXT,
            fans_dispatched INTEGER NOT NULL DEFAULT 0,
            messages_sent   INTEGER NOT NULL DEFAULT 0,
            filters         TEXT
        )
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_task_fanout_started_at
            ON task_fanout (started_at)
    """)

    op.execute("""
        CREATE TABLE IF NOT EXISTS task_collect (
            id                    UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
            fanout_task_id        UUID    NOT NULL,
            dsp_client_name       TEXT    NOT NULL,
            started_at            TIMESTAMP WITH TIME ZONE NOT NULL,
            finished_at           TIMESTAMP WITH TIME ZONE,
            status                TEXT    NOT NULL,
            finished_reason       TEXT,
            fans_total            INTEGER NOT NULL,
            fans_skipped          INTEGER NOT NULL DEFAULT 0,
            fans_processed        INTEGER NOT NULL DEFAULT 0,
            fans_errors           INTEGER NOT NULL DEFAULT 0,
            fans_stale_tokens     INTEGER NOT NULL DEFAULT 0,
            requests              INTEGER NOT NULL DEFAULT 0,
            requests_skipped      INTEGER NOT NULL DEFAULT 0,
            requests_rate_limited INTEGER NOT NULL DEFAULT 0
        )
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_task_collect_dsp_client_name_started_at
            ON task_collect (dsp_client_name, started_at)
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_task_collect_fanout_task_id_dsp_client_name
            ON task_collect (fanout_task_id, dsp_client_name)
    """)
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_task_collect_started_at_status
            ON task_collect (started_at, status)
    """)


def downgrade() -> None:
    for table in (
        "task_collect",
        "task_fanout",
        "fan_collection_state",
        "fan_connection",
        "dsp_client",
    ):
        op.execute(f"DROP TABLE IF EXISTS {table}")