#!/usr/bin/env python2
# Script to query the beam database
#

import getopt
import sys
import os
import time
import commands
import subprocess

try:
    import sqlite
except:
    print 'pysqlite does not exist in the standard library'
    print 'using local version 2.6.3'
    pysqlitePath = sys.path[0] + '/pysqlite-2.6.3/build/lib.linux-i686-2.7'
    sys.path.append(pysqlitePath)
    import pyparsing2 as sqlite

def usage():
    '''Function describing the script usage
    '''
    print "Script to query the beam database"
    print "Usage: queryBeam.py [-h][-v][-d=<database>][-s=<schema>][-b=<begintime>]"
    print "                    [-e=<endtime>]"
    print " "
    print "Options:"
    print " -h, --help                  Prints this help"
    print " -v, --verbose               Prints verbose output"
    print " -d, --database=<database>   The sqlite database file."
    print "                             Default is comet_subrun_info.sqlite"
    print " -s, --schema=<schema>       The database schema file."
    print "                             Default is beam.schema."
    print " -b, --begin=<begintime>     The start time in epoc seconds."
    print "                             If -1 uses the timestamp when the script is run"
    print "                             If 0 or non set, uses the <endtime> -7 days"
    print " -e, --end=<endtime>         The end time in epoc seconds."
    print "                             If -1 uses the timestamp when the script is run"
    print "                             If 0 or non set, uses the <begintime> +7 days"
    print ""
    print "Configuration for data quality: ./queryBeam.py -s beam.schema -e -1 "
    print "i.e. the script runs each 7 days and takes the subruns of the previous 7 days"
    print ""
    print "Output files print out: run #, subrun #, startime('unixepoch','localtime'), endtime('unixepoch','localtime'), trigger_priority, beb_active, beam_status, mag_current_min, mag_current_max, comment from subrun_info "    
    print ""
    print "Criteria are in beam.schema: (trigger_priority&1)=1, mag_current_min > 2900. beam_status is either 0 (beam0...txt files) or !0 0 (beamNE0....txt files). Times are defined as above"
    print ""

class dbSchema:
    def __init__(self, dbSchema):
        fh = file(dbSchema)
        self.lines = fh.readlines()
        fh.close()

    def extractQuery(self, qtype):
        '''Extract the query from the file
        '''
        query = ""
        for line in self.lines:
            if ("#" in line): continue
            cpts = line.split(":")
            if (qtype in cpts[0].strip()):
                query = cpts[1]
                break
        return query

    def queryBeam0(self):
        '''Extract beam 0
        '''
        query = self.extractQuery("queryBeam0")
        return query

    def queryBeamNon0(self):
        '''Extract beam non 0
        '''
        query = self.extractQuery("queryBeamNon0")
        return query
    
    def queryBeam0TE(self):
        '''Extract beam 0 with tend
        '''
        query = self.extractQuery("queryBeam0TE")
        return query

    def queryBeamNon0TE(self):
        '''Extract beam non 0 with tend
        '''
        query = self.extractQuery("queryBeamNon0TE")
        return query



def runQuery(dbFile, schema, beamNEFile, beamFile, 
             beginTime, endTime, verbose):
    '''Function to execute the query
    '''
    # Load in the schema
    dbs = dbSchema(schema)

    # Open the database
    conn = sqlite.connect(dbFile)
    cur = conn.cursor()

    # Run a query for beam 0
    output = None
    if (endTime == 0):
        cur.execute(dbs.queryBeam0(), beginTime)
        output = cur.fetchall()
        conn.commit()
    else: 
        cur.execute(dbs.queryBeam0TE(), beginTime, endTime)
        output = cur.fetchall()
        conn.commit()

    if (len(output) == 0):
        print 'Warning: no items with beam_status = 0 selected from database'

    fh = file(beamFile, "w")
    lastEnd1 = printOutput(output, fh)
    fh.close()


    # Run a query for beam =! 0
    output = None
    if (endTime == 0):
        cur.execute(dbs.queryBeamNon0(), (beginTime,))
        output = cur.fetchall()
        conn.commit()
    else:
        cur.execute(dbs.queryBeamNon0TE(), (beginTime, endTime))
        output = cur.fetchall()
        conn.commit()
 
    if (len(output) == 0):
        print 'Warning: No items with beam_status != 0 selected from database'

    fh = file(beamNEFile, "w")
    lastEnd2 = printOutput(output, fh)
    fh.close()

    conn.close()

    if (lastEnd2 > lastEnd1):
        lastEnd = lastEnd2
    else:
        lastEnd = lastEnd1

    return lastEnd

def printOutput(output, fileh):
    # Print the output
    # output is run, subrun, tstart, tstop, trigger_priority, beb_active,
    # beam_status, mag_current_min, mag_current_max, comment
    lastEndTime = 0
    for line in output:
        oline = "%s %s %s %s %s %s %s %s %s %s" % (line[0], line[1], 
                                                   line[2], line[3], 
                                                   line[4], line[5], 
                                                   line[6], line[7], 
                                                   line[8], line[9])
        fileh.write(oline)
        fileh.write("\n")
        lastEndTime = line[3]
    return lastEndTime

