CREATE EXTENSION pg_trgm;

ALTER TABLE entries
ADD COLUMN tsvec tsvector;

CREATE FUNCTION public.update_entry_tsvec () RETURNS TRIGGER LANGUAGE plpgsql AS $function$
declare
    v_text text := '';
    x_text text := '';
begin
	select text into v_text from entry_versions ev where ev.entry_id = new.entry_id order by ev.created_at desc limit 1;
	select text into x_text from entry_executions ex where ex.entry_id = new.entry_id order by ex.created_at desc limit 1;
	update entries set tsvec =
		to_tsvector('german', coalesce(v_text, '')) ||
		to_tsvector('german', coalesce(x_text, ''))
		where id = new.entry_id;
	RETURN NEW;
END
$function$;

CREATE TRIGGER entry_versions_update_tsvec
AFTER INSERT
OR
UPDATE ON entry_versions FOR EACH ROW
EXECUTE PROCEDURE update_entry_tsvec ();

CREATE TRIGGER entry_execution_update_tsvec
AFTER INSERT
OR
UPDATE ON entry_executions FOR EACH ROW
EXECUTE PROCEDURE update_entry_tsvec ();

ALTER TABLE patients
ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

CREATE INDEX entries_tsvec_idx ON entries USING GIN (tsvec);
CREATE INDEX patients_full_name ON patients USING gin (full_name gin_trgm_ops);