--
-- The Categories Package
-- Extension for category synonyms
--
-- @author Bernd Schmeil (bernd@thebernd.de)
-- @author Timo Hentschel (timo@timohentschel.de)
-- @creation-date 2004-01-08
--



-- added
select define_function_args('category_synonym__convert_string','name');

--
-- procedure category_synonym__convert_string/1
--
CREATE OR REPLACE FUNCTION category_synonym__convert_string(
   p_name varchar(100)
) RETURNS varchar(200) AS $$
-- return string to build search index
DECLARE
        v_index_string	varchar(200);
BEGIN
	-- convert string to uppercase and substitute special chars
        -- TODO: complete
        v_index_string := upper (
                        replace (
                        replace (
                        replace (
                        replace (
                        replace (
                        replace (
			replace (p_name, 'ä', 'AE'), 
					 'Ä', 'AE'),
					 'ö', 'OE'),
					 'Ö', 'OE'),
					 'ü', 'UE'),
					 'Ü', 'UE'),
					 'ß', 'SS'));
					  
	return (' ' || v_index_string || ' ');
END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('category_synonym__get_similarity','len1,len2,matches');

--
-- procedure category_synonym__get_similarity/3
--
CREATE OR REPLACE FUNCTION category_synonym__get_similarity(
   p_len1 integer,
   p_len2 integer,
   p_matches bigint
) RETURNS integer AS $$
-- calculates similarity of two strings
DECLARE
BEGIN
	return (p_matches * 200 / (p_len1 + p_len2));
END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('category_synonym__search','search_text,locale');

--
-- procedure category_synonym__search/2
--
CREATE OR REPLACE FUNCTION category_synonym__search(
   p_search_text varchar(100),
   p_locale varchar(5)
) RETURNS integer AS $$
-- return id for search string
DECLARE
	v_search_text	varchar(200);
	v_query_id	integer;
	v_len		integer;
	v_i		integer;
BEGIN
	-- check if search text already exists
	select	query_id into v_query_id
	from	category_search
	where	search_text = p_search_text
	and 	locale = p_locale;

	-- simply update old search data if already exists
	if (v_query_id is not null) then
		update	category_search
		set	queried_count = queried_count + 1,
			last_queried = date('now')
		where	query_id = v_query_id;
		return (v_query_id);
	end if;

	-- get new search query id
	v_query_id := nextval ('category_search_id_seq');

	-- convert string to uppercase and substitute special chars
	v_search_text := category_synonym__convert_string (p_search_text);

	-- insert search data
	insert into category_search (query_id, search_text, locale, queried_count, last_queried)
	values (v_query_id, p_search_text, p_locale, 1, date('now'));

	-- build search index
	v_len := length (v_search_text) - 2;
	v_i := 1;
	while (v_i <= v_len) loop
		insert into category_search_index 
		values (v_query_id, substring (v_search_text, v_i , 3));
		v_i := v_i + 1;
	end loop;

	-- build search result
	insert into category_search_results
	select	v_query_id, s.synonym_id, 
		category_synonym__get_similarity (v_len, length (s.name) - 2, count(*))
	from	category_search_index si, 
		category_synonym_index i,
		category_synonyms s
	where	si.query_id = v_query_id
	and	si.trigram = i.trigram
	and	s.synonym_id = i.synonym_id
	and	s.locale = p_locale
	group by s.synonym_id, s.name;

	return (v_query_id);
END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('category_synonym__reindex','synonym_id,name,locale');

--
-- procedure category_synonym__reindex/3
--
CREATE OR REPLACE FUNCTION category_synonym__reindex(
   p_synonym_id integer,
   p_name varchar(100),
   p_locale varchar(5)
) RETURNS integer AS $$
-- build search index for synonym
DECLARE
	v_name		varchar(200);
	v_len		integer;
	v_i		integer;
BEGIN
	-- delete old search results for this synonym
	delete	from category_search_results
	where	synonym_id = p_synonym_id;

	-- delete old synonym index for this synonym
	delete	from category_synonym_index
	where	synonym_id = p_synonym_id;

	-- convert string to uppercase and substitute special chars
	v_name := category_synonym__convert_string (p_name);

	-- rebuild synonym index
	v_len := length (v_name) - 2;
	v_i := 1;
	while (v_i <= v_len) loop
		insert into category_synonym_index
		values (p_synonym_id, substring (v_name, v_i , 3));
		v_i := v_i + 1;
	end loop;

	-- rebuild search results
	insert into category_search_results
	select	s.query_id, p_synonym_id, 
		category_synonym__get_similarity (v_len, length (s.search_text) - 2, count(*))
	from	category_search_index si, 
		category_synonym_index i,
		category_search s
	where	i.synonym_id = p_synonym_id
	and	si.trigram = i.trigram
	and	si.query_id = s.query_id
	and	s.locale = p_locale
	group by s.query_id, s.search_text;

	return (1);
