DO 
$BODY$
BEGIN
	
	IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'aluvisa') THEN
      CREATE USER aluvisa WITH ENCRYPTED PASSWORD 'aluvisa' LOGIN SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
    END IF;
   
   
	IF NOT EXISTS (SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace WHERE spcname = 'ARTICHISTORICAL') THEN
      CREATE TABLESPACE "ARTICHISTORICAL" OWNER aluvisa LOCATION 'D:\database\ARTICHISTORICAL';
    END IF;
	
	ALTER TABLESPACE "ARTICHISTORICAL" OWNER TO aluvisa;
	
	IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'ARTICHISTORICAL') THEN
      CREATE DATABASE "ARTICHISTORICAL"  WITH OWNER = aluvisa TEMPLATE = 'template0' ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = "ARTICHISTORICAL"  CONNECTION LIMIT = -1;
    END IF;
	
     
END
$BODY$;



CREATE UNLOGGED TABLE IF NOT EXISTS measurement 
(
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) 
PARTITION BY RANGE (logdate);


CREATE UNLOGGED TABLE IF NOT EXISTS measurement_2018 PARTITION OF measurement FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE UNLOGGED TABLE IF NOT EXISTS measurement_2019 PARTITION OF measurement FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE UNLOGGED TABLE IF NOT EXISTS measurement_2020 PARTITION OF measurement FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');



CREATE INDEX IF NOT EXISTS index_logdate ON measurement_2018 (logdate);
CREATE INDEX IF NOT EXISTS index_logdate ON measurement_2019 (logdate);
CREATE INDEX IF NOT EXISTS index_logdate ON measurement_2020 (logdate);






CREATE OR REPLACE PROCEDURE measurement_creator(in inputtimestamp bigint)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

 subtable VARCHAR(16);
 timestamp1 VARCHAR(16);
 timestamp2 VARCHAR(16);
 usetimestamp bigint;
 
BEGIN

 	IF (inputtimestamp = 0) then
		SELECT extract(epoch from now() AT TIME ZONE 'UTC') into usetimestamp::bigint;
    	ELSE
		usetimestamp := inputtimestamp;
	END IF;

	raise notice 'Value: %', usetimestamp;

	subtable :=  to_char(to_timestamp(usetimestamp), 'YYYY_MM');
	timestamp1 :=  to_char(to_timestamp(usetimestamp), 'YYYY-MM') || '-01';
	timestamp2 :=  to_char(to_timestamp(usetimestamp) + interval '1 month' , 'YYYY-MM') || '-01';


	raise notice 'Value: %', subtable;
	raise notice 'Value: %', timestamp1;
	raise notice 'Value: %', timestamp2;
	

	EXECUTE 'CREATE TABLE IF NOT EXISTS measurement_' || subtable || ' PARTITION OF measurement FOR VALUES FROM (' || quote_literal(timestamp1) || ') TO ('  || quote_literal(timestamp2) || ')';
	
	
END;
$BODY$;