monitoring

#!/usr/bin/env bash

function grafana_sql() {
    mysql -h database -u "$GRAFANA_USER" -D "$GRAFANA_DATABASE" -N -B -e "$1"
}

function scanner_sql() {
    export MYSQL_PWD="${SCANNER_PASSWORD}"; mysql -h "${SCANNER_HOST}" -P "${SCANNER_PORT}" -u "${SCANNER_USER}" -D "${SCANNER_DATABASE}" -N -B -e "$1"
}

function scanner_sql2() {
    export MYSQL_PWD="${SCANNER_PASSWORD}"; mysql -h "${SCANNER_HOST}" -P "${SCANNER_PORT}" -u "${SCANNER_USER}" -D maddb -N -B -e "$1"
}

function cleanup() {
    echo "$(date +'%F %T') | Starting Cleanup... "
    while read -r table; do
	    grafana_sql 'delete from '"$table"' where timestamp < date_sub(UTC_TIMESTAMP, INTERVAL '"$CLEANUP_DAYS"' DAY)'
    done < <(grafana_sql 'SHOW TABLES LIKE "%_data"')
}

function check_pokemon () {
    pokemon=$(scanner_sql "SELECT COUNT(*) FROM pokemon WHERE expire_timestamp > UNIX_TIMESTAMP()")
    echo "$(date +'%F %T') | Current Pokémon: $pokemon"
    grafana_sql 'INSERT INTO pokemon_data(timestamp,value) VALUES(now(),"'"$pokemon"'")'
}

function check_iv () {
    iv=$(scanner_sql "SELECT COUNT(*) FROM pokemon WHERE expire_timestamp > UNIX_TIMESTAMP() AND iv IS NOT NULL")
    echo "$(date +'%F %T') | Current Pokémon with IV: $iv"
    grafana_sql 'INSERT INTO iv_data(timestamp,value) VALUES(now(),"'"$iv"'")'
}

function check_quests () {
    quests=$(scanner_sql "SELECT COUNT(*) FROM pokestop WHERE quest_type is not null")
    echo "$(date +'%F %T') | Current Quests: $quests"
    grafana_sql 'INSERT INTO quest_data(timestamp,value) VALUES(now(),"'"$quests"'")'
}

function check_tr () {
    tr=$(scanner_sql "SELECT COUNT(*) FROM pokestop WHERE grunt_type is not null")
    echo "$(date +'%F %T') | Current Team GO Rocket Stops: $tr"
    grafana_sql 'INSERT INTO tr_data(timestamp,value) VALUES(now(),"'"$tr"'")'
}

function check_devices () {
     echo "$(date +'%F %T') | Checking device status..."
     while read -r device; do
     	timestamps=$(scanner_sql2 'SELECT UNIX_TIMESTAMP(lastProtoDateTime), currentSleepTime FROM trs_status WHERE device_id="'"$device"'"')
        devicename=$(scanner_sql2 'SELECT name FROM settings_device WHERE device_id="'"$device"'"')
	now=$(date '+%s')
	array=($(for i in $timestamps; do echo $i; done))
	lastData=${array[0]}
	sleepTime=${array[1]}
        if [[ $lastData -lt $((now-${STATUS_DOWN}-$sleepTime)) ]]; then
            currentStatus="0"
	    echo "$(date +'%F %T') | Device $devicename offline"
        else
            currentStatus="1"
        fi
        grafana_sql 'INSERT INTO device_data(timestamp, device, value) VALUES(now(),"'$devicename'","'"$currentStatus"'")'
     done < <(scanner_sql2 "SELECT device_id FROM trs_status;")
}


function check_raids () {
    raids=$(scanner_sql "SELECT COUNT(*) total, SUM(CASE WHEN raid_level=1 THEN 1 ELSE 0 END) level1, SUM(CASE WHEN raid_level=2 THEN 1 ELSE 0 END) level2, SUM(CASE WHEN raid_level=3 THEN 1 ELSE 0 END) level3, SUM(CASE WHEN raid_level=4 THEN 1 ELSE 0 END) level4, SUM(CASE WHEN raid_level=5 THEN 1 ELSE 0 END) level5 FROM gym WHERE raid_end_timestamp > UNIX_TIMESTAMP()")
    array=($(for i in $raids; do echo $i; done))
    echo "$(date +'%F %T') | Current total Raids: ${array[0]}"
    grafana_sql 'INSERT INTO raid_data(timestamp,raids_total,raids_1,raids_2,raids_3,raids_4,raids_5) VALUES(now(),'${array[0]}','${array[1]}','${array[2]}','${array[3]}','${array[4]}','${array[5]}')'
}

function check_spawnpoints () {
    spawnpoints=$(scanner_sql "select count(*) from spawnpoint where despawn_sec is not null;")
    echo "$(date +'%F %T') | Verified Spanwpoints: $spawnpoints"
    grafana_sql 'INSERT INTO spawnpoint_data(timestamp,value) VALUES(now(),"'"$spawnpoints"'")'
}

while(true); do
    sleep 30
    cleanup
    check_pokemon
    sleep 5
    check_iv
    sleep 5
    check_quests
    sleep 5
    check_tr
    sleep 5
    check_devices
    sleep 5
    check_raids
    sleep 5
    check_spawnpoints
    sleep 5
done