#!/bin/sh

# This is an example script that logs all events into an SQL database
# You need a MYSQL database as described in the documentation.

SQL_HOST=localhost
SQL_USER=root
SQL_PASSWORD=
SQL_DATABASE=smsd
SQL_TABLE=sms_log

DATE=`date +"%Y-%m-%d %H:%M:%S"`
#Extract data from the SMS file
FROM=`formail -zx From: < $2`
TO=`formail -zx To: < $2`
SUBJECT=`formail -zx Subject: < $2`
SENT=`formail -zx Sent: < $2`
DISCHARGE=`sed -e '1,/^$/d' < $2 | formail -zx Discharge_timestamp:`
STATUS=`sed -e '1,/^$/d' < $2 | formail -zx Status:`

#Set some SQL parameters
if [ "$SQL_PASSWORD" != "" ]; then 
  SQL_ARGS="-p$SQL_PASSWORD"; 
else 
  SQL_ARGS=""; 
fi
SQL_ARGS="-h $SQL_HOST -u $SQL_USER $SQL_ARGS -D $SQL_DATABASE -s -e"

#Insert a new entry into the SQL table

if [ "$1" = "FAILED" ] || [ "$1" == "SENT" ]; then
  mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,sender,destination) values (\"$1\",\"$DATE\",\"$FROM\",\"$TO\");";
elif [ "$1" = "RECEIVED" ]; then
  mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,received,sender,destination) values (\"$1\",\"$SENT\",\"$DATE\",\"$FROM\",\"$SUBJECT\");";
elif [ "$1" = "REPORT" ]; then
  mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,received,sender,destination,discharge,status) values (\"$1\",\"$SENT\",\"$DATE\",\"$FROM\",\"$SUBJECT\",\"$DISCHARGE\",\"$STATUS\");";  
fi
