"""series suggestions: assisted-continuation matcher (FC-6.3)

Revision ID: 0041
Revises: 0040
Create Date: 2026-06-07

A confirm-only queue of "this post may continue this series" hints, plus two
import_settings knobs (enable + score threshold) for the matcher.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0041"
down_revision: Union[str, None] = "0040"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "series_suggestion",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column(
            "post_id",
            sa.Integer,
            sa.ForeignKey("post.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "series_tag_id",
            sa.Integer,
            sa.ForeignKey("tag.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("score", sa.Float, nullable=False),
        sa.Column("signals", sa.JSON, nullable=True),
        sa.Column(
            "status", sa.String(16), nullable=False, server_default="pending"
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.UniqueConstraint(
            "post_id", "series_tag_id", name="uq_series_suggestion_post_series"
        ),
    )
    op.create_index(
        "ix_series_suggestion_post_id", "series_suggestion", ["post_id"]
    )
    op.create_index(
        "ix_series_suggestion_series_tag_id",
        "series_suggestion",
        ["series_tag_id"],
    )
    op.create_index(
        "ix_series_suggestion_status", "series_suggestion", ["status"]
    )

    op.add_column(
        "import_settings",
        sa.Column(
            "series_suggest_enabled",
            sa.Boolean,
            nullable=False,
            server_default=sa.true(),
        ),
    )
    op.add_column(
        "import_settings",
        sa.Column(
            "series_suggest_threshold",
            sa.Float,
            nullable=False,
            server_default="0.5",
        ),
    )


def downgrade() -> None:
    op.drop_column("import_settings", "series_suggest_threshold")
    op.drop_column("import_settings", "series_suggest_enabled")
    op.drop_index("ix_series_suggestion_status", table_name="series_suggestion")
    op.drop_index(
        "ix_series_suggestion_series_tag_id", table_name="series_suggestion"
    )
    op.drop_index("ix_series_suggestion_post_id", table_name="series_suggestion")
    op.drop_table("series_suggestion")