#!/bin/sh 
#
# $Id$
#
# sc: ser control; tool for maintaining ser's databases
#

# ser's FIFO server
SER_FIFO=/tmp/ser_fifo
# period in which stats are reprinted
WATCH_PERIOD=2

# SQL config
SQL_DB=ser
SQL_HOST=dbhost
SQL_USER=ser

# the read-only user for whom password may be stored here
RO_USER=serro
RO_PW=47serro11

# binaries
GENHA1='gen_ha1'
MYSQL='mysql'
SER='sr'

# ACL name verification
VERIFY_ACL=1
ACL_GROUPS="local ld int voicemail free-pstn"

# expiration time for alias table
if [ -z "$FOREVER" ]; then 
	FOREVER='2020-05-28 21:32:15'
fi

#### SQL names

# Usr Loc Table
USRLOC=location
USER_COLUMN=user
CALLID_COLUMN=callid

# subscriber table
TABLE=subscriber
REALM_COLUMN=realm
HA1_COLUMN=HA1
HA1B_COLUMN=HA1B
PASSWORD_COLUMN=password
SUBSCRIBER_COLUMN='user_id'
EMAIL_COLUMN=email_address
SUB_CREATED_COLUMN=datetime_created
SUB_MODIFIED_COLUMN=datetime_modified

# acl table
ACL_TABLE=grp
ACL_USER_COLUMN=user
ACL_GROUP_COLUMN=grp
ACL_MODIFIED_COLUMN=last_modified

# aliases table
A_TABLE=aliases
A_USER_COLUMN=user
A_CONTACT_COLUMN=contact
A_EXPIRES_COLUMN=expires
A_Q_COLUMN=q
A_CALLID_COLUMN=callid
A_CSEQ_COLUMN=cseq
A_LAST_MODIFIED_COLUMN=last_modified

#===================================================================


usage() {
CMD=`basename $0`
if [ "0$VERIFY_ACL" -eq 1 ] ; then
	EXTRA_TEXT="ACL privileges are: $ACL_GROUPS"
fi
cat <<EOF
usage: 
           < subscribers >
 $CMD add <name> <password> <email> ... add a new subscriber (*)
 $CMD mail <name> ..................... send an email to a user
 $CMD rm <name> ....................... delete a user (*)
 $CMD alias show [<alias>] ............ show aliases
 $CMD alias rm <alias> ................ remove an alias
 $CMD alias add <alias> <uri> ......... show aliases
           < access control lists >
 $CMD acl show [<user>] ............... show user membership
 $CMD acl grant <user> <group> ........ grant user membership (*)
 $CMD acl revoke <user> [<group>] ..... grant user membership(s) (*)
           < usrloc >
 $CMD dul <table> <name> .............. delete user's UsrLoc entries
 $CMD show ............................ show online users
 $CMD showdb [<name>] ................. show online users flushed in DB
 $CMD passwd <user> <passwd> .......... change user's password (*)
 $CMD perm <user> <uri> ............... introduce a permanent UrLoc entry
           < server health >
 $CMD monitor ......................... show internal status
 $CMD stat ............................ show runnig processes 
                                        + $CMD show

   commands labeled with (*) will prompt for a MySQL password
   if the variable PW is set, the password will not be prompted"

     $EXTRA_TEXT

EOF
}

#params: none
# output: PW
prompt_pw() {
	if [ -z "$PW" ] ; then
    	read -s -p "MySql Password: " PW
    	echo
	fi
}

ul_dump()
{
	name=ser_receiver_$$
	path=/tmp/$name
	if [ ! -w $SER_FIFO ]; then
		echo "Error opening ser's FIFO $SER_FIFO"
		echo "Make sure you have line fifo=$SER_FIFO in your config"
		exit 1
	fi
	mkfifo $path
	if [ $? -ne 0 ] ; then
		echo "error opening read fifo $path"
		exit 1
	fi
	cat > $SER_FIFO <<EOF
:ul_dump:$name

EOF
	cat < $path
	rm $path
}

