#!/bin/bash

export PATH="$PATH:/usr/bin:/usr/sbin:/sbin"

DBNAME=xxxx
DBUSER=xxxx
DBPASS=xxxx

# runs query in arg 1
function query {
  echo "$1" | mysql -N -s --user="$DBUSER" --password="$DBPASS" "$DBNAME";
}

# runs query in arg 1 and selects last_insert_id()
function insert {
  query "$1; SELECT LAST_INSERT_ID()"
}

# converts string ip into number representation
function ip2num {
  IFS='.' read -a b <<< "$1"
  echo $((${b[0]} * (256**3) + ${b[1]} * (256**2) + ${b[2]} * 256 + ${b[3]}))
}

function pingscan {
  nmap -sP "$1" >/dev/null 2>/dev/null
}

# updates db with current arp table data
function arp2db {
  arp -na | grep ether | awk '{print $2 " " $4}' |\
  while read ip mac
  do
    ip=${ip:1: -1}
    add_ip_mac "$ip" "$mac"
  done
}

# updates db with own interfaces
function if2db {
  add_ip_mac "192.168.1.252" "20:cf:30:2a:1e:8a"
}

# adds the given ip with given mac to the db
function add_ip_mac {
    ip="$1"
    mac="$2"

    qry="SELECT id FROM local_client WHERE mac_address = '$mac'"

    client_id=$(query "$qry")
    if [ ! -z "$client_id" ]
    then
      qry="UPDATE local_client SET last_seen = NOW() WHERE id = $client_id"
      query "$qry"
    else
      qry="INSERT INTO local_client (mac_address, first_seen, last_seen)"
      qry="$qry VALUES('$mac', NOW(), NOW())"
      client_id=$(insert "$qry")
    fi

    num_ip=$(ip2num "$ip")
    #echo "$client_id, $mac, $ip, $num_ip"

    qry="SELECT id FROM ip4_address WHERE address = $num_ip"
    ip4_id=$(query "$qry")
    if [ ! -z "$ip4_id" ]
    then
      qry="UPDATE ip4_address SET last_seen = NOW(), local_client_id = $client_id"
      qry="$qry WHERE id = $ip4_id"
      query "$qry"
    else
      qry="INSERT INTO ip4_address (address, local_client_id, first_seen, last_seen)"
      qry="$qry VALUES($num_ip, $client_id, NOW(), NOW())"
      query "$qry"
    fi
}

function wlan {

 sudo iwlist wlan0 scan 2>/dev/null | grep  '\(Cell\|Channel:\|ESSID:\|Quality=\)' |\
 while read line
 do
  if [[ "$line" =~ " Address: " ]]
  then
    channel=
    essid=
    quality=
    address=$(awk '{print $NF}' <<< "$line")
  elif [[ "$line" =~ "Channel:" ]]
  then
    channel=$(awk -F: '{print $NF}' <<< "$line")
  elif [[ "$line" =~ "ESSID:" ]]
  then
    essid=$(awk -F: '{print $NF}' <<< "$line")
    essid="${essid:1: -1}"
  elif [[ "$line" =~ "Quality=" ]]
  then
    quality=$(sed 's#^.*Quality=\([0-9]\+/[0-9]\+\).*$#\1#' <<< "$line")
  fi

  if [ ! -z "$address" -a ! -z "$channel" -a ! -z "$essid" -a ! -z "$quality" ]
  then
    essid="$(tr "'" "_" <<< "$essid")"
    qry="SELECT id FROM wlan WHERE essid LIKE '$essid'"
    wlan_id=$(query "$qry")
    if [ ! -z "$wlan_id" ]
    then
      qry="UPDATE wlan SET mac_address = lower('$address'), channel = $channel,"
      qry="$qry quality = $quality, last_seen = NOW() WHERE id = $wlan_id"
      query "$qry"
    else
      qry="INSERT INTO wlan (essid, channel, mac_address, quality,"
      qry="$qry first_seen, last_seen) VALUES ('$essid', $channel,"
      qry="$qry lower('$address'), $quality, NOW(), NOW())"
      query "$qry"
    fi
  fi
 done
}

if2db
pingscan "192.168.1.*"
arp2db
wlan