#!/usr/bin/python

#############################################################################
#
# NAME:        mrs_status_computation_check
#
# FACILITY:    SAM (Service Availability Monitoring)
#
# COPYRIGHT:
#         Copyright (c) 2009, Members of the EGEE Collaboration.
#         http://www.eu-egee.org/partners/
#         Licensed under the Apache License, Version 2.0.
#         http://www.apache.org/licenses/LICENSE-2.0
#         This software is provided "as is", without warranties
#         or conditions of any kind, either express or implied.
#
# DESCRIPTION: 
#         This script will dump latest (or statuses from given period) from the database for:
#           - service or site or region (list)
#           - profile (list)
#           - timestamp (begin, end). Latest status for now if timestamp not specified.
#
# AUTHORS:     Paloma Fuente, CERN
#
# CREATED:     21-Sep-2011
#
# NOTES:
#
# MODIFIED:
#
##############################################################################


########################
#
# Imports
#
########################

import sys
import cx_Oracle
import getopt


########################
#
# class DB
#
########################

class DB:

    def __init__(self, db):
        self.dbInfo = db
        self.dbConn = cx_Oracle.connect(self.dbInfo)
        self.dbCursor = self.dbConn.cursor()

    def execute(self, query):
        try:
            self.dbCursor.execute(query)
        except cx_Oracle.DatabaseError, exc:
            error, = exc.args
            print >> sys.stderr, "Oracle-Error-Code:", error.code
            print >> sys.stderr, "Oracle-Error-Message:", error.message
            self.close()
            sys.exit("Oracle exception")

    def close(self):
        self.dbConn.close()


########################
#
# Params
#
########################

class Params:

    def __init__(self):
        self.opts, extraparams = getopt.getopt(sys.argv[1:], '', ['db=', 'help', 'service=', 'site=', 'region=', 'profile=', 'timestamp=', 'orderbytime='])
        self.service = None
        self.site = None
        self.region = None 
        self.profile = None 
        self.begin = None
        self.end = None
        self.orderbytime = 'asc'
        self.db = None
        for opt, arg in self.opts:
            if opt == "--help":
                help = "\nmrs_status_computation_check --db=db_account@db_service/db_pass"
                help += "\n(following parameters are optional)"
                help += "\n--service=LIST\n--site=LIST\n--region=LIST\n--profile=LIST"
                help +="\n--timestamp=begin,end\n\tLatest status for now if timestamp not specified\n--orderbytime=asc|desc\n"
                sys.exit(help)
            if opt == '--service':
                self.service = arg
            elif opt == '--site':
                self.site = arg
            elif opt == '--region':
                self.region = arg
            elif opt == '--profile':
                self.profile = arg
            elif opt == '--timestamp':
                arg = arg.split(',')
                self.begin = arg[0]
                self.end = arg[1]
            elif opt == '--orderbytime':
                if arg == 'desc':
                    self.orderbytime = 'desc'
            elif opt == '--db':
                self.db = arg
        if self.db == None:
            sys.exit("Missing DB parameter")


    def procParams(self, where, latest=False):
        self.procService(where)
        self.procSite(where)
        self.procRegion(where)
        self.procProfile(where)
        self.procTimestamp(where, latest)


    def procService(self, where):
        if self.service != None:
            lWhere = []
            for service in self.service.split(','):
                sWhere = "s.hostname = '%s'" %service
                lWhere.append(sWhere)
            sWhere = " or " . join(lWhere)
            where.append("(" + sWhere + ")")


    def procSite(self, where):
        if self.site != None:
            lWhere = []
            for site in self.site.split(','):
                sWhere = "si.sitename = '%s'" %site
                lWhere.append(sWhere)
            sWhere = " or " . join(lWhere)
            where.append("(" + sWhere + ")")


    def procRegion(self, where):
        if self.region != None:
            lWhere = []
            for region in self.region.split(','):
                sWhere = "get_roc_name(s.id) = '%s'" %region
                lWhere.append(sWhere)
            sWhere = " or " . join(lWhere)
            where.append("(" + sWhere + ")")


    def procProfile(self, where):
        if self.profile != None:
            lWhere = []
            for profile in self.profile.split(','):
                sWhere = "f.name = '%s'" %profile
                lWhere.append(sWhere)
            sWhere = " or " .join(lWhere)
            where.append("(" + sWhere + ")")


    def procTimestamp(self, where, latest):
        if latest == True:
            if self.begin == None:
                    sWhere = "sc.timestamp <= to_unixts(current_timestamp)"
                    where.append(sWhere)
            else:
                    sWhere = "sc.timestamp <= to_unixts('%s')" %self.begin
                    where.append(sWhere)
        else:
            if self.begin != None:
                sWhere = "sc.timestamp > to_unixts('%s')" %self.begin
                where.append(sWhere)
            if self.end != None:
                sWhere = "sc.timestamp <= to_unixts('%s')" %self.end
                where.append(sWhere)

