Juvenile Account Cleanup

-- juvenile -> adult
UPDATE actor.usr SET profile = 379, juvenile = FALSE WHERE id IN
    ( SELECT au.id FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    JOIN permission.grp_tree pgt ON pgt.id = au.profile
    WHERE aou.shortname ILIKE 'bcl%' AND pgt.NAME = 'Juvenile'
    AND au.dob < NOW() - INTERVAL '18 years' )
;

-- juvenile non-residen -> adult non-resident
UPDATE actor.usr SET profile = 380, juvenile = FALSE WHERE id IN
    ( SELECT au.id FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    JOIN permission.grp_tree pgt ON pgt.id = au.profile
    WHERE aou.shortname ILIKE 'bcl%' AND pgt.NAME = 'Juvenile Non-Resident'
    AND au.dob < NOW() - INTERVAL '18 years' )
;

-- teen -> adult
UPDATE actor.usr SET profile = 379, juvenile = FALSE WHERE id IN
    ( SELECT au.id FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    JOIN permission.grp_tree pgt ON pgt.id = au.profile
    WHERE aou.shortname ILIKE 'bcl%' AND pgt.NAME = 'Teen'
    AND au.dob < NOW() - INTERVAL '18 years' )
;

-- teen non-residen -> adult non-resident
UPDATE actor.usr SET profile = 380, juvenile = FALSE WHERE id IN
    ( SELECT au.id FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    JOIN permission.grp_tree pgt ON pgt.id = au.profile
    WHERE aou.shortname ILIKE 'bcl%' AND pgt.NAME = 'Teen Non-Resident'
    AND au.dob < NOW() - INTERVAL '18 years' )
;

-- juv to teen by bcl rules
UPDATE actor.usr SET profile = 389, juvenile = TRUE WHERE id IN
    ( SELECT au.id FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    JOIN permission.grp_tree pgt ON pgt.id = au.profile
    WHERE aou.shortname ILIKE 'bcl%' AND pgt.NAME = 'Juvenile'
    AND au.dob < NOW() - INTERVAL '12 years' )
;

-- juv non-residen -> teen non-resident
UPDATE actor.usr SET profile = 390, juvenile = TRUE WHERE id IN
    ( SELECT au.id FROM actor.usr au
    JOIN actor.org_unit aou ON aou.id = au.home_ou
    JOIN permission.grp_tree pgt ON pgt.id = au.profile
    WHERE aou.shortname ILIKE 'bcl%' AND pgt.NAME = 'Juvenile Non-Resident'
    AND au.dob < NOW() - INTERVAL '12 years' )
;