"""
- Access to the Gpc1 database
- Definition of high-level values (recipient, label...)
"""
import sys
import MySQLdb
from ipp.IppSite import Gpc1

#################################################################
class Gpc1Manager:
    """
    Access to the Gpc1 database
    """
    def __init__(self,
                 recipient = 'schastel@ifa.hawaii.edu'):
        """Create a connector to the gpc1 database
        """
        self.recipient = recipient
        self.connector = MySQLdb.connect(Gpc1.HOST, 
                                         Gpc1.USER, 
                                         Gpc1.PASSWORD, 
                                         db = Gpc1.NAME)
        self.label = None # Set by derived classes

    ##############################################################
    def close(self):
        """Close the connection
        """
        self.connector.close()

    ##############################################################
    def multiple_query(self, query):
        """Perform a MySQL query ("multiple" possibly empty answers)
        """
        cursor = self.connector.cursor()
        cursor.execute(query)
        rows = cursor.fetchall()
        cursor.close()
        self.connector.commit()
        return rows

    ##############################################################
    def simple_query(self, query):
        """Perform a MySQL query (unique answer)
        """
        rows = self.multiple_query(query)
        self.connector.commit()
        if rows is None:
            return None
        if len(rows) > 1:
            raise Exception("More than one answer to statement [%s]"
                            % query)
        return rows[0]

    ##############################################################
    def get_exposure_id(self, exposure_name):
        """Get the exposure id associated to a named exposure
        """
        query = "SELECT exp_id FROM rawExp WHERE exp_name = '%s'" % exposure_name
        return int(self.simple_query(query)[0])

    ##############################################################
    def get_chip_id(self, exposure_name, label = None):
        """Get the chip id associated to a named exposure
        """
        query = "SELECT chip_id FROM chipRun JOIN rawExp USING(exp_id) "
        query = "%s WHERE exp_name = '%s'" % (query, exposure_name)
        if label is not None:
            query = "%s AND label = '%s'" % (query, label)
        return int(self.simple_query(query)[0])

    ##############################################################
    def get_fake_id(self, exposure_name, label = None):
        """Get the fake id associated to a named exposure
        """
        query = "SELECT fake_id FROM fakeRun JOIN camRun USING(cam_id) JOIN chipRun USING(chip_id)"
        query = "%s JOIN rawExp USING(exp_id) WHERE exp_name = '%s'" % (query,
                                                                        exposure_name)
        if label is not None:
            query = "%s AND fakeRun.label='%s'" % (query, label)
        return int(self.simple_query(query)[0])

    ##############################################################
    def get_warp_id(self, exposure_name, label):
        """Get the warp id associated to a named and labelled exposure
        """
        query = "SELECT warp_id FROM warpRun JOIN fakeRun USING(fake_id) JOIN camRun USING(cam_id)"
        query = "%s JOIN chipRun USING(chip_id) JOIN rawExp USING(exp_id)" % query
        query = "%s WHERE warpRun.label='%s' AND exp_name='%s'" % (query, label, exposure_name)
        return int(self.simple_query(query)[0])

    ##############################################################
    def send_email(self, subject, body):
        """
        Send an e-mail to the recipient (which has been previously defined)
        """
        import smtplib
        from email.mime.text import MIMEText
        message = MIMEText(body)
        message['Subject'] = subject
        _from = 'ippdor@ifa.hawaii.edu'
        message['From'] = _from
        _to = [ self.recipient, 'schastel@ifa.hawaii.edu' ]
        message['To'] = self.recipient
        smtp_connection = smtplib.SMTP('ippc18.ifa.hawaii.edu')
        smtp_connection.sendmail(_from, _to, message.as_string())
        smtp_connection.quit()

    ##############################################################
    def is_label_used(self):
        """
        Check if the label is used in the chipRun table
        """
        query = """SELECT label from chipRun WHERE label = '%s' LIMIT 1""" % self.label
        values = self.multiple_query(query)
        return values is not None and len(values) != 0

    ##############################################################
    def is_exposure_name_valid(self, exposure_name):
        """
        Check if the exposure named exposure_name is in the rawExp table
        """
        query = """SELECT exp_id FROM rawExp WHERE exp_name='%s'""" % exposure_name
        return int(self.simple_query(query)[0])

#################################################################
if __name__ == '__main__':
    print 'Not intended to be used as an independent program'
    GPC1 = Gpc1Manager(recipient = 'schastel@ifa.hawaii.edu')
    GPC1.send_email("This is a test", "Condor e-mail test")
    sys.exit(1)
