-- CreateEnum
CREATE TYPE "ReservationStatus" AS ENUM ('ACTIVE', 'RELEASED', 'COMMITTED');

-- CreateTable
CREATE TABLE "warehouses" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "name" VARCHAR(100) NOT NULL,

    CONSTRAINT "warehouses_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "stock_items" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "variant_id" UUID NOT NULL,
    "warehouse_id" UUID NOT NULL,
    "on_hand" INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT "stock_items_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "stock_ledger" (
    "id" UUID NOT NULL,
    "stock_item_id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "actor_id" UUID NOT NULL,
    "delta" INTEGER NOT NULL,
    "reason" VARCHAR(200) NOT NULL,
    "idempotency_key" UUID NOT NULL,
    "request_hash" CHAR(64) NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "stock_ledger_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "stock_reservations" (
    "id" UUID NOT NULL,
    "stock_item_id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "quantity" INTEGER NOT NULL,
    "status" "ReservationStatus" NOT NULL DEFAULT 'ACTIVE',
    "idempotency_key" UUID NOT NULL,
    "request_hash" CHAR(64) NOT NULL,
    "expires_at" TIMESTAMPTZ(3) NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "stock_reservations_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "warehouses_organization_id_name_key" ON "warehouses"("organization_id", "name");

-- CreateIndex
CREATE UNIQUE INDEX "warehouses_id_organization_id_key" ON "warehouses"("id", "organization_id");

-- CreateIndex
CREATE INDEX "stock_items_organization_id_id_idx" ON "stock_items"("organization_id", "id");

-- CreateIndex
CREATE UNIQUE INDEX "stock_items_variant_id_warehouse_id_key" ON "stock_items"("variant_id", "warehouse_id");

-- CreateIndex
CREATE UNIQUE INDEX "stock_items_id_organization_id_key" ON "stock_items"("id", "organization_id");

-- CreateIndex
CREATE INDEX "stock_ledger_stock_item_id_created_at_id_idx" ON "stock_ledger"("stock_item_id", "created_at", "id");

-- CreateIndex
CREATE UNIQUE INDEX "stock_ledger_organization_id_idempotency_key_key" ON "stock_ledger"("organization_id", "idempotency_key");

-- CreateIndex
CREATE INDEX "stock_reservations_stock_item_id_status_expires_at_idx" ON "stock_reservations"("stock_item_id", "status", "expires_at");

-- CreateIndex
CREATE INDEX "stock_reservations_user_id_organization_id_idx" ON "stock_reservations"("user_id", "organization_id");

-- CreateIndex
CREATE UNIQUE INDEX "stock_reservations_organization_id_idempotency_key_key" ON "stock_reservations"("organization_id", "idempotency_key");

-- AddForeignKey
ALTER TABLE "warehouses" ADD CONSTRAINT "warehouses_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "stock_items" ADD CONSTRAINT "stock_items_variant_id_organization_id_fkey" FOREIGN KEY ("variant_id", "organization_id") REFERENCES "product_variants"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "stock_items" ADD CONSTRAINT "stock_items_warehouse_id_organization_id_fkey" FOREIGN KEY ("warehouse_id", "organization_id") REFERENCES "warehouses"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "stock_ledger" ADD CONSTRAINT "stock_ledger_stock_item_id_organization_id_fkey" FOREIGN KEY ("stock_item_id", "organization_id") REFERENCES "stock_items"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "stock_reservations" ADD CONSTRAINT "stock_reservations_stock_item_id_organization_id_fkey" FOREIGN KEY ("stock_item_id", "organization_id") REFERENCES "stock_items"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;