
CREATE OR REPLACE FUNCTION regenerate_gateway_lastseen() RETURNS VOID AS
$$
DECLARE
  measure_partition TEXT;
  valueless_measure_partition TEXT;
BEGIN
  /* Create a table that will eventually replace the existing GatewayLastSeen table */
  CREATE TABLE IF NOT EXISTS GatewayLastSeenNew (
    GatewayId VARCHAR(255) NOT NULL DEFAULT '',
    Time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (GatewayId)
  );

  /* Loop through all partitions of the Measure table and populate GatewayLastSeenNew for each Gateway */
  FOR measure_partition IN
    SELECT INHRELID::REGCLASS
    FROM PG_CATALOG.PG_INHERITS
    WHERE INHPARENT = 'measure'::REGCLASS
    ORDER BY INHRELID::REGCLASS ASC
  LOOP
    EXECUTE FORMAT(
      'INSERT INTO GatewayLastSeenNew(GatewayId, Time)
        SELECT GatewayId, MAX(Time) AS max_time
        FROM %1$s
        GROUP BY GatewayId
      ON CONFLICT (GatewayId) DO UPDATE SET Time = EXCLUDED.Time WHERE GatewayLastSeenNew.GatewayId = EXCLUDED.GatewayId AND GatewayLastSeenNew.Time < EXCLUDED.Time;',
      measure_partition
    );
  END LOOP;

  /* Loop through all partitions of the ValuelessMeasure table and populate GatewayLastSeenNew for each Gateway */
  FOR valueless_measure_partition IN
    SELECT INHRELID::REGCLASS
    FROM PG_CATALOG.PG_INHERITS
    WHERE INHPARENT = 'valuelessmeasure'::REGCLASS
    ORDER BY INHRELID::REGCLASS ASC
  LOOP
    EXECUTE FORMAT(
      'INSERT INTO GatewayLastSeenNew(GatewayId, Time)
        SELECT GatewayId, MAX(Time) AS max_time
        FROM %1$s
        GROUP BY GatewayId
      ON CONFLICT (GatewayId) DO UPDATE SET Time = EXCLUDED.Time WHERE GatewayLastSeenNew.GatewayId = EXCLUDED.GatewayId AND GatewayLastSeenNew.Time < EXCLUDED.Time;',
      valueless_measure_partition
    );
  END LOOP;

  /* Populate GatewayLastSeenNew for each Gateway in GatewayMetricsPacket */
  INSERT INTO GatewayLastSeenNew(GatewayId, Time)
    SELECT GatewayId, MAX(Time) AS max_time
    FROM GatewayMetricsPacket
    GROUP BY GatewayId
  ON CONFLICT (GatewayId) DO UPDATE SET Time = EXCLUDED.Time WHERE GatewayLastSeenNew.GatewayId = EXCLUDED.GatewayId AND GatewayLastSeenNew.Time < EXCLUDED.Time;

  /* Populate GatewayLastSeenNew for each Gateway in GatewayTestPacket */
  INSERT INTO GatewayLastSeenNew(GatewayId, Time)
    SELECT GatewayId, MAX(Time) AS max_time
    FROM GatewayTestPacket
    GROUP BY GatewayId
  ON CONFLICT (GatewayId) DO UPDATE SET Time = EXCLUDED.Time WHERE GatewayLastSeenNew.GatewayId = EXCLUDED.GatewayId AND GatewayLastSeenNew.Time < EXCLUDED.Time;

  /* Set aside the current GatewayLastSeen table (after dropping the table that
   * was set aside the last time this function ran)
   * - The ALTER TABLE statement acquires an exclusive lock on the GatewayLastSeen table */
  DROP TABLE IF EXISTS GatewayLastSeenOld;
  ALTER TABLE GatewayLastSeen RENAME TO GatewayLastSeenOld;

  /* Move the new table into place */
  ALTER TABLE GatewayLastSeenNew RENAME TO GatewayLastSeen;
  RETURN;
END;
$$
LANGUAGE plpgsql VOLATILE
SECURITY definer;
