CREATE TABLE mesure_gauge ( -- TODO ; revoir procédures d'injection des données pour faire la différence entre gauge et compteur
  "date" TIMESTAMPTZ,
  id_captation INT NOT NULL,
  valeur float4 NOT NULL
) WITH (
  tsdb.hypertable,
  timescaledb.segmentby = 'id_captation',
  timescaledb.orderby='date DESC'
);

INSERT INTO mesure_gauge ("date", id_captation, valeur)
  SELECT m."date", m.id_captation, m.valeur
    FROM mesure m
    WHERE m.id_captation IN (
      SELECT c.id_captation
        FROM captation c
        WHERE c."type" = 'G' -- TODO : prévoir cette info dans captation
    );

SELECT add_retention_policy('mesure_gauge', INTERVAL '6 months'); -- on ne garde les données brutes que 6 mois

CREATE MATERIALIZED VIEW mesure_gauge_heure -- valeurs par heure
WITH (timescaledb.continuous) AS
SELECT 
  id_captation,
  time_bucket(INTERVAL '1 HOUR', mg."date") AS "date",
  time_weight('linear', mg."date", mg.valeur) AS valeur_temporelle,
  MAX(mg.valeur),
  MIN(mg.valeur)
FROM mesure_gauge mg
GROUP BY 1, 2;

SELECT add_continuous_aggregate_policy( -- On met à jour les données tt les h en relisant les données brutes entre 1 mois et 1 jour
  'mesure_gauge_heure',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 hour'
);

SELECT add_retention_policy('mesure_gauge_heure', INTERVAL '5 years'); -- on ne garde les données par h que 5 ans

CREATE MATERIALIZED VIEW mesure_gauge_jour -- valeurs par jour
WITH (timescaledb.continuous) AS
SELECT 
  id_captation,
  time_bucket(INTERVAL '1 DAY', mg."date") AS "date",
  rollup(valeur_temporelle) AS valeur_temporelle,
  MAX(mg.max),
  MIN(mg.min)
FROM mesure_gauge_heure mg
GROUP BY 1, 2;

SELECT add_continuous_aggregate_policy( -- On met à jour les données tt les h en relisant les données brutes entre 1 mois et 1 jour
  'mesure_gauge_jour',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 hour'
);

SELECT add_retention_policy('mesure_gauge_jour', INTERVAL '20 years'); -- on ne garde les données par h que 20 ans

-- ex. select
select 
  mgh.id_captation,
  time_bucket(INTERVAL '1 DAY', mgh."date") AS "date", -- 1 jour et pas seulement une heure
  average(rollup(valeur_temporelle)) AS moyenne -- rollup pour avour le concaténation des valeur_temporelle
from mesure_gauge_heure mgh
where mgh.id_captation = 59 -- puissance PAC
group by 1, 2
order by "date" desc
limit 1000;