END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('category_synonym__new','name,locale,category_id,synonym_id');

--
-- procedure category_synonym__new/4
--
CREATE OR REPLACE FUNCTION category_synonym__new(
   p_name varchar(100),
   p_locale varchar(5),
   p_category_id integer,
   p_synonym_id integer
) RETURNS integer AS $$
DECLARE
	v_synonym_id	integer;
BEGIN
	-- get new synonym_id
	if (p_synonym_id is null) then
		v_synonym_id := nextval ('category_synonyms_id_seq');
	else 
		v_synonym_id := p_synonym_id;
	end if;

	-- insert synonym data
	insert into category_synonyms (synonym_id, category_id, locale, name, synonym_p)
	values (v_synonym_id, p_category_id, p_locale, p_name, true);

	-- insert in synonym index and search results
	PERFORM category_synonym__reindex (v_synonym_id, p_name, p_locale);

	return (v_synonym_id);
END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('category_synonym__del','synonym_id');

--
-- procedure category_synonym__del/1
--
CREATE OR REPLACE FUNCTION category_synonym__del(
   p_synonym_id integer
) RETURNS integer AS $$
-- delete synonym
DECLARE
BEGIN
	-- delete search results
	delete	from category_search_results
	where	synonym_id = p_synonym_id;

	-- delete synonym index
	delete	from category_synonym_index
	where	synonym_id = p_synonym_id;

	-- delete synonym
	delete	from category_synonyms
	where	synonym_id = p_synonym_id;

	return (1);
END;
$$ LANGUAGE plpgsql;
	


-- added
select define_function_args('category_synonym__edit','synonym_id,new_name,locale');

--
-- procedure category_synonym__edit/3
--
CREATE OR REPLACE FUNCTION category_synonym__edit(
   p_synonym_id integer,
   p_new_name varchar(100),
   p_locale varchar(5)
) RETURNS integer AS $$
DECLARE
BEGIN
	-- update synonym data
	update	category_synonyms
	set	name = p_new_name,
		locale = p_locale
	where	synonym_id = p_synonym_id;

	-- update synonym index and search results
	PERFORM category_synonym__reindex (p_synonym_id, p_new_name, p_locale);

	return (p_synonym_id);
END;
$$ LANGUAGE plpgsql;


-----
-- triggers for category synonyms
-----



--
-- procedure category_synonym__new_cat_trans_trg/0
--
CREATE OR REPLACE FUNCTION category_synonym__new_cat_trans_trg(

) RETURNS trigger AS $$
-- trigger function for inserting category translation
DECLARE
    v_synonym_id     integer;
BEGIN
	-- create synonym
    v_synonym_id := category_synonym__new (NEW.name, NEW.locale, NEW.category_id, null);

	-- mark synonym as not editable for users
    update category_synonyms
    set synonym_p = false
    where synonym_id = v_synonym_id;

    return new;
END;
$$ LANGUAGE plpgsql;



--
-- procedure category_synonym__edit_cat_trans_trg/0
--
CREATE OR REPLACE FUNCTION category_synonym__edit_cat_trans_trg(

) RETURNS trigger AS $$
-- trigger function for updating a category translation
DECLARE
    v_synonym_id    integer;
BEGIN
	-- get synonym_id of updated category translation
    select synonym_id into v_synonym_id
    from   category_synonyms
    where  category_id = OLD.category_id
           and name = OLD.name
           and locale = OLD.locale
           and synonym_p = false;

	-- update synonym
    PERFORM category_synonym__edit (v_synonym_id, NEW.name, NEW.locale);

    return new;
END;
$$ LANGUAGE plpgsql;


create trigger category_synonym__insert_cat_trans_trg 
after insert
on category_translations for each row
execute procedure category_synonym__new_cat_trans_trg();

create trigger category_synonym__update_cat_trans_trg 
before update
on category_translations for each row
execute procedure category_synonym__edit_cat_trans_trg();