CREATE OR REPLACE PROCEDURE sp_generate_spreadtables()
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_sql         TEXT    := '';
    v_type_       TEXT    := '';
    v_updtype_    TEXT    := '';
    v_is_nvarchar BOOLEAN := FALSE;
    v_tsql        TEXT    := '';
    v_tsqlfrom    TEXT    := ' from temp_pretree t0';
    v_tsqlljoin   TEXT    := '';
    v_tsqlselect  TEXT    := 'select distinct t0.entityid as l0';
    v_tsqlselect1 TEXT    := '';
    v_tsqlorder   TEXT    := '';
    v_table_name  TEXT;
    v_lvl         INT;
    v_from        TEXT    := '';
    r_spread      RECORD;
    r_type        RECORD;
BEGIN
    SELECT
        EXISTS
        (
            SELECT
                1
            FROM pg_attribute a
            JOIN pg_class c ON a.attrelid = c.oid
            JOIN pg_namespace n ON c.relnamespace = n.oid
            JOIN pg_type t ON a.atttypid = t.oid
            WHERE
                  n.nspname = 'vclib'
              AND c.relname = 't_entities'
              AND a.attname = 'entname'
              AND t.typname IN ('varchar', 'text')
              AND a.attnum > 0
              AND NOT a.attisdropped
        )
    INTO v_is_nvarchar;

    -- иерархия сущностей
    DROP TABLE IF EXISTS temp_entitieshierarchy;
    CREATE TEMP TABLE temp_entitieshierarchy
    (
        father INT,
        son    INT
    );
    CREATE INDEX ind_temp_entitieshierarchy_father ON temp_entitieshierarchy (father);

    DROP TABLE IF EXISTS temp_pretree;
    CREATE TEMP TABLE temp_pretree AS
    WITH
        RECURSIVE
        cte AS (
            SELECT
                entityid,
                baseentity,
                0 AS lvl
            FROM vclib.t_entities
            WHERE baseentity IS NULL
            UNION ALL
            SELECT
                e.entityid,
                e.baseentity,
                cte.lvl + 1
            FROM vclib.t_entities e
            JOIN cte ON e.baseentity = cte.entityid
        )
    SELECT *
    FROM cte;

    DROP TABLE IF EXISTS temp_num;
    CREATE TEMP TABLE temp_num
    (
        c1 INT
    );

    -- динамический сбор дерева
    FOR v_lvl IN (
        SELECT
            lvl
        FROM temp_pretree
        GROUP BY lvl
        ORDER BY lvl
                 )
        LOOP
            IF v_lvl = 0 THEN
                v_tsqlselect1 := 't0.entityid';
            ELSE
                IF v_lvl = 1 THEN
                    v_tsqlorder := ' order by 1';
                END IF;

                v_tsqlljoin := v_tsqlljoin || ' left join temp_pretree t' || v_lvl ||
                               ' on t' || (v_lvl - 1) || '.entityid = t' || v_lvl || '.baseentity and t' ||
                               (v_lvl - 1) || '.lvl = ' || (v_lvl - 1);

                v_tsqlselect1 := 't' || v_lvl || '.entityid, ' || v_tsqlselect1;
                v_tsqlselect := v_tsqlselect || ', coalesce(' || v_tsqlselect1 || ') as l' || v_lvl;

                IF v_lvl > 1 THEN
                    v_tsqlorder := v_tsqlorder || ', ' || v_lvl;
                END IF;
            END IF;

            INSERT INTO temp_num(c1) VALUES (v_lvl);
        END LOOP;

    -- сборка union запроса
    SELECT
        STRING_AGG('select distinct l' || n1.c1 || ' as father, l' || n2.c1 || ' as son from temp_tree', ' union ')
    INTO v_from
    FROM temp_num n1
    CROSS JOIN temp_num n2
    WHERE n1.c1 <= n2.c1;

    -- выполняем создание temp_tree и вставку в иерархию
    DROP TABLE IF EXISTS temp_tree;
    EXECUTE 'create temp table temp_tree as ' || v_tsqlselect || v_tsqlfrom || v_tsqlljoin || v_tsqlorder;
--     RAISE NOTICE '%', 'create temp table temp_tree as ' || v_tsqlselect || v_tsqlfrom || v_tsqlljoin || v_tsqlorder;
    v_tsql := 'insert into temp_entitieshierarchy(father, son) select distinct father, son from (' || v_from || ') tab';

