/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

l_create_flag varchar2(1);
l_alter_flag  varchar2(1);

BEGIN --
  BEGIN -- Create
    SELECT 'Y'
     INTO l_create_flag
     FROM dual
     WHERE EXISTS
              (SELECT 'true'
                FROM all_tab_columns
                WHERE table_name = 'XXAS_USER_DEVICES' );

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE NOT EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                     WHERE table_name = 'XXAS_USER_DEVICES'
                     AND column_name = 'REGISTRATION_GEO');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN
	     EXECUTE IMMEDIATE 'ALTER TABLE XXAS_USER_DEVICES DROP COLUMN registration_geo';
    END; -- Alter
	
	
    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                     WHERE table_name = 'XXAS_USER_DEVICES'
                     AND column_name = 'UI_VERSION');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_USER_DEVICES ADD (ui_version   varchar2(400))';
    END; -- Alter
    
  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE XXAS_USER_DEVICES
						  (device_id               varchar2(400)       NOT NULL PRIMARY KEY
						  ,user_id                 number              NOT NULL
						  ,platform                varchar2(400)       NOT NULL
						  ,device_name             varchar2(400)
						  ,device_model            varchar2(400)
						  ,device_version          varchar2(400)
						  ,device_uuid             varchar2(400)
						  ,target_arn              varchar2(400)
						  ,active                  varchar2(1)
						  ,arn_attempts            number
						  ,last_attempt            date
						  ,last_error              varchar2(4000)
						  ,ui_version              varchar2(400)
						  ,last_known_login        date
						  ,creation_date           date                NOT NULL
						  ,created_by              number              NOT NULL
						  ,last_update_date        date                NOT NULL
						  ,last_updated_by         number              NOT NULL
						  ,last_update_login       number              NOT NULL)';
            
        EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_USER_DEVICES TO &1 WITH GRANT OPTION';   
  END;    -- Create  
  
END;

/

COMMIT; 
EXIT;