#!/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.test): 
            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):

        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 minidvodbRun USING(minidvodb_name) \
                   JOIN minidvodbProcessed USING(minidvodb_id) \
                   JOIN camRun ON (stage_id = 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 addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full' \
                   AND decl BETWEEN RADIANS(" + str(minDec) + ") AND RADIANS(" + str(maxDec) + ") \
                   AND ra BETWEEN RADIANS(" + str(minRA) + ") AND RADIANS(" + str(maxRA) + ")"

        elif batchType == "ST":       

            stage = "skycal"
            sql = "SELECT DISTINCT stack_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 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 BETWEEN " + str(minDec) + " AND " + str(maxDec) + " \
                   AND radeg BETWEEN " + str(minRA) + " AND " + str(maxRA) 
            
        try:
            # XXX EAM : test output
            self.logger.infoPair("sql for dvo items:", sql)
            rs = self.executeQuery(sql)
            while (rs.next()): 
               rows.append([rs.getInt(1), rs.getFloat(2), rs.getFloat(3)])
            rs.close()
        except:
            self.logger.exception("Can't query for ids in DVO")
            return rows

        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 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 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 \
               FROM camRun \
               JOIN chipRun USING(chip_id) \
               JOIN rawExp USING(exp_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))
        except:
            self.logger.errorPair("getCameraStageMeta()", "empty meta data")

        return meta

    '''
    Gets a camera-stage smf for this cam_id
    '''
    def getCameraStageSmf(self, camID):

        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 = []
        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")

        # XXX EAM : test output
        self.logger.infoPair("smf files:", files)
        if len(files) < 1: 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 staticskyResult.path_base \
               FROM staticskyRun  \
               JOIN staticskyInput USING(sky_id)  \
               JOIN staticskyResult USING(sky_id) \
               JOIN stackRun USING(stack_id)  \
               JOIN stackSumSkyfile USING(stack_id) \
               JOIN addRun ON sky_id = stage_id \
               JOIN minidvodbRun USING (minidvodb_name) \
               WHERE stack_id = " + str(stackID) + " \
               AND minidvodbRun.minidvodb_group = '" + dvoDb + "' \
               AND minidvodbRun.state = 'merged' \
               AND stage = 'staticsky'"
        '''

        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"

        # 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: return None

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

        # XXX validate the file?
        # if fits.getPrimaryHeaderValue("STK_ID") == str(stackID):

            # now find and loop through all cmf files at this path_base

            ## XXX the code below was needed when we ingested from staticsky (N files per run)
            ## XXX ingesting from skycal solves this problem (1 file per run)
            ## files=os.popen("neb-ls " + pathBase + "%cmf")
            ## for i in files.readlines():
            ##     nebPath = i.rstrip()
            ## 
            ##     # now attempt to run 'neb-ls -p' to actually read this cmf file
            ##     try:
            ##         self.logger.debug("Trying to neb-ls -p on '" + nebPath + "'")
            ##         paths=os.popen("neb-ls -p " + nebPath)
            ##         path = paths.readline().rstrip()
            ##         if len(path) < 1:
            ##            self.logger.debug("zero length path - skipping")
            ##            raise
            ## 
            ##         print "starint stack stage cmf 3"
            ## 
            ##         # if we get here, then the cmf is readable, now check the stack_id
            ##         fits = Fits(self.logger, self.config, path)
            ##         if fits.getPrimaryHeaderValue("STK_ID") == str(stackID):
            ##             # we have the right file!
            ##             self.logger.debug("SUCCESS!: %s == %s"% (fits.getPrimaryHeaderValue("STK_ID"), str(stackID)))
            ##             return fits
            ##         else:
            ##             # this is not the correct file
            ##             self.logger.debug("STACK ID: %s != %s"% (fits.getPrimaryHeaderValue("STK_ID"), str(stackID)))
            ## 
            ##     # an exception here means that nebulous could not read this file, so we just skip it
            ##     except:
            ##         self.logger.debug("NEB FAILED SKIPPING")
            ##         pass

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


