ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS foreign_schema varchar(100);

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   foreign_table         VARCHAR(100);           -- Die Spalte referenziert auf diese Tabelle ...
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   foreign_column        VARCHAR(50);            -- ... und Spalte

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   isWritable            BOOLEAN DEFAULT false;

ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_gruppe_textno      INTEGER;
ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_bez_textno         INTEGER;

ALTER TABLE SettingsDyn ADD COLUMN IF NOT EXISTS sd_deleted      BOOLEAN DEFAULT False;

ALTER TABLE adk ADD COLUMN IF NOT EXISTS ad_dokversand INTEGER;
ALTER TABLE anfart ADD COLUMN IF NOT EXISTS aArt_aknr_idx  VARCHAR(30);

--- Liefert Dokumentversandbezeuchnung von ad_dokversand
CREATE FUNCTION TAdk.DokVersandTyp(IN _ad_dokversand INTEGER, OUT ad_dokversand INTEGER,  OUT ad_DokVersandBez VARCHAR) RETURNS RECORD AS $$
 BEGIN
   SELECT null, null INTO ad_dokversand, ad_DokVersandBez;
 END $$ LANGUAGE plpgsql STABLE;
 
ALTER TABLE belegdokument ADD COLUMN beld_gewicht NUMERIC(12,4);
ALTER TABLE belegpos ADD COLUMN belp_gewicht NUMERIC(12,4);


ALTER TABLE belegpos ADD COLUMN IF NOT EXISTS belp_bstat VARCHAR(2) REFERENCES auftgbs ON UPDATE CASCADE ON DELETE SET NULL; -- Statusflags1
ALTER TABLE belegpos ADD COLUMN IF NOT EXISTS belp_bstat1 VARCHAR(2) REFERENCES auftgbs ON UPDATE CASCADE ON DELETE SET NULL; -- Statusflags2
ALTER TABLE belegpos ADD COLUMN IF NOT EXISTS belp_bstat2 VARCHAR(2) REFERENCES auftgbs ON UPDATE CASCADE ON DELETE SET NULL; -- Statusflags3


/*
ALTER TABLE belegposabzu ADD COLUMN IF NOT EXISTS belpaz_type          VARCHAR(1)  DEFAULT 'E';
ALTER TABLE belegposabzu ADD COLUMN IF NOT EXISTS belpaz_pos           INTEGER;
ALTER TABLE belegposabzu ADD COLUMN IF NOT EXISTS belpaz_zutxt_int     TEXT;

ALTER TABLE belegabzu ADD COLUMN IF NOT EXISTS belaz_type          VARCHAR(1) DEFAULT 'E';
ALTER TABLE belegabzu ADD COLUMN IF NOT EXISTS belaz_pos           INTEGER;
ALTER TABLE belegabzu ADD COLUMN IF NOT EXISTS belaz_zutxt_int     TEXT;

ALTER TABLE belabzu ADD COLUMN IF NOT EXISTS beaz_type          VARCHAR(1) DEFAULT 'E';
ALTER TABLE belabzu ADD COLUMN IF NOT EXISTS beaz_pos           INTEGER;
ALTER TABLE belabzu ADD COLUMN IF NOT EXISTS beaz_zutxt_int     TEXT;

ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_type          VARCHAR(1) DEFAULT 'E';
ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_pos           INTEGER;
ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_zutxt_int     TEXT;
ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_visible       BOOLEAN NOT NULL DEFAULT TRUE;

ALTER TABLE belzeil_grund ADD COLUMN IF NOT EXISTS bz_vkp_mce       INTEGER REFERENCES artmgc;
ALTER TABLE ldsdok ADD COLUMN IF NOT EXISTS ld_ekp_mce           INTEGER REFERENCES artmgc;
ALTER TABLE ldsdok ADD COLUMN IF NOT EXISTS ld_ekp_mce           INTEGER REFERENCES artmgc;
ALTER TABLE auftg ADD COLUMN IF NOT EXISTS ag_vkp_mce           INTEGER REFERENCES artmgc;


ALTER TABLE artpruefung ADD COLUMN IF NOT EXISTS apr_doktxt     Text;
ALTER TABLE artpruefung ADD COLUMN IF NOT EXISTS apr_aufdok     BOOL NOT NULL DEFAULT FALSE;
ALTER TABLE artpruefung ADD COLUMN IF NOT EXISTS apr_abz_id     INTEGER REFERENCES abzu ON UPDATE CASCADE;

ALTER TABLE ab2ba ADD COLUMN IF NOT EXISTS a2ba_noz_id          INTEGER REFERENCES normzert;
--*/

