class MECODBReader(object):
    """
    Read records from a database.
    """

    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: True if in testing mode.
        """

        self.connector = MSGDBConnector()
        self.conn = MSGDBConnector(testing).connectDB()
        self.dbUtil = MSGDBUtil()
        self.dbName = self.dbUtil.getDBName(self.connector.dictCur)

    def selectRecord(self, conn, table, keyName, keyValue):
        """
        Read a record in the database given a table name, primary key name,
        and value for the key.

        :param conn DB connection
        :param table DB table name
        :param keyName DB column name for primary key
        :param keyValue Value to be matched
        :returns: Row containing record data.
        """

        print "selectRecord:"
        sql = """SELECT * FROM "%s" WHERE %s = %s""" % (
        table, keyName, keyValue)
        dcur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.dbUtil.executeSQL(dcur, sql)
        row = dcur.fetchone()
        return row

    def readingAndMeterCounts(self):
        """
        Retrieve the reading and meter counts.

        :returns: Multiple lists containing the retrieved data.
        """

        sql = """SELECT "Day", "Reading Count",
        "Meter Count" FROM count_of_readings_and_meters_by_day"""
        dcur = self.conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.dbUtil.executeSQL(dcur, sql)
        rows = dcur.fetchall()

        dates = []
        meterCounts = []
        readingCounts = []

        for row in rows:
            dates.append(row[0])
            readingCounts.append(row[1] / row[2])
            meterCounts.append(row[2])

        return dates, readingCounts, meterCounts

    def test_log_successful_export(self):
        """
        Test logging of export results to the export history table.
        """
        # @REVIEWED
        self.assertTrue(self.exporter.logSuccessfulExport(name = 'test_export',
                                                          url =
                                                          'http://test_url',
                                                          datetime = 0,
                                                          size = 100))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()

        self.assertTrue(
            dbUtil.executeSQL(cursor, 'select * from "ExportHistory" where '
                                      'timestamp = '
                                      'to_timestamp(0)'))

        self.assertEqual(len(cursor.fetchall()), 1,
                         "There should only be one result row.")

        self.assertTrue(
            dbUtil.executeSQL(cursor, 'delete from "ExportHistory" where '
                                      'timestamp = to_timestamp(0)'))
        conn.commit()

class MECODBDeleter(object):
    """
    Provide delete routines for MECO DB.
    """

    def __init__(self):
        """
        Constructor.
        """
        self.dbUtil = MSGDBUtil()


    def deleteRecord(self, conn, tableName, idText, idValue):
        """
        Delete record from DB where record has an int-based serial number.

        param: tableName
        param: idText DB column name for record ID
        param: idValue Value of the ID to be deleted
        """

        sql = """DELETE FROM "{}" where {} = {}""".format(tableName, idText,
                                                          idValue)
        dictCur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.dbUtil.executeSQL(dictCur, sql)
        conn.commit()

class TestMECODupeChecker(unittest.TestCase):
    """
    Unit tests for duplicate checking.
    """

    def setUp(self):
        self.dupeChecker = MECODupeChecker()
        self.p = MECOXMLParser(True) # run in testing mode
        self.dbConnect = MSGDBConnector(True)
        self.dbUtil = MSGDBUtil()
        self.conn = self.dbConnect.connectDB()
        self.cur = self.conn.cursor()

    def testInit(self):
        self.assertEqual(self.dupeChecker.__class__.__name__, "MECODupeChecker",
                         "Dupe checker has been created.")

    def testFindIndividualDupe(self):
        """
        Find a duplicate record when only one exists.
        """
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        self.p.parseXML(fileObject, True)

        self.assertTrue(
            self.dupeChecker.readingBranchDupeExists(self.conn, '100000',
                                                     '2013-04-08 00:30:00',
                                                     '1', True),
            "Record should already exist")

    def testLoadOnTop(self):
        """
        If the same data set is loaded in succession,
        all values will be duplicated. Verify that this is true.

        This is no longer possible as
        duplicates are dropped before insertion.
        """

        pass

    def testLoadSingleMissingEntry(self):
        """
        A reading will be inserted into the database where the reading does
        not currently exist as determined by the
        MeterName-IntervalEndTime-Channel tuple.
        """

        pass

    def tearDown(self):
        self.dbConnect.closeDB(self.conn)

class TestMECOXMLParser(unittest.TestCase):
    """
    Unit tests for MECO XML Parser.
    """

    def setUp(self):
        self.p = MECOXMLParser(True) # run in testing mode
        self.dbConnect = MSGDBConnector(True)
        self.dbUtil = MSGDBUtil()
        self.conn = self.dbConnect.connectDB()
        self.cur = self.conn.cursor()

    def testMECOXMLParserCanBeInited(self):
        self.assertIsNotNone(self.p)

    def testEveryElementIsVisited(self):
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        expectedCount = 125
        self.p.parseXML(fileObject, True)
        print "element count = %s" % self.p.processForInsertElementCount
        self.assertEqual(self.p.processForInsertElementCount, expectedCount)

    def testAllTableNamesArePresent(self):
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        self.p.parseXML(fileObject, True)
        fail = False

        for key in self.p.tableNameCount.keys():
            print key + ": ",
            print self.p.tableNameCount[key]

            if self.p.tableNameCount[key] < 1:
                if key != 'ChannelStatus' and key != 'IntervalStatus' and key \
                        != 'EventData' and key != 'Event':
                    print "table = %s" % key
                    fail = True
        self.assertFalse(fail,
                         "At least one table of each type should have been "
                         "encountered.")

    def tearDown(self):
        self.dbConnect.closeDB(self.conn)

 def setUp(self):
     self.dupeChecker = MECODupeChecker()
     self.p = MECOXMLParser(True) # run in testing mode
     self.dbConnect = MSGDBConnector(True)
     self.dbUtil = MSGDBUtil()
     self.conn = self.dbConnect.connectDB()
     self.cur = self.conn.cursor()

    def __init__(self):
        """
        Constructor.
        """

        warnings.simplefilter('default')
        warnings.warn("This module is deprecated in favor of SEKNotifier.",
                      DeprecationWarning)

        self.config = MSGConfiger()
        self.logger = SEKLogger(__name__, 'info')
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.noticeTable = 'NotificationHistory'
        self.notificationHeader = "This is a message from the Hawaii Smart " \
                                  "Energy Project MSG Project notification " \
                                  "system.\n\n"

        self.noReplyNotice = '\n\nThis email account is not monitored. No ' \
                             'replies will originate from this ' \
                             'account.\n\nYou are receiving this message ' \
                             'because you are on the recipient list for ' \
                             'notifications for the Hawaii Smart Energy ' \
                             'Project.'

    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG')
        self.cursor = MSGDBConnector().connectDB().cursor()
        self.dbUtil = MSGDBUtil()

class MSGWeatherDataDupeChecker(object):
    """
    Determine if a duplicate record exists based on the tuple

    (WBAN, Date, Time, StationType).
    """

    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: Flag for testing mode.
        """

        self.logger = SEKLogger(__name__, 'debug')
        self.dbUtil = MSGDBUtil()


    def duplicateExists(self, dbCursor, wban, datetime, recordType):
        """
        Check for the existence of a duplicate record.

        :param dbCursor
        :param wban
        :param datetime
        :param recordType
        :returns: True if a duplicate record exists, otherwise False.
        """

        tableName = "WeatherNOAA"
        sql = """SELECT wban, datetime, record_type FROM \"%s\" WHERE
                 wban = '%s' AND datetime = '%s' AND record_type = '%s'""" % (
            tableName, wban, datetime, recordType)

        self.logger.log("sql=%s" % sql, 'debug')
        self.logger.log("wban=%s, datetime=%s, record_type=%s" % (
            wban, datetime, recordType), 'debug')

        self.dbUtil.executeSQL(dbCursor, sql)
        rows = dbCursor.fetchall()

        if len(rows) > 0:
            return True
        else:
            return False

    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'debug')
        self.mecoConfig = MSGConfiger()
        self.currentReadingID = 0
        self.dbUtil = MSGDBUtil()

    def __init__(self, testing = False):
        """
        Constructor.
        :param testing: True if testing mode is being used.
        """

        self.logger = SEKLogger(__name__, 'info')
        self.dbUtil = MSGDBUtil()
        self.dupeChecker = MSGWeatherDataDupeChecker()

    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: Flag for testing mode.
        """

        self.logger = SEKLogger(__name__, 'debug')
        self.dbUtil = MSGDBUtil()

    def __init__(self):
        """
        Constructor.
        """

        self.logger = MSGLogger(__name__, 'debug')
        self.mapper = MECOMapper()
        self.dupeChecker = MECODupeChecker()
        self.dbUtil = MSGDBUtil()

 def setUp(self):
     self.reader = MECODBReader()
     self.connector = MSGDBConnector(True)
     self.conn = self.connector.connectDB()
     self.inserter = MECODBInserter()
     self.util = MSGDBUtil()
     self.lastSeqVal = None
     self.tableName = 'MeterData'
     self.colName = 'meter_data_id'
     self.deleter = MECODBDeleter()

    def __init__(self):
        """
        Constructor.
        """

        print __name__
        self.logger = SEKLogger(__name__)
        self.connector = MSGDBConnector()
        self.dbUtil = MSGDBUtil()
        self.notifier = MSGNotifier()
        self.configer = MSGConfiger()

    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: True if in testing mode.
        """

        self.connector = MSGDBConnector()
        self.conn = MSGDBConnector(testing).connectDB()
        self.dbUtil = MSGDBUtil()
        self.dbName = self.dbUtil.getDBName(self.connector.dictCur)

 def __init__(self):
     """
     Constructor.
     """
     self.logger = MSGLogger(__name__, 'DEBUG')
     self.aggregator = MSGDataAggregator()
     self.notifier = MSGNotifier()
     self.rawTypes = ['weather', 'egauge', 'circuit', 'irradiance']
     self.connector = MSGDBConnector()
     self.conn = self.connector.connectDB()
     self.cursor = self.conn.cursor()
     self.dbUtil = MSGDBUtil()

 def setUp(self):
     self.i = MECODBInserter()
     self.util = MSGDBUtil()
     self.connector = MSGDBConnector(True)
     self.deleter = MECODBDeleter()
     self.reader = MECODBReader()
     self.lastSeqVal = None
     self.conn = self.connector.connectDB()
     self.sampleTableName = 'MeterData'
     self.sampleDict = {'MeterName': '100001', 'UtilDeviceID': '100001',
                        'MacID': '00:00:00:00:00:00:00:00'}
     self.keyName = 'meter_data_id'

    def countOfDBExports(self, since = None):
        """
        :param since: datetime indicating last export datetime.
        :return: Int of count of exports.
        """
        myDatetime = lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%S')
        if not since:
            since = myDatetime('1900-01-01 00:00')
        self.logger.log(since.strftime('%Y-%m-%d %H:%M'), 'DEBUG')

        sql = 'SELECT COUNT("public"."ExportHistory"."timestamp") FROM ' \
              '"public"."ExportHistory" WHERE "timestamp" > \'{}\''.format(
            since.strftime('%Y-%m-%d %H:%M'))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        rows = None
        if dbUtil.executeSQL(cursor, sql, exitOnFail = False):
            rows = cursor.fetchall()
        assert len(rows) == 1, 'Invalid return value.'
        return rows[0][0]

def insertData(files, table, cols, testing = False):
	"""
	Insert aggregated data generated by this script into a database table.

	:param files: A list of the filenames to be processed.
	:param table: The name of the table in the DB.
	:param cols: A list of the columns (as strings) in the table.
	:param testing: Specify whether to use test (false by default).
	"""
	connector = MSGDBConnector()
	conn = connector.connectDB()
	dbUtil = MSGDBUtil()
	cursor = conn.cursor()

	cnt = 0

	for file in files:

		with open(file, 'r') as csvfile:
			reader = csv.reader(csvfile, delimiter = ',')
			# Skip the header line.
			reader.next()
			for row in reader:
				sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % (
					table, ','.join(cols),
					','.join("'" + item.strip() + "'" for item in row))

				sql = sql.replace("'NULL'", 'NULL')

				dbUtil.executeSQL(cursor, sql)

				cnt += 1
				if cnt % 10000 == 0:
					conn.commit()

		conn.commit()
		cnt = 0