update_languages_translations_v1-3.sql

/*
 GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=0&v=1&c=c&f=ANSI%20Shadow&t=STAGE%20FUNCS

 SUB GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=1&v=1&c=c&f=Banner3&t=permissions
*/

select *
from start_version_update('1.3', 'Fix of translation methods create/update/delete and copy',
													_component := 'languages_translations');

/***
 *    ████████╗██████╗  █████╗ ███╗   ██╗███████╗    ███████╗██╗   ██╗███╗   ██╗ ██████╗███████╗
 *    ╚══██╔══╝██╔══██╗██╔══██╗████╗  ██║██╔════╝    ██╔════╝██║   ██║████╗  ██║██╔════╝██╔════╝
 *       ██║   ██████╔╝███████║██╔██╗ ██║███████╗    █████╗  ██║   ██║██╔██╗ ██║██║     ███████╗
 *       ██║   ██╔══██╗██╔══██║██║╚██╗██║╚════██║    ██╔══╝  ██║   ██║██║╚██╗██║██║     ╚════██║
 *       ██║   ██║  ██║██║  ██║██║ ╚████║███████║    ██║     ╚██████╔╝██║ ╚████║╚██████╗███████║
 *       ╚═╝   ╚═╝  ╚═╝╚═╝  ╚═╝╚═╝  ╚═══╝╚══════╝    ╚═╝      ╚═════╝ ╚═╝  ╚═══╝ ╚═════╝╚══════╝
 *
 */

-- remove all translation methods

drop function if exists public.create_translation(_created_by text, _user_id integer, _language_code text,
																									_data_group text, _data_object_code text,
																									_data_object_id bigint,
																									_value text,
																									_tenant_id integer);
drop function if exists public.update_translation(_modified_by text, _user_id bigint, _translation_id integer,
																									_value text, _tenant_id integer);
drop function if exists public.update_translation(_modified_by text, _user_id integer, _translation_id integer,
																									_value text, _tenant_id integer);

drop function if exists public.delete_translation(_deleted_by text, _user_id integer, _translation_id integer,
																									_tenant_id integer);

drop function if exists public.copy_translations(_created_by text, _user_id bigint, _from_language_code text,
																								 _to_language_code text, _from_tenant_id integer, _to_tenant_id integer,
																								 _overwrite boolean, _data_group text);


create or replace function public.create_translation(_created_by text, _user_id bigint, _language_code text,
																										 _data_group text, _data_object_code text default null::text,
																										 _data_object_id bigint default null::bigint,
																										 _value text default null::text,
																										 _tenant_id integer default 1)
	returns table
					(
						__translation_id   integer,
						__tenant_id        integer,
						__tenant_title     text,
						__language_code    text,
						__data_group       text,
						__data_object_code text,
						__data_object_id   bigint,
						__value            text
					)
	language plpgsql
as
$$
declare
	__last_id int;
begin
	perform auth.has_permission(_user_id, 'translations.create_translation', _tenant_id);

	insert into translation (created_by, tenant_id, language_code, data_group, data_object_code, data_object_id, value)
	values (_created_by, _tenant_id, _language_code, _data_group, _data_object_code, _data_object_id, _value)
	returning translation_id
		into __last_id;

	perform add_journal_msg(_created_by
		, _user_id
		, format('Translation (data group: %s, data id/code: %s) created by user: %s'
														, _data_group, coalesce(_data_object_id::text, _data_object_code), _created_by)
		, 'translation'
		, _data_object_id := __last_id
		, _data_object_code := _data_object_code
		, _event_id := 60021
		, _tenant_id := _tenant_id);

	return query
		select t.translation_id
				 , t.tenant_id
				 , te.title
				 , t.language_code
				 , t.data_group
				 , t.data_object_code
				 , t.data_object_id
				 , t.value
		from translation t
					 inner join auth.tenant te on t.tenant_id = te.tenant_id
		where t.translation_id = __last_id;
end;
$$;

create or replace function public.update_translation(_modified_by text, _user_id bigint, _translation_id integer,
																										 _value text, _tenant_id integer default 1)
	returns table
					(
						__translation_id   integer,
						__tenant_id        integer,
						__language_code    text,
						__data_group       text,
						__data_object_code text,
						__data_object_id   bigint,
						__value            text
					)
	language plpgsql
