50_create_languages_translations.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', 'Initial version', _component := 'languages_translations');

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

create table const.language
(
	code                              text                                             not null primary key,
	value                             text                                             not null,
	tenant_id                         integer                  default 1               not null references auth.tenant (tenant_id),
	is_frontend_language              boolean                  default true            not null,
	is_backend_language               boolean                  default false           not null,
	is_communication_language         boolean                  default true            not null,
	frontend_logical_order            integer,
	backend_logical_order             integer,
	communication_logical_order       integer,
	is_default_frontend_language      boolean                  default false           not null,
	is_default_backend_language       boolean                  default false           not null,
	is_default_communication_language boolean                  default false           not null,
	custom_data                       jsonb
)
	inherits (public._template_timestamps);

create table translation
(
	translation_id   int generated always as identity not null primary key,
	language_code    text                             not null references const.language (code) on delete cascade,
	tenant_id        int                              not null references auth.tenant (tenant_id) default 1,
	data_group       text,
	data_object_code text,
	data_object_id   bigint,
	value            text                             not null,
	ua_search_data   text                             not null,
	ts_search_data   tsvector                         not null
) inherits (_template_timestamps);

create unique index uq_translation_code on translation (language_code, data_group, data_object_code);
create unique index uq_translation_obj_id on translation (language_code, data_group, data_object_id);

create or replace function helpers.calculate_ts_regconfig(_language_code text)
	returns regconfig
	immutable
	parallel safe
	language plpgsql
as
$$
begin
	_language_code := trim(lower(_language_code));

	if _language_code = 'en' then
		return 'english';
	elseif _language_code = 'de' then
		return 'german';
	else
		return 'english';
	end if;
end;
$$;


create or replace function helpers.calculate_translation_fields() returns trigger as
$$
begin
	if tg_op = 'INSERT' or tg_op = 'UPDATE' then
		new.ua_search_data = helpers.unaccent_text(new.value);
		new.ts_search_data = to_tsvector(helpers.calculate_ts_regconfig(new.language_code), new.value);
		return new;
	end if;
end;
$$ language plpgsql;

create trigger trg_translation
	before insert or update
	on public.translation
	for each row
execute procedure helpers.calculate_translation_fields();

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

create function const.create_language(_created_by text
, _user_id bigint
, _language_code text
, _title_in_natural_form text
, _is_frontend_language bool default true
, _is_backend_language bool default false
, _is_communication_language bool default true
, _frontend_logical_order int default null
, _backend_logical_order int default null
, _communication_logical_order int default null
, _is_default_frontend_language bool default false
, _is_default_backend_language bool default false
, _is_default_communication_language bool default false
, _custom_data jsonb default null
, _tenant_id int default 1)
	returns table
					(
						__code text
					)
	language plpgsql
as
$$
begin
	perform auth.has_permission(_user_id, 'languages.create_language', _tenant_id);

	return query
		insert into const.language (created_by, modified_by, code, value, tenant_id,
																is_frontend_language, is_backend_language, is_communication_language,
																is_default_frontend_language, is_default_backend_language,
																is_default_communication_language,
																frontend_logical_order,
																backend_logical_order,
																communication_logical_order,
																custom_data)
			values ( _created_by, _created_by, _language_code, _title_in_natural_form, _tenant_id
						 , _is_frontend_language, _is_backend_language, _is_communication_language
						 , _is_default_frontend_language, _is_default_backend_language, _is_default_communication_language
						 , _frontend_logical_order
						 , _backend_logical_order
						 , _communication_logical_order
						 , _custom_data)
			returning code;

	if (_is_default_frontend_language) then
		update const.language
		set is_default_frontend_language = false
		where code <> _language_code;
	end if;

	if (_is_default_backend_language) then
		update const.language
		set is_default_backend_language = false
		where code <> _language_code;
	end if;

	if (_is_default_communication_language) then
		update const.language
		set is_default_communication_language = false
		where code <> _language_code;
	end if;

	perform add_journal_msg(_created_by
		, _user_id
		, format('Language (code: %s) created by user: %s'
														, _language_code, _created_by)
		, 'language'
		, _data_object_code := _language_code
		, _payload := array [
			'value', _title_in_natural_form,
			'is_frontend_language', _is_frontend_language::text,
			'is_backend_language', _is_backend_language::text,
			'is_communication_language', _is_communication_language::text,
			'is_default_frontend_language', _is_default_frontend_language::text,
			'is_default_backend_language', _is_default_backend_language::text,
			'is_default_communication_language', _is_default_communication_language::text,
			'frontend_logical_order', _frontend_logical_order::text,
			'backend_logical_order', _backend_logical_order::text,
			'communication_logical_order', _communication_logical_order::text,
			'custom_data', _custom_data::text
			]
		, _event_id := 60011
		, _tenant_id := _tenant_id);
