-- ABS-47643/ ABS-47644

create or replace function getABSDBVersionAsNumber return number is
  functionResult number;

  major       varchar2(60);
  majornumber varchar2(60);
  minor       varchar2(60);
  minornumber varchar2(60);
  micro       varchar2(60);
  micronumber varchar2(60);
  aux         varchar2(60);

begin
  select ut.COLUMN_NAME
    into major
    from user_tab_columns ut
   where ut.TABLE_NAME = 'ABSSOLUTEDBVERSION'
     and ut.COLUMN_NAME like 'MAJOR%';

  select ut.COLUMN_NAME
    into minor
    from user_tab_columns ut
   where ut.TABLE_NAME = 'ABSSOLUTEDBVERSION'
     and ut.COLUMN_NAME like 'MINOR%';

  select ut.COLUMN_NAME
    into micro
    from user_tab_columns ut
   where ut.TABLE_NAME = 'ABSSOLUTEDBVERSION'
     and ut.COLUMN_NAME like 'MICRO%';

  SELECT REGEXP_SUBSTR(major, '(\d+)') into majornumber FROM dual;
  SELECT REGEXP_SUBSTR(minor, '(\d+)') into minornumber FROM dual;
  SELECT REGEXP_SUBSTR(micro, '(\d+)') into micronumber FROM dual;
  aux            := majornumber || minornumber || micronumber;
  functionResult := to_number(aux);

  return functionResult;
end getABSDBVersionAsNumber;
/

create or replace procedure fixAutomaticFlagSequence is
  maxPrimaryKey          number;
  nextSequenceValue      number;
  increasedSequenceValue number;
  x                      number;
  flagAdded              number;
  absDBVersion           number;
