/*
*   Copyright (C) 2005  Golan Zakai aka Diamond.
*		<diamond@pattern.co.il>

*		Field Level Multilingual Support for Postgesql.

*   This program is free software; you can redistribute it and/or modify
*   it under the terms of the GNU General Public License as published by
*   the Free Software Foundation; either version 2 of the License, or
*   (at your option) any later version.

*   This program is distributed in the hope that it will be useful,
*   but WITHOUT ANY WARRANTY; without even the implied warranty of
*   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*   GNU General Public License for more details.

*   You should have received a copy of the GNU General Public License
*   along with this program; if not, write to the Free Software
*   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
*/
-- define types
CREATE TYPE multilingual_varchar AS (
    lIdx            character varying(3)[],
    lValuz          character varying[]	
);
CREATE TYPE multilingual_text AS (
    lIdx            character varying(3)[],
    lValuz          text[]		
);


-- translation functions ( should union to overloading technic like tophp function set )
CREATE FUNCTION get_lang_text(multilingual_text, character varying) RETURNS text
    AS $_$declare
  m_field alias for $1;
  l_name alias for $2;
  l_idx integer;
begin
  select into l_idx * from get_language_location( m_field.lIdx, l_name );
  return m_field.lValuz[l_idx];
end;$_$
    LANGUAGE plpgsql;


CREATE FUNCTION get_lang_varchar(multilingual_varchar, character varying) RETURNS character varying
    AS $_$declare
  m_field alias for $1;
  l_name alias for $2;
  l_idx integer;
begin
  select into l_idx * from get_language_location( m_field.lIdx, l_name );
  return m_field.lValuz[l_idx];
end;$_$
    LANGUAGE plpgsql;

CREATE FUNCTION get_language_location(anyarray, character varying) RETURNS integer
    AS $_$declare
  lan_array alias for $1;
  lan_code3 alias for $2;
  end_idx integer;
  cur_idx integer;
begin
  cur_idx:= array_lower( lan_array, 1 );
  end_idx:= array_upper( lan_array, 1 );
  loop

   -- RAISE NOTICE 'cur_idx is %', cur_idx;
   -- RAISE NOTICE 'end_idx is %', end_idx;
    if( lan_array[cur_idx] = lan_code3 ) then
      return cur_idx;
    end if;
    cur_idx:=cur_idx + 1;
  if( cur_idx > end_idx ) then EXIT;end if;
  if( cur_idx is null ) then EXIT;end if;
  end loop;
  return 1;end;$_$
    LANGUAGE plpgsql;
-- convert any multilingual field to php using function overload
CREATE FUNCTION tophp(multilingual_varchar) RETURNS character varying
    AS $_$declare
  m_field alias for $1;
  r character varying;
begin
  select into r * from public.tophp( m_field.lIdx, m_field.lValuz );
  return r;
end;$_$
    LANGUAGE plpgsql;

CREATE FUNCTION tophp(multilingual_text) RETURNS text
    AS $_$declare
  m_field alias for $1;
  r text;
begin
  select into r * from public.tophp( m_field.lIdx, m_field.lValuz );
  return r;
end;$_$
    LANGUAGE plpgsql;

CREATE FUNCTION tophp(character varying[], text[]) RETURNS text
    AS $_$declare
  lan_array alias for $1;
  val_array alias for $2;
  r text;
  t character varying(3);
  end_idx integer;
  cur_idx integer;
begin
  cur_idx:= array_lower( lan_array, 1 );
  end_idx:= array_upper( lan_array, 1 );
  r:= 'array(';
  loop
    RAISE NOTICE 'cur_idx is %', cur_idx;
    r:= r ||  '''' || lan_array[cur_idx] || '''=>''' || val_array[cur_idx] ||''',';

    cur_idx:=cur_idx + 1;
    if( cur_idx > end_idx ) then EXIT;end if;
    if( cur_idx < 1 ) then EXIT;end if;
    if( cur_idx is null ) then EXIT;end if;
  end loop;
  r:= r || ');';
  return r;
end;$_$
    LANGUAGE plpgsql;

CREATE FUNCTION tophp(anyarray, anyarray) RETURNS character varying
    AS $_$declare
  lan_array alias for $1;
  val_array alias for $2;
  r character varying;
  t character varying(3);
  end_idx integer;
  cur_idx integer;
begin
  cur_idx:= array_lower( lan_array, 1 );
  end_idx:= array_upper( lan_array, 1 );
  r:= 'array(';
  loop
    RAISE NOTICE 'cur_idx is %', cur_idx;
    r:= r ||  '''' || lan_array[cur_idx] || '''=>''' || val_array[cur_idx] ||''',';

    cur_idx:=cur_idx + 1;
    if( cur_idx > end_idx ) then EXIT;end if;
    if( cur_idx < 1 ) then EXIT;end if;
    if( cur_idx is null ) then EXIT;end if;
  end loop;
  r:= r || ');';
  return r;
end;$_$
    LANGUAGE plpgsql;


CREATE TABLE test (
	record_id serial,
	title multilingual_varchar,
	description multilingual_text	
);
INSERT INTO test ( title, description ) values (
  ( ARRAY[ 'ISL', 'GRM', 'USA' ], ARRAY[ 'Hey', 'Hein', 'YO!' ] ),
  ( ARRAY[ 'ISL', 'GRM' ], ARRAY[ 'ma kore gever', 'shien ne tien bo' ] )
);


SELECT 
  get_lang_varchar( title, 'GRM' ) as title,
  get_lang_text( description, 'GRM' ) as description
FROM 
  test;