gistfile1.sql

begin;

drop table if exists schema_changes;
drop table if exists permissions;
drop table if exists databasechangelog;
drop table if exists databasechangeloglock;
drop table if exists customer_groups;
create table customer_groups (
    id serial primary key,
    name varchar(255) unique not null
);
alter table persons add column customer_group_id integer references customer_groups(id);
alter table users add column customer_group_id integer references customer_groups(id);

-- cria um grupo para cada cliente
do
$$
declare
    person record;
    groupid integer;
    personid integer;
begin
	for person in
		select distinct persons.id, persons.name, persons.fancy_name from persons 
			inner join user_privileges on user_privileges.person_id = persons.id 
			inner join ctes on ctes.owner_id = persons.id
			where persons.owner_id is null and ctes.status <> 0
			order by id asc
	loop
        personid := person.id;
		insert into customer_groups (name) values (person.fancy_name) returning id into groupid;
        update persons set customer_group_id = groupid where id = personid;
        raise notice 'foi criado o grupo "%" com o id % para a pessoa %.', person.fancy_name , groupid, personid;
	end loop;
end$$;

--- adiciona o id do grupo para cada usuário
do
$$
declare
    person record;
begin
	for person in
		select distinct user_privileges.user_id, user_privileges.person_id, persons.customer_group_id from user_privileges 
            inner join persons on persons.id = user_privileges.person_id
            inner join ctes on ctes.owner_id = persons.id
            where persons.owner_id is null and ctes.status <> 0
	loop
        update users set customer_group_id = person.customer_group_id where users.id = person.user_id;
        raise notice 'atualizei usuario % para o grupo %', person.user_id, person.customer_group_id;
	end loop;
end$$;
drop table if exists resources;
create table resources (
    id serial primary key,
    name varchar(100) unique not null
);
insert into resources (name) values
    ('userprivileges'),
    ('persons'),
    ('nfs'),
    ('incomes'),
    ('ctes'),
    ('outcomes'),
    ('mdfes'),
    ('costtableregions'),
    ('costtablelines'),
    ('costtableroutes'),
    ('costtablefees'),
    ('costtabletaxes'),
    ('costtables'),
    ('outsourcedpayorders'),
    ('pendencies'),
    ('reports'),
    ('invoices'),
    ('cces'),
    ('invoicewallets'),
    ('costcenters'),
    ('cargoschedulings'),
    ('transportoperations'),
    ('pendencytypes'),
    ('transparencylaw'),
    ('detachedicms'),
    ('vehicles');
    
alter table user_privileges add column resource_id integer references resources(id);

-- adiciona permissão para todos os usuários e todas as empresas
-- deleta permissoes com resource_id nulos
do
$$
declare
    u record;
    p record;
    r record;
begin
	for u in
		select * from users order by id
	loop
		if u.active then
		    raise notice 'usuario % esta ativo.', u.username;
		    for p in select * FROM user_privileges where user_id = u.id loop
                for r in select * from resources loop
                    insert into user_privileges (user_id, person_id, resource_id) values (p.user_id, p.person_id, r.id);
                    raise notice 'Permissão Usuario: % Pessoa: % Resource: %', u.username, p.person_id, r.name;
                end loop;
		    end loop;
		else
		    raise notice 'usuario % esta inativo.', u.username;
		end if;
	end loop;
	delete from user_privileges where resource_id is null;
end$$;