119 lines
4.5 KiB
SQL
119 lines
4.5 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "PayCondition" AS ENUM ('Full', 'Split', 'BillFull', 'BillSplit');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Quotation" (
|
|
"id" TEXT NOT NULL,
|
|
"customerId" TEXT NOT NULL,
|
|
"customerBranchId" TEXT NOT NULL,
|
|
"status" "Status" NOT NULL DEFAULT 'CREATED',
|
|
"statusOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"code" TEXT NOT NULL,
|
|
"date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"payCondition" "PayCondition" NOT NULL,
|
|
"paySplitCount" INTEGER,
|
|
"payBillDate" TIMESTAMP(3),
|
|
"workerCount" INTEGER NOT NULL,
|
|
"urgent" BOOLEAN NOT NULL DEFAULT false,
|
|
"totalPrice" DOUBLE PRECISION NOT NULL,
|
|
"totalDiscount" DOUBLE PRECISION NOT NULL,
|
|
"vat" DOUBLE PRECISION NOT NULL,
|
|
"vatExcluded" DOUBLE PRECISION NOT NULL,
|
|
"finalPrice" DOUBLE PRECISION NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdByUserId" TEXT,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"updatedByUserId" TEXT,
|
|
|
|
CONSTRAINT "Quotation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "QuotationPaySplit" (
|
|
"id" TEXT NOT NULL,
|
|
"no" INTEGER NOT NULL,
|
|
"date" TIMESTAMP(3) NOT NULL,
|
|
"quotationId" TEXT,
|
|
|
|
CONSTRAINT "QuotationPaySplit_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "QuotationWorker" (
|
|
"id" TEXT NOT NULL,
|
|
"no" INTEGER NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"employeeId" TEXT NOT NULL,
|
|
"quotationId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "QuotationWorker_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "QuotationService" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"detail" TEXT NOT NULL,
|
|
"attributes" JSONB,
|
|
"quotationId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "QuotationService_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "QuotationServiceWork" (
|
|
"id" TEXT NOT NULL,
|
|
"order" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"attributes" JSONB,
|
|
"serviceId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "QuotationServiceWork_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "QuotationServiceWorkProduct" (
|
|
"order" INTEGER NOT NULL,
|
|
"workId" TEXT NOT NULL,
|
|
"productId" TEXT NOT NULL,
|
|
"vat" DOUBLE PRECISION NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"discount" DOUBLE PRECISION NOT NULL,
|
|
"pricePerUnit" DOUBLE PRECISION NOT NULL,
|
|
|
|
CONSTRAINT "QuotationServiceWorkProduct_pkey" PRIMARY KEY ("workId","productId")
|
|
);
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Quotation" ADD CONSTRAINT "Quotation_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Quotation" ADD CONSTRAINT "Quotation_customerBranchId_fkey" FOREIGN KEY ("customerBranchId") REFERENCES "CustomerBranch"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Quotation" ADD CONSTRAINT "Quotation_createdByUserId_fkey" FOREIGN KEY ("createdByUserId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Quotation" ADD CONSTRAINT "Quotation_updatedByUserId_fkey" FOREIGN KEY ("updatedByUserId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationPaySplit" ADD CONSTRAINT "QuotationPaySplit_quotationId_fkey" FOREIGN KEY ("quotationId") REFERENCES "Quotation"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationWorker" ADD CONSTRAINT "QuotationWorker_employeeId_fkey" FOREIGN KEY ("employeeId") REFERENCES "Employee"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationWorker" ADD CONSTRAINT "QuotationWorker_quotationId_fkey" FOREIGN KEY ("quotationId") REFERENCES "Quotation"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationService" ADD CONSTRAINT "QuotationService_quotationId_fkey" FOREIGN KEY ("quotationId") REFERENCES "Quotation"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationServiceWork" ADD CONSTRAINT "QuotationServiceWork_serviceId_fkey" FOREIGN KEY ("serviceId") REFERENCES "QuotationService"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationServiceWorkProduct" ADD CONSTRAINT "QuotationServiceWorkProduct_workId_fkey" FOREIGN KEY ("workId") REFERENCES "QuotationServiceWork"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuotationServiceWorkProduct" ADD CONSTRAINT "QuotationServiceWorkProduct_productId_fkey" FOREIGN KEY ("productId") REFERENCES "Product"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|