239 lines
7.2 KiB
SQL
239 lines
7.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "UserRole" AS ENUM ('User', 'Admin', 'Agent', 'superAdmin');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "OrderStatus" AS ENUM ('pending', 'confirmed', 'preparing', 'shipped', 'delivered', 'cancelled');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RepresentationStatus" AS ENUM ('pending', 'active', 'rejected');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ReviewStatus" AS ENUM ('pending', 'approved', 'rejected');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SmsOtpPurpose" AS ENUM ('register', 'login', 'resetPassword');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"firstName" TEXT NOT NULL,
|
|
"lastName" TEXT NOT NULL,
|
|
"cellNumber" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"city" TEXT NOT NULL DEFAULT '',
|
|
"role" "UserRole" NOT NULL DEFAULT 'User',
|
|
"disabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"ordersCount" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "RefreshToken" (
|
|
"id" TEXT NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "RefreshToken_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SmsOtp" (
|
|
"id" TEXT NOT NULL,
|
|
"cellNumber" TEXT NOT NULL,
|
|
"purpose" "SmsOtpPurpose" NOT NULL,
|
|
"codeHash" TEXT NOT NULL,
|
|
"payload" JSONB,
|
|
"attempts" INTEGER NOT NULL DEFAULT 0,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "SmsOtp_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Product" (
|
|
"id" TEXT NOT NULL,
|
|
"nameFa" TEXT NOT NULL,
|
|
"nameEn" TEXT NOT NULL DEFAULT '',
|
|
"price" INTEGER NOT NULL DEFAULT 0,
|
|
"stock" INTEGER NOT NULL DEFAULT 0,
|
|
"category" TEXT NOT NULL DEFAULT '',
|
|
"imageUrl" TEXT,
|
|
"imageKey" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Product_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Order" (
|
|
"id" SERIAL NOT NULL,
|
|
"customerId" TEXT,
|
|
"customerName" TEXT NOT NULL,
|
|
"customerPhone" TEXT NOT NULL,
|
|
"status" "OrderStatus" NOT NULL DEFAULT 'pending',
|
|
"itemCount" INTEGER NOT NULL DEFAULT 0,
|
|
"note" TEXT NOT NULL DEFAULT '',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Order_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OrderItem" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" INTEGER NOT NULL,
|
|
"productId" TEXT,
|
|
"nameFa" TEXT NOT NULL,
|
|
"quantity" INTEGER NOT NULL DEFAULT 1,
|
|
"unitPrice" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "OrderItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "BlogPost" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"summary" TEXT NOT NULL DEFAULT '',
|
|
"body" TEXT NOT NULL DEFAULT '',
|
|
"author" TEXT NOT NULL DEFAULT '',
|
|
"imageUrl" TEXT,
|
|
"imageKey" TEXT,
|
|
"published" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "BlogPost_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Representation" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"owner" TEXT NOT NULL DEFAULT '',
|
|
"mobile" TEXT NOT NULL,
|
|
"city" TEXT NOT NULL,
|
|
"address" TEXT NOT NULL DEFAULT '',
|
|
"status" "RepresentationStatus" NOT NULL DEFAULT 'pending',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Representation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Review" (
|
|
"id" TEXT NOT NULL,
|
|
"authorId" TEXT,
|
|
"author" TEXT NOT NULL,
|
|
"product" TEXT NOT NULL,
|
|
"rating" INTEGER NOT NULL,
|
|
"body" TEXT NOT NULL DEFAULT '',
|
|
"status" "ReviewStatus" NOT NULL DEFAULT 'pending',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Review_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CallRequest" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"mobile" TEXT NOT NULL,
|
|
"note" TEXT NOT NULL DEFAULT '',
|
|
"handled" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "CallRequest_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_cellNumber_key" ON "User"("cellNumber");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_role_idx" ON "User"("role");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_disabled_idx" ON "User"("disabled");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RefreshToken_userId_idx" ON "RefreshToken"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "RefreshToken_tokenHash_idx" ON "RefreshToken"("tokenHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SmsOtp_cellNumber_purpose_idx" ON "SmsOtp"("cellNumber", "purpose");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SmsOtp_expiresAt_idx" ON "SmsOtp"("expiresAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Product_category_idx" ON "Product"("category");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Product_price_idx" ON "Product"("price");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_customerId_idx" ON "Order"("customerId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_status_idx" ON "Order"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_createdAt_idx" ON "Order"("createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrderItem_orderId_idx" ON "OrderItem"("orderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrderItem_productId_idx" ON "OrderItem"("productId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Representation_status_idx" ON "Representation"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Representation_userId_idx" ON "Representation"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Review_status_idx" ON "Review"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CallRequest_handled_idx" ON "CallRequest"("handled");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CallRequest_createdAt_idx" ON "CallRequest"("createdAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "RefreshToken" ADD CONSTRAINT "RefreshToken_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderItem" ADD CONSTRAINT "OrderItem_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderItem" ADD CONSTRAINT "OrderItem_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Representation" ADD CONSTRAINT "Representation_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Review" ADD CONSTRAINT "Review_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CallRequest" ADD CONSTRAINT "CallRequest_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|