drop function category_synonym__convert_string (varchar);
drop function category_synonym__get_similarity (integer, integer, bigint);
drop function category_synonym__search (varchar, varchar);
drop function category_synonym__reindex (integer, varchar, varchar);
drop function category_synonym__new (varchar, varchar, integer, integer);
drop function category_synonym__del (integer);
drop function category_synonym__edit (integer, varchar, varchar);
drop function category_synonym__edit_cat_trans_trg () cascade;
drop function category_synonym__new_cat_trans_trg () cascade;

drop table category_search_results;
drop table category_search_index;
drop table category_search;
drop table category_synonym_index;
drop table category_synonyms;
drop sequence category_search_id_seq;
drop sequence category_synonyms_id_seq;

-----
-- Synonyms
-----

create table category_synonyms (
	synonym_id	integer not null
			constraint category_synonyms_pk primary key,
	category_id	integer not null
			constraint category_synonyms_cat_fk
			references categories on delete cascade,
	locale		varchar(5) not null
			constraint category_synonyms_locale_fk
			references ad_locales on delete cascade,
	name		varchar(100) not null,
	synonym_p	char(1) default 't'
			constraint category_synonyms_synonym_p_ck
			check (synonym_p in ('t','f'))
);

-- to get all synonyms in given locale
create index category_synonyms_locale_ix on category_synonyms(category_id, locale);
-- to sort synonyms by name
create index category_synonyms_name_ix on category_synonyms(category_id, name);

create sequence category_synonyms_id_seq;

comment on table category_synonyms is '
  Stores multilingual synonyms of the categories.
';
comment on column category_synonyms.synonym_id is '
  Primary key.
';
comment on column category_synonyms.category_id is '
  Category the synonyms are referring to.
';
comment on column category_synonyms.locale is '
  Language of the synonym.
';
comment on column category_synonyms.name is '
  Actual synonym of the category in given language.
';
comment on column category_synonyms.synonym_p is '
  Marks if the entry is a synonym to be edited by user or is a copy
  of a category translation and cannot be edited directly.
';

create table category_synonym_index (
        -- category synonyms split up in 3-grams to be used by fuzzy search
        synonym_id	integer not null
                	constraint category_synonym_index_fk
                	references category_synonyms on delete cascade,
        trigram		char(3) not null
);

-- to get all synonyms of given 3-gram
create index category_syn_index_trigram_ix on category_synonym_index(trigram);
-- to delete all 3-grams of given synonym
create index category_syn_index_synonym_ix on category_synonym_index(synonym_id);

comment on table category_synonym_index is '
  Stores the synonym cut down in portions of 3 characters
  to be used in search.
';
comment on column category_synonym_index.synonym_id is '
  Id of the synonym referred to.
';
comment on column category_synonym_index.trigram is '
  3 character part of the synonym.
';

create table category_search (
        query_id	integer not null
                	constraint category_search_id_pk primary key,
        search_text	varchar(200) not null,
	locale		varchar(5) not null
			constraint category_search_locale_fk
			references ad_locales on delete cascade,
        queried_count	integer default 1 not null,
        last_queried	timestamptz default current_timestamp not null,
        constraint category_search_query_un
	unique (search_text, locale)
);

-- to delete old queries
create index category_search_date_ix on category_search(last_queried);

create sequence category_search_id_seq;

comment on table category_search is '
  Stores users multilingual search texts for category synonyms.
';
comment on column category_search.query_id is '
  Primary key.
';
comment on column category_search.locale is '
  Language of the search text.
';
comment on column category_search.search_text is '
  Actual search text in given language.
';
comment on column category_search.queried_count is '
  Counts how often this search text has been used by users.
';
comment on column category_search.last_queried is '
  Date of last usage of this search text.
  A sweeper will delete search texts not used for a while.
';

create table category_search_index (
        query_id	integer not null
                	constraint category_search_index_fk
                	references category_search on delete cascade,
        trigram		char(3) not null
);

-- to get all search texts of given 3-gram
create index category_search_ind_trigram_ix on category_search_index(trigram);
-- to delete all 3-grams of given search text
create index category_search_ind_query_ix on category_search_index(query_id);

comment on table category_search_index is '
  Stores the search text cut down in portions of 3 characters
  to be used in search.
';
comment on column category_search_index.query_id is '
  Id of the search text referred to.
';
comment on column category_search_index.trigram is '
  3 character part of the search text.
';

create table category_search_results (
        query_id	integer not null
                	constraint category_results_query_fk
                	references category_search on delete cascade,
        synonym_id	integer not null
                	constraint category_results_synonym_fk
                	references category_synonyms on delete cascade,
        similarity	integer not null,
	constraint category_search_results_pk
        primary key (query_id, synonym_id)
);

-- to sort all matches found by similarity
create index category_results_similarity_ix on category_search_results (query_id, similarity);

comment on table category_search_results is '
  Stores the result of a users search in synonyms,
  stores matching synonyms and their degree of similarity
  to the search text.
';
comment on column category_search_results.query_id is '
  Id of the search text.
';
comment on column category_search_results.synonym_id is '
  Id of the synonym found.
';
comment on column category_search_results.similarity is '
  Percent of similarity between search text and found synonym.
';

\i ../category-synonym-package.sql

-- insert existing category translations as synonyms
-- and build synonym index


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

) RETURNS integer AS $$
DECLARE
  rec_translations record;
  v_synonym_id integer;
BEGIN
  for rec_translations in
    select category_id, name, locale
    from   category_translations
  loop
    v_synonym_id := category_synonym__new (rec_translations.name, rec_translations.locale, rec_translations.category_id, null);
    update category_synonyms set synonym_p = 'f' where synonym_id = v_synonym_id;
  end loop;
  return 0;
END;
$$ LANGUAGE plpgsql;
select inline_0 ();
drop function inline_0 ();