-- $Id$

CREATE LANGUAGE "plpgsql";

--
-- DNS database
--

-- id lookup table for RR types
CREATE TABLE rrtypes (
	id SERIAL PRIMARY KEY,
	label VARCHAR(10) UNIQUE
);
-- id lookup table for admins
CREATE TABLE admins (
	id SERIAL PRIMARY KEY,
	login VARCHAR(16) UNIQUE

	-- XXX: following line moved to ALTER TABLE to fix circular reference
	-- contact_id INTEGER REFERENCES contacts(id)
);

-- zone table
CREATE TABLE zones (
	id SERIAL PRIMARY KEY,
	name VARCHAR(255) UNIQUE,
	minlen INTEGER DEFAULT 4,
	maxlen INTEGER DEFAULT 63,
	ttl INTEGER DEFAULT 259200,
	updateserial BOOLEAN,
	soaserial INTEGER NOT NULL,
	soarefresh INTEGER DEFAULT 3600,
	soaretry INTEGER DEFAULT 1800,
	soaexpires INTEGER DEFAULT 604800,
	soaminimum INTEGER DEFAULT 259200,
	soaprimary VARCHAR(255) NOT NULL,
	soaemail VARCHAR(255) NOT NULL,
	dyn_ip VARCHAR(64)
	-- XXX: following line moved to ALTER TABLE to fix circular reference
	-- dyn_key VARCHAR(64) REFERENCES keys(name)
);


-- lists allowed RR types for each zone
CREATE TABLE allowed_rr (
	id SERIAL PRIMARY KEY,
	zone_id INTEGER REFERENCES zones(id),
	rrtype_id INTEGER REFERENCES rrtypes(id),
	UNIQUE (zone_id, rrtype_id)
);

-- all domains in all zones
CREATE TABLE domains (
	id SERIAL PRIMARY KEY,
	name VARCHAR(64),
	registry_hold BOOLEAN DEFAULT FALSE,
	registry_lock BOOLEAN DEFAULT FALSE,
	client_hold BOOLEAN DEFAULT FALSE,
	internal BOOLEAN DEFAULT FALSE,
	zone_id INTEGER REFERENCES zones(id),
	registrar_id INTEGER,
	created_by INTEGER REFERENCES admins(id) DEFAULT 0,
	created_on TIMESTAMP WITH TIME ZONE,
	updated_by INTEGER REFERENCES admins(id) DEFAULT 0,
	updated_on TIMESTAMP WITH TIME ZONE,
	end_grace_period TIMESTAMP WITH TIME ZONE,
	UNIQUE (name, zone_id)
);
CREATE INDEX domains_name_index ON domains(name,zone_id);

-- lists allowed administrators for each zone
CREATE TABLE admin_zone (
	id SERIAL PRIMARY KEY,
	admin_id INTEGER REFERENCES admins(id),
	zone_id INTEGER REFERENCES zones(id),
	UNIQUE (admin_id, zone_id)
);

-- all resource records for all domains and zones
CREATE TABLE rrs (
	id SERIAL PRIMARY KEY,	-- used only for web framework access
	domain_id INTEGER REFERENCES domains(id),
	ttl INTEGER,
	rrtype_id INTEGER REFERENCES rrtypes(id),
	created_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	label VARCHAR(64),
	value VARCHAR(2048)
);
CREATE INDEX rrs_domain_id_index ON rrs(domain_id);


-- Support for dynamic DNS updates

-- TSIG key table
CREATE TABLE keys (
	name VARCHAR(64) PRIMARY KEY,
	algorithm VARCHAR(20),
	secret VARCHAR(220)
);

CREATE TABLE dyn_transaction (
	id SERIAL PRIMARY KEY,
	date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	zone_id INTEGER REFERENCES zones,
	external_ref VARCHAR(30),
	executed_at TIMESTAMP WITH TIME ZONE,
	serial_before INTEGER,
	serial_after INTEGER
);

CREATE TABLE dyn_queue (
	id SERIAL PRIMARY KEY,
	trans_id INTEGER REFERENCES dyn_transaction ON DELETE CASCADE,
	action VARCHAR(3),
	label VARCHAR(64),
	ttl INTEGER,
	rrtype_id INTEGER REFERENCES rrtypes(id),
	value VARCHAR(2048)
);

