#!/usr/bin/env jython

import os.path
import sys
import math
import glob
import time
import stilts

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

'''
DetectionBatch class

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

'''
class DetectionBatch(Batch):

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

       super(DetectionBatch, self).__init__(
           logger,
           config,
           skychunk,
           gpc1Db,
           ippToPspsDb,
           scratchDb,
           camID,
           batchID,
           "P2", 
           gpc1Db.getCameraStageSmf(camID,skychunk.P2_smf_version))

       # get camera meta data
       meta = self.gpc1Db.getCameraStageMeta(self.id)
       if not meta:
           self.logger.infoPair("Could not get camera metadata", "trying with no warp id")
           meta = self.gpc1Db.getCameraStageMetaNoWarpID(self.id)
           if not meta:
               self.logger.errorPair("Could not get camera metadata", "now what?")
               raise
       

       # set a flag indicating that we have the new columns added in version 5 of the
       # cmf/smf format. It began around cam_id 982483 apparently.
       # if cam_id is small assume that it cam from the test suite. 
       # XXX: Should be looking for EXTNAME >= "PS1_V5"
       if (self.id >= 982483 or self.id < 100):
           self.v5cols = 1;
       else:
           self.v5cols = 0;

       self.expID = meta[0];
       self.expName = meta[1];
       self.distGroup = meta[2];
       self.analysisVer = meta[3];
       self.bias = meta[4];
       self.biasScat = meta[5];
       self.chipID = meta[6];
       self.warpID = meta[7];
       
       
       if not self.analysisVer: self.analysisVer = -999

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

       self.startX = 0
       self.endX = 8
       self.startY = 0
       self.endY = 8

       #self.startX = 1
       #self.endX = 2
       #self.startY = 7
       #self.endY = 8

       # get a few primary header values. if in test mode, then use defaults
       if self.safeDictionaryAccessWithDefault(self.header, 'MJD-OBS', "1") == "NULL":
           self.logger.errorPair("Could not get", "MJD-OBS")
           raise

       if self.safeDictionaryAccessWithDefault(self.header, 'EXPTIME', "1") == "NULL":
           self.logger.errorPair("Could not get", "EXPTIME")
           raise

       if self.safeDictionaryAccessWithDefault(self.header, 'FILTERID', "g.0000") == "NULL":
           self.logger.errorPair("Could not get", "FILTERID")
           raise
           
       # MJD-OBS is the exposure start, EXTIME / 172800 = (EXPTIME sec / 84600 sec/day) / 2
       self.obsTime = float(self.header['MJD-OBS']) + (float(self.header['EXPTIME']) / 172800.0)
       
       ## MEH md5sum options test 
       self.md5sum = self.fits.getMd5sum()
       
       # set up some defauts
       self.totalNumPhotoRef = 0

       self.filter = self.header['FILTERID'][0:1]
       self.filterID = self.scratchDb.getFilterID(self.filter) 

       # insert what we know about this stack batch into the stack table
       self.ippToPspsDb.insertDetectionMeta(self.batchID, self.expID, self.filter)

       # dump stuff to log
       self.logger.infoPair("Cam ID", "%d" % self.id)
       self.logger.infoPair("Exp ID", "%d" % self.expID)
       self.logger.infoPair("Exp name", "%s" % self.expName)
       self.logger.infoPair("Distribution group", "%s" % self.distGroup)

    '''
    Populates the FrameMeta table, mainly from dictionary values found in IPP FITS header
    '''
    def populateFrameMeta(self):
        self.logger.infoPair("Proccesing table", "FrameMeta")

        sqlLine = sqlUtility("INSERT INTO FrameMeta (")

        sqlLine.group("frameID",               str(self.expID));
        sqlLine.group("frameName",             self.expName);
        sqlLine.group("ippChipID",             str(self.chipID));
        sqlLine.group("ippCamID",              str(self.id));
        sqlLine.group("ippWarpID",             str(self.warpID));
        sqlLine.group("cameraID",              "1");
        sqlLine.group("cameraConfigID",        "1");
        sqlLine.group("telescopeID",           "1");
        sqlLine.group("analysisVer",           self.analysisVer);
        sqlLine.group("md5sum",                str(self.md5sum))

        # these lines should write "NULL" if the header contains NaN -- NULLs are converted below
        # to -999 for the sql database (a terrible solution, but mandatory?)

        sqlLine.group("photoScat",             self.getKeyFloat(self.header, "%.8f", 'ZPT_ERR'));
        sqlLine.group("expStart",              self.getKeyFloat(self.header, "%.10f", 'MJD-OBS'));
        sqlLine.group("expTime",               self.getKeyFloat(self.header, "%.8f", 'EXPREQ'));
        sqlLine.group("airmass",               self.getKeyFloat(self.header, "%.8f", 'AIRMASS'));
        sqlLine.group("raBore",                self.getKeyFloat(self.header, "%.10f", 'RA'));
        sqlLine.group("decBore",               self.getKeyFloat(self.header, "%.10f", 'DEC'));
        sqlLine.group("ctype1",                self.getKeyValue(self.header, 'CTYPE1'));
        sqlLine.group("ctype2",                self.getKeyValue(self.header, 'CTYPE2'));
        sqlLine.group("crval1",                self.getKeyFloat(self.header, "%.8f", 'CRVAL1'));
        sqlLine.group("crval2",                self.getKeyFloat(self.header, "%.8f", 'CRVAL2'));
        sqlLine.group("crpix1",                self.getKeyFloat(self.header, "%.8f", 'CRPIX1'));
        sqlLine.group("crpix2",                self.getKeyFloat(self.header, "%.8f", 'CRPIX2'));
        sqlLine.group("cdelt1",                self.getKeyFloat(self.header, "%.8e", 'CDELT1'));
        sqlLine.group("cdelt2",                self.getKeyFloat(self.header, "%.8e", 'CDELT2'));
        sqlLine.group("pc001001",              self.getKeyFloat(self.header, "%.8e", 'PC001001'));
        sqlLine.group("pc001002",              self.getKeyFloat(self.header, "%.8e", 'PC001002'));
        sqlLine.group("pc002001",              self.getKeyFloat(self.header, "%.8e", 'PC002001'));
        sqlLine.group("pc002002",              self.getKeyFloat(self.header, "%.8e", 'PC002002'));
        sqlLine.group("polyOrder",             self.getKeyValue(self.header, 'NPLYTERM'));
        sqlLine.group("pca1x3y0",              self.getKeyFloat(self.header, "%.8e", 'PCA1X3Y0'));
        sqlLine.group("pca1x2y1",              self.getKeyFloat(self.header, "%.8e", 'PCA1X2Y1'));
        sqlLine.group("pca1x1y2",              self.getKeyFloat(self.header, "%.8e", 'PCA1X1Y2'));
        sqlLine.group("pca1x0y3",              self.getKeyFloat(self.header, "%.8e", 'PCA1X0Y3'));
        sqlLine.group("pca1x2y0",              self.getKeyFloat(self.header, "%.8e", 'PCA1X2Y0'));
        sqlLine.group("pca1x1y1",              self.getKeyFloat(self.header, "%.8e", 'PCA1X1Y1'));
        sqlLine.group("pca1x0y2",              self.getKeyFloat(self.header, "%.8e", 'PCA1X0Y2'));
        sqlLine.group("pca2x3y0",              self.getKeyFloat(self.header, "%.8e", 'PCA2X3Y0'));
        sqlLine.group("pca2x2y1",              self.getKeyFloat(self.header, "%.8e", 'PCA2X2Y1'));
        sqlLine.group("pca2x1y2",              self.getKeyFloat(self.header, "%.8e", 'PCA2X1Y2'));
        sqlLine.group("pca2x0y3",              self.getKeyFloat(self.header, "%.8e", 'PCA2X0Y3'));
        sqlLine.group("pca2x2y0",              self.getKeyFloat(self.header, "%.8e", 'PCA2X2Y0'));
        sqlLine.group("pca2x1y1",              self.getKeyFloat(self.header, "%.8e", 'PCA2X1Y1'));
        sqlLine.group("pca2x0y2",              self.getKeyFloat(self.header, "%.8e", 'PCA2X0Y2'));

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

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

        self.scratchDb.updateAllRows("FrameMeta", "batchID", str(self.batchID))
        self.scratchDb.updateAllRows("FrameMeta", "surveyID", str(self.surveyID))
        self.scratchDb.updateFilterID("FrameMeta", self.filter)
        self.scratchDb.updateAllRows("FrameMeta", "processingVersion", str(self.skychunk.processingVersion))
        return True

    '''
    Populates the ImageMeta table for this OTA
    '''
    def populateImageMetaTable(self, ota, header):

        # does this chip exist in the DVO image table? (if NOT, then skip it)
        if not self.scratchDb.haveThisChip(self.imageIDs[ota], self.sourceIDs[ota]):
            self.logger.info("| %5s |            ------------- Chip not in DVO : rejecting ------------                    |" % ota)
            return False

        # the supplied 'header' matches this chip
        # self.header is the PHU header for this smf

        tableName = "ImageMeta_" + ota
        
        # we drop the table before calling this function so it is not left behind on failure
        sql = "CREATE TABLE " + tableName + " LIKE ImageMeta"
        self.scratchDb.execute(sql)

        if (ota[0:2] == "XY"): ccdID = ota[2:4]
        else: ccdID = 0

        ### any operations below which act on the output of getKeyFloat need to be 
        ### able to handle NULL results.

        # XXX hard-wired platescale : 0.257
        pltscale = 0.257
        pltscale2 = pltscale * pltscale

        # NOTE: getKeyFloat returns the header value if it is a float,
        # -999 if the value is missing and "NULL" if the value in the
        # header is a NaN.  any math operations below need to handle
        # these cases correctly

        # NOTE 2: getKeyFloat NOW returns the header value if it is a
        # float or "NULL" if the value is missing or if the value in
        # the header is a NaN.  any math operations below need to
        # handle these cases correctly

        psfFwhmMajor = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ')
        if (psfFwhmMajor != "NULL"): psfFwhmMajor *= pltscale

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

        psfFWHM = "NULL"
        if (psfFwhmMajor != "NULL") and (psfFwhmMinor != "NULL"):
            psfFWHM = 0.5*(psfFwhmMajor + psfFwhmMinor)

        psfmodel_name = self.getKeyValue(header, 'PSFMODEL')
        psfmodelID    = self.scratchDb.getFitModelID(psfmodel_name)

        ast_cdx = self.getKeyFloat(header, "%.8f",'AST_CDX')
        ast_cdy = self.getKeyFloat(header, "%.8f",'AST_CDY')

        astroscat = "NULL"
        if (ast_cdx != "NULL") and (ast_cdy != "NULL"):
            astroscat = math.sqrt(ast_cdx**2 + ast_cdy**2)
        
        # Convert detectionThreshold to appropriate magnitudes
        magref  = self.getKeyFloat(header, "%.8f", 'DETEFF.MAGREF')
        expTime = self.getKeyFloat(header, "%.8f", 'EXPTIME')
        zp      = self.getKeyFloat(header, "%.8f", 'ZPT_OBS')

        # zp correction from DVO
        zpImage = self.scratchDb.getImageZeroPoint(self.imageIDs[ota])

        if (zpImage == "NULL"):
            zpImage = zp

        detectionThreshold = "NULL"
        if (magref != "NULL") and (zpImage != "NULL") and (expTime != "NULL"):
            detectionThreshold = magref + zpImage + 2.5 * math.log10(expTime)
        
        # other header fields which depend on pltscale:
        momentMajor =        self.getKeyFloat(header, "%.8f", 'IQ_FW1')
        momentMinor =        self.getKeyFloat(header, "%.8f", 'IQ_FW2')
        momentM2C =          self.getKeyFloat(header, "%.8f", 'IQ_M2C')
        momentM2S =          self.getKeyFloat(header, "%.8f", 'IQ_M2S')
        momentM3 =           self.getKeyFloat(header, "%.8f", 'IQ_M3')
        momentM4 =           self.getKeyFloat(header, "%.8f", 'IQ_M4')

        if (momentMajor != "NULL"): momentMajor *= pltscale
        if (momentMinor != "NULL"): momentMinor *= pltscale
        if (momentM2C   != "NULL"): momentM2C   *= pltscale2
        if (momentM2S   != "NULL"): momentM2S   *= pltscale2
        if (momentM3    != "NULL"): momentM3    *= pltscale2
        if (momentM4    != "NULL"): momentM4    *= pltscale2

        # insert image metadata into table
        sqlLine = sqlUtility("INSERT INTO " + tableName + "(")

        sqlLine.group("frameID",            self.expID)
        sqlLine.group("ccdID",              ccdID)
        sqlLine.group("bias",               self.bias)
        sqlLine.group("biasScat",           self.biasScat)
        sqlLine.group("sky",                self.getKeyFloat(header, "%.8f", 'MSKY_MN'))
        sqlLine.group("skyScat",            self.getKeyFloat(header, "%.8f", 'MSKY_SIG'))
        sqlLine.group("detectionThreshold", detectionThreshold)
        sqlLine.group("astroScat",          astroscat)
        sqlLine.group("photoScat",          self.getKeyFloat(header, "%.8f", 'ZPT_ERR'))
        sqlLine.group("photoZero",          zpImage)
        sqlLine.group("nAstroRef",          self.getKeyValue(header, 'NASTRO'))
        sqlLine.group("nPhotoRef",          self.getKeyValue(header, 'NASTRO'))
        sqlLine.group("nAxis1",             self.getKeyInt(header, 0, 'NAXIS1'))
        sqlLine.group("nAxis2",             self.getKeyInt(header, 0, 'NAXIS2'))
        sqlLine.group("psfModelID",         psfmodelID)
        sqlLine.group("psfFWHM",            psfFWHM)
        sqlLine.group("psfWidMajor",        psfFwhmMajor)
        sqlLine.group("psfWidMinor",        psfFwhmMinor)
        sqlLine.group("psfTheta",           self.getKeyFloat(header, "%.8f", 'ANGLE'))
        sqlLine.group("momentMajor",        momentMajor)
        sqlLine.group("momentMinor",        momentMinor)
        sqlLine.group("momentM2C",          momentM2C)
        sqlLine.group("momentM2S",          momentM2S)
        sqlLine.group("momentM3",           momentM3)
        sqlLine.group("momentM4",           momentM4)
        sqlLine.group("apResid",            self.getKeyFloat(header, "%.8f", 'APMIFIT'))
        sqlLine.group("dapResid",           self.getKeyFloat(header, "%.8f", 'DAPMIFIT'))
        sqlLine.group("detectorID",         self.getKeyValue(header, 'DETECTOR'))
        sqlLine.group("qaFlags",            self.scratchDb.getDvoImageFlags(header['IMAGEID']))
        sqlLine.group("detrend1",           self.getKeyValue(header, 'DETREND.MASK'))
        sqlLine.group("detrend2",           self.getKeyValue(header, 'DETREND.DARK'))
        sqlLine.group("detrend3",           self.getKeyValue(header, 'DETREND.FLAT'))

        # DETREND.FRINGE is not required : check if it is in the header before setting
        if 'DETREND.FRINGE' in header:
            sqlLine.group("detrend4",         self.getKeyValue(header, 'DETREND.FRINGE'))

        sqlLine.group("detrend5",         self.getKeyValue(header, 'DETREND.NOISEMAP'))
        sqlLine.group("detrend6",         self.getKeyValue(header, 'DETREND.NONLIN'))
        sqlLine.group("detrend7",         self.getKeyValue(header, 'DETREND.VIDEODARK'))
        # this needs to be calculated:  some combination of PTRN_ROW and PTRN_CON  (These are bools)
        # sqlLine.group("detrend8",         " ")

        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'))
        sqlLine.group("polyOrder",        self.getKeyInt(header, 0, 'NPLYTERM'))

        nPolyterm = int(self.getKeyInt(header, 0, 'NPLYTERM'))
        if nPolyterm > 1:
            sqlLine.group("pca1x2y0",         self.getKeyFloat(header, "%.8e", 'PCA1X2Y0'))
            sqlLine.group("pca1x1y1",         self.getKeyFloat(header, "%.8e", 'PCA1X1Y1'))
            sqlLine.group("pca1x0y2",         self.getKeyFloat(header, "%.8e", 'PCA1X0Y2'))
            sqlLine.group("pca2x2y0",         self.getKeyFloat(header, "%.8e", 'PCA2X2Y0'))
            sqlLine.group("pca2x1y1",         self.getKeyFloat(header, "%.8e", 'PCA2X1Y1'))
            sqlLine.group("pca2x0y2",         self.getKeyFloat(header, "%.8e", 'PCA2X0Y2'))
        else:
            sqlLine.group("pca1x2y0",         "-999")
            sqlLine.group("pca1x1y1",         "-999")
            sqlLine.group("pca1x0y2",         "-999")
            sqlLine.group("pca2x2y0",         "-999")
            sqlLine.group("pca2x1y1",         "-999")
            sqlLine.group("pca2x0y2",         "-999")
            
        if nPolyterm > 2:
            sqlLine.group("pca1x3y0",         self.getKeyFloat(header, "%.8e", 'PCA1X3Y0'))
            sqlLine.group("pca1x2y1",         self.getKeyFloat(header, "%.8e", 'PCA1X2Y1'))
            sqlLine.group("pca1x1y2",         self.getKeyFloat(header, "%.8e", 'PCA1X1Y2'))
            sqlLine.group("pca1x0y3",         self.getKeyFloat(header, "%.8e", 'PCA1X0Y3'))
            sqlLine.group("pca2x3y0",         self.getKeyFloat(header, "%.8e", 'PCA2X3Y0'))
            sqlLine.group("pca2x2y1",         self.getKeyFloat(header, "%.8e", 'PCA2X2Y1'))
            sqlLine.group("pca2x1y2",         self.getKeyFloat(header, "%.8e", 'PCA2X1Y2'))
            sqlLine.group("pca2x0y3",         self.getKeyFloat(header, "%.8e", 'PCA2X0Y3'))
        else:
            sqlLine.group("pca1x3y0",         "-999")
            sqlLine.group("pca1x2y1",         "-999")
            sqlLine.group("pca1x1y2",         "-999")
            sqlLine.group("pca1x0y3",         "-999")
            sqlLine.group("pca2x3y0",         "-999")
            sqlLine.group("pca2x2y1",         "-999")
            sqlLine.group("pca2x1y2",         "-999")
            sqlLine.group("pca2x0y3",         "-999")

        sql = sqlLine.make(") VALUES ( ", ")")
        self.scratchDb.execute(sql)

        self.logger.debugPair("inserted into mysql", "ImageMeta")

        self.scratchDb.updateRecalStats(tableName, header['IMAGEID'], str(pltscale));
        self.scratchDb.updateFilterID(tableName, self.filter)
        self.scratchDb.updateAllRows(tableName, "processingVersion", str(self.skychunk.processingVersion))

        if 'NASTRO' in header: self.totalNumPhotoRef = self.totalNumPhotoRef + int(header['NASTRO'])
        self.scratchDb.replaceNullsInThisColumn(tableName, "polyOrder", "0")

        self.logger.debugPair("completed", "ImageMeta")
        return True

    '''
    Populates the ImageDetEffMeta table for this OTA
    '''
    def populateImageDetEffMetaTablePart1(self, ota, header, deteffHeader):
        # print "---- *** populate ImageDetEffMetaTablePart1"
        ippTableName = ota + "_deteff"
        tableName = "ImageDetEffMeta_" + ota
        self.scratchDb.dropTable(tableName)
        # we drop the table before calling this functoin so it is not left behind on failure
        sql = "CREATE TABLE " + tableName + " LIKE ImageDetEffMeta"
        try: self.scratchDb.execute(sql)
        except: pass

        if (ota[0:2] == "XY"): ccdID = ota[2:4]
        else: ccdID = 0

        # insert image metadata into table
        sqlLine = sqlUtility("INSERT INTO " + tableName + "(")

        # CZW: 20151110 This needs to get the deteff header as well.
        nInjected = int(self.getKeyInt(deteffHeader, 0, 'DETEFF.NUM'))
        magref  = self.getKeyFloat(deteffHeader, "%.8f", 'DETEFF.MAGREF')
        expTime = self.getKeyFloat(header, "%.8f", 'EXPTIME')
        zp      = self.getKeyFloat(header, "%.8f", 'ZPT_OBS')

        # zp correction comes from DVO (unless it is not found)
        zpImage = self.scratchDb.getImageZeroPoint(self.imageIDs[ota])

        if (zpImage == "NULL"):
            zpImage = zp

        detectionThreshold = "NULL"
        if (magref != "NULL") and (zpImage != "NULL") and (expTime != "NULL"):
            detectionThreshold = magref + zpImage + 2.5 * math.log10(expTime)

        sqlLine.group("frameID",          str(self.expID))
        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

        # ok, this is a bit tricky - Ideally I just want this all in one table, however, the way the table is layed out is 90 degrees from how I want it.
        # the steps out of this mess are as follows:
        # give an index to XYxy_deteff (already done, called table_index)
        # do a loop to grab the 5 colums and fill them

        # for loop
        return True

    '''
    Populates the ImageDetEffMeta table for this OTA
    '''
    def populateImageDetEffMetaTablePart2(self, ota):
        # print "---- *** populate ImageDetEffMetaTablePart2"

        ippTableName = ota + "_deteff"
        tableName = "ImageDetEffMeta_" + ota

        # ok, this is a bit tricky - Ideally I just want this all in one table, however, the way the table is layed out is 90 degrees from how I want it.
        # the steps out of this mess are as follows:
        # give an index to XYxy_deteff (already done, called table_index)
        # do a loop to grab the 5 colums and fill them

        # for loop
        for i in xrange(1, 14):
                       stringint = str(i)
                       if i < 10:
                          stringint = '0'+str(i)
      
