#!/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,
                 gpc1Db,
                 ippToPspsDb,
                 scratchDb,
                 dvoID,
                 batchID,
                 useFullTables):

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

       try:
           self.dvoObjects = DvoObjects(self.logger, self.config, 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)
        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 = 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:

            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] + "MeanMag = MAG \
                   ," + filter[1] + "MeanFlux = 3631 * POW(10.0, (-0.4*MAG)) \
                   ," + filter[1] + "MeanMagErr = MAG_ERR \
                   ," + filter[1] + "Min = MAG_20/1000 \
                   ," + filter[1] + "Max = MAG_80/1000 \
                   "
            
            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] + "MeanMagErr = null \
                   WHERE " + filter[1] + "MeanMagErr > " + str(cut)
            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("Populating", "ObjectCalColor")
        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 \
               ,varFlag \
               ,surveyID \
               ,ra \
               ,`dec` \
               ,raErr \
               ,decErr \
               ,nDetections \
               ,dataRelease \
               ) \
               SELECT \
               EXT_ID \
               ,CAT_ID*1000000000 + OBJ_ID \
               ,FLAGS \
               ,0 \
               ," + str(self.surveyID) + " \
               ,RA \
               ,DEC_ \
               ,RA_ERR \
               ,DEC_ERR \
               ,NMEASURE \
               , " + str(self.config.dataRelease) + "\
               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)

        self.updateColors()

        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

