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

# realm
DOMAIN=iptel.org

# SQL config
SQL_DB=csps107
SQL_HOST=dbhost
SQL_USER=csps

# 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'

# SQL names
USER_COLUMN=user
REALM_COLUMN=realm
HA1_COLUMN=HA1
HA1B_COLUMN=HA1B
# subscriber table
TABLE=subscriber
# Usr Loc Table
USRLOC=location
PASSWORD_COLUMN=password
SUBSCRIBER_COLUMN='user_id'
EMAIL_COLUMN=email_address
# acl table
ACL_TABLE=grp
ACL_USER_COLUMN=user
ACL_GROUP_COLUMN=grp



usage() {
	echo "usage: "
	echo -n " `basename $0` add <name> <password> <email> "
	echo "... add a new subscriber (*)"
	echo " `basename $0` dul <name> ... delete user's UsrLoc entries (*)"
	echo " `basename $0` show [<name>] ... show online users"
	echo " `basename $0` mail <name> ... send an email to a user"
	echo " `basename $0` rm <name> ... delete a user (*)"
	echo " `basename $0` passwd <user> <passwd> ... change user's password (*)"
	echo " `basename $0` stat ... show runnig processes + $0 show"
	echo " `basename $0` acl show [<user>] ... show user membership"
	echo " `basename $0` acl grant <user> <group> ... grant user memberhsip (*)"
	echo " `basename $0` acl revoke <user> [<group>] ... grant user memberhsip(s) (*)"
	echo 
	echo "   commands labeled with (*) will prompt for a MySQL password"
	echo
}

acl() {
	case $1 in
		show)
			if [ $# -eq 2 ] ; then
				CLAUSE=" WHERE $ACL_USER_COLUMN='$2' "
			elif [ $# -ne 1 ] ; then
				usage
				exit 1
			fi
			QUERY="select * FROM $ACL_TABLE $CLAUSE ; "
			$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
				-e "$QUERY ;" $SQL_DB

			;;

		grant)
			if [ $# -ne 3 ] ; then
				usage
				exit 1
			fi

        	$MYSQL -h $SQL_HOST -u $SQL_USER -p$PW \
            	-e "use $SQL_DB; insert into $ACL_TABLE \
                ($ACL_USER_COLUMN,$ACL_GROUP_COLUMN) \
                values ('$2','$3');";

			;;

		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"
			$MYSQL -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$QUERY ;" $SQL_DB

			;;

		*)
			usage
			exit 1
			;;
	esac
}

# params: user
# output: false if exists, true otherwise
is_user() {
	CNT=`$MYSQL -B -h $SQL_HOST -u $SQL_USER -p$PW \
		-e "use $SQL_DB; select count(*) from $TABLE \
		where $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$DOMAIN';" \
		| grep -v ERROR | tail -n1`
	if [ "0$CNT" -eq 0 ] ; then
		false
	else
		true
	fi

}

#params: none
# output: PW
prompt_pw() {
	read -s -p "MySql Password: " PW
	echo 
}

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



case $1 in

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

		prompt_pw
		is_user $1
		if [ $? -ne 0 ] ; then
			echo non-existent user
			exit 1
		fi
		$MYSQL -h $SQL_HOST -u $SQL_USER -p$PW \
			-e "use $SQL_DB; update $TABLE \
			set $HA1_COLUMN='$HA1', $HA1B_COLUMN='$HA1B', $PASSWORD_COLUMN='$2' \
			WHERE $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$DOMAIN';"
		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
		prompt_pw
        is_user $1
        if [ $? -eq 0 ] ; then
            echo user already exists
            exit 1
        fi

		$MYSQL -h $SQL_HOST -u $SQL_USER -p$PW \
			-e "use $SQL_DB; insert into $TABLE \
				($SUBSCRIBER_COLUMN,$REALM_COLUMN,$HA1_COLUMN,\
				$HA1B_COLUMN,$PASSWORD_COLUMN,$EMAIL_COLUMN) \
				values ('$1','$DOMAIN','$HA1','$HA1B','$2', '$3');";
		if [ $? -ne 0 ] ; then
			echo "introducing a new user to the database failed"
		else
			echo "new user added"
		fi

		;;

	dul)
		if [ $# -ne 2 ] ; then
			usage
			exit 1
		fi
		prompt_pw
		CNT=`$MYSQL -B -h $SQL_HOST -u $SQL_USER -p$PW \
			 -e "use $SQL_DB; select count(*) from $USRLOC \
			where $USRLOC.$USER_COLUMN='$2';" | grep -v ERROR | tail -n1`
		if [ "0$CNT" -eq 0 ] ; then
			echo Nothing to delete
			exit 1
		fi
		QUERY="delete from $USRLOC where $USRLOC.$USER_COLUMN='$2'"
		$MYSQL -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$QUERY ;" $SQL_DB
		if [ $? -ne 0 ] ; then
			echo "database operation failed"
		else
			echo "ok -- but beware, the server caches the usrloc entries"
			echo "and the changes don't take effect until ser reboot"
		fi
		;;

	mail)
		if [ $# -ne 2 ] ; then
			usage
			exit 1
		fi
		shift
		QUERY="select $TABLE.$EMAIL_COLUMN from $TABLE where  \
			$TABLE.$SUBSCRIBER_COLUMN='$1'"
		EA=`$MYSQL -B -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
			-e "$QUERY ;" $SQL_DB| 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 $DOMAIN SIP admin" $EA
		if [ $? -eq 0 ] ; then
			echo message sent
		else
			echo sending message failed
		fi

		;;


	show)
		if [ $# -ne 1 -a $# -ne 2 ] ; 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 \
			from $TABLE, $USRLOC where  \
			$TABLE.$SUBSCRIBER_COLUMN=$USRLOC.$USER_COLUMN" 

		if [ $# -eq 1 ] ; then
			$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" -e "$QUERY1 ;" $SQL_DB
			$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" -e "$QUERY2 ;" $SQL_DB
		else
			$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" -e "$QUERY3 ;" $SQL_DB
		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'"
        $MYSQL -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$QUERY ;" $SQL_DB
        ;;
			
	stat)
        if [ $# -ne 1 ] ; then
            usage
            exit 1
        fi
		ps -o "pid,user,args" $i -C $SER
		$0 show

		;;

	acl)
		shift
		acl "$@"
		;;
		
	*)
		usage
		exit 1
		;;

esac