\echo Post-Hook SQL Scripts - Run after new events have been created.

begin;

\echo Summary of events for event_def=105
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Change event to invalid if phone notify not set.
-- Set to invalid if phone notify not set.
UPDATE action_trigger.event ate
set state='invalid'
from action.hold_request ahr
where
ahr.id=ate.target
and ate.event_def=105
and ate.state='pending'
and ahr.phone_notify is null
;

\echo Change event to invalid if hold has been fulfilled.
UPDATE action_trigger.event ate
set state='invalid'
from action.hold_request ahr
where
ahr.id=ate.target
and ate.event_def=105
and ate.state='pending'
and ahr.fulfillment_time is not null
;

\echo Change event to pending-delay if call has been sent for this customer in last 12 hours.
UPDATE action_trigger.event ate
set state='invalid',user_data='{"state": "pending-delay"}'::json
from action.hold_request ahr
where
ahr.id=ate.target
and ate.event_def=105
and ate.state='pending'
and exists (
select 1
  from action_trigger.event ates
  join action.hold_request ahrs on ates.target=ahrs.id
  where
  ates.event_def=105
  and ates.state='complete'
  and ates.update_time > now()-'12 hours'::interval
  and ahrs.usr=ahr.usr
  )
returning ate.id eventid, ahr.usr userid, ahr.id holdid
;

\echo Summary of events for event_def=105 - After changes.
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Summary of phone calls by pickup location
select aou.shortname, count(distinct ahr.usr) as patrons, count(distinct ahr.id) as items
from
action_trigger.event ate
join action.hold_request ahr on ate.target=ahr.id
join actor.org_unit aou on ahr.pickup_lib=aou.id
where
ate.event_def=105
and ate.state='pending'
group by aou.parent_ou, aou.shortname
order by aou.parent_ou, aou.shortname
;

commit;