CREATE TABLE dozmod (
  report bigint(20) NOT NULL,
  days bigint(20) NOT NULL,
  vms_total bigint(20) NOT NULL,
  vms_new bigint(20) NOT NULL,
  vms_updated bigint(20) NOT NULL,
  vms_valid bigint(20) NOT NULL,
  lectures_total bigint(20) NOT NULL,
  lectures_new bigint(20) NOT NULL,
  lectures_updated bigint(20) NOT NULL,
  lectures_valid bigint(20) NOT NULL,
  users_total bigint(20) NOT NULL,
  users_organizations bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE machine (
  report bigint(20) NOT NULL,
  days bigint(20) NOT NULL,
  property varchar(30) COLLATE utf8_bin NOT NULL,
  value varchar(200) COLLATE utf8_bin NOT NULL,
  count bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE perLocation (
  report bigint(20) NOT NULL,
  days bigint(20) NOT NULL,
  locationname varchar(40) COLLATE utf8_bin NOT NULL,
  totalTime bigint(20) NOT NULL,
  totalOffTime bigint(20) NOT NULL,
  totalSessionTime bigint(20) NOT NULL,
  totalStandbyTime bigint(20) NOT NULL,
  longSessions bigint(20) NOT NULL,
  shortSessions bigint(20) NOT NULL,
  totalIdleTime bigint(20) NOT NULL,
  medianSessionLength bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE perVM (
  report bigint(20) NOT NULL,
  days bigint(20) NOT NULL,
  vm varchar(40) COLLATE utf8_bin NOT NULL,
  sessions bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE reports (
  id bigint(20) NOT NULL,
  date date NOT NULL,
  ip varchar(20) COLLATE utf8_bin NOT NULL,
  version varchar(200) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE names (
  ip varchar(20) COLLATE utf8_bin NOT NULL,
  name varchar(200) COLLATE utf8_bin NOT NULL,
  testserver tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE server (
  report bigint(20) NOT NULL,
  cpuCount bigint(20) NOT NULL,
  cpuModel varchar(200) COLLATE utf8_bin NOT NULL,
  uptime bigint(20) NOT NULL,
  memTotal bigint(20) NOT NULL,
  memFree bigint(20) NOT NULL,
  swapTotal bigint(20) NOT NULL,
  swapUsed bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE timeframe (
  report bigint(20) NOT NULL,
  days bigint(20) NOT NULL,
  tsFrom datetime NOT NULL,
  tsTo datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE total (
  report bigint(20) NOT NULL,
  days bigint(20) NOT NULL,
  totalTime bigint(20) NOT NULL,
  totalOffTime bigint(20) NOT NULL,
  totalSessionTime bigint(20) NOT NULL,
  totalStandbyTime bigint(20) NOT NULL,
  longSessions bigint(20) NOT NULL,
  shortSessions bigint(20) NOT NULL,
  totalIdleTime bigint(20) NOT NULL,
  medianSessionLength bigint(20) NOT NULL,
  uniqueUsers bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE VIEW reports_newest AS
	SELECT r.id AS id, r.date AS date, r.ip AS ip, r.version AS version
	FROM (
		reports r
		JOIN (
			SELECT reports.ip AS ip, max(reports.date) AS date
			FROM reports
			GROUP BY reports.ip
		) rm
		ON (((r.ip = rm.ip) and (r.date = rm.date)))
	);

CREATE TABLE `basesystem` (
  `report` bigint(20) NOT NULL,
  `days` bigint(20) NOT NULL,
  `system` varchar(50) COLLATE utf8mb3_bin NOT NULL,
  `count` bigint(20) NOT NULL,
  PRIMARY KEY (`report`,`days`,`system`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

ALTER TABLE dozmod
  ADD PRIMARY KEY (report,days);

ALTER TABLE machine
  ADD PRIMARY KEY (report,days,property,value);

ALTER TABLE perLocation
  ADD PRIMARY KEY (report,days,locationname);

ALTER TABLE perVM
  ADD PRIMARY KEY (report,days,vm);

ALTER TABLE reports
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY date_ip (date,ip) USING BTREE;

ALTER TABLE server
  ADD PRIMARY KEY (report);

ALTER TABLE names
  ADD PRIMARY KEY (ip);

ALTER TABLE timeframe
  ADD PRIMARY KEY (report,days);

ALTER TABLE total
  ADD PRIMARY KEY (report,days);

ALTER TABLE reports
  MODIFY id bigint(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE dozmod
  ADD CONSTRAINT dozmod_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE machine
  ADD CONSTRAINT machine_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE perLocation
  ADD CONSTRAINT perLocation_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE perVM
  ADD CONSTRAINT perVM_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE server
  ADD CONSTRAINT server_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE timeframe
  ADD CONSTRAINT timeframe_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE total
  ADD CONSTRAINT total_report_id FOREIGN KEY (report) REFERENCES reports (id) ON DELETE CASCADE ON UPDATE CASCADE;