#!/bin/bash
SCRIPTVERSION='v1.15'
SCRIPT=$(readlink -f $0)
SCRIPTPATH=`dirname $SCRIPT`
PIA_CONF_FILE=${SCRIPTPATH}'/../config/pialert.conf'
PIA_CONF_FILE_PATH=${SCRIPTPATH}'/../config'
PIA_DB_FILE=${SCRIPTPATH}'/../db/pialert.db'
PIA_DB_FILE_PATH=${SCRIPTPATH}'/../db'
PIA_BACK_FILE_PATH=${SCRIPTPATH}'/../back'
PIA_REPORTS_PATH=${SCRIPTPATH}'/../front/reports'
#Textformating
bold=$(tput bold)
normal=$(tput sgr0)
CURRENT_USER=$(whoami)

case $1 in

  help)

read -r -d '' PIALERTCLI_MANUAL << EOM
pialert-cli $SCRIPTVERSION (https://github.com/leiweibau/Pi.Alert)
Command line tool of Pi.Alert, which is used to support the front and back end.
Usage: ${bold}pialert-cli <command>${normal}

This is the list of supported commands

 ${bold}disable_scan <MIN>${normal}
    Stops all active scans and prevents new scans from starting. You can set a
    Timeout in minutes. If no timeout is set, Pi.Alert restarts itself with
    the next scan after 10min.

 ${bold}disable_mainscan <MIN>${normal}
    Disables the main scanning method for Pi.Alert (ARP scan, reading the 
    Pi-hole DHCP leases and device database, as well as collecting data from 
    other routers).

 ${bold}disable_icmp_mon${normal}
    Allows the start of new scans again. If the ICMPSCAN_ACTIVE parameter
    does not exist yet, it will be created and set to FALSE.

 ${bold}disable_service_mon${normal}
    Allows the start of new scans again. If the SCAN_WEBSERVICES parameter
    does not exist yet, it will be created and set to FALSE.

 ${bold}enable_scan${normal}
    Allows the start of new scans again.

 ${bold}enable_mainscan <MIN>${normal}
    Enables the main scanning method for Pi.Alert (ARP scan, reading the 
    Pi-hole DHCP leases and device database, as well as collecting data from 
    other routers).

 ${bold}enable_icmp_mon${normal}
    Enable ICMP Monitoring. If the ICMPSCAN_ACTIVE parameter does not
    exist yet, it will be created and set to TRUE.

 ${bold}enable_service_mon${normal}"
    Enable Web Service Monitoring. If the SCAN_WEBSERVICES parameter does not
    exist yet, it will be created and set to TRUE.

 ${bold}reporting_test${normal}
    Test reporting for all activated services.

 ${bold}set_apikey${normal}
    With the API key it is possible to make queries to the database without
    using the web page. If an API key already exists, it will be replaced.

 ${bold}set_autopassword${normal}
    Sets a new random password as a hashed value and show it plaintext in the
    console. If the PIALERT_WEB_PROTECTION parameter does not exist yet, it 
    will be created and set to TRUE (login enabled).

 ${bold}set_login${normal}
    Sets the parameter PIALERT_WEB_PROTECTION in the config file to TRUE. If 
    the parameter is not present, it will be created. Additionally the default
    password '123456' is set.

 ${bold}set_password <password>${normal}
    Sets the new password as a hashed value. If the PIALERT_WEB_PROTECTION
    parameter does not exist yet, it will be created and set to TRUE (login
    enabled).

 ${bold}set_permissions${normal}
    Repair file group permissions

    Options:
    Repair file user and group permissions.
    --lxc:        set "root" as user name
    --custom:     set individual user name
    --homedir:    get user name from homedir

 ${bold}set_sudoers${normal}
    Create sudoer file for www-data and Pi.Alert user

    Options:
    --lxc:        set "root" as user name
    --custom:     set individual user name

 ${bold}unset_login${normal}
    Sets the parameter PIALERT_WEB_PROTECTION in the config file to FALSE. If
    the parameter is not present, it will be created. Additionally the default
    password '123456' is set.

 ${bold}unset_sudoers${normal}
    Delete sudoer file for www-data and Pi.Alert user

 ${bold}update_db${normal}
    The script tries to make the database compatible for this fork.

2023 leiweibau (https://github.com/leiweibau/Pi.Alert) (https://leiweibau.net)
EOM

    echo -e "$PIALERTCLI_MANUAL" | more
    ;;

  set_login)
    ## Check if PIALERT_WEB_PROTECTION exists
    CHECK_PROT=$(grep "PIALERT_WEB_PROTECTION" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create PIALERT_WEB_PROTECTION and enable it
        sed -i "/^VENDORS_DB.*/a PIALERT_WEB_PROTECTION = True" $PIA_CONF_FILE
        sed -i "/^PIALERT_WEB_PROTECTION.*/a PIALERT_WEB_PASSWORD = '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92'" $PIA_CONF_FILE
    else
        ## Switch PIALERT_WEB_PROTECTION to enable
        sed -i "/PIALERT_WEB_PROTECTION/c\PIALERT_WEB_PROTECTION = True" $PIA_CONF_FILE
    fi
    echo "Login is now enabled"
    ;;

  unset_login)
    ## Check if PIALERT_WEB_PROTECTION exists
    CHECK_PROT=$(grep "PIALERT_WEB_PROTECTION" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create PIALERT_WEB_PROTECTION and disable it
        sed -i "/^VENDORS_DB.*/a PIALERT_WEB_PROTECTION = False" $PIA_CONF_FILE
        sed -i "/^PIALERT_WEB_PROTECTION.*/a PIALERT_WEB_PASSWORD = '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92'" $PIA_CONF_FILE
    else
        ## Switch PIALERT_WEB_PROTECTION to disable
        sed -i "/PIALERT_WEB_PROTECTION/c\PIALERT_WEB_PROTECTION = False" $PIA_CONF_FILE
    fi
    echo "Login is now disabled"
    ;;

  set_password)
    PIA_PASS=$2
    ## Check if PIALERT_WEB_PROTECTION exists
    CHECK_PROT=$(grep "PIALERT_WEB_PROTECTION" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create PIALERT_WEB_PROTECTION and enable it
        sed -i "/^VENDORS_DB.*/a PIALERT_WEB_PROTECTION = True" $PIA_CONF_FILE
    fi
    ## Prepare Hash
    PIA_PASS_HASH=$(echo -n $PIA_PASS | sha256sum | awk '{print $1}')
    echo "   The hashed password is:"
    echo "   $PIA_PASS_HASH"
    ## Check if the password parameter is set
    CHECK_PWD=$(grep "PIALERT_WEB_PASSWORD" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PWD -eq 0 ]
    then
        sed -i "/^PIALERT_WEB_PROTECTION.*/a PIALERT_WEB_PASSWORD = '$PIA_PASS_HASH'" $PIA_CONF_FILE
    else
        sed -i "/PIALERT_WEB_PASSWORD/c\PIALERT_WEB_PASSWORD = '$PIA_PASS_HASH'" $PIA_CONF_FILE
    fi
    echo ""
    echo "The new password is set"
    ;;

  set_autopassword)
    ## Check if PIALERT_WEB_PROTECTION exists
    CHECK_PROT=$(grep "PIALERT_WEB_PROTECTION" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create PIALERT_WEB_PROTECTION and enable it
        sed -i "/^VENDORS_DB.*/a PIALERT_WEB_PROTECTION = True" $PIA_CONF_FILE
    fi
    ## Create autopassword
    PIA_AUTOPASS=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 8 | head -n 1)
    echo "   The password is: $PIA_AUTOPASS"
    ## Prepare Hash
    PIA_AUTOPASS_HASH=$(echo -n $PIA_AUTOPASS | sha256sum | awk '{print $1}')
    echo "   The hashed password is:"
    echo "   $PIA_AUTOPASS_HASH"
    ## Check if the password parameter is set
    CHECK_PWD=$(grep "PIALERT_WEB_PASSWORD" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PWD -eq 0 ]
    then
        ## Create password parameter
        sed -i "/^PIALERT_WEB_PROTECTION.*/a PIALERT_WEB_PASSWORD = '$PIA_AUTOPASS_HASH'" $PIA_CONF_FILE
    else
        ## Overwrite password parameter
        sed -i "/PIALERT_WEB_PASSWORD/c\PIALERT_WEB_PASSWORD = '$PIA_AUTOPASS_HASH'" $PIA_CONF_FILE
    fi
    echo ""
    echo "The new password is set"
    ;;

  disable_scan)
    STOPTIMER=$2
    re='^[0-9]+$'
    if ! [[ $STOPTIMER =~ $re ]] ; then
       echo "No timeout is set. Pi.Alert restarts itself with the next scan after 10min."
       STOPTIMER=10
    fi
    sudo killall arp-scan
    echo $STOPTIMER > ${SCRIPTPATH}/../db/setting_stoppialert
    /usr/bin/python3 ${SCRIPTPATH}/pialert_reporting_test.py reporting_starttimer
    echo "Configured Pi.Alert scans are disabled"
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_002', 'pialert-cli', 'LogStr_0511', '', '${STOPTIMER} min');"
    fi

    ;;

  enable_scan)
    rm ${SCRIPTPATH}/../db/setting_stoppialert
    /usr/bin/python3 ${SCRIPTPATH}/pialert_reporting_test.py reporting_stoptimer
    echo "Configured Pi.Alert scans are enabled"
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_002', 'pialert-cli', 'LogStr_0510', '', '');"
    fi
    ;;

  enable_service_mon)
    ## Check if SCAN_WEBSERVICES exists
    CHECK_PROT=$(grep "SCAN_WEBSERVICES" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create SCAN_WEBSERVICES and enable it
        sed -i "/^VENDORS_DB.*/a SCAN_WEBSERVICES = True" $PIA_CONF_FILE
    else
        ## Switch SCAN_WEBSERVICES to enable
        sed -i "/SCAN_WEBSERVICES/c\SCAN_WEBSERVICES = True" $PIA_CONF_FILE
    fi
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_030', 'pialert-cli', 'LogStr_0301', '', '');"
    fi
    echo "Web Service Monitoring is now enabled"
    ;;

  disable_service_mon)
    ## Check if SCAN_WEBSERVICES exists
    CHECK_PROT=$(grep "SCAN_WEBSERVICES" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create SCAN_WEBSERVICES and disable it
        sed -i "/^VENDORS_DB.*/a SCAN_WEBSERVICES = False" $PIA_CONF_FILE
    else
        ## Switch SCAN_WEBSERVICES to disable
        sed -i "/SCAN_WEBSERVICES/c\SCAN_WEBSERVICES = False" $PIA_CONF_FILE
    fi
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_030', 'pialert-cli', 'LogStr_0302', '', '');"
    fi
    echo "Web Service Monitoring is now disabled"
    ;;

  enable_icmp_mon)
    ## Check if ICMPSCAN_ACTIVE exists
    CHECK_PROT=$(grep "ICMPSCAN_ACTIVE" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create ICMPSCAN_ACTIVE and enable it
        sed -i "/^VENDORS_DB.*/a ICMPSCAN_ACTIVE = True" $PIA_CONF_FILE
    else
        ## Switch ICMPSCAN_ACTIVE to enable
        sed -i "/ICMPSCAN_ACTIVE/c\ICMPSCAN_ACTIVE = True" $PIA_CONF_FILE
    fi
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_031', 'pialert-cli', 'LogStr_0303', '', '');"
    fi
    echo "ICMP Monitoring is now enabled"
    ;;

  disable_icmp_mon)
    ## Check if ICMPSCAN_ACTIVE exists
    CHECK_PROT=$(grep "ICMPSCAN_ACTIVE" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create ICMPSCAN_ACTIVE and disable it
        sed -i "/^VENDORS_DB.*/a ICMPSCAN_ACTIVE = False" $PIA_CONF_FILE
    else
        ## Switch ICMPSCAN_ACTIVE to disable
        sed -i "/ICMPSCAN_ACTIVE/c\ICMPSCAN_ACTIVE = False" $PIA_CONF_FILE
    fi
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_031', 'pialert-cli', 'LogStr_0304', '', '');"
    fi
    echo "ICMP Monitoring is now disabled"
    ;;

  enable_mainscan)
    ## Check if ARPSCAN_ACTIVE exists
    CHECK_PROT=$(grep "ARPSCAN_ACTIVE" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create ARPSCAN_ACTIVE and enable it
        sed -i "/^VENDORS_DB.*/a ARPSCAN_ACTIVE = True" $PIA_CONF_FILE
    else
        ## Switch ARPSCAN_ACTIVE to enable
        sed -i "/ARPSCAN_ACTIVE/c\ARPSCAN_ACTIVE = True" $PIA_CONF_FILE
    fi
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_032', 'pialert-cli', 'LogStr_9991', '', '');"
    fi
    echo "Main Scan is now enabled"
    ;;

  disable_mainscan)
    ## Check if ARPSCAN_ACTIVE exists
    CHECK_PROT=$(grep "ARPSCAN_ACTIVE" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_PROT -eq 0 ]
    then
        ## Create ARPSCAN_ACTIVE and disable it
        sed -i "/^VENDORS_DB.*/a ARPSCAN_ACTIVE = False" $PIA_CONF_FILE
    else
        ## Switch ARPSCAN_ACTIVE to disable
        sed -i "/ARPSCAN_ACTIVE/c\ARPSCAN_ACTIVE = False" $PIA_CONF_FILE
    fi
    if [ "$CURRENT_USER" != "www-data" ]; then
        sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_032', 'pialert-cli', 'LogStr_9992', '', '');"
    fi
    echo "Main Scan is now disabled"
    ;;

  update_db)
    ## update database
    echo "################################################################################"
    echo "# You are planning to update the Pi.Alert DB. Please make sure that no scan    #"
    echo "# takes place during the update to avoid possible database errors afterwards!  #"
    echo "#                                                                              #"
    echo "# This can be done by pausing the Arp scan via the settings page. However,     #"
    echo "# scans that are already running will not be terminated. For more information, #"
    echo "# check the Help/FAQ section in Pi.Alert                                       #"
    echo "#                                                                              #"
    echo "# Press STRG+C to Abort                                                        #"
    echo "################################################################################"
    echo ""
    echo ""
    printf "%s " "Press enter to continue"
    read ans
    echo ""
    echo "Update DB $PIA_DB_FILE"
    echo -e "\nPurge old db backup"
    rm ${PIA_DB_FILE_PATH}/pialert.db.bak
    echo -e "...Create backup before insert new table"
    sqlite3 "$PIA_DB_FILE" ".backup '${PIA_DB_FILE_PATH}/pialert.db.bak'"
    # Online_History
    echo -e "...Insert new table 'Online_History' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'Online_History' ('Index' INTEGER, 'Scan_Date' TEXT, 'Online_Devices' INTEGER, 'Down_Devices' INTEGER, 'All_Devices' INTEGER, 'Archived_Devices' INTEGER, PRIMARY KEY(\"Index\" AUTOINCREMENT));"
    # network_infrastructure
    echo -e "...Insert new table 'network_infrastructure' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'network_infrastructure' ('device_id' INTEGER, 'net_device_name' TEXT NOT NULL, 'net_device_typ' TEXT NOT NULL, 'net_device_port' INTEGER, 'net_downstream_devices' TEXT, PRIMARY KEY(\"device_id\" AUTOINCREMENT));"
    # Devices.dev_Infrastructure
    echo -e "...Insert new column 'dev_Infrastructure' to table 'Devices' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Devices');" | awk -F\| '{print $2}' | grep -wci "dev_Infrastructure")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Devices' ADD COLUMN 'dev_Infrastructure' INTEGER;"
    else
      echo "        Column 'dev_Infrastructure' already exists in the 'Devices' table."
    fi
    # Devices.dev_Infrastructure_port
    echo -e "...Insert new column 'dev_Infrastructure_port' to table 'Devices' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Devices');" | awk -F\| '{print $2}' | grep -wci "dev_Infrastructure_port")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Devices' ADD COLUMN 'dev_Infrastructure_port' INTEGER;"
    else
      echo "        Column 'dev_Infrastructure_port' already exists in the 'Devices' table."
    fi
    # network_infrastructure.net_downstream_devices
    echo -e "...Insert new column 'net_downstream_devices' to table 'network_infrastructure' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('network_infrastructure');" | awk -F\| '{print $2}' | grep -wci "net_downstream_devices")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'network_infrastructure' ADD COLUMN 'net_downstream_devices' TEXT;"
    else
      echo "        Column 'net_downstream_devices' already exists in the 'network_infrastructure' table."
    fi
    # network_dumb_dev
    echo -e "...Insert new table 'network_dumb_dev' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'network_dumb_dev' ('dev_Name' TEXT, 'dev_MAC' TEXT, 'dev_Infrastructure' INTEGER, 'dev_Infrastructure_port' INTEGER, 'dev_PresentLastScan' TEXT, 'dev_LastIP' TEXT, 'id' INTEGER, PRIMARY KEY(\"id\" AUTOINCREMENT));"
    # Services_Events
    echo -e "...Insert new table 'Services_Events' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'Services_Events' ('moneve_URL' TEXT NOT NULL, 'moneve_DateTime' TEXT NOT NULL, 'moneve_StatusCode' NUMERIC NOT NULL, 'moneve_Latency' TEXT NOT NULL, 'moneve_TargetIP' TEXT NOT NULL);"
    # Services_CurrentScan
    echo -e "...Insert new table 'Services_CurrentScan' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'Services_CurrentScan' ('cur_URL' TEXT NOT NULL, 'cur_DateTime' TEXT NOT NULL, 'cur_StatusCode' NUMERIC NOT NULL, 'cur_Latency' TEXT NOT NULL, 'cur_AlertEvents' INTEGER DEFAULT 0, 'cur_AlertDown' INTEGER DEFAULT 0, 'cur_StatusChanged' INTEGER DEFAULT 0, 'cur_LatencyChanged' INTEGER DEFAULT 0, 'cur_TargetIP' TEXT NOT NULL, 'cur_StatusCode_prev' NUMERIC, 'cur_TargetIP_prev' TEXT);"
    # Services
    echo -e "...Insert new table 'Services' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'Services' ('mon_URL' TEXT NOT NULL, 'mon_MAC' TEXT, 'mon_LastStatus' NUMERIC NOT NULL, 'mon_LastLatency' TEXT NOT NULL, 'mon_LastScan' TEXT NOT NULL, 'mon_Tags' TEXT, 'mon_AlertEvents' INTEGER DEFAULT 0, 'mon_AlertDown' INTEGER DEFAULT 0, 'mon_TargetIP' TEXT NOT NULL, PRIMARY KEY(\"mon_URL\"));"
    # Services.mon_Notes
    echo -e "...Insert new column 'mon_Notes' to table 'Services' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services');" | awk -F\| '{print $2}' | grep -wci "mon_Notes")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services' ADD COLUMN 'mon_Notes' TEXT;"
    else
      echo "        Column 'mon_Notes' already exists in the 'Services' table."
    fi
    # Pi.Alert Journal
    echo -e "...Insert new table 'pialert_journal' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'pialert_journal' ('journal_id' INTEGER, 'Journal_DateTime' TEXT NOT NULL, 'LogClass' NUMERIC NOT NULL, 'Trigger' TEXT NOT NULL, 'LogString' TEXT NOT NULL, 'Hash' TEXT, 'Additional_Info' TEXT, PRIMARY KEY(\"journal_id\" AUTOINCREMENT));"
    # Devices.dev_Model
    echo -e "...Insert new column 'dev_Model' to table 'Devices' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Devices');" | awk -F\| '{print $2}' | grep -wci "dev_Model")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Devices' ADD COLUMN 'dev_Model' STRING(250);"
    else
      echo "        Column 'dev_Model' already exists in the 'Devices' table."
    fi
    # Devices.dev_Serialnumber
    echo -e "...Insert new column 'dev_Serialnumber' to table 'Devices' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Devices');" | awk -F\| '{print $2}' | grep -wci "dev_Serialnumber")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Devices' ADD COLUMN 'dev_Serialnumber' STRING(100);"
    else
      echo "        Column 'dev_Serialnumber' already exists in the 'Devices' table."
    fi
    # Devices.dev_ConnectionType
    echo -e "...Insert new column 'dev_ConnectionType' to table 'Devices' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Devices');" | awk -F\| '{print $2}' | grep -wci "dev_ConnectionType")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Devices' ADD COLUMN 'dev_ConnectionType' STRING(30);"
    else
      echo "        Column 'dev_ConnectionType' already exists in the 'Devices' table."
    fi
    # ICMP_Mon
    echo -e "...Insert new table 'ICMP_Mon' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'ICMP_Mon' ('icmp_ip' NUMERIC NOT NULL, 'icmp_hostname' TEXT, 'icmp_LastScan' TEXT NOT NULL, 'icmp_PresentLastScan' INTEGER DEFAULT 0, 'icmp_avgrtt' NUMERIC, 'icmp_AlertEvents' INTEGER DEFAULT 0, 'icmp_AlertDown' INTEGER DEFAULT 0, 'icmp_Favorite' INTEGER DEFAULT 0, 'icmp_Notes' INTEGER, 'icmp_type' TEXT, 'icmp_group' TEXT, 'icmp_location' TEXT, 'icmp_owner' TEXT, PRIMARY KEY(\"icmp_ip\"));"
    # ICMP_Mon_CurrentScan
    echo -e "...Insert new table 'ICMP_Mon_CurrentScan' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'ICMP_Mon_CurrentScan' ('cur_ip' NUMERIC NOT NULL, 'cur_LastScan' TEXT NOT NULL, 'cur_Present' INTEGER, 'cur_PresentChanged' INTEGER, 'cur_avgrrt' NUMERIC, 'cur_AlertEvents' INTEGER NOT NULL DEFAULT 0, 'cur_AlertDown' INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(\"cur_ip\"));"
    # ICMP_Mon_Events
    echo -e "...Insert new table 'ICMP_Mon_Events' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'ICMP_Mon_Events' ( 'icmpeve_ip' NUMERIC NOT NULL, 'icmpeve_DateTime' TEXT NOT NULL, 'icmpeve_Present' INTEGER, 'icmpeve_avgrtt' NUMERIC);"
    # Services.mon_ssl_subject
    echo -e "...Insert new column 'mon_ssl_subject' to table 'Services' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services');" | awk -F\| '{print $2}' | grep -wci "mon_ssl_subject")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services' ADD COLUMN 'mon_ssl_subject' TEXT;"
    else
      echo "        Column 'mon_ssl_subject' already exists in the 'Services' table."
    fi
    # Services.mon_ssl_issuer
    echo -e "...Insert new column 'mon_ssl_issuer' to table 'Services' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services');" | awk -F\| '{print $2}' | grep -wci "mon_ssl_issuer")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services' ADD COLUMN 'mon_ssl_issuer' TEXT;"
    else
      echo "        Column 'mon_ssl_issuer' already exists in the 'Services' table."
    fi
    # Services.mon_ssl_valid_from
    echo -e "...Insert new column 'mon_ssl_valid_from' to table 'Services' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services');" | awk -F\| '{print $2}' | grep -wci "mon_ssl_valid_from")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services' ADD COLUMN 'mon_ssl_valid_from' TEXT;"
    else
      echo "        Column 'mon_ssl_valid_from' already exists in the 'Services' table."
    fi
    # Services.mon_ssl_valid_to
    echo -e "...Insert new column 'mon_ssl_valid_to' to table 'Services' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services');" | awk -F\| '{print $2}' | grep -wci "mon_ssl_valid_to")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services' ADD COLUMN 'mon_ssl_valid_to' TEXT;"
    else
      echo "        Column 'mon_ssl_valid_to' already exists in the 'Services' table."
    fi
    # Services.mon_ssl_fc
    echo -e "...Insert new column 'mon_ssl_fc' to table 'Services' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services');" | awk -F\| '{print $2}' | grep -wci "mon_ssl_fc")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services' ADD COLUMN 'mon_ssl_fc' INTEGER DEFAULT 0;"
    else
      echo "        Column 'mon_ssl_fc' already exists in the 'Services' table."
    fi
    # Services_CurrentScan.cur_ssl_subject
    echo -e "...Insert new column 'cur_ssl_subject' to table 'Services_CurrentScan' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services_CurrentScan');" | awk -F\| '{print $2}' | grep -wci "cur_ssl_subject")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services_CurrentScan' ADD COLUMN 'cur_ssl_subject' TEXT;"
    else
      echo "        Column 'cur_ssl_subject' already exists in the 'Services_CurrentScan' table."
    fi
    # Services_CurrentScan.cur_ssl_issuer
    echo -e "...Insert new column 'cur_ssl_issuer' to table 'Services_CurrentScan' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services_CurrentScan');" | awk -F\| '{print $2}' | grep -wci "cur_ssl_issuer")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services_CurrentScan' ADD COLUMN 'cur_ssl_issuer' TEXT;"
    else
      echo "        Column 'cur_ssl_issuer' already exists in the 'Services_CurrentScan' table."
    fi
    # Services_CurrentScan.cur_ssl_valid_from
    echo -e "...Insert new column 'cur_ssl_valid_from' to table 'Services_CurrentScan' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services_CurrentScan');" | awk -F\| '{print $2}' | grep -wci "cur_ssl_valid_from")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services_CurrentScan' ADD COLUMN 'cur_ssl_valid_from' TEXT;"
    else
      echo "        Column 'cur_ssl_valid_from' already exists in the 'Services_CurrentScan' table."
    fi
    # Services_CurrentScan.cur_ssl_valid_to
    echo -e "...Insert new column 'cur_ssl_valid_to' to table 'Services_CurrentScan' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services_CurrentScan');" | awk -F\| '{print $2}' | grep -wci "cur_ssl_valid_to")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services_CurrentScan' ADD COLUMN 'cur_ssl_valid_to' TEXT;"
    else
      echo "        Column 'cur_ssl_valid_to' already exists in the 'Services_CurrentScan' table."
    fi
    # Services_CurrentScan.cur_ssl_fc
    echo -e "...Insert new column 'cur_ssl_fc' to table 'Services_CurrentScan' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services_CurrentScan');" | awk -F\| '{print $2}' | grep -wci "cur_ssl_fc")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services_CurrentScan' ADD COLUMN 'cur_ssl_fc' INTEGER DEFAULT 0;"
    else
      echo "        Column 'cur_ssl_fc' already exists in the 'Services_CurrentScan' table."
    fi
    # Services_Events.moneve_ssl_fc
    echo -e "...Insert new column 'moneve_ssl_fc' to table 'Services_Events' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Services_Events');" | awk -F\| '{print $2}' | grep -wci "moneve_ssl_fc")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Services_Events' ADD COLUMN 'moneve_ssl_fc' INTEGER DEFAULT 0;"
    else
      echo "        Column 'moneve_ssl_fc' already exists in the 'Services_Events' table."
    fi
    # Online_History.data_source
    echo -e "...Insert new column 'Data_Source' to table 'Online_History' to DB"
    existing=$(sqlite3 $PIA_DB_FILE "PRAGMA table_info('Online_History');" | awk -F\| '{print $2}' | grep -wci "Data_Source")
    if [ $existing -eq 0 ]; then
      sqlite3 $PIA_DB_FILE "ALTER TABLE 'Online_History' ADD COLUMN 'Data_Source' TEXT;"
    else
      echo "        Column 'Data_Source' already exists in the 'Online_History' table."
    fi
    # Tools_Speedtest_History
    echo -e "...Insert new table 'Tools_Speedtest_History' to DB"
    sqlite3 $PIA_DB_FILE "CREATE TABLE IF NOT EXISTS 'Tools_Speedtest_History' ('ID' INTEGER, 'speed_date' TEXT, 'speed_isp' TEXT, 'speed_server' TEXT, 'speed_ping' NUMERIC, 'speed_down' NUMERIC, 'speed_up' INUMERIC, PRIMARY KEY(\"ID\" AUTOINCREMENT));"


    echo -e "\nUpdate finished!\n"

    # Enter to Journal
    sqlite3 $PIA_DB_FILE "INSERT INTO 'pialert_journal' ('Journal_DateTime', 'LogClass', 'Trigger', 'LogString', 'Hash', 'Additional_Info') VALUES('$(date '+%Y-%m-%d %H:%M:%S')', 'b_010', 'pialert-cli', 'LogStr_0010', '', '');"

    ;;

  set_apikey)
    ## Create API-Key
    ##PIA_APIKEY_RAND="Test"
    PIA_APIKEY_RAND=$(head -c 4096 /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 60 | head -n 1)
    ## Check if PIALERT_APIKEY exists
    CHECK_API=$(grep "PIALERT_APIKEY" $PIA_CONF_FILE | wc -l)
    if [ $CHECK_API -eq 0 ]
    then
        ## Create PIALERT_APIKEY
        sed -i "/^VENDORS_DB.*/a PIALERT_APIKEY = '$PIA_APIKEY_RAND'" $PIA_CONF_FILE
    else
        ## Change PIALERT_APIKEY
        sed -i "/PIALERT_APIKEY/c\PIALERT_APIKEY = '$PIA_APIKEY_RAND'" $PIA_CONF_FILE
    fi
    echo "API-Key set"
    ;;

  set_permissions)
    # get username
    user() {
        echo "Pi.Alert User: $PIA_USER"
        echo "    Set DB Permissions"
        sudo chmod -R 775 "$PIA_DB_FILE_PATH"
        sudo chown -R "$PIA_USER":www-data "$PIA_DB_FILE_PATH"
        echo "    Set Config Permissions"
        sudo chmod -R 775 "$PIA_CONF_FILE_PATH"
        sudo chown -R "$PIA_USER":www-data "$PIA_CONF_FILE_PATH"
        echo "    Set Back Permissions"
        sudo chmod -R 775 "$PIA_BACK_FILE_PATH"
        sudo chown -R "$PIA_USER":www-data "$PIA_BACK_FILE_PATH"
        echo "    Set Reports Permissions"
        sudo chmod -R 775 "$PIA_REPORTS_PATH"
        sudo chown -R "$PIA_USER":www-data "$PIA_REPORTS_PATH"
        echo "Permissions set"; }
    case "$2" in
        --custom)
            read -p "Enter the username: " CUSTOM_USER
            PIA_USER="$CUSTOM_USER"
            user
            ;;
        --homedir)
            PIA_USER=$(basename "$(dirname "$(dirname "$SCRIPTPATH")")")
            #PIA_USER=$(echo ${SCRIPTPATH} | xargs dirname | xargs dirname | xargs basename)
            user
            ;;
        --lxc)
            PIA_USER="root"
            user
            ;;
        *)
            echo "Pi.Alert Group: www-data"
            echo "    Set DB Group Permissions only"
            sudo chmod -R 775 "$PIA_DB_FILE_PATH"
            sudo chgrp -R www-data "$PIA_DB_FILE_PATH"
            echo "    Set Config Group Permissions only"
            sudo chmod -R 775 "$PIA_CONF_FILE_PATH"
            sudo chgrp -R www-data "$PIA_CONF_FILE_PATH"
            echo "    Set Back Group Permissions only"
            sudo chmod -R 775 "$PIA_BACK_FILE_PATH"
            sudo chgrp -R www-data "$PIA_BACK_FILE_PATH"
            echo "    Set Reports Permissions"
            sudo chmod -R 775 "$PIA_REPORTS_PATH"
            sudo chown -R "$PIA_USER":www-data "$PIA_REPORTS_PATH"
            echo "Group Permissions set"
            ;;
    esac


    ;;

  set_sudoers)
    # get username
    if [ "$2" == "--lxc" ]; then
        PIA_USER="root"
    elif [ "$2" == "--custom" ]; then
        read -p "Enter the username: " CUSTOM_USER
        PIA_USER="$CUSTOM_USER"
    else
        # Default behavior, calculate PIA_USER from SCRIPTPATH
        PIA_USER=$(echo ${SCRIPTPATH} | xargs dirname | xargs dirname | xargs basename)
    fi
    # get username from install directory
    echo "Pi.Alert User: $PIA_USER"
    echo -e "\nFrontend sudoer"
    echo "www-data ALL=(ALL) NOPASSWD: ${SCRIPTPATH}/pialert-cli" | sudo tee /etc/sudoers.d/pialert-frontend
    echo "www-data ALL=(ALL) NOPASSWD: /usr/bin/df" | sudo tee -a /etc/sudoers.d/pialert-frontend
    echo "www-data ALL=(ALL) NOPASSWD: ${SCRIPTPATH}/speedtest/speedtest" | sudo tee -a /etc/sudoers.d/pialert-frontend
    echo -e "\nBackend sudoer"
    echo "${PIA_USER} ALL=(ALL) NOPASSWD: ${SCRIPTPATH}/pialert-cli" | sudo tee /etc/sudoers.d/pialert-backend
    echo "${PIA_USER} ALL=(ALL) NOPASSWD: /usr/sbin/arp-scan" | sudo tee -a /etc/sudoers.d/pialert-backend
    echo "${PIA_USER} ALL=(ALL) NOPASSWD: /usr/bin/nmap" | sudo tee -a /etc/sudoers.d/pialert-backend
    echo "${PIA_USER} ALL=(ALL) NOPASSWD: ${SCRIPTPATH}/speedtest/speedtest" | sudo tee -a /etc/sudoers.d/pialert-backend
    echo "${PIA_USER} ALL=(ALL) NOPASSWD: /usr/bin/ping" | sudo tee -a /etc/sudoers.d/pialert-backend
    ;;

  unset_sudoers)
    ## Set file permissions
    # get username from install directory
    PIA_USER=$(echo ${SCRIPTPATH} | xargs dirname | xargs dirname | xargs basename)
    echo "Removing sudoers"
    sudo rm /etc/sudoers.d/pialert-frontend
    sudo rm /etc/sudoers.d/pialert-backend
    ;;

  reporting_test)
    ## test reporting
    /usr/bin/python3 ${SCRIPTPATH}/pialert_reporting_test.py reporting_test
    echo "Test executed"
    ;;

  arg_list)
    echo disable_scan disable_mainscan disable_icmp_mon disable_service_mon enable_scan enable_mainscan enable_icmp_mon enable_service_mon reporting_test set_apikey set_autopassword set_login set_password set_permissions set_sudoers unset_login unset_sudoers update_db
    ;;

  *)
    echo "pialert-cli $SCRIPTVERSION (https://github.com/leiweibau/Pi.Alert)"
    echo "Use \"${bold}pialert-cli help${normal}\" for a list of supported commands."
esac