"""Create the `notifications` table backing the admin notification bell.

One row per in-app notification. `recipient_scope` is the primary
discriminator, not `tenant_id`, because platform notifications (for site_admin
users, who have no tenant at all) must be storable with `tenant_id = NULL`.
That is also why this table does NOT get the FORCE ROW LEVEL SECURITY policy
the tenant tables carry — a NULL tenant_id could never satisfy a
current_setting('app.tenant') predicate. (Those policies are inert in this
codebase anyway: nothing in src/ ever calls set_config('app.tenant', ...).)
Scope isolation is enforced by NotificationService._scope_filters().

`user_id` is nullable and means "every admin in this scope" when NULL. It is
created now so per-user targeting can be added later without a migration.

Revision ID: 0081
Revises: 0080
"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision = "0081"
down_revision = "0080"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "notifications",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
            nullable=False,
        ),
        sa.Column("recipient_scope", sa.String(length=20), nullable=False),
        sa.Column("tenant_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("user_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("type", sa.String(length=50), nullable=False),
        sa.Column("title", sa.String(length=200), nullable=False),
        sa.Column("body", sa.Text(), nullable=True),
        sa.Column("entity_type", sa.String(length=50), nullable=True),
        sa.Column("entity_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("link", sa.String(length=500), nullable=True),
        sa.Column("read_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("NOW()"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("NOW()"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["tenant_id"], ["accounts.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        # A tenant row must carry a tenant_id and a platform row must not —
        # enforced in the database so a bug in the service layer cannot leak a
        # tenant's notification into the site-admin bell.
        sa.CheckConstraint(
            "(recipient_scope = 'tenant' AND tenant_id IS NOT NULL) "
            "OR (recipient_scope = 'platform' AND tenant_id IS NULL)",
            name="ck_notifications_scope_tenant_consistency",
        ),
    )

    op.create_index("ix_notifications_tenant_id", "notifications", ["tenant_id"])
    # Badge count + newest-first list, for both scopes.
    op.create_index(
        "idx_notifications_scope_tenant_read_created",
        "notifications",
        ["recipient_scope", "tenant_id", "read_at", "created_at"],
    )
    # Lookup / dedupe by originating enquiry.
    op.create_index(
        "idx_notifications_entity", "notifications", ["entity_type", "entity_id"]
    )


def downgrade() -> None:
    op.drop_index("idx_notifications_entity", table_name="notifications")
    op.drop_index("idx_notifications_scope_tenant_read_created", table_name="notifications")
    op.drop_index("ix_notifications_tenant_id", table_name="notifications")
    op.drop_table("notifications")