end;
$$;

create function const.update_language(_modified_by text
, _user_id bigint
, _language_code text
, _title_in_natural_form text
, _is_frontend_language bool
, _is_backend_language bool
, _is_communication_language bool default true
, _frontend_logical_order int default null
, _backend_logical_order int default null
, _communication_logical_order int default null
, _is_default_frontend_language bool default false
, _is_default_backend_language bool default false
, _is_default_communication_language bool default false
, _custom_data jsonb default null
, _tenant_id int default 1)
	returns table
					(
						__code text
					)
	language plpgsql
as
$$
begin
	perform auth.has_permission(_user_id, 'languages.update_language', _tenant_id);

	return query
		update const.language
			set modified = now(),
				modified_by = _modified_by,
				value = _title_in_natural_form,
				is_frontend_language = _is_frontend_language,
				is_backend_language = _is_backend_language,
				is_communication_language = _is_communication_language,
				is_default_frontend_language = _is_default_frontend_language,
				is_default_backend_language = _is_default_backend_language,
				is_default_communication_language = _is_default_communication_language,
				frontend_logical_order = _frontend_logical_order,
				backend_logical_order = _backend_logical_order,
				communication_logical_order = _communication_logical_order,
				custom_data = _custom_data
			where code = _language_code and tenant_id = _tenant_id
			returning code;

	perform add_journal_msg(_modified_by
		, _user_id
		, format('Language (code: %s) updated by user: %s'
														, _language_code, _modified_by)
		, 'language'
		, _data_object_code := _language_code
		, _payload := array [
			'value', _title_in_natural_form,
			'is_frontend_language', _is_frontend_language::text,
			'is_backend_language', _is_backend_language::text,
			'is_communication_language', _is_communication_language::text,
			'is_default_frontend_language', _is_default_frontend_language::text,
			'is_default_backend_language', _is_default_backend_language::text,
			'is_default_communication_language', _is_default_communication_language::text,
			'frontend_logical_order', _frontend_logical_order::text,
			'backend_logical_order', _backend_logical_order::text,
			'communication_logical_order', _communication_logical_order::text,
			'custom_data', _custom_data::text
			]
		, _event_id := 60012
		, _tenant_id := _tenant_id);
end;
$$;

create 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
$$;

create function const.get_language(_language_code text)
	returns setof const.language
	language sql
as
$$
select *
from const.language
where code = _language_code;
$$;

create function const.get_languages(_display_language_code text,
																		_is_frontend_language bool default null,
																		_is_backend_language bool default null,
																		_is_communication_language bool default null)
	returns table
					(
						__code                              text,
						__value                             text,
						__is_frontend_language              bool,
						__is_backend_language               bool,
						__is_communication_language         bool,
						__is_default_frontend_language      bool,
						__is_default_backend_language       bool,
						__is_default_communication_language bool,
						__frontend_logical_order            int,
						__backend_logical_order             int,
						__communication_logical_order       int
					)
	language sql
as
$$
select l.code
		 , coalesce(t.value, l.value)
		 , l.is_frontend_language
		 , l.is_backend_language
		 , l.is_communication_language
		 , l.is_default_frontend_language
		 , l.is_default_backend_language
		 , l.is_default_communication_language
		 , l.frontend_logical_order
		 , l.backend_logical_order
		 , l.communication_logical_order
from const.language l
			 left join translation t
								 on t.language_code = _display_language_code and data_group = 'language' and data_object_code = l.code
