#!/bin/bash

#$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $

#

# This program queries PostgreSQL directly for various user statistics.

# Set psql access below (CMD=...), ensure that .pgpass lets user to login

#  without password entry.

# Start without arguments for help.

#

# (c)Peter Gervai, 2020-21

# @grin:grin.hu

# Licensed under GPLv3+ and CC_By-Sa-4.0-int

#

#CMD='sudo -u postgres psql synapse -tAc'

CMD='psql -h 10.1.1.257 -U synapse synapse -c'

MODE="$1"

ARG="$2"

if [ "x$MODE" = "x" ]; then

	echo "Usage: $0 <mode> <args> ...."

	echo " modes: user @someuser:grin.hu       - user stats"

	echo "        userevents @someuser:grin.hu - user events"

	echo "        active1                      - last 1 day active users timeline"

	echo "        active1u                     - last 1 day users (unique results)"

	echo "        lastreg [<n>]	           - last <n> (25) registrations"

	exit

fi

egy_arg() {

	local s=$1

	if [ "x$ARG" = "x" ]; then

		echo "Missing arg ${s}!"

		echo "$0 $MODE $s"

		exit

	fi

}

if [ "$MODE" = "user" ]; then

	egy_arg 'user'

	echo "=== User data ==="

	$CMD "SELECT display_name FROM user_directory WHERE user_id='$ARG'"

	$CMD "SELECT medium, address, unts(validated_at) AS validated_at, unts(added_at) AS added_at FROM user_threepids WHERE user_id='$ARG'"

	$CMD "SELECT joined_rooms FROM user_stats_current WHERE user_id='$ARG'"

 	echo "=== Logins ==="

 	$CMD "SELECT user_id,device_id,ip,unts(last_seen) AS last_seen,user_agent FROM user_ips WHERE user_id='$ARG'"

 	$CMD "SELECT device_id, user_agent, unts(timestamp) FROM user_daily_visits WHERE user_id='$ARG' ORDER BY 1 ASC, 3 DESC LIMIT 10"

 	echo "=== Shared private rooms ==="

 	$CMD "select * from users_who_share_private_rooms where user_id='$ARG'"

 	echo "=== Rooms the user has been ==="

 	$CMD "SELECT rm.room_id, rm.event_id, rm.sender, rm.membership, 

       rss.canonical_alias, rss.join_rules, rss.encryption, 

       unts(e.origin_server_ts), rss.name

 FROM room_memberships  rm

 JOIN room_stats_state rss USING(room_id) 

 JOIN events e USING(event_id)

 WHERE user_id='$ARG'

 ORDER BY e.origin_server_ts" | more

 	exit

fi

if [ "$MODE" = "userevents" ]; then

        egy_arg 'user'

        $CMD "SELECT * FROM events WHERE sender='@sztg:grin.hu';

SELECT stream_ordering,type,e.room_id,content,sender,unts(origin_server_ts) AS orig_s,json 

 FROM events e LEFT JOIN event_json j USING(event_id) 

 WHERE sender='$ARG'"

	exit

fi

if [ "$MODE" = "active1" ]; then

	$CMD "SELECT user_id,device_id,ip,unts(MAX(last_seen)) AS last_seen,user_agent FROM user_ips 

		 WHERE unts(last_seen)> NOW()-'1 day'::interval 

		 GROUP BY 1,2,3,5

		 ORDER BY last_seen DESC"

 	exit

fi

if [ "$MODE" = "lastreg" ]; then

	if [ "x$ARG" = "x" ]; then

		ARG=25

	fi

	$CMD "SELECT name,unts(creation_ts*1000) AS created,is_guest,deactivated,shadow_banned,

		device_id,ip,unts(last_seen) AS last_seen, user_agent

		FROM users LEFT JOIN user_ips ON(name=user_id)

		ORDER BY 2 DESC 

		LIMIT $ARG"

fi

if [ "$MODE" = "active1u" ]; then

	$CMD "SELECT DISTINCT user_id FROM user_ips 

		 WHERE unts(last_seen)> NOW()-'1 day'::interval 

		 ORDER BY 1" | cat

	$CMD "SELECT DISTINCT user_id,device_id,ip FROM user_ips 

		 WHERE unts(last_seen)> NOW()-'1 day'::interval 

		 ORDER BY 1" | cat

 	exit

fi