DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS interfaces;
DROP TABLE IF EXISTS hosts;
DROP TABLE IF EXISTS pools;
DROP TABLE IF EXISTS dns_records;
DROP TABLE IF EXISTS networks;
DROP TABLE IF EXISTS sites;

CREATE TABLE sites(
	id int(12) unsigned NOT NULL auto_increment,
	name varchar(64) NOT NULL DEFAULT 'Default site',
	domain varchar(64) NOT NULL DEFAULT 'example.com',
	PRIMARY KEY ( id )
);

CREATE TABLE networks(
	id int(12) unsigned NOT NULL auto_increment,
	site_id int(12) unsigned NOT NULL,
	name varchar(64) NOT NULL DEFAULT 'Main network',
	subdomain_name varchar(16) NOT NULL DEFAULT 'hq',
	ipv4_subnet int(8) unsigned DEFAULT NULL,
	ipv4_subnet_mask int(8) unsigned DEFAULT NULL,
	ipv6_prefix varbinary(32) NOT NULL DEFAULT 0,
	PRIMARY KEY ( id ),
	CONSTRAINT FOREIGN KEY ( site_id ) REFERENCES sites(id)
	  ON DELETE CASCADE
);

CREATE TABLE pools(
	id int(12) unsigned NOT NULL auto_increment,
	network_id int(12) unsigned NOT NULL,
	address_class ENUM('inet4', 'inet6', 'eui48') NOT NULL DEFAULT 'inet4',
	address_start varbinary(32),
	address_end varbinary(32),
	name varchar(64) NOT NULL DEFAULT 'A pool',
	PRIMARY KEY ( id ),
	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
	  ON DELETE CASCADE
);

CREATE TABLE hosts(
	id int(12) unsigned NOT NULL auto_increment,
	site_id int(12) unsigned NOT NULL,
	hostname varchar(32) NOT NULL DEFAULT 'myhost',
	owner varchar(32) NOT NULL DEFAULT 'root',
	created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY ( id ),
	CONSTRAINT FOREIGN KEY ( site_id ) REFERENCES sites(id)
	  ON DELETE CASCADE
);

CREATE TABLE interfaces(
	id int(12) unsigned NOT NULL auto_increment,
	host_id int(12) unsigned NOT NULL,
	network_id int(12) unsigned NOT NULL,
	name varchar(16) NOT NULL DEFAULT 'eth0',
	description varchar(255) NOT NULL DEFAULT 'eth0',
	PRIMARY KEY ( id ),
	CONSTRAINT FOREIGN KEY ( host_id ) REFERENCES hosts(id)
	  ON DELETE CASCADE,
	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
	  ON DELETE CASCADE
);

CREATE TABLE addresses(
	id int(12) unsigned NOT NULL auto_increment,
	interface_id int(12) unsigned NOT NULL,
	pool_id int(12) unsigned DEFAULT NULL,
	address_class ENUM('inet4', 'inet6', 'eui48') NOT NULL DEFAULT 'inet4',
	address_type ENUM('static', 'dynamic', 'automatic') NOT NULL DEFAULT 'dynamic',
	address_value varbinary(32) DEFAULT NULL,
	PRIMARY KEY ( id ),
	CONSTRAINT FOREIGN KEY ( interface_id ) REFERENCES interfaces(id)
	  ON DELETE CASCADE,
	FOREIGN KEY ( pool_id ) REFERENCES pools(id)
	  ON DELETE CASCADE
);

CREATE TABLE dns_records (
	id int(12) unsigned NOT NULL auto_increment,
	network_id int(12) unsigned NOT NULL,
	owner varchar(64) NOT NULL DEFAULT 'root',
	rname varchar(128) NOT NULL,
	type ENUM('A','AAAA','MX','CNAME','NS','SRV','TXT','SSHFP') DEFAULT NULL,
	ttl int(6) unsigned DEFAULT NULL,
	rdata mediumtext,
	PRIMARY KEY (id),
	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
		ON DELETE CASCADE
);

INSERT INTO sites(name, domain) VALUES
	('Rochester', 'tits123.com'),
	('Cleveland', 'tits123.com');

INSERT INTO networks(site_id, subdomain_name, name, ipv4_subnet, ipv4_subnet_mask, ipv6_prefix) VALUES
	(1, 'roc', 'Main VLAN', 0x0a010000, 0xffff0000, 0x20010470e18f0000),
	(1, 'oe', 'Guest VLAN', 0x0a028000, 0xffff8000, 0x20010470e18f0001),
	(2, 'cle', 'Main VLAN', 0x0a000000, 0xffff0000, 0x20010470e0d80000);

INSERT INTO pools(network_id, address_class, address_start, address_end, name) VALUES
	(1, 'inet4', 0x0a010001, 0x0a0100ff, 'Servers'),
	(1, 'inet4', 0x0a010100, 0x0a01017f, 'User machines'),
	(1, 'inet4', 0x0a010180, 0x0a0101ff, 'VMs'),
	(2, 'inet4', 0x0a028003, 0x0a02fffe, 'OpenEars clients'),
	(3, 'inet4', 0x0a000001, 0x0a0000ff, 'Servers'),
	(3, 'inet4', 0x0a000100, 0x0a0003ff, 'User machines'),
	(1, 'inet6', 0x20010470e18f0000020000fffe000000, 0x20010470e18f0000fffffffffeffffff, 'IPv6 autoconfig hosts'),
	(3, 'inet6', 0x20010470e0d80000020000fffe000000, 0x20010470e0d80000fffffffffeffffff, 'IPv6 autoconfig hosts');

INSERT INTO hosts(site_id, hostname, owner) VALUES
	(1, 'xombie', 'root'),
	(1, 'nighthawk', 'root'),
	(1, 'ratsalad', 'dan');

INSERT INTO interfaces(host_id, network_id, name, description) VALUES
	(1, 1, 'vlan1', 'Internal interface'),
	(1, 2, 'vlan5', 'OpenEars interface'),
	(2, 1, 'vl-xx0r', 'Main interface'),
	(3, 1, 'eth0', 'Main interface');

INSERT INTO addresses(interface_id, pool_id, address_class, address_type, address_value) VALUES
	(1, NULL, 'eui48', 'static', 0x001b21c2092c),
	(1, 1	, 'inet4', 'static', 0x0a010001),
	(1, 7	, 'inet6', 'static', 0x20010470e18f00000000000000000001),
	(2, NULL, 'eui48', 'static', 0x001b21c2092c),
	(2, 1	, 'inet4', 'static', 0x0a027f01),
	(3, NULL, 'eui48', 'static', 0x001b21c4f583),
	(3, 1	, 'inet4', 'static', 0x0a010003),
	(4, 2	, 'inet4', 'dynamic', NULL);