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


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

cur.execute('USE P1803010_1;')

# adjust xxx_Virtual_Devices table
cur.execute("ALTER TABLE xxx_Virtual_Devices ALTER COLUMN Table_name nvarchar(255)")
cur.execute("""IF COL_LENGTH('dbo.xxx_Virtual_Devices', 'Virtual_Device_ID') IS NULL
               BEGIN ALTER TABLE dbo.xxx_Virtual_Devices ADD Virtual_Device_ID varchar(64) NULL END
            """)

cur.execute("""CREATE NONCLUSTERED INDEX [IX_xxx_Virtual_Devices_Device_Class_2] ON [dbo].[xxx_Virtual_Devices] 
                ([Device_Class_2] 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]"""
           )

# 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)")

conn.commit()

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


cur.execute("""--DROP INDEX [Table_Name_IDX] ON [dbo].[xxx_Last_Errors] WITH ( ONLINE = OFF );
            ALTER TABLE xxx_Last_Errors ALTER COLUMN Table_Name nvarchar(255);
            CREATE NONCLUSTERED INDEX [Table_Name_IDX] ON [dbo].[xxx_Last_Errors]([Table_Name] ASC)""")

# adjust xxx_active alarm table
cur.execute("""ALTER TABLE dbo.xxx_Active_Alarms ALTER COLUMN Alarm_Source_Table nvarchar(255)
               ALTER TABLE dbo.xxx_Non_Active_Alarms ALTER COLUMN Alarm_Source_Table nvarchar(255)
               ALTER TABLE dbo.xxx_Plant_Device_Properties ALTER COLUMN Value nvarchar(200)""")


# adjust Alarm tables
cur.execute("""CREATE UNIQUE NONCLUSTERED INDEX [st_ast_at_as] ON [dbo].[xxx_Active_Alarms] 
(
    [Start_Date_and_time] ASC,
    [Alarm_Source_Table] ASC,
    [Alarm_Source_Detail] ASC,
    [Alarm_Text_KEY] ASC,
    [Alarm_State] 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] """)



# try:
#     cur.execute("""CREATE NONCLUSTERED INDEX [table_name_idx] ON [dbo].[xxx_RAW_Device_Properties] 
#     (
#         [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]""")
# except Exception, err:
#      print err
#  
#  
# try:
#     cur.execute("""CREATE NONCLUSTERED INDEX [device_name_idx] ON [dbo].[xxx_RAW_Device_Properties] 
#     (
#         [DEVICE_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]""")
# except Exception, err:
#      print err


#------- 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()