-- these lines could be run as a superuser.  alter database name, username, password, group as appropriate.
-- make sure to alter dnsdb.conf to match
-- CREATE GROUP dnsdb;
-- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb;
-- CREATE DATABASE dnsdb OWNED BY dnsdb;
-- SET SESSION AUTHORIZATION 'dnsdb';

-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
CREATE TABLE misc (
	misc_id	serial NOT NULL,
	key text DEFAULT '' NOT NULL,
	value text DEFAULT '' NOT NULL
);

COPY misc (misc_id, key, value) FROM stdin;
1	dbversion	1.2
\.

CREATE TABLE locations (
    location character varying (4) PRIMARY KEY,
    loc_id serial UNIQUE,
    group_id integer NOT NULL DEFAULT 1,
    iplist text NOT NULL DEFAULT '',
    description character varying(40) NOT NULL DEFAULT '',
    comments text NOT NULL DEFAULT ''
);

CREATE TABLE default_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    distance integer DEFAULT 0 NOT NULL,
    weight integer DEFAULT 0 NOT NULL,
    port integer DEFAULT 0 NOT NULL,
    ttl integer DEFAULT 86400 NOT NULL,
    description text
);

-- default records for the default group
COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
1	1	ns1.example.com:hostmaster.DOMAIN	6	10800:3600:604800:5400	0	0	0	86400	\N
2	1	DOMAIN	2	ns2.example.com	0	0	0	7200	\N
3	1	DOMAIN	2	ns1.example.com	0	0	0	7200	\N
4	1	DOMAIN	1	10.0.0.4	0	0	0	7200	\N
5	1	DOMAIN	15	mx1.example.com	10	0	0	7200	\N
6	1	www.DOMAIN	5	DOMAIN	0	0	0	10800	\N
7	1	DOMAIN	16	"v=spf1 a mx -all"	0	0	0	10800	\N
\.

CREATE TABLE default_rev_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    ttl integer DEFAULT 86400 NOT NULL,
    description text
);

COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
1	1	hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN	6	3600:900:1048576:2560	3600	
2	1	unused-%r.ADMINDOMAIN	65283	ZONE	3600	
3	1	ns2.example.com	2	ZONE	7200	\N
4	1	ns1.example.com	2	ZONE	7200	\N
\.

CREATE TABLE domains (
    domain_id serial NOT NULL,
    "domain" character varying(80) NOT NULL PRIMARY KEY,
    group_id integer DEFAULT 1 NOT NULL,
    description character varying(255) DEFAULT ''::character varying NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    zserial integer,
    sertype character(1) DEFAULT 'D'::bpchar,
    changed boolean DEFAULT true NOT NULL,
    default_location character varying (4) DEFAULT '' NOT NULL
);

CREATE TABLE revzones (
    rdns_id serial NOT NULL,
    revnet cidr NOT NULL PRIMARY KEY,
    group_id integer DEFAULT 1 NOT NULL,
    description character varying(255) DEFAULT ''::character varying NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    zserial integer,
    sertype character(1) DEFAULT 'D'::bpchar,
    changed boolean DEFAULT true NOT NULL,
    default_location character varying (4) DEFAULT '' NOT NULL
);

CREATE TABLE groups (
    group_id serial NOT NULL,
    parent_group_id integer DEFAULT 1 NOT NULL,
    group_name character varying(255) DEFAULT ''::character varying NOT NULL,
    permission_id integer DEFAULT 1 NOT NULL,
    inherit_perm boolean DEFAULT true NOT NULL
);

-- Provide a basic default group
COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
1	1	1	default
\.

-- entry is text due to possible long entries from AXFR - a domain with "many"
-- odd records will overflow varchar(200)
CREATE TABLE log (
    log_id serial NOT NULL,
    domain_id integer,
    rdns_id integer,
    user_id integer,
    group_id integer,
    email character varying(60),
    name character varying(60),
    entry text,
    stamp timestamp with time zone DEFAULT now()
);

