-- Web Push subscriptions (browser Notifications API + service worker).
-- Separate from DeviceToken which holds mobile FCM/APNs tokens.

CREATE TABLE IF NOT EXISTS "PushSubscription" (
    "id"         TEXT NOT NULL,
    "ownerId"    TEXT NOT NULL,
    "adminId"    TEXT,
    "approverId" TEXT,
    "endpoint"   TEXT NOT NULL,
    "p256dh"     TEXT NOT NULL,
    "auth"       TEXT NOT NULL,
    "userAgent"  TEXT,
    "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"  TIMESTAMP(3) NOT NULL,
    CONSTRAINT "PushSubscription_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX IF NOT EXISTS "PushSubscription_endpoint_key" ON "PushSubscription"("endpoint");
CREATE INDEX IF NOT EXISTS "PushSubscription_ownerId_idx"    ON "PushSubscription"("ownerId");
CREATE INDEX IF NOT EXISTS "PushSubscription_adminId_idx"    ON "PushSubscription"("adminId");
CREATE INDEX IF NOT EXISTS "PushSubscription_approverId_idx" ON "PushSubscription"("approverId");
