#!/usr/bin/env jython

import re
import sys
import glob
import os
import logging

from mysql import MySql
from fits import Fits
from java.sql import *


'''
Class for GPC1 database connectivity
'''
class Gpc1Db(MySql):

    '''
    Constructor
    '''
    def __init__(self, logger, config):
        # define database type
        if (config.gpc1test): 
            dbType = "gpc1database_test"
        else:
            dbType = "gpc1database"

        super(Gpc1Db, self).__init__(logger, config, dbType)

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

        self.logger.debug("Gpc1Db destructor")


    '''
    Gets a list of ids, ra and dec in this DVO database for this stage, could be cam or staticsky, for a box with defined center and side length
    '''
    def getItemsInThisDVODbForThisStageInThisBox(self, dvoDb, batchType, ra=180., dec=0., side=181.):

        halfSide = side/2.0
        return self.getItemsInThisDVODbForThisStage(dvoDb, batchType, ra-halfSide, ra+halfSide, dec-halfSide, dec+halfSide)

    '''
    Gets a list of ids, ra and dec in this DVO database for this stage, could be cam or staticsky, and a box can be defined with equatorial coordinates
    NB this uses Bill's potentially temporary 'skycells' database when finding RA/Dec for stacks.
    '''
    def getItemsInThisDVODbForThisStage(self, dvoDb, batchType, minRA=-999, maxRA=999, minDec=-999, maxDec=999, tstart="0000-00-00 00:00:00", tend = "0000-00-00 00:00:00"):

        self.logger.debugPair("GPC1 RA range", "%.2f -> %.2f" % (minRA, maxRA))
        self.logger.debugPair("GPC1 Dec range", "%.2f -> %.2f" % (minDec, maxDec))
        
        rows = []

        if batchType == "P2": # TODO define these some place 

            stage = "cam"
            sql = "SELECT DISTINCT stage_id, DEGREES(ra), DEGREES(decl) \
                   FROM addRun \
                   JOIN addProcessedExp USING (add_id) \
                   JOIN minidvodbRun USING(minidvodb_name) \
                   JOIN minidvodbProcessed USING(minidvodb_id) \
                   JOIN camRun ON (stage_id = cam_id) \
                   JOIN camProcessedExp USING(cam_id) \
                   JOIN chipRun USING (chip_id) \
                   JOIN rawExp USING (exp_id) \
                   JOIN mergedvodbRun using (minidvodb_id) \
                   JOIN mergedvodbProcessed using (merge_id) \
                   WHERE mergedvodbRun.mergedvodb = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND mergedvodbRun.state = 'full' \
                   AND minidvodbProcessed.fault = 0 \
                   AND mergedvodbProcessed.fault = 0 \
                   AND camProcessedExp.fault = 0 \
                   AND addProcessedExp.fault = 0 \
                   AND addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full' \
                   AND decl >= RADIANS(" + str(minDec) + ") AND decl < RADIANS(" + str(maxDec) + ") \
                   AND ra >= RADIANS(" + str(minRA) + ") AND ra < RADIANS(" + str(maxRA) + ") \
                   AND dateobs >= '" + tstart + "'\
                   AND dateobs <= '" + tend  + "'"

        elif batchType == "ST":       

            stage = "skycal"
            sql = "SELECT DISTINCT sky_id, radeg, decdeg FROM skycalRun \
                   JOIN addRun ON(skycalRun.skycal_id = addRun.stage_id) \
                   JOIN minidvodbRun USING(minidvodb_name) \
                   JOIN minidvodbProcessed USING(minidvodb_id) \
                   JOIN mergedvodbRun USING(minidvodb_id) \
                   JOIN mergedvodbProcessed USING (merge_id) \
                   JOIN stackRun USING(stack_id) \
                   JOIN stackSumSkyfile using (stack_id) \
                   JOIN skycell USING(skycell_id) \
                   WHERE mergedvodbRun.mergedvodb = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND minidvodbProcessed.fault = 0 \
                   AND mergedvodbRun.state = 'full' \
                   AND mergedvodbProcessed.fault = 0 \
                   AND addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full' \
                   AND decdeg >= " + str(minDec) + " AND decdeg < " + str(maxDec) + " \
                   AND radeg >= " + str(minRA) + " AND radeg < " + str(maxRA) + " \
                   AND mjd_obs >= (to_days('" + tstart + "')-678941) \
                   AND mjd_obs <= (to_days('" + tend + "') - 678941) "
           # self.logger.infoPair("sql",sql)      
        elif batchType == "DF":            # this is specifically only for WARPSTACK types of diffs -
            # it only makes sense for the date to come from the warp1
            # may need to be more clever if we do other types of diffs

            stage = "diff"
            sql = "SELECT stage_id, avg(radeg),avg(decdeg) \
                   FROM addRun \
                   JOIN addProcessedExp using (add_id) \
                   JOIN minidvodbRun using (minidvodb_name) \
                   JOIN minidvodbProcessed using (minidvodb_id)  \
                   JOIN mergedvodbRun using (minidvodb_id) \
                   JOIN mergedvodbProcessed using (merge_id) \
                   JOIN diffInputSkyfile \
                   ON (diff_id = stage_id AND diff_skyfile_id = stage_extra1 AND stage = 'diff') \
                   JOIN skycell using (skycell_id, tess_id) \
                   JOIN warpRun on (warp1 = warp_id \
                   AND diffInputSkyfile.skycell_id = skycell_id) \
                   JOIN fakeRun using (fake_id) \
                   JOIN camRun using (cam_id) \
                   JOIN chipRun using (chip_id)  \
                   JOIN rawExp using (exp_id) \
                   WHERE  mergedvodbRun.mergedvodb = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND minidvodbProcessed.fault = 0 \
                   AND mergedvodbRun.state = 'full' \
                   AND mergedvodbProcessed.fault = 0 \
                   AND addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full' \
                   AND decdeg >= " + str(minDec) + " AND decdeg < " + str(maxDec) + " \
                   AND radeg >= " + str(minRA) + " AND radeg < " + str(maxRA) + " \
                   AND dateobs >= '" + tstart + "'\
                   AND dateobs <= '" + tend  + "'\
                   GROUP BY stage_id"                   

        elif batchType == "FW":
            
            stage = "fullforce"
            sql = "SELECT stage_id, avg(radeg), avg(decdeg) FROM fullForceInput \
                   JOIN addRun ON(fullForceInput.ff_id = addRun.stage_id AND fullForceInput.warp_id = addRun.stage_extra1) \
                   JOIN minidvodbRun USING(minidvodb_name) \
                   JOIN minidvodbProcessed USING(minidvodb_id) \
                   JOIN mergedvodbRun USING(minidvodb_id) \
                   JOIN mergedvodbProcessed USING (merge_id) \
                   JOIN fullForceRun USING(ff_id) \
                   JOIN skycalRun using (skycal_id) \
                   JOIN stackRun USING(stack_id) \
                   JOIN stackSumSkyfile using (stack_id) \
                   JOIN skycell USING(skycell_id) \
                   WHERE mergedvodbRun.mergedvodb = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND minidvodbProcessed.fault = 0 \
                   AND mergedvodbRun.state = 'full' \
                   AND mergedvodbProcessed.fault = 0 \
                   AND addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full' \
                   AND decdeg >= " + str(minDec) + " AND decdeg < " + str(maxDec) + " \
                   AND radeg >= " + str(minRA) + " AND radeg < " + str(maxRA) + " \
                   AND mjd_obs >= (to_days('" + tstart + "')-678941) \
                   AND mjd_obs <= (to_days('" + tend + "') - 678941) \
                   GROUP BY stage_id"

        self.logger.debug("SQL: " + sql)
        rs = self.executeQuery(sql)

        while (rs.next()): 
            rows.append([rs.getInt(1), rs.getFloat(2), rs.getFloat(3)])

        rs.close()

        self.logger.debug("Found %d items in DVO database '%s' for stage='%s'" % (len(rows), dvoDb, stage))

        return rows

    '''
    Gets a list of PSPS image IDs for this stack ID
    '''
    def getImageIDsForThisStackID(self, stackID):

        self.logger.debug("Querying GPC1 for image IDs for stack ID: " + str(stackID))

        # JOIN stackInputSkyfile shoule use stack_id as well as warp_id, right? (maybe not -- the join to warp is first)
        sql = "SELECT DISTINCT CONCAT(exp_id, SUBSTR(class_id, 3, 4)) FROM ( \
               SELECT DISTINCT exp_id,class_id \
               FROM warpSkyCellMap \
               JOIN warpRun USING(warp_id) \
               JOIN stackInputSkyfile USING(warp_id) \
               JOIN stackRun USING(stack_id,skycell_id) \
               JOIN fakeRun USING(fake_id) \
               JOIN camRun USING(cam_id)  \
               JOIN chipRun USING(chip_id) \
               WHERE stackRun.stack_id = " + str(stackID) + ") AS a"

        try:
            rs = self.executeQuery(sql)
        except:
            self.logger.exception("Can't query for imageIDs")

        imageIDs = []
        while (rs.next()):
            imageIDs.append(rs.getString(1))
        rs.close()

        return imageIDs

    

    '''                                                                                                                                              
    Gets a list of PSPS image IDs for this stack ID                                                                                                  
    '''
    def getImageIDsForThisForcedWarpID(self, forcedWarpID):

        self.logger.debug("Querying GPC1 for image IDs for forcedWarp ID: " + str(forcedWarpID))

        # JOIN stackInputSkyfile shoule use stack_id as well as warp_id, right? (maybe not -- the join to warp is first)                             
        sql = "SELECT DISTINCT CONCAT(exp_id, SUBSTR(class_id, 3, 4)) FROM ( \
               SELECT DISTINCT exp_id,class_id \
               FROM addRun join \
               addProcessedExp using(add_id) join \
               fullForceInput on (fullForceInput.ff_id = addRun.stage_id and \
               fullForceInput.warp_id = addRun.stage_extra1) \
               JOIN fullForceResult using (ff_id, warp_id) \
               JOIN warpRun USING(warp_id) \
               JOIN warpSkyCellMap using (warp_id) \
               JOIN fakeRun USING(fake_id) \
               JOIN camRun USING(cam_id)  \
               JOIN chipRun USING(chip_id) \
               WHERE addProcessedExp.fault = 0 and add_id = " + str(forcedWarpID) + ") AS a"

        try:
            rs = self.executeQuery(sql)
        except:
            self.logger.exception("Can't query for imageIDs using :" + sql )

        imageIDs = []
        while (rs.next()):
            imageIDs.append(rs.getString(1))
        rs.close()

        return imageIDs



    '''
    Gets a list of add ids for a given stage id (distinct fullforce stage id
    is what is queued in the batch
    '''

    def getListOfForcedWarpIdsForForcedWarpBatch(self, dvoDb, stageID):

        self.logger.debug("Querying GPC1 for add IDs for ff_id: " + str(stageID))

        sql = "SELECT add_id FROM fullForceInput \
              JOIN addRun ON(fullForceInput.ff_id = addRun.stage_id \
              AND fullForceInput.warp_id = addRun.stage_extra1) \
              JOIN addProcessedExp using (add_id) \
              JOIN minidvodbRun USING(minidvodb_name) \
              JOIN minidvodbProcessed USING(minidvodb_id)\
              JOIN mergedvodbRun USING(minidvodb_id) \
              JOIN mergedvodbProcessed USING (merge_id) \
              JOIN fullForceRun USING(ff_id) \
              JOIN skycalRun using (skycal_id) \
              JOIN stackRun USING(stack_id) \
              JOIN stackSumSkyfile using (stack_id) \
              JOIN skycell USING(skycell_id) \
              WHERE mergedvodbRun.mergedvodb = '"+ dvoDb + "'\
              AND minidvodbRun.state = 'merged' \
              AND minidvodbProcessed.fault = 0 \
              AND mergedvodbRun.state = 'full' \
              AND mergedvodbProcessed.fault = 0 \
              AND addRun.stage = 'fullforce' \
              AND addRun.state = 'full'\
              AND addProcessedExp.fault = 0 \
              AND stage_id = " + str(stageID)

        rs = self.executeQuery(sql)

        addIDs = []
        while (rs.next()):
            addIDs.append(rs.getString(1))
        rs.close()

        

        return addIDs



    '''
    Gets a list of add ids for a given stage id (distinct fullforce stage id
    is what is queued in the batch
    '''

    def getListOfDiffSkyFileIdsForDiffBatch(self, dvoDb, stageID):

        self.logger.debug("Querying GPC1 for add IDs for diff_id: " + str(stageID))
        stage = "diff"
        sql = "SELECT diff_skyfile_id \
                   FROM addRun \
                   JOIN addProcessedExp using (add_id) \
                   JOIN minidvodbRun using (minidvodb_name) \
                   JOIN minidvodbProcessed using (minidvodb_id)  \
                   JOIN mergedvodbRun using (minidvodb_id) \
                   JOIN mergedvodbProcessed using (merge_id) \
                   JOIN diffInputSkyfile \
                   ON (diff_id = stage_id AND diff_skyfile_id = stage_extra1 AND stage = 'diff') \
                   JOIN skycell using (skycell_id, tess_id) \
                   JOIN warpRun on (warp1 = warp_id \
                   AND diffInputSkyfile.skycell_id = skycell_id) \
                   JOIN fakeRun using (fake_id) \
                   JOIN camRun using (cam_id) \
                   JOIN chipRun using (chip_id)  \
                   JOIN rawExp using (exp_id) \
                   WHERE  mergedvodbRun.mergedvodb = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND minidvodbProcessed.fault = 0 \
                   AND mergedvodbRun.state = 'full' \
                   AND mergedvodbProcessed.fault = 0 \
                   AND addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full' \
                   AND stage_id = " + str(stageID)                               



        try:
            rs = self.executeQuery(sql)
        except:
            self.logger.exception("Can't query for addIDs using :" + sql )


        addIDs = []
        while (rs.next()):
            addIDs.append(rs.getString(1))
        rs.close()

        return addIDs

    '''
    Gets some stack-stage meta data for this sky_id # TODO this SQL could surely be improved
    '''
    def getStackStageMeta(self, stackID):

        self.logger.debug("Querying GPC1 for stack meta data")

        meta = []
        sql = "SELECT \
               filter,\
               skycell_id, \
               software_ver \
               FROM stackRun \
               WHERE stack_id = %d" % stackID
               
        try:
            rs = self.executeQuery(sql)
            rs.first()
        except:
            self.logger.errorPair("Can't query for", "stack meta data")
           
        try:
            meta.append(rs.getString(1))
            meta.append(rs.getString(2))
            meta.append(rs.getString(3))
        except:
            self.logger.errorPair("getStackStageMeta()", "empty meta data")
     
        return meta

    '''
    Gets some forcedwarp stage meta data for this sky_id # TODO this SQL could surely be improved
    '''
    def getForcedWarpStageMeta(self, forcedWarpID, warpSkyfileID):
        self.logger.debug("Querying GPC1 for forced warp meta data")

        meta = {}

        sql = "SELECT \
               exp_id, \
               exp_time, \
               filter, \
               fullForceResult.software_ver, \
               skycell_id, \
               warpRun.tess_id, \
               skycalRun.skycal_id, \
               skycalRun.stack_id \
               FROM fullForceInput join addRun on \
               (fullForceInput.ff_id = addRun.stage_id and fullForceInput.warp_id = addRun.stage_extra1) \
               JOIN fullForceResult using (ff_id,warp_id) \
               JOIN warpRun using (warp_id) \
               JOIN warpImfile using (warp_id) \
               JOIN fakeRun using (fake_id) \
               JOIN camRun using (cam_id) \
               JOIN chipRun using (chip_id) \
               JOIN rawExp using (exp_id) \
               JOIN fullForceRun using(ff_id) \
               JOIN skycalRun USING(skycal_id) \
               WHERE add_id = " + str(forcedWarpID) + " \
               AND warp_skyfile_id = " +  str(warpSkyfileID)

        rs = self.executeQuery(sql)
        if not rs.first():
            self.logger.errorPair("Can't query for", "forced warp meta data")
            self.logger.errorPair("this failed", sql)

            os._exit(3)

        try:
            meta['exp_id']       = rs.getString(1)
            meta['exp_time']     = rs.getString(2)
            meta['filter']       = rs.getString(3)
            meta['software_ver'] = rs.getString(4)
            meta['skycell_id']   = rs.getString(5)
            meta['tess_id']      = rs.getString(6)
            meta['skycal_id']    = rs.getString(7)
            meta['stack_id']     = rs.getString(8)
        except:
            self.logger.errorPair("getForcedWarpStageMeta()", "missing metadata entry")

        return meta

    '''
    Gets some stack-stage meta data for this sky_id # TODO this SQL could surely be improved
    '''
    def getDiffStageMeta(self, diffSkyfileID):

        self.logger.debug("Querying GPC1 for diff meta data")

        meta = {}

        sql = "SELECT \
               filter,\
               diff_mode, \
               warp1, \
               stack2, \
               skycell_id, \
               diffRun.software_ver as software_ver, \
               exp_time, \
               (to_days(dateobs)-678941 + time_to_sec(dateobs)/86400.) as obsTime, \
               diffInputSkyfile.tess_id, \
               exp_id \
               FROM diffRun join diffInputSkyfile using (diff_id) \
               JOIN warpRun on (warp1=warp_id) \
               JOIN fakeRun using (fake_id) \
               JOIN camRun using (cam_id) \
               JOIN chipRun using (chip_id) \
               JOIN rawExp using (exp_id) \
               WHERE diff_skyfile_id = %d" % diffSkyfileID

        rs = self.executeQuery(sql)
        if not rs.first():
            self.logger.infoPair ("this sql is broken:",sql)
            self.logger.errorPair("Can't query for", "stack meta data")
            os._exit(3)
           
        try:
            meta['filter']       = rs.getString(1)
            meta['diff_mode']    = rs.getString(2)
            meta['warp1']        = rs.getString(3)
            meta['stack2']       = rs.getString(4)
            meta['skycell_id']   = rs.getString(5)
            meta['software_ver'] = rs.getString(6)
            meta['exp_time']     = rs.getString(7)
            meta['obsTime']      = rs.getString(8)
            meta['tess_id']      = rs.getString(9)
            meta['exp_id']       = rs.getString(10)

        except:
            self.logger.errorPair("getDiffStageMeta()", "empty meta data")
     
        return meta

    '''
    get forcedwarp stage cmf
    '''
    def getForcedWarpStageCmf(self, dvoDb, forcedWarpID):
        sql = "SELECT DISTINCT fullForceResult.path_base \
              from fullForceInput join addRun on \
              (fullForceInput.ff_id = addRun.stage_id AND \
              fullForceInput.warp_id = addRun.stage_extra1) \
              join fullForceResult using (ff_id, warp_id) \
              where add_id = %d" % forcedWarpID

        try:
            rs = self.executeQuery(sql)
            rs.first()
        except:
            self.logger.infoPair("failed sql", sql)
            self.logger.errorPair("can't query for fullforce cmf", "add_id = %d" %forcedWarpID)
            return None
        
        try:
            pathBase = rs.getString(1)
        except:
            self.logger.errorPair("no full force cmfs found for add_id = %d" %forcedWarpID)
            return None
        
        files = []
        if pathBase.startswith("neb"):
            f=os.popen("neb-ls -p " + pathBase + ".cmf")
            for i in f.readlines():
                files.append(i.rstrip())

        else:
            files = glob.glob(pathBase + ".cmf")

        if len(files) < 1:
            self.logger.errorPair("No files found for forced warp path", pathBase)
            return None
        
        fits = Fits(self.logger, self.config, files[0])
        self.logger.infoPair("cmf file",files[0])
        return fits

    '''
    get diff stage cmf name
    '''
    def getDiffStagePathBase(self, diffSkyfileID):
        sql = "SELECT DISTINCT diffSkyfile.path_base \
               FROM diffInputSkyfile \
               JOIN diffSkyfile using (diff_id, skycell_id) \
               WHERE diff_skyfile_id = " + str(diffSkyfileID)

        rs = self.executeQuery(sql)

        if not rs.first():
            self.logger.errorPair("missing cmf entry diff_skyfile_id:", str(diff_skyfile_id))
            # self.logger.errorPair("no diff cmfs found for diff_skyfile_id = %d" %diff_skyfile_id)
            return None

        pathBase = rs.getString(1)
        return pathBase

    '''
    get diff stage cmf name
    XXX EAM 20151119 : this function does too much work: you have a diffSkyfileID, that matches to a unique path_base
    '''
    def getDiffStagePathBaseOldVersion(self, dvoDb, diffSkyfileID):
        print "got here, diffstagecmf"
        sql = "SELECT DISTINCT diffSkyfile.path_base \
               FROM mergedvodbRun join minidvodbRun using (minidvodb_id) \
               JOIN addRun using (minidvodb_name) \
               JOIN diffInputSkyfile on (diff_skyfile_id = stage_extra1 AND diff_id = stage_id AND stage = 'diff') \
               JOIN diffSkyfile using (diff_id, skycell_id) \
               WHERE diff_skyfile_id = " + str(diffSkyfileID) + " \
               AND minidvodbRun.state = 'merged' \
               AND mergedvodbRun.state = 'full' \
               AND mergedvodbRun.mergedvodb = '" + dvoDb + "' \
               AND stage = 'diff'"

        rs = self.executeQuery(sql)

        if not rs.first():
            self.logger.errorPair("missing cmf entry diff_skyfile_id:", str(diff_skyfile_id))
            # self.logger.errorPair("no diff cmfs found for diff_skyfile_id = %d" %diff_skyfile_id)
            return None

        pathBase = rs.getString(1)
        return pathBase

    '''
    get diff stage cmf name
    '''
    def getDiffStageCmfForPathBase(self, pathBase):

        files = []
        if pathBase.startswith("neb"):
            self.logger.debugPair("searching for nebulous files", pathBase)
            f = os.popen("neb-ls -p " + pathBase + ".cmf")
            for i in f.readlines():
                self.logger.debugPair("real file", i.rstrip())
                files.append(i.rstrip())
        else:
            files = glob.glob(pathBase + ".cmf")

        if len(files) < 1: 
            self.logger.errorPair ("no cmf file found matching pathBase", pathBase + ".cmf")
            return None

        fits = Fits(self.logger, self.config, files[0])
        return fits

    '''
    get diff stage cmf name
    XXX EAM 20151119 This function is broken AND it does too much work
    '''
    def getDiffStageCmf(self, dvoDb, diffSkyfileID):
        print "got here, diffstagecmf"
        sql = "SELECT DISTINCT diffSkyfile.path_base \
               FROM mergedvodbRun join minidvodbRun using (minidvodb_id) \
               JOIN addRun using (minidvodb_name) \
               JOIN diffInputSkyfile on (diff_skyfile_id = stage_extra1 \
               AND diff_id = stage_id AND stage = 'diff') \
               JOIN diffSkyfile using (diff_id, skycell_id, diff_skyfile_id) \
               WHERE diff_skyfile_id = " + str(diffSkyfileID) + " \
               AND minidvodbRun.state = 'merged' \
               AND mergedvodbRun.state = 'full' \
               AND mergedvodbRun.mergedvodb = '" + dvoDb + "' \
               AND stage = 'diff'"

        rs = self.executeQuery(sql)

        if not rs.first():
            self.logger.errorPair("missing cmf entry diff_skyfile_id:", str(diff_skyfile_id))
            # self.logger.errorPair("no diff cmfs found for diff_skyfile_id = %d" %diff_skyfile_id)
            return None

        pathBase = rs.getString(1)

        files = []
        if pathBase.startswith("neb"):
            self.logger.debugPair("searching for nebulous files", pathBase)
            f=os.popen("neb-ls -p " + pathBase + ".cmf")
            for i in f.readlines():
                self.logger.debugPair("real file", i.rstrip())
                files.append(i.rstrip())
        else:
            files = glob.glob(pathBase + ".cmf")

        if len(files) < 1: 
            self.logger.errorPair ("no cmf file found matching pathBase", pathBase + ".cmf")
            return None

        fits = Fits(self.logger, self.config, files[0])
        return fits

    '''
    Gets a list of PSPS image IDs for this diff ID
    '''
    def getImageIDsForThisDiffID(self, diffID):

        self.logger.debug("Querying GPC1 for image IDs for diff ID: " + str(diffID))

        # JOIN stackInputSkyfile shoule use stack_id as well as warp_id, right? (maybe not -- the join to warp is first)
        sql = "SELECT DISTINCT CONCAT(exp_id, SUBSTR(class_id, 3, 4)) FROM ( \
               SELECT DISTINCT exp_id,class_id \
               FROM warpSkyCellMap \
               JOIN warpRun USING(warp_id) \
               JOIN diffInputSkyfile on warp1 = warp_id  \
               JOIN fakeRun using (fake_id) \
               JOIN camRun using (cam_id) \
               JOIN chipRun using (chip_id) \
               WHERE diffInputSkyfile.diff_skyfile_id = " + str(diffID) + ") AS a"

        try:
            rs = self.executeQuery(sql)
        except:
            self.logger.infoPair("failed sql:",sql)
            self.logger.exception("Can't query for imageIDs")
            

        imageIDs = []
        while (rs.next()):
            imageIDs.append(rs.getString(1))
        rs.close()

        return imageIDs


    '''
    Gets the exp_id from teh exp_name
    ''' 
    def getExpIdFromExpName(self, expName):
       sql = "SELECT exp_id from rawExp where exp_name = '"+expName+"'"
       try:
           rs = self.executeQuery(sql)
       except:
           self.logger.infoPair("failed sql:",sql)
           self.logger.exception("Can't query for imageIDs")
            

       expIDs = []
       while (rs.next()):
           expIDs.append(rs.getString(1))
       rs.close()
       expID = expIDs[0]
       return expID

    



    '''
    Gets a list of PSPS image IDs for this diff ID
    '''
    def getWarpSkyFileIDforThisAddIDforFW(self, addID):

        self.logger.debug("Querying GPC1 for stage_extra1 for add ID: " + str(addID))


        sql = "select warp_skyfile_id from addRun join fullForceInput \
              on stage_id = ff_id and stage = 'fullforce' and stage_extra1 = warp_id \
              join fullForceRun using (ff_id) \
              join fullForceResult using (ff_id, warp_id) \
              JOIN skycalRun using (skycal_id) \
              join stackRun using (stack_id) \
              join warpImfile using (warp_id, skycell_id) \
              where add_id = "+str(addID)
        try:
            rs = self.executeQuery(sql)
        except:
            self.logger.infoPair("failed sql:",sql)
            self.logger.exception("Can't query for imageIDs")
            

        #imageIDs = []
        #while (rs.next()):
        #    imageIDs.append(rs.getString(1))
        #rs.close()

        rs.first()
        imageID=rs.getString(1)
        rs.close()
        return imageID

    '''
    Gets a list of PSPS image IDs for this diff ID
    '''
    def getWarpSkyFileIDforThisAddIDforST(self, addID):

        self.logger.debug("Querying GPC1 for stage_extra1 for add ID: " + str(addID))


        sql = "select warp_skyfile_id from addRun join skycalRun \
              on stage_id = skycal_id and stage = 'skycal' \
              join stackRun using (stack_id) \
              join warpImfile using (warp_id, skycell_id) \
              where add_id = "+str(addID)
        try:
            rs = self.executeQuery(sql)
        except:
            self.logger.infoPair("failed sql:",sql)
            self.logger.exception("Can't query for imageIDs")
            

        #imageIDs = []
        #while (rs.next()):
        #    imageIDs.append(rs.getString(1))
        #rs.close()

        rs.first()
        imageID=rs.getString(1)
        rs.close()
        return imageID

    


    
    '''
    Gets some camera-stage meta data for this cam_id
    '''
    def getCameraStageMeta(self, camID):

        self.logger.debug("Querying GPC1 for camera meta data")

        meta = []
        

        sql = "SELECT exp_id, exp_name, camRun.dist_group, camRun.software_ver, rawExp.bg, rawExp.bg_stdev, chip_id, warp_id \
               FROM camRun \
               JOIN chipRun USING(chip_id) \
               JOIN rawExp USING(exp_id) \
               LEFT JOIN fakeRun USING(cam_id) \
               LEFT JOIN warpRun USING(fake_id) \
               WHERE camRun.cam_id = %d" % camID \
               + " AND warpRun.state != 'drop'"

        try:
            rs = self.executeQuery(sql)
            rs.first()
        except:
                self.logger.errorPair("Can't query for", "camera meta data")
        try:
            meta.append(rs.getInt(1))
            meta.append(rs.getString(2))
            meta.append(rs.getString(3))
            meta.append(rs.getString(4))
            meta.append(rs.getString(5))
            meta.append(rs.getString(6))
            meta.append(rs.getInt(7))
            meta.append(rs.getInt(8))
        except:
            self.logger.errorPair("getCameraStageMeta()", "empty meta data")
        return meta

    def getCameraStageMetaNoWarpID(self, camID):

        self.logger.debug("Querying GPC1 for camera meta data")

        meta = []
        

        sql = "SELECT exp_id, exp_name, camRun.dist_group, camRun.software_ver, rawExp.bg, rawExp.bg_stdev, chip_id, -999 \
               FROM camRun \
               JOIN chipRun USING(chip_id) \
               JOIN rawExp USING(exp_id) \
               LEFT JOIN fakeRun USING(cam_id) \
               LEFT JOIN warpRun USING(fake_id) \
               WHERE camRun.cam_id = %d" % camID 

        try:
            rs = self.executeQuery(sql)
            rs.first()
        except:
                self.logger.errorPair("Can't query for", "camera meta data")
        try:
            meta.append(rs.getInt(1))
            meta.append(rs.getString(2))
            meta.append(rs.getString(3))
            meta.append(rs.getString(4))
            meta.append(rs.getString(5))
            meta.append(rs.getString(6))
            meta.append(rs.getInt(7))
            meta.append(rs.getInt(8))
        except:
            self.logger.errorPair("getCameraStageMeta()", "empty meta data")

        return meta




    '''
    Gets a camera-stage smf for this cam_id
    '''
    def getCameraStageSmf(self, camID, smfversion):
        self.logger.infoPair("using", smfversion)
        self.logger.debug("Querying GPC1 for camera smf files with cam_id = " + str(camID))

        sql = "SELECT path_base \
               FROM camProcessedExp \
               JOIN camRun USING(cam_id) \
               WHERE camRun.cam_id = %d" % camID

        try:
            rs = self.executeQuery(sql)
            rs.first()
        except:
            self.logger.errorPair("Can't query for smf file for cam_id",  str(camID))
            return None

        try:
            # get path to base dir of cmf files
            path = rs.getString(1)
        except:
            self.logger.errorPair("No smf files found for cam_id", str(camID) )
            return None
       
        # list all smf files if a neb path
        files = []

        #there are a couple of states for smfversion

        # "use_new" => we have reprocessed the data, use the .smf file

        # "not_reproc" => this has not been reprocessed, use the .smf file

        # "use_original" => we *may* have reprocessed the data, 
        #    use (file).smf.original if camRun.state = 'full' and if there is a .smf and .orig
        #    use (file).smf if camRun.state = 'full' and if there is only a .smf
        #    fault out if camRun.state != 'full' -- that is a race condition we don't want.
        
        if (smfversion == "not_reproc") or (smfversion == "use_new"):
            if path.startswith("neb"):

                # XXX EAM : this should just use the name (path.smf) instead of a wildcard, right?
                f=os.popen("neb-ls -p "+path+"%smf")
                for i in f.readlines():
                    files.append(i.rstrip())

                # or not a neb path
            else:
                files = glob.glob(path + ".smf")

        if (smfversion == "use_original"):
            # get state of camRun
            sql = "SELECT state \
               FROM camProcessedExp \
               JOIN camRun USING(cam_id) \
               WHERE camRun.cam_id = %d" % camID
            try:
                rs = self.executeQuery(sql)
                rs.first()
            except:
                self.logger.errorPair("Can't query for camRun.state for cam_id",  str(camID))
                return None

            try:
                # get state of camRun.cam_id
                camrunstate = rs.getString(1)
            except:
                self.logger.errorPair("No state found for cam_id", str(camID) )
                return None
            
            if (camrunstate == "full"):
              self.logger.infoPair("camRun.state is ",camrunstate)   
                 # if full find smf files
              if path.startswith("neb"):

                 # XXX EAM : this should just use the name (path.smf) instead of a wildcard, right?
                 fsmf=os.popen("neb-ls -p "+path+"%smf")
                 fsmfs = fsmf.readlines()
                 fsmforig=os.popen("neb-ls -p "+path+"%smf.original")
                 fsmforigs = fsmforig.readlines()
                 smfCount = len(fsmfs)
                 smfOrigCount = len (fsmforigs)

                 if (smfCount > 0  and smfOrigCount > 0):
                     #smf and original  so use original
                     self.logger.infoPair("using:", path+"%smf.original")
                     for i in fsmforigs:
                         files.append(i.rstrip())

                 if (smfCount > 0 and smfOrigCount == 0):
                     self.logger.infoPair("using:", path+"%smf")
                     #smf file and no smf.original use .smf
                     for i in fsmfs:
                         files.append(i.rstrip())

                 #if not smf dont' find files ??)        
                 # or not a neb path

              else:
                # this is broken for non neb - on the other hand we probably won't do .original if so
                files = glob.glob(path + ".smf")



            # if .smf and .original use .original
            else:
                self.logger.infoPair("camRun.state not full, can't ingest", str(camID) )
                return None


        self.logger.infoPair("smf files:", files)
        if len(files) < 1:
            self.logger.errorPair("No files found for cam path", path)
            return None

        return Fits(self.logger, self.config, files[0]) # TODO just returning first file - check


    '''
    Gets the path to the cmf file for this stack_id
    
    The logic here is fairly ridiculous due to issues with how GPC1 stores info regarding multi-filter stacks. In partcular, it
    is impossible to find a full path for a cmf file for a given stack_id

    Instead, we get a path_base to a directory with a collection of cmfs. neb-ls will list 5 files, but a number < 5 may actually exist
    meaning that neb-ls -p will fail.

    our logic is as follows:
    
    - use stack_id and the DVO label to find a path base for all cmfs
    - loop through all the potential cmfs, run neb-ls -p on each
    - if neb-ls -p fails, then the file does not exists and we ignore it
    - if neb-ls -p succeeds, we open that cmf file and check the STK_ID value in its header to see if it is the one we want
    - if it is, great, exit
    - if not, try the next one in the list

    '''
    def getStackStageCmf(self, dvoDb, stackID):
     
        sql = "SELECT DISTINCT skycalResult.path_base \
               FROM skycalRun  \
               JOIN skycalResult USING(skycal_id)  \
               JOIN stackRun USING(stack_id)  \
               JOIN stackSumSkyfile USING(stack_id) \
               JOIN addRun ON skycal_id = stage_id \
               JOIN minidvodbRun USING (minidvodb_name) \
               JOIN mergedvodbRun using (minidvodb_id) \
               WHERE stack_id = " + str(stackID) + " \
               AND minidvodbRun.state = 'merged' \
               AND mergedvodbRun.state = 'full' \
               AND mergedvodbRun.mergedvodb = '" + dvoDb + "' \
               AND stage = 'skycal'"


        # print "staring stack stage cmf"
        #self.logger.infoPair("in stackstagecmf - sql is: ", sql) 
        # get single path base for the directory containing all cmf files, one of which is hopefully for our stack_id
        try:
            rs = self.executeQuery(sql)
            rs.first()
        except:
            self.logger.errorPair("Can't query for stack cmfs", "stack_id = %d" % stackID)
            return None

        try:
            pathBase = rs.getString(1)
        except:
            self.logger.errorPair("No stack cmf files found for stack_id = %d" % stackID)
            return None

        # find the files (are they in nebulous or in the filesystem?)
        files = []
        if pathBase.startswith("neb"):
            # XXX EAM : this should just use the name (path.smf) instead of a wildcard, right?
            f=os.popen("neb-ls -p " + pathBase + "%cmf")
            for i in f.readlines():
                files.append(i.rstrip())

        # or not a neb path
        else:
            files = glob.glob(pathBase + ".cmf")

        # print "stack cmf files:", files
        if len(files) < 1:
            self.logger.errorPair("No files found for stack path", pathBase)
            return None

        # if we get here, then the cmf is readable, now check the stack_id
        fits = Fits(self.logger, self.config, files[0])
        print self.logger.infoPair("cmf file",files[0])
        return fits

    '''
    TODO hack to get exposure time for a stack
    '''
    def getStackExpTime(self, stackID):

        self.logger.debug("Querying GPC1 for stack exposure time")

        #this overcounts... likely also true for skycal., so I don't include skycalRun or staticskyRun

        sql = "SELECT SUM(exp_time) * (COUNT(warp_id) - reject_images) / COUNT(warp_id) as EXPTIME \
               FROM stackRun using(stack_id) \
               JOIN stackSumSkyfile using(stack_id) \
               JOIN stackInputSkyfile using(stack_id) \
               JOIN warpRun using(warp_id) \
               JOIN fakeRun using(fake_id) \
               JOIN camRun using(cam_id) \
               JOIN chipRun using(chip_id) \
               JOIN rawExp using(exp_id) \
               WHERE stack_id = %d" % stackID

        try:
            rs = self.executeQuery(sql)
            rs.first()
            return rs.getInt(1)
        except:
            self.logger.exception("Can't query for exposure time")

        return 0.0


    '''
    grabs the stackID for a particular skyId, filter, and dvodb
    the mapping of stack_id from sky_id is not unique unless
    joined to the dvodb 
    '''
    def getStackIDFromSkyIDAndFilter(self,dvoDb,skyID,filter):

        self.logger.infoPair("finding stack_ids from gpc1 for sky_id ", skyID)

        sql = "SELECT DISTINCT stack_id from skycalRun \
               JOIN addRun ON(skycalRun.skycal_id = addRun.stage_id) \
               JOIN minidvodbRun USING(minidvodb_name) \
               JOIN minidvodbProcessed USING(minidvodb_id) \
               JOIN mergedvodbRun USING(minidvodb_id) \
               JOIN mergedvodbProcessed USING(merge_id) \
               JOIN stackRun USING (stack_id) \
               WHERE mergedvodbRun.mergedvodb = '" + dvoDb + "' \
               AND minidvodbRun.state = 'merged' \
               AND minidvodbProcessed.fault = 0 \
               AND mergedvodbRun.state = 'full' \
               AND mergedvodbProcessed.fault = 0  \
               AND addRun.stage = 'skycal' \
               AND addRun.state = 'full' \
               AND filter = '" + filter + "' \
               AND sky_id = %d" % skyID 

#        self.logger.infoPair("sql",sql)

        try:
            rs = self.executeQuery(sql)
                         
        except:    
            self.logger.exception("can't query for stackid in DVO")
            self.logger.infoPair("failed sql:", sql)
            return 0

        rs.first()
        try:
            stackid = rs.getInt(1)
            self.logger.infoPair("found stack_ID", stackid)
        except:
            stackid = -999
            self.logger.infoPair("using stack_ID",stackid)
            
        return stackid    
