-- Support chat attachments.
--
-- `SupportMessage.text` becomes nullable so an attachment-only message can say
-- "there is no text" instead of smuggling that through an empty string. This is
-- a catalog-only change in Postgres — no table rewrite, no long lock.
ALTER TABLE "SupportMessage" ALTER COLUMN "text" DROP NOT NULL;

-- The `attachments` JSONB column has been dead since the init migration: never
-- written, never read, absent from every DTO. It is dropped rather than reused —
-- an untyped blob gives no file identity, no size accounting, and nothing to
-- cascade on delete. Real rows replace it below. (The name is also needed for
-- the new relation.)
ALTER TABLE "SupportMessage" DROP COLUMN "attachments";

-- Backs keyset pagination of a thread (newest-first over (createdAt, id)).
-- Without it, paging a long thread degrades to a scan per page.
-- CreateIndex
CREATE INDEX "SupportMessage_conversationId_createdAt_idx" ON "SupportMessage"("conversationId", "createdAt");

-- CreateTable
CREATE TABLE "SupportAttachment" (
    "id" TEXT NOT NULL,
    "messageId" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "mimeType" TEXT NOT NULL,
    "size" INTEGER NOT NULL,
    "url" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE INDEX "SupportAttachment_messageId_idx" ON "SupportAttachment"("messageId");

-- AddForeignKey
-- CASCADE: an attachment has no meaning without its message. (Object bytes in
-- MinIO are not reaped by this — deleting messages is not a flow that exists.)
ALTER TABLE "SupportAttachment" ADD CONSTRAINT "SupportAttachment_messageId_fkey" FOREIGN KEY ("messageId") REFERENCES "SupportMessage"("id") ON DELETE CASCADE ON UPDATE CASCADE;
