#!/usr/bin/env jython

import stilts
from java.lang import *
from java.sql import *
from batch import Batch

class StackBatch(Batch):

    '''
    Constructor
    '''
    def __init__(self):
       super(StackBatch, self).__init__(
               "stack", 
               #"/data/ipp053.0/eugene/md04.20110320/staticsky/MD04.V2/skycell.087/MD04.V2.skycell.087.stk.280.000.cmf", 
               "demo.fits", 
               "stack.fits",
               "localhost",
               "ipptopsps_scratch",
               "ipp",
               "ipp",
               "MD04") # TODO



    '''
    Updates a table with stackMetaID
    '''
    def updateStackMetaID(self, table):

        sql = "UPDATE " + table + "  SET stackMetaID=" + self.header['STK_ID']
        self.stmt.execute(sql)

    '''
    Updates a table with stackTypeID grabbed from FitModel init table
    '''
    def updateStackTypeID(self, table):

        sql = "UPDATE "+table+" AS a, StackType AS b SET a.stackTypeID=b.stackTypeID WHERE b.name = '"+self.header['STK_TYPE']+"'"
        self.stmt.execute(sql)


    '''
    Updates aperture fluxes for StackApFkx table
    '''
    def updateApFlxs(self, prefix, psfCondition):

        sql = "UPDATE StackApFlx AS a, SkyChip_xrad AS b SET \
        "+prefix+"flxR1=b.APER_FLUX_1, \
        "+prefix+"flxR1Err=b.APER_FLUX_ERR_1, \
        "+prefix+"flxR1Std=b.APER_FLUX_STDEV_1, \
        "+prefix+"flxR1Fill=b.APER_FILL_1, \
        "+prefix+"flxR2=b.APER_FLUX_2, \
        "+prefix+"flxR2Err=b.APER_FLUX_ERR_2, \
        "+prefix+"flxR2Std=b.APER_FLUX_STDEV_2, \
        "+prefix+"flxR2Fill=b.APER_FILL_2, \
        "+prefix+"flxR3=b.APER_FLUX_3, \
        "+prefix+"flxR3Err=b.APER_FLUX_ERR_3, \
        "+prefix+"flxR3Std=b.APER_FLUX_STDEV_3, \
        "+prefix+"flxR3Fill=b.APER_FILL_3, \
        "+prefix+"flxR4=b.APER_FLUX_4, \
        "+prefix+"flxR4Err=b.APER_FLUX_ERR_4, \
        "+prefix+"flxR4Std=b.APER_FLUX_STDEV_4, \
        "+prefix+"flxR4Fill=b.APER_FILL_4, \
        "+prefix+"flxR5=b.APER_FLUX_5, \
        "+prefix+"flxR5Err=b.APER_FLUX_ERR_5, \
        "+prefix+"flxR5Std=b.APER_FLUX_STDEV_5, \
        "+prefix+"flxR5Fill=b.APER_FILL_5, \
        "+prefix+"flxR6=b.APER_FLUX_6, \
        "+prefix+"flxR6Err=b.APER_FLUX_ERR_6, \
        "+prefix+"flxR6Std=b.APER_FLUX_STDEV_6, \
        "+prefix+"flxR6Fill=b.APER_FILL_6, \
        "+prefix+"flxR7=b.APER_FLUX_7, \
        "+prefix+"flxR7Err=b.APER_FLUX_ERR_7, \
        "+prefix+"flxR7Std=b.APER_FLUX_STDEV_7, \
        "+prefix+"flxR7Fill=b.APER_FILL_7, \
        "+prefix+"flxR8=b.APER_FLUX_8, \
        "+prefix+"flxR8Err=b.APER_FLUX_ERR_8, \
        "+prefix+"flxR8Std=b.APER_FLUX_STDEV_8, \
        "+prefix+"flxR8Fill=b.APER_FILL_8, \
        "+prefix+"flxR9=b.APER_FLUX_9, \
        "+prefix+"flxR9Err=b.APER_FLUX_ERR_9, \
        "+prefix+"flxR9Std=b.APER_FLUX_STDEV_9, \
        "+prefix+"flxR9Fill=b.APER_FILL_9, \
        "+prefix+"flxR10=b.APER_FLUX_10, \
        "+prefix+"flxR10Err=b.APER_FLUX_ERR_10, \
        "+prefix+"flxR10Std=b.APER_FLUX_STDEV_10, \
        "+prefix+"flxR10Fill=b.APER_FILL_10 \
        WHERE a.ippDetectID=b.IPP_IDET AND b.PSF_FWHM "+psfCondition

        self.stmt.execute(sql)

    '''
    Updates parameters for a particular model in the StackModelFit table
    '''
    def updateModelFit(self, model, ippModelType):

        if model == "ser":
            modelLong = "sersic"
        else:
            modelLong = model;

        sql = "UPDATE StackModelFit AS a, SkyChip_xfit AS b SET \
        "+model+"Radius=b.EXT_WIDTH_MAJ,  \
        "+model+"Mag=b.EXT_INST_MAG,  \
        "+model+"MagErr=b.EXT_INST_MAG_SIG,  \
        "+model+"Ab=b.EXT_WIDTH_MAJ/b.EXT_WIDTH_MIN, \
        "+model+"Phi=b.EXT_THETA,  \
        "+modelLong+"Covar11=b.EXT_COVAR_00_00,  \
        "+modelLong+"Covar12=b.EXT_COVAR_00_01,  \
        "+modelLong+"Covar13=b.EXT_COVAR_00_02,  \
        "+modelLong+"Covar14=b.EXT_COVAR_00_03,  \
        "+modelLong+"Covar15=b.EXT_COVAR_00_04,  \
        "+modelLong+"Covar16=b.EXT_COVAR_00_05,  \
        "+modelLong+"Covar17=b.EXT_COVAR_00_06,  \
        "+modelLong+"Covar22=b.EXT_COVAR_01_01,  \
        "+modelLong+"Covar23=b.EXT_COVAR_01_02,  \
        "+modelLong+"Covar24=b.EXT_COVAR_01_03,  \
        "+modelLong+"Covar25=b.EXT_COVAR_01_04,  \
        "+modelLong+"Covar26=b.EXT_COVAR_01_05,  \
        "+modelLong+"Covar27=b.EXT_COVAR_01_06,  \
        "+modelLong+"Covar33=b.EXT_COVAR_02_02,  \
        "+modelLong+"Covar34=b.EXT_COVAR_02_03,  \
        "+modelLong+"Covar35=b.EXT_COVAR_02_04,  \
        "+modelLong+"Covar36=b.EXT_COVAR_02_05,  \
        "+modelLong+"Covar37=b.EXT_COVAR_02_06,  \
        "+modelLong+"Covar44=b.EXT_COVAR_03_03,  \
        "+modelLong+"Covar45=b.EXT_COVAR_03_04,  \
        "+modelLong+"Covar46=b.EXT_COVAR_03_05,  \
        "+modelLong+"Covar47=b.EXT_COVAR_03_06,  \
        "+modelLong+"Covar55=b.EXT_COVAR_04_04,  \
        "+modelLong+"Covar56=b.EXT_COVAR_04_05,  \
        "+modelLong+"Covar57=b.EXT_COVAR_04_06,  \
        "+modelLong+"Covar66=b.EXT_COVAR_05_05,  \
        "+modelLong+"Covar67=b.EXT_COVAR_05_06,  \
        "+modelLong+"Covar77=b.EXT_COVAR_06_06   \
        WHERE a.ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'"

        self.stmt.execute(sql)

        # sersic fit has an extra parameter
        if ippModelType == "PS_MODEL_SERSIC":
            sql = "UPDATE StackModelFit AS a, SkyChip_xfit AS b SET \
            "+modelLong+"Covar18=b.EXT_COVAR_00_07,  \
            "+modelLong+"Covar28=b.EXT_COVAR_01_07,  \
            "+modelLong+"Covar38=b.EXT_COVAR_02_07,  \
            "+modelLong+"Covar48=b.EXT_COVAR_03_07,  \
            "+modelLong+"Covar58=b.EXT_COVAR_04_07,  \
            "+modelLong+"Covar68=b.EXT_COVAR_05_07,  \
            "+modelLong+"Covar78=b.EXT_COVAR_06_07,  \
            "+modelLong+"Covar88=b.EXT_COVAR_07_07   \
            WHERE a.ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'"

            self.stmt.execute(sql)


    '''
    Populates the StackMeta table, mainly from dictionary values found in IPP FITS header
    '''
    def populateStackMeta(self):
        self.log("Procesing StackMeta table")

        sql = "INSERT INTO StackMeta (\
        stackMetaID \
        ,skycellID \
        ,photoZero \
        ,nP2Images \
        ,ctype1 \
        ,ctype2 \
        ,crval1 \
        ,crval2 \
        ,crpix1 \
        ,crpix2 \
        ,cdelt1 \
        ,cdelt2 \
        ,pc001001 \
        ,pc001002 \
        ,pc002001 \
        ,pc002002 \
         ) VALUES ( \
        " + self.header['STK_ID'] + " \
        ," + self.skycell + " \
        ," + self.header['FPA.ZP'] + " \
        ," + self.header['NINPUTS'] + " \
        ,'" + self.header['CTYPE1'] + "' \
        ,'" + self.header['CTYPE2'] + "' \
        ," + self.header['CRVAL1'] + " \
        ," + self.header['CRVAL2'] + " \
        ," + self.header['CRPIX1'] + " \
        ," + self.header['CRPIX2'] + " \
        ," + self.header['CDELT1'] + " \
        ," + self.header['CDELT2'] + " \
        ," + self.header['PC001001'] + " \
        ," + self.header['PC001002'] + " \
        ," + self.header['PC002001'] + " \
        ," + self.header['PC002002'] + " \
        )"
        self.stmt.execute(sql)

        self.updateSurveyID("StackMeta")
        self.updateFilterID("StackMeta")
        self.updateStackTypeID("StackMeta")

    '''
    Populates the StackDetection table
    '''
    def populateStackDetection(self):
        self.log("Procesing StackDetection table")

        # insert all the detections
        sql = "INSERT INTO StackDetection (\
               ippDetectID \
               ,xPos \
               ,yPos \
               ,xPosErr \
               ,yPosErr \
               ,instFlux \
               ,instFluxErr \
               ,peakFlux \
               ,sky \
               ,skyErr \
               ,sgSep \
               ,psfWidMajor \
               ,psfWidMinor \
               ,psfTheta \
               ,psfCf \
               ,nFrames \
               ) \
               SELECT \
               IPP_IDET \
               ,X_PSF \
               ,Y_PSF \
               ,X_PSF_SIG \
               ,Y_PSF_SIG \
               ,PSF_INST_FLUX \
               ,PSF_INST_FLUX_SIG \
               ,POW(10.0, (-0.4*PEAK_FLUX_AS_MAG)) / "+self.header['EXPTIME']+" \
               ,SKY \
               ,SKY_SIGMA \
               ,EXT_NSIGMA \
               ,PSF_MAJOR \
               ,PSF_MINOR \
               ,PSF_THETA \
               ,PSF_QF \
               ,N_FRAMES \
               FROM SkyChip_psf"

        self.stmt.execute(sql)

        if self.header['STK_TYPE'] != "NIGHTLY_STACK": 
            sql = "UPDATE StackDetection SET obsTime = " + self.header['MJD-OBS']
            self.stmt.execute(sql)


        sql = "UPDATE StackDetection SET skycellID = " + self.skycell + ", assocDate = '"+self.dateStr+"'"
        self.stmt.execute(sql)
        self.updateSurveyID("StackDetection")
        self.updateFilterID("StackDetection")
        self.updateStackMetaID("StackDetection")
        self.updateStackTypeID("StackDetection")

    '''
    Populates the StackApFlx table
    '''
    def populateStackApFlx(self):
        self.log("Procesing StackApFlx table")
 
        sql = "INSERT INTO StackApFlx \
               (ippDetectID ) \
               SELECT \
               DISTINCT IPP_IDET \
               FROM SkyChip_xrad"
        self.stmt.execute(sql)

        self.log("    Adding 1st convolved fluxes")
        self.updateApFlxs("c1", "< 7.0")
        self.log("    Adding 2nd convolved fluxes")
        self.updateApFlxs("c2", "> 7.0")

        self.log("    Adding petrosian stuff for extended sources")
        sql = "UPDATE StackApFlx AS a, SkyChip_xsrc AS b SET \
        petRadius=b.PETRO_RADIUS \
        ,petRadiusErr=b.PETRO_RADIUS_ERR \
        ,petMag=b.PETRO_MAG \
        ,petMagErr=b.PETRO_MAG_ERR \
        ,petR50=b.PETRO_RADIUS_50 \
        ,petR50Err=b.PETRO_RADIUS_50_ERR \
        ,petR90=b.PETRO_RADIUS_90 \
        ,petR90Err=b.PETRO_RADIUS_90_ERR \
        WHERE a.ippDetectID=b.IPP_IDET"
        self.stmt.execute(sql)

        self.updateSurveyID("StackApFlx")
        self.updateFilterID("StackApFlx")
        self.updateStackMetaID("StackApFlx")
        self.updateStackTypeID("StackApFlx")

        #rs = stmt.executeQuery(sql)
        # list = []

        #while (rs.next()):

        #       print rs.getString(1)

        #rs.close()

    '''
    Populates the StackModelFit table
    '''
    def populateStackModelFit(self):
        self.log("Procesing StackModelFit table")

        # insert all the detections
        sql = "INSERT INTO StackModelFit (ippDetectID) SELECT DISTINCT IPP_IDET from SkyChip_xfit"
        self.stmt.execute(sql)

        # populate model parameters
        self.log("    Adding deVaucouleurs fit")
        self.updateModelFit("deV", "PS_MODEL_DEV")
        self.log("    Adding exponential fit")
        self.updateModelFit("exp", "PS_MODEL_EXP")
        self.log("    Adding sersic fit")
        self.updateModelFit("ser", "PS_MODEL_SERSIC")

        self.updateSurveyID("StackModelFit")
        self.updateFilterID("StackModelFit")
        self.updateStackMetaID("StackModelFit")
        self.updateStackTypeID("StackModelFit")


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

        self.log("Creating indexes on PSPS tables")
        self.createIndex("StackDetection", "ippDetectID")
        self.createIndex("StackApFlx", "ippDetectID")
        self.createIndex("StackModelFit", "ippDetectID")

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

        self.log("Creating indexes on IPP tables")
        self.createIndex("SkyChip_psf", "IPP_IDET")
        self.createIndex("SkyChip_xfit", "IPP_IDET")
        self.createIndex("SkyChip_xrad", "IPP_IDET")
        self.createIndex("SkyChip_xsrc", "IPP_IDET")

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

        # determine skycell from header value
        self.skycell = self.header['SKYCELL']
        self.skycell = self.skycell[8:]

        # get filterID using init table
        self.filter = self.header['FPA.FILTER']
        self.filter = self.filter[0:1]

        self.populateStackMeta()
        self.populateStackDetection()
        self.populateStackModelFit()
        self.populateStackApFlx()

    
stackBatch = StackBatch()
stackBatch.createEmptyPspsTables()
stackBatch.importIppTables(".*psf")
stackBatch.populatePspsTables()
stackBatch.exportPspsTablesToFits()
