#!/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, dbVersion, dbName=None):

        # create gpc1 database objects
        if (config.test): 
            dbType = "localdatabase_test"
        else:
            dbType = "localdatabase"

        if not dbName:
            dbName = config.getDbName(dbType)
            dbName += dbVersion

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

        self.dvoDoneTable = "dvoDone"
        self.dvoSkyTable = "dvoSkyTable"
        self.dvoPhotcodesTable = "dvoPhotcodes"

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

        self.logger.debug("ScratchDb destructor")


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

        if useFull:
            self.dvoImagesTable = "dvoImagesFull"
            self.dvoDetectionTable = "dvoDetectionFull"
        else:
            self.dvoImagesTable = "dvoImages"
            self.dvoDetectionTable = "dvoDetection"

        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 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:
                return False
        except:
            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 = "SELECT path FROM " + self.dvoDoneTable + " WHERE path LIKE '%SkyTable.fits'"
        try:
            rs = self.executeQuery(sql)  
            rs.first()

            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.errorPair("DVO does not match. We have", head)
                return False

        except Exception, e:
            print str(e)
            self.logger.errorPair("No DVO files ingested for this path", path)
            return False

    '''
    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


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

        # drop and create Images table
        self.logger.debugPair("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.debugPair("Creating DVO table", dvoDetectionTable)
        sql = "DROP TABLE " + dvoDetectionTable
        try: self.execute(sql)
        except: pass

        self.logger.debugPair("Creating DVO table", "dvoDetection")
        sql = "CREATE TABLE dvoDetection ( \
               imageID INT, \
               ippDetectID BIGINT, \
               detectID BIGINT, \
               ippObjID BIGINT, \
               objID BIGINT, \
               flags INT, \
               zp REAL, \
               zpErr REAL, \
               airMass REAL, \
               expTime REAL, \
               ra FLOAT, \
               dec_ FLOAT, \
               raErr REAL, \
               decErr REAL, \
               PRIMARY KEY (imageID, ippDetectID), \
               KEY (objID, detectID) \
             )"

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

        # 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):

        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 deletedDetectionCount
           
    '''
    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.dvoDetectionTable)
       self.dropTable(self.dvoDetectionTable)
       self.logger.infoPair("Dropping table", self.dvoDoneTable)
       self.dropTable(self.dvoDoneTable) 

       # 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)

       # 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
       self.changeEngineToInnoDB(self.dvoDetectionTable)

       # add fileID column
       sql = "ALTER TABLE " + self.dvoDetectionTable + " ADD fileID INT NOT NULL"
       try: self.execute(sql)
       except: 
           self.logger.errorPair("Unable to add fileID column to ", self.dvoDetectionTable)
           return False

       # add an index to the fileID column
       self.createIndex(self.dvoDetectionTable, "fileID")

       # XXX : this should be based on choice of useStilts (which is set in dvo.py and must be moved)
       if (False):
           # now add a delete cascading foreign key constraint on fileID
           sql = "ALTER TABLE dvoDetectionFull \
              ADD CONSTRAINT fk_fileID \
              FOREIGN KEY (fileID) \
              REFERENCES dvoDone(id) \
              ON DELETE CASCADE"
           try: self.execute(sql)
           except: 
               self.logger.errorPair("Unable to create foreign key on", self.dvoDoneTable)
               return False

       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
