drop table if exists numeric_type CASCADE;
create table numeric_type(
name varchar PRIMARY KEY
);
insert into numeric_type values ('smallint');
insert into numeric_type values ('integer');
insert into numeric_type values ('bigint');
insert into numeric_type values ('decimal');
insert into numeric_type values ('numeric');
insert into numeric_type values ('real');
insert into numeric_type values ('double precision');

drop function if exists get_min(varchar , varchar);
create or replace function get_min(tabella_name varchar, column_name varchar)
RETURNS real AS
$$
declare
minimo real;
begin
 EXECUTE 'SELECT min('||column_name||') FROM ' || tabella_name || ' ' INTO minimo;
 RETURN minimo;

return min;
end;
$$
LANGUAGE 'plpgsql';

drop function if exists get_max(varchar , varchar);
create or replace function get_max(tabella_name varchar, column_name varchar)
RETURNS real AS
$$
declare
max real;
begin 
EXECUTE 'SELECT max('||column_name||') FROM ' || tabella_name || ' ' INTO max;
return max;
end;
$$
LANGUAGE 'plpgsql';

create or replace function get_max(tabella_name varchar, column_name varchar)
RETURNS real AS
$$
declare
max real;
begin 
EXECUTE 'SELECT max('||column_name||') FROM ' || tabella_name || ' ' INTO max;
return max;
end;
$$
LANGUAGE 'plpgsql';

