CREATE TABLE IF NOT EXISTS `sip_msg` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  `type` tinyint unsigned NOT NULL,
  `ip_src` int unsigned DEFAULT NULL,
  `ip_dst` int unsigned DEFAULT NULL,
  `ip_src_country_code` varchar(5) DEFAULT NULL,
  `ip_dst_country_code` varchar(5) DEFAULT NULL,
  `port_src` smallint unsigned DEFAULT NULL,
  `port_dst` smallint unsigned DEFAULT NULL,
  `number_src` varchar(255) DEFAULT NULL,
  `number_dst` varchar(255) DEFAULT NULL,
  `number_src_country_code` varchar(5) DEFAULT NULL,
  `number_dst_country_code` varchar(5) DEFAULT NULL,
  `domain_src` varchar(255) DEFAULT NULL,
  `domain_dst` varchar(255) DEFAULT NULL,
  `ua_src_id` int unsigned DEFAULT NULL,
  `ua_dst_id` int unsigned DEFAULT NULL,
  `callername` varchar(255) DEFAULT NULL,
  `callid` varchar(255) DEFAULT NULL,
  `cseq` int unsigned DEFAULT NULL,
  `request_id_content_type` mediumint unsigned DEFAULT NULL,
  `request_content_length` mediumint unsigned DEFAULT NULL,
  `request_content` mediumtext,
  `response_id_content_type` mediumint unsigned DEFAULT NULL,
  `response_content_length` mediumint DEFAULT NULL,
  `response_content` mediumtext,
  `response_number` smallint unsigned DEFAULT NULL,
  `response_id` mediumint unsigned DEFAULT NULL,
  `time_us` bigint unsigned DEFAULT NULL,
  `request_repetition` smallint unsigned DEFAULT NULL,
  `request_time` datetime DEFAULT NULL,
  `request_time_us` int unsigned DEFAULT NULL,
  `response_time` datetime DEFAULT NULL,
  `response_time_us` int unsigned DEFAULT NULL,
  `response_duration_ms` int unsigned DEFAULT NULL,
  `qualify_ok` tinyint unsigned DEFAULT NULL,
  `id_sensor` smallint unsigned DEFAULT NULL,
  `vlan` smallint DEFAULT NULL,
  `spool_index` tinyint unsigned DEFAULT NULL,
  `flags` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`,`time`),
  KEY `type` (`type`),
  KEY `time` (`time`),
  KEY `ip_src` (`ip_src`),
  KEY `ip_dst` (`ip_dst`),
  KEY `number_src` (`number_src`),
  KEY `number_dst` (`number_dst`),
  KEY `domain_src` (`number_src`),
  KEY `domain_dst` (`number_dst`),
  KEY `ua_src_id` (`ua_src_id`),
  KEY `ua_dst_id` (`ua_dst_id`),
  KEY `callername` (`callername`),
  KEY `callid` (`callid`),
  KEY `cseq` (`cseq`),
  KEY `response_number` (`response_number`),
  KEY `response_id` (`response_id`),
  KEY `vlan` (`vlan`),
  KEY `id_sensor` (`id_sensor`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `cdr_stat_values` (
  `from_time` datetime DEFAULT NULL,
  `addr` int unsigned NOT NULL,
  `sensor_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_counter` smallint unsigned DEFAULT NULL,
  `count_all` int unsigned DEFAULT NULL,
  `count_connected` int unsigned DEFAULT NULL,
  `count_lsr_3` int unsigned DEFAULT NULL,
  `count_lsr_4` int unsigned DEFAULT NULL,
  `count_lsr_5` int unsigned DEFAULT NULL,
  `count_lsr_6` int unsigned DEFAULT NULL,
  `cc_avg` int unsigned DEFAULT NULL,
  `cc_min` int unsigned DEFAULT NULL,
  `cc_max` int unsigned DEFAULT NULL,
  `cps_avg` int unsigned DEFAULT NULL,
  `cps_min` int unsigned DEFAULT NULL,
  `cps_max` int unsigned DEFAULT NULL,
  `minutes` double DEFAULT NULL,
  `asr` double DEFAULT NULL,
  `acd` double DEFAULT NULL,
  `ner` double DEFAULT NULL,
  `seer` double DEFAULT NULL,
  `mos_avg` double DEFAULT NULL,
  `mos_perc95` double DEFAULT NULL,
  `mos_perc99` double DEFAULT NULL,
  `packet_loss_avg` double DEFAULT NULL,
  `packet_loss_perc95` double DEFAULT NULL,
  `packet_loss_perc99` double DEFAULT NULL,
  `jitter_avg` double DEFAULT NULL,
  `jitter_perc95` double DEFAULT NULL,
  `jitter_perc99` double DEFAULT NULL,
  `delay_avg` double DEFAULT NULL,
  `delay_perc95` double DEFAULT NULL,
  `delay_perc99` double DEFAULT NULL,
  `price_customer` double DEFAULT NULL,
  `price_operator` double DEFAULT NULL,
  `cc_source_data` mediumtext,
  `cps_source_data` mediumtext,
  `asr_source_data` mediumtext,
  `acd_source_data` mediumtext,
  `ner_source_data` mediumtext,
  `seer_source_data` mediumtext,
  `mos_source_data` mediumtext,
  `packet_loss_source_data` mediumtext,
  `jitter_source_data` mediumtext,
  `delay_source_data` mediumtext,
  UNIQUE KEY `comb_1` (`from_time`,`addr`,`sensor_id`,`created_at`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `cdr_stat_values_dst` (
  `from_time` datetime DEFAULT NULL,
  `addr` int unsigned NOT NULL,
  `sensor_id` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_counter` smallint unsigned DEFAULT NULL,
  `count_all` int unsigned DEFAULT NULL,
  `count_connected` int unsigned DEFAULT NULL,
  `count_lsr_3` int unsigned DEFAULT NULL,
  `count_lsr_4` int unsigned DEFAULT NULL,
  `count_lsr_5` int unsigned DEFAULT NULL,
  `count_lsr_6` int unsigned DEFAULT NULL,
  `cc_avg` int unsigned DEFAULT NULL,
  `cc_min` int unsigned DEFAULT NULL,
  `cc_max` int unsigned DEFAULT NULL,
  `cps_avg` int unsigned DEFAULT NULL,
  `cps_min` int unsigned DEFAULT NULL,
  `cps_max` int unsigned DEFAULT NULL,
  `minutes` double DEFAULT NULL,
  `asr` double DEFAULT NULL,
  `acd` double DEFAULT NULL,
  `ner` double DEFAULT NULL,
  `seer` double DEFAULT NULL,
  `mos_avg` double DEFAULT NULL,
  `mos_perc95` double DEFAULT NULL,
  `mos_perc99` double DEFAULT NULL,
  `packet_loss_avg` double DEFAULT NULL,
  `packet_loss_perc95` double DEFAULT NULL,
  `packet_loss_perc99` double DEFAULT NULL,
  `jitter_avg` double DEFAULT NULL,
  `jitter_perc95` double DEFAULT NULL,
  `jitter_perc99` double DEFAULT NULL,
  `delay_avg` double DEFAULT NULL,
  `delay_perc95` double DEFAULT NULL,
  `delay_perc99` double DEFAULT NULL,
  `price_customer` double DEFAULT NULL,
  `price_operator` double DEFAULT NULL,
  `cc_source_data` mediumtext,
  `cps_source_data` mediumtext,
  `asr_source_data` mediumtext,
  `acd_source_data` mediumtext,
  `ner_source_data` mediumtext,
  `seer_source_data` mediumtext,
  `mos_source_data` mediumtext,
  `packet_loss_source_data` mediumtext,
  `jitter_source_data` mediumtext,
  `delay_source_data` mediumtext,
  UNIQUE KEY `comb_1` (`from_time`,`addr`,`sensor_id`,`created_at`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `rtp_stat` (
  `id_sensor` smallint unsigned NOT NULL,
  `time` datetime NOT NULL,
  `saddr` int unsigned NOT NULL,
  `mosf1_min` tinyint unsigned NOT NULL,
  `mosf1_avg` tinyint unsigned NOT NULL,
  `mosf2_min` tinyint unsigned NOT NULL,
  `mosf2_avg` tinyint unsigned NOT NULL,
  `mosAD_min` tinyint unsigned NOT NULL,
  `mosAD_avg` tinyint unsigned NOT NULL,
  `jitter_max` smallint unsigned NOT NULL,
  `jitter_avg` smallint unsigned NOT NULL,
  `loss_max_mult10` smallint unsigned NOT NULL,
  `loss_avg_mult10` smallint unsigned NOT NULL,
  `counter` mediumint unsigned NOT NULL,
  PRIMARY KEY (`time`,`saddr`,`id_sensor`),
  KEY `time` (`time`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `log_sensor` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  `ID_parent` bigint unsigned DEFAULT NULL,
  `id_sensor` smallint unsigned DEFAULT NULL,
  `type` enum('debug','info','notice','warning','error','critical','alert','emergency') DEFAULT NULL,
  `confirmed` tinyint(1) DEFAULT NULL,
  `subject` text,
  `message` text,
  PRIMARY KEY (`ID`,`time`),
  KEY `time` (`time`),
  KEY `ID_parent` (`ID_parent`),
  KEY `id_sensor` (`id_sensor`)
) ENGINE=InnoDB;
