CREATE DATABASE IF NOT EXISTS `smartdata_v1`;

USE `smartdata_v1`;

--
-- Table structure for table `counters`
--
DROP TABLE IF EXISTS `counters`;
CREATE TABLE `counters` (
  `domain` varchar(48) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`domain`),
  KEY `idx_domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
--
-- Table structure for table `series`
--
DROP TABLE IF EXISTS `rows`;
DROP TABLE IF EXISTS `series`;
CREATE TABLE `series` (
<<<<<<< HEAD
  `id`         bigint unsigned NOT NULL AUTO_INCREMENT,
  `version`    tinyint unsigned NOT NULL,
  `unit`       int unsigned NOT NULL,
  `x`          int NOT NULL,
  `y`          int NOT NULL,
  `z`          int NOT NULL,
  `r`          int unsigned NOT NULL,
  `dev`        int unsigned NOT NULL DEFAULT '0',
  `t0`         bigint unsigned NOT NULL,
  `t1`         bigint unsigned NOT NULL,
  `workflow`   int unsigned NOT NULL DEFAULT '0',
  `domain`     varchar(48) NOT NULL DEFAULT 'public',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` char(3) DEFAULT 'OLD',
  `period` bigint(20) DEFAULT 0,
  `event` varchar(300) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `uncertainty` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_series` (`version`,`unit`,`x`,`y`,`z`,`r`,`dev`,`t0`,`t1`,`workflow`,`domain`,`type`,`period`),
  KEY `idx_series_unit` (`unit`),
  KEY `idx_series_domunit` (`domain`,`unit`,`t0`,`t1`),
  KEY `idx_series_t0` (`t0`),
  KEY `idx_series_t1` (`t1`),
  KEY `idx_domain` (`domain`),
  KEY `idx_updated_at` (`updated_at`),
  KEY `idx_domain_updated_at` (`domain`,`updated_at`),
  KEY `idx_domainupdated_at` (`domain`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--
-- Table structure for table `rows`
--
DROP TABLE IF EXISTS `rows`;
CREATE TABLE `rows` (
  `id`         bigint unsigned NOT NULL AUTO_INCREMENT,
  `x`          int NOT NULL,
  `y`          int NOT NULL,
  `z`          int NOT NULL,
  `dev`        int unsigned NOT NULL DEFAULT '0',
  `count`      bigint unsigned NOT NULL DEFAULT '0',
  `row_id`     bigint unsigned NOT NULL,
  `series_id`  bigint unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rows` (`x`,`y`,`z`,`dev`,`row_id`,`series_id`),
  CONSTRAINT `fk_row_series`
    FOREIGN KEY (`series_id`) REFERENCES `series` (`id`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `trackers`
--
DROP TABLE IF EXISTS `trackers`;
CREATE TABLE `trackers` (
  `id`         bigint unsigned NOT NULL AUTO_INCREMENT,
  `version`    tinyint unsigned NOT NULL,
  `unit`       int unsigned NOT NULL,
  `dev`        int unsigned NOT NULL DEFAULT '0',
  `t0`         bigint unsigned NOT NULL,
  `t1`         bigint unsigned NOT NULL,
  `signature`  bigint unsigned NOT NULL,
  `count`      bigint unsigned NOT NULL DEFAULT '0',
  `workflow`   int unsigned NOT NULL DEFAULT '0',
  `domain`     varchar(48) NOT NULL DEFAULT 'public',
  `row_id`     bigint unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` char(3) DEFAULT 'OLD',
  `period` bigint(20) DEFAULT 0,
  `event` varchar(300) DEFAULT NULL,
  `uncertainty` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trackers` (`version`,`unit`,`signature`,`t0`,`t1`,`dev`,`workflow`,`domain`,`row_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `clients`
--
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `certificate` varchar(64) DEFAULT NULL,
  `name` varchar(32) NOT NULL,
  `domain` varchar(48) NOT NULL,
  `level` varchar(2) NOT NULL,
  `enable` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_domain` (`domain`),
  KEY `idx_certificate` (`certificate`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;

DELIMITER //
CREATE TRIGGER `check_level` BEFORE INSERT ON `clients` FOR EACH ROW
BEGIN
  IF NEW.`level` NOT IN ('r', 'w', 'rw') THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = '[table:clients] - `level` column is not valid';
  END IF;
END; //