CREATE OR REPLACE FUNCTION EXTRACT_AXIUM_DDL
    (
        target_schema TEXT,
        dbname TEXT,
        host TEXT,
        port TEXT,
        username TEXT,
        output_file TEXT
    )
    RETURNS SETOF TEXT
LANGUAGE plpgsql AS $$
DECLARE
    typ_filter_file TEXT := '_typ_filter.txt';
    seq_filter_file TEXT := '_seq_filter.txt';
    vws_script_file TEXT := '_vws_script.sql';
    vws_p_script_file TEXT := '_vws_p_script.sql';
    fnc_script_file TEXT := '_fnc_script.sql';
    prc_script_file TEXT := '_prc_script.sql';
    ddl_record RECORD;
BEGIN
	CREATE TEMPORARY TABLE TMP_LOG (id SERIAL PRIMARY KEY, entry text);
    -- Reset files
    INSERT INTO TMP_LOG (entry) VALUES ('@echo off');
    INSERT INTO TMP_LOG (entry) VALUES ('@echo. > ' || output_file);
    --INSERT INTO TMP_LOG (entry) VALUES ('echo. > ' || typ_filter_file);
    INSERT INTO TMP_LOG (entry) VALUES ('echo. > ' || seq_filter_file);
    INSERT INTO TMP_LOG (entry) VALUES ('echo. > ' || vws_script_file);
    INSERT INTO TMP_LOG (entry) VALUES ('echo. > ' || vws_p_script_file);
    INSERT INTO TMP_LOG (entry) VALUES ('echo. > ' || fnc_script_file);
    INSERT INTO TMP_LOG (entry) VALUES ('echo. > ' || prc_script_file);

    -- Define type order (customize for dependencies; EPAS packages included)
    -- We'll query and generate commands in this order
    DECLARE
        current_type TEXT;
        type_order TEXT[] := ARRAY['TYPE', 'SEQUENCE', 'PRIORITY_TABLE', 'TABLE', 'PRIORITY_VIEW', 'PACKAGE', 'TRIGGER', 'VIEW_TRIGGER', 'FUNCTION', 'PROCEDURE', 'VIEW', 'PACKAGE_BODY'];
        --type_order TEXT[] := ARRAY['PACKAGE', 'PACKAGE_BODY'];
    BEGIN
        FOREACH current_type IN ARRAY type_order
        LOOP
            CASE current_type
            /* -- type extraction is not supported yet
                WHEN 'TYPE' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- TYPES >> ' || output_file);
                    FOR ddl_record IN
                        SELECT typname AS name
                        FROM pg_type t JOIN pg_namespace n ON t.typnamespace = n.oid
                        WHERE n.nspname = target_schema AND (t.typtype = 'c' and t.typattrs is not null or t.typtype = 'N' and typstorage='p')
                        ORDER BY typname
                    LOOP
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo include table %I >> %s', ddl_record.name, typ_filter_file));
                    END LOOP;
					INSERT INTO TMP_LOG (entry) VALUES ( format('pg_dump -h %s -p %s -U %s -d %s -n %s -s -O -x --filter=%s >> %s', host, port, username, dbname, target_schema, typ_filter_file, output_file));
                */

                WHEN 'SEQUENCE' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- SEQUENCES (standalone) >> ' || output_file);
                    FOR ddl_record IN
                        SELECT relname AS name
                        FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
                        LEFT JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'a' AND d.classid = 'pg_class'::regclass
                        WHERE c.relkind = 'S' AND n.nspname = target_schema AND d.objid IS NULL  -- Standalone sequences only (owned ones are in table dumps)
                        ORDER BY relname
                    LOOP
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo include table %I >> %s', ddl_record.name, seq_filter_file));
					END LOOP;
					INSERT INTO TMP_LOG (entry) VALUES ( format('pg_dump -h %s -p %s -U %s -d %s -n %s -s -O -x --filter=%s >> %s', host, port, username, dbname, target_schema, seq_filter_file, output_file));

                WHEN 'PRIORITY_TABLE' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- PRIORITY TABLES (includes constraints, indexes, owned sequences) >> ' || output_file);
                    INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "SELECT pg_get_tabledef(n.nspname, relname, false, ''COMMENTS'') FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = ''%s'' AND c.relkind = ''r'' AND relname in (''history'', ''langcd'', ''isettinggrp_pa2'')ORDER BY relname" >> %s', host, port, username, dbname, target_schema, output_file));

                WHEN 'TABLE' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- TABLES (includes constraints, indexes, owned sequences) >> ' || output_file);
                    INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "SELECT pg_get_tabledef(n.nspname, relname, false, ''COMMENTS'') FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = ''%s'' AND c.relkind = ''r'' AND relname not in (''history'', ''langcd'', ''isettinggrp_pa2'') ORDER BY relname" >> %s', host, port, username, dbname, target_schema, output_file));

                WHEN 'TRIGGER' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- TABLE TRIGGERS (excluding view triggers) >> ' || output_file);
                    INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "select pg_get_triggerdef(t.oid, True) || '';'' as triggerdef FROM pg_trigger t, pg_class c, pg_namespace n WHERE n.nspname = ''%s'' and n.oid = c.relnamespace and c.relkind = ''r'' and t.tgrelid = c.oid and NOT t.tgisinternal order by tgname" >> %s', host, port, username, dbname, target_schema, output_file));

                WHEN 'PRIORITY_VIEW' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- PRIORITY VIEWS >> ' || output_file);
                    FOR ddl_record IN
                        SELECT relname AS name
                        FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
                        WHERE n.nspname = target_schema AND c.relkind IN ('v') AND relname in ('ptletter', 'user_procedures', 'user_view')
                        ORDER BY relname
                    LOOP
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \sv %s >> %s', ddl_record.name, vws_p_script_file));
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \echo ; >> %s', vws_p_script_file));
                    END LOOP;
					INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -f %s >> %s', host, port, username, dbname, vws_p_script_file, output_file));

                WHEN 'VIEW' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- VIEWS >> ' || output_file);
                    FOR ddl_record IN
                        SELECT relname AS name
                        FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
                        WHERE n.nspname = target_schema AND c.relkind IN ('v') AND relname not in ('ptletter', 'user_procedures', 'user_view')
                        ORDER BY relname
                    LOOP
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \sv %s >> %s', ddl_record.name, vws_script_file));
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \echo ; >> %s', vws_script_file));
                    END LOOP;
					INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -f %s >> %s', host, port, username, dbname, vws_script_file, output_file));

                WHEN 'VIEW_TRIGGER' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- VIEW TRIGGERS >> ' || output_file);
                    INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "select pg_get_triggerdef(t.oid, True) || '';'' as triggerdef FROM pg_trigger t, pg_class c, pg_namespace n WHERE n.nspname = ''%s'' and n.oid = c.relnamespace and c.relkind = ''v'' and t.tgrelid = c.oid and NOT t.tgisinternal order by tgname" >> %s', host, port, username, dbname, target_schema, output_file));

                WHEN 'FUNCTION' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- FUNCTIONS >> ' || output_file);
                    FOR ddl_record IN
                        SELECT n.nspname || '.' || p.proname || '(' || COALESCE(string_agg(format_type(t.oid, NULL), ',' ORDER BY u.ord), '') || ')' AS func
                        FROM pg_proc p
                        JOIN pg_namespace n ON n.oid = p.pronamespace
                        LEFT JOIN unnest(p.proargtypes) WITH ORDINALITY AS u(typoid, ord) ON TRUE
                        LEFT JOIN pg_type t ON t.oid = u.typoid
                        WHERE n.nspname = target_schema
                        AND p.prokind = 'f'
                        AND p.protype = 0
                        AND p.proname <> 'extract_axium_ddl'
                        GROUP BY n.nspname, p.proname, p.oid
                        ORDER BY p.proname
                    LOOP
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \sf %s >> %s', ddl_record.func, fnc_script_file));
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \echo ; >> %s', fnc_script_file));
                    END LOOP;
					INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -f %s >> %s', host, port, username, dbname, fnc_script_file, output_file));

                WHEN 'PROCEDURE' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- PROCEDURES >> ' || output_file);
                    FOR ddl_record IN
                        SELECT proname AS name, p.oid
                        FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
                        WHERE n.nspname = target_schema AND p.prokind = 'p'
                        ORDER BY proname
                    LOOP
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \sf %s >> %s', ddl_record.name, prc_script_file));
                        INSERT INTO TMP_LOG (entry) VALUES ( format('echo \echo ; >> %s', prc_script_file));
                    END LOOP;
					INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -f %s >> %s', host, port, username, dbname, prc_script_file, output_file));

                WHEN 'PACKAGE' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- PACKAGE SPECS >> ' || output_file);
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- Dependant packages first >> ' || output_file);
					INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "SELECT pkgheadsrc || '';'' FROM edb_package WHERE pkgnamespace = to_regnamespace(''%s'')::oid and pkgname in (''cursor_pkg'', ''ax_pkg'') ORDER BY pkgname desc;" >> %s', host, port, username, dbname, target_schema, output_file));
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- The rest of the packages >> ' || output_file);
                    INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "SELECT pkgheadsrc || '';'' FROM edb_package WHERE pkgnamespace = to_regnamespace(''%s'')::oid and pkgname not in (''cursor_pkg'', ''ax_pkg'') ORDER BY pkgname;" >> %s', host, port, username, dbname, target_schema, output_file));
				WHEN 'PACKAGE_BODY' THEN
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- PACKAGE BODIES >> ' || output_file);
					INSERT INTO TMP_LOG (entry) VALUES ( format('psql -h %s -p %s -U %s -d %s -t -A -c "SELECT pkgbodysrc || '';'' FROM edb_package WHERE pkgnamespace = to_regnamespace(''%s'')::oid ORDER BY pkgname;" >> %s', host, port, username, dbname, target_schema, output_file));
				ELSE
                    -- Placeholder for unhandled types
                    INSERT INTO TMP_LOG (entry) VALUES ('echo -- Skipping unhandled type: ' || current_type || ' >> ' || output_file);
            END CASE;
            INSERT INTO TMP_LOG (entry) VALUES ('echo. >> ' || output_file);  -- Separator between types
        END LOOP;
    END;

    RETURN QUERY SELECT entry FROM TMP_LOG ORDER BY id;
END $$;