#!/usr/bin/env jython

import os.path
import sys

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

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 dvoobjects import DvoObjects

import logging.config

'''
ObjectBatch class

This class, a sub-class of Batch, processes individual DVO region file pairs (cpt and cps files). The cpt file contains a list of all objects in that region, the cps file contains the magnitudes and so has a row per filter per object (so, generally, has rows = 5 x number of objects). The Photcode.dat table us used to figure out the number and order of filters as they appear in the cps file.

'''
class ObjectBatch(Batch):

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

       super(ObjectBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               dvoID,
               batchID,
               "OB", 
               None,
               1)

       try:
           self.dvoObjects = DvoObjects(self.logger, self.config, self.skychunk, self.ippToPspsDb, self.scratchDb.dbName)
       except:
           self.logger.errorPair("Unable to create instance of", "DvoObjects")
           raise

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

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

    '''
    Overriden from batch base-class. We import from DVO directly for objects
    '''
    def importIppTables(self, columns="*", filter=""):

        self.region = self.scratchDb.getRegionNameFromThisDvoIndex(self.id)
        self.ippToPspsDb.insertObjectMeta(self.batchID, self.region)
        if True:
            self.dvoObjects.nativeIngestRegion(self.region)
        else:
            self.dvoObjects.ingestRegion(self.region)

        return True

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

        # dec is reserved in MySQL, so STILTS replaces if with dec_, which PSPS doesn't like. so, force it back again using ``
        self.scratchDb.execute("ALTER TABLE Object CHANGE dec_ `dec` double")

        return True

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

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

        return True

    '''
    Inserts stuff for all mags
    '''
    def insertMags(self, cpsTable):

        # list of all filters PSPS is interested in
        interestedFilters = ['g', 'r', 'i', 'z', 'y']
        
        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
    
        # get a count of the available filters
        filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable()
        # filterCount = len(filters)

        self.logger.infoPair("Available filters in Photcodes", filters)

        # the 'code' now defines the order in the cps file that the mags are listed for a given filter
        self.logger.infoPair("Adding magnitudes from", "cps table")
        for filter in filters:

            filterID = self.scratchDb.getFilterID(filter[1])

            # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000)
	    # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags

            sql = "UPDATE Object JOIN \
                   " + cpsTable + " AS cps ON (cps.row = (Object.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
                   SET \
                   n" + filter[1] + " = NCODE \
                   ," + filter[1] + "MeanPSFMag = MAG \
                   ," + filter[1] + "MeanPSFMagErr = MAG_ERR \
                   ," + filter[1] + "MeanKronMag = MAG_KRON \
                   ," + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
                   ," + filter[1] + "StackPSFMag = -2.5 * log10(FLUX_PSF) + 8.9 \
                   ," + filter[1] + "StackPSFMagErr = FLUX_PSF_ERR / FLUX_PSF \
                   ," + filter[1] + "StackKronMag = -2.5 * log10(FLUX_KRON) + 8.9 \
                   ," + filter[1] + "StackKronMagErr = FLUX_KRON_ERR / FLUX_KRON \
                   ," + filter[1] + "20pct = MAG_20/1000 \
                   ," + filter[1] + "80pct = MAG_80/1000 \
                   ," + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) \
                   ," + filter[1] + "StackDetectID = STACK_DETECT_ID"

            #self.logger.info(sql)
            self.scratchDb.execute(sql)

        self.logger.infoPair("Calculating nDetections from", "n[filters]")
        for filter in filters:
            # now do a sum of n[filters], but do not include the ones with -999
            sql = "UPDATE Object \
                   SET nDetections = nDetections + n" + filter[1] + " \
                   WHERE n" + filter[1] + " != -999"
            self.scratchDb.execute(sql)
            



        # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12)
        cut = 0.5
        self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut)
        for filter in filters:

            sql = "UPDATE Object \
                   SET " + filter[1] + "MeanPSFMagErr = null \
                   WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
            self.scratchDb.execute(sql)

        self.logger.infoPair("Setting to NULL all x20pct =", "32.767" ) 
        for filter in filters:
            sql  = "UPDATE Object \
                   SET " + filter[1] + "20pct = null \
                   WHERE " + filter[1] + "20pct > 32.766"
            self.scratchDb.execute(sql)
        self.logger.infoPair("Setting to NULL all x80pct =", "32.767" )
        for filter in filters:
            sql  = "UPDATE Object \
                    SET " + filter[1] + "80pct = null \
                    WHERE " + filter[1] + "80pct > 32.766"
            self.scratchDb.execute(sql)




    '''
    Inserts colors
    '''
    def updateColors(self):

        self.logger.infoPair("Calculating", "colors")
        sql = "UPDATE Object \
               SET \
               grMeanColor = (gMeanMag - rMeanMag) \
               ,riMeanColor = (rMeanMag - iMeanMag) \
               ,izMeanColor = (iMeanMag - zMeanMag) \
               ,zyMeanColor = (zMeanMag - yMeanMag) \
               ,grMeanColorErr = SQRT(POW(gMeanMagErr,2) + POW(rMeanMagErr,2)) \
               ,riMeanColorErr = SQRT(POW(rMeanMagErr,2) + POW(iMeanMagErr,2)) \
               ,izMeanColorErr = SQRT(POW(iMeanMagErr,2) + POW(zMeanMagErr,2)) \
               ,zyMeanColorErr = SQRT(POW(zMeanMagErr,2) + POW(yMeanMagErr,2)) \
                   "
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't calculate colors", sql)

    '''
    Populates the Object table
    '''
    def populateObjectTable(self):

        cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
        cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps")

        self.logger.infoPair("setting to null  > 1e-38 and < 1e-38 in", "cps FLUX_KRON_ERR")
        sql = "UPDATE " + cpsTableName + " set FLUX_KRON_ERR = NULL where FLUX_KRON_ERR < 1e-37 AND FLUX_KRON_ERR > -1e-37 "
      
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't squash out of range stuff", sql)
            return False

        self.logger.infoPair("setting to null  > 1e-38 and < 1e-38 in", "cps FLUX_KRON")
        sql = "UPDATE " + cpsTableName + " set FLUX_KRON = NULL where FLUX_KRON < 1e-37 AND FLUX_KRON > -1e-37 "
      
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't squash out of range stuff", sql)
            return False




        
        self.logger.infoPair("Populating", "Object s")
        self.logger.infoPair("Inserting objects from", "cpt file")

        # note `` around dec here, as this is a reserved word in MySQL
        sql = "INSERT IGNORE INTO Object (\
               objID \
               ,ippObjID \
               ,objInfoFlag \
               ,qualityFlag \
               ,surveyID \
               ,ra \
               ,`dec` \
               ,raErr \
               ,decErr \
               ,nDetections \
               ,qfPerfect \
               ,sgSep \
               ,dataRelease \
               ,Random \
               ) \
               SELECT \
               EXT_ID \
               ,CAT_ID*1000000000 + OBJ_ID \
               ,FLAGS \
               ,FLAGS >> 24 & 0xFF \
               ," + str(self.surveyID) + " \
               ,RA \
               ,DEC_ \
               ,RA_ERR \
               ,DEC_ERR \
               ,0 \
               ,PSF_QF_PERF \
               ,STARGAL_SEP \
               , " + str(self.skychunk.dataRelease) + "\
               , RAND() \
               FROM " + cptTableName

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't populate Object table", sql)
            return False
 
        # add row count columns so we can perform joins to get colors
        self.logger.infoPair("Adding 'row' columns to", "Object and cps tables")
        self.scratchDb.addRowCountColumn("Object", "row")
        self.scratchDb.addRowCountColumn(cpsTableName, "row")

        self.insertMags(cpsTableName)

