"""add public.push_subscriptions for Web Push

Revision ID: psh001
Revises: chk001
Create Date: 2026-06-30
"""
from alembic import op
import sqlalchemy as sa

revision = 'psh001'
down_revision = 'chk001'
branch_labels = None
depends_on = None


def _public_table_exists(conn, table):
    return conn.execute(sa.text(
        "SELECT 1 FROM information_schema.tables "
        "WHERE table_schema='public' AND table_name=:t"
    ), {"t": table}).fetchone() is not None


def upgrade():
    conn = op.get_bind()
    if _public_table_exists(conn, 'push_subscriptions'):
        return
    op.create_table(
        'push_subscriptions',
        sa.Column('id', sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('endpoint', sa.Text(), nullable=False),
        sa.Column('p256dh', sa.Text(), nullable=False),
        sa.Column('auth', sa.Text(), nullable=False),
        sa.Column('user_agent', sa.String(255), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False,
                  server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.ForeignKeyConstraint(['user_id'], ['public.users.id'], ondelete='CASCADE'),
        sa.UniqueConstraint('endpoint', name='uq_push_subscriptions_endpoint'),
        schema='public',
    )
    op.create_index(
        'ix_push_subscriptions_user_id', 'push_subscriptions', ['user_id'],
        schema='public',
    )


def downgrade():
    op.drop_index('ix_push_subscriptions_user_id', table_name='push_subscriptions', schema='public')
    op.drop_table('push_subscriptions', schema='public')