set client_min_messages='warning';
set statement_timeout = 60000;
set behavior_compat_options = '';


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
    if l_cnt = 0
    then
        create schema compat_tools;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);


-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
     , l.lanname as language
     , n.nspname as schema_name
     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
  from (select oid
             , pronamespace
             , proname
             , prolang
             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
          from pg_catalog.pg_proc
       ) as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
  join pg_catalog.pg_type as t on p.protype = t.oid
  left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
 group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
 union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
  from pg_catalog.pg_proc as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
 where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');


-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
     , coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
     , coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
     , dep.refclassid::regclass::text as ref_object_type
     , cpt.object_name as ref_object_name
     , cpt.compat_type
     , case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
  from pg_depend as dep
  join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
          from compat_tools.compat_version as v
          left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
          left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
                       from pg_catalog.pg_class as cls
                       join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
                    ) as c on v.object_name = c.object_name
         where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
  left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
  left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
  left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
  left join pg_class as cls on rwt.ev_class = cls.oid
  left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
 where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';


-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_object(varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_object( p_object_type    varchar(10)
                                                          , p_object_name    varchar(128)
                                                          , p_object_version varchar(10)
                                                          , p_object_schema  varchar(128)  default 'pg_catalog')
returns boolean
as $$
declare
    l_version       varchar(10);
    l_depend_cnt    bigint;
    l_app_name      varchar(128)  := current_setting('application_name');
    l_result        boolean       := 'true';
    l_operation     varchar(128);
begin
    CREATE TEMPORARY TABLE if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    ) ON COMMIT PRESERVE ROWS;

    -- 字符串参数统一转小写
    p_object_name := lower(p_object_name);
    p_object_type := lower(p_object_type);
    p_object_schema := lower(p_object_schema);

    -- 获取当前目标库中的对象版本
    select max(object_version) into l_version
      from compat_tools.compat_version
     where object_name = p_object_schema||'.'||p_object_name
       and compat_type = p_object_type;

    -- 获取非 Compat Tools 依赖对象数量
    select count(*) into l_depend_cnt
      from compat_tools.pg_depend_list
     where ref_object_name = p_object_schema||'.'||p_object_name;

    if l_version >= p_object_version
    then
        l_operation := 'Skip';
        l_result    := 'false';

        if l_app_name != 'checkMe'
        then
            insert into compat_tools.compat_version
            values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
            ON DUPLICATE KEY UPDATE NOTHING;
        end if;
    else
        l_operation := case when l_version is null then 'Initial creation' else 'Upgrade' end;
        l_result    := 'true';

        if l_app_name != 'checkMe'
        then
            begin
                -- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
                if l_depend_cnt = 0
                then
                    if instr(p_object_name, '(') > 0
                    then
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||replace(p_object_name, '(', '"(')||' cascade';
                    else
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||p_object_name||'" cascade';
                    end if;

                    insert into compat_tools.compat_version
                    values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
                    ON DUPLICATE KEY UPDATE object_version = p_object_version;
                else
                    l_operation := l_operation||' - dependence';
                end if;
            exception
                when others then
                    l_result := 'false';
                    get stacked diagnostics l_operation = message_text;
                    l_operation = substr(l_operation, 1, 128);
            end;
        end if;
    end if;

    -- 插入本次临时结果表
    insert into temp_result (object_type, object_name, local_version, script_version, operation)
    values (p_object_type, p_object_schema||'.'||p_object_name, l_version, p_object_version, l_operation);

    -- 返回函数结果
    if l_app_name = 'checkMe'
    then
        return 'false';
    else
        return l_result;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Comparison Function
-- Result:
--     version_a > version_b  => 1
--     version_a = version_b  => 0
--     version_a < version_b  => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
                                                        , version_b text)
returns int IMMUTABLE strict as $$
declare
    l_rec   record;
begin
    if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
    then
        return null;
    end if;
    for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
                   from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
                   full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
                     on t1.id = t2.id
    loop
        if l_rec.ver_1 > l_rec.ver_2
        then
            return 1;
        elsif l_rec.ver_1 < l_rec.ver_2
        then
            return -1;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $TYPE_CREATION$
declare
    l_cnt    bigint;
begin
    -- =========================================================================
    -- pls_integer
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('TYPE', 'PLS_INTEGER', '1.0')
        then
            create type pg_catalog.pls_integer;

            CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_in(cstring)
            RETURNS pls_integer
            LANGUAGE internal
            IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
            AS $function$int4in$function$;

            CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_out(pls_integer)
            RETURNS cstring
            LANGUAGE internal
            IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
            AS $function$int4out$function$;

            CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_send(pls_integer)
            RETURNS bytea
            LANGUAGE internal
            IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
            AS $function$int4send$function$;

            CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_recv(internal)
            RETURNS pls_integer
            LANGUAGE internal
            IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
            AS $function$int4recv$function$;

            CREATE TYPE pg_catalog.pls_integer (
                INPUT = pls_integer_in,
                OUTPUT = pls_integer_out,
                RECEIVE = pls_integer_recv,
                SEND = pls_integer_send,
                INTERNALLENGTH = 4,
                STORAGE = plain,
                CATEGORY = N,
                DELIMITER = ',',
                PASSEDBYVALUE
                );

            CREATE CAST (pls_integer AS int4) WITHOUT FUNCTION AS IMPLICIT;
            CREATE CAST (int4 AS pls_integer) WITHOUT FUNCTION AS IMPLICIT;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in CREATE TYPE PLS_INTEGER: %', SQLERRM;
            rollback;
    end;
end;
$TYPE_CREATION$ language plpgsql;

-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name  text  := current_setting('application_name');
begin
    set client_min_messages='notice';
    if l_app_name not in ('runMe', 'checkMe')
    then
        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Compat Object List: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   |' || pad_char
                              || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                            from (select greatest(max(length(object_type)), 5) max_object_type
                                       , greatest(max(length(object_name)), 6) max_object_name
                                       , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                       , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                       , greatest(max(length(operation)), 9) max_operation
                                    from temp_result) l
                            join (select 'type' as object_type
                                       , 'name' as object_name
                                       , 'version' as object_version
                                       , 'language' as object_language
                                       , 'operation' as operation
                                       , ' ' as pad_char
                                   union all
                                  select '-' as object_type
                                       , '-' as object_name
                                       , '-' as object_version
                                       , '-' as object_language
                                       , '-' as operation
                                       , '-' as pad_char
                                   union all
                                  select object_type, object_name
                                       , case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
                                       , case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
                                       , operation, ' ' from temp_result) r on 1 = 1
        loop
            raise notice '%', l_app_name;
        end loop;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

reset behavior_compat_options;

\q