"""source ranking: playback telemetry + per-origin source_stats

Revision ID: 0025_source_ranking
Revises: 0024_saved_searches
Create Date: 2026-06-22

Ranking stron-źródeł na Sites screen (user request): ocena 0-5★ per origin wg
częstotliwości odświeżania, bogactwa metadanych i tego czy źródło realnie gra.
- playback_events: fire-and-forget telemetria odtwarzania z apki (sygnał health),
- source_stats: policzona offline ocena per origin (run_source_stats).
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0025_source_ranking"
down_revision: str | None = "0024_saved_searches"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "playback_events",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("origin", sa.String(length=64), nullable=False),
        sa.Column("scene_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("status", sa.String(length=16), nullable=False),
        sa.Column("error_kind", sa.String(length=64), nullable=True),
        sa.Column("ttff_ms", sa.Integer(), nullable=True),
        sa.Column("device_id", sa.String(length=64), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.PrimaryKeyConstraint("id", name="pk_playback_events"),
    )
    op.create_index(
        "ix_playback_events_origin_created", "playback_events", ["origin", "created_at"]
    )

    op.create_table(
        "source_stats",
        sa.Column("origin", sa.String(length=64), nullable=False),
        sa.Column("stars", sa.SmallInteger(), nullable=False, server_default="0"),
        sa.Column("freshness", sa.SmallInteger(), nullable=False, server_default="0"),
        sa.Column("richness", sa.SmallInteger(), nullable=False, server_default="0"),
        sa.Column("health", sa.SmallInteger(), nullable=True),
        sa.Column("scenes", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("new_7d", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("newest_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "components", postgresql.JSONB(), nullable=False, server_default="{}"
        ),
        sa.Column(
            "computed_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.PrimaryKeyConstraint("origin", name="pk_source_stats"),
    )


def downgrade() -> None:
    op.drop_table("source_stats")
    op.drop_index("ix_playback_events_origin_created", table_name="playback_events")
    op.drop_table("playback_events")
