#!/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, scratchDbName):

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

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

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

       cpmPath = self.config.dvoLocation + "/" + region + ".cpm"
       cptPath = self.config.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",
                   cpmTableName)

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

           # 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",
               cptTableName)

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

       self.logger.infoPair("Adding","PSPS objIDs")
       # first try to add a column for PSPS_OBJ_ID. catch failure and continue
       try:
           sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (PSPS_OBJ_ID BIGINT)"
           self.scratchDb.execute(sql)
       except:
           self.logger.infoPair("Already added PSPS_OBJ_ID column to", cpmTableName)
               
       # shove PSPS objIDs from cpt table into measurement table
       sql = "UPDATE " + cpmTableName + " AS a, " + cptTableName + " AS b \
              SET a.PSPS_OBJ_ID = b.EXT_ID \
              WHERE a.CAT_ID = b.CAT_ID \
              AND a.OBJ_ID = b.OBJ_ID"
       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
       self.logger.infoPair("Merging into", self.scratchDb.dvoDetectionTable)
       sql = "INSERT IGNORE INTO " + self.scratchDb.dvoDetectionTable + " (\
              imageID \
              ,ippDetectID \
              ,detectID \
              ,ippObjID \
              ,objID \
              ,flags \
              ,fileID \
              ) SELECT \
              IMAGE_ID \
              ,DET_ID \
              ,EXT_ID \
              ,CAT_ID * 1000000000 + OBJ_ID \
              ,PSPS_OBJ_ID \
              ,DB_FLAGS \
              , " + str(fileId) + " \
              FROM " + cpmTableName
       try:
           self.scratchDb.execute(sql)
       except:
           self.logger.error("FAILED: " + sql)
           return False
       finally:
           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


