-- -----------------------------------------------------
-- Table `IOS_DEVICE`
-- -----------------------------------------------------
CREATE TABLE IOS_DEVICE (
  MOBILE_DEVICE_ID VARCHAR(45) NOT NULL,
  APNS_PUSH_TOKEN VARCHAR(100) DEFAULT NULL,
  MAGIC_TOKEN VARCHAR(100) DEFAULT NULL,
  MDM_TOKEN VARCHAR(100) DEFAULT NULL,
  UNLOCK_TOKEN VARCHAR(5000) DEFAULT NULL,
  CHALLENGE_TOKEN VARCHAR(45) DEFAULT NULL,
  DEVICE_INFO VARCHAR(4000) DEFAULT NULL,
  SERIAL VARCHAR(45) DEFAULT NULL,
  PRODUCT VARCHAR(45) DEFAULT NULL,
  IMEI VARCHAR(45) DEFAULT NULL,
  VERSION VARCHAR(45) DEFAULT NULL,
  MAC_ADDRESS VARCHAR(45) DEFAULT NULL,
  DEVICE_NAME VARCHAR(100) DEFAULT NULL,
  ICCID VARCHAR(45) DEFAULT NULL,
  LATITUDE VARCHAR(45) DEFAULT NULL,
  LONGITUDE VARCHAR(45) DEFAULT NULL,
  CONSTRAINT PK_IOS_DEVICE PRIMARY KEY (MOBILE_DEVICE_ID)
)
/

-- -----------------------------------------------------
-- Table `IOS_FEATURE`
-- -----------------------------------------------------
CREATE TABLE IOS_FEATURE (
  ID INT NOT NULL,
  CODE VARCHAR(45) NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  DESCRIPTION VARCHAR(200) NULL,
  CONSTRAINT PK_IOS_FEATURE PRIMARY KEY (ID)
)
/

-- Sequence `IOS_FEATURE_ID_INC_SEQ`
-- -----------------------------------------------------
CREATE SEQUENCE IOS_FEATURE_ID_INC_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

-- -----------------------------------------------------
-- Trigger `IOS_FEATURE_ID_INC_TRIG`
-- -----------------------------------------------------
CREATE OR REPLACE TRIGGER IOS_FEATURE_ID_INC_TRIG
BEFORE INSERT
ON IOS_FEATURE
REFERENCING NEW AS NEW
FOR EACH ROW
  BEGIN
    SELECT IOS_FEATURE_ID_INC_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END;
/

-- -----------------------------------------------------
-- Table `IOS_DEVICE_PROFILE`
-- -----------------------------------------------------
CREATE TABLE IOS_DEVICE_PROFILE (
  ID NUMBER(10) NOT NULL,
  OPERATION_ID VARCHAR2(10) NOT NULL,
  OPERATION_CODE VARCHAR2(1000) NULL,
  PROFILE_ID VARCHAR2(1000) NULL,
  DEVICE_IDENTIFICATION VARCHAR2(300) DEFAULT NULL,
  PROPERTIES CLOB DEFAULT NULL,
  PRIMARY KEY (ID)
);

-- Generate ID using sequence and trigger
CREATE SEQUENCE IOS_DEVICE_PROFILE_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER IOS_DEVICE_PROFILE_seq_tr
 BEFORE INSERT ON IOS_DEVICE_PROFILE FOR EACH ROW
 WHEN (NEW.ID IS NULL)
BEGIN
 SELECT IOS_DEVICE_PROFILE_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

