#!/usr/bin/env jython

import os.path
import sys

import stilts
from java.lang import *
from java.sql import *

from xml.etree.ElementTree import ElementTree, Element, tostring

from batch import Batch
from gpc1db import Gpc1Db
from ipptopspsdb import IppToPspsDb
from scratchdb import ScratchDb
from dvoobjects import DvoObjects
from sqlUtility import sqlUtility

import logging.config

'''
ObjectBatch class

This class, a sub-class of Batch, processes individual DVO region file pairs (cpt and cps files). The cpt file contains a list of all objects in that region, the cps file contains the magnitudes and so has a row per filter per object (so, generally, has rows = 5 x number of objects). The Photcode.dat table us used to figure out the number and order of filters as they appear in the cps file.

'''
class ObjectBatch(Batch):

    '''
    Constructor
    '''
    def __init__(self, 
                 logger, 
                 config,
                 skychunk,
                 gpc1Db,
                 ippToPspsDb,
                 scratchDb,
                 dvoID,
                 batchID):

       super(ObjectBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               dvoID,
               batchID,
               "OB", 
               None)

       try:
           self.dvoObjects = DvoObjects(self.logger, self.config, self.skychunk, self.ippToPspsDb, self.scratchDb)
       except:
           self.logger.errorPair("Unable to create instance of", "DvoObjects")
           raise

       # create an output filename, which is {dvoINDEX}.FITS
       self.outputFitsFile = "%08d.FITS" % self.id
       self.outputFitsPath = "%s/%s" % (self.localOutPath, self.outputFitsFile)

       # dump stuff to log
       self.logger.infoPair("DVO INDEX", "%d" % self.id)

    '''
    Overriden from batch base-class. We import from DVO directly for objects
    '''
    def importIppTables(self, columns="*", filter=""):

        self.region = self.scratchDb.getRegionNameFromThisDvoIndex(self.id)
        self.ippToPspsDb.insertObjectMeta(self.batchID, self.region)
        self.dvoObjects.nativeIngestRegion(self.region)

        cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
        if not self.scratchDb.tableExists(cptTableName):
            return False

        return True

    '''
    Applies indexes to the PSPS tables
    '''
    def alterPspsTables(self):

        return True

    '''
    Applies indexes to the IPP tables
    '''
    def indexIppTables(self):

        # since dvopsps is now used, no action is needed here
        # self.logger.infoPair("Creating indexes on", "IPP tables")

        return True

    '''
    Inserts stuff for all mags
    '''
    def updateMeanObjectFromCps(self, cpsTable):

        # list of all filters PSPS is interested in
        # XXX EAM : 2014.07.24 : this list should probably be in a config file somewhere
        interestedFilters = ['g', 'r', 'i', 'z', 'y']
        
        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
    
        # get a count of the available filters
        filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable()

        self.logger.infoPair("Available filters in Photcodes", filters)

        # the 'code' now defines the order in the cps file that the mags are listed for a given filter
        self.logger.infoPair("Adding magnitudes from", "cps table")
        for filter in filters:

            filterID = self.scratchDb.getFilterID(filter[1])

            # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000)
	    # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
            # XXX EAM : 20140724 this manipulation is no longer needed : [grizy]Flags is now 4 byte (was 8 byte!)

            # set the MeanObject fields based largely on dvopsps cps fields:

            # XXX EAM 20140724 : filterCount is meant to match
            # Nsecfilt, but is potentially not determined correctly.
            # use a call to a dvo-native command which knows how to
            # find Nsecfilt (or save in the db with dvopsps)

            # the math below depends on filterCount = Nsecfilt and MeanObject.row being 1 counting but cps being 0 counting?
            # cps.row has a count of MeanObject.row * Nsecfilt + Nfilter 
            #  " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \

            sql = "UPDATE MeanObject JOIN \
                   " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
                   SET \
                    MeanObject." + filter[1] + "QfPerfect      = PSF_QF_PERF_MAX \
                   ,MeanObject." + filter[1] + "MeanPSFMag     = MAG \
                   ,MeanObject." + filter[1] + "MeanPSFMagErr  = MAG_ERR \
                   ,MeanObject." + filter[1] + "MeanPSFMagStd  = MAG_STDEV \
                   ,MeanObject." + filter[1] + "MeanPSFMagMin  = MAG_MIN \
                   ,MeanObject." + filter[1] + "MeanPSFMagMax  = MAG_MAX \
                   ,MeanObject." + filter[1] + "MeanPSFMagNpt  = NUSED \
                   ,MeanObject." + filter[1] + "MeanKronMag    = MAG_KRON \
                   ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
                   ,MeanObject." + filter[1] + "MeanKronMagStd = MAG_KRON_STDEV \
                   ,MeanObject." + filter[1] + "MeanKronMagNpt = NUSED_KRON \
                   ,MeanObject." + filter[1] + "MeanApMag      = MAG_AP \
                   ,MeanObject." + filter[1] + "MeanApMagErr   = MAG_AP_ERR \
                   ,MeanObject." + filter[1] + "MeanApMagStd   = MAG_AP_STDEV \
                   ,MeanObject." + filter[1] + "MeanApMagNpt   = NUSED_AP \
                   ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) "


            try: self.scratchDb.execute(sql)
            except:
                self.logger.errorPair("failed update MeanObject", sql)
                raise

        # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12)
        # XXX EAM 20140724 : keep this cut?
        cut = 0.5
        self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut)
        for filter in filters:

            sql = "UPDATE MeanObject \
                   SET " + filter[1] + "MeanPSFMagErr = null \
                   WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
            self.scratchDb.execute(sql)

    '''
    Inserts stuff for all mags
    '''
    def updateObjectThinFromCps(self, cpsTable):

        # list of all filters PSPS is interested in
        interestedFilters = ['g', 'r', 'i', 'z', 'y']
        
        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
    
        # get a count of the available filters
        filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable()
        # filterCount = len(filters)

        self.logger.infoPair("Available filters in Photcodes", filters)

        # the 'code' now defines the order in the cps file that the mags are listed for a given filter
        self.logger.infoPair("Adding magnitudes from", "cps table")
        for filter in filters:

            filterID = self.scratchDb.getFilterID(filter[1])

            # XXX EAM 20140724 : this is quite awkward, add a objRow and ncode value to mysql db table?
            # sqlLine = sqlUtility()

            sql  = "UPDATE ObjectThin JOIN "
            sql += cpsTable + " AS cps "
            sql += "ON (cps.row = (ObjectThin.row * "
            sql += str(filterCount) + ")-(" 
            sql += str(filterCount) + " - " 
            sql += str(filter[0])
            sql += ")) "

            sql += "SET ObjectThin.n" + filter[1] + " = NCODE, "
            sql += "ObjectThin.nStackDetections = ObjectThin.nStackDetections + cps.NSTACK_DET"
            self.logger.info(sql)
            self.scratchDb.execute(sql)

        # XXX this does not seem like a good thing to leave in SQL
        self.logger.infoPair("Calculating nDetections from", "n[filters]")
        for filter in filters:
            # now do a sum of n[filters], but do not include the ones with -999
            sql  = "UPDATE ObjectThin "
            sql += "SET nDetections = nDetections + n" + filter[1]
            sql += " WHERE n" + filter[1] + " != -999"
            self.scratchDb.execute(sql)

    '''
    give objectName to objectThin
    XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps?
    '''
    def updateObjName(self):
    ##this is a 4 part update:
        self.logger.infoPair("updating objName", "using stacks")
    ## if we have a stackra and dec, use stackra and dec , dec >=0

        sql = "update ObjectThin set objName =  concat('PS1.2 J', \
        lpad(floor(raStack/15.),2,'0'), \
        lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \
        lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \
        '+', \
        lpad(floor(decStack),2,'0'), \
        lpad((decStack-(floor(decStack)))*60. ,2,'0'), \
        lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \
        ) where raStack > -999 and decStack > -999 and decStack >= 0"
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("failed sql", sql)
            return False


    ## if we have a stackra and dec, use stackra and dec, dec < 0

        sql = "update ObjectThin set objName =  concat('PS1.2 J', \
        lpad(floor(raStack/15.),2,'0'), \
        lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \
        lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \
        '-', \
        lpad(floor(decStack),2,'0'), \
        lpad((decStack-(floor(decStack)))*60. ,2,'0'), \
        lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \
        ) where raStack > -999 and decStack > -999 and decStack <= 0"
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("failed sql", sql)
            return False
        self.logger.infoPair("updating objName", "using means")
    ## use mean ra and dec (dec >= 0)
        sql = "update ObjectThin set objName =  concat('PS1.2 J', \
        lpad(floor(raMean/15.),2,'0'), \
        lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \
        lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \
        '+', \
        lpad(floor(decMean),2,'0'), \
        lpad((decMean-(floor(decMean)))*60. ,2,'0'), \
        lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \
        ) where raMean > -999 and decMean > -999 and decMean >= 0"
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("failed sql", sql)
            return False
 
    ## use mean ra and dec (dec < 0)

        sql = "update ObjectThin set objName =  concat('PS1.2 J', \
        lpad(floor(raMean/15.),2,'0'), \
        lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \
        lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \
        '-', \
        lpad(floor(decMean),2,'0'), \
        lpad((decMean-(floor(decMean)))*60. ,2,'0'), \
        lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \
        ) where raMean > -999 and decMean > -999 and decMean < 0"

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("failed sql", sql)
            return False

    '''
    Populates the Object table
    '''
    def populateObjectThinTable(self):

        cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
        cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps")

        if False:
            # XXX EAM 20140724 : this is probably wrong : flux measurements can be 0.0 or negative: please review
            self.logger.infoPair("setting to null  > 1e-38 and < 1e-38 in", "cps FLUX_KRON_ERR")
            sql = "UPDATE " + cpsTableName + " set FLUX_KRON_ERR = NULL where FLUX_KRON_ERR < 1e-37 AND FLUX_KRON_ERR > -1e-37 "
            self.exitProgram("review this code" + sql)
      
            try:
                self.scratchDb.execute(sql)
            except:
                self.logger.errorPair("Couldn't squash out of range stuff", sql)
                return False

            self.logger.infoPair("setting to null  > 1e-38 and < 1e-38 in", "cps FLUX_KRON")
            sql = "UPDATE " + cpsTableName + " set FLUX_KRON = NULL where FLUX_KRON < 1e-37 AND FLUX_KRON > -1e-37 "
      
            try:
                self.scratchDb.execute(sql)
            except:
                self.logger.errorPair("Couldn't squash out of range stuff", sql)
                return False

        
        self.logger.info("Populating ThinObject")
        self.logger.info("Inserting objects from cpt file")

        # note "dec" is a reserved word in MySQL
        # XXX EAM 20140724 : do not use INGORE unless we discover unavoidable problems...
        # INSERT IGNORE INTO ObjectThin

        sqlLine = sqlUtility("INSERT INTO ObjectThin (")

        sqlLine.group("objID",           "EXT_ID") 
        sqlLine.group("ippObjID",        "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly
        sqlLine.group("surveyID",        "'" + str(self.surveyID) + "'")
        sqlLine.group("randomID",        "FLOOR(RAND()*9223372036854775807)") # XXX where does this number come from??
        sqlLine.group("batchID",         "'" + str(self.batchID) + "'")
        sqlLine.group("dvoRegionID",     "CAT_ID") 
        sqlLine.group("tessID",          "TESS_ID") 
        sqlLine.group("projectionID",    "PROJECTION_ID") 
        sqlLine.group("skycellID",       "SKYCELL_ID") 
        sqlLine.group("dataRelease",     "'" + str(self.skychunk.dataRelease) + "'")
        sqlLine.group("objInfoFlag",     "FLAGS") 
        sqlLine.group("qualityFlag",     "FLAGS >> 24 & 0xFF")
        sqlLine.group("consistencyFlag", "'0'") 
        sqlLine.group("raStack",         "RA_STK")
        sqlLine.group("decStack",        "DEC_STK") 
        sqlLine.group("raStackErr",      "RA_STK_ERR") 
        sqlLine.group("decStackErr",     "DEC_STK_ERR")
        sqlLine.group("raMean",          "RA_MEAN")
        sqlLine.group("decMean",         "DEC_MEAN")
        sqlLine.group("raMeanErr",       "RA_ERR")
        sqlLine.group("decMeanErr",      "DEC_ERR")
        sqlLine.group("posMeanChisq",    "CHISQ_POS")
        sqlLine.group("nStackObjectRows", "'0'") # XXX I need to add / define this in dvopsps
        sqlLine.group("nStackDetections", "'0'")
        sqlLine.group("nDetections",      "'0'")
        sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName)

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't populate Object table", sql)
            return False
 
        # add row count columns so we can perform joins to get colors
        self.logger.infoPair("Adding 'row' columns to", "Object and cps tables")
        self.scratchDb.addRowCountColumn("ObjectThin", "row")
        self.scratchDb.addRowCountColumn(cpsTableName, "row")

