#!/usr/bin/env jython

import re
import sys
import os
import logging

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

from mysql import MySql

'''
Class for local scratch database connectivity
'''
class ScratchDb(MySql):

    '''
    Constructor
    '''
    def __init__(self, logger, config):

        # create gpc1 database objects
        # XXX the test option is probably not needed since
        # the scratchDb config infor is set up specially
        if (config.test): 
            dbType = "localdatabase_test"
        else:
            dbType = "localdatabase"

        # print "trying to connect: ", dbType

        dbName = config.getDbName(dbType)

        super(ScratchDb, self).__init__(logger, config, dbType, dbName)

        self.dvoDoneTable = "dvoDone"
        self.dvoSkyTable = "dvoSkyTable"
        self.dvoPhotcodesTable = "dvoPhotcodes"
        self.memoryTableSize = config.getDbMemory(dbType)

    '''
    Destructor
    '''
    def __del__(self):

        self.logger.debug("ScratchDb destructor")


    '''
    Sets whether we are using normal or pre-ingested DVO tables
    '''
    def setDvoTableNames(self):

        self.dvoImagesTable = "dvoImagesFull"
        self.dvoDetectionTable = "dvoDetectionFull"

        self.logger.debugPair("Using DVO images table", self.dvoImagesTable)
        self.logger.debugPair("Using DVO detection table", self.dvoDetectionTable)

    '''
    Creates a stored procedure for psfLikelihood. This uses an implementation of the complimentary error function taken from Numerical Recipes in C.
    '''
    def createPsfLikelihoodFunction(self):

        sql = "DROP FUNCTION IF EXISTS `psfLikelihood`"
        try:
            self.execute(sql)
        except:
            self.logger.errorPair("Could not drop stored procedure", "psfLikelihood")
            return False

        sql = "CREATE FUNCTION `psfLikelihood`(x REAL) RETURNS REAL \
               DETERMINISTIC \
               COMMENT 'Returns the psfLikelihood using the complimentary error function with fractional error everywhere less than 1.2 x 10-7. From Numerical Recipes in C' \
               BEGIN \
               DECLARE ans REAL; \
               DECLARE t REAL; \
               DECLARE z REAL; \
               DECLARE s INT; \
               SET s= SIGN(x); \
               SET x = ABS(x) / 1.4142135623731; \
               SET z=ABS(x); \
               SET t=1.0 / (1.0 + 0.5*z); \
               SET ans = t * EXP(-z*z-1.26551223+t*(1.00002368+t*(.37409196+t*(.09678418+t*(-.18628806+t*(.27886807+t*(-1.13520398+t*(1.48851587+t*(-.82215223+t*.17087277))))))))); \
               IF x < 0.0 THEN \
               SET ans = 2.0 - ans; \
               END IF; \
               SET ans = ans * s; \
               RETURN ans; \
               END"

        try:
            self.execute(sql)
            self.logger.debugPair("Installed stored procedure", "psfLikelihood")
            return True
        except:
            self.logger.errorPair("Could not install stored procedure", "psfLikelihood")
            return False

    '''
    Gets survey ID for this survey name
    '''
    def getSurveyID(self, name):
     
        sql = "SELECT surveyID FROM Survey WHERE name = '" + name + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            surveyID = rs.getInt(1)
        except:
            self.logger.errorPair("No survey ID found for survey", name)
            surveyID = -1

        return surveyID

    '''
    Gets filter ID for this filter name
    '''
    def getFilterID(self, name):
     
        sql = "SELECT filterID FROM Filter WHERE filterType = '" + name + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            filterID = rs.getInt(1)
        except:
            self.logger.errorPair("No filter ID found for filter", name)
            filterID = -1

        return filterID

    '''
    Gets tess ID for this tess name
    '''
    def getTessID(self, name):
     
        sql = "SELECT tessID FROM TessellationType WHERE name = '" + name + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            tessID = rs.getInt(1)
        except:
            self.logger.errorPair("No tess ID found for tess", name)
            tessID = -1

        return tessID

    ''' 
    Gets imageID from extern ID 
    ''' 
    def getImageIDFromExternID(self, externID): 
               
        imageID = -1 
               
        sql = "SELECT IMAGE_ID FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID) 
        try: 
            rs = self.executeQuery(sql)   
            rs.first() 
            imageID = rs.getInt(1) 
        except: 
            self.logger.errorPair("Unable to get imageID from dvo using", sql) 
              
        return imageID 

    '''
    Gets DVO image flags
    '''
    def getDvoImageFlags(self, externID):

        flags = 0

        sql = "SELECT FLAGS FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
        try:
            rs = self.executeQuery(sql)  
            rs.first()
            flags = rs.getInt(1)
        except:
            self.logger.errorPair("Unable to get flags from dvo using", sql)
        return flags

    '''
    Gets photcode (aka photoCalID from dvo table)
    '''
    def getPhotoCalID(self, externID):

        photcode = -1

        sql = "SELECT PHOTCODE FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
        try:
            rs = self.executeQuery(sql)  
            rs.first()
            photcode = rs.getInt(1)
        except:
            self.logger.errorPair("Unable to get photcode from dvo with", sql)

        return photcode

    '''
    Updates a table with filterID grabbed from Filter init table
    '''
    def updateFilterID(self, table, filter):

        sql = "UPDATE "+table+" AS a, Filter AS b SET a.filterID=b.filterID WHERE b.filterType = '" + filter + "'"
        self.execute(sql)

    '''
    Inserts a new sourceID/imageID combo into dvoImages
    '''
    def insertNewDvoExternID(self, sourceID, externID):

        sql = "INSERT INTO " + self.dvoImagesTable + " ( \
               SOURCE_ID, \
               EXTERN_ID \
               ) VALUES (\
               " + str(sourceID) + ", \
               " + str(externID) + "   \
               )"
        self.execute(sql)

    '''
    Do we have an out-of-date version of this file?
    '''
    def haveOutOfDateVersionOfThisDvoTable(self, path):

        fileStat = os.stat(path)

        sql = "SELECT COUNT(*) FROM " + self.dvoDoneTable + " WHERE path = '" + path + "' AND modifiedDate != " + str(fileStat.st_mtime) 

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) > 0:
                self.logger.debugPair("Have out-to-date version of",  path)
                return True
            else:
                return False
        except:
            self.logger.exception("Unable to check whether this DVO table is out-of-date")

    '''
    Have we already imported this DVO table?
    Checks path and modified date to be sure
    '''
    def alreadyImportedThisDvoTable(self, path):

        fileStat = os.stat(path)

        sql = "SELECT COUNT(*) FROM " + self.dvoDoneTable + " WHERE path = '" + path + "' AND modifiedDate = " + str(fileStat.st_mtime) 

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) > 0:
                self.logger.debugPair("Already imported up-to-date version of",  path)
                return True
            else:
                self.logger.infoPair("Did NOT already import from",  path)
                return False
        except:
            print "***** exception in alreadyImportedThisDvoTable *****"
            self.logger.exception("Unable to check whether this DVO table has been imported")

    '''
    Updates dvoDone table with this DVO table
    '''
    def setImportedThisDvoTable(self, path, region="null"):

        fileStat = os.stat(path)

        # first delete any old version we have ingested
        sql = "DELETE FROM " + self.dvoDoneTable + " WHERE path = '" + path + "'"
        self.execute(sql)

        # now insert new version with up-to-date size and date
        sql = "INSERT INTO " + self.dvoDoneTable + " (region, path, modifiedDate, size) VALUES ('" + region + "', '" + path + "', " + str(str(fileStat.st_mtime)) + ", " + str(str(fileStat.st_size)) + ")"
        self.execute(sql)


    '''
    Gets the id for the provided file
    '''
    def getDvoFileId(self, path):

        id = -1
        sql = "SELECT id FROM " + self.dvoDoneTable + " WHERE path = '" + path + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            id = rs.getInt(1)
        except:
            self.logger.errorPair("Could not get file id from " + self.dvoDoneTable + "for", path)

        return id
                                                                                                
    '''
    Returns a list of DVO regions currently ingested
    '''
    def getIngestedDvoRegions(self):

        regions = []

        sql = "SELECT DISTINCT region FROM dvoDone WHERE region != 'null'"
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                regions.append(rs.getString(1))
            rs.close()
        except:
            self.logger.errorPair("Can't query for ingested regions", sql)

        return regions

    
    '''
    Gets total size of DVO files imported to this database
    Counts only cpm and cpt files
    '''
    def getTotalSizeOfIngestedDvoFiles(self):

        size = 0
        sql = "SELECT SUM(size) FROM " + self.dvoDoneTable + " WHERE path LIKE '%cpm' OR path LIKE '%cpt'"
        try:
            rs = self.executeQuery(sql)  
            rs.first()
            size = rs.getLong(1)
        except:
            self.logger.errorPair("Unable to get total ingested DVO size", sql)

        return size

    '''
    Checks whether DVO files we have ingested are from the DVO path provided
    Does this by checking the original path of the SkyTable.fits file
    '''
    def isCorrectDvo(self, path):

        sql = "show tables"
        try:
            rs = self.executeQuery(sql)  
            haveDone = False
            while (rs.next()):
                tableName = rs.getString(1)
                # print "table: " + tableName
                if tableName == self.dvoDoneTable:
                    haveDone = True
                    
            rs.close()
            if not haveDone:
                self.logger.info("dvoDone not yet built")
                return False

        except Exception, e:
            print str(e)
            self.logger.errorPair("problem examining table", sql)
            sys.exit(1)

        sql = "SELECT path FROM " + self.dvoDoneTable + " WHERE path LIKE '%SkyTable.fits'"
        try: rs = self.executeQuery(sql)  
        except Exception, e:
            print str(e)
            self.logger.errorPair("problem querying for dvo path", path)
            sys.exit(1)

        if not rs.next():
            self.logger.info("dvoDone exists, but is empty")
            return False

        try:
            head,tail = os.path.split(rs.getString(1))

            if head == path: 
                self.logger.debugPair("Already ingested stuff from DVO at", path)
                return True
            else: 
                self.logger.infoPair("DVO does not match. We have", head)
                return False

        except Exception, e:
            print str(e)
            self.logger.errorPair("problem searching for DVO at", path)
            sys.exit(1)

    '''
    Checks whether the astrometric solution is ok for this chip
    TODO the value of 50 for numAstroRef (provided by Gene) is hardcoded here, but this should be temporary anyway
    '''
    def astrometricSolutionOK(self, ota):

        sql = "SELECT numAstroRef FROM ImageMeta_" + ota

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) < 50:
                # print "bad astromety"
                self.logger.debug("Bad astrometric solution for",  ota)
                return False
            else:
                # print "good astromety"
                return True
        except:
            # print "query failed"
            self.logger.debug("Unable to check astrometric solution")
            return False

        return True


    '''
    Checks whether the given chipID is in the DVO Image table
    '''
    def haveThisChip(self, imageID, sourceID):

        sql = "SELECT EXTERN_ID FROM " \
            + self.dvoImagesTable + \
            " WHERE EXTERN_ID = " + str(imageID) + \
            " AND SOURCE_ID = " + str(sourceID)

        try:
            rs = self.executeQuery(sql)
            
            if not rs:
                print "missing result set for imageID query"
                raise

            # if no returned rows, return an error
            if not rs.next():
                self.logger.infoPair("DVO lacks imageID ", imageID)
                return False

            if rs.getInt(1) == int(imageID):
                # print "DVO has imageID ", imageID
                return True

            else:
                self.logger.error("DVO has wrong imageID? ")
                self.logger.error("imageID: ", imageID)
                self.logger.error("EXTERN_ID: ", rs.getInt(1))
                self.logger.infoPair("sql: ", sql)
                raise

        except:
            print "DVO imageID query failed ", imageID
            print "sql: ", sql
            raise

        # how can I reach this?
        return True

    '''
    Creates a table for for ID matching
    '''
    def createDvoTables(self):
    
        dvoImagesTable = "dvoImages"
        dvoDetectionTable = "dvoDetection"

        # drop and create Images table
        self.logger.infoPair("Creating DVO table", dvoImagesTable)
        sql = "DROP TABLE " + dvoImagesTable

        try: self.execute(sql)
        except: pass
        
        sql = "CREATE TABLE " + dvoImagesTable + " ( \
               SOURCE_ID SMALLINT, \
               IMAGE_ID INT, \
               EXTERN_ID INT, \
               FLAGS INT, \
               PHOTCODE SMALLINT, \
               PRIMARY KEY (IMAGE_ID, EXTERN_ID) \
               )"

        try: self.execute(sql)
        except: 
            self.logger.error("Unable to create DVO meta-data database table")

        # now detection table
        self.logger.infoPair("Dropping DVO table", dvoDetectionTable)
        sql = "DROP TABLE " + dvoDetectionTable
        try: self.execute(sql)
        except: 
            self.logger.errorPair("problem dropping DVO table", dvoDetectionTable)
            pass

        self.logger.infoPair("Creating DVO table", "dvoDetection")
        sql = "CREATE TABLE dvoDetection ( \
               objID BIGINT, \
               detectID BIGINT, \
               ippObjID BIGINT, \
               ippDetectID BIGINT, \
               imageID INT, \
               catID INT, \
               ra DOUBLE, \
               dec_ DOUBLE, \
               raErr FLOAT, \
               decErr FLOAT, \
               zp FLOAT, \
               telluricExt FLOAT, \
               airmass FLOAT, \
               expTime FLOAT, \
               Mpsf FLOAT, \
               dMpsf FLOAT, \
               Mkron FLOAT, \
               dMkron FLOAT, \
               Map FLOAT, \
               dMap FLOAT, \
               flags INT, \
               PRIMARY KEY (imageID, ippDetectID), \
               KEY (objID, detectID), \
               KEY (objID, ippDetectID) \
             )"

        try: self.execute(sql)
        except: 
            self.logger.error("Unable to create DVO detection database table")
            self.sys.exit(1)

        # XXX EAM : for the parallel mode, dvoDetection requires duplicate objID values!
        # self.makeColumnUnique("dvoDetection", "objID") 

    '''
    Removes the provided DVO FITS files from from dvoDone and dvoDetection tables
    Returns a count of the number of detections deleted, if any
    '''
    def purgeTheseDvoRegions(self, regions, fileTypes):

        # XXX this must have been a remnant from something else: we are deleting a region, not entries from the dvoDetectionTable
        # detectionCount = self.getRowCount(self.dvoDetectionTable)
        for region in regions:
          
           for fileType in fileTypes:
               sql = "DELETE FROM " + self.dvoDoneTable + " WHERE region = '" + region + "' AND path LIKE '%" + fileType + "'"
               try: self.execute(sql)
               except:
                    self.logger.errorPair("Unable to delete " + fileType + " file from", self.dvoDoneTable)

               tableName = self.getDbFriendlyTableName(region + "." + fileType) 
               sql = "DROP TABLE " + tableName
               try: self.execute(sql)
               except:
                    self.logger.errorPair("Unable to drop table", tableName)
                    pass

        # deletedDetectionCount = detectionCount - self.getRowCount(self.dvoDetectionTable)

        # after a big detection delete, we should OPTIMIZE the table
        # if deletedDetectionCount > 0:
        #     self.logger.infoPair("Running OPTIMIZE on ", self.dvoDetectionTable)
        #     self.optimizeTable(self.dvoDetectionTable)

        return 1
           
    '''
    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
    '''
    def resetDvoDetectionTable(self):

       self.logger.infoPair("Dropping table", self.dvoDetectionTable)
       self.dropTable(self.dvoDetectionTable)

       # we need a big enough memory table for the incoming detections
       # print "requested memory in gigs: ", self.memoryTableSize
       tableSize = int(self.memoryTableSize) * 1024 * 1024 * 1024
       self.logger.infoPair("setting max heap size: ", tableSize)

       sql = "set @@max_heap_table_size = " + str(tableSize)
       try: self.execute(sql)
       except: 
           self.logger.errorPair("Unable to set max MEMORY table size for ", self.dvoDetectionTable)
           return False

       # create detections table
       self.logger.infoPair("Creating table", self.dvoDetectionTable)
       sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
       try: self.execute(sql)
       except:
           self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
           return False

       # XXX changing the dvoDetectionFull table to MEMORY
       # self.changeEngineToInnoDB(self.dvoDetectionTable)

       # set engine to memory
       sql = "ALTER TABLE " + self.dvoDetectionTable + " ENGINE = MEMORY"
       try: self.execute(sql)
       except: 
           self.logger.errorPair("Unable to set engine to MEMORY for ", self.dvoDetectionTable)
           return False

       return True

    '''
    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
    '''
    def resetAllDvoTables(self):

       # drop all dvo tables - NB detections have to be deleted prior to dvoDone due to foreign key constraint
       self.logger.infoPair("Dropping table", self.dvoImagesTable)
       self.dropTable(self.dvoImagesTable) 
       self.logger.infoPair("Dropping table", self.dvoSkyTable)
       self.dropTable(self.dvoSkyTable) 
       self.logger.infoPair("Dropping table", self.dvoDoneTable)
       self.dropTable(self.dvoDoneTable) 

       # blow away existing dvoDetection table & re-crate
       if not self.resetDvoDetectionTable():
           self.logger.errorPair("Unable to reset scratch table", "DvoDetectionFull")
           return False

       # create dvoDone table
       self.logger.infoPair("Creating table", self.dvoDoneTable)
       sql = "CREATE TABLE " + self.dvoDoneTable + " (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, region VARCHAR(100), path VARCHAR(1000), modifiedDate BIGINT, size BIGINT)"
       try: self.execute(sql)
       except: 
           self.logger.errorPair("Unable to create table", self.dvoDoneTable)
           return False

       self.changeEngineToInnoDB(self.dvoDoneTable)

       return True

    '''
    Gets a list of DVO regions that overlap with the defined box
    '''
    def getDvoRegions(self, minRa, maxRa, minDec, maxDec):

        self.logger.debug("Querying DVO SkyTable for FITS files in this region")

        regions = []
        self.getDvoRegionsForThisBox(regions, minRa, maxRa, minDec, maxDec)

        # deal with 0/360 boundary
        if minRa < 0: self.getDvoRegionsForThisBox(regions, minRa + 360, 361 ,minDec, maxDec)
        if maxRa > 360: self.getDvoRegionsForThisBox(regions, 0, maxRa - 360 ,minDec, maxDec)

        return regions

    '''
    Gets a list of DVO regions that overlap with the defined box
    '''
    def getDvoRegionsForThisBox(self, regions, minRa, maxRa, minDec, maxDec):

        sql = "SELECT \
               INDEX_, \
               R_MIN+ (R_MAX - R_MIN)/2.0, \
               D_MIN + (D_MAX - D_MIN)/2.0, \
               NAME \
               FROM " + self.dvoSkyTable + " \
               WHERE NOT ((" + str(maxRa) + " <= R_MIN) OR \
                       (" + str(minRa) + " >= R_MAX) OR \
                       (" + str(maxDec) + " <= D_MIN) OR \
                       (" + str(minDec) + " >= D_MAX)) "

        try:
            rs = self.executeQuery(sql)
            while (rs.next()): regions.append([
                    rs.getInt(1), 
                    rs.getFloat(2), 
                    rs.getFloat(3), 
                    rs.getString(4)])

            rs.close()
        except:
            self.logger.exception("Can't query for DVO regions")


    '''
    Gets the id for the provided file
    '''
    def getRegionNameFromThisDvoIndex(self, index):

        region = ""
        sql = "SELECT NAME FROM " + self.dvoSkyTable + " WHERE INDEX_ = " + str(index)
        try:
            rs = self.executeQuery(sql)
            rs.first()
            region = rs.getString(1)
            rs.close()
        except:
            self.logger.errorPair("Could not get region using index", sql )

        return region

    '''
    Gets a count of average filters (secfilt) in the DVO Photcodes table
    '''
    def getCountOfFiltersFromPhotcodesTable(self):

        # 2 is hardwired -- convert to an enum (should probably read Ohana/src/libdvo/include/dvo.h defines
        sql = "SELECT count(TYPE_AS_INT) FROM " + self.dvoPhotcodesTable + " WHERE TYPE_AS_INT = 2"

        filterCount = 0
        try:
            rs = self.executeQuery(sql)
            row = 1
            while (rs.next()):
                filterCount = rs.getString(1)
            rs.close()
        except:
            self.logger.errorPair("Could not get filter count", sql )

        return filterCount

    '''
    Gets a list of filters ordered as thy are in the DVO Photcodes table, as this reflects the order they apper in the cps file
    '''
    def getOrderedListOfFiltersFromPhotcodesTable(self, interestedFilters):

        sql = "SELECT NAME FROM " + self.dvoPhotcodesTable + " WHERE "

        count = 0
        for filter in interestedFilters:
           if count != 0: sql = sql + " OR "
           sql = sql + " name = '" + filter + "'"
           count += 1

        sql = sql + " ORDER BY CODE"

        orderedFilters = []
        try:
            rs = self.executeQuery(sql)
            row = 1
            while (rs.next()):
                orderedFilters.append([row,rs.getString(1)])
                row += 1
            rs.close()
        except:
            self.logger.errorPair("Could not get ordered filter list", sql )

        return orderedFilters


    def getRaDecRangesFromdvoSkyTable(self, name):
     
        sql = "SELECT R_MIN, R_MAX, D_MIN, D_MAX FROM dvoSkyTable WHERE NAME = '" + name + "' limit 1"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            raMin = rs.getFloat(1)
            raMax = rs.getFloat(2)
            decMin = rs.getFloat(3)
            decMax = rs.getFloat(4)
           
        except:
            self.logger.errorPair("no ra/decs found for", name)
            raMin = -999
            raMax = -999
            decMin = -999
            decMax = -999

        return raMin, raMax, decMin, decMax
 
    def getFromdvoSkyTable(self, column, name):
     
        sql = "SELECT "+column + " FROM dvoSkyTable WHERE NAME = '" + name + "' limit 1"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            res = rs.getDouble(1)
                       
        except:
            self.logger.errorPair("no ra/decs found for", name)
            res = -999
            

        return res
