-- Copyright IBM Corp. 2020, 2026
-- SPDX-License-Identifier: BUSL-1.1

begin;
  select plan(44);
  select wtt_load('widgets', 'iam');

  --------------------------------------------------------------------------------
  -- 1) testing iam_role_global table constraints and insert_role_subtype
  --------------------------------------------------------------------------------

  -- 1a) insert a valid row -> should succeed and insert_role_subtype trigger
  -- r_1111111111 is a global role with grant_scope=descendants
  -- r_2222222222 is a global role with grant_scope=children
  -- r_3333333333 is a global role with grant_scope=individual
  prepare insert_valid_global_role as
    insert into iam_role_global 
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_1111111111', 'global', true, 'descendants'),
        ('r_2222222222', 'global', true, 'children'),
        ('r_3333333333', 'global', true, 'individual');
  select lives_ok('insert_valid_global_role');


  -- verify it also created a row in base iam_role
  select is(count(*), 1::bigint) from iam_role where public_id = 'r_1111111111';
  select is(count(*), 1::bigint) from iam_role where public_id = 'r_2222222222';
  select is(count(*), 1::bigint) from iam_role where public_id = 'r_3333333333';

  -- 1b) try duplicate (public_id, grant_scope) => unique violation
  prepare insert_dup_public_id_grant_scope as
    insert into iam_role_global
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_1111111111', 'global', true, 'children');
  select throws_like(
    'insert_dup_public_id_grant_scope',
    'duplicate key value violates unique constraint "iam_role_pkey"',
    'unique(public_id) is enforced'
  );

  -- 1c) invalid grant_scope (not in iam_role_global_grant_scope_enm table)
  prepare insert_invalid_grant_scope as
    insert into iam_role_global
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_globeglobe', 'global', true, 'invalid_grant_scope');
  select throws_like(
    'insert_invalid_grant_scope',
    'insert or update on table "iam_role_global" violates foreign key constraint "iam_role_global_grant_scope_enm_fkey"',
    'invalid grant_scope must fail foreign key to iam_role_global_grant_scope_enm'
  );

  -- 1d) invalid scope_id -> must reference iam_scope_global(scope_id)
  prepare insert_bad_scope_id as
    insert into iam_role_global
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_globeglobe', 'does_not_exist', true, 'individual');
  select throws_like(
    'insert_bad_scope_id',
    'insert or update on table "iam_role" violates foreign key constraint "iam_scope_scope_id_fkey"',
    'scope_id must exist in iam_scope_global(scope_id)'
  );

  --------------------------------------------------------------------------------
  -- 2) testing insert_grant_scope_update_time trigger
  --------------------------------------------------------------------------------

  -- 2a) insert a new row (grant_this_role_scope, grant_scope) => should initialize
  prepare insert_with_grant_scope_update_time_set as
    insert into iam_role_global 
        (public_id, scope_id, grant_scope, grant_scope_update_time)
    values 
        ('r_4444444444', 'global', 'descendants', null);
  select lives_ok('insert_with_grant_scope_update_time_set');

  -- 2b) check if grant_scope_update_time is set
  select is(
    (select grant_scope_update_time is not null from iam_role_global where public_id = 'r_4444444444'),
    true,
    'grant_scope_update_time should be set with the default timestamp right after insert'
  );

  -- 2c) update grant_this_role_scope => trigger should update grant_scope_update_time timestamp
  prepare update_grant_this_role_scope as
    update iam_role_global
       set grant_this_role_scope = true
     where public_id = 'r_4444444444';
  select lives_ok('update_grant_this_role_scope');
  select is(
    (select grant_scope_update_time is not null from iam_role_global where public_id = 'r_4444444444'),
    true,
    'grant_scope_update_time should be set with the default timestamp right after insert'
  );

  --------------------------------------------------------------------------------
  -- 3) testing iam_role_global_individual_org_grant_scope table constraints
  --------------------------------------------------------------------------------
 
  -- 3a) insert invalid row: grant_scope = 'descendants'
  prepare insert_invalid_individual_org_grant_scope_descendants as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'descendants', 'p____bwidget');
  select throws_like(
    'insert_invalid_individual_org_grant_scope_descendants',
    'new row for relation "iam_role_global_individual_org_grant_scope" violates check constraint "only_individual_grant_scope_allowed"',
    'check(grant_scope = "individual") is enforced'
  );

  -- 3b) insert invalid row: grant_scope = 'children'
  prepare insert_invalid_individual_org_grant_scope_children as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'children', 'p____bwidget');
  select throws_like(
    'insert_invalid_individual_org_grant_scope_children',
    'new row for relation "iam_role_global_individual_org_grant_scope" violates check constraint "only_individual_grant_scope_allowed"',
    'check(grant_scope = "individual") is enforced'
  );

  -- 3c) insert invalid row with a scope_id that is not global
  prepare insert_invalid_iam_role_global_individual_org_grant_scope as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'o_1111111111');
  select throws_like(
        'insert_invalid_iam_role_global_individual_org_grant_scope',
        'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_scope_org_fkey"',
        'foreign key also enforces matching grant_scope=individual in iam_role_global'
  );

  -- 3d) insert invalid row where scope_id is 'global'
  prepare insert_iam_role_global_individual_scope_id as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'global');
  select throws_like(
        'insert_iam_role_global_individual_scope_id',
        'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_scope_org_fkey"',
        'check(scope_id != ''global'') is enforced'
  );

  -- 3e) insert invalid row where scope_id is a project
  prepare insert_invalid_project_into_individual_org_scope as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'p____bwidgetp____bwidget');
  select throws_like(
        'insert_iam_role_global_individual_scope_id',
        'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_scope_org_fkey"',
        'foreign key also enforces matching grant_scope=individual in iam_role_global'
  );

  -- 3f) insert into iam_role_global_individual_org_grant_scope when role grant_scope is 'children'
  prepare insert_invalid_individual_org_scope as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_2222222222', 'individual', 'o_____widget');
  select throws_like(
        'insert_invalid_individual_org_scope',
        'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_role_global_grant_scope_fkey"',
        'foreign key also enforces matching grant_scope=individual in iam_role_global'
  );

  -- 3g) insert into iam_role_global_individual_org_grant_scope when role grant_scope is descendants is not allowed
  -- r_1111111111 is a global role with grant_scope=descendants
  prepare iam_role_global_individual_org_grant_scope_role_descendants as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_1111111111', 'individual', 'o_____widget');
  select throws_like(
        'iam_role_global_individual_org_grant_scope_role_descendants',
        'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_role_global_grant_scope_fkey"',
        'foreign key also enforces matching grant_scope=individual in iam_role_global'
  );


  -- 3h) insert into iam_role_global_individual_org_grant_scope when role grant_scope is children is not allowed
  -- r_2222222222 is a global role with grant_scope=children
  prepare iam_role_global_individual_org_grant_scope_role_children as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_2222222222', 'individual', 'o_____widget');
  select throws_like(
        'iam_role_global_individual_org_grant_scope_role_children',
        'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_role_global_grant_scope_fkey"',
        'foreign key also enforces matching grant_scope=individual in iam_role_global'
  );


  --3i) insert entry with role_id that does not exist in iam_role_global
  prepare insert_invalid_role_id as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_1231231231', 'individual', 'p____bwidget');
  select throws_like(
    'insert_invalid_role_id',
    'insert or update on table "iam_role_global_individual_org_grant_scope" violates foreign key constraint "iam_role_global_fkey"',
    'foreign key enforces that role exists in iam_role_global'
  );

  -- 3j) insert into iam_role_global_individual_org_grant_scope when role grant_scope is 'individual'
  -- r_3333333333 is a global role with grant_scope=individual
  prepare insert_valid_individual_org_scope as
    insert into iam_role_global_individual_org_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'o_____widget');
  select lives_ok('insert_valid_individual_org_scope');



  --------------------------------------------------------------------------------
  -- 4) testing iam_role_global_individual_project_grant_scope table constraints
  --------------------------------------------------------------------------------
 
  -- 4a) insert invalid row: grant_scope = 'descendants'
  prepare insert_invalid_individual_project_grant_scope_descendants as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'descendants', 'p____bwidget');
  select throws_like(
    'insert_invalid_individual_project_grant_scope_descendants',
    'new row for relation "iam_role_global_individual_project_grant_scope" violates check constraint "only_individual_or_children_grant_scope_allowed"',
    'check(grant_scope in ["children", "individual"]) is enforced'
  );

  -- 4b) insert invalid row: grant_scope = 'children'
  prepare insert_invalid_individual_project_grant_scope_children as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'children', 'p____bwidget');
  select throws_like(
    'insert_invalid_individual_project_grant_scope_children',
    'insert or update on table "iam_role_global_individual_project_grant_scope" violates foreign key constraint "iam_role_global_grant_scope_fkey"',
    'foreign key to grant_scope in iam_role_global is enforced'
  );

  -- 4c) insert invalid row with a scope_id project does not exist
  prepare insert_invalid_iam_role_global_individual_project_grant_scope as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'p_1111111111');
  select throws_like(
        'insert_invalid_iam_role_global_individual_project_grant_scope',
        'insert or update on table "iam_role_global_individual_project_grant_scope" violates foreign key constraint "iam_scope_project_fkey"',
        'foreign key also enforces matching grant_scope=individual in iam_role_global'
  );

  -- 4d) insert invalid row where scope_id is 'global'
  prepare insert_invalid_project_grant_scope_global as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'global');
  select throws_like(
        'insert_invalid_project_grant_scope_global',
        'insert or update on table "iam_role_global_individual_project_grant_scope" violates foreign key constraint "iam_scope_project_fkey"',
        'check(scope_id != ''global'') is enforced'
  );

  -- 4e) insert invalid row where scope_id is an org
  prepare insert_invalid_org_into_individual_proj_scope as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'o_____widget');
  select throws_like(
        'insert_invalid_org_into_individual_proj_scope',
        'insert or update on table "iam_role_global_individual_project_grant_scope" violates foreign key constraint "iam_scope_project_fkey"',
        'foreign key enforces that scope_id is a project scope'
  );

  -- 4f) insert into iam_role_global_individual_project_grant_scope when role grant_scope is descendants is not allowed
  -- r_1111111111 is a global role with grant_scope=descendants
  prepare iam_role_global_individual_project_grant_scope_role_descendants as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_1111111111', 'individual', 'p_____widget');
  select throws_like(
        'iam_role_global_individual_project_grant_scope_role_descendants',
        'insert or update on table "iam_role_global_individual_project_grant_scope" violates foreign key constraint "iam_scope_project_fkey"',
        'foreign key enforces that scope_id is a project scope'
  );

  -- 4g) insert entry with role_id that does not exist in iam_role_global
  prepare insert_invalid_role_id_proj_grants_scope as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_1231231231', 'individual', 'p____bwidget');
  select throws_like(
    'insert_invalid_role_id_proj_grants_scope',
    'insert or update on table "iam_role_global_individual_project_grant_scope" violates foreign key constraint "iam_role_global_fkey"',
    'foreign key enforces that role exists in iam_role_global'
  );


  -- 4h) insert into iam_role_global_individual_project_grant_scope when role grant_scope is 'children' is valid
  -- r_2222222222 is a global role with grant_scope=children
  prepare insert_valid_individual_project_scope_children_grants as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_2222222222', 'children', 'p____bwidget');
  select lives_ok('insert_valid_individual_project_scope_children_grants');

  -- 4i) insert into iam_role_global_individual_project_grant_scope when role grant_scope is 'individual'
  -- r_3333333333 is a global role with grant_scope=individual
  prepare insert_valid_individual_project_scope as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_3333333333', 'individual', 'p____bwidget');
  select lives_ok('insert_valid_individual_project_scope');



  -- 4j) update to iam_role_global.grant_scope from children to individual cascades to iam_role_global_individual_project_grant_scope.grant_scope
  -- r_5555555555 is a global role with grant_scope=children
  prepare insert_valid_global_role_children_grants as
    insert into iam_role_global 
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_5555555555', 'global', true, 'children');
  select lives_ok('insert_valid_global_role_children_grants');

  -- create a row in iam_role_global_individual_project_grant_scope with grant_scope=children
  prepare insert_valid_project_scope_to_children_grants as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_5555555555', 'children', 'p____bwidget');
  select lives_ok('insert_valid_project_scope_to_children_grants');

  -- take away children grants by updating to iam_role_global to individual
  prepare update_grant_scope_to_individual as
    update iam_role_global
        set grant_scope = 'individual'
      where public_id = 'r_5555555555';
    select lives_ok('update_grant_scope_to_individual');
  
  -- check that the update cascaded to iam_role_global_individual_project_grant_scope
  -- and that the grant_scope is now individual
  select is(count(*), 1::bigint) from iam_role_global_individual_project_grant_scope where role_id = 'r_5555555555' and grant_scope = 'individual';


  -- 4k) update to iam_role_global.grant_scope from children to individual cascades to iam_role_global_individual_project_grant_scope.grant_scope
  -- r_6666666666 is a global role with grant_scope=individual and is granted individual org and project 
  prepare insert_valid_global_role_individual_grants as
    insert into iam_role_global 
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_6666666666', 'global', true, 'individual');
  select lives_ok('insert_valid_global_role_individual_grants');

  -- create a row in iam_role_global_individual_project_grant_scope with grant_scope=individual
  prepare insert_valid_project_scope_to_individual_grants as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_6666666666', 'individual', 'p____bwidget');
  select lives_ok('insert_valid_project_scope_to_individual_grants');

  -- take away children grants by updating to iam_role_global to children
  prepare update_grant_scope_to_children as
    update iam_role_global
        set grant_scope = 'children'
      where public_id = 'r_6666666666';
    select lives_ok('update_grant_scope_to_children');
  
  -- verify that iam_role_global.grant_scope is updated to children
  select is(count(*), 1::bigint) from iam_role_global where public_id = 'r_6666666666' and grant_scope = 'children';
  -- check that the update cascaded to iam_role_global_individual_project_grant_scope and iam_role_global_individual_org_grant_scope
  -- and that the grant_scope is now children
  select is(count(*), 1::bigint) from iam_role_global_individual_project_grant_scope where role_id = 'r_6666666666' and scope_id = 'p____bwidget' and grant_scope = 'children';


  -- 4l) update to iam_role_global.grant_scope from children to individual sets
  -- individually granted project scope in iam_role_global_individual_project_grant_scope grant_scope to children
  prepare insert_r8_valid_global_scope_to_individual_grants as
    insert into iam_role_global 
        (public_id, scope_id, grant_this_role_scope, grant_scope)
    values
        ('r_8888888888', 'global', true, 'children');
  select lives_ok('insert_r8_valid_global_scope_to_individual_grants');

  -- create a row in iam_role_global_individual_project_grant_scope with grant_scope=children
  prepare insert_r8_valid_project_scope_to_children_grants as
    insert into iam_role_global_individual_project_grant_scope
        (role_id, grant_scope, scope_id)
    values
        ('r_8888888888', 'children', 'p____bwidget');
  select lives_ok('insert_r8_valid_project_scope_to_children_grants');

  -- take away children grants by updating to iam_role_global to individual
  prepare update_r8_grant_scope_to_individual as
    update iam_role_global
        set grant_scope = 'individual'
      where public_id = 'r_8888888888';
    select lives_ok('update_r8_grant_scope_to_individual');
  
  -- verify that iam_role_global.grant_scope is updated to individual
  select is(count(*), 1::bigint) from iam_role_global where public_id = 'r_8888888888' and grant_scope = 'individual';
  -- check that the update deletes all individual grant scopes in iam_role_global_individual_org_grant_scope and iam_role_global_individual_project_grant_scope
  select is(count(*), 1::bigint) from iam_role_global_individual_project_grant_scope where role_id = 'r_8888888888' and grant_scope = 'individual' and scope_id = 'p____bwidget';

  select * from finish();
rollback;
