cristianc-ty

create extension if not exists "uuid-ossp";

create table business (
   id uuid default uuid_generate_v4() primary key,
   name character varying not null
);

create table customer (
   id uuid default uuid_generate_v4() primary key,
   business_id uuid references business(id),

cristianc-ty

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';

cristianc-ty

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,

cristianc-ty

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

cristianc-ty

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;

cristianc-ty

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,

cristianc-ty

-- 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

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)

cristianc-ty

cristianc-ty