ul_show_contact() # params: <table> <username>
{
	name=ser_receiver_$$
	path=/tmp/$name
	if [ ! -w $SER_FIFO ]; then
		echo "Error opening ser's FIFO $SER_FIFO"
		exit 1
	fi
	mkfifo $path
	if [ $? -ne 0 ] ; then
		echo "error opening read fifo $path"
		exit 1
	fi
	cat > $SER_FIFO <<EOF
:ul_show_contact:$name
$1
$2

EOF
	cat < $path
	rm $path
}

ul_rm() # params: <table> <username>
{
	name=ser_receiver_$$
	path=/tmp/$name
	if [ ! -w $SER_FIFO ]; then
		echo "Error opening ser's FIFO $SER_FIFO"
		echo "Make sure you have line fifo=$SER_FIFO in your config"
		exit 1
	fi
	mkfifo $path
	if [ $? -ne 0 ] ; then
		echo "error opening read fifo $path"
		exit 1
	fi
	cat > $SER_FIFO <<EOF
:ul_rm:$name
$1
$2

EOF
	cat < $path
	rm $path
}


# $1 = name $2=path $3=attempt
print_stats() {

echo "[cycle #: $3; if constant make sure server lives and fifo is on]"

cat > $SER_FIFO <<EOF
:version:$1

:uptime:$1

EOF
cat < $2
echo

echo Transaction Statistics
cat > $SER_FIFO <<EOF
:t_stats:$1

EOF
cat < $2
echo

echo Stateless Server Statistics
cat > $SER_FIFO <<EOF
:sl_stats:$1

EOF
cat < $2
echo

echo UsrLoc Stats
cat > $SER_FIFO <<EOF
:ul_stats:$1

EOF
cat < $2
}


# input: sql query, optional mysql command-line params
sql_query() {
	# if password not yet queried, query it now
	if [ -z "$PW" ] ; then
		read -s -p "MySql Password: " PW >&2
		echo >&2
	fi
	$MYSQL $2 -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$1 ;" $SQL_DB
}

# input: sql query, optional mysql command-line params
sql_ro_query() {
	$MYSQL $2 -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
		-e "$1 ;" $SQL_DB
}


ser_alias() {
	case $1 in 
		show)
			if [ $# -eq 2 ] ; then
				CLAUSE=" WHERE $A_USER_COLUMN='$2' "
			elif [ $# -ne 1 ] ; then
				usage
				exit 1
			fi
			QUERY="select * FROM $A_TABLE $CLAUSE ; "
			sql_ro_query "$QUERY"
			;;
		add)
			if [ $# -ne 3 ] ; then
				usage
				exit 1
			fi
			QUERY="insert into $A_TABLE \
				($A_USER_COLUMN, $A_CONTACT_COLUMN, $A_EXPIRES_COLUMN, \
				$A_Q_COLUMN, $A_CALLID_COLUMN, $A_CSEQ_COLUMN, \
				$A_LAST_MODIFIED_COLUMN) \
				values ( '$2', '$3', '$FOREVER', \
				'1.00', 'call-id-for-ever', '1',
				now() );"
			sql_query "$QUERY"
			;;
		rm)
            if [ $# -ne 2 ] ; then
                usage
                exit 1
            fi

            QUERY="delete from $A_TABLE where \
                $A_TABLE.$A_USER_COLUMN='$2' "
			sql_query "$QUERY"

            ;;

		*)
			usage
			exit 1
			;;
	esac
}