########################
#
# Main
#
########################

def getLatestInfo(params):
    dbQuery = "SELECT s.hostname as service, stf.flavourname, si.sitename as site, get_roc_name(s.id) as region, f.name as profile, max(uts_to_w3ctime(sc.timestamp)) as timestamp FROM statuschange_service_profile sc inner join service s on sc.service_id = s.id inner join metricstatus m on sc.metricstatus_id = m.id inner join service_site ss on ss.service_id = s.id inner join site si on si.id = ss.site_id inner join profile f on sc.profile_id = f.id inner join service_type_flavour stf on stf.id = s.flavour_id"
    where = []
    params.procParams(where, True)
    if where != []:
        dbQuery = dbQuery + " where " + " and " . join(where)
    dbQuery += " group by s.hostname, stf.flavourname, si.sitename, get_roc_name(s.id), f.name order by s.hostname"
    db = DB(params.db)
    db.execute(dbQuery)
    info = []
    for row in db.dbCursor:
        status = getLatestStatus(params.db, row[0], row[1], row[2], row[3], row[4], row[5])
        rowInfo = (row[0], row[1], row[2], row[3], row[4], row[5], status)
        info.append(rowInfo)
    db.close()
    return info

def getLatestStatus(dbInfo, service, flavourname, site, region, profile, timestamp):
    dbQuery = "SELECT s.hostname as service, stf.flavourname, si.sitename as site, get_roc_name(s.id) as region, f.name as profile, uts_to_w3ctime(sc.timestamp) as timestamp, m.description as status FROM statuschange_service_profile sc inner join service s on sc.service_id = s.id inner join metricstatus m on sc.metricstatus_id = m.id inner join service_site ss on ss.service_id = s.id inner join site si on si.id = ss.site_id inner join profile f on sc.profile_id = f.id inner join service_type_flavour stf on stf.id = s.flavour_id where (s.hostname = '%s') and (stf.flavourname = '%s') and (si.sitename = '%s') and (get_roc_name(s.id) = '%s') and (f.name = '%s') and uts_to_w3ctime(sc.timestamp) = '%s'" %(service, flavourname, site, region, profile, timestamp) 
    db = DB(dbInfo)
    db.execute(dbQuery)
    status = "-"
    for row in db.dbCursor:
        status = row[6]
    db.close()
    return status

def compAsc(x, y):
    strX = x[0]+x[1]+x[2]+x[3]+x[4]
    strY = y[0]+y[1]+y[2]+y[3]+y[4]
    if strX < strY :
        rst = -1
    elif strX > strY :
        rst = 1
    else :
        if x[5] < y[5]:
            rst = -1
        elif x[5] > y[5]:
            rst = 1
        else:
            rst = 0
    return rst

def compDesc(x, y):
    strX = x[0]+x[1]+x[2]+x[3]+x[4]
    strY = y[0]+y[1]+y[2]+y[3]+y[4]
    if strX < strY :
        rst = -1
    elif strX > strY :
        rst = 1
    else :
        if y[5] < x[5]:
            rst = -1
        elif y[5] > x[5]:
            rst = 1
        else:               
            rst = 0    
    return rst

params = Params()

#latest status
info = []
info = getLatestInfo(params)


if params.begin != None: # timestamp -> latest status + status for timestamp
    dbQuery = 'SELECT s.hostname as service, stf.flavourname, si.sitename as site, get_roc_name(s.id) as region, f.name as profile, uts_to_w3ctime(sc.timestamp) as timestamp, m.description as status FROM statuschange_service_profile sc inner join service s on sc.service_id = s.id inner join metricstatus m on sc.metricstatus_id = m.id inner join service_site ss on ss.service_id = s.id inner join site si on si.id = ss.site_id inner join profile f on sc.profile_id = f.id inner join service_type_flavour stf on stf.id = s.flavour_id'
    where = []
    params.procParams(where)
    if where != []:
        dbQuery = dbQuery + " where " + " and " . join(where)
    dbQuery = dbQuery + " order by s.hostname, uts_to_w3ctime(sc.timestamp) " + params.orderbytime
    db = DB(params.db)
    db.execute(dbQuery)
    for row in db.dbCursor:
        info.append(row)
    db.close()

    if params.orderbytime == 'desc':
        info.sort(compDesc)
    else:
        info.sort(compAsc)

for el in info:
    print el
