Supabase failing migration

20250929064707_happywhale_fixes.sql

DROP FUNCTION happywhale.fetch_encounter;
DROP FUNCTION happywhale.upsert_encounter;

ALTER TABLE happywhale.encounters ALTER COLUMN verbatim_location TYPE VARCHAR(500);

CREATE FUNCTION happywhale.fetch_encounter (IN id integer, OUT encounter jsonb, OUT media jsonb) STRICT
LANGUAGE SQL
STABLE
SET search_path = ''
BEGIN
  ATOMIC
  SELECT
    encounter, media
  FROM
    http.http (('GET', 'https://happywhale.com/app/cs/encounter/full/' || id::text, ARRAY[http.http_header ('Accept', 'application/json')], NULL, NULL)::http.http_request), jsonb_to_record(content::jsonb) AS payload (encounter jsonb,
    media jsonb,
    comments jsonb,
    contributors jsonb,
    sighters jsonb,
    "externalIds" jsonb)
WHERE
  status = 200;

END;

CREATE FUNCTION happywhale.upsert_encounter (
  encounter jsonb, media jsonb
) RETURNS integer 
LANGUAGE SQL VOLATILE set search_path=''
BEGIN ATOMIC
  INSERT INTO happywhale.encounters (
    id, start_date, start_time, end_date, end_time, timezone, verbatim_location, location,
    accuracy, precision_source, individual_id, species_id, min_count, max_count, comments,
    user_id, public, fetched_at
  ) SELECT
    e.id,
    "startDate" AS start_date,
    "startTime" AS start_time,
    "endDate",
    "endTime",
    timezone,
    "verbatimLocation",
    gis.ST_Point((loc.latLng).lng, (loc.latLng).lat) AS location,
    accuracy,
    "precisionSource",
    happywhale.upsert_individual(ind.id, ind."speciesKey", ind."primaryId", ind.nickname, lower(sex)::public.sex) AS individual_id,
    sp.id AS species_id,
    "minCount",
    "maxCount",
    "adminComments",
    happywhale.upsert_user(u.id, u."displayName"),
    public,
    current_timestamp
  FROM
    jsonb_to_record(encounter) AS e (
      id integer,
      "dateRange" jsonb,
      location jsonb,
      individual jsonb,
      species varchar,
      "minCount" integer,
      "maxCount" integer,
      "adminComments" text,
      "user" jsonb,
      "public" boolean
    ) LEFT JOIN happywhale.species AS sp ON sp.code = species,
    jsonb_to_record("dateRange") AS dr ("startDate" date, "startTime" time, "endDate" date, "endTime" time, timezone varchar),
    jsonb_to_record(location) AS loc ("verbatimLocation" varchar, latLng public.lat_lng, accuracy happywhale.accuracy, "precisionSource" varchar),
    jsonb_to_record(individual) AS ind (id integer, "speciesKey" varchar, "primaryId" varchar, nickname varchar, sex varchar),
    jsonb_to_record("user") AS u (id integer, "displayName" varchar)
  ON CONFLICT (id) DO UPDATE SET
    start_date = EXCLUDED.start_date,
    start_time = EXCLUDED.start_time,
    end_date = EXCLUDED.end_date,
    end_time = EXCLUDED.end_time,
    timezone = EXCLUDED.timezone,
    verbatim_location = EXCLUDED.verbatim_location,
    fetched_at = EXCLUDED.fetched_at;
  
  INSERT INTO happywhale.media (id, encounter_id, thumb_url, url, "timestamp", timezone, user_id, license_level, mimetype, public)
  SELECT
    m.id,
    (encounter->'id')::INTEGER,
    "thumbUrl",
    "url",
    "timestamp" AT TIME ZONE 'UTC',
    "timezone",
    happywhale.upsert_user(("user"->'id')::integer, "user"->>'displayName'),
    "licenseLevel",
    "mimetype",
    "public"
  FROM jsonb_to_recordset($2) AS mm (media jsonb), jsonb_to_record(mm.media) AS m (
    id INTEGER,
    "thumbUrl" VARCHAR,
    "url" VARCHAR,
    "timestamp" timestamp,
    "timezone" VARCHAR,
    "type" VARCHAR,
    "user" jsonb,
    "licenseLevel" VARCHAR,
    "origFilename" VARCHAR,
    "mimetype" VARCHAR,
    "public" BOOLEAN
  )
  ON CONFLICT (id) DO UPDATE SET
    license_level=EXCLUDED.license_level,
    "public"=EXCLUDED.public;
  
  SELECT (encounter->'id')::integer;
END;