-- fixme:  include template-ish ACL?
CREATE TABLE groups (
	group_id SERIAL UNIQUE,
	parent_group_id int NOT NULL DEFAULT '1',
	name varchar(255) NOT NULL DEFAULT ''
);
-- gotta have a default group to attach some further default data to...
INSERT INTO groups (name) VALUES ('default');

-- use ON DELETE (CASCADE|RESTRICT|NO ACTION) on FKs

CREATE TABLE domains (
	domain_id SERIAL UNIQUE,
	domain varchar(80) NOT NULL PRIMARY KEY,
	group_id int NOT NULL DEFAULT 1 REFERENCES groups (group_id),
	description varchar(255) NOT NULL DEFAULT '',
	status int NOT NULL DEFAULT 1
);

CREATE TABLE default_records (
	record_id SERIAL PRIMARY KEY,
	group_id int NOT NULL DEFAULT 1 REFERENCES groups (group_id),
	host varchar(100) NOT NULL DEFAULT '',
	type int NOT NULL DEFAULT 1,
	val varchar(100) NOT NULL DEFAULT '',
	distance int NOT NULL DEFAULT 0,
	weight int NOT NULL DEFAULT 0,
	port int NOT NULL DEFAULT 0,
	ttl int NOT NULL DEFAULT 86400,
	description varchar(255)
);

-- let's take pity on the poor sod installing things and include some default data that's halfway sane
INSERT INTO default_records (group_id,host,type,val,ttl) VALUES (1,'ns1.example.com:hostmaster.DOMAIN',6,'10800:3600:604800:10800',86400);

CREATE TABLE records (
	domain_id int NOT NULL REFERENCES domains (domain_id),
	record_id SERIAL PRIMARY KEY,
	host varchar(100) NOT NULL DEFAULT '',
	type int NOT NULL DEFAULT 1,
	val varchar(100) NOT NULL DEFAULT '',
	distance int NOT NULL DEFAULT 0,
	weight int NOT NULL DEFAULT 0,
	port int NOT NULL DEFAULT 0,
	ttl int NOT NULL DEFAULT 86400,
	description varchar(255)
);

-- oooo! oooo!  allow staff/unwashed-masses parallel users?
--  - multitable, or just another column in the database...?
--  - go with column for now (could even overload groups...)
CREATE TABLE users (
	user_id SERIAL PRIMARY KEY,
	group_id int NOT NULL DEFAULT 1 REFERENCES groups (group_id),
	email varchar(60) NOT NULL,
	password varchar(34) NOT NULL,
	firstname varchar(30),
	lastname varchar(30),
	phone varchar(15),
	type char NOT NULL DEFAULT 'S',
	status int NOT NULL DEFAULT 1,
	acl varchar(40) NOT NULL DEFAULT 'b'
);

-- include a default user, so we don't break on install.  not happy with this, but...
-- default password is 'test'
INSERT INTO users (group_id,email,password,type,status,acl) VALUES (1,'test@test','$1$BByge8u2$48AaGX3YeHplfErX5Tlqa1','S',1,'A');

-- these could be stuffed in a hash, but that gets tedious
CREATE TABLE rectypes (
	val int NOT NULL,
	name varchar(12) NOT NULL,
	stdflag int NOT NULL DEFAULT 1,
	listorder int NOT NULL DEFAULT 255,
	PRIMARY KEY (val,name)
);
-- data from http://www.iana.org/assignments/dns-parameters
COPY rectypes (val, name, stdflag, listorder) FROM stdin;
1	A	1	1
2	NS	1	2
3	MD	2	255
4	MF	2	255
5	CNAME	1	6
6	SOA	0	8
7	MB	3	255
8	MG	3	255
9	MR	3	255
10	NULL	3	255
11	WKS	3	255
12	PTR	2	4
13	HINFO	3	255
14	MINFO	3	255
15	MX	1	3
16	TXT	1	5
17	RP	2	255
18	AFSDB	3	255
19	X25	3	255
20	ISDN	3	255
21	RT	3	255
22	NSAP	3	255
23	NSAP-PTR	3	255
24	SIG	3	255
25	KEY	3	255
26	PX	3	255
27	GPOS	3	255
28	AAAA	2	2
29	LOC	3	255
30	NXT	3	255
31	EID	3	255
32	NIMLOC	3	255
33	SRV	1	7
34	ATMA	3	255
35	NAPTR	3	255
36	KX	3	255
37	CERT	3	255
38	A6	3	3
39	DNAME	3	255
40	SINK	3	255
41	OPT	3	255
42	APL	3	255
43	DS	3	255
44	SSHFP	3	255
45	IPSECKEY	3	255
46	RRSIG	3	255
47	NSEC	3	255
48	DNSKEY	3	255
49	DHCID	3	255
50	NSEC3	3	255
51	NSEC3PARAM	3	255
55	HIP	3	255
99	SPF	3	255
100	UINFO	3	255
101	UID	3	255
102	GID	3	255
103	UNSPEC	3	255
249	TKEY	3	255
250	TSIG	3	255
251	IXFR	3	255
252	AXFR	3	255
253	MAILB	3	255
254	MAILA	3	255
32768	TA	3	255
32769	DLV	3	255
\.