-- v0.4.0 — Plant groups (irises, hostas, etc.)

CREATE TABLE "PlantGroup" (
  "id"        TEXT NOT NULL,
  "name"      TEXT NOT NULL,
  "notes"     TEXT,
  "active"    BOOLEAN NOT NULL DEFAULT true,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "PlantGroup_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "PlantGroup_name_key" ON "PlantGroup"("name");
CREATE INDEX "PlantGroup_active_idx" ON "PlantGroup"("active");

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

ALTER TABLE "Plant"
  ADD CONSTRAINT "Plant_groupId_fkey"
  FOREIGN KEY ("groupId") REFERENCES "PlantGroup"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

CREATE INDEX "Plant_groupId_idx" ON "Plant"("groupId");