# -*- coding: utf-8 -*- 
import pymssql


conn = pymssql.connect(server='db01.be4energy.com', user='be4visionadmin', password='cdg4s!fcv')
cur = conn.cursor()

cur.execute('use P17TEST2;')

# adjust xxx_Virtual_Devices table
cur.execute("ALTER TABLE xxx_Virtual_Devices ALTER COLUMN Table_name nvarchar(255)")

# adjust xxx_RAW_Device_Properties table
cur.execute("ALTER TABLE xxx_RAW_Device_Properties ALTER COLUMN DEVICE_VALUE nvarchar(255)")
cur.execute("ALTER TABLE xxx_RAW_Device_Properties ALTER COLUMN COLUMN_NAME nvarchar(100)")
cur.execute("ALTER TABLE xxx_RAW_Device_Properties ALTER COLUMN DEVICE_VPC nvarchar(100)")

# adjust xxx_Last_Data table
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] """)

    
#------- this code is optional depending on the db structure version;
#------- if it's not working you don't need it and you can comment it out
# adjust xxx_Plant_Device table if needed
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)")



conn.commit()
conn.close()