-- Run before switching an installation from site-agent to beto membership.
-- Every count must be zero for a cutover that preserves existing authorised keys.
WITH personal_keys AS (
    SELECT state->>'allocationId' AS allocation_id, state->>'memberId' AS member_id
    FROM pharos.aggregates
    WHERE kind = 'key' AND state->>'kind' = 'personal' AND state->>'state' = 'active'
), member_links AS (
    SELECT state->>'allocationId' AS allocation_id, state->>'memberId' AS member_id,
           state->>'waldurUser' AS waldur_user, state->>'wanted' = 'true' AS wanted
    FROM pharos.aggregates
    WHERE kind = 'chat_member'
)
SELECT
    (SELECT count(*) FROM personal_keys AS key
     WHERE NOT EXISTS (
         SELECT 1 FROM member_links AS member
         WHERE member.allocation_id = key.allocation_id AND member.member_id = key.member_id
     )) AS unlinked_personal_keys,
    (SELECT count(*) FROM personal_keys AS key
     JOIN member_links AS member
       ON member.allocation_id = key.allocation_id AND member.member_id = key.member_id
     WHERE COALESCE(member.waldur_user, '') !~ '^[0-9a-f]{32}$') AS unpaired_personal_keys,
    (SELECT count(*) FROM member_links
     WHERE wanted AND COALESCE(waldur_user, '') !~ '^[0-9a-f]{32}$') AS unpaired_wanted_members;