CREATE TABLE IF NOT EXISTS _Dates (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  time TIMESTAMP NOT NULL
);


INSERT INTO _Dates (time)
  SELECT *
  FROM
    ((SELECT date_add(TIMESTAMP('2016-11-01'), interval (t6.i * 1000000 + t5.i * 100000 +t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) * 30 MINUTE) selected_date
     FROM
       (SELECT 0 i
      UNION SELECT 1
      UNION SELECT 2
      UNION SELECT 3
      UNION SELECT 4
      UNION SELECT 5
      UNION SELECT 6
      UNION SELECT 7
      UNION SELECT 8
      UNION SELECT 9)
       t0,
       (SELECT 0 i
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9)
       t1,
       (SELECT 0 i
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9)
       t2,
       (SELECT 0 i
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9)
       t3,
       (SELECT 0 i
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9)
       t4,
       (SELECT 0 i
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9)
       t5,
       (SELECT 0 i
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9)
       t6
    ) v)
  WHERE (selected_date BETWEEN '2016-01-01' AND  now()) AND ( selected_date >= '2016-03-27 03:00:00' OR selected_date < '2016-03-27 02:00:00');


## TABELLE DI LOGGING

CREATE TABLE IF NOT EXISTS `Log` (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `userId` INT unsigned not null,
 entityName varchar(255) null,
 stringId varchar(128) null,
 eventName varchar(255) not null,
 eventValue varchar(255) null,
  actionName varchar(255) null,
 backtrace varchar(255) null,
 time TIMESTAMP not null DEFAULT NOW(),
  CONSTRAINT `fk_log_user_constraint` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);

##tabella per la gestione degli eventi

CREATE TABLE `Events` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `event` VARCHAR(50) NOT NULL,
  `eventListener` VARCHAR(100) NOT NULL,
  `isActive` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `priority` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
  `isAsync` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  INDEX `event` (`event`),
  INDEX `isStopped` (`isActive`)
)
  COMMENT='gestore degli eventi'
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
  AUTO_INCREMENT=5
;

Create TABLE IF NOT EXISTS `StatisticsHeader` (
 `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 `tableName` VARCHAR(50) NOT NULL,
  `groupDataType` ENUM('SUM', 'MEDIUM', 'SUM-PREVIOUS') NOT NULL,
 `dateGroup` ENUM('HOUR', 'DAY', 'WEEK', 'MONTH', 'YEAR') not null,
 `dateStart` TIMESTAMP not null,
 `dateEnd` TIMESTAMP not null,
 `keyField` varchar(80) NOT NULL,
 `graphUnitName` varchar(80) NOT NULL,
 `graphValueName` varchar(80) NOT NULL,
 `fullScaleValue` BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS `StatisticsRow` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `idStatisticsHeader` INT UNSIGNED,
  `idTime` BIGINT UNSIGNED NULL,
  `time` timestamp NULL,
  `value` DOUBLE NOT NULL,
  CONSTRAINT `FK1_stRows_toStHead` FOREIGN KEY (`idStatisticsHeader`) REFERENCES `StatisticsHeader` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);