-- 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
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_DEVICES (
		DEVICE_ID varchar (100) NOT NULL,
		PUSH_ID varchar (100) NULL,
		USER_ID int NOT NULL,
		LAST_SYNCED timestamp NULL,
		REGISTERED timestamp NULL,
		DEVICE_PROFILE varchar (200) NULL,
		KEY_TYPE varchar (20) NULL,	
		KEY_BYTES blob NULL,
		SYNC_METHOD varchar (20) NULL,
		CONSTRAINT FK_MOB_DEV_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE,
		CONSTRAINT PK_MOB_DEVICES PRIMARY KEY (DEVICE_ID, USER_ID)
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_DEVICES_USER ON MOB_DEVICES(USER_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_DEVICES_ID ON MOB_DEVICES(DEVICE_ID)';
	EXECUTE IMMEDIATE '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';
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_USERS DROP (DEVICE_PROFILE, KEY_TYPE, KEY_BYTES, SYNC_METHOD, LAST_LOGIN)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/