-- Task 70: Garden module — containers, plants, care logs, care schedules, species cache

CREATE TABLE "garden_containers" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "household_id" uuid NOT NULL REFERENCES "households"("id") ON DELETE CASCADE,
  "name" text NOT NULL,
  "type" text NOT NULL DEFAULT 'other',
  "location_notes" text,
  "cover_image_url" text,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX "garden_containers_household_idx" ON "garden_containers" ("household_id");

CREATE TABLE "garden_plants" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "household_id" uuid NOT NULL REFERENCES "households"("id") ON DELETE CASCADE,
  "container_id" uuid REFERENCES "garden_containers"("id") ON DELETE SET NULL,
  "name" text NOT NULL,
  "scientific_name" text,
  "species_id" text,
  "category" text NOT NULL DEFAULT 'other',
  "notes" text,
  "acquisition_date" date,
  "growth_stage" text,
  "health_status" text NOT NULL DEFAULT 'healthy',
  "sunlight" text,
  "watering_notes" text,
  "fertilizing_notes" text,
  "primary_image_url" text,
  "images" jsonb NOT NULL DEFAULT '[]',
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX "garden_plants_household_idx" ON "garden_plants" ("household_id");
CREATE INDEX "garden_plants_household_container_idx" ON "garden_plants" ("household_id", "container_id");

CREATE TABLE "garden_care_logs" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "plant_id" uuid NOT NULL REFERENCES "garden_plants"("id") ON DELETE CASCADE,
  "household_id" uuid NOT NULL REFERENCES "households"("id") ON DELETE CASCADE,
  "care_type" text NOT NULL,
  "performed_by" uuid REFERENCES "users"("id") ON DELETE SET NULL,
  "notes" text,
  "performed_at" timestamp with time zone NOT NULL DEFAULT now(),
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX "garden_care_logs_plant_performed_idx" ON "garden_care_logs" ("plant_id", "performed_at");
CREATE INDEX "garden_care_logs_household_idx" ON "garden_care_logs" ("household_id");

CREATE TABLE "garden_care_schedules" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "plant_id" uuid NOT NULL REFERENCES "garden_plants"("id") ON DELETE CASCADE,
  "household_id" uuid NOT NULL REFERENCES "households"("id") ON DELETE CASCADE,
  "care_type" text NOT NULL,
  "interval_days" integer NOT NULL,
  "last_performed_at" timestamp with time zone,
  "next_due_at" timestamp with time zone,
  "enabled" boolean NOT NULL DEFAULT true,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX "garden_care_schedules_plant_type_uq" ON "garden_care_schedules" ("plant_id", "care_type");
CREATE INDEX "garden_care_schedules_household_due_idx" ON "garden_care_schedules" ("household_id", "next_due_at");

CREATE TABLE "garden_species_cache" (
  "species_id" text PRIMARY KEY NOT NULL,
  "data" jsonb NOT NULL,
  "cached_at" timestamp with time zone NOT NULL DEFAULT now()
);