-- 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 YEAR TO SECOND,
	REGISTERED datetime YEAR TO SECOND,
	DEVICE_PROFILE varchar (200),
	KEY_TYPE varchar (20),	
	KEY_BYTES blob (32),
	SYNC_METHOD varchar (20),
	FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_DEV_UID,
	PRIMARY KEY (DEVICE_ID, USER_ID)
);

-- CREATE INDEX IDX_MOB_DEVICES_USER ON MOB_DEVICES(USER_ID);  -- already exists
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 (DEVICE_PROFILE,KEY_TYPE,KEY_BYTES,SYNC_METHOD,LAST_LOGIN);