#!/usr/bin/env jython

import os.path
import sys
import glob
import time
import math
import stilts
import re
from java.lang import *
from java.sql import *

from java.lang import Math
from org.apache.commons.math.special import Erf

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 sqlUtility import sqlUtility

import logging.config

'''
DiffBatch class

This class, a sub-class of Batch, processes single exposures in the form of IPP smf files

'''
class DiffBatch(Batch):

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

       super(DiffBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               stageid,
               batchID,
               "DF", 
               None)

       # get diff meta data
       # if error about catID you are usings an old ipptopsps_scratch db

       # initialize list variables here


       self.header ={}
       self.diffSkyFileIDs = {}
       self.ippDiffID = {}
       self.filterName = {}
       self.filterID = {}
       self.photoCalID = {}
       self.difftypeID = {}
       self.posImageID = {}
       self.negImageID = {}
       self.skycellName = {}
       self.analysisVer = {}
       self.md5sum = {}
       self.expTime = {}
       self.zpImage = {}
       self.magref = {}
       self.nInjected = {}
       self.obsTime = {}
       self.posFits = {}
       self.negFits = {}
       self.tessID = {}
       self.tessName = {}
       self.skycellID = {}
       self.projectionID ={}
       self.expID ={}
       self.psfModelID = {}
       self.psfModelName = {}
       self.psfFWHM = {}
       self.psfFwhmMajor = {}
       self.psfFwhmMinor = {}
       
       # number is special - it is simply a list of diff skyfile IDs, used for
       # looping over things
       self.stageid = stageid
       self.number = {}
       num = 0 

       # grab list of diffs from stage id

       self.diffSkyFileIDs = gpc1Db.getListOfDiffSkyFileIdsForDiffBatch(skychunk.dvoLabel, stageid)

       # loop over diffs

       for diffSkyFileID in self.diffSkyFileIDs:
           self.number[num]= diffSkyFileID

           self.logger.infoPair("getting diff" ," metadata")
           self.logger.infoPair("for diff skyfile id ", str(diffSkyFileID))

           meta = self.gpc1Db.getDiffStageMeta(int(diffSkyFileID))

           # EAM 20151105 : I have changed the function above to return "meta" as a dictionary 
           if not meta:
               self.logger.errorPair("Could not get", "diff metadata")
               raise

           self.ippDiffID[num]  = stageid

           self.filterName[num]  = meta['filter'];
           self.difftypeID[num]  = meta['diff_mode'];
           self.posImageID[num]  = meta['warp1'];
           self.negImageID[num]  = meta['stack2'];
           self.skycellName[num] = meta['skycell_id'];
           self.analysisVer[num] = meta['software_ver'];
           self.expTime[num]     = meta['exp_time'];
           self.expID[num]       = meta['exp_id'];
           self.tessName[num]    = meta['tess_id'];

	   ## MEH -- should be able to use diff header now for mjd (see below)
           # self.obsTime[num] = meta['obsTime'];

           # tessName could also come from the header...
           # tessName = None
           # tessName = self.getKeyValue(self.header[num], 'TESS_ID')

           self.logger.infoPair("loading diff stage cmf", diffSkyFileID)

           # look up the path base once
           pathBase = gpc1Db.getDiffStagePathBase(int(diffSkyFileID))

           # select the two (possible) cmf files (.cmf and .inv.cmf)
           self.posFits[num] = gpc1Db.getDiffStageCmfForPathBase(pathBase)
           self.negFits[num] = gpc1Db.getDiffStageCmfForPathBase(pathBase + ".inv")

           self.logger.infoPair("loading diff stage header", diffSkyFileID)
           self.header[num] = self.posFits[num].getPrimaryHeader()

           # obsTime is the center of the exposure.  MJD-OBS is the start in days, expTime is the length in seconds
           self.obsTime[num] = float(self.header[num]['MJD-OBS']) + (float(self.expTime[num]) / 172800.0)

           # grap zpImage from the PHU header
           self.zpImage[num] = self.getKeyFloat(self.header[num],"%.8f","FPA.ZP")

           deteffHeader = self.posFits[num].findAndReadHeader("SkyChip.deteff", self.config.test)
           if not deteffHeader:
               if self.config.test: self.logger.errorPair("No header found for Skychip.deteff for", filter)
               return False

           # grab the detection threshold reference magnitude
           self.magref[num] = self.getKeyFloat(deteffHeader, "%.8f", "DETEFF.MAGREF")

           self.nInjected[num] = self.getKeyInt(deteffHeader, 0, 'DETEFF.NUM')

           # self.logger.infoPair("Found zeropoint and set magref", str(magref))

           tessName = self.tessName[num]
           self.logger.infoPair("TessName ", tessName)
           self.tessID[num] = self.scratchDb.getTessID(tessName)
           self.logger.infoPair("TessName ",tessName)

           if self.tessName[num] == 'RINGS.V3':
               # skycell is, eg "skycell.1133.081"
               #                 0123456789012345
               self.projectionID[num] = self.skycellName[num][8:12]
               self.skycellID[num] = self.skycellName[num][13:] 
           else:
               # skycell is, eg "skycell.081"
               #                 01234567890
               self.projectionID[num] = 0
               self.skycellID[num] = self.skycellName[num][8:]
               
           if self.projectionID[num] == "": 
               self.projectionID[num] = -1
           if self.skycellID[num] == "": 
               self.skycellID[num] = -1

           ## MEH md5sum test
           self.md5sum[num] = self.posFits[num].getMd5sum()

           ## MEH adding psfFWHM info from header
           ## -- XXX hard-wired platescale : 0.25
           pltscale = 0.25
           self.psfFwhmMajor[num] = self.getKeyFloat(self.header[num], "%.8f", 'FWHM_MAJ')
           if (self.psfFwhmMajor[num] != "NULL"): self.psfFwhmMajor[num] *= pltscale

           self.psfFwhmMinor[num] = self.getKeyFloat(self.header[num], "%.8f", 'FWHM_MIN')
           if (self.psfFwhmMinor[num] != "NULL"): self.psfFwhmMinor[num] *= pltscale

           self.psfFWHM[num] = "NULL"
           if (self.psfFwhmMajor[num] != "NULL") and (self.psfFwhmMinor[num] != "NULL"):
               self.psfFWHM[num] = 0.5*(self.psfFwhmMajor[num] + self.psfFwhmMinor[num])

           self.psfModelName[num] = self.getKeyValue(self.header[num], 'PSFMODEL')
           self.psfModelID[num]   = self.scratchDb.getFitModelID(self.psfModelName[num])

           # drop the existing tables    

           self.dropTableVerbose("DiffMeta_"+str(num))
           self.dropTableVerbose("DiffToImage_"+str(num))
           self.dropTableVerbose("DiffDetection_"+str(num))
           self.dropTableVerbose("DiffDetEffMeta_"+str(num))

           # if not self.analysisVer[num]: self.analysisVer[num] = -999

           self.logger.infoPair("getPhotoCalID",diffSkyFileID)
           self.photoCalID[num] = str(self.scratchDb.getPhotoCalID(int(diffSkyFileID)))

           self.logger.infoPair("get filterID", diffSkyFileID)
           self.filterID[num] = self.scratchDb.getFilterID(self.filterName[num][0])

           self.logger.infoPair("insertdiffmeta", diffSkyFileID)
           self.logger.infoPair("insertdiffmeta", self.filterName[num][0])
           self.logger.infoPair("insertdiffmeta", self.difftypeID[num])
           self.ippToPspsDb.insertDiffMeta(self.batchID, self.filterName[num][0], self.difftypeID[num])
      
           num = num + 1

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

       self.processingVersion = skychunk.processingVersion;

       # dump stuff to log
       self.logger.infoPair("stage_id", "%d" % self.stageid)

    '''
    Populates the FrameMeta table, mainly from dictionary values found in IPP FITS header
    '''
    def populateDiffMeta(self,num):
        diffSkyFileID = self.number[num]
        self.logger.infoPair("Proccesing table", "DiffMeta_"+str(num))

        header = self.header[num]

        # Convert detectionThreshold to appropriate magnitudes
        # we have read these values from the appropriate locations in init()
        zpImage = "NULL" if (self.zpImage[num] == "NULL") else float(self.zpImage[num])
        expTime = "NULL" if (self.expTime[num] == "NULL") else float(self.expTime[num])
        magref  = "NULL" if (self.magref[num]  == "NULL") else float(self.magref[num])

        isNull = (zpImage == "NULL") or (expTime == "NULL") or (magref == "NULL")
        detectionThreshold = "NULL" if isNull else magref + zpImage + 2.5 * math.log10(expTime)

        sql = "CREATE TABLE DiffMeta_"+str(num)+" like DiffMeta"
        try: self.scratchDb.execute(sql)
        except: raise

        sqlLine = sqlUtility("INSERT INTO DiffMeta_"+str(num)+" (")

        sqlLine.group("diffImageID",           str(diffSkyFileID));
        sqlLine.group("difftypeID",            self.difftypeID[num]);
        sqlLine.group("frameID",               self.expID[num]);
        sqlLine.group("posImageID",            self.posImageID[num]);
        sqlLine.group("negImageID",            self.negImageID[num]);
        sqlLine.group("ippDiffID",             self.ippDiffID[num]);
	sqlLine.group("tessID",                str(self.tessID[num]))
	sqlLine.group("projectionID",          str(self.projectionID[num]))
        sqlLine.group("skycellID",             str(self.skycellID[num]))
        sqlLine.group("photoCalID",            self.photoCalID[num]);
        sqlLine.group("analysisVer",           str(self.analysisVer[num]));
	sqlLine.group("md5sum",                str(self.md5sum[num]))
        sqlLine.group("detectionThreshold",    detectionThreshold)
        sqlLine.group("expTime",               self.expTime[num]);
        sqlLine.group("psfModelID",            self.psfModelID[num])
        sqlLine.group("psfFWHM",               self.psfFWHM[num])
        sqlLine.group("psfWidMajor",           self.psfFwhmMajor[num])
        sqlLine.group("psfWidMinor",           self.psfFwhmMinor[num])

        kernel = self.getKeyValue(header, 'PPSUB.KERNEL')
        kernel = kernel.replace(",",";")
        
        sqlLine.group("kernel",                str(kernel))       
        sqlLine.group("mode",                  self.getKeyValue(header, 'SUBTRACTION.MODE'))
        sqlLine.group("psfTheta",              self.getKeyFloat(header, "%.8f", 'ANGLE'))
        sqlLine.group("numStamps",             self.getKeyValue(header, 'SUBTRACTION.STAMPS'))
        sqlLine.group("stampDevMean",          self.getKeyFloat(header, "%.8e", 'SUBTRACTION.DEV.MEAN'))
        sqlLine.group("stampDevRMS",           self.getKeyFloat(header, "%.8e", 'SUBTRACTION.DEV.RMS'))
        sqlLine.group("normalization",         self.getKeyFloat(header, "%.8e", 'SUBTRACTION.NORM'))
        sqlLine.group("convolveMax",           self.getKeyFloat(header, "%.8e", 'SUBTRACTION.CONVOL.MAX'))
        sqlLine.group("deconvolveMax",         self.getKeyFloat(header, "%.8e", 'SUBTRACTION.DECONV.MAX'))
        sqlLine.group("ctype1",                self.getKeyValue(header, 'CTYPE1'));
        sqlLine.group("ctype2",                self.getKeyValue(header, 'CTYPE2'));
        sqlLine.group("crval1",                self.getKeyFloat(header, "%.8f", 'CRVAL1'));
        sqlLine.group("crval2",                self.getKeyFloat(header, "%.8f", 'CRVAL2'));
        sqlLine.group("crpix1",                self.getKeyFloat(header, "%.8f", 'CRPIX1'));
        sqlLine.group("crpix2",                self.getKeyFloat(header, "%.8f", 'CRPIX2'));
        sqlLine.group("cdelt1",                self.getKeyFloat(header, "%.8e", 'CDELT1'));
        sqlLine.group("cdelt2",                self.getKeyFloat(header, "%.8e", 'CDELT2'));
        sqlLine.group("pc001001",              self.getKeyFloat(header, "%.8e", 'PC001001'));
        sqlLine.group("pc001002",              self.getKeyFloat(header, "%.8e", 'PC001002'));
        sqlLine.group("pc002001",              self.getKeyFloat(header, "%.8e", 'PC002001'));
        sqlLine.group("pc002002",              self.getKeyFloat(header, "%.8e", 'PC002002'));

        sql = sqlLine.make(") VALUES ( ", ")")

        try: self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql: ', sql)
            raise
        self.logger.infoPair("DiffMeta_"+str(num),"final stuff")
        self.scratchDb.updateAllRows("DiffMeta_"+str(num), "batchID", str(self.batchID))
        self.scratchDb.updateAllRows("DiffMeta_"+str(num), "surveyID", str(self.surveyID))
        self.scratchDb.updateFilterID("DiffMeta_"+str(num), self.filterName[num][0])
        self.scratchDb.updateAllRows("DiffMeta_"+str(num), "processingVersion", str(self.skychunk.processingVersion))
        self.logger.infoPair("DiffMeta_"+str(num),"done")

    '''
    Populates the Diff table for this OTA
    '''
    def populateDiffDetectionTable(self, num, isPosFile):

        # XXX we no longer delete detections with funny mags (eg PSF_INST_MAG < -17.5)
        # for examples of code which do this, go to r35097 or earlier

        if isPosFile:
            ippTableName =  "SkyChip_psf_"+str(num)
        else:
            ippTableName =  "SkyChip_psf_inv_"+str(num)

        pspsTableName = "DiffDetection_" + str(num)
        diffSkyFileID = self.number[num]

        if isPosFile:
            sql = "CREATE TABLE DiffDetection_" + str(num) + " like DiffDetection"
            self.scratchDb.execute(sql)

        expTimeString = str(self.expTime[num])
        if isPosFile:
            fluxScaleStr = "1"
            detIDoffset = 0
        else:
            fluxScaleStr = "-1"
            detIDoffset = self.posImageDetCount[num]

        if isPosFile or self.haveNegFits[num]:

            # insert all detections into table : do NOT include the IGNORE unless we have to
            # sqlLine = sqlUtility("INSERT IGNORE INTO " + pspsTableName + " (")
            sqlLine = sqlUtility("INSERT INTO " + pspsTableName + " (")
            
            # XXX WARNING: need to apply platescale to convert sizes to arcsec
            # XXX apFluxF or apFluxNpix + apFluxRadius?
            
            sqlLine.group("ippDetectID",     "IPP_IDET + " + str(detIDoffset))
            sqlLine.group("diffImageID",     str(diffSkyFileID))
            sqlLine.group("fromPosImage",    str(isPosFile))
            
            sqlLine.group("randomDiffID",     "RAND("+str(self.batchID)+")")
            sqlLine.group("filterID",        str(self.filterID[num]))
            sqlLine.group("surveyID",        str(self.surveyID))
            sqlLine.group("tessID",          str(self.tessID[num]))
            sqlLine.group("projectionID",    str(self.projectionID[num]))
            sqlLine.group("skycellID",       str(self.skycellID[num]))
            sqlLine.group("expTime",         str(self.expTime[num]))
            sqlLine.group("obsTime",         str(self.obsTime[num]))
            sqlLine.group("xPos",            "X_PSF")
            sqlLine.group("yPos",            "Y_PSF")
            sqlLine.group("xPosErr",         "X_PSF_SIG")
            sqlLine.group("yPosErr",         "Y_PSF_SIG")
            if (self.id >= 982483):
                sqlLine.group("pltScale",        "PLTSCALE")
                sqlLine.group("posAngle",        "POSANGLE")
                sqlLine.group("raErr",           "X_PSF_SIG * abs(PLTSCALE)")
                sqlLine.group("decErr",          "Y_PSF_SIG * abs(PLTSCALE)")
            else:
                sqlLine.group("pltScale",        "0.25")
                # sqlLine.group("posAngle",        "-999")
                sqlLine.group("raErr",           "X_PSF_SIG * 0.25")
                sqlLine.group("decErr",          "Y_PSF_SIG * 0.25")

            sqlLine.group("DpsfFlux",         "PSF_INST_FLUX / " + expTimeString + " * " + fluxScaleStr)
            sqlLine.group("DpsfFluxErr",      "PSF_INST_FLUX_SIG / " + expTimeString)
                
            if self.havePsfFwhm[num]:
                print "I have PSF_FWHM_MAJ, setting a value"
                sqlLine.group("DpsfMajorFWHM",    "PSF_FWHM_MAJ * abs(PLTSCALE)")
                sqlLine.group("DpsfMinorFWHM",    "PSF_FWHM_MIN * abs(PLTSCALE)")
                sqlLine.group("DpsfCore",         "PSF_CORE")
                    
            if self.haveSrcPos[num]:
                print "I have SRC_CHIP_X, setting a value"
                sqlLine.group("xPosChip",          "SRC_CHIP_X")
                sqlLine.group("yPosChip",          "SRC_CHIP_Y")
                sqlLine.group("ccdID"   ,          "SRC_CHIP_NUM")
                    
            sqlLine.group("DpsfTheta",        "PSF_THETA")
            sqlLine.group("DpsfQf",           "PSF_QF")
            sqlLine.group("DpsfQfPerfect",    "PSF_QF_PERFECT")
            sqlLine.group("DpsfChiSq",        "PSF_CHISQ / PSF_NDOF")
            sqlLine.group("DpsfLikelihood",   "psfLikelihood(EXT_NSIGMA)")
            sqlLine.group("DmomentXX",        "MOMENTS_XX * PLTSCALE * PLTSCALE")
            sqlLine.group("DmomentXY",        "MOMENTS_XY * PLTSCALE * PLTSCALE")
            sqlLine.group("DmomentYY",        "MOMENTS_YY * PLTSCALE * PLTSCALE")
            sqlLine.group("DmomentR1",        "MOMENTS_R1 * abs(PLTSCALE)")
            sqlLine.group("DmomentRH",        "MOMENTS_RH * sqrt(abs(PLTSCALE))")
            if (self.id >= 982483):
                sqlLine.group("DapFlux",          "AP_FLUX / " + expTimeString + " * " + fluxScaleStr)
                sqlLine.group("DapFluxErr",       "AP_FLUX_SIG / " + expTimeString)
                
            if self.haveApNpix[num]:
                sqlLine.group("DapFillF", "AP_NPIX / (3.14159265359 * AP_MAG_RADIUS * AP_MAG_RADIUS)")

            sqlLine.group("DkronFlux",        "KRON_FLUX / " + expTimeString + " * " + fluxScaleStr)
            sqlLine.group("DkronFluxErr",     "KRON_FLUX_ERR / " + expTimeString)
            sqlLine.group("DkronRad",         "MOMENTS_R1 * 2.5 * abs(PLTSCALE)")
            sqlLine.group("diffNPos",         "DIFF_NPOS")
            sqlLine.group("diffFPosRatio",    "DIFF_FRATIO")
            sqlLine.group("diffNPosRatio",    "DIFF_NRATIO_BAD")
            sqlLine.group("diffNPosMask",     "DIFF_NRATIO_MASK")
            sqlLine.group("diffNPosAll",      "DIFF_NRATIO_ALL")
            sqlLine.group("diffPosdist",      "DIFF_R_P")
            sqlLine.group("diffNegdist",      "DIFF_R_M")
            sqlLine.group("diffPosSN",        "DIFF_SN_P")
            sqlLine.group("diffNegSN",        "DIFF_SN_M")
            sqlLine.group("Dsky",             "SKY / PLTSCALE / PLTSCALE / " + expTimeString)
            sqlLine.group("DskyErr",          "SKY_SIGMA / PLTSCALE / PLTSCALE / " + expTimeString)
            sqlLine.group("DinfoFlag",        "FLAGS")
            sqlLine.group("DinfoFlag2",       "FLAGS2")

            sqlLine.group("processingVersion",     str(self.skychunk.processingVersion))                  
            
            sql = sqlLine.makeRaw(") SELECT ", " FROM " + ippTableName)
            
            self.scratchDb.execute(sql)

        # XXX EAM 20151105 : Not sure how this must be modified to handle the inv data:

        ## if we have a negative image (inv.cmf), do the ending chunk after loading the negative image data
        if isPosFile and self.haveNegFits[num]:
            return

        # insert stuff from dvo
        self.logger.infoPair("inserting dvo info:find imageID"," from externID")
        imageID = self.scratchDb.getImageIDFromExternID(diffSkyFileID)
        self.logger.infoPair("updating","diffdetection_"+str(num))
        sqlLine  = sqlUtility("UPDATE DiffDetection_"+str(num)+" as a, " + self.scratchDb.dvoDetectionTable + " as b SET ")
        sqlLine.group("a.diffObjID",   "b.objId")
        sqlLine.group("a.diffDetID",   "b.detectID")
        sqlLine.group("a.ippObjID",    "b.ippObjID")
        sqlLine.group("a.dvoRegionID", "b.catID")
        sqlLine.group("a.ra",          "b.ra")
        sqlLine.group("a.dec",         "b.dec_")
        sqlLine.group("a.raErr",       "b.raErr")   # NOTE: This is supplied above as well, is this needed??
        sqlLine.group("a.decErr",      "b.decErr") # NOTE: This is supplied above as well, is this needed??
        sqlLine.group("a.zp",          "b.zpPSF")
        sqlLine.group("a.telluricExt", "b.telluricExt")
        sqlLine.group("a.airmass",     "b.airmass")
        sqlLine.group("a.DinfoFlag3",  "b.flags")

        sql = sqlLine.makeEquals("WHERE a.ippDetectID = b.ippDetectID AND b.imageID = " +str( imageID))   
        
        try: self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("failed sql:",sql)
            raise

        self.logger.infoPair("need to cull"," nulls objid")
        results= self.scratchDb.reportAndDeleteRowsWithNULLS("DiffDetection_"+str(num), "diffObjID")
        self.logger.infoPair("Adding 'row' columns to", "DiffDetection_"+str(num))
        # diff has a primary key, so use this instead
        self.scratchDb.addRowCountColumnNoKey("DiffDetection_"+str(num), "row")
        self.logger.infoPair("adding","uniquePSPSDFid")
        self.updatePspsUniqueIDs("DiffDetection_"+str(num),num)
        self.logger.infoPair("Dropping row column from", "DiffDetection table")
        self.scratchDb.dropColumn("DiffDetection_"+str(num), "row")

    '''
    Populates the DiffToImage table
    '''
    def populateDiffToImage(self,num):
        diffSkyFileID = self.number[num]
        self.logger.infoPair("Procesing table", "DiffToImage")

        sql = "CREATE TABLE DiffToImage_"+str(num)+" like DiffToImage"
        try: self.scratchDb.execute(sql)
        except: raise
        #sql = "CREATE TABLE DiffDetection_"+str(num)+" like DiffDetection"
        #try: self.scratchDb.execute(sql)
        #except: raise
        imageIDs = self.gpc1Db.getImageIDsForThisDiffID(int(diffSkyFileID))

        for imageID in imageIDs:

            sql = "INSERT INTO DiffToImage_"+str(num)+" (diffImageID, imageID) \
            VALUES (\
            " + str(diffSkyFileID) + ", " + imageID + ")"
            self.scratchDb.execute(sql)

            # now update StackMeta with correct number of inputs
            #sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaID)+")"
            #self.scratchDb.execute(sql)

        #self.tablesToExport.append("DiffToImage")


    ''' 
    Populate DiffDetEffMeta
    '''
    def populateDiffDetEffMeta(self,num):
        diffSkyFileID = self.number[num]
        self.logger.infoPair("Processing table", "DiffDetEffMeta")

        if diffSkyFileID <= 0:
            self.logger.infoPair("no diff data for number", num)
            return False

        cmfTable = "SkyChip_deteff_" + str(num)
        if not cmfTable in self.tablesLoaded:
            self.logger.infoPair("no deteff data for diff", diffSkyFileID)
            self.logger.infoPair("(did not find table in tablesLoaded)", cmfTable)
            for name in self.tablesLoaded:
                print "-- table: " + name
            os._exit(3)
            return False

        self.logger.infoPair("Found cmfTable:", cmfTable)

        ippTableName = "DiffDetEffMeta_" + str(num)
        sql = "CREATE TABLE " + ippTableName + " LIKE DiffDetEffMeta"
        try: self.scratchDb.execute(sql)
        except: pass

        self.logger.infoPair("Created tmp table:", ippTableName)

        # we have read these values from the appropriate locations in init()
        zpImage = "NULL" if (self.zpImage[num] == "NULL") else float(self.zpImage[num])
        expTime = "NULL" if (self.expTime[num] == "NULL") else float(self.expTime[num])
        magref  = "NULL" if (self.magref[num]  == "NULL") else float(self.magref[num])

        isNull = (zpImage == "NULL") or (expTime == "NULL") or (magref == "NULL")
        detectionThreshold = "NULL" if isNull else magref + zpImage + 2.5 * math.log10(expTime)

        nInjected = int(self.nInjected[num])

        sqlLine = sqlUtility("INSERT INTO " + ippTableName + "(")
        sqlLine.group("diffImageID", str(diffSkyFileID))
        sqlLine.group("magref",       detectionThreshold)
        sqlLine.group("nInjected",    nInjected)
        sql = sqlLine.make(") VALUES ( ", ")")

        try: self.scratchDb.execute(sql)
        except: 
            self.logger.errorPair('failed sql: ', sql)
            raise

        self.logger.infoPair("CZW:", "About to loop over deteff bins.")

        for i in xrange(1, 14):
            stringint = str(i)
            if i < 10:
                stringint = '0' + str(i)
                
            sql = "UPDATE " + ippTableName + " , " + cmfTable + \
                " SET offset"+stringint + " = OFFSET, counts" \
                +stringint + " = COUNTS, diffMean" \
                +stringint + " = DIFF_MEAN, diffStdev" \
                +stringint + " = DIFF_STDEV, errMean" \
                +stringint + " = ERR_MEAN WHERE table_index = " + str(i)
                
            try: self.scratchDb.execute(sql)
            except:
                self.logger.errorPair('filed sql: ', sql)
                raise
            # end for
                # Append this like this?
        self.tablesToExport.append(ippTableName)
        # end filters


        return True

    '''
    Applies indexes and other constraints to the PSPS tables
    '''
    def alterPspsTables(self):
        
        self.logger.debug("Altering PSPS tables")
        self.logger.debug("Creating indexes on PSPS tables")
        self.scratchDb.makeColumnUnique("DiffDetection", "diffDetID")
        self.scratchDb.makeColumnPrimaryKey("DiffDetection", "ippDetectID")

        # dictionary objects to hold imageIDs for later
        self.imageIDs = {}
        self.sourceIDs = {}

        # loop through all OTAs and populate ImageMeta extensions
        # the column in PSPS
        self.scratchDb.execute("ALTER TABLE DiffDetection"+" CHANGE dec_ `dec` double")

        return True


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

        self.logger.infoPair("Creating indexes on", "IPP tables")

        #self.scratchDb.createIndex(extension, "IPP_IDET")
        # try the test Chip
        self.scratchDb.createIndex("SkyChip_psf_"+str(num), "IPP_IDET")

        self.logger.infoPair("created indexes on", "IPP tables")      

    '''
    Updates provided table with DVO IDs from DVO table
    '''
    def updateDvoIDs(self, table, externID):

        imageID = self.scratchDb.getImageIDFromExternID(externID)
        self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = %d" % imageID)

        sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \
               a.objID        = b.objID, \
               a.detectID     = b.detectID, \
               a.ippObjID     = b.ippObjID, \
               a.dvoRegionID  = b.catID, \
               a.ra           = b.ra, \
               a.dec          = b.dec_, \
               a.zp           = b.zpPSF, \
               a.telluricExt  = b.telluricExt, \
               a.airmass      = b.airmass, \
               a.expTime      = b.expTime, \
               a.DinfoFlag3   = b.flags  \
               a.DpsfFlux     = a.DpsfFlux    * b.zpFactorPSF, \
               a.DpsfFluxErr  = a.DpsfFluxErr * b.zpFactorPSF, \
               a.apFlux       = a.apFlux      * b.zpFactorAPER, \
               a.apFluxErr    = a.apFluxErr   * b.zpFactorAPER, \
               a.kronFlux     = a.kronFlux    * b.zpFactorAPER, \
               a.kronFluxErr  = a.kronFluxErr * b.zpFactorAPER  \
               a.sky          = a.sky         * b.zpFactorAPER, \
               a.skyErr       = a.skyErr      * b.zpFactorAPER  \
               WHERE a.ippDetectID = b.ippDetectID \
               AND b.imageID = " + str(imageID)

        try: self.scratchDb.execute(sql)
        except:
            self.logger.infoPair("failed sql",sql)
            raise
    '''
    Updates table and generates pspsuniqueids
    '''

    def updatePspsUniqueIDs(self,table,num):
        #sql = "UPDATE "+table+" join (select @r:=@r+1 rownum, diffobjID from \
        #(select @r:=0) r, "+table+" t) as foo using (diffobjID) set \
        #uniquePspsDFid = ((" +str(self.batchID)+ "*1000000000 ) + rownum)"
        sql = "UPDATE DiffDetection_"+str(num)+" set uniquePspsDFid = (("+str(self.batchID)+"*1000000000 ) + ("+str(num)+"*1000000)+  row)"
        try: self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql',sql)
            raise
        
    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''

    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTables(self):
        self.logger.infoPair("starting","populatePspsTables for diffBatch")
        # loop through all OTAs again to update with DVO IDs
        self.skipBatch = False
        self.tablesToExport = []
        tablesForMinMax = []
        for num in self.number:

            self.tablesToExport.append("DiffMeta_"+str(num))
            self.populateDiffMeta(num)
            self.logger.infoPair("populated","DiffMeta_"+str(num))
            self.tablesToExport.append("DiffToImage_"+str(num))
            self.populateDiffToImage(num)
            self.logger.infoPair("populated","DiffToImage_"+str(num))
            self.tablesToExport.append("DiffDetection_"+str(num))
            self.populateDiffDetectionTable(num, 1) # populate the positive image data
            self.populateDiffDetectionTable(num, 0) # populate the negative image data (if present)
            self.logger.infoPair("populated","DiffDetection_"+str(num))
