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