#!/usr/bin/env jython

import os.path
import glob
import sys
import math

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

import logging.config

'''
StackBatch class

This class, a sub-class of Batch, processes single stack skycells in the form of IPP cmf files

'''
class StackBatch(Batch):

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

       super(StackBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               skyID,
               batchID,
               "ST", 
               None)

       self.stackType = "DEEP_STACK" # TODO
       self.stackTypeID = self.getStackTypeID()
       
       self.filters = ['g', 'r', 'i', 'z', 'y']
       self.stackIDs = {}
       self.imageIDs = {}
       self.fits = {}
       self.headerSet = {}

       # we have two sets of imageIDs here:

       # stackID : this is the id from the gpc1 stack table and (should) match the 
       # cmf header keyword 'IMAGEID', this is also called EXTERN_ID in dvoImagesTable
       
       # imageID : this is the DVO-internal image ID for the stack cmf, and is called
       # IMAGE_ID in dvoImagesTable

       # we just need one valid filter to get the generic metadata
       validFilter = 'none'

       # get a list of stackIDs, identify the CMFs, read the headers:
       for filter in self.filters:
           filterName = filter + ".00000"

           self.logger.infoPair ("getting stackID for: ", filterName)
           stackID = gpc1Db.getStackIDFromSkyIDAndFilter(skychunk.dvoLabel, skyID, filterName)
           self.stackIDs[filter] = stackID

           if (stackID <= 0): 
               continue
               
           validFilter = filter

           # from each stack, get fits name from getStackStageCmf
           self.logger.infoPair ("finding fits file for: ", filterName)
           self.fits[filter] = gpc1Db.getStackStageCmf(skychunk.dvoLabel, stackID)
           if not self.fits[filter]:
               self.logger.errorPair("could not find cmf file name for ", stackID)
               raise

           header = self.fits[filter].getPrimaryHeader()
           if not header:
               self.logger.errorPair("could not read FITS PHU for ", stackID)
               raise

           self.headerSet[filter] = header

           self.logger.infoPair(filter + " stackID = ", stackID)       

           if not (str(stackID) == header['IMAGEID']): 
               self.logger.errorPair("inconsistency: header IMAGEID does not match stackID", str(stackID) + " vs " + header['IMAGEID'])
               raise

           imageID = self.scratchDb.getImageIDFromExternID(stackID)
           self.logger.infoPair(filter + " imageID = ", imageID)       
           self.imageIDs[filter] = imageID

       # did we find any valid stacks?
       if validFilter == 'none':
           self.logger.errorPair("can't find any valid stacks...?? ","??")
           raise
    
       validStackID = self.stackIDs[validFilter]

       # using the given stack ID, get the generic information: filter, skycell, etc.
       metadata = self.gpc1Db.getStackStageMeta(validStackID)  

       if not metadata:
           self.logger.errorPair("Could not get stack metadata for", validStackID)
           raise

       validHeader = self.headerSet[validFilter]

       tessName = self.getKeyValue(header, 'TESS_ID')
       self.tessID = self.scratchDb.getTessID(tessName)

       skycellName = self.getKeyValue(header, 'SKYCELL')

       # XXX EAM 20140812 : I am hardwiring the parsing logic for RINGS vs LOCAL
       if tessName == 'RINGS.V3':
           # skycell is, eg "skycell.1133.081"
           #                 0123456789012345
           self.projectionID = skycellName[8:12]
           self.skycellID = skycellName[13:]
       else:
           # skycell is, eg "skycell.081"
           #                 01234567890
           self.projectionID = 0
           self.skycellID = skycellName[8:11]
       if self.skycellID == "":
           self.skycellID = -1

       self.analysisVer = metadata[2];

       # delete PSPS tables
       for filter in self.filters:
           tableName = filter + "StackMeta"
           self.dropTableVerbose(tableName)

       self.dropTableVerbose("StackObjectThin")
       self.dropTableVerbose("StackObjectRaw")
       
       self.dropTableVerbose("StackModelFitExtra")
       self.dropTableVerbose("StackModelFitDeV")
       self.dropTableVerbose("StackModelFitExp")
       self.dropTableVerbose("StackModelFitSer")

       self.dropTableVerbose("StackApFlx")
       self.dropTableVerbose("StackApFlxFull")
       self.dropTableVerbose("StackApFlxFullC1")
       self.dropTableVerbose("StackApFlxFullC2")

       self.dropTableVerbose("StackPetrosian")

       self.dropTableVerbose("StackToImage")

       # delete IPP tables
       if not self.config.retry:
           self.logger.info("dropping Stack cmf tables")
           for filter in self.filters:
               tableName = filter + "SkyChip_psf"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_xrad"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_xsrc"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_xfit"
               self.scratchDb.dropTable(tableName)

       # create an output filename, which is {stackID}.FITS
       self.logger.infoPair( "self.id", self.id)
       self.logger.infoPair ("self.localOutPath",self.localOutPath)
       self.outputFitsFile = "%08d.FITS" % self.id
       self.outputFitsPath = "%s/%s" % (self.localOutPath, self.outputFitsFile)
       self.logger.infoPair("self.outputFitsFile",self.outputFitsFile)
       
       # set some constants
       self.historyModNum = "0"

       # insert what we know about this stack batch into the stack table
       self.ippToPspsDb.insertStackMeta(self.batchID, "x" , self.stackType)

       # dump stuff to log
       self.logger.infoPair("Stack ID", "%d" % self.id)
       self.logger.infoPair("Stack type", "%s" % self.stackType)
       self.logger.infoPair("SkycellID", "%s" % self.skycellID)
       self.logger.infoPair("ProjectionID", "%s" % self.projectionID)

    ### *********************** main populate functions ************************************

    '''
    Populates the StackMeta table, mainly from header values found in stack cmf FITS header
    '''
    def populateStackMeta(self,filter):

        header = self.headerSet[filter]
        tablename = filter + "StackMeta"
        stackID = self.stackIDs[filter]
        filterName = filter + ".00000"

        self.logger.infoPair("Populating table", tablename)

        fwhm_maj    = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ')
        fwhm_maj_uq = self.getKeyFloat(header, "%.8f", 'FW_MJ_UQ')
        psfmodel    = self.getKeyValue(header, 'PSFMODEL')

        # make a table
        filterID = self.scratchDb.getFilterID(filter)
        self.logger.infoPair("filterID is ",str(filterID))

        photoCalID = str(self.scratchDb.getPhotoCalID(stackID))

        # mysql is sensitive to values which are ambiugously float.  eg
        # a warning is raised if we try to insert '25.' into a float field.
        # use getKeyFloat(hdr, format, key) to avoid this problem

        sql = "CREATE TABLE " + tablename + " LIKE StackMeta"
        try: self.scratchDb.execute(sql)
        except: pass

        sqlLine = sqlUtility("INSERT INTO " + tablename + " (")

        sqlLine.group("stackMetaID",   str(stackID))        
        sqlLine.group("batchID",       str(self.batchID))
        sqlLine.group("surveyID",      str(self.surveyID))
        sqlLine.group("filterID",      str(filterID))           
        sqlLine.group("stackTypeID",   str(self.stackTypeID))
        sqlLine.group("tessID",        str(self.tessID))            
        sqlLine.group("projectionID",  str(self.projectionID))            
        sqlLine.group("skyCellID",     str(self.skycellID))            
        sqlLine.group("photoCalID",    photoCalID) 
        sqlLine.group("analysisVer",   str(self.analysisVer)) 
        sqlLine.group("expTime",       self.getKeyFloat(header, "%.5f", 'EXPTIME'))  
        sqlLine.group("psfModelID",    psfmodel)           
        sqlLine.group("psfFwhm_mean",  fwhm_maj)      
        sqlLine.group("psfFwhm_max",   fwhm_maj_uq)    
        sqlLine.group("photoZero",     self.getKeyFloat(header, "%.5f", 'FPA.ZP'))
        sqlLine.group("ctype1",        header['CTYPE1'])  
        sqlLine.group("ctype2",        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')) 
        sqlLine.group("dataRelease",   str(self.skychunk.dataRelease))

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

        try: self.scratchDb.execute(sql)
        except:
            self.logger.error("problem with SQL: " + sql)
            raise

        self.tablesToExport.append(tablename)

    '''
    Populates the StackObjectThin table
    '''
    def populateStackObjectThin(self):

        self.logger.infoPair("Procesing table", "StackObjectThin")

        # insert the per-object data (IDs, random stack ID, dataRelease, etc)
        self.selectDvoObjIDs()

        # add indexes StackObjectThin
        self.scratchDb.createIndex("StackObjectThin", "objID")

        self.logger.infoPair("inserting filter dependent items into", "StackObjectThin")

        for filter in self.filters:
            self.logger.infoPair("filter", filter)

            stackID = self.stackIDs[filter]
            self.logger.infoPair(filter + "stackID = ", stackID)

            if stackID <= 0:  
                self.logger.infoPair("no stack data for filter" , filter) 
                continue

            imageID = self.imageIDs[filter]
            self.logger.infoPair("selecting imageID", imageID)

            # at this point, the StackObjectThin table includes objID and ippObjID

            # insert detection information for each filter based on the DVO contents
            sqlLine = sqlUtility("UPDATE StackObjectThin as a, " + self.scratchDb.dvoDetectionTable + " as b SET")

            sqlLine.group("a."+filter+"ippDetectID",   "b.ippDetectID") # eg, gippDetectID (from dvo.measure.detID)
            sqlLine.group("a."+filter+"stackDetectID", "b.detectID")    # eg, gstackDetectID (from dvo.measure.extID)
            sqlLine.group("a."+filter+"stackMetaID",    str(stackID))   # eg, gstackMetaID (from cmf.IMAGEID)

            sqlLine.group("a.dvoRegionID",             "b.catID")       # this is not filter dependent, but is re-written for each filter (if data exists)
            sqlLine.group("a.primaryDetection",        "((b.flags & 0x10000) >> 16)") # this is not filter dependent, but is re-written for each filter (if data exists)

            sqlLine.group("a."+filter+"haveData",      "'1'")

            sqlLine.group("a."+filter+"ra",            "b.ra")
            sqlLine.group("a."+filter+"dec",           "b.dec_")

            sqlLine.group("a."+filter+"PSFMag",        "b.Mpsf")
            sqlLine.group("a."+filter+"PSFMagErr",     "b.dMpsf")
            sqlLine.group("a."+filter+"KronMag",       "b.Mkron")
            sqlLine.group("a."+filter+"KronMagErr",    "b.dMkron")
            sqlLine.group("a."+filter+"ApMag",         "b.Map")
            sqlLine.group("a."+filter+"ApMagErr",      "b.dMap")
            sqlLine.group("a."+filter+"telluricExt",   "b.telluricExt")

            sqlLine.group("a."+filter+"infoFlag2",     "(b.flags << 13)")
            sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND b.imageID = " + str(imageID))

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

            # insert values coming from the CMF, not carried by DVO
            header = self.headerSet[filter]
            exptime = self.getKeyFloat(header, "%.5f", "EXPTIME")

            sqlLine = sqlUtility("UPDATE StackObjectThin AS a , " + filter + "SkyChip_psf AS b SET")

            sqlLine.group("a."+filter+"raErr",         "b.X_PSF_SIG * b.PLTSCALE")
            sqlLine.group("a."+filter+"decErr",        "b.Y_PSF_SIG * b.PLTSCALE")
            sqlLine.group("a."+filter+"sky",           "b.SKY / " + str(exptime))
            sqlLine.group("a."+filter+"skyErr",        "b.SKY_SIGMA / " + str(exptime))
            sqlLine.group("a."+filter+"infoFlag",      "b.FLAGS")
            sqlLine.group("a."+filter+"infoFlag2",     "b.FLAGS2 | a."+filter+"infoFlag2")
            sqlLine.group("a."+filter+"nFrames",       "b.N_FRAMES")

            sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID=b.IPP_IDET")

            # index the (f)ippDetectIDs for the next steps?
            # for filter in self.filters:
            #     IDname = filter + "ippDetectID"
            #     self.scratchDb.createIndex("StackObjectThin", IDname)

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

        self.tablesToExport.append("StackObjectThin")

    '''
    Populates the StackObjectRaw table
    '''
    def populateStackObjectRaw(self):

        tablename = "StackObjectRaw"

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        self.scratchDb.createIndex(tablename, "objID") 

        self.logger.infoPair("inserting filter dependent cmf items into", tablename)
        for filter in self.filters:
            self.logger.infoPair("filter", filter)

            stackID = self.stackIDs[filter]
            self.logger.infoPair(filter + "stackID = ", stackID)

            if stackID <= 0:  
                self.logger.infoPair("no stack data for filter" , filter) 
                continue

            header = self.headerSet[filter]
            exptime = self.getKeyFloat(header, "%.5f", "EXPTIME")

            # insert all the detections
            sqlLine = sqlUtility("UPDATE " + tablename + " AS a , " + filter + "SkyChip_psf AS b SET")

            sqlLine.group("a."+filter+"haveData",      "'1'")

            sqlLine.group("a."+filter+"xPos",          "b.X_PSF")
            sqlLine.group("a."+filter+"yPos",          "b.Y_PSF")
            sqlLine.group("a."+filter+"xPosErr",       "b.X_PSF_SIG")
            sqlLine.group("a."+filter+"yPosErr",       "b.Y_PSF_SIG")
            sqlLine.group("a."+filter+"psfMajorFWHM",  "b.PSF_FWHM_MAJ")
            sqlLine.group("a."+filter+"psfMinorFWHM",  "b.PSF_FWHM_MIN")
            sqlLine.group("a."+filter+"psfTheta",      "b.PSF_THETA")
            sqlLine.group("a."+filter+"psfCore",       "b.PSF_CORE")
            sqlLine.group("a."+filter+"psfLikelihood", "psfLikelihood(b.EXT_NSIGMA)")
            sqlLine.group("a."+filter+"psfQf",         "b.PSF_QF")
            sqlLine.group("a."+filter+"psfQfPerfect",  "b.PSF_QF_PERFECT")
            sqlLine.group("a."+filter+"psfChiSq",      "b.PSF_CHISQ / b.PSF_NDOF")
            sqlLine.group("a."+filter+"momentXX",      "b.MOMENTS_XX")
            sqlLine.group("a."+filter+"momentXY",      "b.MOMENTS_XY")
            sqlLine.group("a."+filter+"momentYY",      "b.MOMENTS_YY")
            sqlLine.group("a."+filter+"momentR1",      "b.MOMENTS_R1")
            sqlLine.group("a."+filter+"momentRH",      "b.MOMENTS_RH")
            sqlLine.group("a."+filter+"PSFFlux",       "b.PSF_INST_FLUX / " + str(exptime))
            sqlLine.group("a."+filter+"PSFFluxErr",    "b.PSF_INST_FLUX_SIG / " + str(exptime))
            sqlLine.group("a."+filter+"ApFlux",        "b.AP_FLUX / " + str(exptime))
            sqlLine.group("a."+filter+"ApFluxErr",     "b.AP_FLUX_SIG / " + str(exptime))
            sqlLine.group("a."+filter+"KronFlux",      "b.KRON_FLUX / " + str(exptime))
            sqlLine.group("a."+filter+"KronFluxErr",   "b.KRON_FLUX_ERR / " + str(exptime))
            sqlLine.group("a."+filter+"ApFillFac",     "b.AP_NPIX / (3.14159265359 * POW(b.AP_MAG_RADIUS - 0.5, 2))")
            sqlLine.group("a."+filter+"ApRadius",      "b.AP_MAG_RADIUS")
            sqlLine.group("a."+filter+"KronRad",       "b.MOMENTS_R1 * 2.5")
            sqlLine.group("a."+filter+"PlateScale",    "b.PLTSCALE")

            sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET")

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

            # insert detection information for each filter based on the DVO contents
            sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
            sqlLine.group("a."+filter+"expTime",       "b.expTime")
            sqlLine.group("a."+filter+"zp",            "b.zp")

            # where should these go?
            # sqlLine.group("a."+filter+"airmass",       "b.airmass")

            sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID")

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


        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Populates the StackModelFitExtra table
    '''
    def populateStackModelFitExtra(self):

        self.logger.infoPair("Procesing table", "StackModelFitExtra")
        self.insertDvoIDs("StackModelFitExtra", "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        # self.scratchDb.createIndex(tablename, "objID") 

        # currently, none of the fields defined by the table are generated
        
    '''
    Populates the StackModelFitExp table
    '''
    def populateStackModelFit(self, model, ippModelType):

        tablename = "StackModelFit" + model

        if self.config.camera == "simtest":
            self.logger.infoPair("Skipping table for simtest:", tablename)
            return

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        # self.scratchDb.createIndex(table, "objID") 

        for filter in self.filters:
            self.populateStackModelFitFilter(model, ippModelType, filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Updates parameters for a particular model in the StackModelFit table
    '''
    def populateStackModelFitFilter(self, model, ippModelType, filter):

        tablename = "StackModelFit" + model

        if self.stackIDs[filter] <= 0:
            self.logger.infoPair("no stack data for filter" , filter) 
            return True

        header  = self.headerSet[filter]

        # model calibrated magnitude = instrumental magnitude + 2.5*log10(exptime) + ZP (added below from dvoDetectionTable)
        magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME'])))

        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + filter + "SkyChip_xfit AS b SET")

        sqlLine.group("a."+filter+"haveData",              "'1'")

        # model calibrated magnitude = instrumental magnitude + 2.5*log10(exptime) + ZP (added below from dvoDetectionTable)
        sqlLine.group("a." + filter + model + "Mag",       "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime)
        sqlLine.group("a." + filter + model + "MagErr",    "b.EXT_INST_MAG_SIG")
                      
        sqlLine.group("a." + filter + model + "Radius",    "b.EXT_WIDTH_MAJ")
        sqlLine.group("a." + filter + model + "Ab",        "b.EXT_WIDTH_MIN/b.EXT_WIDTH_MAJ")
        sqlLine.group("a." + filter + model + "Phi",       "DEGREES(b.EXT_THETA)")

        sqlLine.group("a." + filter + model + "RadiusErr", "b.EXT_WIDTH_MAJ_ERR") # these are not correctly defined.
        sqlLine.group("a." + filter + model + "AbErr",     "b.EXT_WIDTH_MIN_ERR") # these are not correctly defined.
        sqlLine.group("a." + filter + model + "PhiErr",    "b.EXT_THETA_ERR") # these are not correctly defined.

        sqlLine.group("a." + filter + model + "Chisq",     "b.EXT_CHISQ / b.EXT_NDOF")

        # I have raExp = X_EXT

        # This is tricky: RA_EXT is not defined for PS1_SV3 or PS1_V5, but X_EXT is
        # XXX EAM 20140812: for PV3, update cmf output to define RA_EXT, DEC_EXT
        sqlLine.group("a." + filter + model + "Ra",        "b.X_EXT")
        sqlLine.group("a." + filter + model + "Dec",       "b.Y_EXT")
        sqlLine.group("a." + filter + model + "RaErr",     "b.X_EXT_SIG") # need to modify by platescale
        sqlLine.group("a." + filter + model + "DecErr",    "b.Y_EXT_SIG")

        # sersic fit has an extra parameter
        if (ippModelType == "PS_MODEL_SERSIC"):
            sqlLine.group("a." + filter + model + "Nu",    "b.EXT_PAR_07")
            sqlLine.group("a." + filter + model + "NuErr", "'0.1'") # this is not measured at the moment

        # missing these two fields?
        # sqlLine.group("a." + filter + model + "CF",         "b.XX")
        # sqlLine.group("a." + filter + model + "Likelihood", "b.XX")

        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND b.MODEL_TYPE = '" + ippModelType + "'")

        try: self.scratchDb.execute(sql)
        except:
            self.logger.error("problem with SQL: " + sql)
            raise

        # insert detection information for each filter based on the
        # DVO contents.  Since I already have extracted
        # StackObjectRaw, this is probably faster using that table:
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
        sqlLine.group("a." + filter + model + "Mag",       "a." + filter + model + "Mag + b.zp")
        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID")

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

        # we have (modulo signs):
        # gRaExp  = gra  + (X_EXT - gxPos)*gPlateScale / cos(gdec)
        # gDecExp = gdec + (Y_EXT - gyPos)*gPlateScale

        rawS  = "c." + filter + "PlateScale"

        extR  = "a." + filter + model + "Ra"
        rawR  = "b." + filter + "ra"
        rawX  = "c." + filter + "xPos"

        extD  = "a." + filter + model + "Dec"
        rawD  = "b." + filter + "dec"
        rawY  = "c." + filter + "yPos"

        # dRA / dX = -PlateScale
        # b.+filter+ra  - (a.+filter+model+Ra - c.+filter+xPos)*c.+filter+PlateScale / cos(+filter+dec)
        # extR = rawR - (extR - rawX)*rawS / 3600.0 / cos(rawD)

        # dDEC / dY = +PlateScale
        # b.+filter+dec + (a.+filter+model+Dec - c.+filter+xPos)*c.+filter+PlateScale / cos(+filter+dec)
        # extD = rawD + (extD - rawY)*rawS / 3600.0

        # insert detection information for each filter based on the DVO contents
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, StackObjectThin as b, StackObjectRaw as c SET ")
        sqlLine.group(extR, rawR + " - (" + extR + " - " + rawX + ")*" + rawS + " / 3600.0 / cos(radians(" + rawD + "))")
        sqlLine.group(extD, rawD + " + (" + extD + " - " + rawY + ")*" + rawS + " / 3600.0")
        field = "a." + filter + model + "RaErr"
        sqlLine.group(field, field + "*" + rawS)
        field = "a." + filter + model + "DecErr"
        sqlLine.group(field, field + "*" + rawS)
        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a.objID = c.objID")

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

    '''
    Populates the StackPetrosian table, mainly from dictionary values found in IPP FITS header
    '''
    def populateStackPetrosian(self):

        tablename = "StackPetrosian"

        if self.config.camera == "simtest":
            self.logger.infoPair("Skipping table for simtest:", tablename)
            return

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        # self.scratchDb.createIndex(tablename, "objID") 

        for filter in self.filters:
            self.populateStackPetrosianFilter(filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Populates the StackPetrosian table, mainly from dictionary values found in IPP FITS header
    '''
    def populateStackPetrosianFilter(self, filter):

        tablename = "StackPetrosian"

        if self.stackIDs[filter] <= 0:
            self.logger.infoPair("no stack data for filter" , filter) 
            return True

        header  = self.headerSet[filter]

        # PETRO_MAG is instrumental + 2.5log(exptime) + FPA.ZP
        # we want to apply the DVO zero point, so remove FPA.ZP:

        hdrZP   = "%.5f" % float(header['FPA.ZP'])
        # magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME'])))

        self.logger.infoPair("Adding", "petrosians for extended sources")
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + filter + "SkyChip_xsrc AS b SET")

        sqlLine.group("a."+filter+"haveData",         "'1'")

        sqlLine.group("a." + filter + "petRadius",    "b.PETRO_RADIUS")
        sqlLine.group("a." + filter + "petRadiusErr", "b.PETRO_RADIUS_ERR")
        sqlLine.group("a." + filter + "petMag",       "b.PETRO_MAG - " + hdrZP)  
        sqlLine.group("a." + filter + "petMagErr",    "1.0 / b.PETRO_MAG_ERR")  
        sqlLine.group("a." + filter + "petR50",       "b.PETRO_RADIUS_50")
        sqlLine.group("a." + filter + "petR50Err",    "b.PETRO_RADIUS_50_ERR")
        sqlLine.group("a." + filter + "petR90",       "b.PETRO_RADIUS_90")
        sqlLine.group("a." + filter + "petR90Err",    "b.PETRO_RADIUS_90_ERR")
        sqlLine.group("a." + filter + "petCf",        "b.PETRO_FILL")

        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET")

        try: self.scratchDb.execute(sql)
        except:
            self.logger.error("problem with SQL: " + sql)
            raise

        # modify petMag to apply the zero point
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
        sqlLine.group("a." + filter + "petMag",       "a." + filter + "petMag + b.zp")
        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID")

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

    '''
    Populates the StackApFlx table
    '''
    def populateStackApFlx(self):

        tablename = "StackApFlx"

        if self.config.camera == "simtest":
            self.logger.infoPair("Skipping table for simtest:", tablename)
            return

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        self.scratchDb.createIndex(tablename, "objID") 

        for filter in self.filters:
            self.populateStackApFlxFilter(tablename, "", filter)
            self.populateStackApFlxFilter(tablename, "C1", filter)
            self.populateStackApFlxFilter(tablename, "C2", filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Populates the StackApFlxFull table
    '''
    def populateStackApFlxFull(self, version):

        tablename = "StackApFlxFull" + version

        if self.config.camera == "simtest":
            self.logger.infoPair("Skipping table for simtest:", tablename)
            return

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        self.scratchDb.createIndex(tablename, "objID") 

        for filter in self.filters:
            self.populateStackApFlxFilter(tablename, version, filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Updates aperture fluxes for StackApFkx table
    '''
    def populateStackApFlxFilter(self, tablename, version, filter):

        prefix = "none"

        # order of the 3 convolutions is fixed (raw, C1, C2) = (1, 2, 3)
        if version == "":   
            psfCondition = "(b.table_index % 3 = 1)"
            prefix = ""
        if version == "C1": 
            psfCondition = "(b.table_index % 3 = 2)"
            prefix = "c1"
        if version == "C2": 
            psfCondition = "(b.table_index % 3 = 0)" 
            prefix = "c2"

        if prefix == "none":
            self.logger.errorPair("invalid stack ap flux version", version)
            raise

        if self.stackIDs[filter] <= 0:
            self.logger.infoPair("no stack data for filter" , filter) 
            return True

        minRadius = 1
        maxRadius = 10
        if tablename == 'StackApFlx':
            minRadius = 3
            maxRadius = 5

        # properly -999 these to start with.  the default should take
        # care of this, but does not

        sqlLine = sqlUtility("UPDATE " + tablename + " SET")
        for radius in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]:
            if radius < minRadius: continue
            if radius > maxRadius: continue
            sqlLine.group(filter + prefix + "flxR" + str(radius),          "-999")
            sqlLine.group(filter + prefix + "flxR" + str(radius) + "Err",  "-999")
            sqlLine.group(filter + prefix + "flxR" + str(radius) + "Std",  "-999")
            sqlLine.group(filter + prefix + "flxR" + str(radius) + "Fill", "-999")

        sql = sqlLine.makeEquals("")

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

        # set the flux values from the cmf file
        cmfTable = filter + "SkyChip_xrad"

        # we have variable numbers of these columns. find out which are in use
        sql = "DESCRIBE " + cmfTable + " 'aper_fill_%'"
        rs = self.scratchDb.executeQuery(sql)
        rs.first()
        columns = []
        column = rs.getString(1)
        columns.append(column)
        while (rs.next()):
            column = rs.getString(1)
            columns.append(column)
        rs.close()

        # use regex to select the aperture number (APER_FILL_N)
        numbers = []
        for column in columns: 
            number = re.sub("APER_FILL_","",column)
            if (int(number) >= minRadius and int(number) <= maxRadius):
                numbers.append(number)
                self.logger.infoPair("Aperture Numbers",number)

        # get the exposure time for aperture fluxes
        header = self.headerSet[filter]
        exptime = self.getKeyFloat(header, "%.5f", "EXPTIME")

        # generate the sql to do the necessary ops on the columns    
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ")

        sqlLine.group("a."+filter+"haveData", "'1'")

        for number in numbers:
            field = filter + prefix + "flxR" + number
            sqlLine.group(field,          "b.APER_FLUX_"       + number + " / " + exptime)
            sqlLine.group(field + "Err",  "b.APER_FLUX_ERR_"   + number + " / " + exptime)
            sqlLine.group(field + "Std",  "b.APER_FLUX_STDEV_" + number + " / " + exptime)
            sqlLine.group(field + "Fill", "b.APER_FILL_"       + number)
            
        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND  " + psfCondition)

        self.scratchDb.execute(sql)

        # instrumental flux, AB mags, Jy:
        # flux_inst = cts / sec
        # mag_AB = -2.5*log(flux_inst) + ZP [with appropriate definition of zero point]
        # mag_AB = -2.5*log(flux_cgs) - 48.6 [by construction with ~Vega flux in V-band]
        # flux_cgs = ten(-0.4*(mag_AB + 48.6))
        # flux_cgs = ten(-0.4*((-2.5*log(flux_inst) + ZP) + 48.6))
        # flux_cgs = flux_inst * ten(-0.4*(ZP + 48.6))
        # flux_Jy  = flux_cgs * 10^23
        # flux_Jy  = flux_inst * ten(-0.4*(ZP + 48.6) + 23.0)  [23.0 - 0.4*48.6 = 3.56]
        # flux_Jy  = flux_inst * ten(-0.4*ZP + 3.56)
        # flux_Jy  = flux_inst * ten(-0.4*ZP) * 3630.78

        # modify flux to apply the zero point
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
        for number in numbers:
            field = filter + prefix + "flxR" + number
            sqlLine.group(field,               field         + " * POW(10, 3.56 - 0.4* b.zp)")
            sqlLine.group(field + "Err",       field + "err" + " * POW(10, 3.56 - 0.4* b.zp)")
            sqlLine.group(field + "Std",       field + "Std" + " * POW(10, 3.56 - 0.4* b.zp)")
        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID")
        self.scratchDb.execute(sql)

    '''
    Populates the StackToImage table
    '''
    def populateStackToImage(self):

        self.logger.infoPair("Procesing table", "StackToImage")

        for filter in self.filters:
            
            stackmetaID = self.stackIDs[filter]
            if stackmetaID > 0:
    
                imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID)

                for imageID in imageIDs:

                    sql = "INSERT INTO StackToImage (stackMetaID, imageID) \
                   VALUES (\
                   " + str(stackmetaID) + ", " + 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("StackToImage")

    ### *********************** Utility Functions ************************************

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

    '''
    get stackTypeID corresponding to our stackType
    '''
    def getStackTypeID(self):

        sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'"
        rs = self.scratchDb.executeQuery(sql)
        if not rs.next():
            self.logger.errorPair("cannot get stack type ID from database for", self.stackType)
            raise
        
        ID = rs.getInt(1)
        return ID

    def deleteRowsWithNoStackData(self, tableName):

        self.logger.infoPair("Deleting rows with no stack data from", tableName)
        sql = "DELETE FROM " + tableName + " WHERE "

        for i in range(len(self.filters)):
            filter = self.filters[i]
            sql += filter + "haveData is NULL"

            if (i < len(self.filters) - 1):
                sql += " AND "

        self.logger.infoPair('sql',sql)    
        self.scratchDb.execute(sql)

        for filter in self.filters:
            column = filter + "haveData"
            self.scratchDb.dropColumn(tableName, column)

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

        self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)")

        return True

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

        for filter in self.filters:
            if self.stackIDs[filter] <= 0:  
                self.logger.infoPair("no files for filter" , filter) 
                continue

            self.logger.infoPair("Creating indexes on", "IPP tables")
            self.scratchDb.createIndex(filter + "SkyChip_psf",  "IPP_IDET")
            self.scratchDb.createIndex(filter + "SkyChip_xfit", "IPP_IDET")
            self.scratchDb.createIndex(filter + "SkyChip_xrad", "IPP_IDET")
            self.scratchDb.createIndex(filter + "SkyChip_xsrc", "IPP_IDET")

        return True

    '''
    select objID (psps object ID) from dvo restricted to this set of imageIDs. also insert
    ippObjID and several constants (for a skycell): surveyID, skyCellId, dataRelease
    '''
    def selectDvoObjIDs(self):

        # we need a temporary column for each filter to identify empty rows
        for filter in self.filters:
            self.scratchDb.addColumn("StackObjectThin", filter + "haveData", "tinyint")

        sqlLine = sqlUtility("INSERT INTO StackObjectThin (")
        
        sqlLine.group("objID",            "objID")
        sqlLine.group("ippObjID",         "ippObjID")
        sqlLine.group("surveyID",         "'" + str(self.surveyID) + "'")
        sqlLine.group("tessID",           "'" + str(self.tessID) + "'")
        sqlLine.group("projectionID",     "'" + str(self.projectionID) + "'")
        sqlLine.group("skyCellID",        "'" + str(self.skycellID) + "'")
        sqlLine.group("dataRelease",      "'" + str(self.skychunk.dataRelease) + "'")
        sqlLine.group("randomStackObjID", "FLOOR(RAND()*9223372036854775807)")

        whereClause = " WHERE ("
        imageIDs = self.imageIDs.values()
        for i in range(len(imageIDs)):
            imageID = imageIDs[i]
            self.logger.infoPair("selecting imageID", imageID)

            whereClause += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")"

            if (i < len(imageIDs) - 1):
                whereClause += " or "

        whereClause += ")"

        sql = sqlLine.makeRaw(") SELECT DISTINCT ", "FROM " + self.scratchDb.dvoDetectionTable + whereClause)

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

    '''
    Insert objid (from dvo) into table from main table
    '''
    def insertDvoIDs(self, table, mainTable):

        # we need a temporary column for each filter to identify empty rows
        for filter in self.filters:
            self.scratchDb.addColumn(table, filter + "haveData", "tinyint")

        fields = "objID, ippObjID, randomStackObjID, primaryDetection, bestDetection, "

        for i in range(len(self.filters)):
            filter = self.filters[i]
            fields += filter + "ippDetectID, "
            fields += filter + "stackDetectID, "
            fields += filter + "stackMetaID" # careful here: do not add comma to last entry
            if (i < len(self.filters) - 1):
                fields += ", "
        
        sql  = "INSERT INTO " + table + " (" + fields + ") "
        sql += "SELECT " + fields + " FROM " + mainTable

        try: self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql',sql)
            return
    
    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTables(self):
        self.logger.infoPair("starting","populatePspsTables");
        self.skipBatch = False

        # each of the "populate*" methods below add their tables to the list:
        self.tablesToExport=[]    

        # EAM 20140730 : I have revised the StackObjectXX construction
        # process.  The challenge here is that we have dvo-generated
        # objIDs and ippObjIDs for each stack detection in seperate
        # rows, but we want to merge the 5 filter sets into a single
        # set of rows.  The old code was relying on INSERT IGNORE,
        # making it hard to catch errors, and probably not doing the
        # insert very efficiently
        
        # Now we do a first pass with SELECT DISTINCT to get the
        # complete set of objIDs which are associated with detections
        # from one of the relevant images (ie, measure.imageID matches
        # the set of image IDs).  

        # once we have determined the complete set of desired objects,
        # then we can use an update to set the per-field fields with
        # data selected for each filter.  Once we have constructed
        # this basic common information in StackObjectThin, then we
        # can uses this table to seed the same rows in the other Stack
        # tables

        for filter in self.filters:
            if self.stackIDs[filter] > 0:        
                self.populateStackMeta(filter)

        self.populateStackObjectThin()

        self.populateStackObjectRaw()

        if self.stackType != "NIGHTLY_STACK":

            self.logger.infoPair("populating ","StackModelFitExtra")
            self.populateStackModelFitExtra()

            self.logger.infoPair("populating ","StackModelFit")
            self.populateStackModelFit("Exp", "PS_MODEL_EXP")
            self.populateStackModelFit("DeV", "PS_MODEL_DEV")
            self.populateStackModelFit("Ser", "PS_MODEL_SERSIC")

            self.populateStackPetrosian()

            self.logger.infoPair("populating ","StackApFlx Tables")
            self.populateStackApFlx()

            self.populateStackApFlxFull("")
            self.populateStackApFlxFull("C1")
            self.populateStackApFlxFull("C2")

        # delete rows with no content. for the other tables, this is done in 
        # the 'populate' functions, but StackObjectThin needs to retain all rows
        # until those tables are done
        self.deleteRowsWithNoStackData("StackObjectThin")

        self.logger.infoPair("populating","StackToImage") 
        self.populateStackToImage()
        
        self.setMinMaxObjID(["StackObjectThin"])

        self.logger.infoPair("finishing","populatePspsTables"); 
        return True

    '''
    overidding this method - use regex to trimoff letter before stack
    '''
    def exportPspsTablesToFits(self, regex="(.*)"):
        return super(StackBatch, self).exportPspsTablesToFits("(Stack.*)")

    '''
    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 importIppTables(self, columns="*", tableRE=""):
        
        if self.config.retry: return True

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

        count = 0
        for filter in self.filters:
            if self.stackIDs[filter] <= 0:  
                self.logger.infoPair("no files for filter" , filter) 
                continue

            fileName = self.fits[filter].getPath()
            
            self.logger.infoPair("using filename:",fileName)
            
            try:
                tables = stilts.treads(fileName)
            except:
                self.logger.errorPair("STILTS could not import from", fileName)
                return False
            
            # count = 0
            for table in tables:
              
                match = re.match(tableRE, table.name)
                if not match: continue

                self.logger.infoPair("Reading IPP table", filter + table.name)
                table = stilts.tpipe(table, cmd='addcol table_index $0')
                table = stilts.tpipe(table, cmd='explodeall')
                  
                # drop any previous tables before import
                self.scratchDb.dropTable(filter + table.name)
                  
                # 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 + '#' + filter + table.name)
                    count = count + 1
                except:
                    self.logger.exception("Problem writing table '" + filter + table.name + "' to the database")
                      
        self.logger.infoPair("Done. Imported", "%d tables" % count)
        self.indexIppTables()
                      
        return True