where (_is_frontend_language is null or is_frontend_language = _is_frontend_language)
	and (_is_backend_language is null or is_backend_language = _is_backend_language)
	and (_is_communication_language is null or is_communication_language = _is_communication_language)
order by l.code;
$$;

create function const.get_frontend_languages(_display_language_code text)
	returns table
					(
						__code        text,
						__value       text,
						__custom_data jsonb
					)
	language sql
as
$$
select l.code
		 , coalesce(t.value, l.value)
		 , l.custom_data
from const.language l
			 left join translation t
								 on t.language_code = _display_language_code and data_group = 'language' and data_object_code = l.code
where is_frontend_language = true
order by l.frontend_logical_order, coalesce(t.value, l.value);
$$;

create function const.get_backend_languages(_display_language_code text)
	returns table
					(
						__code        text,
						__value       text,
						__custom_data jsonb
					)
	language sql
as
$$
select l.code
		 , coalesce(t.value, l.value)
		 , l.custom_data
from const.language l
			 left join translation t
								 on t.language_code = _display_language_code and data_group = 'language' and data_object_code = l.code
where is_backend_language = true
order by l.backend_logical_order, coalesce(t.value, l.value);
$$;

create function const.get_communication_languages(_display_language_code text)
	returns table
					(
						__code        text,
						__value       text,
						__custom_data jsonb
					)
	language sql
as
$$
select l.code
		 , coalesce(t.value, l.value)
		 , l.custom_data
from const.language l
			 left join translation t
								 on t.language_code = _display_language_code and data_group = 'language' and data_object_code = l.code
where is_communication_language = true
order by l.communication_logical_order, coalesce(t.value, l.value);
$$;

create function const.get_default_language(
	_display_language_code text,
	_is_frontend_language bool default null,
	_is_backend_language bool default null,
	_is_communication_language bool default null)
	returns table
					(
						__code                              text,
						__value                             text,
						__is_frontend_language              bool,
						__is_backend_language               bool,
						__is_communication_language         bool,
						__is_default_frontend_language      bool,
						__is_default_backend_language       bool,
						__is_default_communication_language bool,
						__frontend_logical_order            int,
						__backend_logical_order             int,
						__communication_logical_order       int,
						__custom_data                       jsonb
					)
	language sql
as
$$
select l.code
		 , coalesce(t.value, l.value)
		 , l.is_frontend_language
		 , l.is_backend_language
		 , l.is_communication_language
		 , l.is_default_frontend_language
		 , l.is_default_backend_language
		 , l.is_default_communication_language
		 , l.frontend_logical_order
		 , l.backend_logical_order
		 , l.communication_logical_order
		 , l.custom_data
from const.language l
			 left join translation t
								 on t.language_code = _display_language_code and data_group = 'language' and data_object_code = l.code
where (_is_frontend_language is null or
			 (l.is_frontend_language = _is_frontend_language and is_default_frontend_language))
	and (_is_backend_language is null or (l.is_backend_language = _is_backend_language and is_default_backend_language))
	and (_is_communication_language is null or
			 (l.is_communication_language = _is_communication_language and is_default_communication_language))
order by l.code;
$$;

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


