/**
 * @author Titouan via Stéphane
 * @date 15/07/21
 * @version 1.0.0
 * @project SIEau
 * @table function
 */

# --- !Ups

create or replace function f_calc_volume_reel(p_idCapteur INTEGER, p_coeffLecture INTEGER, p_dateDebut TIMESTAMP with time zone, p_dateFin TIMESTAMP with time zone)
returns REAL
language plpgsql
as $$
DECLARE
	v_dateIndex1 TIMESTAMP with time zone;;
	v_index1 REAL;;
	v_dateIndex2 TIMESTAMP with time zone;;
	v_index2 REAL;;
BEGIN
	-- Premier index connu
	p_dateDebut = date_trunc('day', p_dateDebut) + INTERVAL '1 day';;
	raise notice 'f_calc_volume_reel date début: %', p_dateDebut;;
	select dateMesure, mesure into v_dateIndex1, v_index1 from MAT_CHRONIQUES where idmateriel=p_idCapteur and typemesure=2 and dateMesure<p_dateDebut ORDER BY dateMesure DESC LIMIT 1;;
	raise notice 'f_calc_volume_reel date index1 : %', v_dateIndex1;;
	raise notice 'f_calc_volume_reel index1 : %', v_index1;;

	-- Dernier index connu
	select dateMesure, mesure into v_dateIndex2, v_index2 from MAT_CHRONIQUES where idmateriel=p_idCapteur and typemesure=2 and dateMesure<=p_dateFin ORDER BY dateMesure DESC LIMIT 1;;
	raise notice 'f_calc_volume_reel date index2 : %', v_dateIndex2;;
	raise notice 'f_calc_volume_reel index2 : %', v_index2;;

	RETURN (v_index2 - v_index1)*p_coeffLecture;;
END;;
$$ ;

# --- !Downs