#                      stringint.rjust(2, '0')
                       sql = "UPDATE " +tableName+ " , " + ippTableName + " SET   \
                             offset"+stringint + " = OFFSET  \
                             ,counts" +stringint + " = COUNTS \
                             ,diffMean" + stringint + " = DIFF_MEAN \
                             ,diffStdev" + stringint + " = DIFF_STDEV \
                             ,errMean" + stringint + " = ERR_MEAN \
                             WHERE table_index = " + str(i)   
 
#                      sqlLine = sqlUtility("UPDATE " + tableName + "," + ippTableName " SET ")
#                      sqlLine.group("offset ="+stringint, "OFFSET")
#                      sqlLine.group(",counts ="+stringint, "COUNTS")
#                      sqlLine.group(",diffMean ="+stringint, "DIFF_MEAN")
#                      sqlLine.group(",diffStdev ="+stringint, "DIFF_STDEV")
#                      sqlLine.group(",errMean ="+stringint, "ERR_MEAN")
#                      sql = sqlLine.makeRaw(" SELECT ", "FROM " + ippTableName + " WHERE table_index = " + str(i) + " and frameID = " + str(self.expID))

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

        
    '''
    Populates the Detection table for this OTA
    '''
    def populateDetectionTable(self, ota, results):

        pspsTableName = "Detection_" + ota
        ippTableName = ota + "_psf"
       
        results['ORIGINALTOTAL'] = self.scratchDb.getRowCount(ippTableName)

        # drop then re-create table
        self.scratchDb.dropTable(pspsTableName)
        sql = "CREATE TABLE " + pspsTableName + " LIKE Detection"
        try: self.scratchDb.execute(sql)
        except: pass
       
        # 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

        BEFORE = self.scratchDb.getRowCount(ippTableName)
        results['SATDET'] = 0 

        expTimeString = str(self.header['EXPTIME'])

        # 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?

        # EAM 20150925: the code below populates the mysql temporary smf table with
        # instrumental fluxes in counts/sec and calibrated sizes (arcsec).
        # below, we convert instrumental fluxes to Jy

        # NOTE: math operations below take place in SQL, not JYTHON.  thus we use (e.g.) 
        # abs() not math.fabs()

        sqlLine.group("ippDetectID",      "IPP_IDET")                                                
