#!/usr/bin/env jython

import stilts
import datetime
import re
import sys
import os
import logging
import glob

from dvo import Dvo


'''
A class for ingesting DVO detections into MySQL
'''
class DvoDetections(Dvo):

    '''
    Constructor
    '''
    def __init__(self, logger, config, skychunk, ippToPspsDb, scratchDbName):

        super(DvoDetections, self).__init__(logger, config, skychunk, ippToPspsDb, scratchDbName)

        # declare DVO file types of interest
        self.ingestFileTypes = ['cpm','cpt']

    '''
    Ingests detections for this region
    '''
    def ingestRegion(self, region):

       cpmPath = self.skychunk.dvoLocation + "/" + region + ".cpm"
       cptPath = self.skychunk.dvoLocation + "/" + region + ".cpt"

       cpmTableName = self.scratchDb.getDbFriendlyTableName(region + ".cpm")
       cptTableName = self.scratchDb.getDbFriendlyTableName(region + ".cpt")

       # if we have not already ingested the cpm table, then do it now
       if not self.scratchDb.alreadyImportedThisDvoTable(cpmPath):
   
           # import cpm table and index
           self.importFits(
                   cpmPath,
                   "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS PHOTCODE AIRMASS M_TIME M_CAL MAG_CAL_ERR D_RA D_DEC PLTSCALE X_CCD_ERR Y_CCD_ERR",
                   cpmTableName)

           self.scratchDb.createIndex(cpmTableName, "IMAGE_ID")
           self.scratchDb.createIndex(cpmTableName, "CAT_ID")
           self.scratchDb.createIndex(cpmTableName, "OBJ_ID")

           # XXX: Should we create index on PHOTCODE?

           # we can now report that we have imported this table
           self.scratchDb.setImportedThisDvoTable(cpmPath, region)

       # get the id for this cpm file so we can insert it in the dvoDetection table
       fileId = self.scratchDb.getDvoFileId(cpmPath)

       # import cpt table and index
       self.importFits(
               cptPath,
               "OBJ_ID CAT_ID EXT_ID RA DEC",
               cptTableName)

       self.scratchDb.createIndex(cptTableName, "IMAGE_ID")
       self.scratchDb.createIndex(cptTableName, "CAT_ID")
       self.scratchDb.createIndex(cptTableName, "OBJ_ID")

       # SA10
       # Columns needed for DetectionCalib and StackDetectionCalib
       # zp      = photcode.K * (AIRMASS - 1) + photcode.C_LAM * 0.001 - cpm.M_CAL
       # zpErr   = cpm.MAG_CAL_ERR
       # airMass = cpm.AIRMASS
       # expTime = POW(10.0, 0.4 * cpm.M_TIME)
       # ra      = cpt.RA  - (cpm.D_RA  / 3600.0)
       # dec     = cpt.DEC - (cpm.D_DEC / 3600.0)
       # raErr   = cpm.X_CCD_ERR * cpm.PLTSCALE
       # decErr  = cpm.Y_CCD_ERR * cpm.PLTSCALE

       self.logger.infoPair("Adding","PSPS objIDs")
       # first try to add the new columns. catch failure and continue
       try:
           sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (PSPS_OBJ_ID BIGINT), ADD COLUMN (ZP REAL), ADD COLUMN (RA FLOAT), ADD COLUMN (DEC_ FLOAT)"
           self.scratchDb.execute(sql)
       except:
           # failed, but may be due to mysql error
           self.logger.infoPair("mysql error or already added PSPS_OBJ_ID column to", cpmTableName)
           # self.logger.infoPair("sql command", sql)
               
       # shove PSPS objIDs from cpt table and the calibrated zero point and coordinates which are formed 
       # by combining values from multiple tables into cpt table
       sql = "UPDATE " \
              + cpmTableName + " AS meas, " \
              + cptTableName + " AS ave, " \
              + self.scratchDb.dvoPhotcodesTable + " AS phot \
              SET \
              meas.PSPS_OBJ_ID = ave.EXT_ID, \
              meas.ZP  = phot.C_LAM * 0.001 + phot.K * (meas.AIRMASS - 1) - meas.M_CAL, \
              meas.RA  = ave.RA  - (meas.D_RA  / 3600.0), \
              meas.DEC_ = ave.DEC_ - (meas.D_DEC / 3600.0) \
              WHERE meas.CAT_ID = ave.CAT_ID \
              AND meas.OBJ_ID = ave.OBJ_ID   \
              AND meas.PHOTCODE = phot.CODE"

       # EAM : add some debug I/O
       # self.logger.infoPair("sql command", sql)
       self.scratchDb.execute(sql)

       # NB we use an INSERT IGNORE here. This is because of a known issue where multiple DVO cpm
       # files can include the same detection, with the same IMAGE_ID/DET_ID pairing, but different
       # PSPS object IDs assigned in the corresponding cpt file. This is believed to be a chip-boundary 
       # issue within DVO. So, for now, we take the first IMAGE_ID/DET_ID detection we find, and ignore the rest
       # XXX: Bills 2012-10031 Does this problem still exist?
       self.logger.infoPair("Merging into", self.scratchDb.dvoDetectionTable)
       sql = "INSERT IGNORE INTO " + self.scratchDb.dvoDetectionTable + " (\
              imageID \
              ,ippDetectID \
              ,detectID \
              ,ippObjID \
              ,objID \
              ,flags \
              ,zp   \
              ,zpErr    \
              ,airMass  \
              ,expTime  \
              ,ra   \
              ,dec_  \
              ,raErr    \
              ,decErr   \
              ,fileID \
              ) SELECT \
              IMAGE_ID \
              ,DET_ID \
              ,EXT_ID \
              ,CAT_ID * 1000000000 + OBJ_ID \
              ,PSPS_OBJ_ID \
              ,DB_FLAGS \
              ,ZP   \
              ,MAG_CAL_ERR  \
              ,AIRMASS  \
              ,POW(10.0, 0.4 * M_TIME)  \
              ,RA   \
              ,DEC_  \
              ,X_CCD_ERR*PLTSCALE   \
              ,Y_CCD_ERR*PLTSCALE   \
              , " + str(fileId) + " \
              FROM " + cpmTableName

       # print "sql :", sql
       # response = raw_input("prepare to insert dvoDetectionTable ")

       try:
           self.scratchDb.execute(sql)
       except:
           self.logger.error("FAILED: " + sql)
           return False
       finally:
           # response = raw_input("insert dvoDetectionTable ")
           self.logger.infoPair("Dropping tables", "%s and %s" % (cpmTableName, cptTableName))
           self.scratchDb.dropTable(cpmTableName)
           self.scratchDb.dropTable(cptTableName)

       self.scratchDb.setImportedThisDvoTable(cptPath, region)

       return True
