DROP PROCEDURE IF EXISTS sp_Generate_SpreadTablesInsert;
DROP FUNCTION IF EXISTS sp_Generate_SpreadTablesInsert;

CREATE PROCEDURE sp_Generate_SpreadTablesInsert()
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(
    'insert into ' 
    || e.schema || '.F_' || e.EntName || '_' || ea.name || '(id, value) select t.id, t.field_value
    from
    (
        select s.id, s.field_value from dbo.sys_' 
        || case
                when pkc.SystemType in ('int', 'bigint') then 'i'
                when pkc.SystemType in ('string', 'text') then 'a'
                when pkc.SystemType = 'guid' then 'g'
           end
        || case
				when ea.AttributeType = 4 -- Choice
				then 'i'
				when ea.AttributeType = 0 -- Attribute
				then case
					when ea.SystemType in ('int', 'bigint') then 'i'
					when ea.SystemType in ('string','text') then 'a'
					when ea.SystemType in ('date','datetime') then 'd'
					when ea.SystemType = 'float' then 'f'
					when ea.SystemType in ('bit','bool','boolean') then 'b'
					when ea.SystemType = 'guid' then 'g'
				end
				-- Parent, Link, Child
				else case
					when eaBase.SystemType in ('int', 'bigint') then 'i'
					when eaBase.SystemType in ('string','text') then 'a'
					when eaBase.SystemType in ('date','datetime') then 'd'
					when eaBase.SystemType = 'float' then 'f'
					when eaBase.SystemType in ('bit','bool','boolean') then 'b'
					when eaBase.SystemType = 'guid' then 'g'
				end
           end
        || ' s where ' || cast(ea.EntityID as varchar(11)) || ' in (select ae.BaseEntity from allEntities ae where ae.EntityID = s.object_type)' || ' and s.field_id = ' || cast(ea.Index as varchar(11)) || '
        and not exists(select * from ' 
            || e.Schema || '.F_' 
            || e.EntName || '_' 
            || ea.Name || ' cur where cur.id = s.id)
    ) t;', '
')
    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, tea.Index
            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, tea.Index
            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
        inner 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, 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
            from VCLib.T_ENTITIES_ATTRIBUTES tea
			where tea.IsPrimaryKey = true
            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
            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;

    drop table allEntities;
End;
$$ Language plpgsql;