#       sqlLine.group("randomDetID",      "FLOOR(RAND("+str(self.batchID)+")*9223372036854775807)")                       
        sqlLine.group("randomDetID",      "RAND("+str(self.batchID)+")")                       
        sqlLine.group("filterID",         str(self.filterID))                                        
        sqlLine.group("surveyID",         str(self.surveyID))                                        
        sqlLine.group("obsTime",          str(self.obsTime))                                         
        sqlLine.group("xPos",             "X_PSF")                                                   
        sqlLine.group("yPos",             "Y_PSF")                                                   
        sqlLine.group("xPosErr",          "X_PSF_SIG")                                               
        sqlLine.group("yPosErr",          "Y_PSF_SIG")                                               
        if (self.v5cols):
            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.257")                                      
            # sqlLine.group("posAngle",     "-999")                                      
            sqlLine.group("raErr",        "X_PSF_SIG * 0.257") 
            sqlLine.group("decErr",       "Y_PSF_SIG * 0.257")
        sqlLine.group("extNSigma",        "EXT_NSIGMA")    
        sqlLine.group("psfFlux",          "PSF_INST_FLUX / " + expTimeString)
        sqlLine.group("psfFluxErr",       "PSF_INST_FLUX_SIG / " + expTimeString)
        if (self.v5cols):
            sqlLine.group("psfMajorFWHM", "PSF_FWHM_MAJ * abs(PLTSCALE)")                                               
            sqlLine.group("psfMinorFWHM", "PSF_FWHM_MIN * abs(PLTSCALE)")                                               
            sqlLine.group("psfCore",      "PSF_CORE")                                                    
        sqlLine.group("psfTheta",         "PSF_THETA")                                               
        sqlLine.group("psfQf",            "PSF_QF")                                                  
        sqlLine.group("psfQfPerfect",     "PSF_QF_PERFECT")                                
        sqlLine.group("psfChiSq",         "PSF_CHISQ / PSF_NDOF")                                     
        sqlLine.group("psfLikelihood",    "psfLikelihood(EXT_NSIGMA)")                               
        sqlLine.group("momentXX",         "MOMENTS_XX * PLTSCALE * PLTSCALE")                                              
        sqlLine.group("momentXY",         "MOMENTS_XY * PLTSCALE * PLTSCALE")                                              
        sqlLine.group("momentYY",         "MOMENTS_YY * PLTSCALE * PLTSCALE")                                              
        sqlLine.group("momentR1",         "MOMENTS_R1 * abs(PLTSCALE)")                                              
        sqlLine.group("momentRH",         "MOMENTS_RH * sqrt(abs(PLTSCALE))")                                              
        sqlLine.group("momentM3C",        "MOMENTS_M3C * PLTSCALE * PLTSCALE")                                             
        sqlLine.group("momentM3S",        "MOMENTS_M3S * PLTSCALE * PLTSCALE")                                             
        sqlLine.group("momentM4C",        "MOMENTS_M4C * PLTSCALE * PLTSCALE")                                             
        sqlLine.group("momentM4S",        "MOMENTS_M4S * PLTSCALE * PLTSCALE") 
        if (self.v5cols):
            sqlLine.group("apFlux",       "AP_FLUX / " + expTimeString)
            sqlLine.group("apFluxErr",    "AP_FLUX_SIG / " + expTimeString)
            sqlLine.group("apFillF",      "AP_NPIX / (3.14159265359 * AP_MAG_RADIUS * AP_MAG_RADIUS)")
        sqlLine.group("apRadius",         "AP_MAG_RADIUS * abs(PLTSCALE)")
        sqlLine.group("kronFlux",         "KRON_FLUX / " + expTimeString)
        sqlLine.group("kronFluxErr",      "KRON_FLUX_ERR / " + expTimeString)
        sqlLine.group("kronRad",          "MOMENTS_R1 * 2.5 * abs(PLTSCALE)")
        sqlLine.group("sky",              "SKY / PLTSCALE / PLTSCALE / " + expTimeString)
        sqlLine.group("skyErr",           "SKY_SIGMA / PLTSCALE / PLTSCALE / " + expTimeString)
        sqlLine.group("infoFlag",         "FLAGS")                          
        sqlLine.group("infoFlag2",        "FLAGS2")                          
        sqlLine.group("processingVersion", str(self.skychunk.processingVersion))                  

        sql = sqlLine.makeRaw(") SELECT ", " FROM " + ippTableName)

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

        # NOTE : Flux limits : in the current PSPS schema, negative fluxes 
        # cause problems for sql queries which work in mags as SQL cannot do
        # something like (f < 0.0) ? -999 : -2.5*log10(f)

        # users need to use something like mag = -2.5*log10(abs(f)) and save a flag value (f > 0 as posDetection)

        # we don't delete these anymore
        results['NULLINSTFLUX'] = 0;

        return True

    '''
    Populates the SkinnyObject table for this OTA
    '''
    def populateSkinnyObjectTable(self, ota):

        tableName = "SkinnyObject_" + ota
        
        # drop then re-create table
        self.scratchDb.dropTable(tableName)
        sql = "CREATE TABLE " + tableName + " LIKE SkinnyObject"
        try: self.scratchDb.execute(sql)
        except: pass

        # insert all detections into table
        sql = "INSERT INTO " + tableName + " ( \
               objID \
               ,ippObjID \
               ,surveyID \
               ) \
               SELECT \
               objID \
               ,ippObjID \
               ,surveyID \
               FROM Detection_" + ota
        self.scratchDb.execute(sql)

        self.scratchDb.updateAllRows(tableName, "processingVersion", str(self.skychunk.processingVersion))
        return True

    '''
    Populates the SkinnyObject table for this OTA
    '''
    def populateSkinnyObjectTable(self, ota):

        tableName = "SkinnyObject_" + ota
        
        # drop then re-create table
        self.scratchDb.dropTable(tableName)
        sql = "CREATE TABLE " + tableName + " LIKE SkinnyObject"
        try: self.scratchDb.execute(sql)
        except: pass

        # insert all detections into table
        sql = "INSERT INTO " + tableName + " ( \
               objID \
               ,ippObjID \
               ,surveyID \
               ) \
               SELECT \
               objID \
               ,ippObjID \
               ,surveyID \
               FROM Detection_" + ota
        self.scratchDb.execute(sql)

        self.scratchDb.updateAllRows(tableName, "processingVersion", str(self.skychunk.processingVersion))
        return True

    '''
    Applies indexes and other constraints to the PSPS tables
    '''
    def alterPspsTablesChip(self, chipname, extname, x, y):

        # drop the ImageMeta_ table first, or we can leave an invalid table behind
        tableName = "ImageMeta_" + chipname
        
        # drop then re-create table
        self.scratchDb.dropTable(tableName)

        # load corresponding header into memory
        header = self.fits.findAndReadHeader(extname, self.config.test)
        if not header:
            # XXX There is no way to check if we SHOULD find a given
            # chip.  We have to accept the absence of the header as
            # information that the chip is not available.  This
            # message is too verbose, only print in test mode
            if self.config.test: self.logger.errorPair("No header found for chip", chipname)
            return False
