-- Licensed Materials - Property of IBM
-- BI and PM: Mobile
-- (C) Copyright IBM Corp. 2007, 2012
-- US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

-- MOB_DEVICES
CREATE TABLE [MOB_DEVICES] (
	[DEVICE_ID] varchar (100) NOT NULL,
	[PUSH_ID] varchar (100),
	[USER_ID] int NOT NULL,
	[LAST_SYNCED] datetime,
	[REGISTERED] datetime,
	[DEVICE_PROFILE] varchar (200),
	[KEY_TYPE] varchar (20),	
	[KEY_BYTES] varbinary (32),
	[SYNC_METHOD] varchar (20),
	CONSTRAINT [FK_MOB_DEV_UID] FOREIGN KEY([USER_ID]) REFERENCES [MOB_USERS]([USER_ID]) ON DELETE CASCADE,
	PRIMARY KEY ([DEVICE_ID], [USER_ID])
)
CREATE INDEX [IDX_MOB_DEVICES_USER] ON [MOB_DEVICES]([USER_ID]);
CREATE INDEX [IDX_MOB_DEVICES_ID] ON [MOB_DEVICES]([DEVICE_ID]);

INSERT INTO [MOB_DEVICES] ([DEVICE_ID]
      ,[USER_ID]
      ,[DEVICE_PROFILE]
      ,[KEY_TYPE]
      ,[KEY_BYTES]
      ,[SYNC_METHOD])
      SELECT [DEVICE_ID]
      ,[USER_ID]
      ,[DEVICE_PROFILE]
      ,[KEY_TYPE]
      ,[KEY_BYTES]
      ,[SYNC_METHOD]
	FROM [MOB_USERS]
	WHERE [DEVICE_ID] IS NOT NULL AND [DEVICE_ID] <> '';
	
ALTER TABLE [MOB_USERS] DROP COLUMN [DEVICE_PROFILE],[KEY_TYPE],[KEY_BYTES],[SYNC_METHOD],[LAST_LOGIN];