##        self.insertMeanMags(cpsTableName)
## this is the new version
        self.logger.infoPair("update objName for ","objectThin")
        self.updateObjName()

        self.logger.infoPair("update ObjectThin from ","cps table")

        self.updateObjectThinFromCps(cpsTableName)

        # XXX EAM 20140724 : is this necessary??
        #objects can have out of range ra dec in dvo - need to find and kill them at the end

        self.logger.infoPair("Determining", "ra/dec range")

        raMin = self.scratchDb.getFromdvoSkyTable("R_MIN",self.region)
        raMax = self.scratchDb.getFromdvoSkyTable("R_MAX",self.region)
        decMin = self.scratchDb.getFromdvoSkyTable("D_MIN",self.region)
        decMax = self.scratchDb.getFromdvoSkyTable("D_MAX",self.region)

        self.logger.infoPair("R_MIN", raMin)
        self.logger.infoPair("R_MAX", raMax)
        self.logger.infoPair("D_MIN", decMin)
        self.logger.infoPair("D_MAX", decMax)
        #count out of range

        sql = "SELECT count(*) FROM ObjectThin where \
              ObjectThin.decMean > " + str(decMax) + " \
              or ObjectThin.decMean < " + str(decMin) + " \
              or ObjectThin.raMean > " + str(raMax) + " \
              or ObjectThin.raMean < " + str(raMin)       
    
        rs = self.scratchDb.executeQuery(sql)
        rs.first()
        nToDelete = rs.getInt(1)
        
        #delete out of range
        
 
        sql = "DELETE FROM ObjectThin where \
              ObjectThin.decMean > " + str(decMax) + " or \
              ObjectThin.decMean < " + str(decMin) + " or \
              ObjectThin.raMean > " + str(raMax) + " or \
              ObjectThin.raMean < " + str(raMin) 
        self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range")

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't cull outsiders from ObjectThin table", sql)
            return False

        self.logger.infoPair("Dropping row column from", "ObjectThin table")
        self.scratchDb.dropColumn("ObjectThin", "row")
        ##self.logger.infoPair("Purging from scratch Db", self.region + " region")

        ##Don't do this till after MeanObject
        ##self.dvoObjects.purgeRegion(self.region)

        self.setMinMaxObjID(["ObjectThin"])

        return True

    '''
    Populates the ObjectCalColor table
    '''
    def populateMeanObjectTable(self):
        cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
        cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps")
        self.logger.infoPair("Populating MeanObject from ", "ObjectThin")

        sql = "INSERT INTO MeanObject ( objID ) SELECT objID FROM ObjectThin"
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't populate MeanObject table", sql)
            return False

        self.logger.infoPair("Adding 'row' columns to", "MeanObject table")
        self.scratchDb.addRowCountColumn("MeanObject", "row")
 

        ##self.scratchDb.addRowCountColumn(cpsTableName, "row")
        self.logger.infoPair("update MeanObjects from ","cps table")
        self.updateMeanObjectFromCps(cpsTableName)

        self.logger.infoPair("Dropping row column from", "MeanObject table")
        self.scratchDb.dropColumn("MeanObject", "row")

        return True

    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTables(self):

        if not self.populateObjectThinTable(): return False
        #if not self.populateObjectCalColorTable(): return False
        if not self.populateMeanObjectTable(): return False

        # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number
        self.logger.infoPair("Forcing uniqueness on", "objID in ObjectThin table")
        rowCountBefore = self.scratchDb.getRowCount("ObjectThin")

        # XXX EAM : note that in mysql versions later than 5.1, this fails
        # unless the following is called first: 
        # set session old_alter_table=1
        # follow the command with 
        # set session old_alter_table=0
        # OF COURSE, this fails for mysql version < 5.5...
        if self.scratchDb.version > 5.1:
            self.scratchDb.execute("set session old_alter_table=1")
            
        self.scratchDb.execute("ALTER IGNORE TABLE ObjectThin ADD UNIQUE INDEX(objID)")
        if self.scratchDb.version > 5.1:
            self.scratchDb.execute("set session old_alter_table=0")

        rowCountAfter = self.scratchDb.getRowCount("ObjectThin")
        self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))

        self.logger.infoPair("Forcing uniqueness on", "objID in MeanObject table")
        rowCountBefore = self.scratchDb.getRowCount("MeanObject")

        # XXX EAM : note that in mysql versions later than 5.1, this fails
        # unless the following is called first: 
        # set session old_alter_table=1
        # follow the command with 
        # set session old_alter_table=0
        # OF COURSE, this fails for mysql version < 5.5...
        if self.scratchDb.version > 5.1:
            self.scratchDb.execute("set session old_alter_table=1")
            
        self.scratchDb.execute("ALTER IGNORE TABLE MeanObject ADD UNIQUE INDEX(objID)")
        if self.scratchDb.version > 5.1:
            self.scratchDb.execute("set session old_alter_table=0")

        rowCountAfter = self.scratchDb.getRowCount("MeanObject")
        self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))

        self.dvoObjects.purgeRegion(self.region)

        #this is abuse of something but this is how I get the object batches to crash to further investigate them
        
#        rowCountAfter = self.scratchDb.getRowCount("Object")
        return True
#        return False
