Cristian Constantinescu cristianc-ty

	-- global fuzzy search
	-- Search for customers matching the search term in customer details
	select
	qcs."name",
	qcs."photo",
	qcs."customer_id",
	-- fetch all contact methods for the customer that match the search query
	-- * contact method term display (national number for phone, email address for email)
	array_agg(qcs."term_display"),
	-- type of contact method (Email of Phone)

	-- build a view to show all data that combines
	create view quick_customer_search as
	select customer_search.*
	from customer_search
	-- * records that are not marked as stale
	where customer_search.is_stale = false
	union all
	select r.*
	from customer_search
	-- * records that are stale => recompute the most up to date fields on the fly

	create function refresh_customer_search(_customer_id uuid, _contact_id uuid)
	returns customer_search
	security definer
	language sql
	As $$
	with contact_method as (
	-- For (_customer_id, _contact_id)
	select contact.*,
	customer.name,
	customer.business_id,

	create function customer_search_email_insert()
	returns trigger
	security definer
	language plpgsql
	as $$
	begin
	insert into customer_search(customer_id, contact_method, is_stale, term, contact_id)
	values(new.customer_id, 'Email', true, new.email_address, new.id);
	return new;
	end;

	create function customer_search_customer_update()
	returns trigger
	security definer
	language plpgsql
	as $$
	begin
	update customer_search
	set is_stale=true,
	name=new.name,
	deleted_at=new.deleted_at

	create table if not exists customer_search (
	customer_id uuid,
	contact_id uuid,
	primary key(customer_id, contact_id),
	-- contact method type, for ex. 'Phone' or 'Email'
	contact_method text,
	-- term is a value computed from the contact method
	term text,
	-- term_display is used as display value instead of the term column which is more technical
	term_display character varying,

	create extension if not exists pg_trgm;
	create extension if not exists unaccent;

	create text search configuration en ( copy = pg_catalog.english );
	alter text search configuration en
	alter mapping for asciiword, asciihword, hword_asciipart, word, hword, hword_part
	with unaccent, english_stem;

	set default_text_search_config = 'en';