--------
----	"Private" (Implementation-Specific) Utility Functions
--------

-- Fetch the language ID for a given language code
CREATE FUNCTION i18n.lang_id(in_language_code text) RETURNS integer
AS $$
	Declare
		result int;
	Begin
		If ( in_language_code Is Null )
		Then
			Raise Exception 'Function i18n.lang_id() does not accept NULL input.';
		End If;
	
		Select Into result
			id
		From	i18n.language
		Where	code = in_language_code;
	
		If ( result Is Null )
		Then
			Raise Exception 'Invalid language code "%".', in_language_code;
		End If;
	
		Return result;
	End;
$$ LANGUAGE plpgsql STABLE;

-- Fetch the language code for a given language ID
CREATE FUNCTION i18n.lang_code(in_language_id integer) RETURNS text
AS $$
	Declare
		result text;
	Begin
		If ( in_language_id Is Null )
		Then
			Raise Exception 'Function i18n.lang_code() does not accept NULL input.';
		End If;
	
		Select Into result
			code
		From	i18n.language
		Where	id = in_language_id;
	
		If ( result Is Null )
		Then
			Raise Exception 'Invalid language ID "%".', in_language_id::text;
		End If;
	
		Return result;
	End;
$$ LANGUAGE plpgsql STABLE;

--------
----	Functions for Retrieving a Particular Translation
--------

-- Basic translation: translate(column, language_code)
CREATE FUNCTION i18n.translate(in_trans_array i18n.translated_text, in_language_code text) RETURNS text
AS $$
	Declare
		test_lang_id int;
		result text;
	Begin
		-- Allow a nullable translated column to show as null, not error
		If ( in_trans_array Is Null )
		Then
			return Null;
		End If;
	
		test_lang_id := i18n.lang_id(in_language_code);	
	
		Loop
			result := in_trans_array[test_lang_id];
			
			If ( 
				result Is Not Null
				And
				result <> ''
			)
			Then
				Return result;
			End If;
	
			If ( 
				Select (fallback_id = id OR fallback_id Is Null)
				From i18n.language
				Where id = test_lang_id
			)
			Then
				Raise Warning 'Translation chain terminated without selecting a value from input.';
				Return Null;
			Else
				Select Into test_lang_id
					fallback_id
				From i18n.language
				Where id = test_lang_id;
			End If;	
		End Loop;
	End;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Set the default language for the current session, using env.set()
CREATE FUNCTION i18n.set_lang(in_language_code text) RETURNS void
AS $$
	Begin
		-- Check value is sane
		If Not Exists (
			Select *
			From	i18n.language
			Where	code = in_language_code
		)
		Then
			Raise Exception 'Invalid language code "%".', in_language_code;
		End If;
	
		Perform env.set('i18n_lang', in_language_code);
	End;
$$ LANGUAGE plpgsql;

-- Simplified translation using session default language retrieved from env.get()
CREATE FUNCTION i18n.translate(in_trans_array i18n.translated_text) RETURNS text
AS
$$
	Begin
		Return i18n.translate( in_trans_array, env.get('i18n_lang') );
	End;
$$ LANGUAGE plpgsql STABLE;

--------
----	Basic Functions for Altering Translated Text
--------

Create Function i18n.set_translation(in_trans_array i18n.translated_text, in_language_code text, in_translation text)
	Returns i18n.translated_text
AS
$$
	Declare
		target_index Int;
		highest_index Int;
		out_trans_array i18n.translated_text;
	Begin
		target_index := i18n.lang_id(in_language_code);
		If ( target_index Is Null )
		Then
			Raise Exception 'Invalid language code "%".', in_language_code;
		End If;
		
		out_trans_array := in_trans_array;
		
		-- Ensure the array is long enough, by adding one element at a time
		Loop
			highest_index := coalesce( array_upper(out_trans_array, 1), 0 );
			Exit When highest_index >= target_index;
		
			out_trans_array[highest_index + 1] := '';
		End Loop;
		
		out_trans_array[ target_index ] := in_translation;
		
		Return out_trans_array;
	End;
$$ LANGUAGE plpgsql STABLE;

