"""Stream R2b: read state per thread, the follow-up mark, and where a moved comment came from.

Schema only (build plan §2): a new table and nullable columns, no data move. Rehearsed up, down
and up on `dfp-devdb` from a copy of the newest nightly dump. Revises the hours migration
(`d8e9f0a1b2c3`), the single head at the time of writing; `alembic heads` was checked
immediately before this file was written.

1. `thread_reads`: one row per (thread, account), `read_at` moved on every open, so the panel
   can say what is unread for the viewer (T12). A surrogate id plus the unique pair, so the
   staging refresh puts it back by natural key like every other client-written table.
2. `threads.followup_note`, `followup_by_id`, `followup_at`: the needs-follow-up mark a curator
   sets with a one-line note, who and when (T13); cleared by setting the three NULL.
3. `discussion_comments.moved_from_thread_id`, `moved_by_id`, `moved_at`: a re-filed comment
   keeps who moved it and where it was (T18). Who-columns are `FK accounts.id NULL`.

Revision ID: e9f0a1b2c3d4
Revises: d8e9f0a1b2c3
Create Date: 2026-09-13
"""

import sqlalchemy as sa

from alembic import op

revision = "e9f0a1b2c3d4"
down_revision = "d8e9f0a1b2c3"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "thread_reads",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("thread_id", sa.Integer(), sa.ForeignKey("threads.id"), nullable=False),
        sa.Column("account_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=False),
        sa.Column("read_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("thread_id", "account_id", name="uq_thread_read"),
    )
    op.create_index("ix_thread_reads_account_id", "thread_reads", ["account_id"])

    op.add_column("threads", sa.Column("followup_note", sa.String(400), nullable=True))
    op.add_column("threads", sa.Column("followup_by_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True))
    op.add_column("threads", sa.Column("followup_at", sa.DateTime(timezone=True), nullable=True))

    op.add_column("discussion_comments", sa.Column("moved_from_thread_id", sa.Integer(), sa.ForeignKey("threads.id"), nullable=True))
    op.add_column("discussion_comments", sa.Column("moved_by_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True))
    op.add_column("discussion_comments", sa.Column("moved_at", sa.DateTime(timezone=True), nullable=True))


def downgrade() -> None:
    op.drop_column("discussion_comments", "moved_at")
    op.drop_column("discussion_comments", "moved_by_id")
    op.drop_column("discussion_comments", "moved_from_thread_id")
    op.drop_column("threads", "followup_at")
    op.drop_column("threads", "followup_by_id")
    op.drop_column("threads", "followup_note")
    op.drop_index("ix_thread_reads_account_id", table_name="thread_reads")
    op.drop_table("thread_reads")