CREATE TABLE dyn_transaction_hist (
	id INTEGER UNIQUE,
	date TIMESTAMP WITH TIME ZONE,
	zone_id INTEGER REFERENCES zones,
	external_ref VARCHAR(30),
	executed_at TIMESTAMP WITH TIME ZONE,
	serial_before INTEGER,
	serial_after INTEGER
);

CREATE TABLE dyn_queue_hist (
	id INTEGER,
	trans_id INTEGER REFERENCES dyn_transaction_hist(id) ON DELETE CASCADE,
	action VARCHAR(3),
	label VARCHAR(64),
	ttl INTEGER,
	rrtype_id INTEGER REFERENCES rrtypes(id),
	value VARCHAR(2048)
);

-- Triggers

CREATE OR REPLACE FUNCTION trig_del_dyn_transaction()
    RETURNS TRIGGER as $trig_del_dyn_transaction$
    BEGIN
	INSERT INTO dyn_transaction_hist (
		id, date, zone_id, external_ref,
		executed_at, serial_before, serial_after)
	    VALUES (
		OLD.id, OLD.date, OLD.zone_id, OLD.external_ref,
		OLD.executed_at, OLD.serial_before, OLD.serial_after);
	RETURN OLD;
    END;
$trig_del_dyn_transaction$ LANGUAGE plpgsql;

-- only useful on DELETE
CREATE TRIGGER trig_dyn_transaction BEFORE DELETE ON dyn_transaction
	FOR EACH ROW EXECUTE PROCEDURE trig_del_dyn_transaction();

CREATE OR REPLACE FUNCTION trig_del_dyn_queue()
    RETURNS TRIGGER as $trig_del_dyn_queue$
    BEGIN
	INSERT INTO dyn_queue_hist (
		id, trans_id, action, label, ttl, rrtype_id, value)
	    VALUES (
		OLD.id, OLD.trans_id, OLD.action, OLD.label, OLD.ttl, OLD.rrtype_id, OLD.value);
	RETURN OLD;
    END;
$trig_del_dyn_queue$ LANGUAGE plpgsql;

-- only useful on DELETE
CREATE TRIGGER trig_dyn_queue BEFORE DELETE ON dyn_queue
	FOR EACH ROW EXECUTE PROCEDURE trig_del_dyn_queue();


-- keeping history

-- deleted domains
-- no UNIQUE (name, zone_id) constraint here:
-- a same domain can be created/deleted several times
CREATE TABLE domains_hist (
	id INTEGER,
	name VARCHAR(64),
	registry_hold BOOLEAN DEFAULT FALSE,
	registry_lock BOOLEAN DEFAULT FALSE,
	client_hold BOOLEAN DEFAULT FALSE,
	internal BOOLEAN DEFAULT FALSE,
	zone_id INTEGER REFERENCES zones(id),
	registrar_id INTEGER,
	created_by INTEGER REFERENCES admins(id),
	created_on TIMESTAMP WITH TIME ZONE,
	deleted_by INTEGER REFERENCES admins(id),
	deleted_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	end_grace_period TIMESTAMP WITH TIME ZONE
);

