#!/bin/bash

help()
{
    echo "Usage:     statsip [ result_line_number | -h ]"
}


if [ $# == 1 ]; then
    if [ "$1" == "-h" ]; then
	help
	exit 0
    else
	limit="limit $1"
    fi
else
    limit=""
fi


sql="
select 
    count(*) AS ACCOUNTS,
    sum(case when a.suspended_at is null then 1 else 0 end) as NOT_SUSPENDED,
    sum(case when u.approved=false then 1 else 0 end) as WAITING_MODERATION,
    case when family(u.last_sign_in_ip)=4 then host(u.last_sign_in_ip) else substring(host(u.last_sign_in_ip), '(([0-9a-f]{0,4}:){1,4})') end as IP
from users u 
inner join accounts a on 
    a.id=u.account_id
where
    u.last_sign_in_ip is not null
group by IP
order by ACCOUNTS desc
$limit
"


su - mastodon -c "psql -P pager=off mastodon-production -c \"$sql\" " | grep -v '[[:space:]]\+0[[:space:]]*|[[:space:]]*0'