set term ^;
execute block
AS
  declare tableName varchar(50);
  declare tableID integer;
  declare fieldName varchar(50);

  declare recordCount integer;
  declare maxPK bigint;

  declare generatorRoot varchar(30);

  -- Pseudo parameters:
  declare rejectInputIDs smallint;
  -- * -1 -> if new.ID null or -1 -> get sequence value
  -- * 1 -> *always* get sequence value, no matter what the input for "ID" was
  -- * otherwise -> if new.ID is null -> get sequence value

  declare overwriteTriggers smallint;
  -- * 1 -> equally named triggers will be re-created
  -- * null or <>1 -> equally named triggers won't be touched
begin
  rejectInputIDs = 0;
  overwriteTriggers = 0;
  
  for SELECT
        trim(r.Rdb$Relation_Name) TableName,
        r.Rdb$Relation_ID TableID,
        min(trim(idxFields.Rdb$Field_Name)) FieldName
      FROM
        Rdb$Indices i
        INNER JOIN Rdb$Relations r
          on i.Rdb$Relation_Name = r.Rdb$Relation_Name
        INNER JOIN Rdb$Index_Segments idxFields
          on i.Rdb$Index_Name = idxFields.Rdb$Index_Name
        INNER JOIN Rdb$Relation_Constraints rc
          on rc.Rdb$Relation_Name = r.Rdb$Relation_Name
             and rc.Rdb$Index_Name = i.Rdb$Index_Name
        INNER JOIN Rdb$Relation_Fields fld
          on fld.Rdb$Relation_Name = r.Rdb$Relation_Name
             and fld.Rdb$Field_Name = idxFields.Rdb$Field_Name
        INNER JOIN Rdb$Fields fldTypes
          on fldTypes.Rdb$Field_Name = fld.Rdb$Field_Source
      WHERE
        rc.Rdb$Constraint_Type = 'PRIMARY KEY' 
        and fldTypes.Rdb$FIELD_Type IN (7, 8, 16)
        and fldTypes.Rdb$Field_Scale > -1
      GROUP BY r.Rdb$Relation_Name,
               r.Rdb$Relation_ID
      HAVING COUNT(*) = 1
      INTO :tableName,
           :tableID,
           :fieldName do
  begin
    generatorRoot = :tableName;

    if (CHAR_LENGTH(generatorRoot) > 24) then
      generatorRoot = 'TableID ' || tableID;

    SELECT count(*)
    FROM Rdb$Generators
    WHERE trim(Rdb$Generator_Name) = 'GEN_' || :generatorRoot || '_ID' 
    INTO recordCount;
      
    if (recordCount = 0) then
       execute statement 'create sequence "GEN_' || generatorRoot || '_ID"';
        
    execute statement 'select max("' || fieldName || '") from "' || tableName || '"' 
       into maxPK;

    if ((maxPK is not null)
    and (maxPK > 0)) then
      execute statement 'alter sequence "GEN_' || generatorRoot || '_ID" restart with ' || (maxPK + 1);
  
    if (overwriteTriggers = 1) then
      recordCount = 0;
    else
      SELECT count(*)
      FROM Rdb$Triggers
      WHERE trim(Rdb$Trigger_Name) = :generatorRoot || '_BI' 
      INTO recordCount;

    if (recordCount = 0) then
    begin
      if (rejectInputIDs = 1) then
        execute statement
          'recreate trigger "'|| generatorRoot||'_BI"' || ascii_char(10) ||
          ' for "'||tableName||'"' || ascii_char(10) ||
          ' active before insert position 0' || ascii_char(10) ||
          'as' || ascii_char(10) ||
          'begin' || ascii_char(10) ||
          ' new."'||fieldName||'" = gen_id("GEN_'|| generatorRoot ||'_ID",1);' || ascii_char(10) ||
          'end';
      else if (rejectInputIDs = -1) then
        execute statement
          'recreate trigger "'|| generatorRoot||'_BI"' || ascii_char(10) ||
          ' for "'||tableName||'"' || ascii_char(10) ||
          ' active before insert position 0' || ascii_char(10) ||
          'as' || ascii_char(10) ||
          'begin' || ascii_char(10) ||
          ' if (new."'||fieldName||'" is null or new."'||fieldName||'" = 0) then' || ascii_char(10) ||
          ' new."'||fieldName||'" = gen_id("GEN_'|| generatorRoot ||'_ID",1);' || ascii_char(10) ||
          'end';
      else execute statement
          'recreate trigger "'|| generatorRoot ||'_BI"' || ascii_char(10) ||
          ' for "'||tableName||'"' || ascii_char(10) ||
          ' active before insert position 0' || ascii_char(10) ||
          'as' || ascii_char(10) ||
          'begin' || ascii_char(10) ||
          ' if (new."'||fieldName||'" is null) then' || ascii_char(10) ||
          ' new."'||fieldName||'" = gen_id("GEN_'|| generatorRoot ||'_ID",1);' || ascii_char(10) ||
          'end';
    end
  end
end^