397 lines
16 KiB
SQL
397 lines
16 KiB
SQL
|
-- Additive migration: 0001_init created the original gold-bank tables.
|
||
|
|
-- The admin-panel port later added columns/enums/tables to schema.prisma
|
||
|
|
-- without a SQL migration, which produced P2022 on production
|
||
|
|
-- ("The column Product.productCode does not exist").
|
||
|
|
--
|
||
|
|
-- Statements are idempotent so a database that was already brought up to date
|
||
|
|
-- with `prisma db push` still records this file as applied.
|
||
|
|
|
||
|
|
-- ── Enums ────────────────────────────────────────────────────────────────
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
CREATE TYPE "CustomerKind" AS ENUM ('SALES_AGENT', 'APP_SHOPKEEPER', 'WALK_IN_SHOPKEEPER', 'BANKER', 'WORKSHOP_SUPERVISOR');
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
CREATE TYPE "OrderChannel" AS ENUM ('RETAIL', 'WHOLESALE');
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
CREATE TYPE "CommentStatus" AS ENUM ('PENDING', 'APPROVED', 'REJECTED');
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
CREATE TYPE "PromotionScope" AS ENUM ('PRODUCT', 'CATEGORY', 'CUSTOMER', 'BRAND');
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
CREATE TYPE "PromotionUsage" AS ENUM ('ONCE', 'UNTIL_END_DATE');
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
CREATE TYPE "MessageAudience" AS ENUM ('SINGLE_NUMBER', 'MULTIPLE_NUMBERS', 'ALL_CUSTOMERS', 'WHOLESALE_CUSTOMERS', 'RETAIL_CUSTOMERS', 'SALES_AGENTS');
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- ── Columns on existing tables ───────────────────────────────────────────
|
||
|
|
|
||
|
|
ALTER TABLE "City" ADD COLUMN IF NOT EXISTS "percent" DECIMAL(6,3);
|
||
|
|
ALTER TABLE "City" ADD COLUMN IF NOT EXISTS "percentAux" DECIMAL(6,3);
|
||
|
|
|
||
|
|
ALTER TABLE "AdminUser" ADD COLUMN IF NOT EXISTS "userTypeId" INTEGER;
|
||
|
|
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "kind" "CustomerKind" NOT NULL DEFAULT 'APP_SHOPKEEPER';
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "phone" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "address" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "storeName" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "pasazhName" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "zone" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "memberCode" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "avatarFileId" TEXT;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "canPlaceOrders" BOOLEAN NOT NULL DEFAULT true;
|
||
|
|
ALTER TABLE "Customer" ADD COLUMN IF NOT EXISTS "isArchived" BOOLEAN NOT NULL DEFAULT false;
|
||
|
|
|
||
|
|
ALTER TABLE "Branch" ADD COLUMN IF NOT EXISTS "address" TEXT;
|
||
|
|
ALTER TABLE "Branch" ADD COLUMN IF NOT EXISTS "phone" TEXT;
|
||
|
|
ALTER TABLE "Branch" ADD COLUMN IF NOT EXISTS "isActive" BOOLEAN NOT NULL DEFAULT true;
|
||
|
|
|
||
|
|
ALTER TABLE "Workshop" ADD COLUMN IF NOT EXISTS "address" TEXT;
|
||
|
|
ALTER TABLE "Workshop" ADD COLUMN IF NOT EXISTS "phone" TEXT;
|
||
|
|
ALTER TABLE "Workshop" ADD COLUMN IF NOT EXISTS "supervisorId" TEXT;
|
||
|
|
|
||
|
|
ALTER TABLE "Category" ADD COLUMN IF NOT EXISTS "type" INTEGER NOT NULL DEFAULT 1;
|
||
|
|
ALTER TABLE "Category" ADD COLUMN IF NOT EXISTS "imageUrl" TEXT;
|
||
|
|
ALTER TABLE "Category" ADD COLUMN IF NOT EXISTS "iconUrl" TEXT;
|
||
|
|
ALTER TABLE "Category" ADD COLUMN IF NOT EXISTS "metaTag" TEXT;
|
||
|
|
ALTER TABLE "Category" ADD COLUMN IF NOT EXISTS "description" TEXT;
|
||
|
|
|
||
|
|
ALTER TABLE "Product" ADD COLUMN IF NOT EXISTS "productCode" TEXT;
|
||
|
|
ALTER TABLE "Product" ADD COLUMN IF NOT EXISTS "showType" INTEGER NOT NULL DEFAULT 3;
|
||
|
|
|
||
|
|
ALTER TABLE "ProductVariant" ADD COLUMN IF NOT EXISTS "qty" INTEGER NOT NULL DEFAULT 0;
|
||
|
|
ALTER TABLE "ProductVariant" ADD COLUMN IF NOT EXISTS "isPreOrder" BOOLEAN NOT NULL DEFAULT false;
|
||
|
|
|
||
|
|
ALTER TABLE "Order" ADD COLUMN IF NOT EXISTS "channel" "OrderChannel" NOT NULL DEFAULT 'RETAIL';
|
||
|
|
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "labName" TEXT;
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "hallmark" TEXT;
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "accountName" TEXT;
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "bankName" TEXT;
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "transferTo" TEXT;
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "originalValue" DECIMAL(14,4);
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "priceRial" DECIMAL(20,2);
|
||
|
|
ALTER TABLE "LedgerEntry" ADD COLUMN IF NOT EXISTS "receivedAt" TIMESTAMP(3);
|
||
|
|
|
||
|
|
-- ── New tables ───────────────────────────────────────────────────────────
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "user_type" (
|
||
|
|
"id" INTEGER NOT NULL,
|
||
|
|
"title" TEXT NOT NULL,
|
||
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "user_type_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "permission" (
|
||
|
|
"rid" TEXT NOT NULL,
|
||
|
|
"userTypeId" INTEGER NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "permission_pkey" PRIMARY KEY ("rid","userTypeId")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "Comment" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"productId" TEXT NOT NULL,
|
||
|
|
"customerId" TEXT NOT NULL,
|
||
|
|
"body" TEXT NOT NULL,
|
||
|
|
"status" "CommentStatus" NOT NULL DEFAULT 'PENDING',
|
||
|
|
"replyToId" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "Comment_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "Setting" (
|
||
|
|
"key" TEXT NOT NULL,
|
||
|
|
"value" JSONB NOT NULL,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "Setting_pkey" PRIMARY KEY ("key")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "Promotion" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"title" TEXT NOT NULL,
|
||
|
|
"description" TEXT,
|
||
|
|
"scope" "PromotionScope" NOT NULL DEFAULT 'PRODUCT',
|
||
|
|
"usage" "PromotionUsage" NOT NULL DEFAULT 'ONCE',
|
||
|
|
"discountPercent" DECIMAL(6,3) NOT NULL,
|
||
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"platform" INTEGER NOT NULL DEFAULT 3,
|
||
|
|
"startsAt" TIMESTAMP(3),
|
||
|
|
"endsAt" TIMESTAMP(3),
|
||
|
|
"productId" TEXT,
|
||
|
|
"categoryId" TEXT,
|
||
|
|
"customerId" TEXT,
|
||
|
|
"brandId" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
|
||
|
|
CONSTRAINT "Promotion_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "PromotionExclusion" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"type" INTEGER NOT NULL,
|
||
|
|
"itemId" TEXT NOT NULL,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "PromotionExclusion_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "BranchAllocation" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"branchId" TEXT NOT NULL,
|
||
|
|
"allocatorAdminUserId" TEXT,
|
||
|
|
"note" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "BranchAllocation_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "BranchAllocationItem" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"allocationId" TEXT NOT NULL,
|
||
|
|
"productId" TEXT NOT NULL,
|
||
|
|
"weightGrams" DECIMAL(14,4),
|
||
|
|
|
||
|
|
CONSTRAINT "BranchAllocationItem_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "SmsMessage" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"audience" "MessageAudience" NOT NULL,
|
||
|
|
"recipients" TEXT,
|
||
|
|
"body" TEXT NOT NULL,
|
||
|
|
"sentCount" INTEGER NOT NULL DEFAULT 0,
|
||
|
|
"sentByAdminUserId" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "SmsMessage_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "Notification" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"title" TEXT NOT NULL,
|
||
|
|
"body" TEXT NOT NULL,
|
||
|
|
"audience" "MessageAudience" NOT NULL DEFAULT 'ALL_CUSTOMERS',
|
||
|
|
"sentByAdminUserId" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "Notification_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "NotificationRecipient" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"notificationId" TEXT NOT NULL,
|
||
|
|
"customerId" TEXT NOT NULL,
|
||
|
|
"readAt" TIMESTAMP(3),
|
||
|
|
|
||
|
|
CONSTRAINT "NotificationRecipient_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "NewsletterSubscriber" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"email" TEXT,
|
||
|
|
"mobile" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "NewsletterSubscriber_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "CustomerActivityLog" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"customerId" TEXT NOT NULL,
|
||
|
|
"action" TEXT NOT NULL,
|
||
|
|
"detail" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "CustomerActivityLog_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "UploadedFile" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"url" TEXT NOT NULL,
|
||
|
|
"originalName" TEXT NOT NULL,
|
||
|
|
"mimeType" TEXT NOT NULL,
|
||
|
|
"sizeBytes" INTEGER NOT NULL,
|
||
|
|
"purpose" TEXT,
|
||
|
|
"uploadedByAdminUserId" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "UploadedFile_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- ── Indexes ──────────────────────────────────────────────────────────────
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS "AdminUser_userTypeId_idx" ON "AdminUser"("userTypeId");
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "Product_productCode_key" ON "Product"("productCode");
|
||
|
|
CREATE INDEX IF NOT EXISTS "permission_userTypeId_idx" ON "permission"("userTypeId");
|
||
|
|
CREATE INDEX IF NOT EXISTS "Comment_productId_idx" ON "Comment"("productId");
|
||
|
|
CREATE INDEX IF NOT EXISTS "Comment_customerId_idx" ON "Comment"("customerId");
|
||
|
|
CREATE INDEX IF NOT EXISTS "Comment_status_idx" ON "Comment"("status");
|
||
|
|
CREATE INDEX IF NOT EXISTS "Promotion_scope_isActive_idx" ON "Promotion"("scope", "isActive");
|
||
|
|
CREATE INDEX IF NOT EXISTS "PromotionExclusion_type_idx" ON "PromotionExclusion"("type");
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "PromotionExclusion_type_itemId_key" ON "PromotionExclusion"("type", "itemId");
|
||
|
|
CREATE INDEX IF NOT EXISTS "BranchAllocation_branchId_idx" ON "BranchAllocation"("branchId");
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "BranchAllocationItem_allocationId_productId_key" ON "BranchAllocationItem"("allocationId", "productId");
|
||
|
|
CREATE INDEX IF NOT EXISTS "SmsMessage_createdAt_idx" ON "SmsMessage"("createdAt");
|
||
|
|
CREATE INDEX IF NOT EXISTS "Notification_createdAt_idx" ON "Notification"("createdAt");
|
||
|
|
CREATE INDEX IF NOT EXISTS "NotificationRecipient_customerId_readAt_idx" ON "NotificationRecipient"("customerId", "readAt");
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "NotificationRecipient_notificationId_customerId_key" ON "NotificationRecipient"("notificationId", "customerId");
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "NewsletterSubscriber_email_key" ON "NewsletterSubscriber"("email");
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "NewsletterSubscriber_mobile_key" ON "NewsletterSubscriber"("mobile");
|
||
|
|
CREATE INDEX IF NOT EXISTS "CustomerActivityLog_customerId_createdAt_idx" ON "CustomerActivityLog"("customerId", "createdAt");
|
||
|
|
CREATE INDEX IF NOT EXISTS "UploadedFile_purpose_idx" ON "UploadedFile"("purpose");
|
||
|
|
|
||
|
|
-- ── Foreign keys added after 0001_init ───────────────────────────────────
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "AdminUser" ADD CONSTRAINT "AdminUser_userTypeId_fkey" FOREIGN KEY ("userTypeId") REFERENCES "user_type"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Customer" ADD CONSTRAINT "Customer_avatarFileId_fkey" FOREIGN KEY ("avatarFileId") REFERENCES "UploadedFile"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Workshop" ADD CONSTRAINT "Workshop_supervisorId_fkey" FOREIGN KEY ("supervisorId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "permission" ADD CONSTRAINT "permission_userTypeId_fkey" FOREIGN KEY ("userTypeId") REFERENCES "user_type"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Comment" ADD CONSTRAINT "Comment_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Comment" ADD CONSTRAINT "Comment_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Comment" ADD CONSTRAINT "Comment_replyToId_fkey" FOREIGN KEY ("replyToId") REFERENCES "Comment"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Promotion" ADD CONSTRAINT "Promotion_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Promotion" ADD CONSTRAINT "Promotion_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Promotion" ADD CONSTRAINT "Promotion_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Promotion" ADD CONSTRAINT "Promotion_brandId_fkey" FOREIGN KEY ("brandId") REFERENCES "Brand"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "BranchAllocation" ADD CONSTRAINT "BranchAllocation_branchId_fkey" FOREIGN KEY ("branchId") REFERENCES "Branch"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "BranchAllocation" ADD CONSTRAINT "BranchAllocation_allocatorAdminUserId_fkey" FOREIGN KEY ("allocatorAdminUserId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "BranchAllocationItem" ADD CONSTRAINT "BranchAllocationItem_allocationId_fkey" FOREIGN KEY ("allocationId") REFERENCES "BranchAllocation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "BranchAllocationItem" ADD CONSTRAINT "BranchAllocationItem_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "SmsMessage" ADD CONSTRAINT "SmsMessage_sentByAdminUserId_fkey" FOREIGN KEY ("sentByAdminUserId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "Notification" ADD CONSTRAINT "Notification_sentByAdminUserId_fkey" FOREIGN KEY ("sentByAdminUserId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "NotificationRecipient" ADD CONSTRAINT "NotificationRecipient_notificationId_fkey" FOREIGN KEY ("notificationId") REFERENCES "Notification"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "NotificationRecipient" ADD CONSTRAINT "NotificationRecipient_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "CustomerActivityLog" ADD CONSTRAINT "CustomerActivityLog_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
DO $$ BEGIN
|
||
|
|
ALTER TABLE "UploadedFile" ADD CONSTRAINT "UploadedFile_uploadedByAdminUserId_fkey" FOREIGN KEY ("uploadedByAdminUserId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|