#!/bin/bash

#
# Name:         rsb-server-acct.sh
# Version:      1.0.1
# Description:  Track OpenVPN sessions.
#
# (c) 2023, Rapidseedbox, alexey@rapidseedbox.com
#

#set -x
#. /var/db/rsb/openvpn-debug.env

set -o pipefail

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

SC_NAME="$( basename "${0}" )"
SC_VER="$( grep -m 1 -E '^#\s*Version:\s+' "${0}" | awk '{print $NF}' )"

[ -z "${ifconfig_pool_remote_ip}" ] || [ -z "${script_type}" ] && exit 0

command -v sqlite3 > /dev/null 2>&1 || exit 0

HOSTNAME="$( hostname )"

STATS_DIR="/var/db/rsb"
[ ! -d "${STATS_DIR}" ] && mkdir -p "${STATS_DIR}"
STATS_DB="${STATS_DIR}/openvpn.db"

#env >> "${STATS_DIR}/openvpn-debug.log"
#set >> "${STATS_DIR}/openvpn-debug.log"

if [ ! -e "${STATS_DB}" ] ; then
    sqlite3 "${STATS_DB}" <<EOF
CREATE TABLE version ( 
    id TEXT
);
INSERT INTO version ( id ) VALUES ( "${SC_VER}" );
CREATE TABLE session(
    id                       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    time_unix                INTEGER,
    username                 TEXT,
    dev_type                 TEXT,
    trusted_ip               TEXT,
    trusted_port             ITEGER,
    ifconfig_remote          TEXT,
    ifconfig_pool_remote_ip  TEXT,
    bytes_received           INTEGER,
    bytes_sent               INTEGER,
    SESSION_START            INTEGER,
    SESSION_END              INTEGER
    );
EOF
fi

case "${script_type}" in
  "client-connect")
      _tstamp="$( date -u '+%s' )"
      sqlite3 "${STATS_DB}" \
          "INSERT INTO session \
              ( time_unix, username, dev_type, \
                trusted_ip, trusted_port, ifconfig_remote, \
                ifconfig_pool_remote_ip, \
                bytes_received, bytes_sent, \
                SESSION_START, SESSION_END )
           VALUES \
              ( ${time_unix}, '${username}', '${dev_type}', \
                '${trusted_ip}', '${trusted_port}', '${ifconfig_remote}', \
                '${ifconfig_pool_remote_ip}', \
                0, 0, \
                ${_tstamp}, 0 );"
      ;;
  "client-disconnect")
      _tstamp="$( date -u '+%s' )"
      sqlite3 "${STATS_DB}" \
          "UPDATE session \
              SET SESSION_END = ${_tstamp}, \
                  bytes_received = ${bytes_received}, \
                  bytes_sent = ${bytes_received} \
              WHERE     time_unix = ${time_unix} \
                    AND username = '${username}' \
                    AND ifconfig_pool_remote_ip = '${ifconfig_pool_remote_ip}';"
      ;;
esac  

exit 0