CREATE FUNCTION TSystem.Settings__GetBool(vari varchar, defvalue boolean DEFAULT false) RETURNS boolean AS $$
    DECLARE r boolean;
              s varchar;
    BEGIN
       IF vari = 'AdminLoginAllowed' THEN RETURN true; END IF;

       SELECT s_inha INTO s FROM public.settings WHERE s_vari = vari;
       --
       IF NOT FOUND THEN
              RETURN defvalue;
       END IF;
       --
       IF UPPER(s) IN ('-1','1', 'T', 'TRUE') THEN
              RETURN true;
       ELSE
              RETURN false;
       END IF;
    END$$LANGUAGE plpgsql PARALLEL SAFE;
	
	CREATE OR REPLACE FUNCTION TSystem.Settings__GetText(vari varchar)
    RETURNS text
    AS $$
        SELECT coalesce(
                    (SELECT s_inha FROM public.settings WHERE s_vari = vari)
                    , ''
               );
    $$ LANGUAGE sql PARALLEL SAFE;

-- Hinweis: Begrenzung von den 8191 Zeichen, im PgDAC, bei vom VARCHAR ohne Längenangabe.
CREATE FUNCTION TSystem.Settings__Get(vari varchar)
    RETURNS varchar
    AS $$
        SELECT TSystem.Settings__GetText(vari);
    $$LANGUAGE sql PARALLEL SAFE;
	
	
-- Setting als BOOL setzen
CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha BOOL) RETURNS VOID AS $$
  BEGIN
    IF inha IS NULL THEN
        PERFORM TSystem.Settings__Delete(vari);
        RETURN;
    END IF;
    IF inha THEN
        PERFORM TSystem.Settings__Set(vari, '-1');  -- true
    ELSE
        PERFORM TSystem.Settings__Set(vari, '0');   -- false
    END IF;

    RETURN;
  END $$ LANGUAGE plpgsql;

--

-- Setting als VARCHAR setzen
CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha VARCHAR) RETURNS VOID AS $$
  DECLARE num NUMERIC;
  BEGIN
    IF inha IS NULL THEN
        PERFORM TSystem.Settings__Delete(vari);
        RETURN;
    END IF;
    IF EXISTS(SELECT true FROM public.settings WHERE s_inha = inha AND s_vari = vari) THEN
        RETURN;
    END IF;
    BEGIN
        num:= inha::NUMERIC(20,8); -- Cast auf max. Numeric versuchen. Länge entspr. Standard für interne Werte.
    EXCEPTION
        WHEN OTHERS THEN num:= NULL;
    END;
    UPDATE settings SET s_inha= inha, s_num_inha= num WHERE s_vari = vari;
    IF NOT FOUND THEN
        INSERT INTO settings (s_vari, s_inha, s_num_inha) VALUES (vari, inha, num);
    END IF;

    RETURN;
  END $$ LANGUAGE plpgsql;

--

