#!/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, doc, useFull=0):
        super(ScratchDb, self).__init__(logger, doc, "localdatabase")

        if useFull:
            self.dvoMetaTable = "dvoMetaFull"
            self.dvoDetectionTable = "dvoDetectionFull"
            self.dvoDoneTable = "dvoDone"
        else:
            self.dvoMetaTable = "dvoMeta"
            self.dvoDetectionTable = "dvoDetection"

        self.logger.debugPair("Using DVO meta table", self.dvoMetaTable)
        self.logger.debugPair("Using DVO detection table", self.dvoDetectionTable)

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

        self.logger.debug("ScratchDb destructor")

    '''
    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; \
               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; \
               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, sourceID, externID): 
               
        imageID = -1 
               
        sql = "SELECT imageID FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, 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, sourceID, externID):

        flags = 0

        sql = "SELECT flags FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, 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, sourceID, externID):

        photcode = -1

        sql = "SELECT photcode FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, 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 dvoMeta
    '''
    def insertNewDvoExternID(self, sourceID, externID):

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

    '''
    Updates dvoDone table with this DVO table
    '''
    def setImportedThisDvoTable(self, name):

        sql = "INSERT INTO dvoDone (name) VALUES ('" + name + "')"
        self.execute(sql)
        
    '''
    Checks whether the astrometric solution is ok for this chip
    TODO the value of 50 for numAstroRef 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:
                self.logger.debug("Bad astrometric solution for",  ota)
                return False
            else:
                return True
        except:
            self.logger.debug("Unable to check astrometric solution")

        return True

    '''
    Have we already imported this DVO table?
    '''
    def alreadyImportedThisDvoTable(self, name):

        sql = "SELECT COUNT(*) FROM dvoDone WHERE name = '" + name + "'"

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) > 0:
                self.logger.errorPair("Already imported DVO tables for",  name)
                return True
            else:
                return False
        except:
            self.logger.exception("Unable to check whether this DVO table has been imported")


    '''
    Creates a table for for ID matching
    '''
    def createDvoTables(self):

        self.logger.debugPair("Creating DVO table", "dvoMeta")

        sql = "DROP TABLE dvoMeta"
        try: self.execute(sql)
        except: pass
        
        sql = "DROP TABLE dvoDetection"
        try: self.execute(sql)
        except: pass

        sql = "CREATE TABLE dvoMeta ( \
               sourceID INT, \
               imageID INT, \
               externID INT, \
               flags INT, \
               photcode INT, \
               PRIMARY KEY (sourceID, imageID, externID) \
               )"

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

        self.logger.debugPair("Creating DVO table", "dvoDetection")
        sql = "CREATE TABLE dvoDetection ( \
               sourceID INT, \
               imageID INT, \
               ippDetectID BIGINT, \
               detectID BIGINT, \
               ippObjID BIGINT, \
               objID BIGINT, \
               flags INT, \
               PRIMARY KEY (sourceID, imageID, ippDetectID) \
               )"

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


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

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

       self.logger.infoPair("Creating table", self.dvoMetaTable)
       sql = "CREATE TABLE " + self.dvoMetaTable + " LIKE dvoMeta"
       try: self.execute(sql)
       except: self.logger.errorPair("Unable to create table", self.dvoMetaTable)

       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)

       self.logger.infoPair("Creating table", self.dvoDoneTable )
       sql = "CREATE TABLE " + self.dvoDoneTable + " (name VARCHAR(100))"
       try: self.execute(sql)
       except: self.logger.errorPair("Unable to create table", self.dvoDoneTable)



