#!/usr/bin/env python2
'''Script to query the Data Quality MySQL database tables
Author F. R. Di Lodovico, QMUL, May/03/2010
'''
import databaseUtils
import getopt
import os
import sys

def usage():
    '''Function to print out the usage of the script
    '''
    print "Script to query the data quality database."
    print "Usage:"
    print "queryDQ.py [-h][-p][-n][-s][-b][-e][-c][-f][-r][-a]"
    print ""
    print "Where:"
    print "<event time> is the time in seconds the event was taken."
    print ""
    print "Options:"
    print "-h, --help            Prints this help."
    print "-s, --subdetector=    Subdetector to query (default is all subdetectors)."
    print "                      A ':'-separated list of subdetectors can be supplied"
    print "                      ECAL:TPC:POD to select ECAL, TPC and POD."
    print "-b, --begintime=      The start time interval in seconds."
    print "-e, --endtime=        The end time interval in seconds."
    print "-c, --createtime=     The time the data were loaded into the database in seconds"
    print "-f, --flag=           The flag value."
    print "-r, --version=        The version of the release used to extract the flag."
    print "                      A ':'-separated list of releases can be supplied"
    print "                      'rel1':'rel2':'rel3' to select rel1, rel2 and rel3."
    print "-a, --author=         The person that loaded the data."
    print "                      A ':'-separated list of authors can be supplied"
    print "                      'author1':'author2':'author3' to select author1, author2 and author3."
    print "-p, --production      Flag to query production tables (default is test)."
    print "-n, --cosmics         Flag to query cosmics tables (default is test)."
    print "-v, --verbose         Prints verbose output."
    print ""

def parseOptions():
    '''Function to read in the command line options
    '''
    detectors = ["TPC", "FGD", "P0D", "ECAL", "SMRD", "MAGNET", "INGRID"]
    
    eventtime = None
    
    try:
        opts, args = getopt.getopt(sys.argv[1:], "hs:b:e:c:r:f:a:pnv",
                                   ["help",
                                    "subdetector=", 
                                    "flag=",
                                    "version=",
                                    "author=",
                                    "begintime=",
                                    "endtime=",
                                    "createtime=",
                                    "production", 
                                    "cosmics", 
                                    "verbose"])
    except getopt.GetoptError, err:
        print str(err)
        usage()
        sys.exit(2)
    
    mode = "test"
    cmode = False
    begintime = -1
    endtime = -1
    createtime = -1
    flag = -1
    # Use vectors for more choices
    subdetectors = []
    versions = []
    authors = []
    verboseFlag = False
    for opt, val in opts:
        if (opt in ("-h", "--help")):
            usage()
            sys.exit()
        if (opt in ("-p", "--production")):
            mode = "production"
        if (opt in ("-n", "--cosmics")):
            cmode = True
        if (opt in ("-v", "--verbose")):
            verboseFlag = True
        if (opt in ("-s", "--subdetector")):
            if (":" in val):
                subdetectors = val.split(":")
            else:
                subdetectors = [val]
        if (opt in ("-b", "--begintime")):
            begintime = int(val)
        if (opt in ("-e", "--endtime")):
            endtime = int(val)
        if (opt in ("-c", "--createtime")):
            createtime = int(val)
        if (opt in ("-f", "--flag")):
            flag = int(val)
        if (opt in ("-r", "--version")):
            if (":" in val):
                versions = val.split(":")
            else:
                versions = [val]
        if (opt in ("-a", "--author")):
            if (":" in val):
                authors = val.split(":")
            else:
                authors = [val]
    if (cmode):
        if (mode == "production"):
            mode = "prodcosmics"
        else:
            mode = "testcosmics"


    # Return the event time and the subdetectors selected 
    return (mode, subdetectors, begintime, endtime, createtime,
            flag, versions, authors, verboseFlag)

def queryDQDB():
    # Read in the command-line options
    mode, subdetectors, begintime, \
    endtime, createtime, flag, versions, authors, \
    verboseFlag = parseOptions()

    # Read in the table schemas
    dqTableName, authorTableName, flagDefsTableName, versionTableName, subdetectorTableName = databaseUtils.dqTables(mode, verboseFlag)

    # Read in the schema
    dqSchema = databaseUtils.DQSchema(dqTableName, verboseFlag)
    
    # Get the database tables
    dqtable = databaseUtils.DQTable(dqSchema, verboseFlag)

    # Query the table and return all rows
    if (len(subdetectors) == 0 and begintime == -1 and endtime == -1 and
        createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRows()

    # Query the table if begintime supplied
    if (len(subdetectors) == 0 and begintime > 0 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithBeginTime(begintime)

    # Query the table if endtime supplied
    if (len(subdetectors) == 0 and begintime == -1 and endtime > 0 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithEndTime(endtime)

    # Query the table if createtime supplied
    if (len(subdetectors) == 0 and begintime == -1 and endtime == -1 and
       createtime > 0 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithCreateTime(createtime)

    # Query the table if subdetector supplied
    if (len(subdetectors) > 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetector(subdetectors)

    # Query the table if flag supplied
    if (len(subdetectors) == 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag > 0 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithFlag(flag)

    # Query the table if version supplied
    if (len(subdetectors) == 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) > 0 and len(authors) == 0):
        rows = dqtable.getRowsWithVersion(versions)

    # Query the table if author name supplied
    if (len(subdetectors) == 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) > 0):
        rows = dqtable.getRowsWithAuthor(authors)

    # Query the table if subdetector + begintime supplied
    if (len(subdetectors) > 0 and begintime > 0 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetectorBeginTime(subdetectors, begintime)

    # Query the table if subdetector + endtime supplied
    if (len(subdetectors) > 0 and begintime == -1 and endtime > 0 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetectorEndTime(subdetectors, endtime)

    # Query the table if subdetector + flag supplied
    if (len(subdetectors) > 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag > 0 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetectorFlag(subdetectors, flag)

    # Query the table if subdetector + createtime supplied
    if (len(subdetectors) > 0 and begintime == -1 and endtime == -1 and
       createtime > 0 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetectorCreateTime(subdetectors, createtime)

    # Query the table if subdetector + version supplied
    if (len(subdetectors) > 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) > 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetectorVersion(subdetectors, versions)

    # Query the table if subdetector + author supplied
    if (len(subdetectors) > 0 and begintime == -1 and endtime == -1 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) > 0):
        rows = dqtable.getRowsWithSubdetectorAuthor(subdetectors, authors)

    # Query the table if subdetector + begintime + endtime supplied
    if (len(subdetectors) > 0 and begintime > 0 and endtime > 0 and
       createtime == -1 and flag == -1 and len(versions) == 0 and len(authors) == 0):
        rows = dqtable.getRowsWithSubdetectorBeginEndTime(subdetectors, begintime, endtime)
        
    print "-------------- RESULTS --------------------"
    for row in rows[1:]:
       print row

if __name__ == '__main__':
    queryDQDB()