#!/usr/bin/env jython

import os.path
import glob
import sys

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

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,
                 stackID,
                 batchID,
                 useFullTables):

       super(StackBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               stackID,
               batchID,
               "ST", 
               gpc1Db.getStackStageCmf(skychunk.dvoLabel, stackID),
               useFullTables)

       # self.printline = 0
       # # self.testprint()

       self.stackType = "DEEP_STACK" # TODO

       # get stack meta data
       meta = self.gpc1Db.getStackStageMeta(self.id)
       if not meta:
           self.logger.errorPair("Could not get stack", "metadata")
           raise

       self.filter = meta[0];
       self.filter = self.filter[0:1]
       self.filterID = self.scratchDb.getFilterID(self.filter)
       self.skycell = meta[1];

       # self.testprint()

       # skycell is, eg "skycell.1133.081"
       # we need 1133.081 for skycell, but store as an int, so instead store 1133081
       # projection cell is just the 1133 bit
       # this is how it should be done
       #self.projectioncell = self.skycell[8:12]
       #self.skycell = self.skycell[8:]
       #self.skycell = self.skycell.replace(".", "")
       #self.projectioncell = self.skycell[8:12]

       # TODO this is the wrong way, but is consistent with SkyCell table in IN data
       self.skycell = self.skycell[8:12]
       self.projectioncell = self.skycell

       # self.testprint()

       # proposed new values. Need to coordinate with the SkyCell table
       # this fits in 32 bits for 0 < tessID < 214
       # self.skycell = str( (tessID * (10000 * 1000)) + (int(self.projectioncell) * 1000) + cellID )

       self.analysisVer = meta[2];

       #self.expTime = gpc1Db.getStackExpTime(self.id)
       self.expTime = self.header['EXPTIME']

       # delete PSPS tables
       self.scratchDb.dropTable("StackMeta")
       self.scratchDb.dropTable("StackDetection")
       self.scratchDb.dropTable("StackModelFit")
       self.scratchDb.dropTable("StackApFlx")
       self.scratchDb.dropTable("StackToImage")
       self.scratchDb.dropTable("SkinnyObject")
       #self.scratchDb.dropTable("ObjectCalColor")
       self.scratchDb.dropTable("StackDetectionCalib")

       # self.testprint()

       # delete IPP tables
       self.scratchDb.dropTable("SkyChip_psf")
       self.scratchDb.dropTable("SkyChip_xrad")
       self.scratchDb.dropTable("SkyChip_xfit")
       self.scratchDb.dropTable("SkyChip_xsrc")

       # create an output filename, which is {stackID}.FITS
       self.outputFitsFile = "%08d.FITS" % self.id
       self.outputFitsPath = "%s/%s" % (self.localOutPath, 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, self.filter, self.stackType)

       # insert sourceID/imageID combo so DVO can look it up
       if not self.useFullTables:
           self.scratchDb.insertNewDvoExternID(self.header['SOURCEID'], self.header['IMAGEID'])

       # self.testprint()

       # dump stuff to log
       self.logger.infoPair("Stack ID", "%d" % self.id)
       self.logger.infoPair("Stack type", "%s" % self.stackType)
       self.logger.infoPair("Skycell", "%s" % self.skycell)
       self.logger.infoPair("Projectioncell", "%s" % self.projectioncell)
       self.logger.infoPair("Filter", "%s" % self.filter)

    '''
    Updates a table with stackMetaID
    '''
    def updateStackMetaID(self, table):

        sql = "UPDATE " + table + "  SET stackMetaID=" + str(self.id)
        self.scratchDb.execute(sql)

    '''
    Updates a table with stackTypeID grabbed from FitModel init table
    '''
    def updateStackTypeID(self, table):

        sql = "UPDATE "+table+" AS a, StackType AS b SET a.stackTypeID=b.stackTypeID WHERE b.name = '" + self.stackType + "'"
        self.scratchDb.execute(sql)


    def testprint(self):
      print "here ", self.printline
      self.printline += 1

    '''
    Updates aperture fluxes for StackApFkx table
    '''
    def updateApFlxs(self, prefix, psfCondition):
        #properly -999 these to start with:

        sql = "UPDATE StackApFlx AS a, SkyChip_xrad AS b SET \
        "+prefix+"flxR1=-999, \
        "+prefix+"flxR1Err=-999, \
        "+prefix+"flxR1Std=-999, \
        "+prefix+"flxR1Fill=-999, \
        "+prefix+"flxR2=-999, \
        "+prefix+"flxR2Err=-999, \
        "+prefix+"flxR2Std=-999, \
        "+prefix+"flxR2Fill=-999, \
        "+prefix+"flxR3=-999, \
        "+prefix+"flxR3Err=-999, \
        "+prefix+"flxR3Std=-999, \
        "+prefix+"flxR3Fill=-999, \
        "+prefix+"flxR4=-999, \
        "+prefix+"flxR4Err=-999, \
        "+prefix+"flxR4Std=-999, \
        "+prefix+"flxR4Fill=-999, \
        "+prefix+"flxR5=-999, \
        "+prefix+"flxR5Err=-999, \
        "+prefix+"flxR5Std=-999, \
        "+prefix+"flxR5Fill=-999, \
        "+prefix+"flxR6=-999, \
        "+prefix+"flxR6Err=-999, \
        "+prefix+"flxR6Std=-999, \
        "+prefix+"flxR6Fill=-999, \
        "+prefix+"flxR7=-999, \
        "+prefix+"flxR7Err=-999, \
        "+prefix+"flxR7Std=-999, \
        "+prefix+"flxR7Fill=-999, \
        "+prefix+"flxR8=-999, \
        "+prefix+"flxR8Err=-999, \
        "+prefix+"flxR8Std=-999, \
        "+prefix+"flxR8Fill=-999, \
        "+prefix+"flxR9=-999, \
        "+prefix+"flxR9Err=-999, \
        "+prefix+"flxR9Std=-999, \
        "+prefix+"flxR9Fill=-999, \
        "+prefix+"flxR10=-999, \
        "+prefix+"flxR10Err=-999, \
        "+prefix+"flxR10Std=-999, \
        "+prefix+"flxR10Fill=-999 \
        WHERE a.ippDetectID=b.IPP_IDET "
        #self.logger.infoPair("sql", sql)
        self.scratchDb.execute(sql)


        #we have variable numbers of these columns. find out which are in use
        sql = "DESCRIBE SkyChip_xrad '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 leave just the numbers

        numbers = []
        for column in columns: 
            number = re.sub("APER_FILL_","",column)
            if (int(number) >= 1 and int(number) <=10):
                numbers.append(number)
                self.logger.infoPair("Aperture Numbers",number)

        #generate the sql to do the necessary ops on the columns    
        sql = "UPDATE StackApFlx AS a, SkyChip_xrad AS b SET "
        cnt =0
        for number in numbers:
            if (cnt>0):
                sql=sql+" , "
            sql=sql+prefix+"flxR"+number+"=b.APER_FLUX_"+number +" / " + str(self.expTime) + ", "
            sql=sql+prefix+"flxR"+number+"Err=b.APER_FLUX_ERR_"+number +" / " + str(self.expTime) + ", "
            sql=sql+prefix+"flxR"+number+"Std=b.APER_FLUX_STDEV_"+number +" / " + str(self.expTime) + ", "
            sql=sql+prefix+"flxR"+number+"Fill=b.APER_FILL_"+number + " "
            cnt=cnt+1
            
        sql=sql+"WHERE a.ippDetectID=b.IPP_IDET AND b.PSF_FWHM "+psfCondition     
        self.scratchDb.execute(sql)

        #self.logger.infoPair("sql",sql)

        #sql = "UPDATE StackApFlx AS a, SkyChip_xrad AS b SET \
        #"+prefix+"flxR1=b.APER_FLUX_1 / " + str(self.expTime) + ", \
        #"+prefix+"flxR1Err=b.APER_FLUX_ERR_1 / " + str(self.expTime) + ", \
        #"+prefix+"flxR1Std=b.APER_FLUX_STDEV_1 / " + str(self.expTime) + ", \
        #"+prefix+"flxR1Fill=b.APER_FILL_1, \
        #"+prefix+"flxR2=b.APER_FLUX_2 / " + str(self.expTime) + ", \
        ##"+prefix+"flxR2Err=b.APER_FLUX_ERR_2 / " + str(self.expTime) + ", \
        #"+prefix+"flxR2Std=b.APER_FLUX_STDEV_2 / " + str(self.expTime) + ", \
        #"+prefix+"flxR2Fill=b.APER_FILL_2, \
        #"+prefix+"flxR3=b.APER_FLUX_3 / " + str(self.expTime) + ", \
        #"+prefix+"flxR3Err=b.APER_FLUX_ERR_3 / " + str(self.expTime) + ", \
        #"+prefix+"flxR3Std=b.APER_FLUX_STDEV_3 / " + str(self.expTime) + ", \
        #"+prefix+"flxR3Fill=b.APER_FILL_3, \
        #"+prefix+"flxR4=b.APER_FLUX_4 / " + str(self.expTime) + ", \
        #"+prefix+"flxR4Err=b.APER_FLUX_ERR_4 / " + str(self.expTime) + ", \
        #"+prefix+"flxR4Std=b.APER_FLUX_STDEV_4 / " + str(self.expTime) + ", \
        #"+prefix+"flxR4Fill=b.APER_FILL_4, \
        #"+prefix+"flxR5=b.APER_FLUX_5 / " + str(self.expTime) + ", \
        #"+prefix+"flxR5Err=b.APER_FLUX_ERR_5 / " + str(self.expTime) + ", \
        #"+prefix+"flxR5Std=b.APER_FLUX_STDEV_5 / " + str(self.expTime) + ", \
        #"+prefix+"flxR5Fill=b.APER_FILL_5, \
        #"+prefix+"flxR6=b.APER_FLUX_6 / " + str(self.expTime) + ", \
        #"+prefix+"flxR6Err=b.APER_FLUX_ERR_6 / " + str(self.expTime) + ", \
        #"+prefix+"flxR6Std=b.APER_FLUX_STDEV_6 / " + str(self.expTime) + ", \
        #"+prefix+"flxR6Fill=b.APER_FILL_6, \
        #"+prefix+"flxR7=b.APER_FLUX_7 / " + str(self.expTime) + ", \
        #"+prefix+"flxR7Err=b.APER_FLUX_ERR_7 / " + str(self.expTime) + ", \
        #"+prefix+"flxR7Std=b.APER_FLUX_STDEV_7 / " + str(self.expTime) + ", \
        #"+prefix+"flxR7Fill=b.APER_FILL_7, \
        #"+prefix+"flxR8=b.APER_FLUX_8 / " + str(self.expTime) + ", \
        #"+prefix+"flxR8Err=b.APER_FLUX_ERR_8 / " + str(self.expTime) + ", \
        #"+prefix+"flxR8Std=b.APER_FLUX_STDEV_8 / " + str(self.expTime) + ", \
        #"+prefix+"flxR8Fill=b.APER_FILL_8, \
        #"+prefix+"flxR9=b.APER_FLUX_9 / " + str(self.expTime) + ", \
        #"+prefix+"flxR9Err=b.APER_FLUX_ERR_9 / " + str(self.expTime) + ", \
        #"+prefix+"flxR9Std=b.APER_FLUX_STDEV_9 / " + str(self.expTime) + ", \
        #"+prefix+"flxR9Fill=b.APER_FILL_9, \
        #"+prefix+"flxR10=b.APER_FLUX_10 / " + str(self.expTime) + ", \
        #"+prefix+"flxR10Err=b.APER_FLUX_ERR_10 / " + str(self.expTime) + ", \
        #"+prefix+"flxR10Std=b.APER_FLUX_STDEV_10 / " + str(self.expTime) + ", \
        #"+prefix+"flxR10Fill=b.APER_FILL_10 \
        #WHERE a.ippDetectID=b.IPP_IDET AND b.PSF_FWHM "+psfCondition

        #self.scratchDb.execute(sql)

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

        if model == "ser":
            modelLong = "sersic"
        else:
            modelLong = model;

        sql = "UPDATE StackModelFit AS a, SkyChip_xfit AS b SET \
        "+model+"Radius=b.EXT_WIDTH_MAJ,  \
        "+model+"Flux=POW(10, -0.4 * b.EXT_INST_MAG) / " + str(self.expTime) + " ,  \
        "+model+"FluxErr=ABS(b.EXT_INST_MAG_SIG) * POW(10, -0.4 * b.EXT_INST_MAG) / " + str(self.expTime) + " / 1.085736, \
        "+model+"Ab=b.EXT_WIDTH_MAJ/b.EXT_WIDTH_MIN, \
        "+model+"Phi=b.EXT_THETA,  \
        "+modelLong+"Covar11=b.EXT_COVAR_00_00,  \
        "+modelLong+"Covar12=b.EXT_COVAR_00_01,  \
        "+modelLong+"Covar13=b.EXT_COVAR_00_02,  \
        "+modelLong+"Covar14=b.EXT_COVAR_00_03,  \
        "+modelLong+"Covar15=b.EXT_COVAR_00_04,  \
        "+modelLong+"Covar16=b.EXT_COVAR_00_05,  \
        "+modelLong+"Covar17=b.EXT_COVAR_00_06,  \
        "+modelLong+"Covar22=b.EXT_COVAR_01_01,  \
        "+modelLong+"Covar23=b.EXT_COVAR_01_02,  \
        "+modelLong+"Covar24=b.EXT_COVAR_01_03,  \
        "+modelLong+"Covar25=b.EXT_COVAR_01_04,  \
        "+modelLong+"Covar26=b.EXT_COVAR_01_05,  \
        "+modelLong+"Covar27=b.EXT_COVAR_01_06,  \
        "+modelLong+"Covar33=b.EXT_COVAR_02_02,  \
        "+modelLong+"Covar34=b.EXT_COVAR_02_03,  \
        "+modelLong+"Covar35=b.EXT_COVAR_02_04,  \
        "+modelLong+"Covar36=b.EXT_COVAR_02_05,  \
        "+modelLong+"Covar37=b.EXT_COVAR_02_06,  \
        "+modelLong+"Covar44=b.EXT_COVAR_03_03,  \
        "+modelLong+"Covar45=b.EXT_COVAR_03_04,  \
        "+modelLong+"Covar46=b.EXT_COVAR_03_05,  \
        "+modelLong+"Covar47=b.EXT_COVAR_03_06,  \
        "+modelLong+"Covar55=b.EXT_COVAR_04_04,  \
        "+modelLong+"Covar56=b.EXT_COVAR_04_05,  \
        "+modelLong+"Covar57=b.EXT_COVAR_04_06,  \
        "+modelLong+"Covar66=b.EXT_COVAR_05_05,  \
        "+modelLong+"Covar67=b.EXT_COVAR_05_06,  \
        "+modelLong+"Covar77=b.EXT_COVAR_06_06   \
        WHERE a.ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'"

        self.scratchDb.execute(sql)

        # sersic fit has an extra parameter
        if ippModelType == "PS_MODEL_SERSIC":
            sql = "UPDATE StackModelFit AS a, SkyChip_xfit AS b SET \
            "+modelLong+"Covar18=b.EXT_COVAR_00_07,  \
            "+modelLong+"Covar28=b.EXT_COVAR_01_07,  \
            "+modelLong+"Covar38=b.EXT_COVAR_02_07,  \
            "+modelLong+"Covar48=b.EXT_COVAR_03_07,  \
            "+modelLong+"Covar58=b.EXT_COVAR_04_07,  \
            "+modelLong+"Covar68=b.EXT_COVAR_05_07,  \
            "+modelLong+"Covar78=b.EXT_COVAR_06_07,  \
            "+modelLong+"Covar88=b.EXT_COVAR_07_07,   \
            "+"serNu=b.EXT_PAR_07,   \
            "+"serNuErr=SQRT(EXT_COVAR_07_07)  \
            WHERE a.ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'"

            self.scratchDb.execute(sql)


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

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

        self.fwhm_maj    = self.safeDictionaryAccess(self.header, 'FWHM_MAJ')
        self.fwhm_maj_uq = self.safeDictionaryAccess(self.header, 'FW_MJ_UQ')
        self.psfmodel    = self.safeDictionaryAccess(self.header, 'PSFMODEL')
        if (self.fwhm_maj    == "NULL"): self.fwhm_maj    = -999
        if (self.fwhm_maj_uq == "NULL"): self.fwhm_maj_uq = -999

        # print "fwhm_maj    = ", self.fwhm_maj
        # print "fwhm_maj_uq = ", self.fwhm_maj_uq
        # print "psfmodel    = ", self.psfmodel

        sql = "INSERT INTO StackMeta (\
        stackMetaID \
        ,skycellID \
        ,photoCalID \
        ,photoZero \
        ,expTime \
        ,psfModelID \
        ,psfFwhm_mean \
        ,psfFwhm_max \
        ,ctype1 \
        ,ctype2 \
        ,crval1 \
        ,crval2 \
        ,crpix1 \
        ,crpix2 \
        ,cdelt1 \
        ,cdelt2 \
        ,pc001001 \
        ,pc001002 \
        ,pc002001 \
        ,pc002002 \
         ) VALUES ( \
        " + str(self.id) + " \
        ," + self.skycell + " \
        ," + str(self.scratchDb.getPhotoCalID(self.header['IMAGEID'])) + " \
        ," + self.header['FPA.ZP'] + " \
        ," + self.expTime + " \
        ,'" + self.psfmodel   + "' \
        ," + str(self.fwhm_maj)    + " \
        ," + str(self.fwhm_maj_uq) + " \
        ,'" + self.header['CTYPE1'] + "' \
        ,'" + self.header['CTYPE2'] + "' \
        ," + self.header['CRVAL1'] + " \
        ," + self.header['CRVAL2'] + " \
        ," + self.header['CRPIX1'] + " \
        ," + self.header['CRPIX2'] + " \
        ," + self.header['CDELT1'] + " \
        ," + self.header['CDELT2'] + " \
        ," + self.header['PC001001'] + " \
        ," + self.header['PC001002'] + " \
        ," + self.header['PC002001'] + " \
        ," + self.header['PC002002'] + " \
        )"
        self.scratchDb.execute(sql)

        self.scratchDb.updateAllRows("StackMeta", "surveyID", str(self.surveyID))
        self.scratchDb.updateFilterID("StackMeta", self.filter)
        self.scratchDb.updateAllRows("StackMeta", "dataRelease", str(self.skychunk.dataRelease))
        self.updateStackTypeID("StackMeta")

    '''
    Populates the StackDetection table
    '''
    def populateStackDetection(self):

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

        # insert all the detections
        sql = "INSERT INTO StackDetection (\
               ippDetectID \
               ,skyCellID \
               ,projectionCellID \
               ,xPos \
               ,yPos \
               ,xPosErr \
               ,yPosErr \
               ,psfFlux \
               ,psfFluxErr \
               ,peakFlux \
               ,sky \
               ,skyErr \
               ,sgSep \
               ,psfWidMajor \
               ,psfWidMinor \
               ,psfTheta \
               ,psfLikelihood \
               ,infoFlag \
               ,psfQf \
               ,psfQfPerfect \
               ,momentXX \
               ,momentXY \
               ,momentYY \
               ,momentM3C \
               ,momentM3S \
               ,momentM4C \
               ,momentM4S \
               ,momentR1 \
               ,momentRH \
               ,apFlux \
               ,apFluxErr \
               ,kronFlux \
               ,kronFluxErr \
               ,kronRad \
               ,psfChiSq \
               ,nFrames \
               ,assocDate \
               ,historyModNum \
               ) \
               SELECT \
               IPP_IDET \
               ," + self.skycell + " \
               ," + self.projectioncell + " \
               ,X_PSF \
               ,Y_PSF \
               ,X_PSF_SIG \
               ,Y_PSF_SIG \
               ,POW(10.0, (-0.4*PSF_INST_MAG)) / " + str(self.expTime) + " \
               ,ABS((PSF_INST_MAG_SIG*(POW(10.0, (-0.4*PSF_INST_MAG)) / " + str(self.expTime) + ")) / 1.085736) \
               ,POW(10.0, (-0.4*PEAK_FLUX_AS_MAG)) / " + str(self.expTime) + " \
               ,SKY / " + str(self.expTime) + " \
               ,SKY_SIGMA / " + str(self.expTime) + " \
               ,EXT_NSIGMA \
               ,PSF_MAJOR \
               ,PSF_MINOR \
               ,PSF_THETA \
               ,psfLikelihood(EXT_NSIGMA) \
               , FLAGS2 << 32 | FLAGS \
               ,PSF_QF \
               ,PSF_QF_PERFECT \
               ,MOMENTS_XX \
               ,MOMENTS_XY \
               ,MOMENTS_YY \
               ,MOMENTS_M3C \
               ,MOMENTS_M3S \
               ,MOMENTS_M4C \
               ,MOMENTS_M4S \
               ,MOMENTS_R1 \
               ,MOMENTS_RH \
               ,POW(10.0, -0.4*AP_MAG) / " + str(self.expTime) + " \
               ,NULL \
               ,KRON_FLUX / " + str(self.expTime) + " \
               ,KRON_FLUX_ERR / " + str(self.expTime) + " \
               ,MOMENTS_R1 * 2.5 \
               ,PSF_CHISQ / PSF_NDOF \
               ,N_FRAMES \
               , '" + self.dateStr + "' \
               ," + self.historyModNum + " \
               FROM SkyChip_psf"

        # print "sql: ", sql
        # response = raw_input("ready to insert stack det ")

        self.scratchDb.execute(sql)

        #it is possible to drop some detections from dvo (that are present in the cmf). when that happens we get a 0 for objid
        #we drop those...
        sql="DELETE FROM StackDetection where objID = 0"
        self.scratchDb.execute(sql)
        self.logger.infoPair("Deleting", "entries with StackDetection.objID = 0")
        
        self.scratchDb.updateAllRows("StackDetection", "surveyID", str(self.surveyID))
        
        self.scratchDb.updateFilterID("StackDetection", self.filter)
        
        self.scratchDb.updateAllRows("StackDetection", "dataRelease", str(self.skychunk.dataRelease))
        
        self.scratchDb.updateAllRows("StackDetection", "primaryF", "0")
        
        self.scratchDb.updateAllRows("StackDetection", "activeFlag", "0")
        
        self.updateStackMetaID("StackDetection")
        
        self.updateStackTypeID("StackDetection")
        
        self.updateDvoIDsAndFlags("StackDetection")
        # response = raw_input("updated dvo ")
        
        sql = "ALTER IGNORE TABLE StackDetection ADD PRIMARY KEY (objID)"
        
        self.scratchDb.execute(sql)
        # response = raw_input("add primary key? ")
  
        if self.stackType == "DEEP_STACK": 

            #if deep stack and instFlux = null and err not null
            sql = "UPDATE StackDetection AS a, SkyChip_psf AS b \
                   SET psfFlux = 2*b.PSF_INST_FLUX_SIG / " + str(self.expTime) + " \
                   WHERE psfFlux IS NULL \
                   AND a.ippDetectID = b.IPP_IDET  \
                   AND b.PSF_INST_FLUX_SIG IS NOT NULL"
            
            self.scratchDb.execute(sql)
            # response = raw_input("add psf flux ")
            
        #leave null instflux in
        #self.scratchDb.reportAndDeleteRowsWithNULLS("StackDetection", "instFlux")
        
        self.scratchDb.reportAndDeleteRowsWithNULLS("StackDetection", "objID")
        # response = raw_input("delete nulls ")
        
        sql="DELETE FROM StackDetection where objID = 0"
        
        self.scratchDb.execute(sql)
        self.logger.infoPair("Deleting", "entries with StackDetection.objID = 0")
        # response = raw_input("deleted objID is 0 ")
        

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

        self.logger.infoPair("Procesing", "StackApFlx")
 
        sql = "INSERT INTO StackApFlx \
               (ippDetectID ) \
               SELECT \
               DISTINCT IPP_IDET \
               FROM SkyChip_xrad"

        try:
            self.scratchDb.execute(sql)
        except: return

        # TODO temporarily loading 1st convolved fluxes into unconvolved fields
        self.logger.infoPair("Adding fluxes", "un-convolved")
        self.updateApFlxs("", "IS NULL")
        self.logger.infoPair("Adding fluxes", "1st convolved")
        self.updateApFlxs("c1", "< 7.0")
        self.logger.infoPair("Adding fluxes", "2nd convolved")
        self.updateApFlxs("c2", "> 7.0")

        self.logger.infoPair("Adding", "petrosians for extended sources")
        sql = "UPDATE StackApFlx AS a, SkyChip_xsrc AS b SET \
        petRadius=b.PETRO_RADIUS \
        ,petRadiusErr=b.PETRO_RADIUS_ERR \
        ,petFlux=POW(10.0, -0.4 * b.PETRO_MAG) /" + str(self.expTime) + " \
        ,petFluxErr=ABS(b.PETRO_MAG_ERR) * POW(10.0, (-0.4*b.PETRO_MAG)) / " + str(self.expTime) + " / 1.085736  \
        ,petR50=b.PETRO_RADIUS_50 \
        ,petR50Err=b.PETRO_RADIUS_50_ERR \
        ,petR90=b.PETRO_RADIUS_90 \
        ,petR90Err=b.PETRO_RADIUS_90_ERR \
        ,petCf=b.PETRO_FILL \
        WHERE a.ippDetectID=b.IPP_IDET"
        self.scratchDb.execute(sql)

        self.scratchDb.updateAllRows("StackApFlx", "surveyID", str(self.surveyID))
        self.scratchDb.updateFilterID("StackApFlx", self.filter)
        self.scratchDb.updateAllRows("StackApFlx", "dataRelease", str(self.skychunk.dataRelease))
        self.scratchDb.updateAllRows("StackApFlx", "primaryF", "0")
        self.scratchDb.updateAllRows("StackApFlx", "activeFlag", "0")
        self.updateStackMetaID("StackApFlx")
        self.updateStackTypeID("StackApFlx")
        self.updateDvoIDs("StackApFlx")
        self.scratchDb.reportAndDeleteRowsWithNULLS("StackApFlx", "objID")
        self.deleteDetectionsNotInStackDetection("StackApFlx")


    '''
    Populates the StackModelFit table
    '''
    def populateStackModelFit(self):

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

        # insert all the detections
        sql = "INSERT INTO StackModelFit (ippDetectID) SELECT DISTINCT IPP_IDET from SkyChip_xfit"
        try:
            self.scratchDb.execute(sql)
        except:
            return

        # populate model parameters
        self.logger.infoPair("Adding model fit", "deVaucouleurs")
        self.updateModelFit("deV", "PS_MODEL_DEV")
        self.logger.infoPair("Adding model fit", "exponential")
        self.updateModelFit("exp", "PS_MODEL_EXP")
        self.logger.infoPair("Adding model fit", "sersic")
        self.updateModelFit("ser", "PS_MODEL_SERSIC")

        self.scratchDb.updateAllRows("StackModelFit", "surveyID", str(self.surveyID))
        self.scratchDb.updateFilterID("StackModelFit", self.filter)
        self.scratchDb.updateAllRows("StackModelFit", "dataRelease", str(self.skychunk.dataRelease))
        self.scratchDb.updateAllRows("StackModelFit", "primaryF", "0")
        self.scratchDb.updateAllRows("StackModelFit", "activeFlag", "0")
        self.updateStackMetaID("StackModelFit")
        self.updateStackTypeID("StackModelFit")
        self.updateDvoIDs("StackModelFit")
        self.scratchDb.reportAndDeleteRowsWithNULLS("StackModelFit", "objID")
        self.deleteDetectionsNotInStackDetection("StackModelFit")

    '''
    Reports and deletes detections in this table that are not in StackDetection
    '''
    def deleteDetectionsNotInStackDetection(self, table):

        sql = "SELECT COUNT(*) FROM " + table + " WHERE ippDetectID NOT IN (SELECT ippDetectID FROM StackDetection)"
        rs = self.scratchDb.executeQuery(sql)
        rs.first()
        nMissing = rs.getInt(1)
  
        if nMissing < 1: return
        
        sql = "DELETE FROM " + table + " WHERE ippDetectID NOT IN (SELECT ippDetectID FROM StackDetection)"
        self.scratchDb.execute(sql)
        self.logger.infoPair("Detections not in StackDetection", "%d deleted from %s" % (nMissing, table))
       

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

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

        imageIDs = self.gpc1Db.getImageIDsForThisStackID(self.id)

        for imageID in imageIDs:
            sql = "INSERT INTO StackToImage (stackMetaID, imageID) \
                   VALUES (\
                   " + str(self.id) + ", " + imageID + ")"
            self.scratchDb.execute(sql)

        # now update StackMeta with correct number of inputs
        sql = "UPDATE StackMeta SET nP2Images = (SELECT COUNT(*) FROM StackToImage)"
        self.scratchDb.execute(sql)

    '''
    Populates the SkinnyObject table
    '''
    def populateSkinnyObject(self):

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

        sql = "INSERT INTO SkinnyObject (\
               objID \
               ,ippObjID \
               ,projectionCellID \
               ) \
               SELECT \
               DISTINCT objID \
               ,ippObjID \
               ,projectionCellID \
               FROM StackDetection"
        self.scratchDb.execute(sql)

        self.scratchDb.updateAllRows("SkinnyObject", "surveyID", str(self.surveyID))
        self.scratchDb.updateAllRows("SkinnyObject", "dataRelease", str(self.skychunk.dataRelease))

    '''    
    Populates the StackDetectionCalib table
    XXX this can probably get a big speed increase by using 'SELECT () INTO OUTFILE '/tmp/name' and then
    calling the load data infile '/tmp/name' into table;
    '''
    def populateStackDetectionCalibInsertUpdate(self):
        self.logger.infoPair("Processing table", "StackDetectionCalib")
        tableName = "StackDetectionCalib"
        # insert all detections into table
        sql = "INSERT INTO " + tableName + " ( \
               objID \
               ,stackDetectID \
               ,ippObjID \
               ,ippDetectID \
               ,filterID \
               ,surveyID \
               ) \
               SELECT \
               objID \
               ,stackDetectID \
               ,ippObjID \
               ,ippDetectID \
               ,filterID \
               ,surveyID \
               FROM StackDetection"
        self.scratchDb.execute(sql)

        imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])
        self.logger.infoPair("obtained","imageID")

        # insert calibration information from dvoDetections into the Table
        sql = "UPDATE " + tableName + " AS a, "  + self.scratchDb.dvoDetectionTable + " AS b \
            SET a.ra = b.ra, \
            a.dec = b.dec_, \
            a.raErr = b.raErr, \
            a.decErr = b.decErr, \
            a.zp = b.zp, \
            a.zpErr = b.zpErr, \
            a.expTime = b.expTime, \
            a.airMass = b.airMass   \
            WHERE a.stackDetectID = b.detectID \
            AND b.imageID = " + str(imageID)
        self.scratchDb.execute(sql)
        self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease))
        
    def populateStackDetectionCalib(self):

        tableName = "StackDetectionCalib"
        self.logger.infoPair("Processing table", tableName)

        imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])
        self.logger.infoPair("obtained","imageID")

        # check for & create output directory first
        datadumpDir = "/tmp/datadump"
        try:
            statinfo = os.stat(datadumpDir)
            # check on the stat results?
        except:
            print "making the data dump directory ", datadumpDir
            os.mkdir(datadumpDir)
            os.chmod(datadumpDir, 0777)
            statinfo = os.stat(datadumpDir)

        dumpFile = datadumpDir + "/genetest.xx.dat"
        files = glob.glob(dumpFile)
        if len(files) > 0:
            os.unlink(dumpFile)

        # insert all detections into table
        sql = "SELECT \
          a.objID,    \
          a.stackDetectID, \
          a.ippObjID,      \
          a.ippDetectID,   \
          a.filterID,      \
          a.surveyID,      \
          b.ra,            \
          b.dec_,          \
          b.raErr,         \
          b.decErr,        \
          b.zp,            \
          b.zpErr,         \
          b.expTime,       \
          b.airMass,       \
          " + str(self.skychunk.dataRelease) + " \
         FROM              \
           StackDetection as a \
         JOIN " + self.scratchDb.dvoDetectionTable + " as b \
         ON (a.stackDetectID = b.detectID) where b.imageID = " + str(imageID) + \
         " INTO OUTFILE '" + dumpFile + "'"
        print "sql: ", sql
        self.scratchDb.execute(sql)

        sql = "LOAD DATA INFILE '" + dumpFile + "' INTO TABLE " + tableName
        print "sql: ", sql
        self.scratchDb.execute(sql)

        ## XXX write this with the select/insert
        # self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease))

    '''
    Populates the ObjectCalColor table
    '''
    def populateObjectCalColor(self):

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

        sql = "INSERT INTO ObjectCalColor (\
               objID \
               ,ippObjID \
               ) \
               SELECT \
               DISTINCT objID \
               ,ippObjID \
               FROM StackDetection"
        self.scratchDb.execute(sql)

        self.scratchDb.updateFilterID("ObjectCalColor", self.filter)
        self.scratchDb.updateAllRows("ObjectCalColor", "dataRelease", str(self.skychunk.dataRelease))


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

        self.logger.debug("Altering PSPS tables")
        # heather uncommented the unique line -- well that no worky
        #self.scratchDb.makeColumnUnique("StackDetection", "objID")
        self.scratchDb.createIndex("StackDetection", "ippDetectID")
        self.scratchDb.createIndex("StackApFlx", "ippDetectID")
        self.scratchDb.createIndex("StackModelFit", "ippDetectID")

        # dec is reserved in MySQL, so STILTS replaces if with dec_, which is not the name of 
        # the column in PSPS
        self.scratchDb.execute("ALTER TABLE StackDetectionCalib CHANGE dec_ `dec` double")

        return True

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

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

        return True

    '''
    Updates provided table with DVO IDs from DVO table
    '''
    def updateDvoIDs(self, table):
        self.logger.infoPair("getting","imageID")
        imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])
        self.logger.infoPair("obtained","imageID")
        self.logger.debug("Updating table '" + table + "' with DVO IDs...")
        sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \
               a.ippObjID = b.ippObjID, \
               a.stackDetectID = b.detectID, \
               a.objID = b.objID \
               WHERE a.ippDetectID = b.ippDetectID \
               AND b.imageID = " + str(imageID)
        self.scratchDb.execute(sql)
    
    '''
    Updates Flags
    '''
    def updateDvoIDsAndFlags(self, table):
        self.logger.infoPair("getting","imageID")
        imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])
        self.logger.infoPair("obtained","imageID")
        self.logger.debug("Updating table '" + table + "' with DVO IDs...")
        sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \
               a.ippObjID = b.ippObjID, \
               a.stackDetectID = b.detectID, \
               a.objID = b.objID, \
               a.infoFlag = b.flags << 45 | a.infoFlag \
               WHERE a.ippDetectID = b.ippDetectID \
               AND b.imageID = " + str(imageID)

        # print "update dvo sql: ", sql
        # response = raw_input("update dvo...")
        self.scratchDb.execute(sql)
    
    
    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTables(self):

        if not self.useFullTables:
            if not self.getIDsFromDVO():
                return False
        #self.logger.infoPair("populating","StackMeta")    
        self.populateStackMeta()
        #self.logger.infoPair("populating","StackDetection") 
        self.populateStackDetection()
        
        if self.stackType != "NIGHTLY_STACK": 
            #self.logger.infoPair("populating","StackModelFit") 
            self.populateStackModelFit()
            #self.logger.infoPair("populating","StackApFlx") 
            self.populateStackApFlx()
        #self.logger.infoPair("populating","StackToImage") 
        self.populateStackToImage()
        #self.logger.infoPair("populating","SkinnyObject") 
        self.populateSkinnyObject()
        #self.logger.infoPair("populating","ObjectCalColor") 
        #self.populateObjectCalColor()
        #self.logger.infoPair("populating","StackDetectionCalib") 
        self.populateStackDetectionCalib()

        self.setMinMaxObjID(["StackDetection"])
       
        if self.totalDetections < 1: 

            self.logger.error("No detections to publish")
            return False

        return True

