"""add tenant_isolation policy on conversation_verification table

`conversation_verification` rows hold email OTP challenges for unverified
guests. The table has RLS enabled (Neon auto-enabled it across the schema
when "Auth (Neon RLS)" was switched on) but no policy ever existed for it,
because migration 0030's `TENANT_TABLES` only covers tables with a direct
`restaurant_id` column — this one keys off `conversation_id` instead.

Right now the table is NEVER queried under the `authenticated` role: the
agent's verification tools (`send_verification_code_impl`,
`verify_code_impl`) are gated by `caller.channel != "dashboard"`
(`backend/app/agents/restaurant.py`), and the two channels that DO enable
them — public chat and inbound WhatsApp/Telegram via Restate — use owner-
pool sessions (BYPASSRLS). So the default-deny state isn't biting today.

But it's one one-line gate change away from being a silent
`StaleDataError` (UPDATE) or empty-result (SELECT) on every verification
attempt — the same trap that hit `restaurant` in migration 0032. Pre-
emptively adding the policy means the gate can move without taking RLS
with it.

The policy is JOIN-based against `conversation` because there is no
denormalised `restaurant_id` on this table. The indexed lookup is
negligible: the table is read ≤ 3 times per chat session and the agent
caches the verification state in memory after the first hit. If/when
write traffic justifies it, the right follow-up is to mirror the
`0022_add_restaurant_id_to_indirect_tables` pattern and denormalise.

Revision ID: 0033_conversation_verification_rls_policy
Revises: 0032_restaurant_rls_policy
Create Date: 2026-05-26 22:45:00.000000
"""

from collections.abc import Sequence

from alembic import op

revision: str = "0033_conversation_verification_rls_policy"
down_revision: str | None = "0032_restaurant_rls_policy"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.execute('ALTER TABLE "conversation_verification" ENABLE ROW LEVEL SECURITY')
    op.execute('ALTER TABLE "conversation_verification" FORCE ROW LEVEL SECURITY')
    op.execute('DROP POLICY IF EXISTS tenant_isolation ON "conversation_verification"')
    op.execute(
        'CREATE POLICY tenant_isolation ON "conversation_verification" '
        "FOR ALL "
        "USING ("
        "    EXISTS ("
        '        SELECT 1 FROM "conversation" c '
        "        WHERE c.id = conversation_verification.conversation_id "
        "          AND c.restaurant_id = (auth.session() ->> 'activeRestaurantId')"
        "    )"
        ") "
        "WITH CHECK ("
        "    EXISTS ("
        '        SELECT 1 FROM "conversation" c '
        "        WHERE c.id = conversation_verification.conversation_id "
        "          AND c.restaurant_id = (auth.session() ->> 'activeRestaurantId')"
        "    )"
        ")"
    )


def downgrade() -> None:
    op.execute('DROP POLICY IF EXISTS tenant_isolation ON "conversation_verification"')