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

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

        self.logger.debug("Gpc1Db destructor")


    '''
    Gets a list of ids in this DVO database for this stage, could be cam or staticsky, and a box can be defined with equatorial coordinates
    '''
    def getIDsInThisDVODbForThisStage(self, dvoDb, batchType, minRA=-999, maxRA=999, minDec=-999, maxDec=999):

        if batchType == "P2": # TODO define these someplace 

            stage = "cam"
            sql = "SELECT DISTINCT stage_id \
                   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) \
                   WHERE minidvodbRun.minidvodb_group = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   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 FROM staticskyInput \
                   JOIN addRun ON(staticskyInput.sky_id = addRun.stage_id) \
                   JOIN minidvodbRun USING(minidvodb_name) \
                   JOIN minidvodbProcessed USING(minidvodb_id) \
                   WHERE minidvodbRun.minidvodb_group = '" + dvoDb + "' \
                   AND minidvodbRun.state = 'merged' \
                   AND minidvodbProcessed.fault = 0 \
                   AND addRun.stage = '" + stage + "' \
                   AND addRun.state = 'full'"

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

        ids = []
        while (rs.next()):
            ids.append(rs.getInt(1))

        rs.close()

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

        return ids

    '''
    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)
            path = path[0:path.rfind("/")]
        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.doc, files[0]) # TODO just returning first file - check


    '''
    Gets the path to the cmf file for this stack_id
    '''
    def getStackStageCmf(self, stackID):
      
        sql = "SELECT 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) \
               WHERE stack_id = %s" % stackID

        try:
            rs = self.executeQuery(sql)
            rs.first()
            pathBase = rs.getString(1)

            # now find ALL cmf files at this path_base as there can be more than one
            f=os.popen("neb-ls -p " + pathBase + "%cmf")
            for i in f.readlines():

                path = i.rstrip()
                fits = Fits(self.logger, self.doc, path)

                # we need to check if this is the correct cmf file, and if so then break and return
                if fits.getPrimaryHeaderValue("STK_ID") == str(stackID):
                    return fits

        except:
            self.logger.exception("Can't query for stack cmfs")

        self.logger.error("Could not find stack cmf")
        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


