#
# Copyright (c) Ensim Corporation 2000, 2001   All Rights Reserved.
#
# This software is furnished under a license and may be used and copied
# only  in  accordance  with  the  terms  of such  license and with the
# inclusion of the above copyright notice. This software or any other
# copies thereof may not be provided or otherwise made available to any
# other person. No title to and ownership of the software is hereby
# transferred.
#
# The information in this software is subject to change without notice
# and  should  not be  construed  as  a commitment by Ensim Corporation.
# Ensim assumes no responsibility for the use or  reliability  of its
# software on equipment which is not supplied by Ensim.
#
# 
install_srv() {

cat << EOF | psql appldb
\set ON_ERROR_STOP

CREATE TABLE bandwidth (
  site_id INT PRIMARY KEY NOT NULL,
  threshold INT8,
  rollover INT4,
  FOREIGN KEY (site_id) REFERENCES siteinfo(site_id) INITIALLY DEFERRED
);

CREATE TABLE bandwidth_log ( 
  site_id   INT,
  in_bytes  INT8,
  out_bytes INT8,
  svc_id    INT4,
  ts        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (site_id) REFERENCES siteinfo(site_id) INITIALLY DEFERRED
);
CREATE INDEX log_site_id_index ON bandwidth_log (site_id);
CREATE INDEX log_ts_index ON bandwidth_log (ts);

CREATE TABLE bandwidth_spans (
  site_id   INT,
  begindate DATE,
  enddate   DATE,
  FOREIGN KEY (site_id) REFERENCES siteinfo(site_id) INITIALLY DEFERRED
);
CREATE INDEX spans_site_id_key on bandwidth_spans (site_id);

CREATE TABLE bandwidth_services (
  svc_id INT PRIMARY KEY NOT NULL,
  name   varchar(64)
);
INSERT INTO bandwidth_services (svc_id, name) VALUES (0, 'Unknown');
INSERT INTO bandwidth_services (svc_id, name) VALUES (1, 'SMTP');
INSERT INTO bandwidth_services (svc_id, name) VALUES (2, 'POP');
INSERT INTO bandwidth_services (svc_id, name) VALUES (3, 'IMAP');
INSERT INTO bandwidth_services (svc_id, name) VALUES (4, 'FTP');
INSERT INTO bandwidth_services (svc_id, name) VALUES (5, 'SSH');
INSERT INTO bandwidth_services (svc_id, name) VALUES (6, 'HTTP 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (7, 'HTTPS 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (8, 'TELNET');
INSERT INTO bandwidth_services (svc_id, name) VALUES (9, 'HTTP Headers 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (10, 'HTTPS Headers 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (11, 'HTTP (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (12, 'HTTP Headers (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (13, 'HTTPS (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (14, 'HTTPS Headers (Preview) 1.3');

INSERT INTO bandwidth_services (svc_id, name) VALUES (15, 'HTTP 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (16, 'HTTPS 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (17, 'HTTP Headers 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (18, 'HTTPS Headers 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (19, 'HTTP (Preview) 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (20, 'HTTP Headers (Preview) 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (21, 'HTTPS (Preview) 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (22, 'HTTPS Headers (Preview) 2.0');

CREATE VIEW dummy_site_totals_view
  (site_id, domain, email, total, over, threshold, begindate)
AS SELECT siteinfo.site_id,
          siteinfo.domain,
          siteinfo.email,
          sum(bandwidth_log.in_bytes) + sum(bandwidth_log.out_bytes),
          sum(bandwidth_log.in_bytes) + sum(bandwidth_log.out_bytes) - bandwidth.threshold,
          bandwidth.threshold,
          bandwidth_spans.begindate
    WHERE bandwidth_log.site_id = siteinfo.site_id
      AND bandwidth.site_id = siteinfo.site_id
      AND bandwidth_spans.site_id = siteinfo.site_id
      AND bandwidth_spans.enddate is NULL
      AND bandwidth_log.ts >= bandwidth_spans.begindate
 GROUP BY siteinfo.domain,bandwidth.threshold,
          siteinfo.email,siteinfo.site_id,bandwidth_spans.begindate
UNION ALL
   SELECT siteinfo.site_id,
          siteinfo.domain,
          siteinfo.email,
          0,
          0,
          bandwidth.threshold,
          bandwidth_spans.begindate
    WHERE bandwidth.site_id = siteinfo.site_id
      AND bandwidth_spans.site_id = siteinfo.site_id
      AND bandwidth_spans.enddate is NULL;

CREATE VIEW site_totals_view
 (site_id, domain, email, total_bw, over, threshold, begindate)
AS SELECT site_id,
          domain,
          email,
          sum(total),
          sum(over),
          threshold,
          begindate
     FROM dummy_site_totals_view
 GROUP BY site_id, domain, email, threshold, begindate;

CREATE VIEW svc_totals_view
  (site_id, svc_name, domain, total_bytes)
AS SELECT siteinfo.site_id,bandwidth_services.name,siteinfo.domain,
           sum(bandwidth_log.in_bytes) + sum(bandwidth_log.out_bytes)
    WHERE bandwidth_log.site_id = siteinfo.site_id
      AND bandwidth_services.svc_id = bandwidth_log.svc_id
      AND bandwidth.site_id = siteinfo.site_id
 GROUP BY siteinfo.domain,bandwidth_services.name,siteinfo.site_id;


EOF

local retval="$?"
if [ $retval != 0 ]; then
   exit $retval
fi

touch /etc/appliance/apachemodules/vwh_httpdmon_module

config_sys_v_init_srv "bandwidth_manager" 1

}

upgrade_srv() {

    set_context "bandwidth postinstall: creating dummy_site_totals_view and site_totals_view"

# 3.1.0 introduced new tables, this code snipplet will
# recreate the necessary tables for upgrades from
# 3.0.x (PR 18296).

cat << EOF | psql appldb
DROP VIEW site_totals_view;
DROP VIEW dummy_site_totals_view;
\set ON_ERROR_STOP
CREATE VIEW dummy_site_totals_view
  (site_id, domain, email, total, over, threshold, begindate)
AS SELECT siteinfo.site_id,
          siteinfo.domain,
          siteinfo.email,
          sum(bandwidth_log.in_bytes) + sum(bandwidth_log.out_bytes),
          sum(bandwidth_log.in_bytes) + sum(bandwidth_log.out_bytes) - bandwidth.threshold,
          bandwidth.threshold,
          bandwidth_spans.begindate
    WHERE bandwidth_log.site_id = siteinfo.site_id
      AND bandwidth.site_id = siteinfo.site_id
      AND bandwidth_spans.site_id = siteinfo.site_id
      AND bandwidth_spans.enddate is NULL
      AND bandwidth_log.ts >= bandwidth_spans.begindate
 GROUP BY siteinfo.domain,bandwidth.threshold,
          siteinfo.email,siteinfo.site_id,bandwidth_spans.begindate
UNION ALL
   SELECT siteinfo.site_id,
          siteinfo.domain,
          siteinfo.email,
          0,
          0,
          bandwidth.threshold,
          bandwidth_spans.begindate
    WHERE bandwidth.site_id = siteinfo.site_id
      AND bandwidth_spans.site_id = siteinfo.site_id
      AND bandwidth_spans.enddate is NULL;

CREATE VIEW site_totals_view
 (site_id, domain, email, total_bw, over, threshold, begindate)
AS SELECT site_id,
          domain,
          email,
          sum(total),
          sum(over),
          threshold,
          begindate
     FROM dummy_site_totals_view
 GROUP BY site_id, domain, email, threshold, begindate;

\unset ON_ERROR_STOP
DELETE FROM bandwidth_services where svc_id = 6;
DELETE FROM bandwidth_services where svc_id = 7;
DELETE FROM bandwidth_services where svc_id = 9;
DELETE FROM bandwidth_services where svc_id = 10;
DELETE FROM bandwidth_services where svc_id = 11;
DELETE FROM bandwidth_services where svc_id = 12;
DELETE FROM bandwidth_services where svc_id = 13;
DELETE FROM bandwidth_services where svc_id = 14;
DELETE FROM bandwidth_services where svc_id = 15;
DELETE FROM bandwidth_services where svc_id = 16;
DELETE FROM bandwidth_services where svc_id = 17;
DELETE FROM bandwidth_services where svc_id = 18;
DELETE FROM bandwidth_services where svc_id = 19;
DELETE FROM bandwidth_services where svc_id = 20;
DELETE FROM bandwidth_services where svc_id = 21;
DELETE FROM bandwidth_services where svc_id = 22;
\set ON_ERROR_STOP
INSERT INTO bandwidth_services (svc_id, name) VALUES (6, 'HTTP 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (7, 'HTTPS 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (9, 'HTTP Headers 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (10, 'HTTPS Headers 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (11, 'HTTP (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (12, 'HTTP Headers (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (13, 'HTTPS (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (14, 'HTTPS Headers (Preview) 1.3');
INSERT INTO bandwidth_services (svc_id, name) VALUES (15, 'HTTP 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (16, 'HTTPS 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (17, 'HTTP Headers 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (18, 'HTTPS Headers 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (19, 'HTTP (Preview) 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (20, 'HTTP Headers (Preview) 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (21, 'HTTPS (Preview) 2.0');
INSERT INTO bandwidth_services (svc_id, name) VALUES (22, 'HTTPS Headers (Preview) 2.0');

EOF
# Need to update bandwidth_services table? How to do this idempotently?
}
. /etc/appliance/scriptsLib-3.0/common.lib

G_WP_SRV_NAME=bandwidth

if [ "$1" = "1" ]; then
    install_srv
elif [ "$1" ]; then
    upgrade_srv
fi

flush_context 0

set_context "Creating the service database entry"
exe "/usr/bin/edit_svcdb -i $G_WP_SRV_NAME"

script_exit_success "post"
