WITH x AS ( -- begin WITH
    SELECT au.*
    --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

        ( -- begin regular patrons
        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
        ) -- end regular patrons

    OR

        ( -- begin PLAY cards
        -- 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 PLAY cards

    ) -- 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
    )
) -- end WITH
SELECT actor.usr_delete(u.id, NULL) FROM actor.usr u WHERE u.id in (SELECT id FROM x)
;