#!/usr/bin/env bash

POSITIONAL=()
while [[ $# -gt 0 ]]; do
  key="$1"

  case $key in
    -f|--format)
      format="$2"
      shift # past argument
      shift # past value
      ;;

    -p|--periodSecs)
      periodSecs="$2"
      shift # past argument
      shift # past value
      ;;

    -h|--hostsMatch)
      hostsMatch="$2"
      shift # past argument
      shift # past value
      ;;
  esac
done


if [ -z $format ];then
  format="CSV"
fi

if [ -z $periodSecs ];then
  periodSecs=24*3600
fi

if [ -z $hostsMatch ];then
  hostsMatch="(?i)(\\W|^)(rt.ru|domru.ru.com)(\\W|$)"
fi

clickhouse-client \
  --database=qoestor \
  --query="
            select today() as sys_date, *
from (select subscriber,
             any(login)                 login_any,
             countMerge(total_count)    total,
             uniqMerge(session_id_uniq) sessons_uniq,
             uniq(host)                 hosts_uniq,
             uniq(device_other)         device_other_uniq,
             uniq(user_agent)           user_agent_uniq,
             uniq(host_ip)              host_ip_uniq,
             uniq(url)                  url_uniq,
             uniq(subscriber)           subscribers_uniq,
             uniq(host_category)        host_category_uniq,
             uniq(bridge_vchannel)      bridge_vchannel_uniq
      from (select time                                                                                time,
                   subscriber                                                                          subscriber,
                   subscriber_login                                                                    subscriber_login,
                   login                                                                               login,
                   host                                                                                host,
                   host_ip                                                                             host_ip,
                   device                                                                              device,
                   referal                                                                             referal,
                   user_agent                                                                          user_agent,
                   cookie                                                                              cookie,
                   method                                                                              method,
                   result_code                                                                         result_code,
                   content_type                                                                        content_type,
                   total_count                                                                         total_count,
                   session_id_uniq                                                                     session_id_uniq,
                   content_length_avg                                                                  content_length_avg,
                   empty(host) ? 'Unknown' : replaceOne(host, '*', 'www')                              host_other,
                   empty(device) ? 'Unknown' : device                                                  device_other,
                   empty(user_agent) ? 'Unknown' : user_agent                                          user_agent_other,
                   empty(referal) ? 'Unknown' : referal                                                url,
                   dictGetUInt64OrDefault('urlcats_host_dic', 'cat_id', tuple(host_other),
                                          dictGetUInt64OrDefault('urlcats_host_dic', 'cat_id',
                                                                 tuple(replaceOne(host_other, 'www.', '')),
                                                                 dictGetUInt64OrDefault('urlcats_host_dic', 'cat_id',
                                                                                        tuple(concat('www.', host_other)),
                                                                                        toUInt64(0)))) host_category,
                   octet_delta_count_from_subscriber_sum,
                   octet_delta_count_to_subscriber_sum,
                   octet_delta_count_sum,
                   packet_delta_count_from_subscriber_sum,
                   packet_delta_count_to_subscriber_sum,
                   packet_delta_count_sum,
                   flow_start_millisecond_min,
                   flow_start_millisecond_from_subscriber_min,
                   flow_start_millisecond_to_subscriber_min,
                   flow_end_millisecond_max,
                   flow_end_millisecond_from_subscriber_max,
                   flow_end_millisecond_to_subscriber_max,
                   rtt_from_subscriber_avg,
                   rtt_to_subscriber_avg,
                   rtt_avg,
                   dictGetStringOrDefault('ulr_vchannel_name_dic', 'name', tuple(dpi_id, bridge_vchannel_num),
                                          toString(bridge_vchannel_num))                               bridge_vchannel,
                   dpi_id
            from qoestor.clickstream_agg
            where 1 = 1
              and (time >= (now() - ${periodSecs}) and time <= now())
              and match(host, '${hostsMatch}') = 1)
      group by subscriber
      with totals
      order by total desc
      limit 0,1000000) format ${format};
  " \