<commit_before>CREATE OR REPLACE FUNCTION get_last_sc_update(ScID bigint)
  RETURNS jsonb AS
  -- TODO: add feature with deleted geom ids
$func$
DECLARE
  features jsonb;
  geometry_output jsonb;
  lastTime TIMESTAMP;
BEGIN
  IF (SELECT count(*) < 1 FROM scenario WHERE scenario.id = ScID) THEN
    RAISE EXCEPTION 'Nonexistent scenario (ScID = %)', ScID
          USING HINT = 'You are trying to get a scenario that has not ever existed!';
  END IF;

  -- get the time of the last scenario update
  lastTime := greatest
    ( (SELECT max(last_update) FROM sc_geometry WHERE scenario_id = ScID)
    , (SELECT max(last_update) FROM sc_geometry_prop WHERE scenario_id = ScID)
    , (SELECT max(last_update) FROM scenario_prop WHERE scenario_id = ScID)
    );

  SELECT jsonb_build_object(
        'type',     'FeatureCollection',
        'features', jsonb_agg(fcs.feature)
        ) as FeatureCollection
    FROM (
        SELECT jsonb_build_object(
          'type',       'Feature',
          'id',         CAST(g.id AS text),
          'geometry',   ST_AsGeoJSON(ST_Transform(g.geom, (SELECT srid FROM scenario WHERE id = ScID)))::jsonb,
          'properties', coalesce(
                        ( SELECT jsonb_object_agg(ph.name, ph.value)
                            FROM sc_geometry_prop_history ph
                           WHERE ph.ts_update = lastTime
                             AND ph.scenario_id = ScID
                             AND ph.geometry_id = g.id
                             AND ph.alive
                        ), '{}') || jsonb_build_object('geomID' , g.id)
        ) as feature
        FROM (SELECT gh.*
                FROM sc_geometry_history gh
               WHERE gh.ts_update = lastTime
                 AND gh.scenario_id = ScID
                 AND gh.alive) g
        WHERE g.geom IS NOT NULL
        ) fcs
    INTO features;

  geometry_output := jsonb_build_object
    ( 'format'       , 'GeoJSON'
    , 'name'         , (SELECT name FROM scenario WHERE id = ScID)
    , 'ScID'         , ScID
    , 'geometry'     , features
    , 'properties'   , ( SELECT jsonb_object_agg(ph.name, ph.value)
                           FROM scenario_prop_history ph
                          WHERE ph.ts_update = lastTime
                            AND ph.scenario_id = ScID
                            AND ph.alive )
    );

  RETURN jsonb_build_object
    ( 'geometry_output' , geometry_output
    , 'ScID'            , ScID
    , 'created'         , (SELECT round(EXTRACT(epoch FROM MIN(ts_update))) FROM sc_geometry_history WHERE scenario_id = ScID)
    , 'lastmodified'    , round(EXTRACT(epoch FROM lastTime))
    );
END;
$func$ LANGUAGE plpgsql;<commit_msg>Added srid and lat/lon to update messages<commit_after>CREATE OR REPLACE FUNCTION get_last_sc_update(ScID bigint)
  RETURNS jsonb AS
  -- TODO: add feature with deleted geom ids
$func$
DECLARE
  features jsonb;
  geometry_output jsonb;
  lastTime TIMESTAMP;
BEGIN
  IF (SELECT count(*) < 1 FROM scenario WHERE scenario.id = ScID) THEN
    RAISE EXCEPTION 'Nonexistent scenario (ScID = %)', ScID
          USING HINT = 'You are trying to get a scenario that has not ever existed!';
  END IF;

  -- get the time of the last scenario update
  lastTime := greatest
    ( (SELECT max(last_update) FROM sc_geometry WHERE scenario_id = ScID)
    , (SELECT max(last_update) FROM sc_geometry_prop WHERE scenario_id = ScID)
    , (SELECT max(last_update) FROM scenario_prop WHERE scenario_id = ScID)
    );

  SELECT jsonb_build_object(
        'type',     'FeatureCollection',
        'features', jsonb_agg(fcs.feature)
        ) as FeatureCollection
    FROM (
        SELECT jsonb_build_object(
          'type',       'Feature',
          'id',         CAST(g.id AS text),
          'geometry',   ST_AsGeoJSON(ST_Transform(g.geom, (SELECT srid FROM scenario WHERE id = ScID)))::jsonb,
          'properties', coalesce(
                        ( SELECT jsonb_object_agg(ph.name, ph.value)
                            FROM sc_geometry_prop_history ph
                           WHERE ph.ts_update = lastTime
                             AND ph.scenario_id = ScID
                             AND ph.geometry_id = g.id
                             AND ph.alive
                        ), '{}') || jsonb_build_object('geomID' , g.id)
        ) as feature
        FROM (SELECT gh.*
                FROM sc_geometry_history gh
               WHERE gh.ts_update = lastTime
                 AND gh.scenario_id = ScID
                 AND gh.alive) g
        WHERE g.geom IS NOT NULL
        ) fcs
    INTO features;

  geometry_output := jsonb_build_object
    ( 'format'       , 'GeoJSON'
    , 'name'         , (SELECT name FROM scenario WHERE id = ScID)
    , 'ScID'         , ScID
    , 'srid'         , (SELECT srid FROM scenario WHERE id = ScID)
    , 'lon'          , (SELECT lon  FROM scenario WHERE id = ScID)
    , 'lat'          , (SELECT lat  FROM scenario WHERE id = ScID)
    , 'alt'          , (SELECT alt  FROM scenario WHERE id = ScID)
    , 'geometry'     , features
    , 'properties'   , ( SELECT jsonb_object_agg(ph.name, ph.value)
                           FROM scenario_prop_history ph
                          WHERE ph.ts_update = lastTime
                            AND ph.scenario_id = ScID
                            AND ph.alive )
    );

  RETURN jsonb_build_object
    ( 'geometry_output' , geometry_output
    , 'ScID'            , ScID
    , 'created'         , (SELECT round(EXTRACT(epoch FROM MIN(ts_update))) FROM sc_geometry_history WHERE scenario_id = ScID)
    , 'lastmodified'    , round(EXTRACT(epoch FROM lastTime))
    );
END;
$func$ LANGUAGE plpgsql;
{'fraction_non_alphanumeric': 0.08412698412698413, 'fraction_numerical': 0.0003527336860670194, 'mean_word_length': 1.841182364729459, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4542737', 'n_tokens_mistral': 1699, 'n_tokens_neox': 1651, 'n_words': 467}