#!/bin/bash
## Report connected DAS' and their state

svcid=$BASH_ARGV
TRANSLATION="when 4015 then 'Gen2_SJC12' 
when 4016 then 'Gen2_SJC17' 
when 4017 then 'Gen2_SJC18' 
when 4018 then 'Gen2_SJC19' 
when 4019 then 'Gen2_SJC5' 
when 4020 then 'Gen2_SJC6'  
when 4021 then 'Gen2_SJC7' 
when 4022 then 'Gen2_SJC8' 
when 4023 then 'Gen2_SJC9' 
when 4024 then 'Gen2_SJC10' 
when 4025 then 'Gen2_SJC11' 
when 4026 then 'Gen2_SJC20' 
when 4027 then 'Gen2_SJC13' 
when 4028 then 'Gen2_SJC14' 
when 4029 then 'Gen2_SJC15' 
when 4030 then 'Gen2_SJC16'
when 4031 then 'Gen2_SJC21'
when 4032 then 'Gen2_SJC22'
when 4033 then 'Gen2_SJC23'
when 4034 then 'Gen2_SJC24'
when 4035 then 'Gen2_SJC25'
when 4036 then 'Gen2_SJC26'
when 4037 then 'Gen2_SJC27'"

function single {
daslog=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', ROUND((d.dev_used / 1024 / 1024 / 1024), 2) AS 'Size (GB)', IF(s.name LIKE 'ca-adsdas%', 'Gen2_Canada', (case d.namespace_port $TRANSLATION end)) AS 'Namespace', d.date_attached AS 'Attached', d.replication_started AS 'Replication Begun', d.replication_completed AS 'Replication Completed', d.date_detached AS 'Detached' from stations s, device d where d.das_station=s.id and svcid = \"${svcid}\" ORDER BY s.id;")
if [ -n "${daslog}" ]; then
    echo -e "${daslog}"
else
    echo -e "DAS ${svcid} not found"
fi
}

function main {
daslog=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', ROUND((d.dev_used / 1024 / 1024 / 1024), 2) AS 'Size (GB)', IF(s.name LIKE 'ca-adsdas%', 'Gen2_Canada', (case d.namespace_port $TRANSLATION end)) AS 'Namespace', d.date_attached AS 'Attached', d.replication_started AS 'Replication Begun', CONCAT(COUNT(j.start_dev_read_sec), ' of ', COUNT(j.job_type)) AS 'Current Job', d.replication_completed AS 'Replication Completed' from device d, stations s, jobs j where d.date_detached = 0 and d.das_station=s.id and d.id = j.device_id GROUP BY 2 ORDER BY s.id, 2;")
if [ -n "${daslog}" ]; then
    echo -e "${daslog}"
else
    echo -e "Warning: No connected DASes"
fi
}

function completions {
##daslog=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT das_station AS 'Station', svcid, ROUND((dev_used / 1024 / 1024 / 1024), 2) AS 'Size (GB)', IF(s.name LIKE 'ca-adsdas%', 'Gen2_Canada', (case d.namespace_port $TRANSLATION end)) AS 'Namespace', date_attached AS 'Attached', date_detached as 'Detached', replication_started AS 'Replication Begun', replication_completed AS 'Replication Completed', TIMEDIFF(replication_completed, replication_started) as 'TIME (H:M:S)', ROUND(((dev_used / 1024 / 1024) / (TIME_TO_SEC(TIMEDIFF(replication_completed, replication_started)))),4) as 'RATE MB/s' from device where replication_completed between date_sub(now(),INTERVAL \"${days}\" DAY) AND now()  ORDER BY 8;")
daslog=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', ROUND((d.dev_used / 1024 / 1024 / 1024), 2) AS 'Size (GB)', IF(s.name LIKE 'ca-adsdas%', 'Gen2_Canada', (case d.namespace_port $TRANSLATION end)) AS 'Namespace', d.date_attached AS 'Attached', d.date_detached as 'Detached', d.replication_started AS 'Replication Begun', d.replication_completed AS 'Replication Completed', TIMEDIFF(d.replication_completed, d.replication_started) as 'TIME (H:M:S)', ROUND(((d.dev_used / 1024 / 1024) / (TIME_TO_SEC(TIMEDIFF(d.replication_completed, d.replication_started)))),4) as 'RATE MB/s' from device d, stations s where replication_completed between date_sub(now(),INTERVAL \"${days}\" DAY) AND now() AND d.das_station=s.id ORDER BY 8;")
if [ -n "${daslog}" ]; then
    echo -e "${daslog}"
else
    echo -e "Warning: No connected DASes"
fi
}

