do $$
begin
    if exists (select * from pg_proc where proname='sp_generate_spreadtables')
    then
        DROP FUNCTION sp_Generate_SpreadTables();
    end if;
end;
$$ LANGUAGE plpgsql;

CREATE or Replace Function sp_Generate_SpreadTables()
RETURNS void As 
$$
Declare    
    query1 text;
Begin
    CREATE TEMP TABLE allEntities AS WITH RECURSIVE AllEntity
    (EntityID, BaseEntityIndex)
    AS 
    (
            SELECT e.EntityID, e.BaseEntity
            FROM VCLib.T_ENTITIES e
                    left join VCLib.T_ENTITIES e2 on e2.EntityID = e.BaseEntity
            UNION ALL 
            SELECT e.EntityID, e2.BaseEntityIndex
            FROM VCLib.T_ENTITIES e
            INNER JOIN AllEntity e2 On e.BaseEntity = e2.EntityID
    )
    SELECT EntityID, COALESCE(BaseEntityIndex, EntityID) BaseEntity
    FROM AllEntity;



query1 := (
    select string_agg(
    'create table if not exists ' 
    || e.schema || '.F_' || e.EntName || '_' || ea.name 
    || '(id ' || case when pk.data_type is null then case
                                                        when pkc.SystemType in ('int', 'bigint') then pkc.SystemType
                                                        when pkc.SystemType = 'guid' then 'uuid'
                                                        when pkc.SystemType = 'string' then 'varchar(' || cast(ea.MaxLength as varchar(5)) || ')'
                                                     end
                        when pk.data_type in ('character', 'character varying') then pk.data_type || '(' || pk.character_maximum_length || ')' 
                        when pk.data_type = 'numeric' then pk.data_type || '(' || pk.numeric_precision || ',' || pk.numeric_scale || ')' 
                        else pk.data_type
                 end
    || case when ea.IsPrimaryKey = true then '' else ', value ' end
    || case
        when ea.IsPrimaryKey = true then ''
        when ea.AttributeType = 4 -- Choice
        then 'int'
        when ea.AttributeType = 0 -- Attribute
        then case
            when ea.SystemType in ('int', 'bigint', 'text') then ea.SystemType
            when ea.SystemType in ('datetime', 'date') then 'timestamp'
            when ea.SystemType = 'guid' then 'uuid'
            when (ea.SystemType = 'string' and ea.MaxLength is null) then 'text'
            when ea.SystemType in ('binary', 'byte') then 'bytea'
            when ea.SystemType in ('bool', 'boolean') then 'boolean'
            when ea.SystemType = 'float' then 'decimal(' || cast(ea.Precision as varchar(2)) || ', ' || cast(ea.Scale as varchar(2)) || ')'
            when ea.SystemType = 'string' then 'varchar(' || cast(ea.MaxLength as varchar(5)) || ')'
        end
        -- Parent, Link, Child
        else case
            when eaBase.SystemType in ('int', 'bigint', 'text') then eaBase.SystemType
            when eaBase.SystemType in ('datetime', 'date') then 'timestamp'
            when eaBase.SystemType = 'guid' then 'uuid'
            when (eaBase.SystemType = 'string' and eaBase.MaxLength is null) then 'text'
            when eaBase.SystemType in ('binary', 'byte') then 'bytea'
            when eaBase.SystemType in ('bool', 'boolean') then 'boolean'
            when eaBase.SystemType = 'float' then 'decimal(' || cast(eaBase.Precision as varchar(2)) || ', ' || cast(eaBase.Scale as varchar(2)) || ')'
            when eaBase.SystemType = 'string' then 'varchar(' || cast(eaBase.MaxLength as varchar(5)) || ')'
        end
    end
    || case when ea.IsRequired = true then ' not null' else '' end
    || ', CONSTRAINT ' || 'PK_F_' || e.EntName || '_' || ea.name || ' PRIMARY KEY(id));'
    || case
           when ea.AttributeType = 1 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, '
')
    as exec1
    from (
            select tea.EntityID, tea.Name, tea.MappingType, lower(coalesce(tea.SystemType, 'string')) as SystemType,
            case
                when tea.MaxLength is null then 255
                when tea.MaxLength > 8000 then null
                else tea.MaxLength
            end MaxLength, coalesce(tea.Precision, 31) as Precision, coalesce(tea.Scale, 10) Scale, tea.IsRequired, tea.IsPrimaryKey, tea.AttributeType
            from VCLib.T_ENTITIES_ATTRIBUTES tea
            where tea.MappingType = 5
            union all
            select tea.EntityID, tea.Name, tea.MappingType, lower(coalesce(tea.SystemType, 'string')) as SystemType,
            case
                when tea.MaxLength is null then 255
                when tea.MaxLength > 8000 then null
                else tea.MaxLength
            end MaxLength, coalesce(tea.Precision, 31) as Precision, coalesce(tea.Scale, 10) Scale, tea.IsRequired, tea.IsPrimaryKey, tea.AttributeType
            from V_ENTITY_ATTRIBUTES tea
            where tea.MappingType = 5
        ) ea
        inner join 
            vclib.t_entities e 
            on e.entityid = ea.entityid
        join (
            select EntityID, Name, MappingType, MappingTable, lower(coalesce(SystemType, 'string')) as SystemType
            from VCLib.T_ENTITIES_ATTRIBUTES
            where IsPrimaryKey = true
            union all
            select EntityID, Name, MappingType, MappingTable, lower(coalesce(SystemType, 'string')) as SystemType
            from V_ENTITY_ATTRIBUTES
            where IsPrimaryKey = true
        ) pkc on pkc.EntityID in (select ae.BaseEntity from allEntities ae where ae.EntityID = ea.EntityID)
        inner join 
            vclib.t_entities pke
            on pke.entityid = pkc.entityid
        left join
            information_schema.columns pk
            on pk.table_schema = lower(e.schema)
            and pk.table_name = lower(pkc.mappingtable)
            and pk.column_name = lower(pkc.name)
        left join vclib.t_entities eBase 
            on lower(eBase.Schema || '.' || eBase.EntName) = ea.SystemType
        left join (
            select tea.EntityID, IsRequired, tea.MappingType, tea.MappingName, IsStored, MappingTable, tea.Name, lower(coalesce(tea.SystemType, 'string')) as SystemType,
            case
                when tea.MaxLength is null then 255
                when tea.MaxLength > 8000 then null
                else tea.MaxLength
            end MaxLength, coalesce(tea.Precision, 31) as Precision, coalesce(tea.Scale, 10) Scale, tea.IsRequired, tea.IsPrimaryKey
            from VCLib.T_ENTITIES_ATTRIBUTES tea
            where tea.IsPrimaryKey = true
            union all
            select tea.EntityID, IsRequired, tea.MappingType, tea.MappingName, IsStored, MappingTable, tea.Name, lower(coalesce(tea.SystemType, 'string')) as SystemType,
            case
                when tea.MaxLength is null then 255
                when tea.MaxLength > 8000 then null
                else tea.MaxLength
            end MaxLength, coalesce(tea.Precision, 31) as Precision, coalesce(tea.Scale, 10) Scale, tea.IsRequired, tea.IsPrimaryKey
            from V_ENTITY_ATTRIBUTES tea
            where tea.IsPrimaryKey = true
        ) eaBase
            on eaBase.EntityID in (select ae.BaseEntity from allEntities ae where ae.EntityID = eBase.EntityID)
);

    if query1 is not null
    then
        execute query1;
    end if;



query1 := (
    select string_agg(
    -- create type_ if needed
    case
            when (child.entityid is not null and not exists(select * from information_schema.columns col where col.table_schema = coalesce(pk.table_schema, pk.table_schema) and col.table_name = coalesce(pk.table_name, pk.table_name) and col.column_name = 'type_'))
            then 'ALTER TABLE '
                || pk.table_schema || '.' || pk.table_name
                || ' ADD COLUMN type_ int;
'
                || 'UPDATE '
                || pk.table_schema || '.' || pk.table_name
                || ' SET type_ = ' || cast(ea.EntityID as varchar) || ';'
            else ''
        end, '
')
    as exec1
    from (
            select tea.EntityID, e.Schema,
                case
                    when tea.MappingType = 5 then 'F_' || e.EntName || '_' || tea.Name
                    else tea.MappingTable
                end MappingTable,
                case
                    when tea.MappingType = 5 then 'id'
                    else tea.MappingName
                end as MappingName
            from VCLib.T_ENTITIES_ATTRIBUTES tea
            inner join 
                vclib.t_entities e 
                on e.entityid = tea.entityid
            where IsPrimaryKey = true
            union all
            select tea.EntityID, e.Schema,
                case
                    when tea.MappingType = 5 then 'F_' || e.EntName || '_' || tea.Name
                    else tea.MappingTable
                end MappingTable,
                case
                    when tea.MappingType = 5 then 'id'
                    else tea.MappingName
                end as MappingName
            from V_ENTITY_ATTRIBUTES tea
            inner join 
                vclib.t_entities e 
                on e.entityid = tea.entityid
            where IsPrimaryKey = true
        ) ea
        left join information_schema.columns pk
            on pk.table_schema = lower(ea.schema)
            and pk.table_name = lower(ea.mappingtable)
            and pk.column_name = lower(ea.MappingName)
        left join 
            lateral(select be.entityid from vclib.t_entities be where be.baseentity = ea.entityid limit 1) as child on true
);

    if query1 is not null
    then
        execute query1;
    end if;

    drop table allEntities;
End;
$$ Language plpgsql;
