CREATE TYPE "SocialPostMediaType" AS ENUM ('IMAGE', 'VIDEO');

CREATE TABLE "social_post_media" (
  "id" UUID NOT NULL,
  "post_id" UUID NOT NULL,
  "type" "SocialPostMediaType" NOT NULL,
  "storage_key" TEXT NOT NULL,
  "mime_type" VARCHAR(80) NOT NULL,
  "byte_size" INTEGER NOT NULL,
  "sort_order" SMALLINT NOT NULL DEFAULT 0,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT "social_post_media_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "social_post_media_storage_key_key"
  ON "social_post_media"("storage_key");
CREATE INDEX "social_post_media_post_id_sort_order_idx"
  ON "social_post_media"("post_id", "sort_order");

ALTER TABLE "social_post_media"
  ADD CONSTRAINT "social_post_media_post_id_fkey"
  FOREIGN KEY ("post_id") REFERENCES "social_posts"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;