213 lines
6.6 KiB
SQL
213 lines
6.6 KiB
SQL
|
-- CreateEnum
|
||
|
|
CREATE TYPE "Role" AS ENUM ('ADMIN', 'EDITOR');
|
||
|
|
|
||
|
|
-- CreateEnum
|
||
|
|
CREATE TYPE "MessageStatus" AS ENUM ('NEW', 'READ', 'ARCHIVED');
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "AdminUser" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"email" TEXT NOT NULL,
|
||
|
|
"name" TEXT NOT NULL,
|
||
|
|
"passwordHash" TEXT NOT NULL,
|
||
|
|
"role" "Role" NOT NULL DEFAULT 'EDITOR',
|
||
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"lastLoginAt" TIMESTAMP(3),
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "AdminUser_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "RefreshToken" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"userId" INTEGER NOT NULL,
|
||
|
|
"tokenHash" TEXT NOT NULL,
|
||
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
"revokedAt" TIMESTAMP(3),
|
||
|
|
"userAgent" TEXT NOT NULL DEFAULT '',
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "RefreshToken_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "Media" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"key" TEXT NOT NULL,
|
||
|
|
"mime" TEXT NOT NULL,
|
||
|
|
"size" INTEGER NOT NULL,
|
||
|
|
"width" INTEGER,
|
||
|
|
"height" INTEGER,
|
||
|
|
"alt" TEXT NOT NULL DEFAULT '',
|
||
|
|
"originalName" TEXT NOT NULL DEFAULT '',
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "Media_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "Category" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"slug" TEXT NOT NULL,
|
||
|
|
"code" TEXT NOT NULL,
|
||
|
|
"name" TEXT NOT NULL,
|
||
|
|
"nameEn" TEXT NOT NULL DEFAULT '',
|
||
|
|
"shortDesc" TEXT NOT NULL DEFAULT '',
|
||
|
|
"fullDesc" TEXT NOT NULL DEFAULT '',
|
||
|
|
"icon" TEXT NOT NULL DEFAULT '',
|
||
|
|
"coverImageId" INTEGER,
|
||
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
||
|
|
"isPublished" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"seoTitle" TEXT NOT NULL DEFAULT '',
|
||
|
|
"seoDescription" TEXT NOT NULL DEFAULT '',
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "Category_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "ProductGroup" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"categoryId" INTEGER NOT NULL,
|
||
|
|
"slug" TEXT NOT NULL,
|
||
|
|
"name" TEXT NOT NULL,
|
||
|
|
"desc" TEXT NOT NULL DEFAULT '',
|
||
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
||
|
|
|
||
|
|
CONSTRAINT "ProductGroup_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "Product" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"categoryId" INTEGER NOT NULL,
|
||
|
|
"groupId" INTEGER,
|
||
|
|
"code" TEXT NOT NULL,
|
||
|
|
"slug" TEXT NOT NULL,
|
||
|
|
"name" TEXT NOT NULL,
|
||
|
|
"nameEn" TEXT NOT NULL DEFAULT '',
|
||
|
|
"desc" TEXT NOT NULL DEFAULT '',
|
||
|
|
"specs" TEXT[],
|
||
|
|
"variants" TEXT[],
|
||
|
|
"imageId" INTEGER,
|
||
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
||
|
|
"isPublished" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "Product_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "ProductImage" (
|
||
|
|
"productId" INTEGER NOT NULL,
|
||
|
|
"mediaId" INTEGER NOT NULL,
|
||
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
||
|
|
|
||
|
|
CONSTRAINT "ProductImage_pkey" PRIMARY KEY ("productId","mediaId")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "SiteSection" (
|
||
|
|
"key" TEXT NOT NULL,
|
||
|
|
"data" JSONB NOT NULL,
|
||
|
|
"updatedById" INTEGER,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "SiteSection_pkey" PRIMARY KEY ("key")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "ContactMessage" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"name" TEXT NOT NULL,
|
||
|
|
"org" TEXT NOT NULL,
|
||
|
|
"phone" TEXT NOT NULL,
|
||
|
|
"message" TEXT NOT NULL DEFAULT '',
|
||
|
|
"status" "MessageStatus" NOT NULL DEFAULT 'NEW',
|
||
|
|
"ip" TEXT NOT NULL DEFAULT '',
|
||
|
|
"userAgent" TEXT NOT NULL DEFAULT '',
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "ContactMessage_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "AuditLog" (
|
||
|
|
"id" SERIAL NOT NULL,
|
||
|
|
"userId" INTEGER,
|
||
|
|
"action" TEXT NOT NULL,
|
||
|
|
"entity" TEXT NOT NULL,
|
||
|
|
"entityId" TEXT NOT NULL DEFAULT '',
|
||
|
|
"summary" TEXT NOT NULL DEFAULT '',
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "AuditLog_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "AdminUser_email_key" ON "AdminUser"("email");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "RefreshToken_tokenHash_key" ON "RefreshToken"("tokenHash");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "RefreshToken_userId_idx" ON "RefreshToken"("userId");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "Media_key_key" ON "Media"("key");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "Category_slug_key" ON "Category"("slug");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "Category_sortOrder_idx" ON "Category"("sortOrder");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "ProductGroup_categoryId_slug_key" ON "ProductGroup"("categoryId", "slug");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "Product_code_key" ON "Product"("code");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "Product_slug_key" ON "Product"("slug");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "Product_categoryId_sortOrder_idx" ON "Product"("categoryId", "sortOrder");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "ContactMessage_status_createdAt_idx" ON "ContactMessage"("status", "createdAt");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "AuditLog_createdAt_idx" ON "AuditLog"("createdAt");
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "RefreshToken" ADD CONSTRAINT "RefreshToken_userId_fkey" FOREIGN KEY ("userId") REFERENCES "AdminUser"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "Category" ADD CONSTRAINT "Category_coverImageId_fkey" FOREIGN KEY ("coverImageId") REFERENCES "Media"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "ProductGroup" ADD CONSTRAINT "ProductGroup_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "Product" ADD CONSTRAINT "Product_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "Product" ADD CONSTRAINT "Product_groupId_fkey" FOREIGN KEY ("groupId") REFERENCES "ProductGroup"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "Product" ADD CONSTRAINT "Product_imageId_fkey" FOREIGN KEY ("imageId") REFERENCES "Media"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "ProductImage" ADD CONSTRAINT "ProductImage_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "ProductImage" ADD CONSTRAINT "ProductImage_mediaId_fkey" FOREIGN KEY ("mediaId") REFERENCES "Media"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "AuditLog" ADD CONSTRAINT "AuditLog_userId_fkey" FOREIGN KEY ("userId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|