create or replace function public.create_translation(_created_by text, _user_id integer, _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;
$$;

-- drop function public.update_translation(_modified_by text, _user_id int, _translation_id integer,
-- 																										 _value text, _tenant_id int);
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 public.delete_translation(_deleted_by text, _user_id integer, _translation_id integer,
																										 _tenant_id integer default 1) returns SETOF translation
	language plpgsql
as
$$
begin
	perform auth.has_permission(_user_id, 'translations.delete_translation', _tenant_id);

	return query
		delete from translation
			where translation_id = _translation_id and tenant_id = _tenant_id
			returning *;

	perform add_journal_msg(_deleted_by
		, _user_id
		, format('Translation deleted by user: %s'
														, _deleted_by)
		, 'translation'
		, _data_object_id := _translation_id
		, _event_id := 60023
		, _tenant_id := _tenant_id);
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;
$$;

-- drop function search_translations(_page int, _page_size int,
--                                     _tenant_id int,
--                                     _language_code text,
--                                     _data_group text,
--                                     _data_object_code text,
--                                     _data_object_id bigint,
--                                     _value_search_text text
-- );
create or replace function public.search_translations(_display_language_code text, _page integer default 1,
																											_page_size integer default 10,
																											_tenant_id integer default null::integer,
																											_language_code text default null::text,
																											_data_group text default null::text,
																											_data_object_code text default null::text,
																											_data_object_id bigint default null::bigint,
																											_value_search_text text default null::text)
	returns TABLE
					(
						__translation_id   integer,
						__tenant_id        integer,
						__tenant_title     text,
						__language_code    text,
						__language_value   text,
						__data_group       text,
						__data_object_code text,
						__data_object_id   bigint,
						__value            text,
						__total_items      bigint
					)
	language plpgsql
as
$$
declare
begin

	return query
		with filtered_rows
					 as (select t.translation_id
										, count(*) over () as total_items
							 from translation t
							 where (_tenant_id is null
								 or tenant_id = _tenant_id)
								 and (helpers.is_empty_string(_language_code)
								 or language_code = _language_code)
								 and (helpers.is_empty_string(_data_group)
								 or lower(data_group) = lower(_data_group))
								 and (helpers.is_empty_string(_data_object_code)
								 or lower(data_object_code) = lower(_data_object_code))
								 and (_data_object_id is null
								 or data_object_id = _data_object_id)
								 and (helpers.is_empty_string(_value_search_text)
								 or
											helpers.unaccent_text(value) like '%' || helpers.unaccent_text(_value_search_text) || '%')
							 order by data_group, data_object_code, data_object_id nulls last, language_code
							 offset ((_page - 1) * _page_size) limit _page_size)
		select t.translation_id
				 , t.tenant_id
				 , te.title
				 , t.language_code
				 , tl.value
				 , t.data_group
				 , t.data_object_code
				 , t.data_object_id
				 , t.value
				 , total_items
		from filtered_rows fr
					 inner join translation t on fr.translation_id = t.translation_id
					 inner join auth.tenant te on t.tenant_id = te.tenant_id
					 left join translation tl on tl.language_code = _display_language_code and tl.data_group = 'language' and
																			 tl.data_object_code = t.language_code
		order by data_group, data_object_code, data_object_id nulls last, language_code;
end;
$$;


/***
 *    ██████   ██████  ███████ ████████        ██████ ██████  ███████  █████  ████████ ███████
 *    ██   ██ ██    ██ ██         ██          ██      ██   ██ ██      ██   ██    ██    ██
 *    ██████  ██    ██ ███████    ██    █████ ██      ██████  █████   ███████    ██    █████
 *    ██      ██    ██      ██    ██          ██      ██   ██ ██      ██   ██    ██    ██
 *    ██       ██████  ███████    ██           ██████ ██   ██ ███████ ██   ██    ██    ███████
 *
 *
 */


create or replace procedure internal.load_initial_languages_data()
	language plpgsql
as
$$
declare
	__null bigint;
begin
	perform unsecure.create_permission_as_system('Languages', _is_assignable := false);
	perform unsecure.create_permission_as_system('Create language', 'languages');
	perform unsecure.create_permission_as_system('Update language', 'languages');
	perform unsecure.create_permission_as_system('Delete language', 'languages');

	perform unsecure.create_permission_as_system('Translations', _is_assignable := false);
	perform unsecure.create_permission_as_system('Create translation', 'translations');
	perform unsecure.create_permission_as_system('Read translation', 'translations');
	perform unsecure.create_permission_as_system('Update translation', 'translations');
	perform unsecure.create_permission_as_system('Delete translation', 'translations');


	select ps.perm_set_id
	from auth.perm_set ps
		 , lateral auth.add_perm_set_permissions('update_script_v2.1', 1,
																						 ps.perm_set_id, array ['languages', 'translations'], 1)
	where code in ('system_admin',
								 'tenant_creator',
								 'tenant_admin')
	into __null;
end
$$;

call internal.load_initial_languages_data();

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