-- Setting als INTEGER setzen
CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha INTEGER) RETURNS VOID AS $$
  DECLARE num NUMERIC;
  BEGIN
    IF inha IS NULL THEN
        PERFORM TSystem.Settings__Delete(vari);
        RETURN;
    END IF;
    IF EXISTS(SELECT true FROM public.settings WHERE s_inha = inha::VARCHAR AND s_vari = vari) THEN
        RETURN;
    END IF;
    BEGIN
        num:= inha::NUMERIC(20,8); -- Cast auf max. Numeric versuchen. Länge entspr. Standard für interne Werte.
    EXCEPTION
        WHEN OTHERS THEN num:= NULL;
    END;
    UPDATE settings SET s_inha= inha::VARCHAR, s_num_inha= num WHERE s_vari = vari;
    IF NOT FOUND THEN
        INSERT INTO settings (s_vari, s_inha, s_num_inha) VALUES (vari, inha::VARCHAR, num);
    END IF;

    RETURN;
  END $$ LANGUAGE plpgsql;
	

  -- Value aus einem INI-TEXT auslesen -> [Section] Name=Value
  CREATE FUNCTION TSystem.INI_GetValue(ini TEXT, section VARCHAR, name VARCHAR, defvalue VARCHAR DEFAULT NULL) RETURNS VARCHAR AS $$
  DECLARE
    sectionsinhalt TEXT;
    val VARCHAR;
  BEGIN
    --Inhalt der eingegebenen 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden
    SELECT trim(substring(ini FROM E'\\[' || section || E'\\]([^\\[]*)'), E'\r\n\t ') INTO sectionsinhalt;
    IF sectionsinhalt IS NULL THEN
      RETURN defvalue;
    END IF;

    --Value mit dem 'name' aus der 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden
    SELECT COALESCE(trim(substring(sectionsinhalt FROM E'[$\r\n]*' || name || E'[ \t]*=[ \t]*([^\r\n$]*)'), E'\r\n\t '), defvalue) INTO val;

    RETURN val;
  END $$ LANGUAGE plpgsql IMMUTABLE;
  
CREATE FUNCTION tsystem.dokutypes__find__by__parentnode_id( _parentnode_id varchar )
  RETURNS varchar
  AS $$
     SELECT null
  $$ LANGUAGE sql;  
  
CREATE FUNCTION tsystem.database__triggers_inaktiv__get(
    IN _all_schemas   boolean = false
    )
    RETURNS TABLE (
      tgname          varchar,
      src_schemaname  varchar,
      src_tablename   varchar,
      trg_schemaname  varchar,
      trg_tablename   varchar
    )
    AS $$
          -- hinweis: cast as varchar nur in pg kleiner 13?!
          SELECT tgname::varchar,
                 src_schema.nspname::varchar  AS src_schemaname,
                 src_table.relname::varchar   AS src_tablename,
                 trg_schema.nspname::varchar  AS trg_schemaname,
                 trg_table.relname::varchar   AS trg_tablename
            FROM pg_trigger
            LEFT JOIN pg_class      AS src_table  ON src_table.oid  = pg_trigger.tgrelid
            LEFT JOIN pg_class      AS trg_table  ON trg_table.oid  = pg_trigger.tgconstrrelid
            LEFT JOIN pg_namespace  AS src_schema ON src_schema.oid = src_table.relnamespace
            LEFT JOIN pg_namespace  AS trg_schema ON trg_schema.oid = trg_table.relnamespace
           WHERE tgenabled = 'D'
             AND (
                  -- Prüfung auf deaktivierte Trigger für alle Schemata
                      _all_schemas
                  -- Prüfung auf deaktivierte Trigger gilt nicht für folg. Schemata
                    -- ignore:    trigger on drop,   fkey: public -> drop, drop -> public, drop -> drop
                    -- relevant:  trigger on public, fkey: public -> public
                  OR  (
                       -- Quelle des deaktivierten Triggers bzw. Contraint-Triggers (fKey) ist nicht in folg. Schemata
                           coalesce( src_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )
                       -- analog für Ziel des Contraint-Triggers (fKey)
                       AND coalesce( trg_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )
                      )
                 )
           ORDER BY tgname
    $$ LANGUAGE sql;
		
		
		
		
--
CREATE FUNCTION TSystem.triggers__all__disable() RETURNS VOID AS $$
  DECLARE r RECORD;
  BEGIN
    BEGIN
      PERFORM TSystem.Settings__Set('triggersdisabled', True);
    EXCEPTION WHEN OTHERS THEN
      PERFORM TSystem.Settings__Set('triggersdisabled', True);
    END;

    FOR r IN
      --SELECT DISTINCT schemaname, tablename
      --FROM tablefieldinfo
      --WHERE schemaname NOT IN ('z_99_drop', 'import')
      --  AND tablename NOT IN ('tablefieldinfo')  -- es gibt ein SELECT darauf, drum lässt sich kein ALTER TABLE machen
      --  AND NOT isView
      SELECT DISTINCT table_schema AS schemaname, table_name AS tablename
      FROM information_schema.tables
      WHERE table_catalog = current_database()
        AND table_type = 'BASE TABLE'
        AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')
    LOOP
      EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' DISABLE TRIGGER ALL';
    END LOOP;
  END $$ LANGUAGE plpgsql;
