import pymssql
import src.settings as settings

class CommonDB:
    
    
    COMMONDB_CONNECTION = 0
    
    def __init__(self, host, user, password, schema):

        self.logger = settings.logger
        self.logger.debug('CommonDB initialized 1')
        self._Host = host 
        self._User = user
        self._Password = password
        self._Schema = schema
        self._Connection = None

        self.logger.debug('CommonDB initialized 2' +self._Schema)

    def __del__(self):
        
        if self._Connection is not None:
            self.disconnect()
        
    def disconnect(self):
        
        if self._Connection is not None:
            CommonDB.COMMONDB_CONNECTION -= 1
            self._Connection.close()
            self._Connection = None

        self.logger.warning("Closed COMMONDB DBConnection: {_c}".format(_c=CommonDB.COMMONDB_CONNECTION))

    def connect(self):

        if self._Connection is None:
            CommonDB.COMMONDB_CONNECTION += 1
            self.logger.warning("Open COMMONDB DBConnection: {_c}".format(_c=CommonDB.COMMONDB_CONNECTION))
            self._Connection = pymssql.connect(server=self._Host, user=self._User, password=self._Password)

    def checkToken(self, token):

        if self._Connection!=None:
            cur = self._Connection.cursor()
            cur.execute("use " + self._Schema + "; SELECT customer, default_db_id FROM authentication WHERE auth_token=%s ",(token,))
            res = cur.fetchone()

            return res

    def get_routing_data(self, logger_id, default_db_id, token):

        if self._Connection != None:

            cur = self._Connection.cursor()
            '''
            cur.execute("use " + self._Schema + "; SELECT db_id FROM data_routing WHERE logger_id=%s AND auth_token=%s", (logger_id, token))
            res_db_id = cur.fetchall()
            if res_db_id !=[]:
                self.logger.debug('logger id found in data_routing')
                db_id = res_db_id[0]
                self.logger.info('db_id: '+str(db_id))
            else:
                db_id = default_db_id
                cur.execute('INSERT INTO data_routing(logger_id, db_id, auth_token) VALUES(%s, %s, %s)', (logger_id, db_id, token))
                
            cur.execute("use " + self._Schema + "; SELECT host, port, db_name, db_type, username, psw, master_db, db_adjusted  FROM db_info WHERE db_id=%s", (db_id,))
            '''
            cur.execute("""SELECT db_info.host, db_info.port, db_info.db_name,
                                db_info.db_type, db_info.username, db_info.psw,
                                data_routing.master_db, db_info.db_adjusted
                        FROM db_info 
                        JOIN data_routing ON db_info.db_id=data_routing.db_id
                        WHERE data_routing.logger_id = %s AND data_routing.auth_token=%s""", (logger_id, token))


            res = cur.fetchall()
            routing_info_dict = {'master' : {},
                                 'mirror' : []}
            if res!=[]:
                for row in res:
                    routing_info = {}
                    routing_info['host'] = row[0]
                    routing_info['port'] = row[1]
                    routing_info['db_name'] = row[2]                
                    routing_info['db_type'] = row[3]
                    routing_info['username'] = row[4]
                    routing_info['psw'] = row[5]
                    routing_info['master_db'] = row[6]
                    if routing_info['master_db'] is True:
                        routing_info_dict['master'] = routing_info
                    else:
                        routing_info_dict['mirror'].append(routing_info)
                        
                    db_adjusted = row[7]
                    
                    
                    if db_adjusted is not True:
                        self.adjust_db(routing_info['host'],
                                        routing_info['username'], 
                                        routing_info['psw'], 
                                        routing_info['db_name'])
                        
                        cur.execute("""UPDATE db_info SET db_adjusted=1 
                        WHERE db_name='{_db_name}' """.format(_db_name=routing_info['db_name']) )
            else:
                self.logger.warning('No routing data was found for logger ' + logger_id + ' and authentication token ' + token)
                

            self.logger.info('db_name ' + routing_info['db_name'])

            self._Connection.commit()

            return routing_info_dict

    def checkLoggerId(self, loggerId, token):
            
        if self._Connection is not None:
            
            cur = self._Connection.cursor()
            cur.execute("use " + self._Schema + "; SELECT db_id FROM data_routing WHERE logger_id=%s AND auth_token=%s AND master_db=1", (loggerId, token))
            res = cur.fetchone()
                
            if res is not None:
                return True
            else:
                return False
 
    def adjust_db(self, host, user, psw, db_name):
        
       	conn = pymssql.connect(server=host, user=user, password=psw)
        cur = conn.cursor()
	try:
		cur.execute('use ' + db_name + ';')
        	cur.execute("ALTER TABLE xxx_Virtual_Devices ALTER COLUMN Table_name varchar(255)")
        	cur.execute("ALTER TABLE xxx_RAW_Device_Properties ALTER COLUMN DEVICE_VALUE varchar(255)")
		cur.execute("DROP INDEX xxx_Last_Data.Table_Name_IDX")
		cur.execute("ALTER TABLE xxx_Last_Data ALTER COLUMN Table_Name nvarchar(255)")
		cur.execute("""CREATE NONCLUSTERED INDEX [Table_Name_IDX] ON [dbo].[xxx_Last_Data] 
			(	
   			 [Table_Name] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] """)
		cur.execute("ALTER TABLE xxx_Plant_Device DROP CONSTRAINT PK__xxx_Plan__3214EC2707020F21")
		cur.execute("ALTER TABLE xxx_Plant_Device DROP COLUMN ID")
		cur.execute("DROP INDEX xxx_Plant_Device.Date_and_Time_IDX")
		cur.execute("ALTER TABLE xxx_Plant_Device ALTER COLUMN Date_and_Time datetime NOT NULL")
		cur.execute("ALTER TABLE xxx_Plant_Device ADD PRIMARY KEY (Date_and_Time)")
        except Exception as e:
		conn.rollback()
		self.logger.error("CommonDB: Error when updating DB" + str(e))

        conn.commit()
        conn.close()