set search_path to temp_agg;

--- sum  
create or replace function temporal_sum_next (
    accum double precision, 
    val double precision, 
    val_s timestamptz,
    val_e timestamptz,
    q_s  timestamptz,
    q_e timestamptz)
    returns double precision
IMMUTABLE language SQL 
return case 
 when q_s = q_e and val_s <= q_s and q_s < val_e 
    and val is not null
 then coalesce(accum,0) + val
 when (val_s, val_e) overlaps (q_s,q_e) and q_s < q_e and val is not null 
 then
      coalesce(accum,0) + val *
       EXTRACT(EPOCH FROM least(val_e, q_e) - greatest(val_s,q_s)) /
       EXTRACT(EPOCH FROM q_e-q_s)
 else accum
 end;

drop AGGREGATE if exists temporal_sum (
    double precision, timestamptz, timestamptz, timestamptz, timestamptz);
CREATE AGGREGATE temporal_sum (
    double precision, timestamptz, timestamptz, timestamptz, timestamptz
    ) (
    sfunc= temporal_sum_next,
    STYPE = double precision
    );

---- COUNT
create or replace function temporal_count_next (
    accum double precision, 
    val double precision, 
    val_s timestamptz,
    val_e timestamptz,
    q_s  timestamptz,
    q_e timestamptz)
    returns double precision
IMMUTABLE language SQL 
return case 
 when q_s = q_e and val_s <= q_s and q_s < val_e and val is not null
 then coalesce(accum,0::double precision) + 1::double precision
 when (val_s, val_e) overlaps (q_s,q_e) and q_s < q_e and val is not null 
 then
    coalesce(accum,0::double precision) + 
       EXTRACT(EPOCH FROM least(val_e, q_e) - greatest(val_s,q_s)) /
       EXTRACT(EPOCH FROM q_e-q_s)
 else accum
 end;

drop AGGREGATE if exists temporal_count (
    double precision, timestamptz, timestamptz, timestamptz, timestamptz);
   
CREATE AGGREGATE temporal_count (
    double precision, timestamptz, timestamptz, timestamptz, timestamptz
    ) (
    sfunc= temporal_count_next,
    STYPE = double precision
    );

---  Accumulate
create or replace function temporal_accum_next (
    accum double precision, 
    val double precision, 
    per interval,
    val_s timestamptz,
    val_e timestamptz,
    q_s  timestamptz,
    q_e timestamptz)
    returns double precision
IMMUTABLE language SQL 
return case 
 when q_s = q_e and val_s <= q_s and q_s < val_e and val is not null
 then  0::double precision
when (val_s, val_e) overlaps (q_s,q_e) and q_s < q_e and val is not null 
 then
      coalesce(accum,0::double precision) + val *
       EXTRACT(EPOCH FROM least(val_e, q_e) - greatest(val_s,q_s)) /
       EXTRACT(EPOCH FROM per) 
 else accum
 end;

DO $$ BEGIN
   
CREATE AGGREGATE temporal_accum (
    double precision, interval, timestamptz, timestamptz, timestamptz, timestamptz
    ) (
    sfunc= temporal_accum_next,
    STYPE = double precision
    );
EXCEPTION
    WHEN duplicate_function THEN NULL;
END $$;
   
---- average
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'temporal_avg_state') THEN
         create type temporal_avg_state as (
            state_sum double precision,
            state_cnt double precision);
    END IF;
    --more types here...
END$$;

create or replace function temporal_avg_next (
    accum temporal_avg_state, 
    val double precision, 
    val_s timestamptz,
    val_e timestamptz,
    q_s  timestamptz,
    q_e timestamptz)
    returns temporal_avg_state
IMMUTABLE language SQL 
 return case 
 when q_s = q_e and val_s <= q_s and q_s < val_e and val is not null
 then (
  coalesce(accum.state_sum,0) + val,
  coalesce(accum.state_cnt,0) + 1
  )::temporal_avg_state 
 when (val_s, val_e) overlaps (q_s,q_e) and q_s < q_e and val is not null 
 then (
    coalesce(accum.state_sum,0) + val *
       EXTRACT(EPOCH FROM least(val_e, q_e) - greatest(val_s,q_s)) /
       EXTRACT(EPOCH FROM q_e-q_s),
    coalesce(accum.state_cnt,0) +
       EXTRACT(EPOCH FROM least(val_e, q_e) - greatest(val_s,q_s)) /
       EXTRACT(EPOCH FROM q_e-q_s)
       )::temporal_avg_state 
 else accum
 end;

create or replace function temporal_avg_final(
     s temporal_avg_state) 
returns double precision
IMMUTABLE language SQL 
return 
   case when s.state_sum is not null and s.state_cnt <> 0 
   then s.state_sum / s.state_cnt 
   else null end; 

drop AGGREGATE if exists temporal_avg (
    double precision, timestamptz, timestamptz, timestamptz, timestamptz);
CREATE AGGREGATE temporal_avg (
    double precision, timestamptz, timestamptz, timestamptz, timestamptz
    ) (
    sfunc= temporal_avg_next,
    STYPE = temporal_avg_state,
    FINALFUNC = temporal_avg_final
    );