function still_running {
daslog=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid, ROUND((d.dev_used / 1024 / 1024 / 1024), 2) AS 'Size (GB)', IF(s.name LIKE 'ca-adsdas%', 'Gen2_Canada', (case d.namespace_port $TRANSLATION end)) AS 'Namespace', d.date_attached AS 'Attached', d.replication_started AS 'Replication Begun', CONCAT(COUNT(j.start_dev_read_sec), ' of ', COUNT(j.job_type)) AS 'Current Job', CONCAT(TIMESTAMPDIFF(HOUR, MAX(j.last_update), NOW()), ' hours ago') AS 'Last Update' from stations s, device d, jobs j where d.das_station=s.id and d.replication_completed = 0 and d.date_detached = 0 and d.id = j.device_id GROUP BY 2 ORDER BY 5;")
if [ -n "${daslog}" ]; then
    echo -e "${daslog}"
else
    echo -e "Warning: No running DASes"
fi
}
function daily {
start=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', d.dev_used AS 'Used_bytes', date_attached AS 'Attached', IFNULL(f.flag_name, '-') AS 'Flag' from stations s, device d LEFT JOIN flags f on f.svcid=d.svcid where d.das_station=s.id and d.date_detached = 0 and d.replication_started = 0 ORDER BY s.id;")
remove=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', d.namespace_port AS 'Port', d.dev_used as 'Bytes', IFNULL(f.flag_name, '-') AS 'Flag' from stations s, device d LEFT JOIN flags f on f.svcid=d.svcid where d.das_station=s.id and d.replication_completed > 0 and d.date_detached = 0 ORDER BY s.id;")
last3=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', d.namespace_port AS 'Port', d.dev_used AS 'Bytes', d.date_attached AS 'Attached', d.replication_started AS 'Rep_Start', d.replication_completed AS 'Rep_End', IFNULL(f.flag_name, '-') AS 'Flag' from stations s, device d LEFT JOIN flags f on f.svcid=d.svcid where d.das_station=s.id and d.date_attached > date_sub(now(), INTERVAL 3 DAY) ORDER BY 5, s.id;")
detach_yesterday=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', d.namespace_port AS 'Port', d.dev_used AS 'Bytes', d.date_attached AS 'Attached', d.date_detached AS 'Detached', IFNULL(f.flag_name, '-') AS 'Flag' from stations s, device d LEFT JOIN flags f on f.svcid=d.svcid where d.das_station=s.id and d.date_detached > SUBDATE(CURDATE(), 1) ORDER BY s.id;")
if [ -n "${start}" ]; then
    echo -e "\n[*] DASes to start"
    echo -e "${start}"
else
    echo -e "[-] No Fresh DAS today"
fi
if [ -n "${remove}" ]; then
    echo -e "\n[*] DASes to remove"
    echo -e "${remove}"
else
    echo -e "[-] No DAS to remove"
fi
if [ -n "${last3}" ]; then
    echo -e "\n[*] Previous 3 Days Attached"
    echo -e "${last3}"
else
    echo -e "[-] Nothing added in 3 Days?"
fi
if [ -n "${detach_yesterday}" ]; then
    echo -e "\n[*] Detached Yesterday"
    echo -e "${detach_yesterday}"
else
    echo -e "[-] Nothing detached yesterday"
fi
}

function cron {
unplug=$(mysql -t -u das_status -p das -h opsdb-p1.sjc.axdc.net --password='axST@tu$PW' -e "SELECT s.name AS 'Station', d.svcid AS 'ServiceID', d.namespace_port AS 'Port', d.dev_used as 'Bytes', IFNULL(f.flag_name, '-') AS 'Flag' from stations s, device d LEFT JOIN flags f on f.svcid=d.svcid where d.das_station=s.id and d.replication_completed > 0 and d.date_detached = 0 ORDER BY s.id;")
if [ -n "${unplug}" ]; then
    echo -e "DAS to unplug"
    echo -e "${unplug}"
else
    echo -e "No DAS to unplug\n"
fi
    echo -e "Still ingesting"
    still_running
}

function help_text {
    echo -e "Print list and status of all connected DAS devices.\n"
    echo "Usage: das_status [OPTIONS] [svcid]"
    echo -e "\t-h\t\t - print this message"
    echo -e "\t-s [svcid]\t - optionally show individual device stats. eg: das_status -s 7oh5"
    echo -e "\t-c # days\t - Show completed DASes over the last 'n' days. Defaults to 3 days"
    echo -e "\t-d\t\t - Daily output"
    echo -e "\t-r\t\t - Show currently running DASes"
    exit 2
}

case $1 in
    -h|--help)
        help_text
        ;;
    -s)
        if [ "$#" -lt 2 ]; then
            echo -e "ERROR: This option requires a SVCID\n"
            help_text
        else
            single
        fi
        ;;
    -c)
        if [ "$#" -lt 2 ]; then
            days="3"
            echo -e "\n[*] Showing completions for past ${days} days"
            completions
        else
            days=${BASH_ARGV}
            echo -e "\n[*] Showing completions for past ${days} days"
            completions
        fi
        ;;
    -d)
        daily
        ;;
    --cron)
        cron
        ;;
    -r)
        still_running
        ;;
    *)
        main
        ;;
    esac
