-- case 0: clean sample data
DO $$
DECLARE
  fbAdmin character varying(32) := '42D0EEB1C66F497A90DD526DC597E6F0'; 
BEGIN
  delete from ad_field_access fa
   where exists (select 1 from ad_tab_access t, ad_window_access wa where wa.ad_role_id = fbAdmin 
      and wa.ad_window_id = '140');
   delete from ad_tab_access where ad_window_access_id =   (select ad_window_access_id from ad_window_access where ad_role_id = fbAdmin and ad_window_id ='140');
   	delete from ad_role where name like 'gen-role-%';
END$$;


-- case 1: ad_tab_access to current role for all tabs in product
DO $$
DECLARE
  fbAdmin character varying(32) := '42D0EEB1C66F497A90DD526DC597E6F0';
BEGIN
  delete from ad_tab_access where ad_window_access_id =   (select ad_window_access_id from ad_window_access where ad_role_id = fbAdmin and ad_window_id ='140');
  insert into ad_tab_access (
   ad_tab_access_id   ,   ad_client_id       ,   ad_org_id          ,
   isactive           ,   created            ,   createdby          ,
   updated            ,   updatedby          ,   ad_window_access_id,
   ad_tab_id          ,   isreadwrite )
  select 
   get_uuid(),      '23C59575B9CF467C9620760EB255B389',          '0',
   'Y',          now(),          '100',
   now()            ,   '100'          ,  (select ad_window_access_id from ad_window_access where ad_role_id = fbAdmin and ad_window_id ='140'),
   ad_tab_id          ,   'Y' 
   from ad_tab where ad_window_id = '140';
END$$;

-- case 2: case 1 + ad_field_access to current role for all fields in product
DO $$
DECLARE
  fbAdmin character varying(32) := '42D0EEB1C66F497A90DD526DC597E6F0';
BEGIN
  delete from ad_field_access fa
   where exists (select 1 from ad_tab_access t, ad_window_access wa where wa.ad_role_id = fbAdmin 
      and wa.ad_window_id = '140');

  insert into ad_field_access
    (ad_field_access_id ,    ad_client_id      ,     ad_org_id         ,
     isactive          ,     created           ,     createdby         ,
     updated           ,     updatedby         ,     ad_tab_access_id  ,
     ad_field_id  ,isreadwrite     
   ) 
   select get_uuid(),     '23C59575B9CF467C9620760EB255B389',          '0',
     'Y',          now(),          '100',
     now(),        '100'        ,     ta.ad_tab_access_id  ,
     f.ad_field_id , 'N'     
     from ad_window_access wa, ad_tab_access ta, ad_tab t, ad_field f
    where wa.ad_role_id = fbAdmin 
      and wa.ad_window_id = '140'
      and ta.ad_tab_id = t.ad_tab_id
      and t.ad_tab_id in (select ad_Tab_id from ad_tab where ad_window_id = '140')
      and f.ad_tab_id = t.ad_tab_id;
END$$;

-- case 3: 10K roles with access to product window
DO $$
DECLARE 
  r numeric;
  roleId character varying(32);
BEGIN
    delete from ad_role where name like 'gen-role-%';
    FOR r IN 1..10000 LOOP
        raise notice '%', r;
        roleId := get_uuid();
        insert into ad_role (
           ad_role_id             ,          ad_client_id           ,          ad_org_id              ,
           isactive               ,          created                ,          createdby              ,
           updated                ,          name                   ,          updatedby              ,
           description            ,          userlevel              ,          c_currency_id          ,
           amtapproval            ,          ad_tree_menu_id        ,          ismanual               ,
           processing             ,          is_client_admin        ,          isadvanced             ,
           isrestrictbackend      ,          isportal               ,          isportaladmin          ,
           iswebserviceenabled    ,          istemplate             ,          recalculatepermissions 
          )
          values (
          roleId,          '23C59575B9CF467C9620760EB255B389',          '0',
          'Y',          now(),          '100',
          now(),          'gen-role-'||r,          '100',
          null,          ' CO',          '102',
          '0',           null,          'Y',
          'N',          'N',          'Y',
          'N',          'N',          'N',
          'N',          'N',          'N'
          );

          insert into ad_window_access (
            ad_window_access_id,            ad_window_id       ,            ad_role_id         ,
            ad_client_id       ,            ad_org_id          ,            isactive           ,
            created            ,            createdby          ,            updated            ,
            updatedby          ,            isreadwrite             
          ) values (
            get_uuid(),            '140',            roleId,
            '23C59575B9CF467C9620760EB255B389',            '0',            'Y',
             now(),            '100',            now(),
            '100',            'Y'
         );
    END LOOP;
END$$;