255 lines
8.2 KiB
SQL
255 lines
8.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "UserRole" AS ENUM ('user', 'dev', 'com');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "BugSeverity" AS ENUM ('low', 'medium', 'high', 'critical');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "BugStatus" AS ENUM ('open', 'in_progress', 'resolved', 'closed');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "EventType" AS ENUM ('announcement', 'update', 'milestone', 'poll');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"role" "UserRole" NOT NULL DEFAULT 'user',
|
|
"isAdmin" BOOLEAN NOT NULL DEFAULT false,
|
|
"isBanned" BOOLEAN NOT NULL DEFAULT false,
|
|
"avatarUrl" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ForumCategory" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"icon" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ForumCategory_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ForumThread" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"isPinned" BOOLEAN NOT NULL DEFAULT false,
|
|
"isLocked" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"authorId" TEXT NOT NULL,
|
|
"categoryId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ForumThread_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ForumReply" (
|
|
"id" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"authorId" TEXT NOT NULL,
|
|
"threadId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ForumReply_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BugReport" (
|
|
"id" TEXT NOT NULL,
|
|
"uniqueCode" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"stepsToReproduce" TEXT NOT NULL,
|
|
"severity" "BugSeverity" NOT NULL,
|
|
"gameVersion" TEXT NOT NULL,
|
|
"screenshotUrl" TEXT,
|
|
"status" "BugStatus" NOT NULL DEFAULT 'open',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"submittedById" TEXT NOT NULL,
|
|
"assignedToId" TEXT,
|
|
|
|
CONSTRAINT "BugReport_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BugComment" (
|
|
"id" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"bugReportId" TEXT NOT NULL,
|
|
"authorId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "BugComment_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BugReportNote" (
|
|
"id" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"bugReportId" TEXT NOT NULL,
|
|
"authorId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "BugReportNote_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MeTooBug" (
|
|
"userId" TEXT NOT NULL,
|
|
"bugReportId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "MeTooBug_pkey" PRIMARY KEY ("userId","bugReportId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "StaffPost" (
|
|
"id" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"authorId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "StaffPost_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "EventPost" (
|
|
"id" TEXT NOT NULL,
|
|
"type" "EventType" NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"isPublic" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"authorId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "EventPost_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Poll" (
|
|
"id" TEXT NOT NULL,
|
|
"question" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"endsAt" TIMESTAMP(3),
|
|
"allowMultipleVotes" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"eventId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Poll_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PollOption" (
|
|
"id" TEXT NOT NULL,
|
|
"text" TEXT NOT NULL,
|
|
"pollId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "PollOption_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PollVote" (
|
|
"userId" TEXT NOT NULL,
|
|
"pollOptionId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "PollVote_pkey" PRIMARY KEY ("userId","pollOptionId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SiteSettings" (
|
|
"id" INTEGER NOT NULL DEFAULT 1,
|
|
"forumEnabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"bugsEnabled" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "SiteSettings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TeamMember" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL,
|
|
"bio" TEXT,
|
|
"avatarInitials" TEXT NOT NULL,
|
|
"twitterHandle" TEXT,
|
|
"githubHandle" TEXT,
|
|
|
|
CONSTRAINT "TeamMember_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_username_key" ON "User"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "BugReport_uniqueCode_key" ON "BugReport"("uniqueCode");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Poll_eventId_key" ON "Poll"("eventId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForumThread" ADD CONSTRAINT "ForumThread_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForumThread" ADD CONSTRAINT "ForumThread_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "ForumCategory"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForumReply" ADD CONSTRAINT "ForumReply_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForumReply" ADD CONSTRAINT "ForumReply_threadId_fkey" FOREIGN KEY ("threadId") REFERENCES "ForumThread"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "BugReport" ADD CONSTRAINT "BugReport_submittedById_fkey" FOREIGN KEY ("submittedById") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "BugReport" ADD CONSTRAINT "BugReport_assignedToId_fkey" FOREIGN KEY ("assignedToId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "BugComment" ADD CONSTRAINT "BugComment_bugReportId_fkey" FOREIGN KEY ("bugReportId") REFERENCES "BugReport"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "BugComment" ADD CONSTRAINT "BugComment_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "BugReportNote" ADD CONSTRAINT "BugReportNote_bugReportId_fkey" FOREIGN KEY ("bugReportId") REFERENCES "BugReport"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "BugReportNote" ADD CONSTRAINT "BugReportNote_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MeTooBug" ADD CONSTRAINT "MeTooBug_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MeTooBug" ADD CONSTRAINT "MeTooBug_bugReportId_fkey" FOREIGN KEY ("bugReportId") REFERENCES "BugReport"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StaffPost" ADD CONSTRAINT "StaffPost_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "EventPost" ADD CONSTRAINT "EventPost_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Poll" ADD CONSTRAINT "Poll_eventId_fkey" FOREIGN KEY ("eventId") REFERENCES "EventPost"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PollOption" ADD CONSTRAINT "PollOption_pollId_fkey" FOREIGN KEY ("pollId") REFERENCES "Poll"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PollVote" ADD CONSTRAINT "PollVote_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PollVote" ADD CONSTRAINT "PollVote_pollOptionId_fkey" FOREIGN KEY ("pollOptionId") REFERENCES "PollOption"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|