#        self.logger.infoPair("extname is ",self.fits[0])

        # check we have valid sourceID/imageID pair from the header
        # hardwire it
        if self.getKeyValue(header, 'SOURCEID') == "NULL": 
            header['SOURCEID']=0
            self.logger.infoPair("no sourceid, using:",header['SOURCEID'])
        if self.getKeyValue(header, 'IMAGEID') == "NULL": return False
        
        # store sourceID/imageID combo in Db so DVO can look up later (XXX deprecated?)
        # self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID'])
            
        # store these for later
        self.imageIDs[chipname] = header['IMAGEID']
        self.sourceIDs[chipname] = header['SOURCEID']
        #self.sourceIDs[chipname] = 33
            
        # populate ImageMeta
        # XXX EAM 20151024 : 
        if not self.populateImageMetaTable(chipname, header):
            self.logger.infoPair("skipping chip: ", chipname)
            return False

        self.updateImageID("ImageMeta_" + chipname, x, y) 

        deteffHeader = self.fits.findAndReadHeader(chipname + ".deteff", self.config.test)

        # XXX EAM 20151024 : problem is here?
        self.populateImageDetEffMetaTablePart1(chipname, header, deteffHeader)
        self.updateImageID("ImageDetEffMeta_" + chipname, x, y)
        
        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("Detection", "objID")
        self.scratchDb.makeColumnPrimaryKey("Detection", "ippDetectID")

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

        # loop through all OTAs and populate ImageMeta extensions
        self.logger.infoPair("Reading", "FITS headers")
        self.logger.infoPair("Populating table", "ImageMeta")

        if self.config.camera == "gpc1":
            for x in range(self.startX, self.endX):
                for y in range(self.startY, self.endY):
                    
                    ## for a quick test, just do the first chip:
                    if False and self.config.test and self.config.camera == "gpc1":
                        if x != 0 or y != 1: continue

                    # dodge the corners
                    if x==0 and y==0: continue
                    if x==0 and y==7: continue
                    if x==7 and y==0: continue
                    if x==7 and y==7: continue
                    
                    ota = "XY%d%d" % (x, y)
                    self.alterPspsTablesChip(ota, ota + ".hdr", x, y)

        self.alterPspsTablesChip("Chip", "Chip.hdr", 0, 0)

        # 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 Detection CHANGE dec_ `dec` double")

        return True

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

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

        for x in range(self.startX, self.endX):
            for y in range(self.startY, self.endY):

                ## for a quick test, just do the first chip:
                if False and self.config.test and self.config.camera == "gpc1":
                    if x != 0 or y != 1: continue

                # dodge the corners
                if x==0 and y==0: continue
                if x==0 and y==7: continue
                if x==7 and y==0: continue
                if x==7 and y==7: continue

                cmfTable = "XY%d%d.psf" % (x, y)
                mysTable = "XY%d%d_psf" % (x, y)
                if cmfTable not in self.tablesLoaded: continue

                self.scratchDb.createIndex(mysTable, "IPP_IDET")

        cmfTable = "XY%d%d.psf" % (x, y)
        mysTable = "XY%d%d_psf" % (x, y)
        if cmfTable in self.tablesLoaded: 
            self.scratchDb.createIndex(mysTable, "IPP_IDET")

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


    '''
    Updates provided table with DVO IDs from DVO table
    '''
    def updateDvoIDs(self, table, externID):
        self.logger.info("getting imageID")
        imageID = self.scratchDb.getImageIDFromExternID(externID)
        self.logger.info("Updating table '" + table + "' with DVO IDs using imageID = %d" % imageID)

        # XXX : EAM 20150925 : is this IGNORE necessary?
        sqlLine = sqlUtility("UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET")
        sqlLine.group("a.objID",        "b.objID")
        sqlLine.group("a.detectID",     "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.zp",           "b.zpPSF")
        sqlLine.group("a.telluricExt",  "b.telluricExt")
        sqlLine.group("a.airmass",      "b.airmass")
        sqlLine.group("a.infoFlag3",    "b.flags")
        sqlLine.group("a.expTime",      "b.expTime")
        sqlLine.group("a.psfFlux",      "a.psfFlux     * b.zpFactorPSF")
        sqlLine.group("a.psfFluxErr",   "a.psfFluxErr  * b.zpFactorPSF")
        sqlLine.group("a.apFlux",       "a.apFlux      * b.zpFactorAPER")
        sqlLine.group("a.apFluxErr",    "a.apFluxErr   * b.zpFactorAPER")
        sqlLine.group("a.kronFlux",     "a.kronFlux    * b.zpFactorAPER")
        sqlLine.group("a.kronFluxErr",  "a.kronFluxErr * b.zpFactorAPER")
        sqlLine.group("a.sky",          "a.sky         * b.zpFactorAPER")
        sqlLine.group("a.skyErr",       "a.skyErr      * b.zpFactorAPER")

        sql = sqlLine.makeEquals("WHERE a.ippDetectID = b.ippDetectID AND b.imageID = " + str(imageID))

        ## a.psfFlux      = a.psfFlux * 3630.78 * POW(10.0, -0.4*b.zpPSF), \

        # instrumental flux vs Janskies:
        # b.zpPSF is defined to include the airmass term and zero point offset from relative calibration or ubercal
        # (see dvopsps/src/insert_detections_dvopsps_catalog.c:311
        # mag_AB = -2.5*log(flux_DN/sec) + ZP
        # flux_Jy = flux_cgs * 10^23
        # flux_cgs = ten(-0.4*(mag_AB + 48.6)) (~by definition, ~Vega flux in V-band)
        # flux_cgs = ten(-0.4*mag_AB - 19.44)
        # flux_cgs = ten(-0.4*mag_AB) * 3.63078e-20
        # flux_Jy  = ten(-0.4*mag_AB) * 3630.78
        # flux_Jy  = ten(-0.4*(-2.5*log(flux_DN/sec) + ZP)) * 3630.78
        # flux_Jy  = flux_DN/sec * ten(-0.4*ZP) * 3630.78
        # NOTE: update dvopsps to populate zpFactor = 3630.78 * ten(-0.4*zp) 

        self.scratchDb.execute(sql)
        return True

    '''
    Updates table and generates pspsuniqueids
    '''

    def updatePspsUniqueIDs(self,table, ota):
        #sql = "UPDATE "+table+" join (select @r:=@r+1 rownum, objID from \
        #(select @r:=0) r, "+table+" t) as foo using (objID) set \
        #uniquePspsP2id = ((" +str(self.batchID)+ "*1000000000 ) \
        #+ (" +str(ota) + "*10000000  )+rownum)"
        sql = "UPDATE "+table+" set uniquePspsP2id = (("+str(self.batchID)+"*1000000000 ) + (" +str(ota) + "*10000000  )+ row)"
        try: self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql',sql)
            raise
        return True
        
    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTablesChip(self, chipname, x, y, results, tables):
        # XXX EAM NOTE: drop tables Detection_* here so
        # they do not polute the db?
        # XXX or put an explicit drop at the end of the loop?

        # self.logger.infoTitle("Processing " + chipname)
        # this is a bit crude: if the chip is not present, this test will fail and the chip 
        # will be (correctly) skipped.  would be better to carry that information explicitly ("chip is missing")

        # XXX keep this in or not?
        # if not self.scratchDb.astrometricSolutionOK(chipname):
        #     self.logger.info("| %5s |            ------------- Bad astrometric solution : rejecting ------------                    |" % chipname)
        #     return False

        # does this chip exist in the DVO image table (if NOT, then skip it)
        if not self.scratchDb.haveThisChip(self.imageIDs[chipname], self.sourceIDs[chipname]):
            self.logger.info("| %5s |            ------------- Chip not in DVO : rejecting ------------                    |" % chipname)
            return False

        #self.logger.info("populate stuff ");
        # populate remainder of tables
        self.populateDetectionTable(chipname, results)
        #self.logger.info("successful populate ");
        # now add DVO IDs
        self.logger.infoPair("Adding 'row' column to ", "Detection_"+chipname)
        #detection has a primary index, so don't add, use this one
        self.scratchDb.addRowCountColumnNoKey("Detection_"+chipname, "row")
        self.logger.info("added row")
        self.updateDvoIDs("Detection_" + chipname, self.imageIDs[chipname])
        self.logger.info("updated dvoids")

        results['NULLOBJID'] = self.scratchDb.reportAndDeleteRowsWithNULLS("Detection_" + chipname, "objID")
        self.logger.info("deleted nulls")

        ota = x*10+y

        self.logger.info("add psps unique p2 ids")
        self.updatePspsUniqueIDs("Detection_" + chipname, ota)
        self.logger.infoPair("Dropping 'row' column from ", "Detection_"+chipname)
        self.scratchDb.dropColumn("Detection_"+chipname,"row")
        self.updateImageID("Detection_" + chipname, x, y)
        #self.logger.info("updateImageId")
        rowCount = self.scratchDb.getRowCount("Detection_" + chipname) 
        #self.logger.info("got row count")
        self.logger.info("| %5s | %13d | %13d | %13d | %13d | %13d |", 
                chipname, 
                results['ORIGINALTOTAL'], 
                results['SATDET'], 
                results['NULLINSTFLUX'], 
                results['NULLOBJID'],
                rowCount)
        self.totalOriginal     = self.totalOriginal + results['ORIGINALTOTAL']
        self.totalSatDet       = self.totalSatDet + results['SATDET']
        self.totalNulIInstFlux = self.totalNulIInstFlux + results['NULLINSTFLUX']
        self.totalNullObjID    = self.totalNullObjID + results['NULLOBJID']
        self.totalDetections   = self.totalDetections + rowCount

        fractionKept = float(rowCount) / float(results['ORIGINALTOTAL'])
        # print "kept ", str(fractionKept), " detections from ", chipname

        if fractionKept < 0.8:
            # print "Too many rejected detections (", str(fractionKept), ", fail on exposure for", chipname
            self.logger.infoPair("Too many rejected detections, fail on exposure for", chipname)
            self.skipBatch = True
            return False;

        #self.logger.info("updated totals")
        # check we have something in this Detection table TODO add this to table above
        if rowCount < 1:
            self.logger.infoPair("Empty table for chip, fail on exposure", chipname)
            self.skipBatch = True
            return False;

        # update ImageMeta with count of detections for this CHIPNAME and photoCodeID
        sql = "UPDATE ImageMeta_" + chipname + " \
               SET nDetect = %d, photoCalID = %d" % (self.scratchDb.getRowCount("Detection_" + chipname), self.scratchDb.getPhotoCalID(self.imageIDs[chipname]))
        self.scratchDb.execute(sql)
        
        # add these to list of tables to export later
        self.tablesToExport.append("ImageMeta_" + chipname)
        self.logger.info("export ImageMeta")

        self.tablesToExport.append("ImageDetEffMeta_" + chipname)
        self.logger.info("export ImageDetEffMeta")
        
        self.tablesToExport.append("Detection_" + chipname)
        self.logger.info("export Detection")

        tables.append("Detection_" + chipname)
        self.logger.info("updated detection")

        self.validChips.append(chipname)

        return True

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

        # print "********** ***************"
        # for table in self.tablesLoaded:
        #     print "table: " + str(table)
        # print "--------------------------"

        # loop through all OTAs again to update with DVO IDs
        self.skipBatch = False
        self.validChips = []    
        self.tablesToExport = []    
        self.tablesToExport.append("FrameMeta")
        tables = []    
        otaCount = 0
        results = {}
        self.totalOriginal = self.totalSatDet = self.totalNulIInstFlux = self.totalNullPeakFlux = self.totalNullObjID = self.totalDetections = 0
        self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+")
        self.logger.info("|  OTA  | Initial total |   Sat Det     | NULL instFlux | NULL obj ID   |  Remainder    |")
        self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+")
        for x in range(self.startX, self.endX):
            for y in range(self.startY, self.endY):
               
                ## for a quick test, just do the first chip:
                if False and self.config.test and self.config.camera == "gpc1":
                    if x != 0 or y != 1: continue

                # dodge the corners
                if x==0 and y==0: continue
                if x==0 and y==7: continue
                if x==7 and y==0: continue
                if x==7 and y==7: continue

                # skip chips which were not loaded
                ota = "XY%d%d" % (x, y)
                extension = ota + ".psf"
                if extension not in self.tablesLoaded: continue

                # skip chips which had a problem (e.g., not in DVO)
                if not self.populatePspsTablesChip(ota, x, y, results, tables):
                    if self.skipBatch: 
                        self.logger.error("fatal problem for exposure, skipping")
                        return False
                    continue
                                
                otaCount = otaCount + 1
                self.populateImageDetEffMetaTablePart2(ota)

        extension = "Chip.psf"
        if extension in self.tablesLoaded:
            if not self.populatePspsTablesChip("Chip", 0, 0, results, tables):
                print "--- skipped Chip.psf"
                if self.skipBatch: 
                    self.logger.error("fatal problem for exposure, skipping")
                    return False
            else:
                otaCount = otaCount + 1

            print "--- tried Chip.psf"

        # print totals
        self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+")
        self.logger.info("| Total | %13d | %13d | %13d | %13d | %13d | %13d |", 
                self.totalOriginal, 
                self.totalSatDet, 
                self.totalNulIInstFlux, 
                self.totalNullPeakFlux, 
                self.totalNullObjID,
                self.totalDetections)
        self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+")

        print "--- tried Chip.psf (2)"

        # if we only have one table export, i.e. FrameMeta, then get out of here (skip batch, but do not abort)
        if len(self.tablesToExport) == 1:
            self.skipBatch = True
            self.logger.error("No tables to export")
            return False

        # XXX EAM : turn this on (and fix up the code) if we need it 
        # self.removeDuplicateObjects()

        self.setMinMaxObjID(tables)

        # update FrameMeta with count OTAs in this file and total number of photometric reference sources
        sql = "UPDATE FrameMeta SET nOTA = %d, nPhotoRef = %d" % (otaCount, self.totalNumPhotoRef)
        self.scratchDb.execute(sql)
        #to make it stop
        #test = self.scratchDb.getRowCount("Object")
        #return False
        
        return True

    def removeDuplicateObjects(self):

        self.scratchDb.dropTable("SkinnyObject_All")

        sql = "CREATE TABLE SkinnyObject_All (objID bigint, ippObjID bigint, chipID char(16))"
        self.scratchDb.execute(sql)

        for chipname in self.validChips:
            
            sql = "INSERT INTO SkinnyObject_All (objID, ippObjID, chipID) \
                   SELECT objID, ippObjID, '" + chipname + "' from SkinnyObject_" + chipname
            self.scratchDb.execute(sql)

        Nduplicates = 0
        sql = "SELECT n, objID from (select count(objID) as n, objID from SkinnyObject_All group by objID) as tmp where n > 1"
        try:
            rs = self.scratchDb.executeQuery(sql)
            while (rs.next()):
                objID = rs.getInt(1)
                chipID = rs.getString(3)
                print "delete: ", str(objID), chipID
                Nduplicates += 1

            rs.close()
        except:
            print "failed to get duplicates"
            raise

        print "duplicates found: ", str(Nduplicates)
        return True

    '''
    Updates imageID {EXP_ID}{OTA} in the provided table
    '''
    def updateImageID(self, tableName, x, y):

        sql = "UPDATE " + tableName + " SET imageID = %d%d%d" % (self.expID, x, y) 
        self.scratchDb.execute(sql)
        return True

    '''
    Overriding this method
    '''
    def reportNullsInAllPspsTables(self, showPartials):

        # loops round all imported tables, but we want to check one OTA's worth
        for table in self.pspsTables:
            if table.name == "FrameMeta": self.scratchDb.reportNulls(table.name, showPartials)
            else: self.scratchDb.reportNulls(table.name + "_XY33", showPartials)
        return True

    '''
    This function reads the cmf/smf file and loads it into the database as its own table
    Overriding this method. Filter to only import *.psf extensions
    '''
    def importIppTables(self, filter=""):
        
        ## regex only works for -stilts import
        regex = ".*"
        
        ## for a quick test, just do the first chip:
        if self.config.test and self.config.camera == "gpc1":
            regex = "XY*"
            
        print "my ID: " + str(self.id)

        if (not self.v5cols):
            # XXX EAM NOTE : this is fragile : requires PS1_V4
            columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG                   PSF_INST_FLUX PSF_INST_FLUX_SIG       PSF_MAJOR PSF_MINOR PSF_THETA          PSF_QF PSF_QF_PERFECT PSF_CHISQ EXT_NSIGMA MOMENTS_XX MOMENTS_XY MOMENTS_YY MOMENTS_R1 MOMENTS_RH MOMENTS_M3C MOMENTS_M3S MOMENTS_M4C MOMENTS_M4S                             AP_MAG_RADIUS KRON_FLUX KRON_FLUX_ERR SKY SKY_SIGMA FLAGS FLAGS2"
        else:
            # XXX EAM NOTE : this is fragile : requires PS1_V5
            columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG POSANGLE PLTSCALE PSF_INST_FLUX PSF_INST_FLUX_SIG PSF_FWHM_MAJ PSF_FWHM_MIN PSF_THETA PSF_CORE PSF_QF PSF_QF_PERFECT PSF_CHISQ EXT_NSIGMA MOMENTS_XX MOMENTS_XY MOMENTS_YY MOMENTS_R1 MOMENTS_RH MOMENTS_M3C MOMENTS_M3S MOMENTS_M4C MOMENTS_M4S AP_FLUX AP_FLUX_SIG AP_NPIX AP_MAG_RADIUS KRON_FLUX KRON_FLUX_ERR SKY SKY_SIGMA FLAGS FLAGS2"

        ## the list of columns above works fine for *.psf, but breaks the *.deteff tables
        ## just read all columns
        columns = "*"

        return super(DetectionBatch, self).importIppTables(columns, regex)

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