--     RAISE NOTICE '%', v_tsql;
    EXECUTE v_tsql;

    DROP TABLE IF EXISTS temp_baseentity;
    CREATE TEMP TABLE temp_baseentity AS
    SELECT
        b.father,
        b.son,
        e0.schema || '.' || e0.entname AS baseentity,
        e1.schema || '.' || e1.entname AS entity
    FROM temp_entitieshierarchy b
    JOIN temp_pretree t ON t.entityid = b.son
    JOIN vclib.t_entities e0 ON e0.entityid = b.father
    JOIN vclib.t_entities e1 ON e1.entityid = b.son
    WHERE e0.baseentity IS NULL;

    -- spread - атрибуты
    FOR r_spread IN (
        WITH
            base_ea AS (
                SELECT
                    tea.entityid,
                    isrequired,
                    mappingtype,
                    mappingname,
                    isstored,
                    mappingtable,
                    name,
                    COALESCE(systemtype, 'string') AS systemtype
                FROM vclib.t_entities_attributes tea
                WHERE isprimarykey = 1
                UNION
                SELECT
                    tea.entityid,
                    isrequired,
                    mappingtype,
                    mappingname,
                    isstored,
                    mappingtable,
                    name,
                    COALESCE(systemtype, 'string') AS systemtype
                FROM v_entity_attributes tea
                WHERE isprimarykey = 1
                       ),
            ea_base AS (
                SELECT
                    tea.entityid,
                    isrequired,
                    mappingtype,
                    mappingname,
                    isstored,
                    mappingtable,
                    name,
                    COALESCE(systemtype, 'string') AS systemtype,
                    CASE
                        WHEN maxlength IS NULL THEN 255
                        WHEN maxlength > 4000 THEN NULL
                        ELSE maxlength END         AS maxlength,
                    COALESCE(precision, 31)        AS precision,
                    COALESCE(scale, 10)            AS scale
                FROM vclib.t_entities_attributes tea
                WHERE isprimarykey = 1
                UNION
                SELECT
                    tea.entityid,
                    isrequired,
                    mappingtype,
                    mappingname,
                    isstored,
                    mappingtable,
                    name,
                    COALESCE(systemtype, 'string') AS systemtype,
                    CASE
                        WHEN maxlength IS NULL THEN 255
                        WHEN maxlength > 4000 THEN NULL
                        ELSE maxlength END         AS maxlength,
                    COALESCE(precision, 31)        AS precision,
                    COALESCE(scale, 10)            AS scale
                FROM v_entity_attributes tea
                WHERE isprimarykey = 1
                       )
        SELECT
            e.schema || '.f_' || e.entname || '_' || ea.name AS tabname,
            e.schema                                         AS e_schema,
            e.entname                                        AS e_entname,
            ea.name                                          AS ea_name,
            ea.isprimarykey                                  AS ea_isprimarykey,
            ea.attributetype                                 AS ea_attributetype,
            ea.systemtype                                    AS ea_systemtype,
            ea.maxlength                                     AS ea_maxlength,
            ea.precision                                     AS ea_precision,
            ea.scale                                         AS ea_scale,
            ea.isrequired                                    AS ea_isrequired,
            eab.systemtype                                   AS eabase_systemtype,
            eab.maxlength                                    AS eabase_maxlength,
            eab.precision                                    AS eabase_precision,
            eab.scale                                        AS eabase_scale,
            eab.mappingtype                                  AS eabase_mappingtype,
            eab.mappingtable                                 AS eabase_mappingtable,
            eab.mappingname                                  AS eabase_mappingname,
            ebase.schema                                     AS ebase_schema,

            -- получаем готовый тип (например 'numeric(10,2)' или 'varchar(255)') прямо из pg_catalog
            (
                SELECT
                    FORMAT_TYPE(a.atttypid, a.atttypmod)
                FROM pg_attribute a
                JOIN pg_class c ON a.attrelid = c.oid
                JOIN pg_namespace n ON c.relnamespace = n.oid
                WHERE
                      n.nspname = LOWER(ebase.schema)
                  AND c.relname = LOWER(base_ea.mappingtable) -- ИСПРАВЛЕНО
                  AND a.attname = LOWER(base_ea.mappingname) -- ИСПРАВЛЕНО
                  AND a.attnum > 0
                  AND NOT a.attisdropped
                LIMIT 1
            )                                                AS pk_full_type


        FROM (
            SELECT
                entityid,
                isrequired,
                mappingtype,
                mappingname,
                isstored,
                mappingtable,
                name,
                COALESCE(systemtype, 'string')                                                          AS systemtype,
                CASE WHEN maxlength IS NULL THEN 255 WHEN maxlength > 4000 THEN NULL ELSE maxlength END AS maxlength,
                COALESCE(precision, 31)                                                                 AS precision,
                COALESCE(scale, 10)                                                                     AS scale,
                isprimarykey,
                attributetype
            FROM vclib.t_entities_attributes
            WHERE mappingtype = 5 AND name NOT LIKE '% %'
            UNION
            SELECT
                entityid,
                isrequired,
                mappingtype,
                mappingname,
                isstored,
                mappingtable,
                name,
                COALESCE(systemtype, 'string')                                                          AS systemtype,
                CASE WHEN maxlength IS NULL THEN 255 WHEN maxlength > 4000 THEN NULL ELSE maxlength END AS maxlength,
                COALESCE(precision, 31)                                                                 AS precision,
                COALESCE(scale, 10)                                                                     AS scale,
                isprimarykey,
                attributetype
            FROM v_entity_attributes
            WHERE mappingtype = 5 AND name NOT LIKE '% %'
             ) ea
        JOIN vclib.t_entities e ON ea.entityid = e.entityid
        JOIN temp_baseentity be ON be.son = e.entityid
        JOIN base_ea ON base_ea.entityid = be.father
        JOIN vclib.t_entities ebase ON ebase.entityid = be.father
        LEFT JOIN temp_baseentity valuebase ON valuebase.entity = ea.systemtype
        LEFT JOIN ea_base eab ON eab.entityid = valuebase.father
                    )
        LOOP

            -- Генерируем название таблицы
            v_table_name := LOWER('f_' || r_spread.e_entname || '_' || r_spread.ea_name);

            -- Если длина таблицы больше лимита (63 символа), просто пропускаем ее
            IF LENGTH(v_table_name) > 63 THEN
                RAISE NOTICE 'ПРОПУСК: Название таблицы % превышает лимит в 63 символа', v_table_name;
                CONTINUE;
            END IF;

            IF NOT EXISTS
            (
                SELECT
                    1
                FROM pg_class c
                JOIN pg_namespace n ON n.oid = c.relnamespace
                WHERE
                      n.nspname = LOWER(r_spread.e_schema)
                  AND c.relname = v_table_name
            ) THEN

                -- Собираем CREATE TABLE
                v_sql := 'create table ' || LOWER(r_spread.e_schema) || '.' || v_table_name || '( id ';

                -- тип pk
                IF r_spread.pk_full_type IS NULL THEN
                    IF r_spread.ea_systemtype IN ('int', 'bigint') THEN
                        v_sql := v_sql || r_spread.ea_systemtype;
                    ELSIF r_spread.ea_systemtype = 'guid' THEN
                        v_sql := v_sql || 'uuid';
                    ELSIF r_spread.ea_systemtype = 'string' THEN
                        v_sql := v_sql || 'varchar(' || COALESCE(r_spread.ea_maxlength::TEXT, 'max') || ')';
                    END IF;
                ELSE
                    -- используем готовую строку с типом (например 'numeric(10,2)')
                    v_sql := v_sql || r_spread.pk_full_type;
                END IF;

                -- добавление value
                IF r_spread.ea_isprimarykey = 0 THEN
                    v_sql := v_sql || ', value ';
                    IF r_spread.ea_attributetype = 4 THEN
                        v_sql := v_sql || 'int';
                    ELSIF r_spread.ea_attributetype = 0 THEN
                        IF r_spread.ea_systemtype IN ('int', 'bigint') THEN
                            v_sql := v_sql || r_spread.ea_systemtype;
                        ELSIF r_spread.ea_systemtype = 'datetime' THEN
                            v_sql := v_sql || 'timestamp';
                        ELSIF r_spread.ea_systemtype = 'guid' THEN
                            v_sql := v_sql || 'uuid';
                        ELSIF r_spread.ea_systemtype = 'text' OR
                              (r_spread.ea_systemtype = 'string' AND r_spread.ea_maxlength IS NULL) THEN
                            v_sql := v_sql || 'text';
                        ELSIF r_spread.ea_systemtype IN ('binary', 'byte') THEN
                            v_sql := v_sql || 'bytea';
                        ELSIF r_spread.ea_systemtype IN ('bool', 'boolean') THEN
                            v_sql := v_sql || 'boolean';
                        ELSIF r_spread.ea_systemtype = 'float' THEN
                            v_sql := v_sql || 'numeric(' || r_spread.ea_precision || ', ' || r_spread.ea_scale || ')';
                        ELSIF r_spread.ea_systemtype = 'date' THEN
                            v_sql := v_sql || 'timestamp';
                        ELSIF r_spread.ea_systemtype = 'string' THEN
                            v_sql := v_sql || 'varchar(' || r_spread.ea_maxlength || ')';
                        END IF;
                    ELSE
                        IF r_spread.eabase_systemtype IN ('int', 'bigint') THEN
                            v_sql := v_sql || r_spread.eabase_systemtype;
                        ELSIF r_spread.eabase_systemtype = 'datetime' THEN
                            v_sql := v_sql || 'timestamp';
                        ELSIF r_spread.eabase_systemtype = 'guid' THEN
                            v_sql := v_sql || 'uuid';
                        ELSIF r_spread.eabase_systemtype = 'text' OR
                              (r_spread.eabase_systemtype = 'string' AND r_spread.eabase_maxlength IS NULL) THEN
                            v_sql := v_sql || 'text';
                        ELSIF r_spread.eabase_systemtype IN ('binary', 'byte') THEN
                            v_sql := v_sql || 'bytea';
                        ELSIF r_spread.eabase_systemtype IN ('bool', 'boolean') THEN
                            v_sql := v_sql || 'boolean';
                        ELSIF r_spread.eabase_systemtype = 'float' THEN
                            v_sql :=
                                v_sql || 'numeric(' || r_spread.eabase_precision || ', ' || r_spread.eabase_scale ||
                                ')';
                        ELSIF r_spread.eabase_systemtype = 'date' THEN
                            v_sql := v_sql || 'timestamp';
                        ELSIF r_spread.eabase_systemtype = 'string' THEN
                            v_sql := v_sql || 'varchar(' || r_spread.eabase_maxlength || ')';
                        END IF;
                    END IF;
                END IF;

                IF r_spread.ea_isrequired = 1 THEN v_sql := v_sql || ' not null'; END IF;

                v_sql :=
                    v_sql || ', constraint pk_f_' || r_spread.e_entname || '_' || r_spread.ea_name ||
                    ' primary key (id));';

                -- добавление внешнего ключа
                IF r_spread.ea_attributetype = 1 AND r_spread.eabase_mappingtype != 6 THEN
                    v_sql := v_sql || ' alter table ' || LOWER(r_spread.e_schema) || '.' ||
                             LOWER('f_' || r_spread.e_entname || '_' || r_spread.ea_name) ||
                             ' add constraint fk_spread_' || REPLACE(r_spread.ea_systemtype, '.', '_') || '_' ||
                             r_spread.ea_name ||
                             ' foreign key (value) references ' || LOWER(r_spread.ebase_schema) || '.' ||
                             LOWER(r_spread.eabase_mappingtable) || ' (' ||
                             LOWER(r_spread.eabase_mappingname) || ');';
                END IF;

                RAISE NOTICE '%', v_sql;
                EXECUTE v_sql;
            END IF;
        END LOOP;

    -- create type_ if needed
    FOR r_type IN (
        WITH
            pk AS (
                SELECT
                    ea.entityid,
                    e.schema,
                    CASE
                        WHEN ea.mappingtype = 5 THEN 'f_' || e.entname || '_' || ea.name
                        ELSE ea.mappingtable END AS mappingtable
                FROM vclib.t_entities_attributes ea
                JOIN vclib.t_entities e ON e.entityid = ea.entityid
                WHERE ea.isprimarykey = 1 AND ea.mappingtype = 1
                UNION
                SELECT
                    ea.entityid,
                    e.schema,
                    CASE
                        WHEN ea.mappingtype = 5 THEN 'f_' || e.entname || '_' || ea.name
                        ELSE ea.mappingtable END AS mappingtable
                FROM v_entity_attributes ea
                JOIN vclib.t_entities e ON e.entityid = ea.entityid
                WHERE ea.isprimarykey = 1 AND ea.mappingtype = 1
            )
        SELECT
            pk.entityid,
            pk.schema       AS table_schema,
            pk.mappingtable AS table_name
        FROM pk
        WHERE
              EXISTS
              (
                  SELECT
                      1
                  FROM vclib.t_entities c
                  WHERE c.baseentity = pk.entityid
              )
          AND NOT EXISTS
              (
                  SELECT
                      1
                  FROM pg_attribute a
                  JOIN pg_class c ON a.attrelid = c.oid
                  JOIN pg_namespace n ON c.relnamespace = n.oid
                  WHERE
                        LOWER(n.nspname) = LOWER(pk.schema)
                    AND LOWER(c.relname) = LOWER(pk.mappingtable)
                    AND a.attname = 'type_'
                    AND a.attnum > 0
                    AND NOT a.attisdropped
              )

                  )
        LOOP
            v_type_ := 'alter table ' || LOWER(r_type.table_schema) || '.' || LOWER(r_type.table_name) ||
                       ' add column type_ int;';
            v_updtype_ :=
                'update ' || LOWER(r_type.table_schema) || '.' || LOWER(r_type.table_name) || ' cur set type_ = ' ||
                r_type.entityid || ';';

            RAISE NOTICE '%', v_type_;
            EXECUTE v_type_;
            RAISE NOTICE '%', v_updtype_;
            EXECUTE v_updtype_;
        END LOOP;

END;
$$;
--CALL sp_generate_spreadtables();