acl() {
	case $1 in
		show)
			if [ $# -eq 2 ] ; then
				is_user $2
				if [ $? -ne 0 ] ; then
					echo non-existent user
					exit 1;
				fi
				CLAUSE=" WHERE $ACL_USER_COLUMN='$2' "
			elif [ $# -ne 1 ] ; then
				usage
				exit 1
			fi
			QUERY="select * FROM $ACL_TABLE $CLAUSE ; "
			sql_ro_query "$QUERY"

			;;

		grant)
			if [ $# -lt 3 ] ; then
				usage
				exit 1
			fi
			prompt_pw
			is_user $2
			if [ $? -ne 0 ] ; then
				echo non-existent user
				exit 1
			fi
			SIP_USER="$2"
			shift 2
			while [ $# -gt 0 ] ; do

				if [ $VERIFY_ACL -eq 1 ] ; then
					found=0
					for i in $ACL_GROUPS ; do
						if [ "$1" = "$i" ] ; then
							found=1
							break
						fi
					done	
					if [ $found -eq 0 ] ; then
						echo "Invalid privilege: $1 (ignored)"
						shift
						continue
					fi
				fi

        		QUERY="insert into $ACL_TABLE \
                	($ACL_USER_COLUMN,$ACL_GROUP_COLUMN,$ACL_MODIFIED_COLUMN) \
                	values ('$SIP_USER','$1', now());"
				sql_query "$QUERY"
				if [ $? -ne 0 ] ; then
					echo "SQL Error"
					exit 1
				fi
				shift
			done

			$0 acl show $SIP_USER

			;;

		revoke)
			if [ $# -eq 3 ] ; then
				CLAUSE=" and $ACL_GROUP_COLUMN='$3' "
			elif [ $# -ne 2 ] ; then
				usage
				exit 1
			fi	

			QUERY="delete from $ACL_TABLE where \
				$ACL_TABLE.$ACL_USER_COLUMN='$2' $CLAUSE"
			sql_query "$QUERY"

			$0 acl show $2

			;;

		*)
			usage
			exit 1
			;;
	esac
}

# params: user
# output: false if exists, true otherwise
is_user() {
	QUERY="select count(*) from $TABLE \
		where $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$SIP_DOMAIN';"
	CNT=`sql_ro_query "$QUERY" | grep -v ERROR | tail -n1`
	if [ "0$CNT" -eq 0 ] ; then
		false
	else
		true
	fi

}


# params: user, password
# output: HA1, HA1B
credentials()
{
	HA1=`$GENHA1 $1 $SIP_DOMAIN $2`
	if [ $? -ne 0 ] ; then
		echo "HA1 calculation failed"
		exit 1
	fi
	HA1B=`$GENHA1 "$1@$SIP_DOMAIN" $SIP_DOMAIN $2`
	if [ $? -ne 0 ] ; then
		echo "HA1B calculation failed"
		exit 1
	fi
}

#================================================================

if [ -z "$SIP_DOMAIN" ] ; then
	echo "You need to set environment variable SIP_DOMAIN (e.g. to 'foobar.com') first"
	echo
	usage
	exit 1
fi

# if the script calls itself ...
export PW

case $1 in

	passwd)
		if [ $# -ne 3 ] ; then
			usage
			exit 1
		fi
		shift
		credentials $1 $2
		prompt_pw

		is_user $1
		if [ $? -ne 0 ] ; then
			echo non-existent user
			exit 1
		fi
		QUERY="update $TABLE \
			set $HA1_COLUMN='$HA1', $HA1B_COLUMN='$HA1B', $PASSWORD_COLUMN='$2' \
			, $SUB_MODIFIED_COLUMN=now() \
			WHERE $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$SIP_DOMAIN';"
		sql_query "$QUERY"
		if [ $? -ne 0 ] ; then
			echo "password change failed"
		else
			echo "password change succeeded"
		fi

		;;		
			
		

	add)
		if [ $# -ne 4 ] ; then
			usage
			exit 1
		fi
		shift
		credentials $1 $2
		prompt_pw
        is_user $1
        if [ $? -eq 0 ] ; then
            echo user already exists
            exit 1
        fi

		QUERY="insert into $TABLE \
				($SUBSCRIBER_COLUMN,$REALM_COLUMN,$HA1_COLUMN,\
				$HA1B_COLUMN,$PASSWORD_COLUMN,$EMAIL_COLUMN, $SUB_CREATED_COLUMN) \
				values ('$1','$SIP_DOMAIN','$HA1','$HA1B','$2', '$3', now() );";
		sql_query "$QUERY"
		if [ $? -ne 0 ] ; then
			echo "introducing a new user to the database failed"
		else
			echo "new user added"
		fi

		;;

	# add a permanent UsrLoc entry
	perm)
		if [ $# -ne 3 ] ; then
			usage
			exit 1
		fi
		prompt_pw
		is_user $2
		if [ $? -ne 0 ] ; then
			echo non-existent user
			exit 1;
		fi
		QUERY="insert into $USRLOC \
			($A_USER_COLUMN, $A_CONTACT_COLUMN, $A_EXPIRES_COLUMN, \
			$A_Q_COLUMN, $A_CALLID_COLUMN, $A_CSEQ_COLUMN, \
			$A_LAST_MODIFIED_COLUMN) \
			values ( '$2', '$3', '$FOREVER', \
			'1.00', 'call-id-for-ever', '1',
			now() );"
			sql_query "$QUERY"
		echo "Changes do not take effect until server restarted"
		;;


	dul)
		shift
		if [ $# -ne 2 ] ; then
			usage
			exit 1
		fi
		ul_rm $1 $2
		exit $?
		;;

	monitor|console|moni|con)
		name=ser_receiver_$$
		path=/tmp/$name
		if [ ! -w $SER_FIFO ]; then
			echo "Error opening ser's FIFO $SER_FIFO"
			echo "Make sure you have line fifo=$SER_FIFO in your config"
			exit 1
		fi
		mkfifo $path
		if [ $? -ne 0 ] ; then
			echo "error opening read fifo $path"
			exit 1
		fi
		trap "rm $path; clear; echo sc monitor ^C-ed; exit 1" 2
		attempt=0
		clear
		while [ 1 -eq 1 ]; do
			attempt=`expr $attempt + 1`
			#clear
			tput cup 0 0
			print_stats $name $path $attempt
			sleep $WATCH_PERIOD
		done
		rm $path
		exit 0
		;;

	mail)
		if [ $# -ne 2 ] ; then
			usage
			exit 1
		fi
		shift
		QUERY="select $TABLE.$EMAIL_COLUMN from $TABLE where  \
			$TABLE.$SUBSCRIBER_COLUMN='$1'"
		EA=`sql_ro_query "$QUERY" "-B" | grep -v ERROR | tail -n1`
		if [ $? -ne 0 ] ; then
			echo "MySql query failed"
			exit 1
		fi
		echo "Write email to $1: $EA now ..."
		mail -s "Message from $SIP_DOMAIN SIP admin" $EA
		if [ $? -eq 0 ] ; then
			echo message sent
		else
			echo sending message failed
		fi

		;;

	show)
		shift
	
		if [ $# -eq 1 ] ; then
			ul_show_contact $USRLOC $1
		else
			ul_dump
		fi
		exit $?
		;;

	online)
		ul_dump grep aor| awk '{print $3}' | sort | sort -mu
		exit $?
		;;


	showdb|userdb)
		if [ $# -eq 2 ] ; then
			is_user $2
			if [ $? -ne 0 ] ; then
				echo non-existent user
				exit 1;
			fi
		elif [ $# -ne 1  ] ; then
			usage
			exit 1
		fi

		shift

		QUERY1="select $TABLE.$EMAIL_COLUMN from $TABLE where  \
			$TABLE.$SUBSCRIBER_COLUMN='$1'"
		QUERY2="select $USRLOC.* from $USRLOC where \
			$USRLOC.$USER_COLUMN='$1' order by expires desc"
		QUERY3="select $USRLOC.$USER_COLUMN, $TABLE.$EMAIL_COLUMN, $USRLOC.$CALLID_COLUMN \
			from $TABLE, $USRLOC where  \
			$TABLE.$SUBSCRIBER_COLUMN=$USRLOC.$USER_COLUMN  order by $USRLOC.$USER_COLUMN" 

		if [ $# -eq 1 ] ; then
			sql_ro_query "$QUERY1"
			sql_ro_query "$QUERY2"
		else
			sql_ro_query "$QUERY3"
		fi
		echo -n "Note: Due to usage of cache, server's list "
		echo "may differ from DB list."

		;;

	rm)
        if [ $# -ne 2 ] ; then
            usage
            exit 1
        fi
		shift 
		prompt_pw 

        is_user $1
        if [ $? -ne 0 ] ; then
            echo non-existent user
            exit 1
        fi

        QUERY="delete from $TABLE where $TABLE.$SUBSCRIBER_COLUMN='$1'"
		sql_query "$QUERY"

		$0 acl revoke $1 > /dev/null 2>&1
		$0 dul $1   > /dev/null 2>&1
        ;;
			
	stat)
        if [ $# -ne 1 ] ; then
            usage
            exit 1
        fi
		ps -o "pid,user,args" $i -C $SER
		$0 show

		;;

	acl)
		shift
		acl "$@"
		;;

	alias)
		shift
		ser_alias "$@"
		;;
		
	*)
		usage
		exit 1
		;;

esac