CREATE TABLE "ShoppingGroup" (
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "position" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

ALTER TABLE "ShoppingListItem"
ADD COLUMN "groupId" TEXT;

ALTER TABLE "ShoppingGroup"
ADD CONSTRAINT "ShoppingGroup_userId_fkey"
FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "ShoppingListItem"
ADD CONSTRAINT "ShoppingListItem_groupId_fkey"
FOREIGN KEY ("groupId") REFERENCES "ShoppingGroup"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE INDEX "ShoppingGroup_userId_idx" ON "ShoppingGroup"("userId");
CREATE UNIQUE INDEX "ShoppingGroup_userId_name_key" ON "ShoppingGroup"("userId", "name");
CREATE UNIQUE INDEX "ShoppingGroup_userId_position_key" ON "ShoppingGroup"("userId", "position");
CREATE INDEX "ShoppingListItem_groupId_idx" ON "ShoppingListItem"("groupId");