Claims and roles

create type claim_agg_type as enum ('first','last','any','all','sum','max','min','array_agg');
create type claim_resolution_sources as enum ('global','region','merchant','team','fleet','user','role');
create type claim_value_type as enum ('integer','interval','varchar','float','date');

create table claim_types
(
	name varchar not null,
	resolution_order claim_resolution_sources[] default array['user','role','merchant','global']::claim_resolution_sources[],
	aggregator claim_agg_type default 'array_agg'::claim_agg_type ,
 	value_type claim_value_type default 'varchar',
    default_value varchar,
    scope varchar[] default array[]::varchar[],
    constraint claim_types_pk primary key (name)
);
create unique index claim_types_name_uindex on claim_types (name);

create table claims_global
(
    name varchar not null
        constraint global_claims_claim_types_name_fk
            references claim_types (name)
            on update cascade on delete cascade ,
    value varchar not null ,
    constraint global_claims_pk
        primary key (name, value)
);

create table claims_merchant
(
    name varchar not null
        constraint merchant_claims_claim_types_name_fk
            references claim_types (name)
            on update cascade on delete cascade ,
    value varchar not null ,
    merchant_id int not null
        constraint merchant_claims_merchant_id_fk
            references merchants (id)
            on update cascade on delete cascade ,
    constraint merchant_claims_pk
        primary key (name, value, merchant_id)
);

create table claims_team
(
    name varchar not null
        constraint team_claims_claim_types_name_fk
            references claim_types (name)
            on update cascade on delete cascade ,
    value varchar not null ,
    team_id int not null
        constraint team_claims_team_id_fk
            references teams (id)
            on update cascade on delete cascade ,
    constraint team_claims_pk
        primary key (name, value, team_id)
);

create table claims_fleet
(
    name varchar not null
        constraint fleet_claims_claim_types_name_fk
            references claim_types (name)
            on update cascade on delete cascade ,
    value varchar not null ,
    fleet_id int not null
        constraint fleet_claims_fleet_id_fk
            references fleets (id)
            on update cascade on delete cascade ,
    constraint fleet_claims_pk
        primary key (name, value, fleet_id)
);

create table claims_user
(
    name varchar not null
        constraint user_claims_claim_types_name_fk
            references claim_types (name)
            on update cascade on delete cascade ,
    value varchar not null,
    user_id int not null
        constraint user_claims_user_id_fk
            references users (id)
            on update cascade on delete cascade ,
    constraint user_claims_pk
        primary key (name, value, user_id)
);

create table claims_role
(
    role varchar not null,
    name varchar not null
        constraint role_claims_claim_types_name_fk
            references claim_types (name)
            on update cascade on delete cascade ,
    value varchar not null,
--     filter varchar not null default 'merchant:self',
    constraint role_claims_pk
        primary key (name, value, role)
);
-- alter table claims_role drop column filter;

CREATE INDEX role_claims_name_text_pattern_ops_idx ON claims_role(name text_pattern_ops);
CREATE INDEX global_claims_name_text_pattern_ops_idx ON claims_global(name text_pattern_ops);
CREATE INDEX fleet_claims_name_text_pattern_ops_idx ON claims_fleet(name text_pattern_ops);
CREATE INDEX merchant_claims_name_text_pattern_ops_idx ON claims_merchant(name text_pattern_ops);
CREATE INDEX user_claims_name_text_pattern_ops_idx ON claims_user(name text_pattern_ops);
CREATE INDEX team_claims_name_text_pattern_ops_idx ON claims_team(name text_pattern_ops);

CREATE OR REPLACE FUNCTION claims_agg(_arr varchar[], agg_type claim_agg_type, var_type claim_value_type, default_value varchar)
    RETURNS varchar AS
$$
DECLARE
    retVal varchar;
    typ regtype;
BEGIN
    execute 'select pg_typeof(NULL::' || var_type ||')' into typ;
    if agg_type = 'array_agg' then
        return array_to_string(_arr,',');
    elseif agg_type = 'all' then
        execute 'select coalesce(bool_and(cast(elem as boolean)),cast($2 as '|| typ ||')) from unnest($1) as elem;' using _arr, default_value into retval;
        return retVal;
    elseif agg_type = 'any' then
        execute 'select coalesce(bool_or(cast(elem as boolean)),cast($2 as '|| typ ||')) from unnest($1) as elem;' using _arr, default_value into retval;
        return retVal;
    elseif agg_type = 'first' then
        return _arr[array_lower(_arr, 1)];
    elseif agg_type = 'last' then
        return _arr[array_upper(_arr, 1)];
    elseif agg_type in ('max','min','sum') then
        execute 'select coalesce(' || agg_type ||'(cast(elem as ' || typ || ')),cast($2 as ' || typ || ' )) from unnest($1) as elem;' using _arr, default_value into retval;
        return retVal;
    end if;
    return '';
