#!/bin/bash

# Path to speedtest-cli (Change this to match the path to your speedtest-cli executable)
SPEEDTEST_CLI_PATH="/usr/local/bin/speedtest-cli"

# Path to the sqlite database (Change this to suit your needs)
#DB_PATH="/home/lino/speedlog.db"
DB_PATH="/var/www/speedtest-api/speedtest.db"

# Create test table SQL statement
SQL_CREATE="                        \
        create table tests (        \
                id integer PRIMARY KEY AUTOINCREMENT, \
                server_id integer,  \
                sponsor text,       \
                server_name text,   \
                timestamp text,     \
                distance real,      \
                ping real,          \
                download real,      \
                upload real,        \
                share text,         \
                ip_address text     \
        );"

# Input Field Seperator for splitting the CSV result into an array
IFS=","

# Create Sqlite database and table if it does not exist
if ! [ -f "$DB_PATH" ]
then
    sqlite3 $DB_PATH "$SQL_CREATE"
fi

# Do Speed Test
result=$($SPEEDTEST_CLI_PATH --csv)

# Result to Array
arr=($result)

# Transform values
arr[6]=$(awk "BEGIN {print ${arr[6]}/1000000}")
arr[7]=$(awk "BEGIN {print ${arr[7]}/1000000}")

# Array to Sqlite database
sqlite3 $DB_PATH "                  \
            insert into tests (     \
                server_id,          \
                sponsor,            \
                server_name,        \
                timestamp,          \
                distance,           \
                ping,               \
                download,           \
                upload,             \
                share,              \
                ip_address          \
            ) values (              \
                ${arr[0]},          \
                '${arr[1]}',        \
                '${arr[2]}',        \
                '${arr[3]}',        \
                ${arr[4]%.*},       \
                ${arr[5]%.*},       \
                ${arr[6]},          \
                ${arr[7]},          \
                '${arr[8]}',        \
                '${arr[9]}'         \
            );"