232 lines
7.2 KiB
SQL
232 lines
7.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "Role" AS ENUM ('ADMIN', 'USER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RegistrationStatus" AS ENUM ('CONFIRMED', 'PAYMENT_PENDING', 'PAID', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "WebinarVisibility" AS ENUM ('PUBLIC', 'PRIVATE');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
"email" TEXT NOT NULL,
|
|
"emailVerified" BOOLEAN NOT NULL DEFAULT false,
|
|
"image" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"role" "Role" NOT NULL DEFAULT 'USER',
|
|
"firstName" TEXT,
|
|
"lastName" TEXT,
|
|
"gender" TEXT,
|
|
"dob" TIMESTAMP(3),
|
|
"address" TEXT,
|
|
"forcePasswordReset" BOOLEAN NOT NULL DEFAULT false,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Account" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL DEFAULT 'oauth',
|
|
"provider" TEXT NOT NULL,
|
|
"providerAccountId" TEXT NOT NULL,
|
|
"refreshToken" TEXT,
|
|
"accessToken" TEXT,
|
|
"expiresAt" INTEGER,
|
|
"tokenType" TEXT,
|
|
"scope" TEXT,
|
|
"idToken" TEXT,
|
|
"sessionState" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Account_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Credential" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Credential_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Session" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionToken" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"expires" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Session_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Verification" (
|
|
"id" TEXT NOT NULL,
|
|
"identifier" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"userId" TEXT,
|
|
|
|
CONSTRAINT "Verification_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SystemConfig" (
|
|
"id" INTEGER NOT NULL,
|
|
"data" JSONB NOT NULL DEFAULT '{}',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SystemConfig_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AppSetup" (
|
|
"id" INTEGER NOT NULL,
|
|
"googleAuthEnabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"googleClientId" TEXT,
|
|
"googleClientSecret" TEXT,
|
|
"socials" JSONB NOT NULL DEFAULT '{}',
|
|
"categories" JSONB NOT NULL DEFAULT '[]',
|
|
"paginationItemsPerPage" INTEGER NOT NULL DEFAULT 10,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "AppSetup_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Webinar" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"speaker" TEXT NOT NULL,
|
|
"startAt" TIMESTAMP(3) NOT NULL,
|
|
"duration" INTEGER NOT NULL,
|
|
"bannerUrl" TEXT,
|
|
"category" TEXT NOT NULL,
|
|
"visibility" "WebinarVisibility" NOT NULL DEFAULT 'PUBLIC',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"capacity" INTEGER NOT NULL,
|
|
"priceCents" INTEGER NOT NULL DEFAULT 0,
|
|
"meetingInfo" JSONB NOT NULL DEFAULT '{}',
|
|
"learningPoints" JSONB NOT NULL DEFAULT '[]',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Webinar_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WebinarRegistration" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"webinarId" TEXT NOT NULL,
|
|
"status" "RegistrationStatus" NOT NULL DEFAULT 'CONFIRMED',
|
|
"stripeCheckoutSessionId" TEXT,
|
|
"stripePaymentIntentId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "WebinarRegistration_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ContactMessage" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"subject" TEXT NOT NULL,
|
|
"message" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'NEW',
|
|
"adminNote" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"authorId" TEXT,
|
|
|
|
CONSTRAINT "ContactMessage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Account_userId_idx" ON "Account"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Credential_userId_key" ON "Credential"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Credential_userId_idx" ON "Credential"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Session_sessionToken_key" ON "Session"("sessionToken");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Session_userId_idx" ON "Session"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Verification_userId_idx" ON "Verification"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Verification_identifier_value_key" ON "Verification"("identifier", "value");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Webinar_visibility_idx" ON "Webinar"("visibility");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Webinar_isActive_idx" ON "Webinar"("isActive");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "WebinarRegistration_userId_idx" ON "WebinarRegistration"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "WebinarRegistration_webinarId_idx" ON "WebinarRegistration"("webinarId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "WebinarRegistration_userId_webinarId_key" ON "WebinarRegistration"("userId", "webinarId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ContactMessage_status_idx" ON "ContactMessage"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ContactMessage_createdAt_idx" ON "ContactMessage"("createdAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Account" ADD CONSTRAINT "Account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Credential" ADD CONSTRAINT "Credential_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Verification" ADD CONSTRAINT "Verification_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "WebinarRegistration" ADD CONSTRAINT "WebinarRegistration_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "WebinarRegistration" ADD CONSTRAINT "WebinarRegistration_webinarId_fkey" FOREIGN KEY ("webinarId") REFERENCES "Webinar"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ContactMessage" ADD CONSTRAINT "ContactMessage_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|