44 lines
1.8 KiB
SQL
44 lines
1.8 KiB
SQL
CREATE TYPE "UserRole" AS ENUM ('admin', 'user');
|
|
CREATE TYPE "SupportConversationStatus" AS ENUM ('open', 'closed');
|
|
|
|
CREATE TABLE "SupportConversation" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"userEmail" TEXT NOT NULL,
|
|
"assignedAdminId" TEXT,
|
|
"assignedAdminEmail" TEXT,
|
|
"status" "SupportConversationStatus" NOT NULL DEFAULT 'open',
|
|
"userLastReadAt" TIMESTAMP(3),
|
|
"adminLastReadAt" TIMESTAMP(3),
|
|
"lastMessageAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"lastUserMessageAt" TIMESTAMP(3),
|
|
"lastAdminMessageAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SupportConversation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "SupportMessage" (
|
|
"id" TEXT NOT NULL,
|
|
"conversationId" TEXT NOT NULL,
|
|
"authorId" TEXT NOT NULL,
|
|
"authorEmail" TEXT NOT NULL,
|
|
"authorRole" "UserRole" NOT NULL,
|
|
"body" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SupportMessage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "SupportConversation_userId_key" ON "SupportConversation"("userId");
|
|
CREATE INDEX "SupportConversation_status_lastMessageAt_idx" ON "SupportConversation"("status", "lastMessageAt");
|
|
CREATE INDEX "SupportConversation_assignedAdminId_idx" ON "SupportConversation"("assignedAdminId");
|
|
CREATE INDEX "SupportMessage_conversationId_createdAt_idx" ON "SupportMessage"("conversationId", "createdAt");
|
|
CREATE INDEX "SupportMessage_authorId_createdAt_idx" ON "SupportMessage"("authorId", "createdAt");
|
|
|
|
ALTER TABLE "SupportMessage"
|
|
ADD CONSTRAINT "SupportMessage_conversationId_fkey"
|
|
FOREIGN KEY ("conversationId") REFERENCES "SupportConversation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|