#!/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):
        super(Gpc1Db, self).__init__(logger, config, "gpc1database")

    '''
    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) \
                   WHERE mergedvodbRun.mergedvodb = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND mergedvodbRun.state = 'full' \
                   AND minidvodbProcessed.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 = "staticsky"
            sql = "SELECT DISTINCT stack_id, radeg, decdeg FROM staticskyInput \
                   JOIN addRun ON(staticskyInput.sky_id = addRun.stage_id) \
                   JOIN minidvodbRun USING(minidvodb_name) \
                   JOIN minidvodbProcessed USING(minidvodb_id) \
                   JOIN  stackRun USING(stack_id) JOIN skycell USING(skycell_id) \
                   WHERE minidvodbRun.minidvodb_group = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND minidvodbProcessed.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:
            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))

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

            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 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 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) \
               JOIN mergedvodbRun using (minidvodb_id) \
               WHERE stack_id = " + str(stackID) + " \
               AND minidvodbRun.state = 'merged' \
               AND mergedvodbRun.state = 'full' \
               AND mergedvodbRun.mergedvodb = '" + dvoDb + "' \
               AND stage = 'staticsky'"


        # 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()
            pathBase = rs.getString(1)

            # now find and loop through all cmf files at this path_base
            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

                    # 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

        except:
            self.logger.errorPair("Can't query for stack cmfs", "stack_id = %d" % stackID)

        self.logger.errorPair("Couldn't find stack cmf for", "stack_id = %d" % stackID)
        return None

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

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

        sql = "SELECT SUM(exp_time) * (COUNT(warp_id) - reject_images) / COUNT(warp_id) as EXPTIME \
               FROM staticskyRun JOIN staticskyInput using(sky_id) \
               JOIN 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