begin

  select max(a.automaticflag_id) into maxPrimaryKey from automaticflag a;

  select seq_automaticflag.nextval into nextSequenceValue from dual;

  if maxPrimaryKey > nextSequenceValue then
    x := maxPrimaryKey - nextSequenceValue;
  
    begin
      execute immediate 'ALTER SEQUENCE SEQ_AUTOMATICFLAG INCREMENT BY ' || x;
    end;
  
    select seq_automaticflag.nextval into increasedSequenceValue from dual;
  
    begin
      execute immediate 'ALTER SEQUENCE SEQ_AUTOMATICFLAG INCREMENT BY 1';
    end;
  end if;

  -- check if there are any automatic flags missing. if yes, then add them

  select count(*)
    into flagAdded
    from automaticflag af
   where af.event = 'Maximum number of washes reached';

  if flagAdded = 0 then
    insert into automaticflag
      (automaticflag_id,
       event,
       systemuser_id,
       timestamp,
       selectable,
       deletable,
       transferable,
       flagtype)
    values
      (seq_automaticflag.nextval,
       'Maximum number of washes reached',
       getminsystemuser(),
       sysdate,
       'N',
       'N',
       'Y',
       1);
    commit;
  end if;

  select count(*)
    into flagAdded
    from automaticflag af
   where af.event = 'End of norm life time reached, auto replacement';

  if flagAdded = 0 then
    insert into automaticflag
      (automaticflag_id,
       event,
       systemuser_id,
       timestamp,
       selectable,
       deletable,
       transferable,
       flagtype)
    values
      (seq_automaticflag.nextval,
       'End of norm life time reached, auto replacement',
       getminsystemuser(),
       sysdate,
       'N',
       'Y',
       'Y',
       1);
    commit;
  end if;

  select count(*)
    into flagAdded
    from automaticflag af
   where af.event = 'Repair requested by repair pointer';

  if flagAdded = 0 then
    insert into automaticflag
      (automaticflag_id,
       event,
       systemuser_id,
       timestamp,
       selectable,
       deletable,
       transferable,
       flagtype)
    values
      (seq_automaticflag.nextval,
       'Repair requested by repair pointer',
       getminsystemuser(),
       sysdate,
       'N',
       'Y',
       'Y',
       1);
    commit;
  end if;

  select count(*)
    into flagAdded
    from automaticflag af
   where af.event = 'Replacement requested using replacement button';

  if flagAdded = 0 then
    insert into automaticflag
      (automaticflag_id,
       event,
       systemuser_id,
       timestamp,
       selectable,
       deletable,
       transferable,
       flagtype)
    values
      (seq_automaticflag.nextval,
       'Replacement requested using replacement button',
       getminsystemuser(),
       sysdate,
       'N',
       'N',
       'N',
       1);
    commit;
  end if;

  select count(*)
    into flagAdded
    from automaticflag af
   where af.event = 'Return free of charge item';

  if flagAdded = 0 then
    insert into automaticflag
      (automaticflag_id,
       event,
       systemuser_id,
       timestamp,
       selectable,
       deletable,
       transferable,
       flagtype)
    values
      (seq_automaticflag.nextval,
       'Return free of charge item',
       getminsystemuser(),
       sysdate,
       'N',
       'N',
       'N',
       1);
    commit;
  end if;

  select count(*)
    into flagAdded
    from automaticflag af
   where af.event = 'Every xx washes reached';

  if flagAdded = 0 then
    insert into automaticflag
      (automaticflag_id,
       event,
       systemuser_id,
       timestamp,
       selectable,
       deletable,
       transferable,
       flagtype)
    values
      (seq_automaticflag.nextval,
       'Every xx washes reached',
       getminsystemuser(),
       sysdate,
       'N',
       'N',
       'Y',
       1);
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event =
           'Flag for return to stock due to manual decrease of max inv';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Flag for return to stock due to manual decrease of max inv',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Quit wearer';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Quit wearer',
         getminsystemuser(),
         sysdate,
         'Y',
         'N',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'End of Season';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'End of Season',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Start of Season';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Start of Season',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Missing emblem';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Missing emblem',
         getminsystemuser(),
         sysdate,
         'N',
         'Y',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Substitute garment';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Substitute garment',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Quit Mat';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Quit Mat',
         getminsystemuser(),
         sysdate,
         'Y',
         'N',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Charged for Loss';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Charged for Loss',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Temporary wearer inventory rent stop';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Temporary wearer inventory rent stop',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event =
           'Decrease max inv. after return to stock items with this flag';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Decrease max inv. after return to stock items with this flag',
         getminsystemuser(),
         sysdate,
         'Y',
         'Y',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Product contract ended for customer';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Product contract ended for customer',
         getminsystemuser(),
         sysdate,
         'Y',
         'Y',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Customer no longer active';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Customer no longer active',
         getminsystemuser(),
         sysdate,
         'Y',
         'N',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Rewash';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Rewash',
         getminsystemuser(),
         sysdate,
         'Y',
         'Y',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Show on replaced item before replacement is active';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Show on replaced item before replacement is active',
         getminsystemuser(),
         sysdate,
         'N',
         'N',
         'N',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Saving changed wearer data';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Saving changed wearer data',
         getminsystemuser(),
         sysdate,
         'Y',
         'Y',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Rewashed';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Rewashed',
         getminsystemuser(),
         sysdate,
         'Y',
         'Y',
         'Y',
         4);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Garment Dispenser';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Garment Dispenser',
         getminsystemuser(),
         sysdate,
         'Y',
         'Y',
         'Y',
         1);
      commit;
    end if;
  
    select count(*)
      into flagAdded
      from automaticflag af
     where af.event = 'Quit Wearer Inventory';
  
    if flagAdded = 0 then
      insert into automaticflag
        (automaticflag_id,
         event,
         systemuser_id,
         timestamp,
         selectable,
         deletable,
         transferable,
         flagtype)
      values
        (seq_automaticflag.nextval,
         'Quit Wearer Inventory',
         getminsystemuser(),
         sysdate,
         'Y',
         'N',
         'Y',
         1);
      commit;
    end if;
  
    absDBVersion := getABSDBVersionAsNumber;
  
    if absDBVersion >= 90002 then
      select count(*)
        into flagAdded
        from automaticflag af
       where af.event = 'Returned';
    
      if flagAdded = 0 then
        insert into automaticflag
          (automaticflag_id,
           event,
           systemuser_id,
           timestamp,
           selectable,
           deletable,
           transferable,
           flagtype)
        values
          (seq_automaticflag.nextval,
           'Returned',
           getminsystemuser(),
           sysdate,
           'N',
           'N',
           'Y',
           1);
        commit;
      end if;
    end if;
  
    if absDBVersion >= 90101 then
      select count(*)
        into flagAdded
        from automaticflag af
       where af.event = 'Maximum Number of Repairs';
    
      if flagAdded = 0 then
        insert into automaticflag
          (automaticflag_id,
           event,
           systemuser_id,
           timestamp,
           selectable,
           deletable,
           transferable,
           flagtype)
        values
          (seq_automaticflag.nextval,
           'Maximum Number of Repairs',
           getminsystemuser(),
           sysdate,
           'N',
           'Y',
           'Y',
           1);
        commit;
      end if;
    end if;
  end if;
end fixAutomaticFlagSequence;
/

execute fixAutomaticFlagSequence;

drop procedure fixAutomaticFlagSequence;

drop procedure getABSDBVersionAsNumber;

