CREATE TYPE "CartableNotificationType" AS ENUM (
  'SOCIAL_LIKE',
  'SOCIAL_COMMENT',
  'SOCIAL_FOLLOW'
);

CREATE TABLE "cartable_notifications" (
  "id" UUID NOT NULL,
  "recipient_id" UUID NOT NULL,
  "actor_id" UUID NOT NULL,
  "type" "CartableNotificationType" NOT NULL,
  "event_key" VARCHAR(220) NOT NULL,
  "post_id" UUID,
  "comment_id" UUID,
  "read_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "cartable_notifications_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "cartable_notifications_recipient_id_fkey"
    FOREIGN KEY ("recipient_id") REFERENCES "identity_users"("id")
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "cartable_notifications_actor_id_fkey"
    FOREIGN KEY ("actor_id") REFERENCES "identity_users"("id")
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "cartable_notifications_post_id_fkey"
    FOREIGN KEY ("post_id") REFERENCES "social_posts"("id")
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "cartable_notifications_comment_id_fkey"
    FOREIGN KEY ("comment_id") REFERENCES "social_comments"("id")
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE UNIQUE INDEX "cartable_notifications_event_key_key"
  ON "cartable_notifications"("event_key");
CREATE INDEX "cartable_notifications_recipient_id_read_at_created_at_idx"
  ON "cartable_notifications"("recipient_id", "read_at", "created_at");
CREATE INDEX "cartable_notifications_recipient_id_type_created_at_idx"
  ON "cartable_notifications"("recipient_id", "type", "created_at");
CREATE INDEX "cartable_notifications_post_id_idx"
  ON "cartable_notifications"("post_id");