"""Migration #6: threads, the comment columns, notifications, email_sends, account_preferences.

Schema only (build plan §2): every column NULL or server-defaulted; the data move is
`python -m app.cli backfill threads` after the deploy (idempotent: a thread per legacy key,
`thread_id` set, `edited_at` where an edit had moved `updated_at`). Design: the accounts plan
§8 on the server's Interaction Standard. Revises migration #4 (`b5c6d7e8f9a0`), the single
head at the time of writing.

1. `threads`: one conversation per `(subject_type, subject_id)`, unique; `label` is the page's
   breadcrumb for the subject (a small addition to the plan's column list, so the bell can say
   what a thread is about without the server knowing every static key).
2. `discussion_comments` gains `thread_id` (FK, NULL until the follow-up makes it NOT NULL and
   drops `item_id`, `feature_key` and `ck_comment_one_target`), `edited_at`, `deleted_at`;
   `feature_key` widens to 80.
3. `notifications`: the standard's event shape with the who-columns as account ids.
4. `email_sends` and `account_preferences`, for the mail slice.

Revision ID: c7d8e9f0a1b2
Revises: b5c6d7e8f9a0
Create Date: 2026-09-11
"""

import sqlalchemy as sa

from alembic import op

revision = "c7d8e9f0a1b2"
down_revision = "b5c6d7e8f9a0"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "threads",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("subject_type", sa.String(24), nullable=False),
        sa.Column("subject_id", sa.String(80), nullable=False),
        sa.Column("label", sa.String(160), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("resolved", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("resolved_by_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
        sa.UniqueConstraint("subject_type", "subject_id", name="uq_thread_subject"),
    )

    op.add_column("discussion_comments", sa.Column("thread_id", sa.Integer(), nullable=True))
    op.create_foreign_key("fk_discussion_comments_thread", "discussion_comments", "threads", ["thread_id"], ["id"])
    op.create_index("ix_discussion_comments_thread_id", "discussion_comments", ["thread_id"])
    op.add_column("discussion_comments", sa.Column("edited_at", sa.DateTime(timezone=True), nullable=True))
    op.add_column("discussion_comments", sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True))
    op.alter_column("discussion_comments", "feature_key", type_=sa.String(80), existing_type=sa.String(64),
                    existing_nullable=True)

    op.create_table(
        "notifications",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("recipient_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=False),
        sa.Column("actor_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("app", sa.String(40), nullable=False, server_default="dfp"),
        sa.Column("kind", sa.String(16), nullable=False),
        sa.Column("category", sa.String(80), nullable=False, server_default=""),
        sa.Column("context_label", sa.String(220), nullable=False, server_default=""),
        sa.Column("body", sa.String(200), nullable=False, server_default=""),
        sa.Column("url", sa.String(400), nullable=False),
        sa.Column("source_type", sa.String(24), nullable=False, server_default="comment"),
        sa.Column("source_id", sa.String(64), nullable=False, server_default=""),
        sa.Column("project_id", sa.String(80), nullable=True),
        sa.Column("dedupe_key", sa.String(180), nullable=False),
        sa.Column("read", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("resolved", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("delivered_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("dedupe_key", name="uq_notifications_dedupe_key"),
    )
    op.create_index("ix_notifications_inbox", "notifications", ["recipient_id", "read"])

    op.create_table(
        "email_sends",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("bucket", sa.String(120), nullable=False),
        sa.Column("sent_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )
    op.create_index("ix_email_sends_bucket", "email_sends", ["bucket"])

    op.create_table(
        "account_preferences",
        sa.Column("account_id", sa.Integer(), sa.ForeignKey("accounts.id"), primary_key=True),
        sa.Column("mail_notifications", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )


def downgrade() -> None:
    op.drop_table("account_preferences")
    op.drop_index("ix_email_sends_bucket", table_name="email_sends")
    op.drop_table("email_sends")
    op.drop_index("ix_notifications_inbox", table_name="notifications")
    op.drop_table("notifications")
    op.alter_column("discussion_comments", "feature_key", type_=sa.String(64), existing_type=sa.String(80),
                    existing_nullable=True)
    op.drop_column("discussion_comments", "deleted_at")
    op.drop_column("discussion_comments", "edited_at")
    op.drop_index("ix_discussion_comments_thread_id", table_name="discussion_comments")
    op.drop_constraint("fk_discussion_comments_thread", "discussion_comments", type_="foreignkey")
    op.drop_column("discussion_comments", "thread_id")
    op.drop_table("threads")