as
$$
begin
	perform auth.has_permission(_user_id, 'translations.update_translation', _tenant_id);

	return query
		update translation set
			modified = now(),
			modified_by = _modified_by,
			value = _value
			where translation_id = _translation_id and tenant_id = _tenant_id
			returning translation_id
				, tenant_id
				, language_code
				, data_group
				, data_object_code
				, data_object_id
				, value;

	perform add_journal_msg(_modified_by
		, _user_id
		, format('Translation updated by user: %s'
														, _modified_by)
		, 'translation'
		, _data_object_id := _translation_id
		, _event_id := 60022
		, _tenant_id := _tenant_id);
end;
$$;

create or replace function delete_translation(_deleted_by text, _user_id bigint, _translation_id integer,
																							_tenant_id integer default 1)
	returns table
					(
						__translation_id   integer,
						__tenant_id        integer,
						__language_code    text,
						__data_group       text,
						__data_object_code text,
						__data_object_id   bigint,
						__value            text
					)
	language plpgsql
as
$$
begin
	perform
		auth.has_permission(_user_id, 'translations.delete_translation', _tenant_id);

	return query
		delete from public.translation
			where translation_id = _translation_id and tenant_id = _tenant_id
			returning translation_id
				, tenant_id
				, language_code
				, data_group
				, data_object_code
				, data_object_id
				, value;

	perform add_journal_msg(_deleted_by
		, _user_id
		, format('Translation deleted by user: %s'
														, _deleted_by)
		, _tenant_id
		, 'translation'
		, _data_object_id := _translation_id
		, _event_id := 60023);
end;
$$;

create or replace function public.copy_translations(_created_by text, _user_id bigint, _from_language_code text,
																										_to_language_code text, _from_tenant_id integer default 1,
																										_to_tenant_id integer default 1, _overwrite boolean default false,
																										_data_group text default null::text)
	returns TABLE
					(
						__operation  text,
						__rows_count bigint
					)
	language plpgsql
as
$$
begin
	perform auth.has_permission(_user_id, 'translations.create_translation', _to_tenant_id);

	return query
		with updated_rows as materialized (
			update translation
				set modified = now(),
					modified_by = _created_by,
					value = source.value
				from (select data_group, data_object_code, data_object_id, value
							from translation st
							where st.tenant_id = _from_tenant_id
								and st.language_code = _from_language_code
								and (helpers.is_empty_string(_data_group) or data_group = _data_group)) as source
				where
					_overwrite
						and translation.tenant_id = _to_tenant_id
						and translation.language_code = _to_language_code
						and translation.data_group = source.data_group
						and coalesce(translation.data_object_code, '') = coalesce(source.data_object_code, '')
						and coalesce(translation.data_object_id, 0) = coalesce(source.data_object_id, 0)
				returning translation_id)
			 , new_rows as materialized (
			insert
				into translation (created_by, modified_by, tenant_id, language_code, data_group, data_object_code,
													data_object_id, value)
					select _created_by
							 , _created_by
							 , _to_tenant_id
							 , _to_language_code
							 , t.data_group
							 , t.data_object_code
							 , t.data_object_id
							 , t.value
					from translation t
								 left join translation dt on dt.tenant_id = _to_tenant_id and
																						 dt.language_code = _to_language_code and
																						 coalesce(t.data_object_code, '') = coalesce(dt.data_object_code, '') and
																						 coalesce(t.data_object_id, 0) = coalesce(dt.data_object_id, 0)
					where t.tenant_id = _from_tenant_id
						and t.language_code = _from_language_code
						and (helpers.is_empty_string(_data_group) or t.data_group = _data_group)
						and dt is null
					returning translation_id)
		select 'updated', count(*)
		from updated_rows
		union all
		select 'created', count(*)
		from new_rows
		order by 1;
end;
$$;

create or replace function const.delete_language(_deleted_by text
, _user_id bigint
, _language_code text
, _tenant_id int default 1)
	returns table
					(
						__code text
					)
	language plpgsql
as
$$
begin
	perform auth.has_permission(_user_id, 'languages.delete_language', _tenant_id);

	return query
		delete from const.language
			where code = _language_code and tenant_id = _tenant_id
			returning code;

	perform add_journal_msg(_deleted_by
		, _user_id
		, format('Language (code: %s) deleted by user: %s'
														, _language_code, _deleted_by)
		, 'language'
		, _data_object_code := _language_code
		, _event_id := 60013
		, _tenant_id := _tenant_id);
end
$$;


select *
from stop_version_update('1.3', _component := 'languages_translations');