99 lines
3.3 KiB
SQL
99 lines
3.3 KiB
SQL
-- DropTable
|
|
DROP TABLE "offers";
|
|
|
|
-- DropTable
|
|
DROP TABLE "calculations";
|
|
|
|
-- DropTable
|
|
DROP TABLE "suppliers";
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "exchange_quotes" (
|
|
"id" SERIAL NOT NULL,
|
|
"uuid" TEXT NOT NULL,
|
|
"supplier_id" INTEGER NOT NULL,
|
|
"product_id" INTEGER NOT NULL,
|
|
"status" VARCHAR(20) NOT NULL DEFAULT 'active',
|
|
"quantity" DECIMAL(12,2) NOT NULL,
|
|
"unit" VARCHAR(20) NOT NULL DEFAULT 'ton',
|
|
"price_per_unit" DECIMAL(12,2) NOT NULL,
|
|
"currency" VARCHAR(10) NOT NULL DEFAULT 'USD',
|
|
"incoterms_code" VARCHAR(20),
|
|
"origin_point_uuid" VARCHAR(100),
|
|
"origin_name" VARCHAR(255),
|
|
"valid_until" DATE,
|
|
"notes" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "exchange_quotes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "exchange_suppliers" (
|
|
"id" SERIAL NOT NULL,
|
|
"uuid" TEXT NOT NULL,
|
|
"team_uuid" VARCHAR(100),
|
|
"name" VARCHAR(255) NOT NULL,
|
|
"description" TEXT,
|
|
"country" VARCHAR(100) NOT NULL DEFAULT '',
|
|
"country_code" VARCHAR(10) NOT NULL DEFAULT '',
|
|
"logo_url" VARCHAR(500),
|
|
"is_verified" BOOLEAN NOT NULL DEFAULT false,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "exchange_suppliers_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "exchange_products" (
|
|
"id" SERIAL NOT NULL,
|
|
"uuid" TEXT NOT NULL,
|
|
"sku" VARCHAR(100),
|
|
"name" VARCHAR(255) NOT NULL,
|
|
"category_name" VARCHAR(255) NOT NULL DEFAULT '',
|
|
"unit" VARCHAR(20) NOT NULL DEFAULT 'ton',
|
|
"description" TEXT,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "exchange_products_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "exchange_quotes_uuid_key" ON "exchange_quotes"("uuid");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "exchange_quotes_status_product_id_created_at_idx" ON "exchange_quotes"("status", "product_id", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "exchange_quotes_supplier_id_created_at_idx" ON "exchange_quotes"("supplier_id", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "exchange_suppliers_uuid_key" ON "exchange_suppliers"("uuid");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "exchange_suppliers_team_uuid_key" ON "exchange_suppliers"("team_uuid");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "exchange_suppliers_country_code_is_active_idx" ON "exchange_suppliers"("country_code", "is_active");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "exchange_products_uuid_key" ON "exchange_products"("uuid");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "exchange_products_sku_key" ON "exchange_products"("sku");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "exchange_products_category_name_is_active_idx" ON "exchange_products"("category_name", "is_active");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "exchange_quotes" ADD CONSTRAINT "exchange_quotes_supplier_id_fkey" FOREIGN KEY ("supplier_id") REFERENCES "exchange_suppliers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "exchange_quotes" ADD CONSTRAINT "exchange_quotes_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "exchange_products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|