#! /usr/bin/python
# -*- coding: utf-8 -*-


from datetime import datetime, timedelta
import ast
import mysql.connector
from mysql.connector.errors import ProgrammingError, Error


class DbInstance:

    def __init__(self, host, username, password, port, database):
        self.host = host
        self.username = username
        self.password = password
        self.port = port
        self.database = database
        self.cnx = None
        self.cursor = None

    def connect(self):
        try:
            self.cnx = mysql.connector.connect(user=self.username, password=self.password, host=self.host,
                                               database=self.database, port=self.port)
            self.cursor = self.cnx.cursor()
            return True
        except Error:
            return False

    def get_max_nes_id(self):
        sql = ("SELECT MAX(neid) FROM nes")
        self.cursor.execute(sql)
        row = self.cursor.fetchone()
        if row and row[0]:
            return row[0]
        else:
            return 0

    def insert_ne(self, dict_ne, id):
        print 'Process the data table "nes"...'
        sql = ("INSERT INTO nes (neid, nename, netype, ip, port, username, password, sftpPort, sftpUsername, "
               "sftpPassword, pmFileRemotePath, fmFileRemotePath, nelogFileRemotePath, lipwd, froid) VALUES "
               "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")

        s = (id, dict_ne['nename'], dict_ne['netype'], dict_ne['ip'], dict_ne['port'], dict_ne['username'],
             dict_ne['password'], dict_ne['sftpPort'], dict_ne['sftpUsername'], dict_ne['sftpPassword'],
             dict_ne['pmFileRemotePath'], dict_ne['fmFileRemotePath'], dict_ne['nelogFileRemotePath'], dict_ne['lipwd'],
             dict_ne['froid'])
        self.cursor.execute(sql, s)
        self.cnx.commit()

        print 'Process the data table "neconfig"...'
        ne_name = dict_ne['nename']
        sql = ("INSERT INTO neconfig (neid, nodeId, licId) VALUES (%s, %s, %s)")
        s = (id, 'N' + ne_name, 'L' + ne_name)
        self.cursor.execute(sql, s)
        self.cnx.commit()
        if dict_ne.has_key('snmpport') and dict_ne.has_key('usmusername') and dict_ne.has_key('authpassword') \
                and dict_ne.has_key('privpassword') and dict_ne.has_key('serviceusername') \
                and dict_ne.has_key('servicepassword'):
            print 'Process the data table "ne_additinal_snmp" since the added node has SNMP V3 infomation'

            sql = ("INSERT INTO ne_additional_snmp (neid, snmpport, usmusername, authpassword, privpassword, "
                   "serviceusername, servicepassword) VALUES (%s, %s, %s, %s, %s, %s, %s)")
            s = (id, dict_ne['snmpport'], dict_ne['usmusername'], dict_ne['authpassword'], dict_ne['privpassword'],
                 dict_ne['serviceusername'], dict_ne['servicepassword'])
            self.cursor.execute(sql, s)
            self.cnx.commit()
        print 'Update "nelist" in table users...'

        sql = ("SELECT nelist from users WHERE username=%s")
        self.cursor.execute(sql, ('admin', ))
        row = self.cursor.fetchone()
        if row and row[0]:
            # ne_list = ast.literal_eval(row[0].decode('utf-8'))
            # ne_list.append('{"name":"%d"}' % id)
            str_ne_list = row[0].decode('utf-8').strip()[:-1]
            if '[' != str_ne_list[-1]:
                str_ne_list += ','
            str_ne_list += '{"name":"%d"}]' % id

            sql = ("UPDATE users SET nelist=%s WHERE username=%s")
            s = (str_ne_list, 'admin')
            self.cursor.execute(sql, s)
            self.cnx.commit()

    def insert_alarm(self):
        pass

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.cnx:
            self.cnx.close()