-- -----------------------------------------------------
-- Table `IOS_ENROLLMENT_TOKEN`
-- -----------------------------------------------------
CREATE TABLE IOS_ENROLLMENT_TOKEN (
  TENANT_DOMAIN VARCHAR(255) NOT NULL,
  CHALLENGE_TOKEN VARCHAR(45) NOT NULL,
  DEVICE_ID VARCHAR(45) DEFAULT NULL,
  ACCESS_TOKEN VARCHAR(100) NOT NULL,
  REFRESH_TOKEN VARCHAR(100) NOT NULL,
  USERNAME VARCHAR(255) NOT NULL,
  CLIENT_CREDENTIALS VARCHAR(255) NOT NULL,
  AGENT_AVAILABLE VARCHAR(10) NOT NULL,
  CONSTRAINT UNIQUE_IOS_ENROLLMENT_TOKEN UNIQUE (TENANT_DOMAIN, CHALLENGE_TOKEN),
  CONSTRAINT PK_IOS_ENROLLMENT_TOKEN PRIMARY KEY (CHALLENGE_TOKEN)
);

-- -----------------------------------------------------
-- Table `IOS_DEP_PROFILE`
-- -----------------------------------------------------
CREATE TABLE IOS_DEP_PROFILE (
  ID NUMBER(10) NOT NULL,
  UUID VARCHAR2(100) DEFAULT NULL,
  PROFILE_NAME VARCHAR2(200) DEFAULT NULL,
  TENANT_DOMAIN VARCHAR2(255) NOT NULL,
  PROFILE_CONTENT CLOB DEFAULT NULL,
  UNIQUE (UUID),
  PRIMARY KEY (ID)
);

-- Generate ID using sequence and trigger
CREATE SEQUENCE IOS_DEP_PROFILE_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER IOS_DEP_PROFILE_seq_tr
 BEFORE INSERT ON IOS_DEP_PROFILE FOR EACH ROW
 WHEN (NEW.ID IS NULL)
BEGIN
 SELECT IOS_DEP_PROFILE_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

-- -----------------------------------------------------
-- Table `IOS_PRE_ENROLLED_DEVICE`
-- -----------------------------------------------------
CREATE TABLE IOS_PRE_ENROLLED_DEVICE (
  ID NUMBER(10) NOT NULL,
  SERIAL VARCHAR2(45) DEFAULT NULL,
  DEVICE_IDENTIFIER VARCHAR2(45) DEFAULT NULL,
  USERNAME VARCHAR2(255),
  TENANT_DOMAIN VARCHAR2(255) NOT NULL,
  STATUS VARCHAR2(100) DEFAULT NULL,
  DEP_PROFILE_ID NUMBER(10) DEFAULT NULL,
  PROFILE_ASSIGN_TIME TIMESTAMP(0) DEFAULT NULL,
  PROFILE_PUSH_TIME TIMESTAMP(0) DEFAULT NULL,
  DEVICE_ASSIGNED_TIME TIMESTAMP(0) DEFAULT NULL,
  DEVICE_ASSIGNED_BY VARCHAR2(100) DEFAULT NULL,
  NEED_BASIC_AUTH NUMBER(10) DEFAULT 0,
  IS_AGENT_REQUIRED NUMBER(10) DEFAULT 0,
  OS VARCHAR2(45) DEFAULT NULL,
  DEVICE_FAMILY VARCHAR2(45) DEFAULT NULL,
  DEVICE_MODEL VARCHAR2(45) DEFAULT NULL,
  DESCRIPTION VARCHAR2(200) DEFAULT NULL,
  COLOR VARCHAR2(200) DEFAULT NULL,
  UNIQUE (SERIAL),
  PRIMARY KEY (ID),
  CONSTRAINT fk_IOS_PRE_ENROLLED_DEVICE_IOS_DEP_PROFILE
    FOREIGN KEY (DEP_PROFILE_ID)
    REFERENCES IOS_DEP_PROFILE (ID)
);

-- Generate ID using sequence and trigger
CREATE SEQUENCE IOS_PRE_ENROLLED_DEVICE_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER IOS_PRE_ENROLLED_DEVICE_seq_tr
 BEFORE INSERT ON IOS_PRE_ENROLLED_DEVICE FOR EACH ROW
 WHEN (NEW.ID IS NULL)
BEGIN
 SELECT IOS_PRE_ENROLLED_DEVICE_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
