schema.sql

-- WARNING: This schema is for context only and is not meant to be run.
-- Table order and constraints may not be valid for execution.

CREATE TABLE public.plants (
  id integer GENERATED ALWAYS AS IDENTITY NOT NULL,
  name_gr character varying NOT NULL,
  name_sci character varying,
  light_level smallint,
  image_url character varying,
  CONSTRAINT plants_pkey PRIMARY KEY (id)
);
CREATE TABLE public.plant_stages (
  id integer GENERATED ALWAYS AS IDENTITY NOT NULL,
  plant_id integer NOT NULL,
  stage_order smallint NOT NULL,
  stage_name character varying NOT NULL,
  duration_days integer,
  soil_moisture_min smallint NOT NULL,
  soil_moisture_max smallint NOT NULL,
  air_humidity_max smallint,
  temp_min_c numeric,
  temp_max_c numeric,
  CONSTRAINT plant_stages_pkey PRIMARY KEY (id),
  CONSTRAINT plant_stages_plant_id_fkey FOREIGN KEY (plant_id) REFERENCES public.plants(id)
);
CREATE TABLE public.active_zones (
  zone_id integer NOT NULL,
  plant_id integer NOT NULL,
  current_stage_id integer NOT NULL,
  custom_name character varying,
  date_planted date NOT NULL DEFAULT CURRENT_DATE,
  CONSTRAINT active_zones_pkey PRIMARY KEY (zone_id),
  CONSTRAINT active_zones_plant_id_fkey FOREIGN KEY (plant_id) REFERENCES public.plants(id),
  CONSTRAINT active_zones_current_stage_id_fkey FOREIGN KEY (current_stage_id) REFERENCES public.plant_stages(id)
);
CREATE TABLE public.greenhouse_readings (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  zone_id integer NOT NULL,
  plant_id integer,
  stage_id integer,
  soil_raw integer,
  soil_percent integer,
  light_raw integer,
  temp_c numeric,
  hum_percent numeric,
  pump_on boolean DEFAULT false,
  fan_on boolean DEFAULT false,
  lamp_on boolean DEFAULT false,
  needs_watering boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT greenhouse_readings_pkey PRIMARY KEY (id),
  CONSTRAINT greenhouse_readings_zone_id_fkey FOREIGN KEY (zone_id) REFERENCES public.active_zones(zone_id),
  CONSTRAINT greenhouse_readings_plant_id_fkey FOREIGN KEY (plant_id) REFERENCES public.plants(id),
  CONSTRAINT greenhouse_readings_stage_id_fkey FOREIGN KEY (stage_id) REFERENCES public.plant_stages(id)
);