CREATE TABLE dwh.fact_measures_raw (
	id serial NOT NULL,
	equipment_id varchar NULL,
	parameter_id varchar NULL,
	value float8 NULL,
	ts_original int8 NULL,
	ts_timestamp timestamp NULL,
	created_at timestamp NULL DEFAULT now(),
	updated_at timestamp NULL DEFAULT now()
);
CREATE INDEX idx_ts_timestamp ON dwh.fact_measures_raw USING btree (ts_timestamp);

CREATE OR REPLACE FUNCTION dwh.fact_raw_insert_function()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare
	partition_date text;

partition_name text;

start_of_hour text;

end_of_next_hour text;


begin partition_date := to_char(NEW.ts_timestamp, 'YYYY-MM-DD-HH');

partition_name := 'dwh.raw_data_' || partition_date;

start_of_hour := to_char((NEW.ts_timestamp), 'YYYY-MM-DD HH:DD:SS') || '-01';

end_of_next_hour := to_char((NEW.ts_timestamp + interval '1 hour'), 'YYYY-MM-DD HH:DD:SS') || '-01';

if not exists (
select
	1
from
	information_schema.tables
where
	table_name = partition_name) then raise notice 'A partition has been created %',
partition_name;

execute format(E'CREATE TABLE %I (CHECK ( date_trunc(\'hour\', ts_timestamp) >= ''%s'' AND date_trunc(\'hour\', ts_timestamp) < ''%s'')) 
INHERITS (dwh.fact_measures_raw)',
partition_name,
start_of_hour,
end_of_next_hour);


 end if;

execute format('INSERT INTO %I (equipment_id,parameter_id,value,ts_original,ts_timestamp) VALUES($1,$2,$3,$4,$5)',
partition_name)
	using NEW.equipment_id,new.parameter_id,new.value,new.ts_original,NEW.ts_timestamp;

return null;

end $function$;

CREATE TRIGGER fact_raw_insert
    BEFORE INSERT ON dwh.fact_measures_raw
    FOR EACH ROW EXECUTE PROCEDURE dwh.fact_raw_insert_function();

begin partition_date := to_char(NEW.ts_timestamp, 'YYYY-MM-DD-HH24');

partition_name := 'dwh.raw_data_' || partition_date;

start_of_hour := to_char((NEW.ts_timestamp), 'YYYY-MM-DD HH24:DD:SS') || '-01';

end_of_next_hour := to_char((NEW.ts_timestamp + interval '1 hour'), 'YYYY-MM-DD HH24:DD:SS') || '-01';

CREATE TABLE dwh.fact_measures_raw (
	id serial NOT NULL,
	equipment_id varchar NULL,
	parameter_id varchar NULL,
	value float8 NULL,
	ts_original int8 NULL,
	ts_timestamp timestamp NULL,
	created_at timestamp NULL DEFAULT now(),
	updated_at timestamp NULL DEFAULT now()
) partition by range (ts_timestamp);

CREATE OR REPLACE PROCEDURE dwh.fact_raw_measures_partitions()
 LANGUAGE plpgsql
AS $procedure$
declare
	partition_date text;
	partition_name text;
	start_date text;
	end_date text;
	i int;
	tble_name text;
	indx_name text;
begin
	i :=0;
	
	loop 
		exit when i>6 ;
		i:=i+1;
		
		partition_date := to_char((current_timestamp +  (i || ' day')::interval), 'YYYY_MM_DD');
		partition_name :=  'dwh.fact_measures_raw_' || partition_date;
		start_date := to_char((current_timestamp+ (i || ' day')::interval), 'YYYY-MM-DD');
		end_date := to_char((current_timestamp+ (i+1 || ' day')::interval), 'YYYY-MM-DD');
		tble_name = 'fact_measures_raw_'|| partition_date;
		indx_name = 'idx_ts_timestamp' || partition_date;

		if not exists (select 1 from information_schema.tables where table_name= tble_name ) then
		EXECUTE format('CREATE TABLE %s partition of dwh.fact_measures_raw for values from (%L) to (%L)',
				partition_name, start_date, end_date);
		execute format('CREATE INDEX %s ON %s USING btree (ts_timestamp);',indx_name,partition_name);

		end if;

	end loop ;
end;
$procedure$
;