refresh_customer_search.sql

refresh_customer_search.sql

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,
       -- document is obtained by concatenating:
       -- * ts_vector from customer name with weight 'A'
       setweight(to_tsvector('en', coalesce(customer.name, '')), 'A') || ' ' ||
       -- * ts_vector from term (phone or email) with weight 'B'
       setweight(to_tsvector('en', contact.term), 'B') as document,
       customer.profile_data#>>'{photo,url}' as photo
   from (
            -- retrieve all contact: phones and emails
            select phone.customer_id as customer_id,
                   'Phone' as contact_method,
                -- ignore + for international format and add national format for phone term
                   trim(leading '+' from phone.phone_number) || ' ' || phone.national_format as term,
                   phone.national_format as term_display,
                   phone.id as contact_id
            from phone
            where deleted_at is null

            union

            select email.customer_id as customer_id,
                   'Email' AS contact_method,
                   email.email_address as term,
                   email.email_address as term_display,
                   email.id as contact_id
            from email
            where deleted_at is null) contact
       join customer on customer.id = contact.customer_id
   -- filter (_customer_id, _contact_id)
   where contact.customer_id = _customer_id and
         contact.contact_id = _contact_id
)
update customer_search
set business_id             = contact_method.business_id,
   name                    = contact_method.name,
   document                = contact_method.document,
   term_display            = contact_method.term_display,
   photo                   = contact_method.photo,
   -- sets stale to false since the record was refreshed
   is_stale                = false
from contact_method
where customer_search.customer_id = _customer_id and
     customer_search.contact_id = _contact_id
returning customer_search.*;
$$;