-- Add practice_id to event_template
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'practice_id' AND Object_ID = Object_ID(N'[dbo].[event_template]'))    
ALTER TABLE [dbo].[event_template]
   ADD practice_id varchar(4)

-- Add sequence_id to event_template
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'sequence_id' AND Object_ID = Object_ID(N'[dbo].[event_template]'))    
ALTER TABLE [dbo].[event_template]
   ADD sequence_id uniqueidentifier
GO

-- Drop old_permission column (if exists). This shouldn't be in any production databases.
IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'old_permission' AND Object_ID = Object_ID(N'[dbo].[event_security]'))    
ALTER TABLE [dbo].[event_security]
    DROP COLUMN old_permission
GO

-- Drop Primary key on event security to drop unique_id column as its not necessary
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TABLE_NAME = 'event_security' AND CONSTRAINT_NAME = 'PK_event_security'
    AND TABLE_SCHEMA ='dbo' )
alter table dbo.event_security drop constraint [PK_event_security]
GO

-- Drop the unique_id column
IF EXISTS(SELECT * FROM sys.columns 
    WHERE Name = N'unique_id' AND Object_ID = Object_ID(N'event_security'))
ALTER TABLE [dbo].[event_security] DROP COLUMN [unique_id]
GO