-- postgres@db01:~/eg$ cat set_users_inactive_nightly.sql
begin;
update actor.usr au
set
    active = false,
    alert_message = 'automatically set to inactive status via PINES policy / ' || alert_message,
    last_update_time = now()
-- no unfinished circulations and no circulations within the last 3 years
where not exists (
    select 1
        from action.circulation ac
        where ac.usr = au.id
        and (
            xact_finish is null or (
                now() - ac.xact_start < '3 years'::interval
            )
        )
    )
-- no hold requests placed in the last 3 years
and not exists (
    select 1
        from action.hold_request ahr
        where ahr.usr = au.id
        and (now() - request_time) < '3 years'::interval
    )
-- no owed money in either direction and no payment within the last 3 years
and not exists (
    select 1
        from money.materialized_billable_xact_summary mmbxs
        where mmbxs.usr = au.id
        and (
            balance_owed <> '0.00' or (now() - last_payment_ts) < '3 years'::interval)
    )
-- no activity entries within the last 3 years
and not exists (
    select 1
        from actor.usr_activity aua
        where aua.usr = au.id
        and (now() - event_time) < '3 years'::interval
    )
-- we only care about active users
and au.active
-- we don't care about deleted users
and not au.deleted
-- don't include non-expired users that don't otherwise meet the "inactive" criteria
and expire_date < now()
-- we don't want users that have been created within the last 3 years
and (now() - au.create_date) > '3 years'
-- restrict to patron profiles ('Patrons' = 45)
and profile in (
    select id
        from permission.grp_descendants(45)
    )
;
commit;