0967fc23fd
- Added 1030 Cash in Transit to ISP_CHART_OF_ACCOUNTS (between 1020 and 1100) - Added CollectionStatus (COMPLETED/VOIDED) and RemittanceStatus (PENDING/VERIFIED) enums - Added Collection model with FIFO allocations, zone-scoped collector, JE link, void fields - Added CollectionAllocation model linking collections to invoices - Added Remittance model with two-party verification, variance field, JE link - Added reverse relations on User (collections, remittances) and Subscriber (collections) - Applied migration: add-collections-remittances - Extended TENANT_SCOPED_MODELS with collection, collectionAllocation, remittance - Added full 12-operation extension blocks for all three new models
112 lines
4.1 KiB
SQL
112 lines
4.1 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "CollectionStatus" AS ENUM ('COMPLETED', 'VOIDED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RemittanceStatus" AS ENUM ('PENDING', 'VERIFIED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Collection" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"collectorId" TEXT NOT NULL,
|
|
"subscriberId" TEXT NOT NULL,
|
|
"amount" DECIMAL(10,2) NOT NULL,
|
|
"collectionDate" TIMESTAMP(3) NOT NULL,
|
|
"status" "CollectionStatus" NOT NULL DEFAULT 'COMPLETED',
|
|
"notes" TEXT,
|
|
"journalEntryId" TEXT,
|
|
"voidedAt" TIMESTAMP(3),
|
|
"voidedById" TEXT,
|
|
"voidJournalEntryId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Collection_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CollectionAllocation" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"collectionId" TEXT NOT NULL,
|
|
"invoiceId" TEXT NOT NULL,
|
|
"amount" DECIMAL(10,2) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "CollectionAllocation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Remittance" (
|
|
"id" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"collectorId" TEXT NOT NULL,
|
|
"remittanceDate" TIMESTAMP(3) NOT NULL,
|
|
"collectedTotal" DECIMAL(10,2) NOT NULL,
|
|
"verifiedTotal" DECIMAL(10,2),
|
|
"variance" DECIMAL(10,2),
|
|
"status" "RemittanceStatus" NOT NULL DEFAULT 'PENDING',
|
|
"verifiedById" TEXT,
|
|
"verifiedAt" TIMESTAMP(3),
|
|
"journalEntryId" TEXT,
|
|
"notes" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Remittance_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Collection_tenantId_idx" ON "Collection"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Collection_tenantId_collectorId_idx" ON "Collection"("tenantId", "collectorId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Collection_tenantId_collectionDate_idx" ON "Collection"("tenantId", "collectionDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Collection_tenantId_subscriberId_idx" ON "Collection"("tenantId", "subscriberId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CollectionAllocation_collectionId_idx" ON "CollectionAllocation"("collectionId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CollectionAllocation_invoiceId_idx" ON "CollectionAllocation"("invoiceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CollectionAllocation_tenantId_idx" ON "CollectionAllocation"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Remittance_tenantId_idx" ON "Remittance"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Remittance_tenantId_collectorId_idx" ON "Remittance"("tenantId", "collectorId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Remittance_tenantId_remittanceDate_idx" ON "Remittance"("tenantId", "remittanceDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Remittance_tenantId_status_idx" ON "Remittance"("tenantId", "status");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Collection" ADD CONSTRAINT "Collection_collectorId_fkey" FOREIGN KEY ("collectorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Collection" ADD CONSTRAINT "Collection_subscriberId_fkey" FOREIGN KEY ("subscriberId") REFERENCES "Subscriber"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Collection" ADD CONSTRAINT "Collection_voidedById_fkey" FOREIGN KEY ("voidedById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CollectionAllocation" ADD CONSTRAINT "CollectionAllocation_collectionId_fkey" FOREIGN KEY ("collectionId") REFERENCES "Collection"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CollectionAllocation" ADD CONSTRAINT "CollectionAllocation_invoiceId_fkey" FOREIGN KEY ("invoiceId") REFERENCES "Invoice"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Remittance" ADD CONSTRAINT "Remittance_collectorId_fkey" FOREIGN KEY ("collectorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Remittance" ADD CONSTRAINT "Remittance_verifiedById_fkey" FOREIGN KEY ("verifiedById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|