-- deleted RRs
CREATE TABLE rrs_hist (
	domain_id INTEGER,
	ttl INTEGER,
	rrtype_id INTEGER REFERENCES rrtypes(id),
	created_on TIMESTAMP WITH TIME ZONE NOT NULL,
	label VARCHAR(64),
	value VARCHAR(2048),
	deleted_on TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX rrs_hist_domain_id_index ON rrs_hist(domain_id);

-- Functions

-- Allocate handle and insert contact at the same time
-- to avoid race conditions

CREATE OR REPLACE FUNCTION insertcontact(
	sethandle VARCHAR,
	exthandle VARCHAR,
	initials VARCHAR,
	name VARCHAR, email VARCHAR,
	validated_on TIMESTAMP WITH TIME ZONE,
	country CHAR(2), passwd VARCHAR,
	addr VARCHAR, phone VARCHAR, fax VARCHAR,
	created_on TIMESTAMP WITH TIME ZONE,
	updated_on TIMESTAMP WITH TIME ZONE,
	updated_by VARCHAR,
	private BOOLEAN,
	OUT newhandle VARCHAR,
	OUT contact_id INT,
	OUT now TIMESTAMP WITH TIME ZONE)
    AS $insertcontact$
DECLARE
    num INT;
    upinitials VARCHAR;
BEGIN
	now := NOW();
	IF created_on IS NULL THEN
		created_on := now;
	END IF;
	IF updated_on IS NULL THEN
		updated_on := now;
	END IF;

	IF sethandle IS NOT NULL THEN
		-- handle is already set by the caller,
		-- insert directly.
		newhandle := sethandle;
		INSERT INTO contacts (
			handle, exthandle, name, email,
			validated_on, country, passwd, addr, phone, fax,
			created_on, updated_by, updated_on, private
		) VALUES (
			newhandle, exthandle, name, email,
			validated_on, country, passwd, addr, phone, fax,
			created_on, updated_by, updated_on, private
		);
		SELECT currval('contacts_id_seq') INTO contact_id;
		RETURN;
	END IF;

	upinitials := UPPER(initials);
	-- Find the highest allocated handle with the same initials
	SELECT CAST(SUBSTRING(handle FROM '[0-9]+') AS INT)
	    INTO num
	    FROM contacts WHERE handle SIMILAR TO upinitials || '[0-9]+'
	    ORDER BY CAST(SUBSTRING(handle FROM '[0-9]+') AS INT)
	    DESC LIMIT 1;
        IF NOT FOUND THEN
	    num := 0;
        END IF;
	-- Assume the next is free
	num := num + 1;
	-- Try to insert the contact with the new handle
	<<insert_until>>
	LOOP
		-- Build new handle
		newhandle := upinitials || CAST(num AS VARCHAR);
		BEGIN
			INSERT INTO contacts (
				handle, exthandle, name, email,
				validated_on, country, passwd, addr, phone, fax,
				created_on, updated_by, updated_on, private
			) VALUES (
				newhandle, exthandle, name, email,
				validated_on, country, passwd, addr, phone, fax,
				created_on, updated_by, updated_on, private
			);
			SELECT currval('contacts_id_seq') INTO contact_id;
			-- INSERT ok; return the handle, contact id and NOW()
			RETURN;
		EXCEPTION
			-- INSERT failed, race condition:
			-- loop again to try next handle
			WHEN UNIQUE_VIOLATION THEN
		END;
		num := num + 1;
	END LOOP insert_until;
END;
$insertcontact$ LANGUAGE plpgsql;

-- Triggers

CREATE OR REPLACE FUNCTION trig_domains()
    RETURNS TRIGGER as $trig_domains$
    BEGIN
	IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
	    INSERT INTO domains_hist (
		id, name, zone_id,
		registry_hold, registry_lock, internal,
		client_hold,
		registrar_id,
		created_by, created_on, end_grace_period, deleted_on)
	      VALUES (
		OLD.id, OLD.name, OLD.zone_id,
		OLD.registry_hold, OLD.registry_lock, OLD.internal,
		OLD.client_hold,
		OLD.registrar_id,
		OLD.created_by, OLD.created_on, OLD.end_grace_period, NOW());
	END IF;
	IF TG_OP = 'DELETE' THEN
	    RETURN OLD;
	ELSE
	    RETURN NEW;
	END IF;
    END;
$trig_domains$ LANGUAGE plpgsql;

CREATE TRIGGER trig_domains BEFORE UPDATE OR DELETE ON domains
	FOR EACH ROW EXECUTE PROCEDURE trig_domains();

CREATE OR REPLACE FUNCTION trig_rrs()
    RETURNS TRIGGER as $trig_rrs$
    BEGIN
	IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
	    INSERT INTO rrs_hist (
		domain_id,
		ttl, rrtype_id, label, value,
		created_on, deleted_on)
	      VALUES (
		OLD.domain_id,
		OLD.ttl, OLD.rrtype_id, OLD.label, OLD.value,
		OLD.created_on, NOW());
	END IF;
	IF TG_OP = 'DELETE' THEN
	    RETURN OLD;
	ELSE
	    NEW.created_on := NOW();
	    RETURN NEW;
	END IF;
    END;
$trig_rrs$ LANGUAGE plpgsql;

CREATE TRIGGER trig_rrs BEFORE UPDATE OR DELETE ON rrs
	FOR EACH ROW EXECUTE PROCEDURE trig_rrs();

---------------------------------------------------------------------

--
-- Whois database
--

-- contact handling

CREATE TABLE contacts (
	id SERIAL PRIMARY KEY,
	handle varchar(20) UNIQUE NOT NULL,
	exthandle varchar(20),
	name varchar(255),
	email varchar(80),
	addr varchar(400),
	country char(2),
	phone varchar(40),
	fax varchar(40),
	passwd varchar(250),
	created_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	validated_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	updated_by varchar(64),
	updated_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	private boolean DEFAULT FALSE
);
CREATE INDEX contacts_handle_index ON contacts(handle);
CREATE INDEX contacts_name_index ON contacts(lower(name));
CREATE INDEX contacts_exthandle_index ON contacts(exthandle);

CREATE TABLE otp (
	id SERIAL PRIMARY KEY,	-- used only for web framework access
	contact_id INTEGER REFERENCES contacts(id) UNIQUE NOT NULL,
	secret varchar(16),
	-- 'codes' string is maximum 99 chars,
	-- + secret + encryption + base64-encoding = 248 max.
	codes varchar(250),
	created_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	active BOOLEAN DEFAULT FALSE
);

-- moved here from CREATE TABLE admins and zones to fix circular reference
ALTER TABLE admins ADD COLUMN contact_id INTEGER REFERENCES contacts(id);
ALTER TABLE zones ADD COLUMN dyn_key VARCHAR(64) REFERENCES keys(name);

-- mostly static table for mapping contact type integer to a string
-- see example rows below
CREATE TABLE contact_types (
	id SERIAL PRIMARY KEY,
	name varchar(20)
);

-- Secrets used to generate obfuscated email addresses for contacts

CREATE TABLE handle_secrets (
	secret varchar(32),
	expires TIMESTAMP WITH TIME ZONE DEFAULT NOW() + '1 week'
);

CREATE VIEW contacts_email AS
	SELECT id, handle, lower(handle) || '-' || MD5(h.secret || handle || '-' || id::text) AS email
	FROM contacts, handle_secrets h LEFT JOIN handle_secrets h2 ON (h.expires < h2.expires)
	WHERE h2.secret IS NULL;

CREATE TABLE whoisdomains (
	id SERIAL PRIMARY KEY,
	fqdn VARCHAR(255) UNIQUE,
	created_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	updated_by varchar(64),
	updated_on TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX whoisdomains_fqdn_index ON whoisdomains(fqdn);

-- links domains with contacts
-- note that the contact type is part of this relation, allowing
-- a given contact record to be used as different contact types
CREATE TABLE domain_contact (
	id SERIAL PRIMARY KEY,	-- used only for web framework access
	whoisdomain_id INTEGER REFERENCES whoisdomains(id) NOT NULL,
	contact_id INTEGER REFERENCES contacts(id) NOT NULL,
	contact_type_id INTEGER REFERENCES contact_types(id) NOT NULL,
	UNIQUE (whoisdomain_id, contact_id, contact_type_id),
	created_on TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX domain_contact_domain_id ON domain_contact(whoisdomain_id);

CREATE TABLE iso3166_countries (
	iso_id CHAR(2) PRIMARY KEY,
	lang CHAR(2),
	name VARCHAR(60)
);

-- history

-- deleted domain-contact associations
CREATE TABLE domain_contact_hist (
	whoisdomain_id INTEGER NOT NULL,
	contact_id INTEGER,
	contact_type_id INTEGER REFERENCES contact_types(id) NOT NULL,
	created_on TIMESTAMP WITH TIME ZONE,
	deleted_on TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE contacts_hist (
	contact_id INTEGER,
	handle varchar(20),
	exthandle varchar(20),
	name varchar(255),
	email varchar(80),
	addr varchar(400),
	country char(2),
	phone varchar(40),
	fax varchar(40),
	passwd varchar(250),
	created_on TIMESTAMP WITH TIME ZONE,
	validated_on TIMESTAMP WITH TIME ZONE,
	updated_by varchar(64),
	updated_on TIMESTAMP WITH TIME ZONE,
	deleted_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	private boolean DEFAULT FALSE
);
CREATE TABLE whoisdomains_hist (
	whoisdomain_id INTEGER,
	fqdn VARCHAR(255),
	-- this is the only place we can keep created_on for deleted domains
	created_on TIMESTAMP WITH TIME ZONE,
	updated_by varchar(64),
	updated_on TIMESTAMP WITH TIME ZONE,
	deleted_on TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Triggers

CREATE OR REPLACE FUNCTION trig_contacts()
    RETURNS TRIGGER as $trig_contacts$
    BEGIN
	IF TG_OP = 'UPDATE' THEN
	    IF NEW.updated_on IS NULL THEN
		-- INSERT case handled by a DEFAULT NOW() clause;
		-- we need to handle the UPDATE case by hand.
		NEW.updated_on := NOW();
	    END IF;
	END IF;
	IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
	    INSERT INTO contacts_hist (
		contact_id, handle, exthandle,
		name, email, addr,
		country, phone, fax,
		passwd,
		created_on, validated_on,
		updated_by, updated_on, deleted_on, private)
	      VALUES (
		OLD.id, OLD.handle, OLD.exthandle,
		OLD.name, OLD.email, OLD.addr,
		OLD.country, OLD.phone, OLD.fax,
		OLD.passwd,
		OLD.created_on, OLD.validated_on,
		OLD.updated_by, OLD.updated_on, NOW(), OLD.private);
	END IF;
	IF TG_OP = 'DELETE' THEN
	    RETURN OLD;
	ELSE
	    RETURN NEW;
	END IF;
    END;
$trig_contacts$ LANGUAGE plpgsql;

CREATE TRIGGER trig_contacts BEFORE UPDATE OR DELETE ON contacts
	FOR EACH ROW EXECUTE PROCEDURE trig_contacts();

CREATE OR REPLACE FUNCTION trig_domain_contact()
    RETURNS TRIGGER as $trig_domain_contact$
    BEGIN
	IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
	    INSERT INTO domain_contact_hist (
		whoisdomain_id, contact_id, contact_type_id,
		created_on, deleted_on)
	      VALUES (
		OLD.whoisdomain_id, OLD.contact_id, OLD.contact_type_id,
		OLD.created_on, NOW());
	END IF;
	IF TG_OP = 'DELETE' THEN
	    RETURN OLD;
	ELSE
	    RETURN NEW;
	END IF;
    END;
$trig_domain_contact$ LANGUAGE plpgsql;

CREATE TRIGGER trig_domain_contact BEFORE UPDATE OR DELETE
	ON domain_contact
	FOR EACH ROW EXECUTE PROCEDURE trig_domain_contact();

CREATE OR REPLACE FUNCTION trig_whoisdomains()
    RETURNS TRIGGER as $trig_whoisdomains$
    BEGIN
	IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
	    INSERT INTO whoisdomains_hist (
		whoisdomain_id, fqdn, created_on, deleted_on)
		VALUES (OLD.id, OLD.fqdn, OLD.created_on, NOW());
	END IF;
	IF TG_OP = 'DELETE' THEN
	    RETURN OLD;
	ELSE
	    RETURN NEW;
	END IF;
    END
$trig_whoisdomains$ LANGUAGE plpgsql;

CREATE TRIGGER trig_whoisdomains BEFORE UPDATE OR DELETE
	ON whoisdomains
	FOR EACH ROW EXECUTE PROCEDURE trig_whoisdomains();

---------------------------------------------------------------------

--
-- Requests database
--

CREATE TABLE requests (
	id VARCHAR(30) PRIMARY KEY,
	email VARCHAR(80),
	action VARCHAR(8),
	fqdn VARCHAR(200),
	zone_id INTEGER REFERENCES zones(id) NOT NULL,
	language VARCHAR(5),
	state VARCHAR(10),
	zonerecord VARCHAR(500),
	whoisrecord VARCHAR(2000),
	tags VARCHAR(50),
	contact_id INTEGER REFERENCES contacts(id) DEFAULT NULL,

	-- pending state variables
	pending_state VARCHAR(10) DEFAULT NULL,
	reason VARCHAR(80) DEFAULT NULL,
	reasonfield VARCHAR(1000) DEFAULT NULL,
	admin_contact_id INTEGER REFERENCES contacts(id) DEFAULT NULL
);
CREATE INDEX requests_fqdn_index ON requests(fqdn);

CREATE TABLE requests_hist (
	id VARCHAR(30),
	email VARCHAR(80),
	action VARCHAR(8),
	fqdn VARCHAR(200),
	zone_id INTEGER REFERENCES zones(id) DEFAULT NULL,
	language VARCHAR(5),
	state VARCHAR(10),
	zonerecord VARCHAR(500),
	whoisrecord VARCHAR(2000),
	tags VARCHAR(50),
	deleted_on TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	contact_id INTEGER REFERENCES contacts(id) DEFAULT NULL,
	reason VARCHAR(80) DEFAULT NULL,
	reasonfield VARCHAR(1000) DEFAULT NULL,
	admin_contact_id INTEGER REFERENCES contacts(id) DEFAULT NULL
);

CREATE TABLE requests_log (
	id SERIAL PRIMARY KEY,
	fqdn VARCHAR(255),
	contact_id INTEGER,
	date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	output VARCHAR(20000),
	errors INTEGER,
	warnings INTEGER,
	request_id VARCHAR(30)
);

-- Triggers

CREATE OR REPLACE FUNCTION trig_del_requests()
    RETURNS TRIGGER as $trig_del_requests$
    BEGIN
	INSERT INTO requests_hist (
		id, email, action, fqdn, zone_id,
		language,
		state, zonerecord, whoisrecord, tags,
		contact_id, reason, reasonfield,
		admin_contact_id)
	    VALUES (
		OLD.id, OLD.email, OLD.action, OLD.fqdn, OLD.zone_id,
		OLD.language,
		OLD.state, OLD.zonerecord, OLD.whoisrecord, OLD.tags,
		OLD.contact_id, OLD.reason, OLD.reasonfield,
		OLD.admin_contact_id);
	RETURN OLD;
    END;
$trig_del_requests$ LANGUAGE plpgsql;

-- only useful on DELETE
CREATE TRIGGER trig_del_requests BEFORE DELETE ON requests
	FOR EACH ROW EXECUTE PROCEDURE trig_del_requests();




-- Logs

CREATE TABLE log (
	id SERIAL PRIMARY KEY,
	date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	contact_id INTEGER REFERENCES contacts(id) DEFAULT NULL,
	action VARCHAR(10),
	message VARCHAR(300) DEFAULT NULL
);

CREATE TABLE log_hist (
	id INTEGER PRIMARY KEY,
	date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	contact_id INTEGER,
	action VARCHAR(10),
	message VARCHAR(300) DEFAULT NULL
);

CREATE OR REPLACE FUNCTION trig_del_log()
    RETURNS TRIGGER as $trig_del_log$
    BEGIN
	INSERT INTO log_hist (id, date, contact_id, action, message)
	    VALUES (
		OLD.id, OLD.date, OLD.contact_id,
		OLD.action, OLD.message);
	RETURN OLD;
    END;
$trig_del_log$ LANGUAGE plpgsql;

-- only useful on DELETE
CREATE TRIGGER trig_del_log BEFORE DELETE ON log
	FOR EACH ROW EXECUTE PROCEDURE trig_del_log();

-- Monitoring
CREATE TABLE serial_server_log (
	id SERIAL PRIMARY KEY,
	zone VARCHAR(255),	-- not a reference to the zones table to allow external zones
	fqdn VARCHAR(255),
	ip VARCHAR(40),
	serial INTEGER,
	first_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	last_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        UNIQUE (zone, ip, serial)
);

CREATE VIEW serial_log AS
	SELECT zone, serial, MIN(first_seen), MAX(last_seen) FROM serial_server_log GROUP by zone, serial;


-- Permissions on DNS database
-- read-write
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE rrs TO root, www, nobody, autoreg;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE domains TO root, www, nobody, autoreg;
GRANT SELECT,UPDATE ON domains_id_seq TO root, www, nobody, autoreg;
GRANT SELECT,UPDATE ON TABLE zones TO root, www, nobody, autoreg;
GRANT INSERT ON TABLE zones TO autoreg;
GRANT SELECT,UPDATE ON TABLE zones_id_seq TO autoreg;
GRANT INSERT,SELECT ON TABLE rrs_hist TO root, www, nobody, autoreg;
GRANT DELETE ON TABLE rrs_hist TO www, autoreg;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE dyn_transaction TO www, autoreg;
GRANT SELECT, INSERT, UPDATE ON TABLE dyn_queue TO www, autoreg;
GRANT DELETE ON TABLE dyn_queue TO autoreg;
-- append-only
GRANT INSERT,SELECT ON TABLE domains_hist TO root, www, nobody, autoreg;
GRANT INSERT,SELECT ON TABLE allowed_rr TO autoreg;
GRANT SELECT,UPDATE ON TABLE allowed_rr_id_seq TO autoreg;
GRANT SELECT,UPDATE ON TABLE dyn_transaction_id_seq TO www, autoreg;
GRANT SELECT,UPDATE ON TABLE dyn_queue_id_seq TO www, autoreg;
GRANT INSERT ON TABLE dyn_transaction_hist TO www, autoreg;
GRANT INSERT ON TABLE dyn_queue_hist TO www, autoreg;
-- read-only
GRANT SELECT ON TABLE admins,allowed_rr,rrtypes TO root, www, nobody;
GRANT SELECT ON TABLE contact_types TO whois;
GRANT SELECT ON TABLE admin_zone TO www, autoreg;
GRANT SELECT ON TABLE keys TO www, autoreg;

-- Permissions on Whois database
-- read-write
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE contacts TO www, autoreg;
GRANT SELECT,UPDATE ON TABLE contacts_id_seq TO www, autoreg;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE whoisdomains TO www, autoreg;
GRANT SELECT,UPDATE ON TABLE whoisdomains_id_seq TO www, autoreg;
GRANT INSERT,SELECT,DELETE ON TABLE domain_contact TO www, autoreg;
-- note: SELECT required below for DELETE based on expiration date
GRANT INSERT,SELECT,DELETE ON TABLE handle_secrets to www, autoreg;
-- append-only
GRANT INSERT,SELECT ON TABLE contacts_hist TO www, autoreg;
GRANT INSERT,SELECT ON TABLE whoisdomains_hist TO www, autoreg;
GRANT INSERT,SELECT ON TABLE domain_contact_hist TO www, autoreg;
GRANT INSERT,SELECT ON TABLE requests_log TO www, autoreg;
GRANT SELECT,UPDATE ON TABLE requests_log_id_seq TO www;
GRANT INSERT ON TABLE admin_zone TO autoreg;
-- read-only
GRANT SELECT ON TABLE contact_types TO www, autoreg;
GRANT SELECT ON TABLE contacts, contacts_email, handle_secrets, whoisdomains, domain_contact TO whois;
GRANT SELECT ON TABLE contacts_email TO www, autoreg;
GRANT SELECT ON TABLE iso3166_countries TO www, autoreg, whois;
GRANT SELECT ON TABLE contacts, handle_secrets TO postfix;
GRANT SELECT ON TABLE serial_log TO autoreg;

-- Permissions on OTP
GRANT INSERT, SELECT, UPDATE, DELETE ON otp TO autoreg, www;
GRANT SELECT, UPDATE ON otp_id_seq TO autoreg, www;

-- Permissions on requests database
-- read-write
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE requests TO autoreg, www;
-- append-only
GRANT INSERT ON TABLE requests_hist TO autoreg, www;

-- Permissions on logs
-- append-only
GRANT INSERT, SELECT ON TABLE log TO www, autoreg;
GRANT INSERT, SELECT ON TABLE log_hist TO www;
GRANT SELECT, UPDATE ON TABLE log_id_seq TO www, autoreg;
GRANT INSERT, SELECT, UPDATE ON TABLE serial_server_log TO autoreg;
GRANT SELECT, UPDATE ON TABLE serial_server_log_id_seq TO autoreg;