Commcat Overdue/Lost Update

-- Commcat Overdue/Lost Alerts
-- I used ids 98/99 so that staff couldn't add the standing penalties manually.
-- 98 is the overdue alert
-- 99 is the lost block
WITH comcat_stats AS (
    SELECT
        au.id AS usr,
        count(DISTINCT CASE WHEN acp.status = 3 THEN acp.id ELSE NULL END) AS lost,
        count(DISTINCT CASE WHEN acp.status <> 3 THEN acp.id ELSE NULL END) AS overdue,
        string_agg(DISTINCT CASE WHEN acp.status = 3 THEN acp.barcode ELSE NULL END, ', ') AS lost_barcodes,
        string_agg(DISTINCT CASE WHEN acp.status <> 3 THEN acp.barcode ELSE NULL END, ', ') AS overdue_barcodes
    FROM action.circulation circ
    JOIN asset.copy acp ON circ.target_copy = acp.id
    JOIN actor.usr au ON circ.usr = au.id
    WHERE
        acp.circ_lib = 103
        AND NOT acp.deleted
        AND circ.xact_finish IS NULL
        AND circ.checkin_time IS NULL
        AND (acp.status = 3 OR circ.due_date < now() - '7 days'::INTERVAL)
    GROUP BY au.id
), eb98 AS (
    SELECT
        id,
        usr
    FROM actor.usr_standing_penalty
    WHERE standing_penalty = 98
), eb99 AS (
    SELECT
        id,
        usr
    FROM actor.usr_standing_penalty
    WHERE standing_penalty = 99
), full_stats AS (
    SELECT
        COALESCE(cs.usr, eb98.usr, eb99.usr) AS usr,
        cs.lost,
        cs.overdue,
        cs.lost_barcodes,
        cs.overdue_barcodes,
        eb98.id AS penalty_98,
        eb99.id AS penalty_99
    FROM comcat_stats cs
    FULL OUTER JOIN eb98 ON ( cs.usr = eb98.usr )
    FULL OUTER JOIN eb99 ON ( COALESCE(cs.usr, eb98.usr) = eb99.usr )
), update_98 AS (
    UPDATE actor.usr_standing_penalty ausp
    SET
        org_unit = 1,
        staff = NULL,
        stop_date = NULL,
        note = format(
            'Patron has %1$s overdue Commonwealth Catalog item%2$s as of %3$s. Item Barcode%2$s: %4$s. If the issue has been resolved, this message can be removed from the messages tab.',
            fs.overdue,
            CASE WHEN fs.overdue > 1 THEN 's' ELSE '' END,
            to_char(now(), 'DD Mon YYYY HH12:MI AM'),
            fs.overdue_barcodes
        )
    FROM full_stats fs
    WHERE fs.penalty_98 = ausp.id AND fs.overdue IS NOT NULL AND fs.overdue > 0
    RETURNING id
), update_99 AS (
    UPDATE actor.usr_standing_penalty ausp
    SET
        org_unit = 1,
        staff = NULL,
        stop_date = NULL,
        note = format(
            'Patron has %1$s LOST Commonwealth Catalog item%2$s as of %3$s. Item Barcode%2$s: %4$s. If the issue has been resolved, this message can be removed from the messages tab.',
            fs.lost,
            CASE WHEN fs.lost > 1 THEN 's' ELSE '' END,
            to_char(now(), 'DD Mon YYYY HH12:MI AM'),
            fs.lost_barcodes
        )
    FROM full_stats fs
    WHERE fs.penalty_99 = ausp.id AND fs.lost IS NOT NULL AND fs.lost > 0
    RETURNING id
), insert_98 AS (
    INSERT INTO actor.usr_standing_penalty (org_unit, usr, standing_penalty, note)
    SELECT
        1,
        usr,
        98,
        format(
            'Patron has %1$s overdue Commonwealth Catalog item%2$s as of %3$s. Item Barcode%2$s: %4$s. If the issue has been resolved, this message can be removed from the messages tab.',
            overdue,
            CASE WHEN overdue > 1 THEN 's' ELSE '' END,
            to_char(now(), 'DD Mon YYYY HH12:MI AM'),
            overdue_barcodes
        )
    FROM full_stats
    WHERE overdue > 0 AND penalty_98 IS NULL
    RETURNING id
), insert_99 AS (
    INSERT INTO actor.usr_standing_penalty (org_unit, usr, standing_penalty, note)
    SELECT
        1,
        usr,
        99,
        format(
            'Patron has %1$s LOST Commonwealth Catalog item%2$s as of %3$s. Item Barcode%2$s: %4$s. If the issue has been resolved, this message can be removed from the messages tab.',
            lost,
            CASE WHEN lost > 1 THEN 's' ELSE '' END,
            to_char(now(), 'DD Mon YYYY HH12:MI AM'),
            lost_barcodes
        )
    FROM full_stats
    WHERE lost > 0 AND penalty_99 IS NULL
    RETURNING id
), delete_old AS (
    DELETE FROM actor.usr_standing_penalty ausp
    USING full_stats fs
    WHERE
        (ausp.standing_penalty = 98 AND fs.penalty_98 = ausp.id AND (fs.overdue IS NULL OR fs.overdue = 0))
        OR
        (ausp.standing_penalty = 99 AND fs.penalty_99 = ausp.id AND (fs.lost IS NULL OR fs.lost = 0))
    RETURNING id
)
select
    count(DISTINCT u98) AS update_overdue,
    count(DISTINCT u99) AS update_lost,
    count(DISTINCT i98) AS insert_overdue,
    count(DISTINCT i99) AS insert_lost,
    count(DISTINCT del) AS deleted_old
FROM
    update_98 u98
    FULL OUTER JOIN update_99 u99 ON true
    FULL OUTER JOIN insert_98 i98 ON true
    FULL OUTER JOIN insert_99 i99 ON true
    FULL OUTER JOIN delete_old del ON true;