/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


/* Creating new tables */

SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS`dm_ddf` (
  `ddfID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `fileName` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `xml` longtext DEFAULT NULL,
  `groupName` varchar(100) DEFAULT NULL,
  `changeDate` datetime NOT NULL,
  PRIMARY KEY (`ddfID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS `dm_ddf_conf` (
  `ddfConfID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `groupName` varchar(100) DEFAULT NULL,
  `changeDate` datetime NOT NULL,
  `ddfID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ddfConfID`),
  CONSTRAINT `FK235C92D32CDC2353` FOREIGN KEY (`ddfID`) REFERENCES `dm_ddf` (`ddfID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS `dm_ddf_values` (
  `ddfValueID` int(11) NOT NULL AUTO_INCREMENT,
  `value` longblob DEFAULT NULL,
  `priority` int(11) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `path` varchar(1024) NOT NULL,
  `format` varchar(50) DEFAULT NULL,
  `operation` varchar(50) DEFAULT NULL,
  `ddfConfID` int(11) NOT NULL,
  PRIMARY KEY (`ddfValueID`),
  CONSTRAINT `FKDE2951114755E0BB` FOREIGN KEY (`ddfConfID`) REFERENCES `dm_ddf_conf` (`ddfConfID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS `notificationSubscription` (
  `subscriptionID` varchar(100) NOT NULL,
  `terminalID` varchar(50) NOT NULL,
  `userID` varchar(50) NOT NULL,
  `resourceID` int(11) NOT NULL,
  `connectorID` varchar(50) NOT NULL,
  `keepaliveTimestamp` datetime NOT NULL,
  `daySchemeID` int(11) NOT NULL,
  PRIMARY KEY (`subscriptionID`),
  KEY `IX_notificationSubscription__terminalID__resourceID` (`terminalID`,`resourceID`),
  KEY `IX_notificationSubscription__created` (`keepaliveTimestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS `dm_device_tree` (
  `deviceID` varchar(50) NOT NULL,
  `nodeTree` longblob DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  PRIMARY KEY (`deviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS `dm_persisted_tree_structure` (
  `jobID` varchar(255) NOT NULL,
  `updatedDate` datetime NOT NULL,
  `treeStructure` longblob NOT NULL,
  PRIMARY KEY (`jobID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `dm_path_exclusions` (
  `pathExclusionID` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(255) NOT NULL,
  PRIMARY KEY (`pathExclusionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE IF NOT EXISTS `Server` (
  `serverId` varchar(128) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`serverId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `RrdEntry` (
  `rrdEntryId` int(11) NOT NULL AUTO_INCREMENT,
  `data` blob DEFAULT NULL,
  `statisticKey` varchar(45) NOT NULL,
  `connectorId` varchar(50),
  `serverId` varchar(128),
  PRIMARY KEY (`rrdEntryId`),
  KEY `IX_RrdEntry_serverId` (`serverId`),
  KEY `IX_RrdEntry_connectorId` (`connectorId`),
  CONSTRAINT `IX_RrdEntry_serverId` FOREIGN KEY (`serverId`) REFERENCES `Server` (`serverId`),
  CONSTRAINT `IX_RrdEntry_connectorId` FOREIGN KEY (`connectorId`) REFERENCES `Connector` (`connectorId`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


/* Remove obsolete tables */
DROP TABLE IF EXISTS `dm_operation_dm_operation`;


/* Altering existing tables */

ALTER TABLE `dm_operation` ADD FOREIGN KEY `FK71208531D328DF2F` (`parent_operationID`) REFERENCES `dm_operation` (`operationID`);

ALTER TABLE `dme_keystore_emails` MODIFY `alias` varchar(255) NOT NULL;
ALTER TABLE `dme_keystore_keys` MODIFY `alias` varchar(255) NOT NULL;

ALTER TABLE `settingsHistory` MODIFY `value` varchar(2048) NOT NULL;
ALTER TABLE `defaultSettings` MODIFY `value` varchar(2048) NOT NULL;
ALTER TABLE `settings` MODIFY `value` varchar(2048) NOT NULL;
ALTER TABLE `settingsType` MODIFY `options` varchar(1024) DEFAULT NULL;
ALTER TABLE `ConnectorProperty` MODIFY `propertyValue` varchar(2048);

/* Adding columns to existing tables */

ALTER TABLE `terminalRessourceNotification` ADD `lastSync` datetime DEFAULT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `lastNotification` datetime DEFAULT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `lastSubscriptionWatermark` varchar(60) DEFAULT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `retries` int(11) DEFAULT 0 NOT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `outstanding` int(11) DEFAULT 0 NOT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `nextScanServer` varchar(200) DEFAULT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `checkSubscriptionEvent` bit DEFAULT 0 NOT NULL;
ALTER TABLE `terminalRessourceNotification` ADD `errorMessage` varchar(255) DEFAULT NULL;

ALTER TABLE `dayScheme` ADD `subscribed` bit(1) DEFAULT 0 NOT NULL;
ALTER TABLE `dm_dme_jobs` ADD `groupID` varchar(255) DEFAULT NULL;
ALTER TABLE `DME_UpdateFile` ADD `listed` bit DEFAULT 1 NOT NULL;
ALTER TABLE `Connector` ADD `revision` int(11) DEFAULT NULL;

-- Make certain collation is uft8 for the keystore certs table
ALTER TABLE dme_keystore_certs  CONVERT TO CHARACTER SET utf8;
 
UPDATE `DME_System` SET value = '5' WHERE name = 'DATABASE_STRUCTURE_VERSION';

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;