SELECT cron.schedule('calculate-a1c', '15 seconds', $$
WITH gtime AS (
  SELECT MAX("time") AS time FROM "glucose-processed-a1c"
),
stime AS MATERIALIZED (
  SELECT MIN("glucose-processed"."time") AS time FROM "glucose-processed"
),
 smoothed  AS MATERIALIZED (
         SELECT MIN("glucose-processed"."time") as time,
            AVG("glucose-processed".absvalue) AS absvalue
           FROM "glucose-processed"
                   LEFT JOIN "stime" ON 1=1
                   LEFT JOIN "gtime" ON 1=1
                   WHERE "glucose-processed"."time" >= (COALESCE(gtime.time, stime.time) - '3.1 months'::interval)
           GROUP BY date_trunc('hour', "glucose-processed"."time") + date_part('minute', "glucose-processed"."time")::int / 5 * interval '5 min'
           ORDER BY 1 ASC
), 
avgs AS (
    SELECT smoothed.time,
            avg(absvalue) OVER (ORDER BY "time" RANGE BETWEEN '3 mons'::interval PRECEDING AND CURRENT ROW) AS "3mo_eabg",
            avg(absvalue) OVER (ORDER BY "time" RANGE BETWEEN '7 days'::interval PRECEDING AND CURRENT ROW) AS "7day_eabg"
      FROM smoothed
          ORDER BY smoothed."time" ASC
        )
INSERT INTO "glucose-processed-a1c"
 SELECT a."time",
    "3mo_eabg",
    "7day_eabg",
    ("3mo_eabg"  + 46.7) / 28.7 AS "3mo_ea1c",
    ("7day_eabg" + 46.7) / 28.7 AS "7day_ea1c"
   FROM avgs AS a
   LEFT JOIN "stime" ON 1=1
   LEFT JOIN "gtime" ON 1=1
   WHERE a."time" >= COALESCE(gtime.time, stime.time)
   ORDER BY a."time" ASC
   LIMIT 1000
ON CONFLICT ("time") DO NOTHING
$$);