#!/bin/bash
#$Id: cmd.sh,v efd1721dba68 2022/03/04 09:54:35 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.
#
# You need to run 'unts' the first time with write access or copy the
# output into psql.
#
# (c)Peter Gervai, 2020-22
# @grin:grin.hu
# Licensed under GPLv3+ and CC_By-Sa-4.0-int
#

#CMD='sudo -u postgres psql synapse -tAc'
#CMD='psql -h 1.2.3.4 -U synapse synapse -c'

. ./_config.sh

CMD="${PSQL_CMD}"
CMDQ='-At'

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"
	echo "        active1u                     - last 1 day users (unique results)"
	echo "        lastreg [<n>]	           - last <n> (25) registrations"
	echo "        room <room_id without !>     - room info"
	echo "        evt <event w/o $             - event info"
	echo "        unts                         - create unts() function for human-readable"
	echo "                                       timestamps; requires Write access or copy displayed sql."
	exit
fi



egy_arg() {
	local s=$1
	if [ "x$ARG" = "x" ]; then
		echo "MIssing arg ${s}!"
		echo "$0 $MODE $s"
		exit
	fi
}


### create unts() func
if [ "$MODE" = "unts" ]; then
	echo "Creating unts() function; needs write access or you can simply copy the following code into psql."
	echo " "
	SQL="CREATE OR REPLACE FUNCTION public.unts(bigint)
 RETURNS timestamp with time zone
 LANGUAGE sql
 IMMUTABLE STRICT
AS \$function$
  SELECT TIMESTAMP WITH TIME ZONE 'epoch' + \$1/1000 * INTERVAL '1 second'
\$function$"
	echo "${SQL}"
	echo " "
	$CMD "${SQL}"
	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 name,password_hash,to_timestamp(creation_ts) AS creation,admin,is_guest,deactivated,shadow_banned FROM users WHERE name='$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' ORDER BY last_seen DESC"
 	$CMD "SELECT device_id, user_agent, unts(timestamp) AS stamp 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' ORDER BY room_id,other_user_id"
 	
 	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 DESC" | 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


if [ "$MODE" = "room" ]; then
	$CMD "SELECT rs.name, rs.canonical_alias,rs.join_rules,rs.topic FROM room_stats_state rs WHERE room_id='!${ARG}'" | cat
	$CMD "SELECT room_alias, creator FROM room_aliases WHERE room_id='!${ARG}'" | cat
	$CMD "SELECT * FROM rooms WHERE room_id='!${ARG}'" | cat
	$CMD "SELECT * FROM room_stats_current WHERE room_id='!${ARG}'" | cat
	# $CMD "SELECT COUNT(*), type FROM state_groups_state WHERE room_id='!${ARG}' GROUP BY 2 ORDER BY 1 DESC" | cat
fi


if [ "$MODE" = "evt" ]; then
	$CMD "SELECT event_id,type,room_id,content,processed,depth,sender,instance_name,state_key,unts(origin_server_ts) AS orig, unts(received_ts) AS rcv FROM events WHERE event_id='\$${ARG}'" | cat
	$CMD "SELECT * FROM event_json WHERE event_id='\$${ARG}'" | cat
	evt='$'"${ARG}"
	room=`$CMD "SELECT room_id FROM events WHERE event_id='${evt}'" $CMDQ`

	#echo "Room_id=$room!"
        $CMD "SELECT rs.name, rs.canonical_alias,rs.join_rules,rs.topic FROM room_stats_state rs WHERE room_id='$room'" | cat
        $CMD "SELECT room_alias, creator FROM room_aliases WHERE room_id='$room'" | cat

fi