#!/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