CREATE TABLE IF NOT EXISTS "system_notifications" (
  "id" text PRIMARY KEY DEFAULT gen_random_uuid()::text NOT NULL,
  "user_id" text NOT NULL,
  "kind" text NOT NULL,
  "title" text NOT NULL,
  "sub" text NOT NULL,
  "when_label" text NOT NULL,
  "href" text,
  "source" text DEFAULT 'system' NOT NULL,
  "source_ref" jsonb DEFAULT '{}'::jsonb NOT NULL,
  "read_at" timestamp with time zone,
  "occurred_at" timestamp with time zone DEFAULT now() NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "system_notifications_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action,
  CONSTRAINT "system_notifications_kind_check" CHECK ("kind" IN ('session', 'billing', 'security', 'feature', 'account'))
);

CREATE INDEX IF NOT EXISTS "system_notifications_user_idx" ON "system_notifications" USING btree ("user_id","read_at","occurred_at");
CREATE INDEX IF NOT EXISTS "system_notifications_kind_idx" ON "system_notifications" USING btree ("user_id","kind","occurred_at");

CREATE TABLE IF NOT EXISTS "system_notification_preferences" (
  "user_id" text NOT NULL,
  "kind" text NOT NULL,
  "in_app" boolean DEFAULT true NOT NULL,
  "email" boolean DEFAULT true NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "system_notification_preferences_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action,
  CONSTRAINT "system_notification_preferences_pk" PRIMARY KEY("user_id","kind"),
  CONSTRAINT "system_notification_preferences_kind_check" CHECK ("kind" IN ('session', 'billing', 'security', 'feature', 'account'))
);

CREATE INDEX IF NOT EXISTS "system_notification_preferences_user_idx" ON "system_notification_preferences" USING btree ("user_id");