Create Function i18n.delete(in_trans_array i18n.translated_text, in_language_code text)
	Returns i18n.translated_text
AS
$$
	Declare
		out_trans_array i18n.translated_text;
		target_index Int;
	Begin
		target_index := i18n.lang_id(in_language_code);
		
		-- Language clearly isn't set in input array, since the array's not that long!
		If ( target_index > array_upper(in_trans_array, 1) )
		Then
			Return in_trans_array;
		End If;
		
		out_trans_array := in_trans_array;
		out_trans_array[ target_index ] := '';
		
		Return out_trans_array;
	End;
$$ LANGUAGE plpgsql STABLE;

-- Adds any non-empty values from the second translated text value to the first
Create Function i18n.combine(in_trans_array_1 i18n.translated_text, in_trans_array_2 i18n.translated_text)
	Returns i18n.translated_text
AS
$$
	Declare
		out_trans_array i18n.translated_text;
		language_id Int;
	Begin
		out_trans_array := in_trans_array_1;
	
		For language_id In 1 .. coalesce( array_upper(in_trans_array_2, 1), 0 )
		Loop
			If ( 
				in_trans_array_2[language_id] Is Not Null
				And
				in_trans_array_2[language_id] <> ''
			)
			Then
				out_trans_array := i18n.set_translation(
					out_trans_array,
					i18n.lang_code(language_id),
					in_trans_array_2[language_id]
				);
			End If;
		End Loop;
	
		Return out_trans_array;
	End;
$$ LANGUAGE plpgsql STABLE;

--------
----	Functions for Creating Translated Text Values
--------

-- The simplest kind of translated text: one language 
Create Function i18n.translation_pair(in_language_code text, in_translation text)
	Returns i18n.translated_text
AS
$$
	Begin
		Return i18n.set_translation('{}'::i18n.translated_text, in_language_code, in_translation);
	End;
$$ LANGUAGE plpgsql STABLE;

-- Create a translated text value from a list of (language_code, translation) pairs
-- e.g. Select i18n.list_to_translation( ARRAY[ ['en','Hello'], ['fr','Bonjour'] ] );
Create Function i18n.list_to_translation(in_list_of_pairs text[][])
	Returns i18n.translated_text
AS
$$
	Declare
		out_trans_array i18n.translated_text;
		loop_index Int;
	Begin
		-- Check value is sane
		If (
			array_upper(in_list_of_pairs, 2) Is Null
			Or
			array_upper(in_list_of_pairs, 2) <> 2
		)
		Then
			Raise Exception 'Function list_to_translation() expects an N by 2 array; N by % given.', 
				coalesce(array_upper(in_list_of_pairs, 2)::text, 'NULL');
		End If;
	
		out_trans_array := '{}'::i18n.translated_text;
	
		For loop_index In 1 .. coalesce( array_upper(in_list_of_pairs, 1), 0 )
		Loop
			out_trans_array := i18n.set_translation(
				out_trans_array,
				in_list_of_pairs[loop_index][1],
				in_list_of_pairs[loop_index][2]
			);
		End Loop;
	
		Return out_trans_array;
	End;
$$ LANGUAGE plpgsql STABLE;

-- Output a translated text value as a list of (language_code, translation) pairs
-- Useful for selecting all current translations of a string for editting in an application
Create Function i18n.translation_to_list(in_trans_array i18n.translated_text)
	Returns text[][]
AS
$$
	Declare
		out_list_of_pairs text[][];
		language_id Int;
	Begin
		out_list_of_pairs := '{}'::text[][];
	
		For language_id In 1 .. coalesce( array_upper(in_trans_array, 1), 0 )
		Loop
			If (
				in_trans_array[language_id] Is Not Null
				And
				in_trans_array[language_id] <> ''
			)
			Then
				out_list_of_pairs := out_list_of_pairs 
					|| 
					ARRAY[ 
						[ i18n.lang_code(language_id), in_trans_array[language_id] ] 
					];
			End If;
		End Loop;
	
		Return out_list_of_pairs;
	End;
$$ LANGUAGE plpgsql STABLE;