#don't do this as we removed those columns.. 
#        self.updateColors()

        #objects can have out of range ra dec in dvo - need to find and kill them at the end

        self.logger.infoPair("Determining", "ra/dec range")

        raMin = self.scratchDb.getFromdvoSkyTable("R_MIN",self.region)
        raMax = self.scratchDb.getFromdvoSkyTable("R_MAX",self.region)
        decMin = self.scratchDb.getFromdvoSkyTable("D_MIN",self.region)
        decMax = self.scratchDb.getFromdvoSkyTable("D_MAX",self.region)

        self.logger.infoPair("R_MIN", raMin)
        self.logger.infoPair("R_MAX", raMax)
        self.logger.infoPair("D_MIN", decMin)
        self.logger.infoPair("D_MAX", decMax)
        #count out of range

        sql = "SELECT count(*) FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin)       
    
        rs = self.scratchDb.executeQuery(sql)
        rs.first()
        nToDelete = rs.getInt(1)
        
        #delete out of range
        
 
        sql = "DELETE FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin) 
        self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range")

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't cull outsiders from Object table", sql)
            return False

        self.logger.infoPair("Dropping row column from", "Object table")
        self.scratchDb.dropColumn("Object", "row")
        self.logger.infoPair("Purging from scratch Db", self.region + " region")

        self.dvoObjects.purgeRegion(self.region)

        self.setMinMaxObjID(["Object"])

        return True

    '''
    Populates the ObjectCalColor table
    '''
    def populateObjectCalColorTable(self):

        self.logger.infoPair("Populating", "ObjectCalColor")

        sql = "INSERT INTO ObjectCalColor ( \
               objID \
               ,ippObjID \
               ,dataRelease \
               ) \
               SELECT \
               objID \
               ,ippObjID \
               ,dataRelease \
               FROM Object"
        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair("Couldn't populate ObjectCalColor table", sql)
            return False

        return True

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

        if not self.populateObjectTable(): return False
        #if not self.populateObjectCalColorTable(): return False

        # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number
        self.logger.infoPair("Forcing uniqueness on", "objID in Object table")
        rowCountBefore = self.scratchDb.getRowCount("Object")
        self.scratchDb.execute("ALTER IGNORE TABLE Object ADD UNIQUE INDEX(objID)")
        rowCountAfter = self.scratchDb.getRowCount("Object")
        self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))

        return True

