CREATE OR REPLACE FUNCTION populate_shape()
RETURNS TRIGGER AS $$
DECLARE
  polygon_text TEXT;
BEGIN
  -- For polygon zones: build WKT POLYGON from JSON paths
   IF NEW.zone_type = 'polygonal' AND NEW.paths IS NOT NULL THEN
    polygon_text := 'POLYGON((' ||
      (
        SELECT string_agg(
                 (p->>'lng')::text || ' ' || (p->>'lat')::text,
                 ', '
               )
        FROM jsonb_array_elements(NEW.paths) AS p
      )
      || ', ' ||
      (
        SELECT (p->>'lng')::text || ' ' || (p->>'lat')::text
        FROM jsonb_array_elements(NEW.paths) AS p
        LIMIT 1
      ) -- close polygon by repeating first point
      || '))';

    NEW.shape := ST_GeomFromText(polygon_text, 4326);
  END IF;

  -- For circular zones: buffer a point with radius (in meters)
   IF NEW.zone_type = 'circular'
     AND NEW.latitude_coords IS NOT NULL
     AND NEW.longitude_coords IS NOT NULL
     AND NEW.radius IS NOT NULL THEN
    NEW.shape := ST_Buffer(
      ST_SetSRID(ST_Point(
        NEW.longitude_coords::double precision,
        NEW.latitude_coords::double precision
      ), 4326)::geography,
      NEW.radius::double precision * 1000  -- km → meters
    )::geometry;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS geofence_shape_trigger ON geofences;

CREATE TRIGGER geofence_shape_trigger
BEFORE INSERT OR UPDATE ON geofences
FOR EACH ROW
EXECUTE FUNCTION populate_shape();