--normalize in plpgsql
drop function if exists
normalizza_pgplsql(nometabella varchar , column_not_to_normalize varchar[] , campi_ext_key varchar[]);
create or replace function 
normalizza_pgplsql(nometabella varchar , column_not_to_normalize varchar[] , campi_ext_key varchar[] ) RETURNS real AS
$$
declare
max real;
text_tabelle text;
query text;
colonna varchar;
min real;
maxx real;
maxx_min real;
query_update text;
query_alter text;
tabelle_not_normalize varchar[];
begin
tabelle_not_normalize := array_cat(column_not_to_normalize , campi_ext_key);
text_tabelle := array_to_string(tabelle_not_normalize , ''' , ''');
text_tabelle := ' and column_name not in ( ''' || text_tabelle || ''' )';
query := 'SELECT column_name FROM information_schema.columns WHERE table_name =''' ||  nometabella  ;
query := query || ''' and data_type in (select * from numeric_type)' || text_tabelle  || ';';
--RAISE NOTICE 'Tanto vale column:%', query;
FOR colonna IN EXECUTE(query) LOOP
		begin
		--Alter table portando tutto a doppia precisione in modo da poter fare la media tranquillamente 
		--su ogni campo numerico senza perdere niente
		query_alter = 'ALTER TABLE ' || nometabella || ' ALTER COLUMN ' || colonna || ' TYPE double precision;';
		execute(query_alter);
		--RAISE NOTICE 'Tanto vale colonna:%', colonna;
		EXECUTE 'SELECT min('||colonna||') FROM ' || nometabella || ' ' INTO min;
		EXECUTE 'SELECT max('||colonna||') FROM ' || nometabella || ' ' INTO maxx;
		RAISE NOTICE 'Tanto vale min:% , tanto max%', min , maxx;
		maxx_min := maxx - min;
		if maxx_min > 0 THEN
			execute 'update ' || nometabella || ' SET ' || colonna || ' = (' || colonna || ' - ' || min || ')/' || maxx_min || ';';
		else -- min is 0, avoiding division by zero
			execute 'update ' || nometabella || ' SET ' || colonna || ' = (' || colonna || ' - ' || min || ');';
		end if;
		--execute query_update;
		--RAISE NOTICE 'Tanto vale query_update:%', query_update;
		end;
END LOOP;
return 0;
end;
$$
Language 'plpgsql';




--end normalize pgplsql 

--some test:
--select normalizza('tabella' , ARRAY['chiave1' , 'chiave2']);
--select get_min('tabella' , 'f4');	
--select min(f4) from tabella;

--aggregate procedure






drop table if exists nuovatabella;




 
-- Tell Postgres how to use our aggregate
drop function if exists creatabella(nometabella varchar);
CREATE FUNCTION creatabella(nometabella varchar) RETURNS real AS $$
DECLARE
nometabella information_schema.columns.column_name%type;

BEGIN
for nometabella
	in SELECT column_name FROM information_schema.columns WHERE table_name ='tabella'
	LOOP
	
	END LOOP;


return 0;
END;
$$ LANGUAGE plpgsql;

--drop table if exists nuovatabella cascade;
--drop function if exists _final_mode_varchar(varchar[]) cascade;
--CREATE OR REPLACE FUNCTION _final_mode_varchar(varchar[])
--  RETURNS varchar AS
--$BODY$
--    SELECT a
--    FROM unnest($1) a where a is not NULL
--    GROUP BY 1 
--    ORDER BY COUNT(1) DESC, 1
--    LIMIT 1;
--$BODY$
--LANGUAGE 'sql' IMMUTABLE;

drop function if exists _final_media_numeric(numeric[]) cascade;
CREATE OR REPLACE FUNCTION _final_media_numeric(numeric[])
   RETURNS numeric AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val where val is not NULL
     ORDER BY 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;


drop function if exists _final_media_double(double precision[]) cascade;
CREATE OR REPLACE FUNCTION _final_media_double(double precision[])
   RETURNS double precision AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val where val is not NULL
     ORDER BY 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;


drop function if exists _final_mode_any(anyarray) cascade;
CREATE OR REPLACE FUNCTION _final_mode_any(anyarray)
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest($1) a
    GROUP BY 1 
    ORDER BY COUNT(1) DESC, 1
    LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
 

--
--drop function if exists _final_media_boolean(boolean[]) cascade;
--CREATE OR REPLACE FUNCTION _final_media_boolean(boolean[])
--   RETURNS boolean AS
--$BODY$
--    SELECT a
--    FROM unnest($1) a where a is not NULL
--    GROUP BY 1 
--    ORDER BY COUNT(1) DESC, 1
--    LIMIT 1;
--$BODY$
--LANGUAGE 'sql' IMMUTABLE;


-- Tell Postgres how to use our aggregate
--drop aggregate if exists media(varchar);
--CREATE AGGREGATE media(varchar) (
--  SFUNC=array_append, --Function to call for each row. Just builds the array
--  STYPE=varchar[],
--  FINALFUNC=_final_mode_varchar, --Function to call after everything has been added to array
--  INITCOND='{}' --Initialize an empty array when starting
--);

drop aggregate if exists media(numeric);
CREATE AGGREGATE media(numeric)(
    sfunc = array_append,
    stype = numeric[],
    finalfunc = _final_media_numeric,
    initcond = '{}'

);


drop aggregate if exists media(double precision);
CREATE AGGREGATE media(double precision)(
    sfunc = array_append,
    stype = double precision[],
    finalfunc = _final_media_double,
    initcond = '{}'
);

drop aggregate if exists media(anyarray);
CREATE AGGREGATE media(anyelement) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=anyarray,
  FINALFUNC=_final_mode_any, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);


--drop aggregate if exists media(boolean);
--CREATE AGGREGATE media(boolean)(
--    sfunc = array_append,
--    stype = boolean[],
--    finalfunc = _final_media_boolean,
--    initcond = '{}'
--
--);

drop function if exists cast_external_key( n varchar  , campi_ext_key varchar[]);
CREATE FUNCTION
cast_external_key(nometabella varchar, campi_ext_key varchar[])
  RETURNS integer
AS
$$
declare
begin


return 0;
end;
$$
language 'plpgsql';


drop function if exists
aggregation_procedure_plpgsql( n varchar , nt varchar , campi_group_by_splitted varchar[] , campi_ext_key varchar[]);
CREATE FUNCTION
aggregation_procedure_plpgsql(nometabella varchar, nuovonome varchar, campi_group_by_splitted varchar[] , campi_ext_key varchar[])
  RETURNS integer
AS
$$
declare
colonna varchar;
query_colonne text;
array_colonne varchar[] ;
Query text;
colonne text;
query_create text;
query_index text;
query_alter text;
array_colonne_esterne varchar[];
begin
query_colonne := 'SELECT column_name FROM information_schema.columns WHERE table_name='''||  nometabella || ''';';
FOR colonna in execute(query_colonne) LOOP
	begin
	--RAISE NOTICE 'Tanto vale colonna:%', colonna;
	if (not(ARRAY[colonna] <@ campi_group_by_splitted)) THEN
		--RAISE NOTICE 'Tanto vale colonna:%', colonna;
		array_colonne:= array_append(array_colonne , colonna);
	end if;
	if (array[colonna] <@  campi_ext_key) THEN --colonna è contenuta in quelle esterne aggiungila a quelle da ALTERARE
		array_colonne_esterne := array_append(array_colonne_esterne , colonna);
	end if;
	end;
END LOOP;

--Cast to string all the column that contains external key
if array_length(array_colonne_esterne, 1) is not null then	
FOREACH colonna IN ARRAY array_colonne_esterne
  	LOOP
    	query_alter := 'ALTER TABLE ' ||nometabella ||' ALTER COLUMN ' || colonna ||' TYPE varchar USING CAST('||colonna||' as varchar);';
    	--RAISE NOTICE 'query alter da eseguire: %' , query_alter;
	execute(query_alter);
END LOOP;
end if;

--Cast to string all the columns that belongs to the group by clause
FOREACH colonna IN ARRAY campi_group_by_splitted
  	LOOP
    	query_alter := 'ALTER TABLE ' ||nometabella ||' ALTER COLUMN ' || colonna ||' TYPE varchar USING CAST('||colonna||' as varchar);';
    	--RAISE NOTICE 'query alter da eseguire: %' , query_alter;
	execute(query_alter);
END LOOP;

--RAISE NOTICE 'Tanto vale array:%', array_colonne;


--RAISE NOTICE 'Tanto vale array:%', array_colonne;
if array_length(array_colonne, 1) is not null then	
	Query := 'select ' || array_to_string(campi_group_by_splitted , ' , ') || ' , ' ; 
	Query := Query || ' media(' || array_to_string(array_colonne , ') , media(') || ') from ' || nometabella || ' group by ';
	Query := Query || array_to_string(campi_group_by_splitted , ' , ') || ';';
	RAISE NOTICE 'Query group by vale:%', Query;
	colonne := '( ' || array_to_string(campi_group_by_splitted , ' , ') || ', ' ||array_to_string(array_colonne , ' , ') ||' )' ;
--RAISE NOTICE 'colonne new table vale:%', colonne;
else
	Query := 'select ' || array_to_string(campi_group_by_splitted , ' , ') || ' ' ; 
	Query := Query || ' from ' || nometabella || ' group by ';
	Query := Query || array_to_string(campi_group_by_splitted , ' , ') || ';';
	RAISE NOTICE 'Query group by vale:%', Query;
	colonne := '( ' || array_to_string(campi_group_by_splitted , ' , ') || ' )' ;
--RAISE NOTICE 'colonne new table vale:%', colonne;
end if;
query_create := 'CREATE TABLE ' || nuovonome || ' ' || colonne || ' AS ' || Query ;
--RAISE NOTICE 'create table vale:%', query_create;
execute(query_create);



--FAI L'INDICE SU CAMPI NUOVA TABELLA
FOREACH colonna IN ARRAY campi_group_by_splitted
  	LOOP
    	query_index := 'CREATE INDEX ' || nometabella||'_'||colonna||'_index' || ' ON ' || nuovonome || '(' ||colonna|| ');';
	execute(query_index);
END LOOP;
--NORMALIZZA LA TABELLA MEDIANTE MIN_MAX
Query := 'select normalizza_pgplsql(''' || nuovonome || ''' , ARRAY[''' || array_to_string(campi_group_by_splitted , ''' , ''') || '''] '; --);'
Query := Query || ' , ARRAY[''' || array_to_string(campi_ext_key  , ''' , ''') || '''] );';
RAISE NOTICE 'query normalizza vale:%', Query;
execute(Query);
return 0;
end;
$$
language 'plpgsql';


