#!/usr/bin/env bash
set -e
set -u

check_for() {
  echo -n "Checking for $*... " >&2
}

check_ok() {
  echo "ok" >&2
}

check_fail() {
  echo "not ok" >&2
  if [ "$res" ]; then
    echo "----------"
    echo "$res"
    echo "----------"
  fi
  exit 2
}

output_redirect() {
  if [ "${stdout}" -eq 1 ]; then
    cat
  else
    cat >>"${reportfile}"
  fi
}

usage() {
  cat <<EOT
Usage: $0 [OPTION]...
Get a subscription report from your Bareos Director.
By default, the report is written to 'subscription-report.txt' in the current
working directory.

Options:
  -o, --output <file>     write report to <file>
  --stdout                print report to stdout
  --anonymize             remove client names from report

EOT
}

reportfile="./subscription-report.txt"
anonymize=0
stdout=0
res=""

while [ "${1:-}" ]; do
  case "$1" in
    --stdout)
      stdout=1
      ;;
    --output|-o)
      reportfile="$2"
      shift
      ;;
    --anon*)
      anonymize=1
      ;;
    --help)
      usage
      exit 0
      ;;
    *)
      echo "Unknown parameter $1" >&2
      exit 1
      ;;
  esac
  shift
done

if [ ${stdout} -eq 0 ] && [ -e "${reportfile}" ]; then
  echo "${reportfile} already exists." >&2
  exit 1
fi

check_for "bconsole command"
if [ "${BCONSOLE:+x}" ] && [ -x "${BCONSOLE}" ]; then
  bconsole="${BCONSOLE}"
  check_ok
elif bconsole="$(command -v bconsole)"; then
  check_ok
else
  check_fail
fi

check_for "director access"
if res="$("${bconsole}" <<< "version")"; then
  check_ok
else
  check_fail
fi
unset res

echo "Generating subscription report" >&2

