#!/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

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

       super(DetectionBatch, self).__init__(
               logger,
               config,
               skychunk,
               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)

       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
       
       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') + " \
        )"

        print "frame meta sql: ", sql

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

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

        tableName = "ImageMeta_" + ota
        
        # XXX we drop the table above so it is not left behind on failure
        # drop then re-create table
        # self.scratchDb.dropTable(tableName)
        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

        # insert image metadata 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) + " \
               ," + str(ccdID) + " \
               ," + 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.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
       
        # delete all detections with PSF_INST_MAG < -17.5, as decreed by Gene
        BEFORE = self.scratchDb.getRowCount(ippTableName)
        #don't do this anymore
        #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)
        #we aren't deletng these now
        results['SATDET'] = 0 
        #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 \
               ,psfFlux \
               ,psfFluxErr \
               ,peakADU \
               ,psfWidMajor \
               ,psfWidMinor \
               ,psfTheta \
               ,psfLikelihood \
               ,psfQf \
               ,momentXX \
               ,momentXY \
               ,momentYY \
               ,apFlux \
               ,kronFlux \
               ,kronFluxErr \
               ,psfQfPerfect \
               ,psfChiSq \
               ,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 \
               ,POW(10.0, -0.4 *AP_MAG) / " + self.header['EXPTIME'] + " \
               ,KRON_FLUX / " + self.header['EXPTIME'] + " \
               ,KRON_FLUX_ERR / " + self.header['EXPTIME'] + " \
               ,PSF_QF_PERFECT \
               ,PSF_CHISQ \
               ,FLAGS2 << 32 | FLAGS \
               ,SKY  / " + self.header['EXPTIME'] + " \
               ,SKY_SIGMA  / " + self.header['EXPTIME'] + " \
               ,EXT_NSIGMA \
               , 0 \
               , '" + self.dateStr + "' \
               , 0 \
               , " + str(self.skychunk.dataRelease) + "\
               FROM " + ippTableName
        # self.logger.info(sql)

        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)

        # add a instFlux = 0.0 -> 0.000001
        sql="UPDATE " + pspsTableName + " SET psfFlux = 0.0000001 WHERE psfFlux =  0"
        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
        # leave them in at this stage- don't delete them.
        #results['NULLINSTFLUX'] = self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "instFlux")
        #results['NULLPEAKADU'] = self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "peakADU")
        #we don't delete thos anymore
        results['NULLINSTFLUX'] = 0;
        results['NULLPEAKADU'] = 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 DetectionCalib table for this OTA
    '''
    def populateDetectionCalibTableUpdateInsert(self, ota):

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

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

        # insert calibration information from dvoDetections into the Table
        sql = "UPDATE " + tableName + " AS a, "  + self.scratchDb.dvoDetectionTable + " AS b \
            SET a.ra = b.ra, \
            a.dec = b.dec_, \
            a.raErr = b.raErr, \
            a.decErr = b.decErr, \
            a.zp = b.zp, \
            a.zpErr = b.zpErr, \
            a.expTime = b.expTime, \
            a.airMass = b.airMass   \
            WHERE a.objID = b.objID AND a.detectID = b.detectID"
        self.scratchDb.execute(sql)
        self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease))
        
    '''
    Populates the DetectionCalib table for this OTA
    '''
    def populateDetectionCalibTable(self, ota):

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

        externID = self.imageIDs[ota]

        imageID = self.scratchDb.getImageIDFromExternID(externID)
        self.logger.infoPair("obtained","imageID")

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

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

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

        sql = "LOAD DATA INFILE '" + dumpFile + "' INTO TABLE " + tableName
        print "sql: ", sql
        self.scratchDb.execute(sql)
        
    '''
    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.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)
        if not header:
            self.logger.errorPair("No header found for chip", chipname)
            return False
        
        # check we have valid sourceID/imageID pair from the header
        if self.safeDictionaryAccess(header, 'SOURCEID') == "NULL": return False
        if self.safeDictionaryAccess(header, 'IMAGEID') == "NULL": return False
        
        # 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[chipname] = header['IMAGEID']
            
        # 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 = {}

        # 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)
                
                self.alterPspsTablesChip(ota, ota + ".hdr", x, y)

        # try the test Chip
        self.alterPspsTablesChip("Chip", "Chip.hdr", 0, 0)

        # now run DVO code to get all IDs
        if not self.useFullTables: 
            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 DetectionCalib 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.ippObjID = b.ippObjID, \
               a.detectID = b.detectID, \
               a.objID = b.objID, \
               a.infoFlag = b.flags << 45 | 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 populatePspsTablesChip(self, chipname, x, y, results, tables):
        # XXX EAM NOTE: drop tables (Detection_, SkinnyObject_, DetectionCalib_, 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")
        if not self.scratchDb.astrometricSolutionOK(chipname):
            self.logger.info("| %5s |            ------------- Bad astrometric solution : 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 | %13d |", 
                chipname, 
                results['ORIGINALTOTAL'], 
                results['SATDET'], 
                results['NULLINSTFLUX'], 
                results['NULLPEAKADU'], 
                results['NULLOBJID'],
                rowCount)
        self.totalOriginal     = self.totalOriginal + results['ORIGINALTOTAL']
        self.totalSatDet       = self.totalSatDet + results['SATDET']
        self.totalNulIInstFlux = self.totalNulIInstFlux + results['NULLINSTFLUX']
        self.totalNullPeakFlux = self.totalNullPeakFlux + results['NULLPEAKADU']
        self.totalNullObjID    = self.totalNullObjID + results['NULLOBJID']
        self.totalDetections   = self.totalDetections + rowCount
        #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("Skipping empty table for chipname", chipname)
            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)
        
        self.logger.info("updated imagedata")
        self.populateSkinnyObjectTable(chipname)
        self.logger.info("updated skinnyobject")
        #self.populateObjectCalColorTable(chipname)
        #self.logger.info("updated objectcalcolor")
        self.populateDetectionCalibTable(chipname)
        self.logger.info("updated detectioncalibtable")
        
        # 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")

        self.tablesToExport.append("SkinnyObject_" + chipname)
        self.logger.info("export Skinny")

        #self.tablesToExport.append("ObjectCalColor_" + chipname)
        self.tablesToExport.append("DetectionCalib_" + chipname)
        self.logger.info("export DetectionCalib")

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

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

                if self.populatePspsTablesChip(ota, x, y, results, tables): otaCount = otaCount + 1

        if self.populatePspsTablesChip("Chip", 0, 0, results, tables): otaCount = otaCount + 1

        # 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
        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"
       regex = ".*.psf"
  
       # XXX EAM NOTE : this is fragile : requires PS1_V4
       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 FLAGS2 SKY SKY_SIGMA EXT_NSIGMA PSF_QF_PERFECT PSF_CHISQ"

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

