#!/usr/bin/env bash

# Character for separating values
# (commas are not safe, because some servers return speeds with commas)
speedtestserver=3271
mysqllogin=#Put your credential here
mysqlpassword=#Put your credential here
mysqldb=#Put your credential here
ip="192.168.1.2"

# Temporary file holding speedtest-cli output
user=$USER
if test -z $user; then
  user=$USERNAME
fi
log=/tmp/$user/speedtest-mysql.log

# Local functions
str_extract() {
 pattern=$1
 # Extract
 res=`grep "$pattern" $log | sed "s/$pattern//g"`
 # Drop trailing ...
 res=`echo $res | sed 's/[.][.][.]//g'`
 # Trim
 res=`echo $res | sed 's/^ *//g' | sed 's/ *$//g'`
 echo $res
}

mkdir -p `dirname $log`

start=`date +"%Y-%m-%d %H:%M:%S"`

echo "Speedtest started at : $start"

# Query Speedtest
/usr/local/bin/speedtest-cli --server $speedtestserver --share > $log

stop=`date +"%Y-%m-%d %H:%M:%S"`

echo "Speedtest ended at : $stop"

# Parse
from=`str_extract "Testing from "`
from_ip=`echo $from | sed 's/.*(//g' | sed 's/).*//g'`
from=`echo $from | sed 's/ (.*//g'`

server=`str_extract "Hosted by "`
server_ping=`echo $server | sed 's/.*: //g'`
server=`echo $server | sed 's/: .*//g'`
server_dist=`echo $server | sed 's/.*\\[//g' | sed 's/\\].*//g'`
server=`echo $server | sed 's/ \\[.*//g'`

download=`str_extract "Download: "`
upload=`str_extract "Upload: "`
share_url=`str_extract "Share results: "`

# Standardize units?
if test "$1" = "--standardize"; then
  download=`echo $download | sed 's/Mbits/Mbit/'`
  upload=`echo $upload | sed 's/Mbits/Mbit/'`
fi

# Test if my Main computer is on
if ping -c 1 $ip >/dev/null
then
  ison=true
else
  ison=false
fi

# Send to Local MySQL

value1=`echo $server_ping | cut -d" " -f1`
value2=`echo $download | cut -d" " -f1`
value3=`echo $upload | cut -d" " -f1`

sql="INSERT INTO tests (id,server,datestart,datestop,ping,dl,ul,ison) VALUES ('','$speedtestserver','$start','$stop','$server_ping','$download','$upload',$ison);"

echo "$sql" | mysql -u$mysqllogin -p$mysqlpassword $mysqldb