# Check how my many backup count are configured for subscriptions
configured_units=$("${bconsole}" <<< ".api 2
show director
" | awk '/"subscriptions"/ {gsub(",","",$2);print $2}')
if [ -z "${configured_units}" ]; then
  configured_units=0
fi

if [ ${anonymize} -eq 1 ]; then
  client_name_column="client_anon"
  client_order_field="client_id"
else
  client_name_column="client_name"
  client_order_field="client_name"
fi

# Collect bareos-dir version
"${bconsole}" <<< ".api 2
version
" | awk '/^.*"version":."[0-9]/ {gsub(",","",$2);gsub("\"","",$2);print "Bareos Director Version: " $2}' | output_redirect

# Global warning about sqlquery mode:
# * no blank lines (as sqlquery will terminate)
# * no comments at end of line (as lines will be merged)
# * beware of shell expansion (esp. $ and \)
#
# We use temporary views. As every bconsole gets its own database session, these will
# never be visible to anybody else.

"${bconsole}" <<EOT | grep "^+\|^|" | output_redirect
.api 0
status dir
sqlquery
CREATE TEMPORARY VIEW fileset_plugin AS (
  SELECT
    filesetid,
    fileset,
    LOWER(
      COALESCE(
        SUBSTRING(filesettext FROM '(?i)plugin = python3?:(?:.*:)*module_name=bareos-fd-([^:\\s]+)'),
        SUBSTRING(filesettext FROM '(?i)plugin = python3?:(?:.*:)?module_name=bareos-([^:\\s]+)'),
        SUBSTRING(filesettext FROM '(?i)plugin = python3?:(?:.*:)?module_name=([^:\\s]+)'),
        CASE
          WHEN filesettext ~* '(?i)plugin = python3?(:|$)' THEN 'other python plugin'
        END,
        SUBSTRING(filesettext FROM '(?i)plugin = grpc:([^:\\s]+)'),
        SUBSTRING(filesettext FROM '(?i)Plugin = ([^:\\s]+)')
      )
    ) AS plugin
  FROM (
    SELECT
      filesetid,
      fileset,
      regexp_replace(
        regexp_replace(
          regexp_replace(filesettext, '\\\\.', '§', 'g'),
          '"', '', 'g'
        ),
      '(?i)plugin = grpc:bareos-grpc-fd-plugin-bridge:', 'plugin = ', 'g'
      ) AS filesettext
    FROM fileset
  ) bashed_fileset
);
CREATE TEMPORARY VIEW client_detail AS (
  SELECT
    c.name AS client_name,
    'Client ' || c.clientid AS client_anon,
    c.clientid AS client_id,
    f.fileset AS fileset,
    GREATEST(j.readbytes::bigint, j.jobbytes::bigint) / 10^9 AS size_gb,
    f.plugin AS plugin
  FROM job j
  INNER JOIN client c
    ON c.clientid = j.clientid
  INNER JOIN fileset_plugin f
    ON j.filesetid = f.filesetid
  WHERE jobid IN (
    SELECT
      MAX(j.jobid) AS jobid
    FROM job j
    INNER JOIN fileset f
      ON j.filesetid = f.filesetid
    WHERE level = 'F'
      AND j.jobstatus IN ('T','W')
      AND j.type = 'B'
      AND j.jobbytes > 0
    GROUP BY j.clientid, f.fileset
  )
);
CREATE TEMPORARY VIEW client_aggregated AS (
  SELECT
    client_name,
    client_anon,
    client_id,
    CASE
      WHEN plugin IS NULL THEN 1
      ELSE COUNT(fileset)
    END as count,
    SUM(size_gb) as size_gb,
    COALESCE(plugin, 'none (file data)') as plugin
  FROM client_detail
  GROUP BY client_name, client_anon, client_id, plugin
);
CREATE TEMPORARY VIEW plugin_aggregated AS (
  SELECT
    COALESCE(plugin, 'none (file data)') as plugin,
    SUM(count) as count,
    SUM(size_gb) as size_gb
  FROM client_aggregated
  GROUP BY plugin
  ORDER BY plugin
);
SELECT ${client_name_column} as client, plugin, count, to_char(size_gb, '9999990D00') AS size_gb FROM (
  SELECT
    client_name,
    client_anon,
    client_id,
    plugin,
    SUM(count) AS count,
    SUM(size_gb) AS size_gb,
    1 AS order
  FROM client_aggregated
  GROUP BY client_name, client_anon, client_id, plugin
  UNION
  SELECT
    'TOTAL' AS client_name,
    'TOTAL' AS client_anon,
    0 as client_id,
    NULL AS plugin,
    SUM(count) AS count,
    SUM(size_gb) AS size_gb,
    2 AS order
  FROM client_aggregated
) t
ORDER BY t.order, t.${client_order_field}, t.plugin
;
SELECT ${client_name_column} as client, count, to_char(size_gb, '9999990D00') AS size_gb FROM (
  SELECT
    client_name,
    client_anon,
    client_id,
    SUM(count) AS count,
    SUM(size_gb) AS size_gb,
    1 AS order
  FROM client_aggregated
  GROUP BY client_name, client_anon, client_id
  UNION
  SELECT
    'TOTAL' AS client_name,
    'TOTAL' AS client_anon,
    0 as client_id,
    SUM(count) AS count,
    SUM(size_gb) AS size_gb,
    2 AS order
  FROM client_aggregated
) t
ORDER BY t.order, t.${client_order_field}
;
SELECT
  plugin,
  count,
  to_char(size_gb, '9999990D00') AS size_gb
FROM (
  SELECT
    plugin,
    count,
    size_gb,
    1 AS order
  FROM plugin_aggregated
  UNION
  SELECT
    'TOTAL' as plugin,
    SUM(count) as count,
    SUM(size_gb) AS size_gb,
    2 AS order
  FROM plugin_aggregated
) t
ORDER BY t.order, t.plugin
;
SELECT
  CASE
    WHEN t.size_units >= t.count_units THEN 'volume-based (' || t.size_units || ' volume-based units >= ' || t.count_units || ' count-based units)'
    ELSE 'count-based (' || t.size_units || ' volume-based < ' || t.count_units || ' count-based)'
  END AS accounting_mode,
  GREATEST(t.count_units, t.size_units) AS used,
  configured_units as configured,
  configured_units - GREATEST(t.count_units, t.size_units) as remaining
FROM (
  SELECT
    COALESCE(SUM(count), 0) AS count_units,
    CEIL(COALESCE(SUM(size_gb)/1000, 0.0)) AS size_units,
    ${configured_units} AS configured_units
  FROM client_aggregated
) t
;
EOT

