"""Add push_subscriptions table for web push notifications."""

from alembic import op

revision = "0018"
down_revision = "0017"


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS push_subscriptions (
            id          SERIAL PRIMARY KEY,
            user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            endpoint    TEXT NOT NULL,
            p256dh      TEXT NOT NULL,
            auth        TEXT NOT NULL,
            user_agent  TEXT,
            created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            last_used   TIMESTAMP WITH TIME ZONE,
            UNIQUE(user_id, endpoint)
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_push_subscriptions_user_id ON push_subscriptions(user_id)")


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_push_subscriptions_user_id")
    op.execute("DROP TABLE IF EXISTS push_subscriptions")