#!/usr/bin/env jython

import os.path
import sys
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

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,
                 gpc1Db,
                 ippToPspsDb,
                 scratchDb,
                 camID,
                 batchID,
                 useFullTables):

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

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

       # if test mode
       if self.config.test:
           self.startX = 3
           self.endX = 4
           self.startY = 3
           self.endY = 4
       else:
           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 fre 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
       
       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")

        sql = "INSERT INTO FrameMeta (\
          frameID \
         ,frameName \
         ,cameraID \
         ,cameraConfigID \
         ,telescopeID \
         ,analysisVer \
         ,p1Recip \
         ,p2Recip \
         ,p3Recip \
         ,photoScat \
         ,expStart \
         ,expTime \
         ,airmass \
         ,raBore \
         ,decBore \
         ,ctype1 \
         ,ctype2 \
         ,crval1 \
         ,crval2 \
         ,crpix1 \
         ,crpix2 \
         ,cdelt1 \
         ,cdelt2 \
         ,pc001001 \
         ,pc001002 \
         ,pc002001 \
         ,pc002002 \
         ,polyOrder \
         ,pca1x3y0 \
         ,pca1x2y1 \
         ,pca1x1y2 \
         ,pca1x0y3 \
         ,pca1x2y0 \
         ,pca1x1y1 \
         ,pca1x0y2 \
         ,pca2x3y0 \
         ,pca2x2y1 \
         ,pca2x1y2 \
         ,pca2x0y3 \
         ,pca2x2y0 \
         ,pca2x1y1 \
         ,pca2x0y2 \
         ) VALUES ( \
        " + str(self.expID) + " \
        ,'" + self.expName + "' \
        ,1  \
        ,1  \
        ,1  \
        ,'" + str(self.analysisVer) + "' \
        ,' ' \
        ,' ' \
        ,' ' \
        ," + self.safeDictionaryAccess(self.header, 'ZPT_ERR') + " \
        ," + self.safeDictionaryAccess(self.header, 'MJD-OBS') + " \
        ," + self.safeDictionaryAccess(self.header, 'EXPREQ') + " \
        ," + self.safeDictionaryAccess(self.header, 'AIRMASS') + " \
        ," + self.safeDictionaryAccess(self.header, 'RA') + " \
        ," + self.safeDictionaryAccess(self.header, 'DEC') + " \
        ,'" + self.safeDictionaryAccess(self.header, 'CTYPE1') + "' \
        ,'" + self.safeDictionaryAccess(self.header, 'CTYPE2') + "' \
        ," + self.safeDictionaryAccess(self.header, 'CRVAL1') + " \
        ," + self.safeDictionaryAccess(self.header, 'CRVAL2') + " \
        ," + self.safeDictionaryAccess(self.header, 'CRPIX1') + " \
        ," + self.safeDictionaryAccess(self.header, 'CRPIX2') + " \
        ," + self.safeDictionaryAccess(self.header, 'CDELT1') + " \
        ," + self.safeDictionaryAccess(self.header, 'CDELT2') + " \
        ," + self.safeDictionaryAccess(self.header, 'PC001001') + " \
        ," + self.safeDictionaryAccess(self.header, 'PC001002') + " \
        ," + self.safeDictionaryAccess(self.header, 'PC002001') + " \
        ," + self.safeDictionaryAccess(self.header, 'PC002002') + " \
        ," + self.safeDictionaryAccess(self.header, 'NPLYTERM') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X3Y0') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X2Y1') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X1Y2') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X0Y3') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X2Y0') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X1Y1') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA1X0Y2') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X3Y0') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X2Y1') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X1Y2') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X0Y3') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X2Y0') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X1Y1') + " \
        ," + self.safeDictionaryAccess(self.header, 'PCA2X0Y2') + " \
        )"
        self.scratchDb.execute(sql)

        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.config.dataRelease))

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

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

        # insert all detections into table
        sql = "INSERT INTO " + tableName + " ( \
               frameID \
               ,ccdID \
               ,bias \
               ,biasScat \
               ,sky \
               ,skyScat \
               ,magSat \
               ,completMag \
               ,astroScat \
               ,photoScat \
               ,numAstroRef \
               ,numPhotoRef \
               ,nx \
               ,ny \
               ,psfFwhm \
               ,psfWidMajor \
               ,psfWidMinor \
               ,psfTheta \
               ,momentFwhm \
               ,momentWidMajor \
               ,momentWidMinor \
               ,apResid \
               ,dapResid \
               ,detectorID \
               ,qaFlags \
               ,detrend1 \
               ,detrend2 \
               ,detrend3 \
               ,detrend4 \
               ,detrend5 \
               ,detrend6 \
               ,detrend7 \
               ,detrend8 \
               ,photoZero \
               ,ctype1 \
               ,ctype2 \
               ,crval1 \
               ,crval2 \
               ,crpix1 \
               ,crpix2 \
               ,cdelt1 \
               ,cdelt2 \
               ,pc001001 \
               ,pc001002 \
               ,pc002001 \
               ,pc002002 \
               ,polyOrder \
               ,pca1x3y0 \
               ,pca1x2y1 \
               ,pca1x1y2 \
               ,pca1x0y3 \
               ,pca1x2y0 \
               ,pca1x1y1 \
               ,pca1x0y2 \
               ,pca2x3y0 \
               ,pca2x2y1 \
               ,pca2x1y2 \
               ,pca2x0y3 \
               ,pca2x2y0 \
               ,pca2x1y1 \
               ,pca2x0y2 \
               ) VALUES ( \
               " + str(self.expID) + " \
               ," + ota[2:4] + " \
               ," + str(self.bias) + " \
               ," + str(self.biasScat) + " \
               ," + self.safeDictionaryAccess(header, 'MSKY_MN') + " \
               ," + self.safeDictionaryAccess(header, 'MSKY_SIG') + " \
               ," + self.safeDictionaryAccess(header, 'FSATUR') + " \
               ," + self.safeDictionaryAccess(header, 'FLIMIT') + " \
               ," + self.safeDictionaryAccess(header, 'CERROR') + " \
               ," + self.safeDictionaryAccess(self.header, 'ZPT_OBS') + " \
               ," + self.safeDictionaryAccess(header, 'NASTRO') + " \
               ," + self.safeDictionaryAccess(header, 'NASTRO') + " \
               ," + self.safeDictionaryAccess(header, 'CNAXIS1') + " \
               ," + self.safeDictionaryAccess(header, 'CNAXIS2') + " \
               , ( " + self.safeDictionaryAccess(header, 'FWHM_MAJ') + " + " + self.safeDictionaryAccess(header, 'FWHM_MIN') + " ) / 2.0 \
               ," + self.safeDictionaryAccess(header, 'FWHM_MAJ') + " \
               ," + self.safeDictionaryAccess(header, 'FWHM_MIN') + " \
               ," + self.safeDictionaryAccess(header, 'ANGLE') + " \
               , ( " + self.safeDictionaryAccess(header, 'IQ_FW1') + " + " + self.safeDictionaryAccess(header, 'IQ_FW2') + " ) / 2.0  \
               ," + self.safeDictionaryAccess(header, 'IQ_FW1') + " \
               ," + self.safeDictionaryAccess(header, 'IQ_FW2') + " \
               ," + self.safeDictionaryAccess(header, 'APMIFIT') + " \
               ," + self.safeDictionaryAccess(header, 'DAPMIFIT') + " \
               ,'" + self.safeDictionaryAccess(header, 'DETECTOR') + "' \
               ," + str(self.scratchDb.getDvoImageFlags(header['IMAGEID'])) + " \
               ,'" + self.safeDictionaryAccess(header, 'DETREND.MASK') + "' \
               ,'" + self.safeDictionaryAccess(header, 'DETREND.DARK') + "' \
               ,'" + self.safeDictionaryAccess(header, 'DETREND.FLAT') + "' \
               ,' ' \
               ,' ' \
               ,' ' \
               ,' ' \
               ,' ' \
               ," + self.safeDictionaryAccess(self.header, 'ZPT_OBS') + " \
               ,'" + self.safeDictionaryAccess(header, 'CTYPE1') + "' \
               ,'" + self.safeDictionaryAccess(header, 'CTYPE2') + "' \
               ," + self.safeDictionaryAccess(header, 'CRVAL1') + " \
               ," + self.safeDictionaryAccess(header, 'CRVAL2') + " \
               ," + self.safeDictionaryAccess(header, 'CRPIX1') + " \
               ," + self.safeDictionaryAccess(header, 'CRPIX2') + " \
               ," + self.safeDictionaryAccess(header, 'CDELT1') + " \
               ," + self.safeDictionaryAccess(header, 'CDELT2') + " \
               ," + self.safeDictionaryAccess(header, 'PC001001') + " \
               ," + self.safeDictionaryAccess(header, 'PC001002') + " \
               ," + self.safeDictionaryAccess(header, 'PC002001') + " \
               ," + self.safeDictionaryAccess(header, 'PC002002') + " \
               ," + self.safeDictionaryAccess(header, 'NPLYTERM') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X3Y0') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X2Y1') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X1Y2') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X0Y3') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X2Y0') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X1Y1') + " \
               ," + self.safeDictionaryAccess(header, 'PCA1X0Y2') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X3Y0') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X2Y1') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X1Y2') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X0Y3') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X2Y0') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X1Y1') + " \
               ," + self.safeDictionaryAccess(header, 'PCA2X0Y2') + " \
               )"

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

        self.scratchDb.updateFilterID(tableName, self.filter)
        self.scratchDb.updateAllRows(tableName, "calibModNum", str(self.calibModNum))
        self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.config.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
       
        # delete all detections with PSF_INST_MAG < 17.5, as decreed by Gene
        BEFORE = self.scratchDb.getRowCount(ippTableName)
        sql = "DELETE FROM " + ippTableName + " WHERE PSF_INST_MAG < -17.5"
        self.scratchDb.execute(sql)
        #DELETED = BEFORE - self.scratchDb.getRowCount(ippTableName)
        results['SATDET'] = BEFORE - self.scratchDb.getRowCount(ippTableName)
        #self.logger.infoPair("Saturated detections", "%d deleted" % DELETED)

        # insert all detections into table
        sql = "INSERT IGNORE INTO " + pspsTableName + " ( \
               ippDetectID \
               ,filterID \
               ,surveyID \
               ,obsTime \
               ,xPos \
               ,yPos \
               ,xPosErr \
               ,yPosErr \
               ,instFlux \
               ,instFluxErr \
               ,peakADU \
               ,psfWidMajor \
               ,psfWidMinor \
               ,psfTheta \
               ,psfLikelihood \
               ,psfCf \
               ,momentXX \
               ,momentXY \
               ,momentYY \
               ,apMag \
               ,kronFlux \
               ,kronFluxErr \
               ,infoFlag \
               ,sky \
               ,skyErr \
               ,sgSep \
               ,activeFlag \
               ,assocDate \
               ,historyModNum \
               ,dataRelease \
               ) \
               SELECT \
               IPP_IDET \
               , " + str(self.filterID) + "\
               , " + str(self.surveyID) + " \
               ," + str(self.obsTime) + " \
               ,X_PSF \
               ,Y_PSF \
               ,X_PSF_SIG \
               ,Y_PSF_SIG \
               ,POW(10.0, (-0.4*PSF_INST_MAG)) / " + self.header['EXPTIME'] + " \
               ,ABS((PSF_INST_MAG_SIG*(POW(10.0, (-0.4*PSF_INST_MAG)) / " + self.header['EXPTIME'] + ")) / 1.085736) \
               ,POW(10.0, (-0.4*PEAK_FLUX_AS_MAG)) / " + self.header['EXPTIME'] + " \
               ,PSF_MAJOR \
               ,PSF_MINOR \
               ,PSF_THETA \
               ,psfLikelihood(EXT_NSIGMA) \
               ,PSF_QF \
               ,MOMENTS_XX \
               ,MOMENTS_XY \
               ,MOMENTS_YY \
               ,AP_MAG \
               ,KRON_FLUX / " + self.header['EXPTIME'] + " \
               ,KRON_FLUX_ERR / " + self.header['EXPTIME'] + " \
               ,FLAGS\
               ,SKY \
               ,SKY_SIGMA \
               ,EXT_NSIGMA \
               , 0 \
               , '" + self.dateStr + "' \
               , 0 \
               , " + str(self.config.dataRelease) + "\
               FROM " + ippTableName
        self.scratchDb.execute(sql)

        # now delete bad flux and bad chip positions
        sql="DELETE FROM " + pspsTableName + " WHERE instFlux < 0.0000001" # TODO clearly a hack = 0 not allowed for instFlux
        self.scratchDb.execute(sql)

        # update cosmic ray and extended likelihoods
        sql="UPDATE " + pspsTableName + " SET extendedLikelihood = 0, crLikelihood = 1.0 - psfLikelihood WHERE sgSep <= 0"
        self.scratchDb.execute(sql)
        sql="UPDATE " + pspsTableName + " SET crLikelihood = 0, extendedLikelihood = 1.0 - psfLikelihood WHERE sgSep > 0"
        self.scratchDb.execute(sql)

        # remove detections will NULL inst flux or NULL peak ADU
        results['NULLINSTFLUX'] = self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "instFlux")
        results['NULLPEAKADU'] = self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "peakADU")

    '''
    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.config.dataRelease))

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

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

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

        self.scratchDb.updateAllRows(tableName, "calibModNum", str(self.calibModNum))
        self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.config.dataRelease))


    '''
    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 = {}

        # loop through all OTAs and populate ImageMeta extensions
        self.logger.infoPair("Reading", "FITS headers")
        self.logger.infoPair("Populating table", "ImageMeta")
        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)
                
                # load corresponding header into memory
                header = self.fits.findAndReadHeader(ota + ".hdr")
                if not header:
                    self.logger.errorPair("No header found for OTA", ota)
                    continue

                # check we have valid sourceID/imageID pair from the header
                if self.safeDictionaryAccess(header, 'SOURCEID') == "NULL": continue
                if self.safeDictionaryAccess(header, 'IMAGEID') == "NULL": continue

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

                # store these for later
                self.imageIDs[ota] = header['IMAGEID']

                # populate ImageMeta
                self.populateImageMetaTable(ota, header)
                self.updateImageID("ImageMeta_" + ota, x, y) 
             
        # now run DVO code to get all IDs
        if not self.useFullTables: 
            if not self.getIDsFromDVO(): return False

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

    '''
    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.ippObjID = b.ippObjID, \
               a.detectID = b.detectID, \
               a.objID = b.objID, \
               a.infoFlag = b.flags << 32 | a.infoFlag \
               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 populatePspsTables(self):

        # loop through all OTAs again to update with DVO IDs
        self.tablesToExport = []    
        self.tablesToExport.append("FrameMeta")
        tables = []    
        otaCount = 0
        results = {}
        totalOriginal = totalSatDet = totalNulIInstFlux = totalNullPeakFlux = totalNullObjID = totalDetections = 0
        self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+---------------+")
        self.logger.info("|  OTA  | Initial total |   Sat Det     | NULL instFlux | NULL peak ADU | 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

                #self.logger.infoTitle("Processing " + ota)
                if not self.scratchDb.astrometricSolutionOK(ota):
                    self.logger.info("| %5s |            ------------- Bad astrometric solution : rejecting ------------                    |" % ota)
                    continue

                # populate remainder of tables
                self.populateDetectionTable(ota, results)

                # now add DVO IDs
                self.updateDvoIDs("Detection_" + ota, self.imageIDs[ota])
                results['NULLOBJID'] = self.scratchDb.reportAndDeleteRowsWithNULLS("Detection_" + ota, "objID")
                self.updateImageID("Detection_" + ota, x, y) 
                rowCount = self.scratchDb.getRowCount("Detection_" + ota) 
                self.logger.info("| %5s | %13d | %13d | %13d | %13d | %13d | %13d |", 
                        ota, 
                        results['ORIGINALTOTAL'], 
                        results['SATDET'], 
                        results['NULLINSTFLUX'], 
                        results['NULLPEAKADU'], 
                        results['NULLOBJID'],
                        rowCount)

                totalOriginal = totalOriginal + results['ORIGINALTOTAL']
                totalSatDet = totalSatDet + results['SATDET']
                totalNulIInstFlux = totalNulIInstFlux + results['NULLINSTFLUX']
                totalNullPeakFlux = totalNullPeakFlux + results['NULLPEAKADU']
                totalNullObjID = totalNullObjID + results['NULLOBJID']
                totalDetections = totalDetections + rowCount

                # check we have something in this Detection table TODO add this to table above
                if rowCount < 1:
                    self.logger.debugPair("Skipping empty table for ota", ota)
                    continue;

                # update ImageMeta with count of detections for this OTA and photoCodeID
                sql = "UPDATE ImageMeta_" + ota + " \
                       SET nDetect = %d, photoCalID = %d" % (self.scratchDb.getRowCount("Detection_" + ota), self.scratchDb.getPhotoCalID(self.imageIDs[ota]))
                self.scratchDb.execute(sql)

                self.populateSkinnyObjectTable(ota)
                self.populateObjectCalColorTable(ota)

                # add these to list of tables to export later
                self.tablesToExport.append("ImageMeta_" + ota)
                self.tablesToExport.append("Detection_" + ota)
                self.tablesToExport.append("SkinnyObject_" + ota)
                self.tablesToExport.append("ObjectCalColor_" + ota)
                tables.append("Detection_" + ota)
           
                otaCount = otaCount + 1

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

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

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

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


    '''
    Overriding this method. Filter to only import *.psf extensions
    '''
    def importIppTables(self, filter=""):

       if self.config.test: regex = "XY33.psf"
       else : regex = ".*.psf"
  
       columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG PSF_INST_MAG PSF_INST_MAG_SIG PEAK_FLUX_AS_MAG PSF_MAJOR PSF_MINOR PSF_THETA EXT_NSIGMA PSF_QF MOMENTS_XX MOMENTS_XY MOMENTS_YY AP_MAG KRON_FLUX KRON_FLUX_ERR FLAGS SKY SKY_SIGMA EXT_NSIGMA"

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

