/*!40101 SET NAMES utf8 */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET SQL_NOTES=0 */;

/* Altering existing tables */

ALTER TABLE `DME_UpdateFile` MODIFY `extraDescription` longtext DEFAULT NULL;

/* Create new tables */

CREATE TABLE IF NOT EXISTS `prov_iphone_cacertificate` (
  `certificateID` int(11) NOT NULL AUTO_INCREMENT,
  `certificate` blob DEFAULT NULL,
  `defaultCA` bit(1) DEFAULT NULL,
  `encryptedkeyPair` blob DEFAULT NULL,
  `subjectdn` varchar(450) DEFAULT NULL,
  PRIMARY KEY (`certificateID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `prov_iphone_enrollment` (
  `enrollmentID` varchar(40) NOT NULL,
  `apnInfo` blob DEFAULT NULL,
  `apnToken` varchar(255) DEFAULT NULL,
  `certificate` blob DEFAULT NULL,
  `challenge` varchar(100) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `deviceID` varchar(50) DEFAULT NULL,
  `lastState` varchar(60) DEFAULT NULL,
  `lastStatusDate` datetime DEFAULT NULL,
  `phoneNumber` varchar(50) DEFAULT NULL,
  `status` blob DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `caCertificate` int(11) DEFAULT NULL,
  PRIMARY KEY (`enrollmentID`),
  KEY `IX_pie_subject` (`subject`),
  KEY `IX_pie_apnToken` (`apnToken`),
  KEY `IX_pie_deviceID` (`deviceID`),
  KEY `IX_pie_lastStatusDate` (`lastStatusDate`),
  CONSTRAINT `FK1C594B47DF152` FOREIGN KEY (`caCertificate`) REFERENCES `prov_iphone_cacertificate` (`certificateID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `prov_iphone_operations` (
  `operationID` int(11) NOT NULL AUTO_INCREMENT,
  `createdDate` datetime DEFAULT NULL,
  `lastStatus` varchar(50) DEFAULT NULL,
  `lastStatusDate` datetime DEFAULT NULL,
  `mdmRequest` longblob DEFAULT NULL,
  `meta` varchar(100) DEFAULT NULL,
  `responseObject` longblob DEFAULT NULL,
  `requestType` varchar(50) DEFAULT NULL,
  `requestUUID` varchar(60) DEFAULT NULL,
  `status` mediumblob DEFAULT NULL,
  `enrollment` varchar(40) DEFAULT NULL, 
  PRIMARY KEY (`operationID`),
  KEY `IX_pio_lastStatusDate` (`lastStatusDate`),
  CONSTRAINT `FK3837383939383` FOREIGN KEY (`enrollment`) REFERENCES `prov_iphone_enrollment` (`enrollmentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 

CREATE TABLE IF NOT EXISTS `pre_cache_connector` ( 
  `terminalID` varchar(50) NOT NULL,
  `function` varchar(128) NOT NULL, 
  `preCacheState` varchar(50) NOT NULL, 
   connectorId varchar(50) NOT NULL, 
 PRIMARY KEY (`terminalID`, `function`)
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8;

UPDATE `DME_System` SET value = '7' WHERE name = 'DATABASE_STRUCTURE_VERSION';