-- Task 40: push_subscriptions table
CREATE TABLE "push_subscriptions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
  "endpoint" text NOT NULL UNIQUE,
  "p256dh" text NOT NULL,
  "auth" text NOT NULL,
  "user_agent" text,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX "push_subscriptions_user_idx" ON "push_subscriptions" ("user_id");

-- Task 42: notifications table
CREATE TABLE "notifications" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
  "title" text NOT NULL,
  "body" text NOT NULL,
  "url" text,
  "read_at" timestamp with time zone,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX "notifications_user_read_idx" ON "notifications" ("user_id", "read_at");

-- Task 42: per-user notification channel preferences
ALTER TABLE "users"
  ADD COLUMN "notif_push" boolean NOT NULL DEFAULT true,
  ADD COLUMN "notif_inapp" boolean NOT NULL DEFAULT true,
  ADD COLUMN "notif_ntfy" boolean NOT NULL DEFAULT false;

-- Task 41: add fired_at and created_by to reminders
ALTER TABLE "reminders"
  ADD COLUMN "fired_at" timestamp with time zone,
  ADD COLUMN "created_by" uuid REFERENCES "users"("id") ON DELETE SET NULL;

-- Task 41: update default channel value to 'auto'
UPDATE "reminders" SET "channel" = 'auto' WHERE "channel" = 'in_app';