-- CreateTable
CREATE TABLE "step_tech_step_ingredient" (
    "id" SERIAL NOT NULL,
    "step_id" INTEGER NOT NULL,
    "tech_step_order" INTEGER NOT NULL,
    "ingredient_id" INTEGER NOT NULL,
    "quantity" DECIMAL(10,2),
    "unit_id" INTEGER,
    "start" INTEGER NOT NULL,
    "end" INTEGER NOT NULL,
    "source" TEXT NOT NULL DEFAULT 'auto',

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

-- CreateTable
CREATE TABLE "step_tech_step_utensil" (
    "id" SERIAL NOT NULL,
    "step_id" INTEGER NOT NULL,
    "tech_step_order" INTEGER NOT NULL,
    "utensil_id" INTEGER NOT NULL,
    "start" INTEGER NOT NULL,
    "end" INTEGER NOT NULL,
    "source" TEXT NOT NULL DEFAULT 'auto',

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

-- AddForeignKey
ALTER TABLE "step_tech_step_ingredient" ADD CONSTRAINT "step_tech_step_ingredient_step_id_tech_step_order_fkey" FOREIGN KEY ("step_id", "tech_step_order") REFERENCES "step_tech_step"("step_id", "order") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "step_tech_step_ingredient" ADD CONSTRAINT "step_tech_step_ingredient_ingredient_id_fkey" FOREIGN KEY ("ingredient_id") REFERENCES "ingredients"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "step_tech_step_ingredient" ADD CONSTRAINT "step_tech_step_ingredient_unit_id_fkey" FOREIGN KEY ("unit_id") REFERENCES "unit"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "step_tech_step_utensil" ADD CONSTRAINT "step_tech_step_utensil_step_id_tech_step_order_fkey" FOREIGN KEY ("step_id", "tech_step_order") REFERENCES "step_tech_step"("step_id", "order") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "step_tech_step_utensil" ADD CONSTRAINT "step_tech_step_utensil_utensil_id_fkey" FOREIGN KEY ("utensil_id") REFERENCES "utensil"("id") ON DELETE CASCADE ON UPDATE CASCADE;
