45 lines
3.4 KiB
SQL
45 lines
3.4 KiB
SQL
CREATE TYPE "PurchaseOrderStatus" AS ENUM ('DRAFT', 'ISSUED', 'CANCELLED');
|
|
|
|
CREATE TABLE "purchase_orders" (
|
|
"id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"supplier_id" UUID NOT NULL,
|
|
"created_by_id" UUID NOT NULL,
|
|
"reference" VARCHAR(20) NOT NULL,
|
|
"status" "PurchaseOrderStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"currency" CHAR(3) NOT NULL,
|
|
"expected_delivery_at" DATE,
|
|
"merchandise_total" DECIMAL(12,2) NOT NULL,
|
|
"issued_at" TIMESTAMPTZ(3),
|
|
"cancelled_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "purchase_orders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
CREATE TABLE "purchase_order_lines" (
|
|
"id" UUID NOT NULL,
|
|
"purchase_order_id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"material_id" UUID NOT NULL,
|
|
"material_name" VARCHAR(160) NOT NULL,
|
|
"material_code" VARCHAR(32) NOT NULL,
|
|
"unit" "MaterialUnit" NOT NULL,
|
|
"supplier_sku" VARCHAR(64) NOT NULL,
|
|
"quantity" DECIMAL(12,3) NOT NULL,
|
|
"unit_price" DECIMAL(12,2) NOT NULL,
|
|
"line_total" DECIMAL(12,2) NOT NULL,
|
|
CONSTRAINT "purchase_order_lines_pkey" PRIMARY KEY ("id")
|
|
);
|
|
CREATE UNIQUE INDEX "purchase_orders_organization_id_reference_key" ON "purchase_orders"("organization_id", "reference");
|
|
CREATE UNIQUE INDEX "purchase_orders_id_organization_id_key" ON "purchase_orders"("id", "organization_id");
|
|
CREATE INDEX "purchase_orders_organization_id_status_created_at_id_idx" ON "purchase_orders"("organization_id", "status", "created_at", "id");
|
|
CREATE UNIQUE INDEX "purchase_order_lines_purchase_order_id_material_id_key" ON "purchase_order_lines"("purchase_order_id", "material_id");
|
|
CREATE INDEX "purchase_order_lines_material_id_organization_id_idx" ON "purchase_order_lines"("material_id", "organization_id");
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_supplier_id_organization_id_fkey" FOREIGN KEY ("supplier_id", "organization_id") REFERENCES "suppliers"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_created_by_id_organization_id_fkey" FOREIGN KEY ("created_by_id", "organization_id") REFERENCES "users"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "purchase_order_lines" ADD CONSTRAINT "purchase_order_lines_purchase_order_id_organization_id_fkey" FOREIGN KEY ("purchase_order_id", "organization_id") REFERENCES "purchase_orders"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "purchase_order_lines" ADD CONSTRAINT "purchase_order_lines_material_id_organization_id_fkey" FOREIGN KEY ("material_id", "organization_id") REFERENCES "materials"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_state_dates" CHECK ((status = 'DRAFT' AND issued_at IS NULL AND cancelled_at IS NULL) OR (status = 'ISSUED' AND issued_at IS NOT NULL AND cancelled_at IS NULL) OR (status = 'CANCELLED' AND issued_at IS NULL AND cancelled_at IS NOT NULL));
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_money" CHECK (currency IN ('INR', 'USD', 'EUR', 'GBP') AND merchandise_total >= 0);
|
|
ALTER TABLE "purchase_order_lines" ADD CONSTRAINT "purchase_order_lines_quantity" CHECK (quantity > 0 AND unit_price >= 0 AND line_total >= 0);
|