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

def usage():
    '''Function to print out the usage of the script
    '''
    print "Script to query the data quality database."
    print "Usage:"
    print "GetDQ.py [-h -v -p -n -s <subdetector>] <event time>"
    print ""
    print "Where:"
    print "<subdetector> is the subdector."
    print "<event time> is the time in seconds the event was taken."
    print ""
    print "Options:"
    print "-h, --help            Prints this help."
    print "-p, --production      Flag to query production tables (default is test)."
    print "-n, --cosmics         Flag to query cosmics tables (default is test)."
    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 "-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:pnv", ["help","subdetector=", "production", "cosmics", "verbose"])
    except getopt.GetoptError, err:
        print str(err)
        usage()
        sys.exit(2)
    
    if (not args):
        print "You must supply the event time to the script"
	usage()
	sys.exit(2)
    else:
	eventtime = int(args[0])

    mode = "test"
    cmode = False
    # Empty subdetectors list denotes all subdetectors
    subdetectors = []
    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 (cmode):
        if (mode == "production"):
            mode = "prodcosmics"
        else:
            mode = "testcosmics"

    # Return the event time and the subdetectors selected 
    return (eventtime, mode, subdetectors, verboseFlag)

def queryDQ():
    # Read in the command-line options
    eventtime, mode, subdetectors, 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 dataquality table for the dq flag, creation time
    # Must pass the subdetector schema as an argument for the join query
    # Returned rows are of the format flag, starttime, endtime, createtime
    rows = dqtable.getFlag(eventtime, subdetectors)
    result = []
    createtime = '-1'
    starttime = '-1'
    endtime = '-1'
    flag = [-1,-1,-1,-1]
    print "-------------- RESULTS --------------------"

    # export setDQDate = 'CREATED; 2000-01-01 10:00:00' 
    # export setDQDate = 'LOADED; 2000-01-01 10:00:00' 
    env_setDQdate = os.environ.get('setDQDate')
    setcreationdate = -1
    setloadingdate = -1
    if ( env_setDQdate ):
        if("CREATED" in env_setDQdate):
            setcreationdatehm = env_setDQdate.split(";")[1].strip()
            try:                
                setcreationdate = time.mktime(time.strptime(setcreationdatehm,"%Y-%m-%d %H:%M:%S"))
            except:
                print 'invalid time format for setDQDate, must be YYYY-MM-DD HH:MM:SS'
                sys.exit(1)
        elif("LOADED" in env_setDQdate):
            setloadingdatehm = env_setDQdate.split(";")[1].strip()
            try:
                setloadingdate = time.mktime(time.strptime(setloadingdatehm,"%Y-%m-%d %H:%M:%S")) 
            except:
                print 'invalid time format for setDQDate, must be YYYY-MM-DD HH:MM:SS' 
                sys.exit(1)    
        else:
            print 'Invalid setDQDate ',setDQDate
            print 'setDQDate must be eg. \'CREATED; YYYY-MM-DD HH:MM:SS\' or \'LOADED; YYYY-MM-DD HH:MM:SS\' '
            sys.exit(1)

    for row in rows[1:]:
       elements = row.split('\t')
       flagCpts = [-1,-1,-1,-1]
       if ( int(elements[0].strip()) == -1 or subdetectors == ['P0D'] or subdetectors == ['SMRD'] or subdetectors == ['MAGNET'] or subdetectors == ['INGRID'] ):
           flagCpts[0] = elements[0]
       elif ( subdetectors == ['ECAL'] ):
          flagCpts = databaseUtils.unpackECALFlag(int(elements[0].strip()))
       elif ( subdetectors == ['FGD'] ):
          flagCpts = databaseUtils.unpackFGDFlag(int(elements[0].strip()))
       elif ( subdetectors == ['TPC'] ):
          flagCpts = databaseUtils.unpackTPCFlag(int(elements[0].strip()))

       if( setcreationdate > 0 and int(elements[3]) > int(setcreationdate) ): 
           continue
       if( setloadingdate > 0 and int(elements[4]) > int(setloadingdate) ): 
           continue
       if (int(elements[3]) > int(createtime)):
          createtime = elements[3]
          starttime = elements[1]
          endtime = elements[2]
          flag = flagCpts

    rows1 = dqtable.getRowsWithSubdetectorgtBeginTime(subdetectors, eventtime, int(endtime.strip()))
    newEndTime = int(endtime.strip())
    for row in rows1[1:]:
        elements = row.split('\t')
        if (int(elements[1].strip()) < newEndTime):
            newEndTime = int(elements[1].strip())

    rows2 = dqtable.getRowsWithSubdetectorltEndTime(subdetectors, eventtime, int(starttime.strip()))
    newStartTime = int(starttime.strip())
    for row in rows2[1:]:
       elements = row.split('\t')
       if (int(elements[2].strip()) > newStartTime):
          newStartTime = int(elements[2].strip())

    if (subdetectors == ['P0D'] or subdetectors == ['SMRD'] or subdetectors == ['MAGNET'] or subdetectors == ['INGRID'] ):
        print flag[0], -1, -1, -1, newStartTime, newEndTime, createtime
    elif (subdetectors == ['FGD'] ):
        print flag[0], flag[1], flag[2], -1, newStartTime, newEndTime, createtime
    elif (subdetectors == ['TPC'] or subdetectors == ['ECAL'] ):
        print flag[0], flag[1], flag[2], flag[3], newStartTime, newEndTime, createtime

if __name__ == '__main__':
    queryDQ()