--

--
CREATE FUNCTION TSystem.triggers__all__enable() RETURNS VOID AS $$
  DECLARE r RECORD;
  BEGIN
    FOR r IN  -- aus TFormDBUpdates.Up_EnableTrigger -> lokales CodeSelect
      SELECT DISTINCT table_schema AS schemaname, table_name AS tablename
      FROM information_schema.tables
      WHERE table_catalog = current_database()
        AND table_type = 'BASE TABLE'
        AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')
    LOOP
      EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' ENABLE TRIGGER ALL';
    END LOOP;

    BEGIN
      PERFORM TSystem.Settings__Set('triggersdisabled', False);
    EXCEPTION WHEN OTHERS THEN
      PERFORM TSystem.Settings__Set('triggersdisabled', False);
    END;
  END $$ LANGUAGE plpgsql;		
  

    CREATE FUNCTION TSystem.LogDebug(source VARCHAR, message TEXT, category INTEGER DEFAULT 0, context TEXT DEFAULT NULL) RETURNS VOID AS $$
      BEGIN
        RETURN;
      END $$ LANGUAGE plpgsql;
	  


-- Erstellt einen View zur Abfrage von DB-Tabellen, Views darauf, Feldern und ForeignKeys der Felder. Schließt PG_catalog und einige System-Schemata aus.
DROP VIEW IF EXISTS TSystem.tablefieldinfo_view;
CREATE VIEW TSystem.tablefieldinfo_view AS

  SELECT
    -- this filters out composite indicies and duplicated FKs on same columns
    DISTINCT ON ( n.nspname, c.relname, a.attnum )

    -- this function is used by the information schema views
    -- the bitmask works like this:
    --    b'00000100' = deleteable
    --    b'00001000' = updateable
    --    b'00010000' = insertable
    (pg_relation_is_updatable(c.oid::regclass, false)::bit(8) & b'00011100' = b'00011100' ) AS isWritable,

    c.relkind = 'v' AS isview,
    n.nspname       AS schemaname,
    c.relname       AS tablename,
    a.attnum        AS index,
    a.attname       AS field,

    -- t.typcategory,
    format_type( a.atttypid, null ) AS type,

    -- length only for string types
    CASE WHEN
        -- S = varchars und chars
        t.typcategory IN ( 'S' )
      THEN nullif(a.atttypmod,-1) - 4
      ELSE null
    END
    AS length,

    -- adsrc was removed in pg12
    pg_get_expr( d.adbin, d.adrelid ) AS "default",

    fn.nspname AS foreign_table_schema,
    ft.relname AS foreign_table_name,
    fc.attname AS foreign_column_name

  FROM
    pg_class                  AS c

    -- schemata
    LEFT JOIN pg_namespace    AS n ON
          n.oid = c.relnamespace

    -- columns
    LEFT JOIN pg_attribute    AS a ON
          c.oid = a.attrelid

    -- columntypes
    LEFT JOIN pg_type         AS t ON
          t.oid = a.atttypid

    -- default values of columns
    LEFT JOIN pg_attrdef      AS d ON
          d.adrelid = c.oid
      AND d.adnum = a.attnum

    -- FKs
    LEFT JOIN pg_constraint   AS f ON
          f.contype = 'f'             -- foreignkey
      AND f.conrelid = c.oid          -- local table
      AND array_length ( f.confkey , 1 ) < 2 -- exclude composite keys
      AND f.conkey[1] = a.attnum      -- conkey is a array over the attributes ordinal positions

    -- destination of FKs
    LEFT JOIN pg_attribute    AS fc ON
          f.confrelid  = fc.attrelid -- foreign table
      AND f.confkey[1] = fc.attnum

    LEFT JOIN pg_class        AS ft ON
          ft.oid = f.confrelid

    LEFT JOIN pg_namespace    AS fn ON
          fn.oid = ft.relnamespace

  WHERE
    -- only tables and views ( including materialized views )
        c.relkind IN ('r','v','m')

    -- p = permanent table ( default tables)
    -- u = unlogged table ( eg. tlog.auditlog )
    -- t = temporary table ( pg_temp_%, create temp table )
    AND c.relpersistence IN ('p','u')

    -- exclude certain namespaces; they are lowercase by default, except for doubleQuoted Identifier
    AND n.nspname NOT IN (
         'pg_catalog', 'information_schema', 'tcache', 'tsystem', 'sunext', 'import', 'z_99_drop', 'x_900_export'
       )

    -- hide internal fields
    -- # select attname, attnum from pg_attribute where attrelid = 'abk'::regclass::oid and attnum < 3
    -- ┌──────────────┬────────┐
    -- │   attname    │ attnum │
    -- ╞══════════════╪════════╡
    -- │ tableoid     │     -7 │
    -- │ cmax         │     -6 │
    -- │ xmax         │     -5 │
    -- │ cmin         │     -4 │
    -- │ xmin         │     -3 │
    -- │ ctid         │     -1 │
    -- │ ab_ix        │      1 │
    -- │ ab_parentabk │      2 │
    -- └──────────────┴────────┘
    AND a.attnum > 0

    -- they have been deleted anyways by the TSystem.tablefieldinfo__recreate() function
    AND a.attname NOT IN ('insert_by', 'modified_by', 'insert_date', 'modified_date')

    -- we must omit dropped columns
    AND a.attisdropped IS false


  ORDER BY schemaname, tablename, index
