-- 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';

CREATE TABLE default_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host character varying(100) DEFAULT ''::character varying NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val character varying(100) DEFAULT ''::character varying 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 character varying(255)
);

-- 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 domains (
    domain_id serial NOT NULL,
    "domain" character varying(80) NOT NULL,
    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
);

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
\.

CREATE TABLE log (
    domain_id integer,
    user_id integer,
    group_id integer,
    email character varying(60),
    name character varying(60),
    entry character varying(200),
    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,
    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, user_id, group_id) FROM stdin;
1	f	f	f	f	f	f	f	f	f	f	f	f	f	f	\N	1
2	t	f	f	f	f	f	f	f	f	f	f	f	f	f	1	\N
\.

-- fixeme:  need to handle looooong TXT records (eg, SPF)
CREATE TABLE records (
    domain_id integer NOT NULL,
    record_id serial NOT NULL,
    host character varying(100) DEFAULT ''::character varying NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val character varying(100) DEFAULT ''::character varying 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 character varying(255)
);

CREATE TABLE rectypes (
    val integer NOT NULL,
    name character varying(12) NOT NULL,
    stdflag integer DEFAULT 1 NOT NULL,
    listorder integer DEFAULT 255 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) 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
\.

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	test@test	$1$BByge8u2$48AaGX3YeHplfErX5Tlqa1	\N	\N	\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_pkey PRIMARY KEY ("domain");

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 domains
    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 records
    ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_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('default_records_record_id_seq', 8, true);
SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
SELECT pg_catalog.setval('groups_group_id_seq', 2, true);
SELECT pg_catalog.setval('permissions_permission_id_seq', 3, true);
SELECT pg_catalog.setval('records_record_id_seq', 1, false);
SELECT pg_catalog.setval('users_user_id_seq', 2, false);
