import pymssql
import datetime

from src.containers.LoggerContainer import LoggerContainer
import src.settings as settings

class LoggerDB:
    
    def __init__(self, host, user, password):
        
        self._Host = host
        self._User = user 
        self._Password = password        
        self._Connection = None
        self._commondb = settings.commondb
        
        
    def __del__(self):
        
        if self._Connection is None:
            self._Connection.close()
             
    def connect(self):
        
        if self._Connection is None:
            self._Connection = pymssql.connect(server=self._Host, user=self._User, password=self._Password)
            
    def setData(self, container):
        
        settings.logger.info("LoggerDB: entered setData()")
        
        if self._Connection!=None:
            cur = self._Connection.cursor()
            listFailedInserts = []
                        
            
            for log in container.listLogger:
                
                try:
                #if True:
                    settings.logger.info('routing schema:' + self._commondb)
                    new_db = log.upid
                    print new_db
                    
                    cur.execute("use " + self._commondb + "; SELECT db_id FROM db_info WHERE db_name=%s",(new_db))
                    res = cur.fetchone()
                    new_db_id = res[0]
                    
                    # check if logger is new 
                    cur.execute("SELECT db_name FROM dbo.db_info "
                                "JOIN dbo.data_routing ON dbo.db_info.db_id=dbo.data_routing.db_id "
                                "WHERE dbo.data_routing.logger_id=%s ", (log.id,))
                    
                    res = cur.fetchone()
                
                    if res==None:
                        print 'logger unknown; db schema is none'
                        cur.execute("use " + new_db + "; IF NOT EXISTS (SELECT * FROM datalogger WHERE logger_id=%s) "
                                     "INSERT INTO datalogger (logger_id,upid,creation_date) VALUES(%s,%s,%s) "
                                     "ELSE UPDATE datalogger "
                                     "SET upid=IsNull(NULLIF(%s,'None'),upid) "
                                     "WHERE logger_id=%s", 
                                     (log.id,log.id,log.upid,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),log.upid,log.id) )
                        
                        
                        cur.execute("INSERT INTO " + self._commondb + ".dbo.data_routing (logger_id, db_id) VALUES (%s,%s)", (log.id, new_db_id))
                
                    elif res[0]!=new_db:
                        
                        logger_db = res[0]
                           
                        settings.logger.info('current db does not match target db')             
                                 
               
                        #cur.execute("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = " + new_db_schema + ") CREATE SCHEMA " + new_db_schema)
                        '''cur.execute("SELECT * FROM sys.schemas WHERE name = '" + new_db_schema + "'")
                        resCheck = cur.fetchone()
                        if resCheck==None: 
                            cur.execute("CREATE DATABASE " + new_db_schema)
                            self._Connection.commit()
                            settings.logger.info('new schema created')'''
                            
                        settings.logger.info("inserting logger info into datalogger table")         
                        # create 
                        cur.execute("use " + new_db + "; if not exists (SELECT * FROM xxx_datalogger WHERE logger_id=%s) "
                            "INSERT INTO xxx_datalogger (logger_id,upid,creation_date) VALUES(%s,%s,%s) "
                            "ELSE UPDATE xxx_datalogger "
                            "SET upid=IsNull(NULLIF(%s,'None'),upid) "
                            "WHERE logger_id=%s", 
                            (log.id,log.id,log.upid,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),log.upid,log.id) )
                        
                        print 'hey'
                        settings.logger.info('migrating data from RAW_Device_Properties...')
                        
                        cur.execute("SELECT TABLE_NAME FROM "+logger_db+".dbo.xxx_RAW_Device_Properties WHERE DEVICE_NAME='Device_ID' AND TABLE_NAME LIKE '%"+log.id+"%'")
                        listDeviceNames = cur.fetchall()                     
                
                        # move data from tables xxx_RAW_Device-Properties, xxx_Virtual Devices and xxx_Device_Mappings to their analog tables in the newly created plant db schema
                        '''cur.execute("SELECT * INTO " + new_db + ".dbo.xxx_RAW_Device_Properties FROM " + logger_db + ".dbo.xxx_RAW_Device_Properties WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                        cur.execute("SELECT * INTO " + new_db + ".dbo.xxx_Virtual_Devices FROM " + logger_db + ".dbo.xxx_Virtual_Devices WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                        cur.execute("SELECT * INTO " + new_db + ".dbo.xxx_Device_Mappings FROM " + logger_db + ".dbo.xxx_Device_Mappings WHERE TABLE_NAME LIKE '%" + log.id + "%'") '''
                
                        print 'hey ya'
                        cur.execute("INSERT INTO " + new_db + ".dbo.xxx_RAW_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE, DEVICE_UNIT, COLUMN_NAME, DEVICE_VPC, VALUE_TYPE, DEVICE_TRANSMIT) "
                                    "SELECT TABLE_NAME, DEVICE_NAME, DEVICE_VALUE, DEVICE_UNIT, COLUMN_NAME, DEVICE_VPC, VALUE_TYPE, DEVICE_TRANSMIT FROM " + logger_db + ".dbo.xxx_RAW_Device_Properties WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                        cur.execute("INSERT INTO " + new_db + ".dbo.xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, User_Device_Key, Device_Key, State_Key, Minimum_Value, Maximum_Value, Fix_Value, Station_ID, RAW_Name, Unit_String, Virtual_Device_ID, Active) "
                                    "SELECT Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, User_Device_Key, Device_Key, State_Key, Minimum_Value, Maximum_Value, Fix_Value, Station_ID, RAW_Name, Unit_String, Virtual_Device_ID, Active "
                                    "FROM " + logger_db + ".dbo.xxx_Virtual_Devices WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                        cur.execute("INSERT INTO " + new_db + ".dbo.xxx_Device_Mappings SELECT * FROM " + logger_db + ".dbo.xxx_Device_Mappings WHERE TABLE_NAME LIKE '%" + log.id + "%'") 
                        
                        settings.logger.info('length of device list: ' + str(len(listDeviceNames)))
                        for dev_name in listDeviceNames:
                            device_name = dev_name[0]
                            cur.execute("SELECT * INTO " + new_db + ".dbo." + device_name + " FROM " + logger_db + ".dbo." + device_name)
                            cur.execute("DROP TABLE " + logger_db + ".dbo." + device_name)
                            #cur.execute("INSERT INTO " + new_db + ".dbo." + device_name + " SELECT * FROM " + logger_db + ".dbo." + device_name)
                    

                            cur.execute("DELETE FROM " + logger_db + ".dbo.xxx_RAW_Device_Properties WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                            cur.execute("DELETE FROM " + logger_db + ".dbo.xxx_Virtual_Devices WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                            cur.execute("DELETE FROM " + logger_db + ".dbo.xxx_Device_Mappings WHERE TABLE_NAME LIKE '%" + log.id + "%'")
                        
                        cur.execute("UPDATE xxx_data_routing SET db_id=%s", (new_db_id,))
                    else:
                        settings.logger.info("updating logger info in datalogger table")         
                        # create 
                        cur.execute("use " + new_db + "; if not exists (SELECT * FROM xxx_datalogger WHERE logger_id=%s) "
                            "INSERT INTO xxx_datalogger (logger_id,upid,creation_date) VALUES(%s,%s,%s) "
                            "ELSE UPDATE xxx_datalogger "
                            "SET upid=IsNull(NULLIF(%s,'None'),upid) "
                            "WHERE logger_id=%s", 
                            (log.id,log.id,log.upid,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),log.upid,log.id) )
                        
                        
                    self._Connection.commit()
                    settings.logger.info("LoggerDB: INSERT success for logger " + log.id)
                                               
                        
                except Exception as e:
                    
                    print str(e)
                    settings.logger.error("INSERT operation failed for logger with serial " + log.id + " " + str(e))  
                    listFailedInserts.append(log.id)
                    self._Connection.rollback()
                    continue
        return listFailedInserts
    
    def getData(self):
        pass