-- Get last successful backup for each stanza
--
-- Requires the monitor.pgbackrest_info function.
with stanza as (
    select
        data -> 'name' as name,
        data -> 'backup' -> (
            jsonb_array_length(data -> 'backup') - 1
        ) as last_backup,
        data -> 'archive' -> (
            jsonb_array_length(data -> 'archive') - 1
        ) as current_archive
    from jsonb_array_elements(monitor.pgbackrest_info()) as data  -- noqa: AL05
)

select
    name,
    to_timestamp(
        (last_backup -> 'timestamp' ->> 'stop')::numeric
    ) as last_successful_backup,
    current_archive ->> 'max' as last_archived_wal
from stanza;