CREATE TABLE permissions (
    permission_id serial NOT NULL,
    "admin" boolean DEFAULT false NOT NULL,
    self_edit boolean DEFAULT false NOT NULL,
    group_create boolean DEFAULT false NOT NULL,
    group_edit boolean DEFAULT false NOT NULL,
    group_delete boolean DEFAULT false NOT NULL,
    user_create boolean DEFAULT false NOT NULL,
    user_edit boolean DEFAULT false NOT NULL,
    user_delete boolean DEFAULT false NOT NULL,
    domain_create boolean DEFAULT false NOT NULL,
    domain_edit boolean DEFAULT false NOT NULL,
    domain_delete boolean DEFAULT false NOT NULL,
    record_create boolean DEFAULT false NOT NULL,
    record_edit boolean DEFAULT false NOT NULL,
    record_delete boolean DEFAULT false NOT NULL,
    record_locchg boolean DEFAULT false NOT NULL,
    location_create boolean DEFAULT false NOT NULL,
    location_edit boolean DEFAULT false NOT NULL,
    location_delete boolean DEFAULT false NOT NULL,
    location_view boolean DEFAULT false NOT NULL,
    user_id integer UNIQUE,
    group_id integer UNIQUE
);

-- Need *two* basic permissions;  one for the initial group, one for the default admin user
COPY permissions (permission_id, "admin", self_edit, group_create, group_edit, group_delete, user_create, user_edit, user_delete, domain_create, domain_edit, domain_delete, record_create, record_edit, record_delete, record_locchg, location_create, location_edit, location_delete, location_view, user_id, group_id) FROM stdin;
1	f	f	f	f	f	f	f	f	t	t	t	t	t	t	f	f	f	f	f	\N	1
2	t	f	f	f	f	f	f	f	f	f	f	f	f	f	f	f	f	f	f	1	\N
\.

-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
CREATE TABLE records (
    domain_id integer NOT NULL DEFAULT 0,
    rdns_id integer NOT NULL DEFAULT 0,
    record_id serial NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    distance integer DEFAULT 0 NOT NULL,
    weight integer DEFAULT 0 NOT NULL,
    port integer DEFAULT 0 NOT NULL,
    ttl integer DEFAULT 7200 NOT NULL,
    description text,
    location character varying (4) DEFAULT '' NOT NULL
);

CREATE TABLE rectypes (
    val integer NOT NULL,
    name character varying(20) NOT NULL,
    stdflag integer DEFAULT 1 NOT NULL,
    listorder integer DEFAULT 255 NOT NULL,
    alphaorder integer DEFAULT 32768 NOT NULL
);

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

-- Custom types (ab)using the "Private use" range from 65280 to 65534
COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
65280	A+PTR	2	2	2
65281	AAAA+PTR	2	4	4
65282	PTR template	3	6	2
65283	A+PTR template	2	7	2
65284	AAAA+PTR template	2	8	2
65285	Delegation	2	9	2
\.

CREATE TABLE users (
    user_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    username character varying(60) NOT NULL,
    "password" character varying(34) NOT NULL,
    firstname character varying(60),
    lastname character varying(60),
    phone character varying(15),
    "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    permission_id integer DEFAULT 1 NOT NULL,
    inherit_perm boolean DEFAULT true NOT NULL
);

-- create initial default user?  may be better to create an "initialize" script or something
COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
1	1	admin	$1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d.	Initial	User	\N	S	1	2	f
\.

--
-- contraints.  add these here so initial data doesn't get added strangely.
--

-- primary keys
ALTER TABLE ONLY permissions
    ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);

ALTER TABLE ONLY domains
    ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);

ALTER TABLE ONLY default_records
    ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);

ALTER TABLE ONLY records
    ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);

ALTER TABLE ONLY rectypes
    ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (username);

ALTER TABLE ONLY users
    ADD CONSTRAINT uidu UNIQUE (user_id);

-- foreign keys
-- fixme: permissions FK refs
ALTER TABLE ONLY locations
    ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY domains
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY revzones
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY default_records
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY users
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);

-- set starting sequence numbers, since we've inserted data before they're active
SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
SELECT pg_catalog.setval('records_record_id_seq', 1, false);
SELECT pg_catalog.setval('users_user_id_seq', 2, false);
