84 lines
3.4 KiB
SQL
84 lines
3.4 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "FinanceProfile" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"currencyCode" TEXT NOT NULL DEFAULT 'USD',
|
|
"standardLaborRatePerHour" REAL NOT NULL DEFAULT 45,
|
|
"overheadRatePerHour" REAL NOT NULL DEFAULT 18,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "FinanceCustomerPayment" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"salesOrderId" TEXT NOT NULL,
|
|
"paymentType" TEXT NOT NULL,
|
|
"paymentMethod" TEXT NOT NULL,
|
|
"paymentDate" DATETIME NOT NULL,
|
|
"amount" REAL NOT NULL,
|
|
"reference" TEXT NOT NULL,
|
|
"notes" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "FinanceCustomerPayment_salesOrderId_fkey" FOREIGN KEY ("salesOrderId") REFERENCES "SalesOrder" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "FinanceCustomerPayment_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "FinanceManufacturingCostSnapshot" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"workOrderId" TEXT NOT NULL,
|
|
"materialCost" REAL NOT NULL DEFAULT 0,
|
|
"laborCost" REAL NOT NULL DEFAULT 0,
|
|
"overheadCost" REAL NOT NULL DEFAULT 0,
|
|
"totalCost" REAL NOT NULL DEFAULT 0,
|
|
"materialIssueCount" INTEGER NOT NULL DEFAULT 0,
|
|
"laborEntryCount" INTEGER NOT NULL DEFAULT 0,
|
|
"calculatedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "FinanceManufacturingCostSnapshot_workOrderId_fkey" FOREIGN KEY ("workOrderId") REFERENCES "WorkOrder" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CapexEntry" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"title" TEXT NOT NULL,
|
|
"category" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL,
|
|
"vendorId" TEXT,
|
|
"purchaseOrderId" TEXT,
|
|
"plannedAmount" REAL NOT NULL,
|
|
"actualAmount" REAL NOT NULL,
|
|
"requestDate" DATETIME NOT NULL,
|
|
"targetInServiceDate" DATETIME,
|
|
"purchasedAt" DATETIME,
|
|
"notes" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "CapexEntry_vendorId_fkey" FOREIGN KEY ("vendorId") REFERENCES "Vendor" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT "CapexEntry_purchaseOrderId_fkey" FOREIGN KEY ("purchaseOrderId") REFERENCES "PurchaseOrder" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "FinanceCustomerPayment_salesOrderId_paymentDate_idx" ON "FinanceCustomerPayment"("salesOrderId", "paymentDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "FinanceCustomerPayment_createdAt_idx" ON "FinanceCustomerPayment"("createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "FinanceManufacturingCostSnapshot_workOrderId_key" ON "FinanceManufacturingCostSnapshot"("workOrderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "FinanceManufacturingCostSnapshot_calculatedAt_idx" ON "FinanceManufacturingCostSnapshot"("calculatedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CapexEntry_status_requestDate_idx" ON "CapexEntry"("status", "requestDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CapexEntry_vendorId_createdAt_idx" ON "CapexEntry"("vendorId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CapexEntry_purchaseOrderId_createdAt_idx" ON "CapexEntry"("purchaseOrderId", "createdAt");
|