88 lines
3.9 KiB
SQL
88 lines
3.9 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "tenders" (
|
|
"id" TEXT NOT NULL,
|
|
"tenderNumber" TEXT NOT NULL,
|
|
"issuingBodyName" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"termsValue" DECIMAL(15,2) NOT NULL,
|
|
"bondValue" DECIMAL(15,2) NOT NULL,
|
|
"announcementDate" DATE NOT NULL,
|
|
"closingDate" DATE NOT NULL,
|
|
"announcementLink" TEXT,
|
|
"source" TEXT NOT NULL,
|
|
"sourceOther" TEXT,
|
|
"announcementType" TEXT NOT NULL,
|
|
"notes" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'ACTIVE',
|
|
"contactId" TEXT,
|
|
"createdById" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "tenders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "tender_directives" (
|
|
"id" TEXT NOT NULL,
|
|
"tenderId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"notes" TEXT,
|
|
"assignedToEmployeeId" TEXT NOT NULL,
|
|
"issuedById" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"completedAt" TIMESTAMP(3),
|
|
"completionNotes" TEXT,
|
|
"completedById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "tender_directives_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "attachments" ADD COLUMN "tenderId" TEXT;
|
|
ALTER TABLE "attachments" ADD COLUMN "tenderDirectiveId" TEXT;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "deals" ADD COLUMN "sourceTenderId" TEXT;
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tenders_tenderNumber_key" ON "tenders"("tenderNumber");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tenders_tenderNumber_idx" ON "tenders"("tenderNumber");
|
|
CREATE INDEX "tenders_status_idx" ON "tenders"("status");
|
|
CREATE INDEX "tenders_createdById_idx" ON "tenders"("createdById");
|
|
CREATE INDEX "tenders_announcementDate_idx" ON "tenders"("announcementDate");
|
|
CREATE INDEX "tenders_closingDate_idx" ON "tenders"("closingDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tender_directives_tenderId_idx" ON "tender_directives"("tenderId");
|
|
CREATE INDEX "tender_directives_assignedToEmployeeId_idx" ON "tender_directives"("assignedToEmployeeId");
|
|
CREATE INDEX "tender_directives_status_idx" ON "tender_directives"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "deals_sourceTenderId_key" ON "deals"("sourceTenderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "attachments_tenderId_idx" ON "attachments"("tenderId");
|
|
CREATE INDEX "attachments_tenderDirectiveId_idx" ON "attachments"("tenderDirectiveId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "tenders" ADD CONSTRAINT "tenders_contactId_fkey" FOREIGN KEY ("contactId") REFERENCES "contacts"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "tenders" ADD CONSTRAINT "tenders_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "tender_directives" ADD CONSTRAINT "tender_directives_tenderId_fkey" FOREIGN KEY ("tenderId") REFERENCES "tenders"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "tender_directives" ADD CONSTRAINT "tender_directives_assignedToEmployeeId_fkey" FOREIGN KEY ("assignedToEmployeeId") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "tender_directives" ADD CONSTRAINT "tender_directives_issuedById_fkey" FOREIGN KEY ("issuedById") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "tender_directives" ADD CONSTRAINT "tender_directives_completedById_fkey" FOREIGN KEY ("completedById") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "deals" ADD CONSTRAINT "deals_sourceTenderId_fkey" FOREIGN KEY ("sourceTenderId") REFERENCES "tenders"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_tenderId_fkey" FOREIGN KEY ("tenderId") REFERENCES "tenders"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_tenderDirectiveId_fkey" FOREIGN KEY ("tenderDirectiveId") REFERENCES "tender_directives"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|