;
  
-- Ausgelagert aus Delphi. Ergänzt um Weitergabe der Constraints zugrundeliegender Tabelle an die Views.
CREATE OR REPLACE FUNCTION TSystem.tablefieldinfo__recreate(OUT fieldCountBefore INTEGER, OUT fieldCountAfter INTEGER) RETURNS RECORD AS $$
  BEGIN
    FieldCountBefore := COUNT(*) FROM tablefieldinfo;

    -- Einfach alles rauswerfen und neu aufbauen aus den Information-Schema Katalogen.
    TRUNCATE tablefieldinfo;

    INSERT INTO tablefieldinfo ( isWritable, isView, schemaname, tablename, index, field, type, length, def, foreign_schema, foreign_table, foreign_column)
    SELECT * FROM TSystem.TableFieldInfo_view ORDER BY schemaname, tablename, field;

    -- View ist Updatable und wird als ChildLink benutzt. Also brauchen wir Defaults und Foreign-Keys.
    PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_data', 'public', 'oplpm');
    PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_stam', 'public', 'oplpm_data');

    -- view dbrid default wert setzen
    UPDATE tablefieldinfo SET def = 'nextval(''db_id_seq'')' WHERE isView AND isWritable AND field = 'dbrid';

    FieldCountAfter :=  COUNT(*) FROM tablefieldinfo;

    --- #18294 Auditlog-Config-FieldList Refresh
    --PERFORM tlog.AuditlogConfig_OF__all__recreate();

    RETURN;
  END $$ LANGUAGE plpgsql VOLATILE;
--

CREATE OR REPLACE FUNCTION TSystem.CopyConstraintInfo( srcSchema VARCHAR,  srcTable VARCHAR,  trgSchema VARCHAR,  trgViewOrTableName VARCHAR) RETURNS VOID AS $$
  BEGIN

    -- kopiert spalten defaultwert von tabellen in view informationen
    UPDATE tablefieldinfo AS trg SET
        def            = CASE WHEN trg.isWritable THEN src.def ELSE NULL END,
        foreign_table  = src.foreign_table,
        foreign_column = src.foreign_column
      FROM tablefieldinfo AS src
      WHERE trg.schemaname = trgSchema
        AND trg.tablename  = trgViewOrTableName
        AND src.Field      = trg.field
        AND src.schemaname = srcSchema
        AND src.tablename  = srcTable;
    RETURN;
  END $$ LANGUAGE plpgsql VOLATILE;  
  
SELECT TSystem.tablefieldinfo__recreate();  


-- DELETE FROM dbupdates WHERE upd_kunde IS NOT null