CREATE TABLE "entity_documents" (
    "id" UUID NOT NULL,
    "entity_type" VARCHAR(50) NOT NULL,
    "property_id" UUID,
    "search_request_id" UUID,
    "uploaded_by_id" UUID,
    "file_name" VARCHAR(512) NOT NULL,
    "storage_key" VARCHAR(1024) NOT NULL,
    "mime_type" VARCHAR(255),
    "size_bytes" BIGINT NOT NULL,
    "kind" VARCHAR(100),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

ALTER TABLE "entity_documents" ADD CONSTRAINT "entity_documents_property_id_fkey"
  FOREIGN KEY ("property_id") REFERENCES "properties"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "entity_documents" ADD CONSTRAINT "entity_documents_search_request_id_fkey"
  FOREIGN KEY ("search_request_id") REFERENCES "search_requests"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "entity_documents" ADD CONSTRAINT "entity_documents_uploaded_by_id_fkey"
  FOREIGN KEY ("uploaded_by_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE INDEX "entity_documents_property_id_idx" ON "entity_documents"("property_id");
CREATE INDEX "entity_documents_search_request_id_idx" ON "entity_documents"("search_request_id");