#!/usr/bin/env jython

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

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


'''
ForcedWarpBatch class

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

'''
class ForcedWarpBatch(Batch):

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

       super(ForcedWarpBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               id,
               batchID,
               "FW",
               None) 

       # make a set of lists (needs to be outside of the loop

       stageID = self.id
       self.fits={}
       self.header={}
       self.warpSkyFileID={}
       self.filterName = {}
       self.historyModNum = "0"


       self.totalNumPhotoRef = 0
       self.number={}
       self.expID = {}
       self.expTime = {}
       self.analysisVer = {}
       self.md5sum = {}
       self.tessName = {}
       self.obsTime = {}
       self.filterID={}           
       self.skycellName={}
       self.skycellID = {}
       self.projectionID = {}
       self.tessID = {}
       self.stackMetaID = {}
       self.ippSkycalID = {}

       #I give a number to the forcedwarp stuff, I am not sure if this is the best way but it might work

       num = 0
       
       self.forcedWarpIds = gpc1Db.getListOfForcedWarpIdsForForcedWarpBatch(skychunk.dvoLabel,stageID) 

       for forcedWarpID in self.forcedWarpIds: 
           self.number[num] = forcedWarpID
           self.logger.infoPair("index",num)
           self.logger.infoPair("forcedWarpID", forcedWarpID)
           self.logger.infoPair("getting the fits file for add_id", forcedWarpID)
           self.fits[num] = gpc1Db.getForcedWarpStageCmf(skychunk.dvoLabel, int(forcedWarpID))
           
           ## print "--- PHU Header ---"
           ## myHeader = self.fits[num].getPrimaryHeader()
           ## for key in myHeader:
           ##     print "--- header: " + key + " = " + myHeader[key]
           ## print "--- END Header ---"

           self.header[num] = self.fits[num].getPrimaryHeader()
           ##self.warpSkyFileID[num] = self.getKeyValue(self.header[num],"IMAGEID")

           ## XXX: filter is not in the heade
           ## self.filterName[num] = self.getKeyValue(self.header[num],"FPA.FILTERID")
           ## self.logger.infoPair("found filterName",self.filterName[num])
           ##if not self.warpSkyFileID[num]:
           self.logger.infoPair("getting warp_skyfile_id from", "gpc1")
           #self.skyfileid = self.gpc1Db.getStageExtra1ForThisAddID(forcedWarpID)[0]
           #self.warpSkyFileID[num] = self.skyfileid[0]
           self.warpSkyFileID[num]=self.gpc1Db.getWarpSkyFileIDforThisAddIDforFW(forcedWarpID)
           self.header[num]['IMAGEID']=self.warpSkyFileID[num]
           self.logger.infoPair("found warp_skyfile_id",self.warpSkyFileID[num])

           # get camera meta data from gpc1 database
           # NOTE: this should all be in the header, but is not in at least some cases
           meta = self.gpc1Db.getForcedWarpStageMeta(forcedWarpID, self.warpSkyFileID[num])

           if not meta:
               self.logger.errorPair("Could not get", "forced warp metadata")
               raise

           self.expID[num]       = meta['exp_id']
           self.expTime[num]     = meta['exp_time']
           self.filterName[num]  = meta['filter']
           self.skycellName[num] = meta['skycell_id']
           self.tessName[num]    = meta['tess_id']
           self.stackMetaID[num] = meta['stack_id']
           self.ippSkycalID[num] = meta['skycal_id']
       
           # This isn't populated in the database (it's NULL).  The hack is below.
           # self.analysisVer[num] = meta[2]

           self.logger.infoPair("found exp_id",     str(self.expID[num]))
           self.logger.infoPair("found exp_time",   str(self.expTime[num]))
           self.logger.infoPair("found filter",     str(self.filterName[num]))
           self.logger.infoPair("found skycell_id", self.skycellName[num])
           self.logger.infoPair("found tess_id",    self.tessName[num])

           self.tessID[num] = self.scratchDb.getTessID(self.tessName[num])

           # As the database doesn't have the software_ver entry, pull the photometry version as the analysis version.
           self.analysisVer[num] = self.getKeyValue(self.header[num],"PHOT_V")

           if not self.analysisVer[num]: self.analysisVer[num] = -999
           if self.analysisVer[num] is None: self.analysisVer[num] = "-999"
           self.logger.infoPair("found analysisVer",self.analysisVer[num])
           
           if self.tessName[num] == 'RINGS.V3':
               # skycell is, eg "skycell.1133.081"
               #                 0123456789012345
               self.projectionID[num] = self.skycellName[num][8:12]
               self.skycellID[num] = self.skycellName[num][13:] 
           else:
               # skycell is, eg "skycell.081"
               #                 01234567890
               self.projectionID[num] = 0
               self.skycellID[num] = self.skycellName[num][8:]
               
           if self.projectionID[num] == "": 
               self.projectionID[num] = -1
           if self.skycellID[num] == "": 
               self.skycellID[num] = -1

           self.logger.infoPair("projection cell found ",self.projectionID[num])
           self.logger.infoPair("skycell found", self.skycellID[num])
           self.dropTableVerbose("ForcedWarpMeasurement"+"_"+str(num))
           self.dropTableVerbose("ForcedWarpMasked"+"_"+str(num))
           self.dropTableVerbose("ForcedWarpExtended"+"_"+str(num))
           self.dropTableVerbose("ForcedWarpMeta"+"_"+str(num))
           self.dropTableVerbose("ForcedWarpToImage"+"_"+str(num))
           self.dropTableVerbose("ForcedWarpLensing"+"_"+str(num))
           if not self.config.retry:
               self.scratchDb.dropTable("ForcedWarpMeasurement"+"_"+str(num))
               self.scratchDb.dropTable("ForcedWarpMasked"+"_"+str(num))
               self.scratchDb.dropTable("ForcedWarpExtended"+"_"+str(num))
               self.scratchDb.dropTable("ForcedWarpMeta"+"_"+str(num))
               self.scratchDb.dropTable("ForcedWarpToImage"+"_"+str(num))
               self.scratchDb.dropTable("ForcedWarpLensing"+"_"+str(num))

           # get a few primary header values. if in test mode, then use defaults

           ## MEH md5sum test
	   self.md5sum[num] = self.fits[num].getMd5sum()

           # MJD-OBS is the exposure start, EXTIME / 172800 = (EXPTIME sec / 84600 sec/day) / 2
           self.obsTime[num] = float(self.header[num]['MJD-OBS']) + (float(self.expTime[num]) / 172800.0)
           self.logger.infoPair("obstime",self.obsTime[num])

           self.filterID[num] = self.scratchDb.getFilterID(self.filterName[num][0])
           self.logger.infoPair("filterID is ",self.filterID[num])

           self.logger.infoPair("inserting basics into ", "ipptopsps.forcedwarp")
           self.ippToPspsDb.insertForcedWarpMeta( int(self.batchID), int(self.expID[num]), self.filterName[num][0])       

           num=num+1

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

       # dump stuff to log
       self.logger.infoPair("add ID", "%d" % self.id)

    '''
    Populates the FrameMeta table, mainly from dictionary values found in IPP FITS header
    '''
    def populateForcedWarpMeta(self,num):
        forcedWarpID = self.number[num]
        tableName = "ForcedWarpMeta_" + str(num)

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

        self.logger.infoPair("creating table", tableName)

        sql = "CREATE TABLE " + tableName + " LIKE ForcedWarpMeta"
        try: self.scratchDb.execute(sql)
        except: 
            self.logger.error("problem with SQL: " + sql)
            raise

        header   = self.header[num]
        self.logger.infoPair("found header","ok")

        

        photoCalID = str(self.scratchDb.getPhotoCalID(header['IMAGEID']))       
        if photoCalID == -1:
            self.logger.infoPair("image not found in DVO, skipping", str(num))
            return False

        # PHOTCODE and FILTER are not in the header; reconstruct here
        # photoCalID = "GPC1.%s.ForcedWarp" % self.filterName[num][0]

        zpImage = self.scratchDb.getImageZeroPoint(header['IMAGEID'])

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

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

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

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

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

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

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

        sqlLine = sqlUtility("INSERT INTO " + tableName + " (")

        # batchID (below)
        # surveyID (below)
        # filterdID (below)

        sqlLine.group("forcedWarpID",   str(forcedWarpID))
        sqlLine.group("frameID",        str(self.expID[num]))
	sqlLine.group("tessID",         str(self.tessID[num]))
	sqlLine.group("projectionID",   str(self.projectionID[num]))
        sqlLine.group("skyCellID",      str(self.skycellID[num])) 
        sqlLine.group("photoCalID",     photoCalID)
        sqlLine.group("analysisVer",    str(self.analysisVer[num]))
	sqlLine.group("md5sum",         str(self.md5sum[num]))
        sqlLine.group("expTime",        self.expTime[num])
        sqlLine.group("ippSkycalID",    self.ippSkycalID[num])
        sqlLine.group("stackMetaID",    self.stackMetaID[num])
        sqlLine.group("psfModelID",     psfmodelID)
        sqlLine.group("psfFWHM",        psfFWHM)
        sqlLine.group("psfWidMajor",    psfFwhmMajor)
        sqlLine.group("psfWidMinor",    psfFwhmMinor)

        # sqlLine.group("photoZero",      self.getKeyFloat(header, "%.8f", 'FPA.ZP'))

        sqlLine.group("psfTheta",       self.getKeyFloat(header, "%.8f", 'ANGLE'))
        sqlLine.group("photoZero",      zpImage)

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

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

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

        # note hardcoded pltscale
        if photoCalID != -1:
            self.scratchDb.updateRecalStats(tableName, header['IMAGEID'], "0.25");

        self.scratchDb.updateAllRows(tableName, "batchID", str(self.batchID))
        self.scratchDb.updateAllRows(tableName, "surveyID", str(self.surveyID))
        # self.scratchDb.updateFilterID(tableName, self.filterID)
        self.scratchDb.updateFilterID(tableName, self.filterName[num][0])
        self.scratchDb.updateAllRows(tableName, "processingVersion", str(self.skychunk.processingVersion))
        # self.tablesToExport.append("ForcedWarpMeta")
        #return false
        return True
       
    '''
    Populates the ForcedWarpToImage table
    '''
    def populateForcedWarpToImage(self,num):
        forcedwarpID = self.number[num]
        self.logger.infoPair("Procesing table", "ForcedWarpToImage")

        tableName = "ForcedWarpToImage_" + str(num)

        sql = "CREATE TABLE " + tableName + " LIKE ForcedWarpToImage"
        self.scratchDb.execute(sql)

        if forcedwarpID > 0:

                imageIDs = self.gpc1Db.getImageIDsForThisForcedWarpID(forcedwarpID)

                for imageID in imageIDs:

                    sql = "INSERT INTO " + tableName + " (forcedwarpID, imageID) VALUES ( " + str(forcedwarpID) + ", " + imageID + ")"
                    try: self.scratchDb.execute(sql)
                    except:
                       self.logger.infoPair("can't execute sql",sql)
                       raise
        return True

    '''
    Populates the ForcedWarpMeasurement and ForcedWarpMasked tables
    '''
    def populateForcedWarpMeasurement(self,num):
        forcedWarpID = self.number[num]
        pspsTableName = "ForcedWarpMeasurement_" + str(num)
        maskTableName = "ForcedWarpMasked_" + str(num)
        ippTableName = "SkyChip_psf_" + str(num)

        sql = "CREATE TABLE " + pspsTableName + " LIKE ForcedWarpMeasurement"
        self.scratchDb.execute(sql)

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

        expTimeString = str(self.expTime[num])

        sqlLine = sqlUtility("INSERT INTO " + pspsTableName + " (")
        sqlLine.group("ippDetectID",       "IPP_IDET")
      
        sqlLine.group("forcedWarpID",      str(forcedWarpID))
        sqlLine.group("randomWarpID",      "RAND("+str(self.batchID)+")") 
        sqlLine.group("filterID",          str(self.filterID[num]))
        sqlLine.group("surveyID",          str(self.surveyID))
	sqlLine.group("tessID",            str(self.tessID[num]))
	sqlLine.group("projectionID",      str(self.projectionID[num]))
        sqlLine.group("skycellID",         str(self.skycellID[num]))
        sqlLine.group("expTime",           str(self.expTime[num]))
        sqlLine.group("forcedSummaryID",   str(self.id))
                                           
        sqlLine.group("obsTime",           str(self.obsTime[num]))
        sqlLine.group("FpsfFlux",          "PSF_INST_FLUX / " + expTimeString)
        sqlLine.group("FpsfFluxErr",       "PSF_INST_FLUX_SIG / " + expTimeString)

        if self.haveSrcPos[num]:
            sqlLine.group("xPosChip",          "SRC_CHIP_X")
            sqlLine.group("yPosChip",          "SRC_CHIP_Y")
            sqlLine.group("FccdID"  ,          "SRC_CHIP_NUM")

        sqlLine.group("FpsfMajorFWHM",     "PSF_FWHM_MAJ * abs(PLTSCALE)")
        sqlLine.group("FpsfMinorFWHM",     "PSF_FWHM_MIN * abs(PLTSCALE)")
        sqlLine.group("FpsfTheta",         "PSF_THETA")
        sqlLine.group("FpsfCore",          "PSF_CORE")
        sqlLine.group("FpsfQf",            "PSF_QF")
        sqlLine.group("FpsfQfPerfect",     "PSF_QF_PERFECT")
        sqlLine.group("FpsfChiSq",         "PSF_CHISQ / PSF_NDOF")
        sqlLine.group("FmomentXX",         "MOMENTS_XX * PLTSCALE * PLTSCALE")
        sqlLine.group("FmomentXY",         "MOMENTS_XY * PLTSCALE * PLTSCALE")
        sqlLine.group("FmomentYY",         "MOMENTS_YY * PLTSCALE * PLTSCALE")
        sqlLine.group("FmomentR1",         "MOMENTS_R1 * abs(PLTSCALE)")
        sqlLine.group("FmomentRH",         "MOMENTS_RH * sqrt(abs(PLTSCALE))")
        sqlLine.group("FmomentM3C",        "MOMENTS_M3C * PLTSCALE * PLTSCALE")
        sqlLine.group("FmomentM3S",        "MOMENTS_M3S * PLTSCALE * PLTSCALE")
        sqlLine.group("FmomentM4C",        "MOMENTS_M4C * PLTSCALE * PLTSCALE")
        sqlLine.group("FmomentM4S",        "MOMENTS_M4S * PLTSCALE * PLTSCALE")
        sqlLine.group("FapFlux",           "AP_FLUX / " + expTimeString)
        sqlLine.group("FapFluxErr",        "AP_FLUX_SIG / " + expTimeString)
        sqlLine.group("FapFillF",          "AP_NPIX / (3.14159265359 * AP_MAG_RADIUS * AP_MAG_RADIUS)")
        sqlLine.group("FapRadius",         "AP_MAG_RADIUS")
        sqlLine.group("FkronFlux",         "KRON_FLUX / " + expTimeString)
        sqlLine.group("FkronFluxErr",      "KRON_FLUX_ERR / " + expTimeString )
        sqlLine.group("FkronRad",          "MOMENTS_R1 * 2.5 * abs(PLTSCALE)")
        sqlLine.group("Fsky",              "SKY / PLTSCALE / PLTSCALE / " + expTimeString)
        sqlLine.group("FskyErr",           "SKY_SIGMA / PLTSCALE / PLTSCALE / " + expTimeString)
        sqlLine.group("FinfoFlag",         "FLAGS")
        sqlLine.group("FinfoFlag2",        "FLAGS2")
        sqlLine.group("processingVersion", str(self.skychunk.processingVersion))

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

        # insert the per-object data (IDs, random stack ID, processingVersion, etc)
        # self.selectDvoObjIDs()
        self.logger.infoPair("inserting dvo info: find imageID", "from externID")

        # get the imageID: this code has cases for simtest vs gpc1 cameras
        # if self.config.test: 
        self.logger.infoPair("current camera is", self.config.camera)
        if self.config.camera == "gpc1":
            self.logger.infoPair("I don't like this", "it is hardwired to gpc1")
            imageID = self.scratchDb.getImageIDFromExternIDandPhotcodeRange(self.warpSkyFileID[num],12000,12500) 
        if self.config.camera == "simtest":
            imageID = self.scratchDb.getImageIDFromExternIDandPhotcodeRange(self.warpSkyFileID[num],15100,15500) 
        if imageID == 0:
            self.logger.infoPair ("failed to get imageID for", str(num))
            raise

        self.logger.infoPair("updating", pspsTableName)

        sqlLine = sqlUtility("UPDATE " + pspsTableName + " as a, " + self.scratchDb.dvoDetectionTable + " as b SET ")
        sqlLine.group("a.objID",      "b.objID")
        sqlLine.group("a.detectID",   "b.detectID")
        sqlLine.group("a.ippObjID",   "b.ippObjID")
        sqlLine.group("a.dvoRegionID","b.catID")
        sqlLine.group("a.zp",         "b.zpPSF")
        sqlLine.group("a.telluricExt","b.telluricExt")
        sqlLine.group("a.airmass",    "b.airmass")
        sqlLine.group("a.FinfoFlag3", "b.flags")

        sqlLine.group("a.FpsfFlux",     "a.FpsfFlux     * b.zpFactorPSF")
        sqlLine.group("a.FpsfFluxErr",  "a.FpsfFluxErr  * b.zpFactorPSF")
        sqlLine.group("a.FapFlux",      "a.FapFlux      * b.zpFactorAPER")
        sqlLine.group("a.FapFluxErr",   "a.FapFluxErr   * b.zpFactorAPER")
        sqlLine.group("a.FkronFlux",    "a.FkronFlux    * b.zpFactorAPER")
        sqlLine.group("a.FkronFluxErr", "a.FkronFluxErr * b.zpFactorAPER")
        sqlLine.group("a.Fsky",         "a.Fsky         * b.zpFactorAPER")
        sqlLine.group("a.FskyErr",      "a.FskyErr      * b.zpFactorAPER")

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

        self.logger.infoPair("need to cull"," nulls objid")
        rowCountBefore = self.scratchDb.getRowCount(pspsTableName)
        results= self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "objID")
        rowCountAfter =  self.scratchDb.getRowCount(pspsTableName)
        self.logger.infoPair("row count of ForcedWarpMeasurement", rowCountBefore)
        self.logger.infoPair("row count of ForcedWarpMeasurement after cull of null objID ", rowCountAfter)
        if rowCountAfter == 0:
            if self.haveLensPSF[num] ==0:
                self.logger.infoPair("skipping this table", "no lenspsf and no dvo, run this:")
                self.logger.infoPair("update addRun join addProcessedExp using (add_id) set fault = 43 where add_id = ",str(self.number[num])) 
                self.skipBatch = True
                return False
            else:    
                self.skipBatch = True
                self.logger.infoPair ("skip this batch", "it is bad")
                return False



        self.logger.infoPair("Adding 'row' columns to", pspsTableName)
        self.scratchDb.addRowCountColumn(pspsTableName, "row")

        self.logger.infoPair("Adding unique ids to", pspsTableName)
        self.generateUniquePspsIDs(pspsTableName, num)

        self.logger.infoPair("Dropping row column from", pspsTableName)
        self.scratchDb.dropColumn(pspsTableName, "row")

        # now move the rows with no data to ForcedWarpMasked
        self.logger.infoPair("Procesing table", maskTableName)
        sql = "CREATE TABLE " + maskTableName + " LIKE ForcedWarpMasked"
        self.scratchDb.execute(sql)

        # This is the definition of a "masked measurement".
        # Do we have NULL columns at this point or are they already -999? This test catches them either way
        maskedCondition = "FpsfQfPerfect < .0001 OR FpsfQfPerfect is NULL"

        sql  = "INSERT INTO " + maskTableName
        sql += " SELECT objID, uniquePspsFWid, ippObjID, ippDetectID, filterID, surveyID," 
        sql += " forcedSummaryID, forcedWarpID, randomWarpID, tessID, projectionID,"
        sql += " skyCellID, dvoRegionID, obsTime"
        sql += " FROM " + pspsTableName + " WHERE " + maskedCondition
        self.scratchDb.execute(sql)

        sql = "DELETE FROM " + pspsTableName + " WHERE " + maskedCondition
        self.scratchDb.execute(sql)
           
        # add indexes ForcedWarpMeasurement
        self.scratchDb.createIndex(pspsTableName, "objID")
        self.scratchDb.createIndex(pspsTableName, "ippDetectID")

        # add indexes ForcedWarpMasked
        self.scratchDb.createIndex(maskTableName, "objID")
        self.scratchDb.createIndex(maskTableName, "ippDetectID")
        return True

    '''
    Populates the ForcedWarpExtended table
    '''
    def populateForcedWarpExtended(self,num):
        forcedWarpID = self.number[num]
        pspsTableName = "ForcedWarpExtended_" + str(num)
        ippTableName = "SkyChip_xrad_" + str(num)
        fMeasTableName = "ForcedWarpMeasurement_" + str(num)

        expTimeString = str(self.expTime[num])

        sql = "CREATE TABLE " + pspsTableName + " LIKE ForcedWarpExtended"
        self.scratchDb.execute(sql)

        # insert stuff from SkyChip_xrad
        self.logger.infoPair("inserting data from", ippTableName)
        sqlLine = sqlUtility("INSERT INTO " + pspsTableName + " (")
        sqlLine.group("ippDetectID",     "IPP_IDET")
        sqlLine.group("flxR5",           "APER_FLUX_3 / " + expTimeString)
        sqlLine.group("flxR5Err",        "APER_FLUX_ERR_3 / " + expTimeString)
        sqlLine.group("flxR5Std",        "APER_FLUX_STDEV_3 / " + expTimeString)
        sqlLine.group("flxR5Fill",       "APER_FILL_3")
        sqlLine.group("flxR6",           "APER_FLUX_4 / " + expTimeString)
        sqlLine.group("flxR6Err",        "APER_FLUX_ERR_4 / " + expTimeString)
        sqlLine.group("flxR6Std",        "APER_FLUX_STDEV_4 / " + expTimeString)
        sqlLine.group("flxR6Fill",       "APER_FILL_4")
        sqlLine.group("flxR7",           "APER_FLUX_5 / " + expTimeString)
        sqlLine.group("flxR7Err",        "APER_FLUX_ERR_5 / " + expTimeString)
        sqlLine.group("flxR7Std",        "APER_FLUX_STDEV_5 / " + expTimeString)
        sqlLine.group("flxR7Fill",       "APER_FILL_5")

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

        # insert the things from ForcedWarpMeasurement
        self.logger.infoPair("adding objID, etc from", fMeasTableName)
        sqlLine = sqlUtility("UPDATE " + pspsTableName + " as a, " + fMeasTableName + " as b SET ")
        sqlLine.group("a.objID",         "b.objID")
        sqlLine.group("a.uniquePspsFWid","b.uniquePspsFWid")
        sqlLine.group("a.detectID",      "b.detectID")
        sqlLine.group("a.ippObjID",      "b.ippObjID")
        sqlLine.group("a.filterID",      "b.filterID")
        sqlLine.group("a.surveyID",      "b.surveyID")
        sqlLine.group("a.forcedWarpID",  "b.forcedWarpID")
        sqlLine.group("a.randomWarpID",  "b.randomWarpID")
        sqlLine.group("a.tessID",        "b.tessID")
        sqlLine.group("a.projectionID",  "b.projectionID")
        sqlLine.group("a.skycellID",     "b.skycellID")
        sqlLine.group("a.dvoRegionID",   "b.dvoRegionID")
        sqlLine.group("a.obsTime",       "b.obsTime")
        sql = sqlLine.makeEquals("WHERE a.ippDetectID = b.ippDetectID")
        self.scratchDb.execute(sql)
        
        # Normalize by zpFactor by joining our table against the dvoDetectionTable
        self.logger.infoPair("inserting dvo info: find imageID", "from externID")
        imageID = self.scratchDb.getImageIDFromExternID(self.warpSkyFileID[num])
        self.logger.infoPair("updating", pspsTableName)

        sqlLine = sqlUtility("UPDATE " + pspsTableName + " as a, " + self.scratchDb.dvoDetectionTable + " as b SET ")
        sqlLine.group("a.flxR5",     "a.flxR5     * b.zpFactorAPER")
        sqlLine.group("a.flxR5Err",  "a.flxR5Err  * b.zpFactorAPER")
        sqlLine.group("a.flxR5Std",  "a.flxR5Std  * b.zpFactorAPER")

        sqlLine.group("a.flxR6",     "a.flxR6     * b.zpFactorAPER")
        sqlLine.group("a.flxR6Err",  "a.flxR6Err  * b.zpFactorAPER")
        sqlLine.group("a.flxR6Std",  "a.flxR6Std  * b.zpFactorAPER")

        sqlLine.group("a.flxR7",     "a.flxR7     * b.zpFactorAPER")
        sqlLine.group("a.flxR7Err",  "a.flxR7Err  * b.zpFactorAPER")
        sqlLine.group("a.flxR7Std",  "a.flxR7Std  * b.zpFactorAPER")

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

        # cull bad entris
        self.logger.infoPair("need to cull"," nulls objid")
        results = self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "objID")
        self.scratchDb.createIndex(pspsTableName, "objID")
        return True

    ''' 
    Populates the ForcedWarpExtended table 
    '''
    def populateForcedWarpLensing(self, num):
        forcedWarpID   = self.number[num]
        ippTableName   = "SkyChip_psf_" + str(num)
        pspsTableName  = "ForcedWarpLensing_" + str(num)
        fMeasTableName = "ForcedWarpMeasurement_" + str(num)

        sql = "CREATE TABLE " + pspsTableName + " LIKE ForcedWarpLensing"
        self.scratchDb.execute(sql)

        # insert stuff from SkyChip_psf 
        self.logger.infoPair("adding information from " + ippTableName + " into", pspsTableName)
        sqlLine = sqlUtility("INSERT INTO " + pspsTableName + " (")
        sqlLine.group("ippDetectID",     "IPP_IDET")
        sqlLine.group("lensObjSmearX11", "LENS_X11_SM_OBJ")
        sqlLine.group("lensObjSmearX12", "LENS_X12_SM_OBJ")
        sqlLine.group("lensObjSmearX22", "LENS_X22_SM_OBJ")
        sqlLine.group("lensObjSmearE1",  "LENS_E1_SM_OBJ")
        sqlLine.group("lensObjSmearE2",  "LENS_E2_SM_OBJ")
        sqlLine.group("lensObjShearX11", "LENS_X11_SH_OBJ")
        sqlLine.group("lensObjShearX12", "LENS_X12_SH_OBJ")
        sqlLine.group("lensObjShearX22", "LENS_X22_SH_OBJ")
        sqlLine.group("lensObjShearE1",  "LENS_E1_SH_OBJ")
        sqlLine.group("lensObjShearE2",  "LENS_E2_SH_OBJ")

        if self.haveLensPSF[num]:
            sqlLine.group("lensPSFSmearX11", "LENS_X11_SM_PSF")
            sqlLine.group("lensPSFSmearX12", "LENS_X12_SM_PSF")
            sqlLine.group("lensPSFSmearX22", "LENS_X22_SM_PSF")
            sqlLine.group("lensPSFSmearE1",  "LENS_E1_SM_PSF")
            sqlLine.group("lensPSFSmearE2",  "LENS_E2_SM_PSF")
            sqlLine.group("lensPSFShearX11", "LENS_X11_SH_PSF")
            sqlLine.group("lensPSFShearX12", "LENS_X12_SH_PSF")
            sqlLine.group("lensPSFShearX22", "LENS_X22_SH_PSF")
            sqlLine.group("lensPSFShearE1",  "LENS_E1_SH_PSF")
            sqlLine.group("lensPSFShearE2",  "LENS_E2_SH_PSF")
            sqlLine.group("psfE1",           "LENS_E1_PSF")
            sqlLine.group("psfE2",           "LENS_E2_PSF")

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

        # insert the things from ForcedWarpMeasurement 
        self.logger.infoPair("inserting objID,etc from", fMeasTableName)
        sqlLine = sqlUtility("UPDATE " + pspsTableName + " as a, " + fMeasTableName + " as b SET ")
        sqlLine.group("a.objID",          "b.objID")
        sqlLine.group("a.uniquePspsFWid", "b.uniquePspsFWid")
        sqlLine.group("a.detectID",       "b.detectID")
        sqlLine.group("a.ippObjID",       "b.ippObjID")
        sqlLine.group("a.filterID",       "b.filterID")
        sqlLine.group("a.surveyID",       "b.surveyID")
        sqlLine.group("a.forcedWarpID",   "b.forcedWarpID")
        sqlLine.group("a.randomWarpID",   "b.randomWarpID")
        sqlLine.group("a.tessID",         "b.tessID")
        sqlLine.group("a.projectionID",   "b.projectionID")
        sqlLine.group("a.skycellID",      "b.skycellID")
        sqlLine.group("a.dvoRegionID",    "b.dvoRegionID")
        sqlLine.group("a.obsTime",        "b.obsTime")

        sql = sqlLine.makeEquals("WHERE a.ippDetectID = b.ippDetectID")
        self.scratchDb.execute(sql)

        # cull bad entries
        self.logger.infoPair("need to cull"," nulls objid")
        results= self.scratchDb.reportAndDeleteRowsWithNULLS(pspsTableName, "objID")

        self.scratchDb.createIndex(pspsTableName, "objID")
        return True

    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTables(self):
        self.logger.infoPair("starting","populatePspsTables");
        self.skipBatch = False
        # each of the "populate*" methods below add their tables to the list:
        self.tablesToExport=[]
        tables = [] # list of tables to calculate min/max objid on...
    
        for num in self.number:    
            forcedWarpID = self.number[num]
            myTable = "ForcedWarpMeta_" + str(num)
            self.logger.infoPair("Populating", myTable)
            if not self.populateForcedWarpMeta(num):
                self.logger.infoPair ("failure for ForcedWarpMeta, skipping ", str(num))
                #continue
                raise
            self.tablesToExport.append(myTable)
            
            myTable = "ForcedWarpMeasurement_" + str(num)
            self.logger.infoPair("Populating", myTable)
            if not self.populateForcedWarpMeasurement(num):
                self.logger.infoPair ("failed on ForcedWarpMeasurement", str(num))
                #continue
                raise
            self.tablesToExport.append(myTable)
            tables.append(myTable)
            #self.logger.infoPair("setting min/max objid from", myTable)
 
            #self.setMinMaxObjID([myTable])

            # populateForcedWarpMeasurement also populates ForcedWarpMasked (from ForcedWarpMeasurement)
            self.tablesToExport.append("ForcedWarpMasked_" + str(num))
            
            myTable = "ForcedWarpExtended_" + str(num)
            self.logger.infoPair("Populating", myTable)
            self.populateForcedWarpExtended(num)
            self.tablesToExport.append(myTable)
                        
            if self.haveLensing[num]:
                myTable = "ForcedWarpLensing_" + str(num)
                self.logger.infoPair("Populating", myTable)
                self.populateForcedWarpLensing(num)
                self.tablesToExport.append(myTable)

            myTable = "ForcedWarpToImage_" + str(num)
            self.logger.infoPair("Populating", myTable)
            self.populateForcedWarpToImage(num)
            self.tablesToExport.append(myTable)
        self.logger.infoPair("setting min/max objid from", "all the fw tables")
        self.setMinMaxObjID(tables)
        self.logger.infoPair("finishing","populatePspsTables")

            

        return True

    '''
    Imports IPP tables from FITS file

    Accepts a regular expression filter so not all tables need to be imported
    '''
    def importIppTables(self, columns="*", filter=""):
        if self.config.useOhanaMYSQLtoFITS:
            if not self.importIppTablesOhana():
                self.logger.errorPair("problem running", "importIppTablesOhana")
                return False
        else:
            if not self.importIppTablesStilts(columns, filter):
                self.logger.errorPair("problem running", "importIppTablesStilts")
                return False

        return True

    '''
    Imports IPP tables from FITS file
    '''
    def importIppTablesOhana(self):
        
        self.logger.infoPair("Importing FW tables", "using fits_to_mysql")

        count = 0
        self.tablesLoaded = []
        self.haveSrcPos = {}
        self.haveLensPSF = {}
        self.haveLensing = {}

        for num in self.number:
            forcedWarpID = self.number[num]
            fileName = self.fits[num].getPath()
            
            self.logger.infoPair("loading for FW id", forcedWarpID)
            
            self.logger.infoPair("using filename:",fileName)
        
            ## output table names need to have "_num" appended.  
            fitsOptions  = "-tablesuffix _" + str(num)

            # this option is equivalent to the stilts "addcol table_index $0" call
            fitsOptions += " -sequence-column table_index"
            fitsOptions += " -sequence-column-start 1"

            ## *** actually write the FITS data to mysql
            self.scratchDb.loadFITStoMYSQL(fileName, fitsOptions)

            ## use stilts to get a list of the tables from the header
            try:
                tables = stilts.treads(fileName)
            except:
                self.logger.errorPair("STILTS could not import from", fileName)
                return False
        
            # this is an optional field, check for it in the table below
            self.haveSrcPos[num] = 0
            self.haveLensPSF[num] = 0
            self.haveLensing[num] = 0

            for table in tables:
                # check for AP_NPIX columns: missing for SAS39
                self.logger.debugPair("checking table", table.name)
                if table.name == "SkyChip.psf":
                    columns = table.columns()
                    for column in columns:
                        columnString = str(column)
                        if columnString == 'LENS_X11_SM_PSF':
                            self.haveLensPSF[num] = 1
                            self.logger.debugPair("found LENS_X11_SM_PSF in file", fileName)
                        if columnString == 'LENS_X11_SM_OBJ':
                            self.haveLensing[num] = 1
                            self.logger.debugPair("found LENS_X11_SM_OBJ in file", fileName)
                        if columnString == 'SRC_CHIP_X':
                            self.haveSrcPos[num] = 1
                            self.logger.debugPair("found SRC_CHIP_X in file", fileName)


                myTable = table.name + "_" + str(num)
                count = count + 1
                      
                self.tablesLoaded.append(myTable)

            # after all tables are read for this forcedWarpID, then index the set
            self.indexIppTables(num)
                      
        self.logger.infoPair("Done. Imported", "%d tables" % count)
        return True

    '''
    This function reads the cmf/smf file and loads it into the database as its own table
    '''
    def importIppTablesStilts(self, columns="*", tableRE=""):

        print "*** STILTS import is now deprecated ***"
        os._exit(4)

        self.tablesLoaded = []

        count = 0 
        for num in self.number: 
            forcedWarpID = self.number[num]
            fileName = self.fits[num].getPath()

            self.logger.infoPair("loading for add id", forcedWarpID)
            self.logger.infoPair("Importing FW tables with table match expression: ", tableRE)
            
            self.logger.infoPair("using filename:",fileName)
            
            try:
                tables = stilts.treads(fileName)
            except:
                self.logger.errorPair("STILTS could not import from", fileName)
                return False

            for table in tables:
              
                match = re.match(tableRE, table.name)
                if not match: continue

                self.logger.infoPair("Reading IPP table", table.name+"_"+str(forcedWarpID))
                table = stilts.tpipe(table, cmd='addcol table_index $0')
                table = stilts.tpipe(table, cmd='explodeall')
           
                # drop any previous tables before import
           
                self.scratchDb.dropTable(table.name+"_"+str(forcedWarpID))
                  
                # IPP FITS files are littered with infinities, so remove these
                self.logger.info("Removing Infinity values from all columns")
                table = stilts.tpipe(table, cmd='keepcols "' + columns + '"')
                table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
                table = stilts.tpipe(table, cmd='replaceval Infinity null *')
                  
                try:
                    table.write(self.scratchDb.url + '#' + table.name+"_"+str(forcedWarpID))
                    count = count + 1
                except:
                    self.logger.exception("Problem writing table '" + table.name+"_"+str(forcedWarpID) + "' to the database")

                self.logger.infoPair("Done. Imported", "%d tables" % count)

                self.tablesLoaded.append(table.name)

            # after all tables are read for this forcedWarpID, then index the set
            self.indexIppTables(forcedWarpID)

        return True


    '''
    Updates table and generates pspsuniqueids
    '''
    def generateUniquePspsIDs(self, tableName, num):
        
        sql = "UPDATE " + tableName + " set uniquePspsFWid = (("+str(self.batchID)+"*1000000000 ) + ("+str(num)+"*1000000 ) + row)"
        self.scratchDb.execute(sql)

    '''
    Defines the tables to be exported to psps            
    '''

    def exportPspsTablesToFits(self, regex="(.*)"):
#       return super(ForcedWarpBatch, self).exportPspsTablesToFits("(ForcedWarp.*)")
       return super(ForcedWarpBatch, self).exportPspsTablesToFits("([a-zA-Z]+)")


    '''
    Drop a table and report the drop
    '''
    def dropTableVerbose(self, table):
        self.logger.infoPair("dropping table:",table)
        self.scratchDb.dropTable(table)


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

        self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)")

        return True

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

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

        # it probably should not be an error not to have one of these:
        cmfTable = "SkyChip_psf_" + str(num)
        if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable,  "IPP_IDET")

        cmfTable = "SkyChip_xfit_" + str(num)
        if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable,  "IPP_IDET")

        cmfTable = "SkyChip_xrad_" + str(num)
        if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable,  "IPP_IDET")

        cmfTable = "SkyChip_xsrc_" + str(num) 
        if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable,  "IPP_IDET")

        cmfTable = "SkyChip_xgal_" + str(num) 
        if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable,  "IPP_IDET")

        return True
