user delete SQL

select au.id
into actor.usrs_to_delete_2024_09_20
from actor.usr au
join permission.grp_tree grp on (au.profile = grp.id)
where
( -- begin OR
grp.name in (
'Digital Only',
'GLS',
'Homebound',
'NonResident',
'OutOfState',
'Patron',
'Quipu',
'Restricted',
'Temp',
'TempRes12',
'TempRes6',
'Trustee'
)

and date(au.expire_date) < date(now()) - '4 years'::interval
-- no owed money in either direction and no payment within the last 4 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) < '4 years'::interval)
    )
-- no activity entries within the last 4 years
and not exists (
    select 1
         from actor.usr_activity aua
         where aua.usr = au.id
         and (now() - event_time) < '4 years'::interval
    )
-- no accounts created within the last 4 years
and date(au.create_date) < date(now()) - '4 years'::interval
) OR (
-- PLAY Cards are deleted one year after expire date\
-- They can't owe any money.
(
grp.name = 'PLAY Card'
and not exists (
    select 1
         from money.materialized_billable_xact_summary mmbxs
         where mmbxs.usr = au.id
         and balance_owed <> '0.00'
    )
and date(au.expire_date) < date(now()) - '1 year'::interval
)
) -- end OR
and au.deleted = false
-- consider open circs
and not exists (
        select 1
        from action.circulation circ
        where circ.usr = au.id
        and xact_finish is null
)
-- not in collections - PATRON_IN_COLLECTIONS standing penalty
and not exists (
        select 1
        from actor.usr_standing_penalty usp
        where usp.usr = au.id
        and usp.standing_penalty = 30
)
;