-------------------------------------------------------------------------------------------------------------------------------
-- After InventoryManagement has been published
-------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------
-- InventoryManagement
-------------------------------------------------------------------------------------------------------------------------------
DECLARE @tableName varchar(128) = 'publ_InventoryManagement'
-------------------------------------------------------------------------------------------------------------------------------
-- PartNumber
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementPartNumber' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementPartNumber] varchar(64) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Brand
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementBrand' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementBrand] varchar(16) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Condition
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementCondition' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementCondition] varchar(16) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- UniqueNumber
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementUniqueNumber' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementUniqueNumber] varchar(64) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Description
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementDescription' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementDescription] varchar(1000) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- WarehouseLocationName
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_WarehouseLocationName' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_WarehouseLocationName] varchar(64) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- BinLocation
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementBinLocation' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementBinLocation] varchar(64) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Area
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementArea' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementArea] varchar(32) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Isle
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementIsle' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementIsle] varchar(32) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Bay
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementBay' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementBay] varchar(32) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Shelf
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementShelf' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementShelf] varchar(32) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Position
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementPosition' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementPosition] varchar(32) null
END
GO
-------------------------------------------------------------------------------------------------------------------------------
-- Bin
-------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryManagement' AND
	COLUMN_NAME = 'InventoryManagement_InventoryManagementLocation_InventoryManagementBin' AND 
	CHARACTER_MAXIMUM_LENGTH = -1)
BEGIN
	ALTER TABLE [publ_InventoryManagement] 
		ALTER COLUMN [InventoryManagement_InventoryManagementLocation_InventoryManagementBin] varchar(32) null
END
GO

-------------------------------------------------------------------------------------------------------------------------------
-- Indexes
-------------------------------------------------------------------------------------------------------------------------------


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_InventoryManagement]') AND name = N'idx_publ_InventoryManagement_UniqueNumber')
DROP INDEX [idx_publ_InventoryManagement_UniqueNumber] ON [dbo].[publ_InventoryManagement]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_InventoryManagement]') AND name = N'idx_publ_InventoryManagement_ParentSurfaceItemId')
DROP INDEX [idx_publ_InventoryManagement_ParentSurfaceItemId] ON [dbo].[publ_InventoryManagement]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_InventoryManagement]') AND name = N'idx_publ_InventoryManagement_itemID')
DROP INDEX [idx_publ_InventoryManagement_itemID] ON [dbo].[publ_InventoryManagement]
GO

CREATE UNIQUE NONCLUSTERED INDEX [idx_publ_InventoryManagement_itemID] ON [dbo].[publ_InventoryManagement]
(
	[itemID] 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]
GO
CREATE NONCLUSTERED INDEX [idx_publ_InventoryManagement_ParentSurfaceItemId] ON [dbo].[publ_InventoryManagement]
(
	[ParentSurfaceItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [idx_publ_InventoryManagement_UniqueNumber] ON [dbo].[publ_InventoryManagement]
(
	[InventoryManagement_InventoryManagementLocation_InventoryManagementUniqueNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