if __name__ == '__main__':

    s0Time = time.time() # current time
    s1Time = time.localtime(s0Time) # convert current time into a tuple (y,m,d,h,m,s,timezone)
    s2Time = time.strftime("%Y-%m-%d::%H:%M:%S", s1Time) # converting to human readable file
    print 'starting cron job: ',s2Time

    verbose = 0
    beginTime = 0
    endTime = 0
    dbFile = '/t2k/dataqual/run/comet_subrun_info.sqlite'
    schema = '/home/irodsdq/cronjobs/beam.schema'

    # setup the environment variables
    # Read in and set the iRODS config

    os.environ["PATH"]="/home/irodsdq/irods/iRODS/clients/icommands/bin:"+os.environ["PATH"]
    os.environ["irodsEnvFile"]="/home/irodsdq/.irods/.irodsEnv"
    os.environ["irodsAuthFileName"]="/home/irodsdq/.irods/.irodsA"
    collection = "/KEK-T2K/home/dataquality/bookkeeping/production"

    # Read in the options
    opts, args = getopt.getopt(sys.argv[1:], 'hvs:d:b:e:', 
                               ['help', 'verbose', 
                                'database', 'schema', 'begin', 'end'])
    for opt, val in opts:
        if (opt == '-h' or opt == '--help'):
            usage()
            sys.exit(0)
        if (opt == '-v' or opt == '--verbose'):
            verbose = 1
        if (opt == '-d' or opt == '--database'):
            dbFile = val.strip()
        if (opt == '-s' or opt == '--schema'):
            schema = val.strip()
        if (opt == '-b' or opt == '--begin'):
            beginTime = val.strip()
            if (beginTime == str(-1)):
                beginTime = int(s0Time)
        if (opt == '-e' or opt == '--end'):
            endTime = val.strip()
            if (endTime == str(-1)):
                endTime = int(s0Time)

    if (not os.path.isfile(schema)):
        print "Error: schema file %s doesn't exist or cannot be read" % schema
        sys.exit(1)

    if (not os.path.isfile(dbFile)):
        print "Error: database file %s doesn't exist or cannot be read" % dbFile
        sys.exit(1)
        
    # if the begintime is 0 then subtract 7 days to the end time
    if (beginTime == 0):
        beginTime = str(int(endTime) - 60*60*24*7)
    sub = 60*60*24*7

    # if the endtime is 0 then add 7 days to the start time
    if (endTime == 0):
        endTime = str(60*60*24*7 + int(beginTime))

    begin1Time = time.localtime(float(beginTime)) # convert current time into a tuple (y,m,d,h,m,s,timezone)
    begin2Time = time.strftime("%Y-%m-%d::%H:%M:%S", begin1Time) # converting to human readable file
    beamFile = "/home/irodsdq/cronjobs/subruns/beam0-%s_%s.txt" % (beginTime, str(begin2Time))
    beamNEFile = "/home/irodsdq/cronjobs/subruns/beamNE0-%s_%s.txt" % (beginTime, str(begin2Time))

    lastEnd = runQuery(dbFile, schema, beamNEFile, beamFile, 
                       beginTime, endTime, verbose)
    
    # output the timestamp as the new starttime
    
    stem = dbFile.split(".")
    stringdb = collection.strip() + "/" + stem[0].split("/")[-1] + "_" + str(s2Time) + ".sqlite" 
    string1 = "iput " + dbFile + " "+ stringdb.strip()
    status1, output1 = commands.getstatusoutput(string1)    
    if(status1 != 0): print string1,' ',str(status1),' ',str(output1)
    
    string2 = "iput " + beamFile + " " +collection.strip()
    status2, output2 = commands.getstatusoutput(string2)    
    if(status2 != 0): print string2,' ',str(status2),' ',str(output2)

    string3 = "iput " + beamNEFile + " " +collection.strip()
    status3, output3 = commands.getstatusoutput(string3)    
    if(status3 != 0): print string3,' ',str(status3),' ',str(output3)
    
    string4 = "iput -f /home/irodsdq/cronjobs/copyIntoIRODSDataBaseJob.log /KEK-T2K/home/dataquality/bookkeeping/copyIntoIRODSDataBaseJob.log "
    status4, output4 = commands.getstatusoutput(string4)
    if(status4 != 0): print string4,' ',str(status4),' ',str(output4)


    # Now store the files in iRODS
    e0Time = time.time() # current time
    e1Time = time.localtime(e0Time) # convert current time into a tuple (y,m,d,h,m,s,timezone)
    e2Time = time.strftime("%Y-%m-%d::%H:%M:%S", e1Time) # converting to human readable file
    print 'ending cron job: ',e2Time