BEGIN;

CREATE TABLE IF NOT EXISTS public.auth_challenge (
  id BIGSERIAL PRIMARY KEY,
  target VARCHAR(190) NOT NULL,
  purpose VARCHAR(32) NOT NULL,
  channel VARCHAR(16) NOT NULL,
  "codeHash" VARCHAR(100) NOT NULL,
  "siteId" BIGINT NULL,
  attempts INTEGER NOT NULL DEFAULT 0,
  "maxAttempts" INTEGER NOT NULL DEFAULT 5,
  "expiresAt" TIMESTAMP NOT NULL,
  "consumedAt" TIMESTAMP NULL,
  "requestedIp" VARCHAR(128) NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "IDX_auth_challenge_target_purpose_created"
  ON public.auth_challenge (target, purpose, created DESC);
CREATE INDEX IF NOT EXISTS "IDX_auth_challenge_expires"
  ON public.auth_challenge ("expiresAt");

ALTER TABLE public.auth_challenge
  DROP CONSTRAINT IF EXISTS "CHK_auth_challenge_purpose";
ALTER TABLE public.auth_challenge
  ADD CONSTRAINT "CHK_auth_challenge_purpose"
  CHECK (purpose IN ('login', 'register', 'password_reset'));
ALTER TABLE public.auth_challenge
  DROP CONSTRAINT IF EXISTS "CHK_auth_challenge_channel";
ALTER TABLE public.auth_challenge
  ADD CONSTRAINT "CHK_auth_challenge_channel"
  CHECK (channel IN ('email', 'sms'));

CREATE TABLE IF NOT EXISTS public.notification (
  id BIGSERIAL PRIMARY KEY,
  "userId" BIGINT NOT NULL,
  "siteId" BIGINT NULL,
  type VARCHAR(64) NOT NULL DEFAULT 'system',
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  link VARCHAR(500) NULL,
  "dedupeKey" VARCHAR(250) NULL,
  "readAt" TIMESTAMP NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "FK_notification_user"
    FOREIGN KEY ("userId") REFERENCES public."user"(id) ON DELETE CASCADE,
  CONSTRAINT "FK_notification_site"
    FOREIGN KEY ("siteId") REFERENCES public.site(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "IDX_notification_user_created"
  ON public.notification ("userId", created DESC);
CREATE INDEX IF NOT EXISTS "IDX_notification_user_unread"
  ON public.notification ("userId", "readAt");
CREATE UNIQUE INDEX IF NOT EXISTS "UQ_notification_dedupe"
  ON public.notification ("dedupeKey") WHERE "dedupeKey" IS NOT NULL;

COMMIT;