CREATE DATABASE ntpa; -- CHARACTER SET utf8 COLLATE utf8_bin;
USE ntpa;

-- DROP USER 'ntpau'@'localhost';

CREATE USER 'ntpau'@'localhost' IDENTIFIED BY 'xxxx';
GRANT ALL PRIVILEGES ON ntpa.* TO 'ntpau'@'localhost';
FLUSH PRIVILEGES;

-- DROP TABLE hostReading;
-- DROP TABLE peerReading;
-- DROP TABLE host;
-- DROP TABLE peer;

CREATE TABLE host (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30) NOT NULL,
	ip VARCHAR(15) NOT NULL,
	orgId INT
) ENGINE=INNODB;

CREATE TABLE peer (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30) NULL,
	ip VARCHAR(15) NULL,
	orgId INT
) ENGINE=INNODB;

CREATE TABLE peerReading (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	time  TIMESTAMP NOT NULL,
	hostId INT NOT NULL,
	peerId INT NOT NULL,
	last INT NOT NULL,
	reach INT NOT NULL,
	delay DOUBLE NOT NULL,
	offset DOUBLE NOT NULL,
	jitter DOUBLE NOT NULL,
	FOREIGN KEY (hostId) REFERENCES host(id),
	FOREIGN KEY (peerId) REFERENCES peer(id)
) ENGINE=INNODB;

CREATE TABLE hostReading (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	time  TIMESTAMP NOT NULL,
	hostId INT NOT NULL,
	peerId INT NOT NULL,
	offset DOUBLE NOT NULL,
	jitter DOUBLE NOT NULL,
	frequency DOUBLE NOT NULL,
	stability DOUBLE NOT NULL,
	FOREIGN KEY (hostId) REFERENCES host(id),
	FOREIGN KEY (peerId) REFERENCES peer(id)
) ENGINE=INNODB;

CREATE TABLE timeServer (
	id INT NOT NULL PRIMARY KEY,
	stratum INT NOT NULL,
	countryCode CHAR(7) NOT NULL,
	hostName VARCHAR(50) NOT NULL,
	ip VARCHAR(15) NULL,
	ip6 VARCHAR(30) NULL,
	useDns BIT(1) NULL,
	poolMember BIT(1) NULL,
	location VARCHAR(100) NULL,
	displayLocation VARCHAR(60) NOT NULL DEFAULT "Unknown",
	organization VARCHAR(50) NULL,
	coordinates VARCHAR(50) NULL,
	synchronization VARCHAR(255) NULL,
	serviceArea VARCHAR(100) NULL,
	accessPolicy VARCHAR(255) NULL,
	accessDetails VARCHAR(512) NULL,
	notification BIT(1) NULL,
	autoKeyUrl VARCHAR(255) NULL,
	symmetricKeyType VARCHAR(100) NULL,
	symmetricKeyURL VARCHAR(100) NULL,
	serverContact VARCHAR(255) NULL,
	updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	providerPage VARCHAR(100) NULL,
	providerUrl VARCHAR(255) NULL
) ENGINE=INNODB;

CREATE TABLE hostIoReading (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	time  TIMESTAMP NOT NULL,
	hostId INT NOT NULL,
	timeSinceReset INT NOT NULL,
	receiveBuffers INT NOT NULL,
	freeReceiveBuffers INT NOT NULL,
	usedReceiveBuffers INT NOT NULL,
	lowWaterRefills INT NOT NULL,
	droppedPackets INT NOT NULL,
	ignoredPackets INT NOT NULL,
	receivedPackets INT NOT NULL,
	packetsSent INT NOT NULL,
	packetsNotSent INT NOT NULL,
	interruptsHandled INT NOT NULL,
	receivedByInt INT NOT NULL,
	FOREIGN KEY (hostId) REFERENCES host(id)
) ENGINE=INNODB;

COMMIT;