#            self.setMinMaxDiffObjID(["DiffDetection_"+str(num)])
            tablesForMinMax.append("DiffDetection_"+str(num))

            self.populateDiffDetEffMeta(num)
            self.logger.infoPair("populated","DiffDetEffMeta_"+str(num))
            # CZW: I appended to tablesToExport inside populateDiffDetEffMeta to be like stack.

        # CZW: This needs to be done outside the loop on a list of all detection tables
        self.setMinMaxDiffObjID(tablesForMinMax)
        #test = self.scratchDb.getRowCount("Object")
        #return False
        
        return True


    '''
    Imports IPP tables from FITS file

    Accepts a regular expression filter so not all tables need to be imported
    '''
    def importIppTables(self, columns="*", filter=""):
        if self.config.useOhanaMYSQLtoFITS:
            if not self.importIppTablesOhana():
                self.logger.errorPair("problem running", "importIppTablesOhana")
                return False
        else:
            if not self.importIppTablesStilts(columns, filter):
                self.logger.errorPair("problem running", "importIppTablesStilts")
                return False

        return True

    '''
    Imports IPP tables from FITS file
    '''
    def importIppTablesOhana(self):
        
        self.logger.infoPair("Importing DF tables", "using fits_to_mysql")

        count = 0
        self.tablesLoaded = []
        self.haveApNpix = {}
        self.havePsfFwhm = {}
        self.haveSrcPos = {}
        self.haveNegFits = {}
        self.posImageDetCount = {}

        for num in self.number:
            fileNamePos = self.posFits[num].getPath()
            
            self.logger.infoPair("using filename:",fileNamePos)
        
            ## output table names need to have "_num" appended.  
            fitsOptions  = "-tablesuffix _" + str(num)

            # this option is equivalent to the stilts "addcol table_index $0" call
            fitsOptions += " -sequence-column table_index"
            fitsOptions += " -sequence-column-start 1"

            ## *** actually write the FITS data to mysql
            self.scratchDb.loadFITStoMYSQL(fileNamePos, fitsOptions)

            ## if we have a negative image (inv.cmf), load that as well:
            self.haveNegFits[num] = 0
            self.posImageDetCount[num] = 0
            if not self.negFits[num] == None:
                fileNameNeg = self.negFits[num].getPath()
            
                self.logger.infoPair("using filename:", fileNameNeg)
        
                ## output table names need to have "_num" appended.  
                fitsOptions  = "-tablesuffix _inv_" + str(num)

                # this option is equivalent to the stilts "addcol table_index $0" call
                fitsOptions += " -sequence-column table_index"
                fitsOptions += " -sequence-column-start 1"

                ## *** actually write the FITS data to mysql
                self.scratchDb.loadFITStoMYSQL(fileNameNeg, fitsOptions)
                self.haveNegFits[num] = 1

                posTableName = "SkyChip_psf_" + str(num)
                # self.posImageDetCount[num] = self.scratchDb.getMaxIPP_IDET(posTableName)
                self.posImageDetCount[num] = self.scratchDb.getRowCount(posTableName)

            ## use stilts to get a list of the tables from the header
            try:
                tables = stilts.treads(fileNamePos)
            except:
                self.logger.errorPair("STILTS could not import from", fileNamePos)
                return False
        
            # this is an optional field, check for it in the table below
            self.haveApNpix[num] = 0
            self.havePsfFwhm[num] = 0
            self.haveSrcPos[num] = 0

            for table in tables:
                # check for AP_NPIX columns: missing for SAS39
                self.logger.debugPair("checking table", table.name)
                if table.name == "SkyChip.psf":
                    columns = table.columns()
                    for column in columns:
                        columnString = str(column)
                        if columnString == 'AP_NPIX': 
                            self.haveApNpix[num] = 1
                            self.logger.debugPair("found AP_NPIX in file", fileNamePos)
                        if columnString == 'PSF_FWHM_MAJ':
                            self.havePsfFwhm[num] = 1
                            self.logger.debugPair("found PSF_FWHM_MAG in file", fileNamePos)
                        if columnString == 'SRC_CHIP_X':
                            self.haveSrcPos[num] = 1
                            self.logger.debugPair("found SRC_CHIP_X in file", fileNamePos)

                myTable = table.name.replace(".","_") + "_" + str(num)
                count = count + 1
                      
                self.tablesLoaded.append(myTable)

            # after all tables are read for this 'num', then index the set
            self.indexIppTables(num)

        self.logger.infoPair("Done. Imported", "%d tables" % count)
        return True

    '''
    Imports IPP tables from FITS file
    Accepts a regular expression to match the tables so not all tables need to be imported
    (This regex feature is not currently used...)
    '''
    def importIppTablesStilts(self, columns="*", tableRE=""):
        
        if self.config.retry: return True

        self.logger.infoPair("Importing DF tables with table match expression: ", tableRE)

        self.tablesLoaded = []
        self.haveApNpix = {}

        count = 0
        for num in self.number:
            diffSkyFileID = self.number[num]
            fileName = self.posFits[num].getPath()
            
            self.logger.infoPair("using filename:",fileName)
        
            try:
                tables = stilts.treads(fileName)
            except:
                self.logger.errorPair("STILTS could not import from", fileName)
                return False
        
            # this is an optional field, check for it in the table below
            self.haveApNpix[num] = 0

            # count = 0
            for table in tables:
              
                match = re.match(tableRE, table.name)
                if not match: continue

                # check for AP_NPIX columns: missing for SAS39
                if table.name == "SkyChip_psf":
                    columns = table.columns()
                    if 'AP_NPIX' in columns:
                        self.haveApNpix[num] = 1

                myTable = table.name + "_" + str(num)

                self.logger.infoPair("Reading IPP table", myTable)
                table = stilts.tpipe(table, cmd='addcol table_index $0')
                table = stilts.tpipe(table, cmd='explodeall')
                  
                # drop any previous tables before import
                self.scratchDb.dropTable(myTable)
                  
                # IPP FITS files are littered with infinities, so remove these
                self.logger.info("Removing Infinity values from all columns")
                table = stilts.tpipe(table, cmd='keepcols "' + columns + '"')
                table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
                table = stilts.tpipe(table, cmd='replaceval Infinity null *')
                  
                try:
                    table.write(self.scratchDb.url + '#' +  myTable)
                    count = count + 1
                except:
                    self.logger.exception("Problem writing table '" +  myTable + "' to the database")
                      
                self.logger.infoPair("Done. Imported", "%d tables" % count)
                self.indexIppTables(num)
                      
                self.tablesLoaded.append(myTable)

        return True

    '''
    Overriding this method. Use regex to trim off, eg _XY33 extension
    '''
    def exportPspsTablesToFits(self, regex="(.*)"):
       return super(DiffBatch, self).exportPspsTablesToFits("([a-zA-Z]+)")


    '''
    Drop a table and report hte drop
    '''
    def dropTableVerbose(self, table):
        self.logger.infoPair("dropping table:",table)
        self.scratchDb.dropTable(table)