END;
$$
LANGUAGE plpgsql;

-- sample data
insert into claim_types (name, aggregator, value_type, default_value, scope)
values ('jwt.ttl','min','integer',3600,'{all}'),
       ('jwt.slide','min','integer',600,'{all}'),
       ('app.web.access','first','varchar','none','{all}'),
       ('app.web.somescreen.access','first','varchar','none','{all}'),
       ('app.web.somescreen.count','max','integer',10,'{all}'),
       ('app.web.tag','array_agg','varchar',null,'{all}'),
       ('app.web.impersonate','array_agg','varchar',null,'{all}'),
       ('identity.role','array_agg','varchar',null,'{all}')
on conflict do nothing;

insert into claims_role (role, name, value )
values ('admin', 'jwt.ttl',3000),
       ('dispatcher', 'jwt.ttl',2000),
       ('driver', 'jwt.ttl',2000),
       ('admin', 'jwt.slide',500),
       ('dispatcher', 'jwt.slide',300),
       ('driver', 'jwt.slide',200),
       ('admin', 'app.web.access','rw'),
       ('dispatcher', 'jwt.ttl','r'),
       ('admin', 'app.web.somescreen.access','rw'),
       ('impersonator', 'app.web.impersonate','any')
on conflict do nothing;

insert into claims_merchant (name, value,merchant_id )
values ('app.web.impersonate','self',1),
       ('identity.role','impersonator',1),
       ('app.web.impersonate','any', 1)
on conflict do nothing;

insert into claims_user (name, value, user_id )
values ('jwt.ttl',100000,1),
       ('jwt.slide',2000000,1),
       ('identity.role','admin',1),
       ('identity.role','driver',1)
on conflict do nothing ;

-- queries

select q.name, q.email, q.id, claims_agg(array_agg(agg),q.aggregator,q.value_type, q.default_value ) from
   lateral (select ct.aggregator,ct.value_type, ct.default_value, ct.name, uz.email, uz.id, unnest(array_remove(array[u.value,ro.value,t.value,m.value,r.value,g.value],null)) agg from users uz
   -- join claim_types ct on  ct.scope && array['sec']::varchar[]
   join claim_types ct on ct.name like any(array['app%','jwt%'])
   left join claims_user u on u.name = ct.name and u.user_id = uz.id
   left join lateral (select cr.name, cr.value, cu.user_id from claims_role cr
                join claims_user cu on  cu.name = 'identity.role' and cr.role = cu.value) ro on ro.name = ct.name and ro.user_id = uz.id
   left join teams_users tu on uz.id = tu.user_id
   left join claims_team t on t.name = ct.name and tu.team_id = t.team_id
   left join claims_fleet r on r.name = ct.name
   left join claims_merchant m on m.name = ct.name and m.merchant_id = uz.merchant_id
   left join claims_global g on g.name = ct.name
        --where ct.name = 'sec:exp'
        where uz.id = 1
        ) as q
group by q.name, q.email, q.id, q.aggregator,q.value_type, q.default_value;

select q.name, q.email,  array_agg(agg) from
    lateral (select ct.aggregator,ct.value_type, ct.default_value, ct.name, uz.email, uz.id, unnest(array_remove(array[u.value,ro.value,t.value,m.value,r.value,g.value],null)) agg from users uz
     join claim_types ct on ct.name like any(array['app%','jwt%'])
     left join claims_user u on u.name = ct.name and u.user_id = uz.id
     left join lateral (select cr.name, cr.value, cu.user_id from claims_role cr
                        join claims_user cu on  cu.name = 'identity.role' and cr.role = cu.value) ro on ro.name = ct.name and ro.user_id = uz.id
     left join teams_users tu on uz.id = tu.user_id
     left join claims_team t on t.name = ct.name and tu.team_id = t.team_id
     left join claims_fleet r on r.name = ct.name
     left join claims_merchant m on m.name = ct.name and m.merchant_id = uz.merchant_id
     left join claims_global g on g.name = ct.name

        --where ct.name = 'sec:exp'
        --where uz.id = 2
        ) as q
group by q.id, q.name, q.email, q.aggregator,q.value_type, q.default_value;