CREATE EXTENSION citext;
CREATE EXTENSION btree_gin;

CREATE TABLE translation_memories (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    customer_key character varying NOT NULL,
    translation_memory_parent_id uuid NULL,
    language character varying NOT NULL,
    text_seg citext NOT NULL,
    tsv_seg tsvector,
    tsv_seg_count integer
);

CREATE INDEX idx_language_tsv_seg ON translation_memories USING gin (language, tsv_seg_count, tsv_seg);
CREATE INDEX idx_customer_language_tsv_seg ON translation_memories USING gin (customer_key, language, tsv_seg_count, tsv_seg);

CREATE FUNCTION update_tsv_seg() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
          begin
            IF TG_OP <> 'UPDATE' OR NEW.text_seg <> OLD.text_seg OR NEW.language <> OLD.language THEN
              NEW.tsv_seg = to_tsvector((CASE 
                WHEN (NEW.language = 'de') THEN 'pg_catalog.german'
                WHEN (NEW.language = 'en') THEN 'pg_catalog.english'
                WHEN (NEW.language = 'es') THEN 'pg_catalog.spanish'
                WHEN (NEW.language = 'fr') THEN 'pg_catalog.french'
                WHEN (NEW.language = 'it') THEN 'pg_catalog.italian'
                WHEN (NEW.language = 'da') THEN 'pg_catalog.danish'
                WHEN (NEW.language = 'nl') THEN 'pg_catalog.dutch'
                WHEN (NEW.language = 'fi') THEN 'pg_catalog.finnish'
                WHEN (NEW.language = 'hu') THEN 'pg_catalog.hungarian'
                WHEN (NEW.language = 'no') THEN 'pg_catalog.norwegian'
                WHEN (NEW.language = 'pt') THEN 'pg_catalog.portuguese'
                WHEN (NEW.language = 'ru') THEN 'pg_catalog.russian'
                WHEN (NEW.language = 'sv') THEN 'pg_catalog.swedish'
                WHEN (NEW.language = 'tr') THEN 'pg_catalog.turkish' 
                ELSE 'pg_catalog.simple' END)::regconfig, NEW.text_seg);
              NEW.tsv_seg_count = length(NEW.tsv_seg);
            END IF;
            RETURN NEW;
          END;
          $$;

CREATE TRIGGER trigger_tsv_seg_update_on_translation_memories 
    BEFORE INSERT OR UPDATE ON translation_memories 
    FOR EACH ROW EXECUTE FUNCTION update_tsv_seg();