#!/bin/bash

# create the database file and table if it doesn't exist
if [ ! -f mydata.db ]; then
  echo "Creating database file..."
  sqlite3 mydata.db "CREATE TABLE mytable(timestamp TEXT, WAN_Connection TEXT, miles_from_server TEXT, download_speed REAL, upload_speed REAL, UNIQUE(timestamp, WAN_Connection));"
fi

# define an array of URLs that point to the csv file. One URL per line and within " "
# e.g. "http://192.168.69.101/speedtest-wan1.csv"
urls=(
  ""
)

# loop through each URL
for url in "${urls[@]}"
do
  # download the file
  filename=$(basename $url)
  curl -s $url -o $filename

  # loop through each line in the input file, starting from the second line
  tail -n +2 $filename | while read line
  do
    # split the CSV data into columns
    WAN_Connection=$(echo $filename | grep -oP '(?<=wan).*' | sed 's/\.csv//')
    timestamp=$(echo $line | cut -d, -f1)
    miles_from_server=$(echo $line | cut -d, -f2 | cut -d' ' -f1)
    download_speed=$(echo $line | cut -d, -f3 | cut -d' ' -f1)
    upload_speed=$(echo $line | cut -d, -f4 | cut -d' ' -f1)

    # check if the data is already in the database
    if ! sqlite3 mydata.db "SELECT * FROM mytable WHERE timestamp='$timestamp' AND WAN_Connection='$WAN_Connection';" | grep -q "$timestamp"; then
      # add the data to the database
      sqlite3 mydata.db "INSERT INTO mytable(timestamp, WAN_Connection, miles_from_server, download_speed, upload_speed) VALUES ('$timestamp', '$WAN_Connection',  $miles_from_server, $download_speed, $upload_speed);"
    fi
  done

  # remove the file
  rm $filename
done