#!/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, self.gpc1Db)
       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

    '''
    Updates table and generates pspsuniqueids
    '''
    
    def updatePspsUniqueIDs(self):
 #       sql = "UPDATE ObjectThin join (select @r:=@r+1 rownum, objID from \
 #       (select @r:=0) r, ObjectThin t) as foo using (objID) set \
 #       uniquePspsOBid = ((" +str(self.batchID) +"*1000000000 ) + rownum)";

        sql = "UPDATE ObjectThin set uniquePspsOBid = (("+str(self.batchID)+"*1000000000 ) + row)"
        try: self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql',sql)
            raise
        

    
    '''
    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
            # both MeanObject.row and cps.row being 1 counting, and
            # filter[0] also being 1 counting

            # cps.row - 1 = (cpt.row - 1)*Nsecfilt + (filter[0] - 1)
            # cps.row     = cpt.row * Nsecfilt - (Nsecfilt - filter[0])
            # cps.row     = cpt.row * Nsecfilt - Nsecfilt + filter[0]
            # [note that the sql below has parenthesis around (Nsecfilt - 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          = FLAGS "

            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)
            try: self.scratchDb.execute(sql)
            except:
                self.logger.infoPair("failed sql",sql)
                raise
    '''
    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)
            try: self.scratchDb.execute(sql)
            except:
                self.logger.InfoPair("failed sql",sql)
                raise

        # 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"
            try: self.scratchDb.execute(sql)
            except:
                self.logger.infoPair("failed sql",sql)
                raise
    '''
    give objectName to objectThin
    XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps?
    '''
    def updateObjName(self):
        self.logger.info("Names are now populated with the populateObjectThinTable call")

        return True
    '''
    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)
                raise
        

            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)
                raise

        
        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",        "RAND("+str(self.batchID)+")") # 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("processingVersion",     "'" + str(self.skychunk.processingVersion) + "'")
        sqlLine.group("objInfoFlag",     "FLAGS") 
        sqlLine.group("qualityFlag",     "FLAGS >> 23 & 0xFF")
        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("objName",         "PSO_NAME")
        sqlLine.group("objNameHMS",      "PSX_NAME")
        sqlLine.group("epochMean",       "EPOCH_MEAN")
        sqlLine.group("raMeanErr",       "RA_ERR")
        sqlLine.group("decMeanErr",      "DEC_ERR")
        sqlLine.group("posMeanChisq",    "CHISQ_POS")
        sqlLine.group("nStackObjectRows", "'-999'") # XXX I need to add / define this in dvopsps # HAF set to -999 as required for pv3
        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)

        if False:
            # this chunk of code deletes objects which are out of ra,dec range for the table.
            # this was a problem in an early version of DVO for cases where the astrometry went insane.
            # this causes problems for the ra = 0,360 boundary (the test below does not handle that situation)
            # and the restrictions below are poorly defined for the regions near the pole.
            
            # in any case, ObjectThin needs to maintain the same order
            # as the cpt table until MeanObjects have been created or
            # the join to the cps table will fail

            # 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) + " + .0033) or \
                  ObjectThin.decMean < (" + str(decMin) + " - .0033) or \
                  ObjectThin.raMean > (" + str(raMax) + " + .0033) or \
                  ObjectThin.raMean < (" + str(raMin) + " - .0033)" 
            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)
                raise

        self.logger.infoPair("updatePspsUniqueIDs","start")
        self.updatePspsUniqueIDs()
        self.logger.infoPair("updatePspsUniqueIDs","end")

        self.logger.infoPair("Dropping row column from", "ObjectThin table")
        self.scratchDb.dropColumn("ObjectThin", "row")
        self.logger.infoPair("Dropped row column", "objectThin")

        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.objid and uniquePspsOBid from ", "ObjectThin")

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

        self.logger.infoPair("Adding 'row' columns to", "MeanObject table")
        self.scratchDb.addRowCountColumn("MeanObject", "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.populateMeanObjectTable(): return False

        # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number

        ### XXX the code below first removes duplicate objID entries
        ### from ObjectThin, then does the same for MeanObject.  This
        ### is a big problem: we have no guarantee that the surviving
        ### rows are the correct matched rows.

        ### force objID uniqueness on *** ObjectThin ***
        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))

        ### force objID uniqueness on *** MeanObject ***
        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))

        # delete the cpt, cps tables from the scratch mysql (or we will run out of space)
        self.dvoObjects.purgeRegion(self.region)

        return True
