V14.0.0.20190618__new_permission_model.sql

--remove artifacts (privileges to non-existing projects)
delete from privilege_table where (type = 'VIEW_PROJECT' or type ='EDIT_COMMENTS_PROJECT') and not id_object in (select id from project_table); 

alter table privilege_table rename column id_object to object_id;
delete from privilege_table where level = 0;
alter table privilege_table drop column level;
alter table privilege_table drop column privilege_class_type_db;

insert into privilege_table (type, object_id)
values ('IS_ADMIN',  null);

insert into privilege_table (type, object_id)
values ('IS_CURATOR',  null);

insert into privilege_table (type, object_id)
values ('CAN_CREATE_OVERLAYS',  null);

insert into privilege_table (type, object_id)
select 'READ_PROJECT', id
from project_table;

insert into privilege_table (type, object_id)
select 'WRITE_PROJECT', id
from project_table;

create table user_privilege_map_table (
    user_id integer not null references user_table(id),
    privilege_id integer not null references privilege_table(id)
);

insert into user_privilege_map_table (user_id, privilege_id)
select s1.user_id, s2.id
from (select user_id, object_id from privilege_table where type = 'VIEW_PROJECT') s1
inner join (select id, object_id from privilege_table where type = 'READ_PROJECT') s2
on s1.object_id = s2.object_id;

insert into user_privilege_map_table (user_id, privilege_id)
select s1.user_id, s2.id 
from (select user_id, object_id from privilege_table where type = 'VIEW_PROJECT' and user_id in (select user_id from privilege_table where type = 'ADD_MAP')) s1
inner join (select id, object_id from privilege_table where type = 'WRITE_PROJECT') s2
on s1.object_id = s2.object_id;

insert into user_privilege_map_table (user_id, privilege_id)
select user_id, (select id from privilege_table where type = 'IS_ADMIN')
from privilege_table where type = 'USER_MANAGEMENT';

insert into user_privilege_map_table (user_id, privilege_id)
select user_id, (select id from privilege_table where type = 'IS_CURATOR')
from privilege_table where type = 'ADD_MAP';

insert into user_privilege_map_table (user_id, privilege_id)
select user_id, (select id from privilege_table where type = 'CAN_CREATE_OVERLAYS')
from privilege_table where type = 'CUSTOM_LAYOUTS';

delete from privilege_table where type = 'VIEW_PROJECT'
                               or type = 'ADD_MAP'
                               or type = 'EDIT_COMMENTS_PROJECT'
                               or type = 'PROJECT_MANAGEMENT'
                               or type = 'USER_MANAGEMENT'
                               or type = 'CUSTOM_LAYOUTS'
                               or type = 'LAYOUT_VIEW'
                               or type = 'CONFIGURATION_MANAGE'
                               or type = 'LAYOUT_MANAGEMENT'
                               or type = 'MANAGE_GENOMES'
                               or type = 'MANAGE_PLUGINS';

delete from user_privilege_map_table t1 using user_privilege_map_table t2
where t1.CTID != t2.CTID
  and t1.user_id = t2.user_id
  and t1.privilege_id = t2.privilege_id;

alter table privilege_table drop column user_id;

alter table privilege_table add constraint unique_rows unique (type, object_id);

alter table user_privilege_map_table add primary key (user_id, privilege_id);


-- objectId is now a String to account for non DB generated ids
alter table privilege_table alter column object_id type varchar;

-- change id to project_id for project prvileges
update privilege_table set object_id = (select project_id from project_table where id::text = object_id) where object_id is not null;