#!/usr/bin/env jython

import os.path
import sys
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.errorPair("Could not get", "camera metadata")
           raise

       self.expID = meta[0];
       self.expName = meta[1];
       self.distGroup = meta[2];
       self.analysisVer = meta[3];
       self.bias = meta[4];
       self.biasScat = meta[5];

       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)
      
       # set up some defauts
       self.calibModNum = 0
       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("p1Recip",               ' ');
        # sqlLine.group("p2Recip",               ' ');
        # sqlLine.group("p3Recip",               ' ');

        sqlLine.group("frameID",               str(self.expID));
        sqlLine.group("frameName",             self.expName);
        sqlLine.group("cameraID",              "1");
        sqlLine.group("cameraConfigID",        "1");
        sqlLine.group("telescopeID",           "1");
        sqlLine.group("analysisVer",           self.analysisVer);
        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", "calibModNum", str(self.calibModNum))
        self.scratchDb.updateAllRows("FrameMeta", "dataRelease", str(self.skychunk.dataRelease))

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

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

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

        psfFwhmMajor = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ')
        psfFwhmMinor = self.getKeyFloat(header, "%.8f", 'FWHM_MIN')
        psfFwhm = 0.5*(float(psfFwhmMajor) + float(psfFwhmMinor))

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

        sqlLine.group("frameID",          str(self.expID))
        sqlLine.group("ccdID",            str(ccdID))
        sqlLine.group("bias",             str(self.bias))
        sqlLine.group("biasScat",         str(self.biasScat))
        sqlLine.group("sky",              self.getKeyFloat(header, "%.8f", 'MSKY_MN'))
        sqlLine.group("skyScat",          self.getKeyFloat(header, "%.8f", 'MSKY_SIG'))
        sqlLine.group("magSat",           self.getKeyFloat(header, "%.8f", 'FSATUR'))
        sqlLine.group("completMag",       self.getKeyFloat(header, "%.8f", 'FLIMIT'))
        sqlLine.group("astroScat",        self.getKeyFloat(header, "%.8f", 'CERROR'))
        sqlLine.group("photoScat",        self.getKeyFloat(self.header, "%.8f", 'ZPT_OBS'))
        sqlLine.group("numAstroRef",      self.getKeyValue(header, 'NASTRO'))
        sqlLine.group("numPhotoRef",      self.getKeyValue(header, 'NASTRO'))
        sqlLine.group("nx",               self.getKeyInt(header, 0, 'CNAXIS1'))
        sqlLine.group("ny",               self.getKeyInt(header, 0, 'CNAXIS2'))
        sqlLine.group("psfFwhm",          str(psfFwhm))
        sqlLine.group("psfWidMajor",      psfFwhmMajor)
        sqlLine.group("psfWidMinor",      psfFwhmMinor)
        sqlLine.group("psfTheta",         self.getKeyFloat(header, "%.8f", 'ANGLE'))
        sqlLine.group("momentMajor",      self.getKeyFloat(header, "%.8f", 'IQ_FW1'))
        sqlLine.group("momentMinor",      self.getKeyFloat(header, "%.8f", 'IQ_FW2'))
        sqlLine.group("momentM2C",        self.getKeyFloat(header, "%.8f", 'IQ_M2C'))
        sqlLine.group("momentM2S",        self.getKeyFloat(header, "%.8f", 'IQ_M2S'))
        sqlLine.group("momentM3",         self.getKeyFloat(header, "%.8f", 'IQ_M3'))
        sqlLine.group("momentM4",         self.getKeyFloat(header, "%.8f", 'IQ_M4'))
        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",          str(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'))
        # sqlLine.group("detrend4",         " ")
        # sqlLine.group("detrend5",         " ")
        # sqlLine.group("detrend6",         " ")
        # sqlLine.group("detrend7",         " ")
        # sqlLine.group("detrend8",         " ")
        sqlLine.group("photoZero",        self.getKeyFloat(self.header, "%.8f", 'ZPT_OBS'))
        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 ( ", ")")

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

        self.scratchDb.updateFilterID(tableName, self.filter)
        self.scratchDb.updateAllRows(tableName, "calibModNum", str(self.calibModNum))
        self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease))
        if 'NASTRO' in header: self.totalNumPhotoRef = self.totalNumPhotoRef + int(header['NASTRO'])
        self.scratchDb.replaceNullsInThisColumn(tableName, "polyOrder", "0")

    '''
    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 

        extTimeString = 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?

        sqlLine.group("ippDetectID",     "IPP_IDET")                                                
        sqlLine.group("randomDetID",     "FLOOR(RAND()*9223372036854775807)")                       
        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.id >= 982483):
            sqlLine.group("pltScale",        "PLTSCALE")                                      
            sqlLine.group("posAngle",        "POSANGLE")                                      
            sqlLine.group("raErr",           "X_PSF_SIG * abs(PLTSCALE)") 
            sqlLine.group("decErr",          "Y_PSF_SIG * abs(PLTSCALE)") 
        else:
            sqlLine.group("pltScale",        "0.257")                                      
            sqlLine.group("posAngle",        "-999")                                      
            sqlLine.group("raErr",           "X_PSF_SIG * 0.257") 
            sqlLine.group("decErr",          "Y_PSF_SIG * 0.257") 
        sqlLine.group("psfFlux",         "PSF_INST_FLUX / " + extTimeString)
        sqlLine.group("psfFluxErr",      "PSF_INST_FLUX_SIG / " + extTimeString)
        if (self.id >= 982483):
            sqlLine.group("psfMajorFWHM",    "PSF_FWHM_MAJ")                                               
            sqlLine.group("psfMinorFWHM",    "PSF_FWHM_MIN")                                               
            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")                                     
        sqlLine.group("psfLikelihood",   "psfLikelihood(EXT_NSIGMA)")                               
        sqlLine.group("momentXX",        "MOMENTS_XX")                                              
        sqlLine.group("momentXY",        "MOMENTS_XY")                                              
        sqlLine.group("momentYY",        "MOMENTS_YY")                                              
        sqlLine.group("momentR1",        "MOMENTS_R1")                                              
        sqlLine.group("momentRH",        "MOMENTS_RH")                                              
        sqlLine.group("momentM3C",       "MOMENTS_M3C")                                             
        sqlLine.group("momentM3S",       "MOMENTS_M3S")                                             
        sqlLine.group("momentM4C",       "MOMENTS_M4C")                                             
        sqlLine.group("momentM4S",       "MOMENTS_M4S")                                             
        if (self.id >= 982483):
            sqlLine.group("apFlux",          "AP_FLUX / " + extTimeString)
            sqlLine.group("apFluxErr",       "AP_FLUX_SIG / " + extTimeString)
            sqlLine.group("apFillF",         "AP_NPIX / (3.14159265359 * POW(AP_MAG_RADIUS - 0.5, 2))")
        sqlLine.group("apRadius",        "AP_MAG_RADIUS")
        sqlLine.group("kronFlux",        "KRON_FLUX / " + extTimeString)
        sqlLine.group("kronFluxErr",     "KRON_FLUX_ERR / " + extTimeString)
        sqlLine.group("kronRad",         "MOMENTS_R1 * 2.5")
        sqlLine.group("sky",             "SKY  / " + extTimeString)
        sqlLine.group("skyErr",          "SKY_SIGMA  / " + extTimeString)
        sqlLine.group("infoFlag",        "FLAGS")                          
        sqlLine.group("infoFlag2",       "FLAGS2")                          
        sqlLine.group("dataRelease",     str(self.skychunk.dataRelease))                  

        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)
        # as a result, the negative fluxes here result in floating point errors
        # XXX EAM 2014072 : Is this still a problem?
        sql = "UPDATE " + pspsTableName + " SET psfFlux = 1e20 WHERE psfFlux <= 0.0"
        self.scratchDb.execute(sql)
        
        sql = "UPDATE " + pspsTableName + " SET apFlux = 1e20 WHERE apFlux <= 0.0"
        self.scratchDb.execute(sql)

        sql = "UPDATE " + pspsTableName + " SET kronFlux = 1e20 WHERE kronFlux <= 0.0"
        self.scratchDb.execute(sql)

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

    '''
    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, "dataRelease", str(self.skychunk.dataRelease))
    '''
    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, "dataRelease", str(self.skychunk.dataRelease))

    '''
    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
        
        # check we have valid sourceID/imageID pair from the header
        if self.getKeyValue(header, 'SOURCEID') == "NULL": return False
        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']
            
        # populate ImageMeta
        self.populateImageMetaTable(chipname, header)
        self.updateImageID("ImageMeta_" + 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):
                    
                    # 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)
                
                    # I need better control over this..
                    if False and self.config.test and not ((x == 0) and (y == 1)):
                        print "skipping ota: " + ota
                        continue
                        
                    self.alterPspsTablesChip(ota, ota + ".hdr", x, y)

        if self.config.camera == "simtest":
            # try the test Chip
            self.alterPspsTablesChip("Chip", "Chip.hdr", 0, 0)

        # now run DVO code to get all IDs ( XXX deprecated, uses dvograbber)
        # if not self.getIDsFromDVO(): return False

        # 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):

                # 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

                extension = "XY%d%d_psf" % (x, y)
                self.scratchDb.createIndex(extension, "IPP_IDET")
        # try the test Chip
        self.scratchDb.createIndex("Chip_psf", "IPP_IDET")

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

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

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

        sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \
               a.objID        = b.objID, \
               a.detectID     = b.detectID, \
               a.ippObjID     = b.ippObjID, \
               a.dvoRegionID  = b.catID, \
               a.ra           = b.ra, \
               a.dec          = b.dec_, \
               a.zp           = b.zp, \
               a.telluricExt  = b.telluricExt, \
               a.airmass      = b.airmass, \
               a.expTime      = b.expTime, \
               a.infoFlag2    = (b.flags << 13) | a.infoFlag2 \
               WHERE a.ippDetectID = b.ippDetectID \
               AND b.imageID = " + str(imageID)

        self.scratchDb.execute(sql)

    '''
    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.updateDvoIDs("Detection_" + chipname, self.imageIDs[chipname])
        #self.logger.info("updated dvoids")
        results['NULLOBJID'] = self.scratchDb.reportAndDeleteRowsWithNULLS("Detection_" + chipname, "objID")
        #self.logger.info("deleted nulls")
        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("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):

        # 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):
               
                # 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)
                if ota not in self.imageIDs: continue

                if False and self.config.test and not ((x == 0) and (y == 1)):
                    print "skipping ota " + ota
                    continue

                if self.populatePspsTablesChip(ota, x, y, results, tables): otaCount = otaCount + 1
                if self.skipBatch: 
                    self.logger.error("fatal problem for exposure, skipping")
                    return False

        if "Chip" in self.imageIDs:
            if self.populatePspsTablesChip("Chip", 0, 0, results, tables): otaCount = otaCount + 1
            if self.skipBatch: 
                self.logger.error("fatal problem for exposure, skipping")
                return False

        # 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("+-------+---------------+---------------+---------------+---------------+---------------+")

        # 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, numPhotoRef = %d" % (otaCount, self.totalNumPhotoRef)
        self.scratchDb.execute(sql)
        #to make it stop
        #test = self.scratchDb.getRowCount("Object")
        #return False
        
        return True

    def removeDuplicateObjects(self):

        sql = "DROP TABLE SkinnyObject_All"
        try:
            self.scratchDb.execute(sql)
        except:
            print "SkinnyObject_All table not yet defined"

        sql = "CREATE TABLE SkinnyObject_All (objID bigint, ippObjID bigint, chipID char(16))"
        try:
            self.scratchDb.execute(sql)
        except:
            print "failed to create table SkinnyObject_All"
            raise

        for chipname in self.validChips:
            
            sql = "INSERT INTO SkinnyObject_All (objID, ippObjID, chipID) \
                   SELECT objID, ippObjID, '" + chipname + "' from SkinnyObject_" + chipname
            try:
                self.scratchDb.execute(sql)
            except:
                print "failed to insert entry SkinnyObject_All: ", chipname
                raise
            

        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)

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

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


    '''
    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 = ".*.psf"
        if False and self.config.test and self.config.camera == "gpc1":
            regex = "XY01.psf"
            
        print "my ID: " + str(self.id)

        if (self.id < 982483):
            # 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"

        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]+)")
