CREATE OR REPLACE FUNCTION sp_Generate_SpreadTables()
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
declare _i int; _maxLvl int; _sql text; _sql_type_1 text; _sql_type_2 text;
Begin
	create temp table EntHierarchy as
	with recursive CTE as 
	(
		SELECT EntityId, BaseEntity, 0 as Lvl, EntName
		FROM VCLib.T_ENTITIES
		WHERE BaseEntity is null
		UNION All
		SELECT e.EntityId, e.BaseEntity, CTE.Lvl + 1, e.EntName
		FROM VCLib.T_ENTITIES as e
		JOIN CTE
		ON e.BaseEntity = CTE.EntityId
	)
	select* from CTE;
	
	_i := 1;
	_maxLvl := (select max(lvl) from EntHierarchy);
	create temp table BaseEntity(father, son, BaseEntity, Entity)
	as 
	select e.entityid, e.entityid, b.Schema || '.' || b.EntName, b.Schema || '.' || b.EntName
	from EntHierarchy e 
	inner join Vclib.T_Entities b on b.EntityID = e.EntityID
	where e.lvl = 0;
	while _i <= _maxLvl
	loop
		insert into BaseEntity(father, son, BaseEntity, Entity)
		select be.father, e.EntityID, be.BaseEntity,  b.Schema || '.' || b.EntName
		from EntHierarchy e
		inner join BaseEntity be on e.BaseEntity = be.son
		inner join Vclib.T_Entities b on b.EntityID = e.EntityID
		where e.lvl = _i;
		_i = _i + 1;
	end loop;
	
	for _sql in 
	select ' create table if not exists ' 
            || e.Schema || '.F_' 
            || e.EntName || '_' 
            || ea.Name || '(' 
            || 'ID '
            || case 
                    when col_pk.data_type is null then case
                                                    when ea.SystemType in ('int', 'bigint') then ea.SystemType
                                                    when ea.SystemType = 'guid' then 'uuid'
                                                    when /*@isNvarchar = 0 and*/ ea.SystemType = 'string' then 'varchar(' || cast(ea.MaxLength as varchar(5)) || ')'
                                                    --when @isNvarchar = 1 and ea.SystemType = N'string' then N'nvarchar(' + cast(ea.[MaxLength] as nvarchar(5)) + N')'
                                                end
                    else col_pk.data_type || case when col_pk.data_type in ('decimal', 'numeric') -- decimal, numeric
                                              then '(' || col_pk.numeric_precision::text || ', ' || col_pk.numeric_scale::text || ')'
                                          when col_pk.data_type in ('varchar', 'char')
                                              then '(' || col_pk.character_maximum_length::text || ')'
                                          else ''
                                     end
              end
            || case when ea.IsPrimaryKey = '1' then '' else ', value ' end
            || case when ea.IsPrimaryKey = '1' then ''
                    when ea.AttributeType = 4 -- Choice
                    then 'int'
                    when ea.AttributeType = 0 -- Attribute
                    then case when ea.SystemType in ('int', 'bigint', 'bool', 'boolean', 'date')					
                              then ea.SystemType
							  when ea.SystemType = 'datetime'
							  then 'timestamp'
                              when ea.SystemType = 'guid'
                              then 'uuid'
                              when /*@isNvarchar = 0 and*/ ea.SystemType = 'text' or (ea.SystemType = 'string' and ea.MaxLength is null)
                              then 'text'
                              --when @isNvarchar = 1 and ea.SystemType = N'text' or (ea.SystemType = N'string' and ea.[MaxLength] is null)
                              --then N'nvarchar(max)'
                              when ea.SystemType in ('binary', 'byte')
                              then 'bytea'
                              when ea.SystemType = 'float'
                              then 'numeric(' || ea."Precision"::text || ', ' || ea."Scale"::text || ')'
                              when /*@isNvarchar = 0 and*/ ea.SystemType = 'string'
							  then case when ea.MaxLength is null then 'text' else 'varchar(' || ea.MaxLength::text ||')' end
                              --when @isNvarchar = 1 and ea.SystemType = N'string'
                              --then N'nvarchar(' + isnull(cast(ea.[MaxLength] as nvarchar(5)),N'max') + N')'
                            end
                    -- Parent, Link, Child
                    else case when eaBase.SystemType in ('int', 'bigint', 'bool', 'boolean', 'date')
                              then eaBase.SystemType
							  when ea.SystemType = 'datetime'
							  then 'timestamp'
                              when eaBase.SystemType = 'guid'
                              then 'uuid'
                              when /*@isNvarchar = 0 and*/ eaBase.SystemType = 'text' or (eaBase.SystemType = 'string' and eaBase.MaxLength is null)
                              then 'text'
                              --when @isNvarchar = 1 and eaBase.SystemType = N'text' or (eaBase.SystemType = N'string' and eaBase.[MaxLength] is null)
                              --then N'nvarchar(max)'
                              when eaBase.SystemType in ('binary', 'byte')
                              then 'bytea'
                              when eaBase.SystemType = 'float'
                              then 'numeric(' || eaBase."Precision"::text || ', ' || eaBase."Scale"::text || ')'
                              when /*@isNvarchar = 0 and*/ eaBase.SystemType = 'string'
                              then case when ea.MaxLength is null then 'text' else 'varchar(' || ea.MaxLength::text ||')' end
                              --when @isNvarchar = 1 and eaBase.SystemType = N'string'
                              --then N'nvarchar(' + isnull(cast(eaBase.[MaxLength] as nvarchar(5)),N'max') + N')'
                            end
                end
            || case when ea.IsRequired = '1' then ' not null' else '' end
            || ', CONSTRAINT PK_F_' || e.EntName || '_' || ea.Name || ' PRIMARY KEY (ID)'
            || ');'
            || case
                   when ea.AttributeType = 1 and eaBase.MappingType != 6 then 
                        ' alter table ' 
                            || e.Schema || '.F_' 
                            || e.EntName || '_' 
                            || ea.Name || ' ADD CONSTRAINT FK_SPREAD_' 
                            || REPLACE(ea.SystemType, '.', '_') || '_' 
                            || ea.Name || ' FOREIGN KEY(value) REFERENCES ' 
                            || eBase.Schema || '.'
                            || eaBase.MappingTable || ' (' 
                            || eaBase.MappingName || ');'
                   else ''
              end
    from 
    (
        SELECT tea.EntityID, IsRequired, MappingType, MappingName, IsStored, MappingTable, tea.Name
		, coalesce(tea.SystemType, 'string') SystemType,
            case
                when tea.MaxLength is null then 255
                when /*@isNvarchar = 0 and*/ tea.MaxLength > 8000 then null
                --when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                else tea.MaxLength
            end MaxLength
		, coalesce(tea.Precision, 31) as "Precision", coalesce(tea.Scale, 10) as "Scale", tea.IsPrimaryKey, tea.AttributeType
        FROM VCLib.T_ENTITIES_ATTRIBUTES tea 
        where tea.MappingType = 5
        and tea.Name not like '% %'
        UNION
        SELECT tea.EntityID, IsRequired, MappingType, MappingName, IsStored, MappingTable, tea.Name
		, coalesce(tea.SystemType, 'string') SystemType,
            case
                when tea.MaxLength is null then 255
                when /*@isNvarchar = 0 and*/ tea.MaxLength > 8000 then null
                --when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                else tea.MaxLength
            end MaxLength
		, coalesce(tea.Precision, 31), coalesce(tea.Scale, 10), tea.IsPrimaryKey, tea.AttributeType
        FROM V_ENTITY_ATTRIBUTES tea 
        where tea.MappingType = 5
        and tea.Name not like '% %'
    ) ea
    inner join
        vclib.T_ENTITIES e
        on ea.EntityID = e.EntityID
    left join lateral
    (
        select c.EntityID as ChildEntityID from vclib.T_ENTITIES c where c.BaseEntity = e.EntityID limit 1
    ) child on true
    inner join
        BaseEntity be
        on be.son = e.EntityID
    inner join
    (
        SELECT tea.EntityID, IsRequired, MappingType, MappingName, IsStored, MappingTable, tea.Name
		, coalesce(tea.SystemType, 'string') SystemType
        FROM VCLib.T_ENTITIES_ATTRIBUTES tea 
        where tea.IsPrimaryKey = '1'
        UNION
        SELECT tea.EntityID, IsRequired, MappingType, MappingName, IsStored, MappingTable, tea.Name
		, coalesce(tea.SystemType, 'string') SystemType
        FROM V_ENTITY_ATTRIBUTES tea 
        where tea.IsPrimaryKey = '1'
    ) Base_ea
        on Base_ea.EntityID = be.Father
    inner join
        vclib.T_ENTITIES eBase
        on eBase.EntityID = be.father
    left join
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        on tc.TABLE_SCHEMA = eBase.Schema
        and tc.TABLE_NAME = Base_ea.MappingTable
        and tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    left JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
        ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
        AND KU.TABLE_SCHEMA = eBase.Schema
        AND KU.TABLE_NAME = Base_ea.MappingTable
    left join
        information_schema.columns col_pk
        on col_pk.table_schema = eBase.Schema
		and col_pk.table_name = Base_ea.MappingTable
        and col_pk.column_name = KU.COLUMN_NAME
    --left join
    --    sys.types t_pk
    --    on t_pk.system_type_id = col_pk.system_type_id
    left join
        BaseEntity valueBase
        on valueBase.Entity = ea.SystemType
    left join
    (
        SELECT tea.EntityID, IsRequired, MappingType, MappingName, IsStored, MappingTable, tea.Name
		, coalesce(tea.SystemType, 'string') SystemType,
            case
                when tea.MaxLength is null then 255
                when /*@isNvarchar = 0 and*/ tea.MaxLength > 8000 then null
                --when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                else tea.MaxLength
            end MaxLength
		, coalesce(tea.Precision, 31) "Precision", coalesce(tea.Scale, 10) "Scale"
        FROM VCLib.T_ENTITIES_ATTRIBUTES tea 
        where tea.IsPrimaryKey = '1'
        UNION
        SELECT tea.EntityID, IsRequired, MappingType, MappingName, IsStored, MappingTable, tea.Name
		, coalesce(tea.SystemType, 'string') SystemType,
            case
                when tea.MaxLength is null then 255
                when /*@isNvarchar = 0 and*/ tea.MaxLength > 8000 then null
                --when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                else tea.MaxLength
            end MaxLength
		, coalesce(tea.Precision, 31), coalesce(tea.Scale, 10)
        FROM V_ENTITY_ATTRIBUTES tea 
        where tea.IsPrimaryKey = '1'
    ) eaBase
        on eaBase.EntityID = valueBase.father
	loop
		RAISE NOTICE '%', _sql;

        if _sql is not null
		then
			EXECUTE _sql;
		end if;
	end loop;
	
	-- create type_ if needed
    for _sql_type_1, _sql_type_2 in
    select
        'Alter Table ' || tc.TABLE_SCHEMA || '.' || tc.TABLE_NAME || ' Add Column type_ int; ',
		'update ' || tc.TABLE_SCHEMA || '.' || tc.TABLE_NAME || ' set cur.type_ = ' || pk.EntityID::text		
    from 
    (
        SELECT ea.EntityID, e.Schema,
            case
                when ea.MappingType = 5 then 'F_' || e.EntName || '_' || ea.Name
                else ea.MappingTable
            end MappingTable
        FROM VCLib.T_ENTITIES_ATTRIBUTES ea 
        join VCLib.T_ENTITIES e
            on e.EntityID = ea.EntityID
        where ea.IsPrimaryKey = '1'
        UNION
        SELECT ea.EntityID, e.Schema,
            case
                when ea.MappingType = 5 then 'F_' || e.EntName || '_' || ea.Name
                else ea.MappingTable
            end MappingTable
        FROM V_ENTITY_ATTRIBUTES ea             
        join VCLib.T_ENTITIES e
            on e.EntityID = ea.EntityID
        where ea.IsPrimaryKey = '1'
    ) pk
    left join lateral
    (
        select c.EntityID as ChildEntityID from vclib.T_ENTITIES c where c.BaseEntity = pk.EntityID limit 1
    ) child on true
    left join
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        on lower(tc.TABLE_SCHEMA) = lower(pk.Schema)
        and lower(tc.TABLE_NAME) = lower(pk.MappingTable)
        and tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    left JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
        ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
        AND KU.TABLE_SCHEMA = lower(pk.Schema)
        AND KU.TABLE_NAME = lower(pk.MappingTable)
	left join
		information_schema.columns col_pk
		on col_pk.table_schema = lower(pk.Schema)
		and col_pk.table_name = lower(pk.MappingTable)
		and col_pk.column_name = KU.COLUMN_NAME
    where 
    child.ChildEntityID is not null 
    and not exists
	(
		select * from information_schema.columns cType 
		where cType.table_schema = lower(pk.Schema)
		and cType.table_name = lower(pk.MappingTable)
		and cType.column_name = 'type_'
	)
	loop
		RAISE NOTICE '%', _sql_type_1;
		RAISE NOTICE '%', _sql_type_2;
		if _sql_type_1 is not null
		then
			EXECUTE _sql_type_1;
		end if;
		if _sql_type_2 is not null
		then
			EXECUTE _sql_type_2;
		end if;
    end loop;
	
End;
$BODY$;
ALTER FUNCTION sp_Generate_SpreadTables() OWNER TO postgres;

