fix_scopes.sql

CREATE OR REPLACE FUNCTION pg_temp.rename_scopes(old text, new text) RETURNS void AS $$
	BEGIN
		UPDATE service_instance_scopes
			SET label = new
			WHERE label = old;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_temp.clone_scope(existing text, newv text, descv text) RETURNS void AS $$
DECLARE
	sis record;
	rsis record;
	sigs record;
BEGIN
	FOR sis IN
		INSERT INTO service_instance_scopes
		SELECT
			uuid_generate_v4(),
			service_instance_id,
			newv,
			descv,
			now(),
			now()
		FROM service_instance_scopes where label = existing
		RETURNING *
	LOOP
		FOR rsis IN
			SELECT role_id from role_service_instance_scopes
			WHERE service_instance_scope_id in (
				SELECT id from service_instance_scopes
				WHERE label = existing and service_instance_id = sis.service_instance_id
			)
		LOOP
			INSERT INTO role_service_instance_scopes(id, role_id, service_instance_scope_id, created_at, updated_at)
			VALUES (uuid_generate_v4(), rsis.role_id, sis.id, now(), now());
		END LOOP;
		FOR sigs IN
			SELECT service_instance_grant_id from service_instance_grant_scopes
			WHERE service_instance_scope_id in (
				SELECT id from service_instance_scopes
				WHERE label = existing and service_instance_id = sis.service_instance_id
			)
		LOOP
			INSERT INTO service_instance_grant_scopes(id, service_instance_grant_id, service_instance_scope_id, created_at, updated_at)
			VALUES (uuid_generate_v4(), sigs.service_instance_grant_id, sis.id, now(), now());
		END LOOP;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pg_temp.remove_scope(remove_label text) RETURNS void AS $$
	BEGIN
		DELETE FROM service_instance_grant_scopes
			WHERE service_instance_scope_id in (
				SELECT id from service_instance_scopes
				WHERE label = remove_label
			);
		DELETE FROM role_service_instance_scopes
			WHERE service_instance_scope_id in (
				SELECT id from service_instance_scopes
				WHERE label = remove_label
			);
		DELETE FROM service_instance_scopes
		WHERE label = remove_label;
	END;
$$ LANGUAGE plpgsql;

-- Renaming

SELECT pg_temp.rename_scopes('read:application_stacks', 'read:application_projects');
SELECT pg_temp.rename_scopes('read:service_env_variables', 'read:service_instance_env_variables');
SELECT pg_temp.rename_scopes('read:service_logs', 'read:service_instance_logs');
SELECT pg_temp.rename_scopes('read:service_tasks', 'read:service_instance_tasks');
SELECT pg_temp.rename_scopes('read:service_urls', 'read:service_instance_urls');
SELECT pg_temp.rename_scopes('read:stacks', 'read:projects');
SELECT pg_temp.rename_scopes('read:worker_service_schedules', 'read:worker_service_instance_schedules');
SELECT pg_temp.rename_scopes('read:organization_service_access', 'read:organization_service_instance_access');
SELECT pg_temp.rename_scopes('read:role_service_scopes', 'read:role_service_instance_scopes');
SELECT pg_temp.rename_scopes('read:service_auth_connections', 'read:service_instance_auth_connections');
SELECT pg_temp.rename_scopes('read:service_grants', 'read:service_instance_grants');
SELECT pg_temp.rename_scopes('read:service_scopes', 'read:service_instance_scopes');
SELECT pg_temp.rename_scopes('read:user_service_scopes', 'read:user_service_instance_scopes');
SELECT pg_temp.rename_scopes('write:application_stacks', 'write:application_projects');
SELECT pg_temp.rename_scopes('write:organization_service_access', 'write:organization_service_instance_access');
SELECT pg_temp.rename_scopes('write:role_service_scopes', 'write:role_service_instance_scopes');
SELECT pg_temp.rename_scopes('write:service_auth_connections', 'write:service_instance_auth_connections');
SELECT pg_temp.rename_scopes('write:service_env_variables', 'write:service_instance_env_variables');
SELECT pg_temp.rename_scopes('write:service_grants', 'write:service_instance_grants');
SELECT pg_temp.rename_scopes('write:service_scopes', 'write:service_instance_scopes');
SELECT pg_temp.rename_scopes('write:service_urls', 'write:service_instance_urls');
SELECT pg_temp.rename_scopes('read:service_urls', 'read:service_instance_urls');
SELECT pg_temp.rename_scopes('write:service_users', 'write:service_instance_users');
SELECT pg_temp.rename_scopes('write:stack_environments', 'write:project_environments');
SELECT pg_temp.rename_scopes('write:stack_roles', 'write:environment_roles');
SELECT pg_temp.rename_scopes('write:stack_users', 'write:environment_users');
SELECT pg_temp.rename_scopes('write:stacks', 'write:projects');
SELECT pg_temp.rename_scopes('write:worker_service_schedules', 'write:worker_service_instance_schedules');

-- Cloning

SELECT pg_temp.clone_scope('read:services', 'read:service_instances', 'Read service instances');
SELECT pg_temp.clone_scope('write:services', 'write:service_instances', 'Write service instances');
SELECT pg_temp.clone_scope('read:projects', 'read:project_environments', 'Read project environments');
SELECT pg_temp.clone_scope('write:projects', 'write:project_environments', 'Write project environments');

-- Removing

SELECT pg_temp.remove_scope('read:frontends');
SELECT pg_temp.remove_scope('write:frontends');
SELECT pg_temp.remove_scope('read:service_user_preferences');
SELECT pg_temp.remove_scope('write:service_user_preferences');