-- $Id$
-- $URL$

-- moet in "public" omdat-ie anders verdwijnt met de DROP SCHEMA:
-- postgres@cobalon:~$ psql -c 'CREATE EXTENSION "pgcrypto" SCHEMA public' mailmin

\encoding UTF8


BEGIN;

DROP SCHEMA IF EXISTS mailmin CASCADE;
CREATE SCHEMA mailmin;



CREATE SEQUENCE seq;

CREATE TABLE lusergroups (
	lusergroup BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	name TEXT
		NOT NULL
		UNIQUE
);

CREATE TABLE lusers (
	luser BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	name TEXT
		NOT NULL
		UNIQUE,
	description TEXT
		NOT NULL
		UNIQUE,
	admin BOOL
		DEFAULT FALSE
		NOT NULL,
	primarygroup BIGINT
		NOT NULL
		REFERENCES lusergroups (lusergroup)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE lusergroupmembers (
	lusergroupmember BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	luser BIGINT
		NOT NULL
		REFERENCES lusers (luser)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	lusergroup BIGINT
		NOT NULL
		REFERENCES lusergroups (lusergroup)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	admin BOOL
		NOT NULL
		DEFAULT FALSE
);

-- token: random waarde in geval van challenge gestuurd naar 'address'
CREATE TABLE emailaddresses (
	emailaddress BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	address TEXT
		NOT NULL,
	token UUID,
	token_expires TIMESTAMP
);

CREATE UNIQUE INDEX emailaddress_lc_address ON emailaddresses (lower(address));

CREATE TABLE addresslists (
	addresslist BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	name TEXT,
	private BOOLEAN
		DEFAULT FALSE,
	ctime TIMESTAMP
		NOT NULL
		DEFAULT CURRENT_TIMESTAMP,
	mtime TIMESTAMP
		NOT NULL
		DEFAULT CURRENT_TIMESTAMP,
	luser BIGINT
		REFERENCES lusers (luser)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	lusergroup BIGINT
		REFERENCES lusergroups (lusergroup)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX addresslists_luser ON addresslists (luser);
CREATE INDEX addresslists_lusergroup ON addresslists (lusergroup);

CREATE TABLE addresslistentries (
	addresslistentry BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	addresslist BIGINT
		NOT NULL
		REFERENCES addresslists (addresslist)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	emailaddress BIGINT
		NOT NULL
		REFERENCES emailaddresses (emailaddress)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED
);

CREATE UNIQUE INDEX addresslistentries_addresslist_emailaddress ON addresslistentries (addresslist, emailaddress);
CREATE INDEX addresslistentries_addresslist ON addresslistentries (addresslist);
CREATE INDEX addresslistentries_emailaddress ON addresslistentries (emailaddress);

CREATE TABLE mailings (
	mailing BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	mailsubject TEXT,
	mailfromname TEXT,
	mailfromemail TEXT,
	mailfromapproved BOOL
		NOT NULL
		DEFAULT FALSE,
	mailfromtoken UUID,
	mailfromtoken_expires TIMESTAMP,
	ctime TIMESTAMP
		NOT NULL
		DEFAULT CURRENT_TIMESTAMP,
	mtime TIMESTAMP
		NOT NULL
		DEFAULT CURRENT_TIMESTAMP,
	proofread_when TIMESTAMP,
	submitted_when TIMESTAMP,
	approved_when TIMESTAMP,
	rejected_when TIMESTAMP,
	sent_when TIMESTAMP,
	addresslist BIGINT
		REFERENCES addresslists (addresslist)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	moderator BIGINT
		REFERENCES lusers (luser)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	rejection TEXT, -- message from the moderator
	comment TEXT,
	luser BIGINT
		REFERENCES lusers (luser)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	lusergroup BIGINT
		REFERENCES lusergroups (lusergroup)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE mailbodies (
	mailbody BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	mailing BIGINT
		UNIQUE
		REFERENCES mailings (mailing)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	bodytext TEXT
		NOT NULL
		DEFAULT ''
);

-- download: 0 indien attachment in mail meegestuurd moet worden, 1 als het downloadlink moet zijn
CREATE TABLE attachments (
	attachment BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	luser BIGINT
		REFERENCES lusers (luser)
		ON DELETE SET NULL
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	mailing BIGINT
		REFERENCES mailings (mailing)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	filename TEXT
		NOT NULL
		DEFAULT '',
	filesize BIGINT
		NOT NULL
		DEFAULT 0,
	download BOOLEAN
		DEFAULT FALSE,
	contents BYTEA
);

CREATE TABLE attachmentkeys (
	attachmentkey BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	attachment BIGINT
		REFERENCES attachments (attachment)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	used BIGINT
		DEFAULT 0,
	key UUID
		NOT NULL
		DEFAULT public.gen_random_uuid()
);

CREATE TABLE finaldestinations (
	finaldestination BIGINT
		PRIMARY KEY
		DEFAULT nextval('seq'),
	mailing BIGINT
		NOT NULL
		REFERENCES mailings (mailing)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	emailaddress BIGINT
		NOT NULL
		REFERENCES emailaddresses (emailaddress)
		ON DELETE CASCADE
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	actual_emailaddress TEXT
		NOT NULL,
	verp UUID
		NOT NULL
		DEFAULT public.gen_random_uuid(),
	sent_when TIMESTAMP,
	last_bounce BYTEA,
	last_bounce_received_when TIMESTAMP
);

CREATE INDEX finaldestinations_verp ON finaldestinations (verp);
CREATE INDEX finaldestinations_mailing ON finaldestinations (mailing);
CREATE INDEX finaldestinations_emailaddress ON finaldestinations (emailaddress);

--CREATE VIEW view_groupmemberships AS 
--	SELECT lusergroups.lusergroup, lusers.luser, lusergroupmembers.lusergroupmember
--	FROM lusergroups JOIN lusergroupmembers USING (lusergroup)
--		JOIN lusers USING (luser);

--CREATE VIEW view_addresslists AS
--		SELECT addresslists.*, NULL, addresslistlusergroups.lusergroup 
--			FROM addresslists JOIN addresslistlusergroups USING (addresslist)
--	UNION
--		SELECT addresslists.*, lusergroup, NULL
--			FROM addresslists;

--CREATE VIEW view_emailaddresses AS
--	SELECT e.emailaddress, e.address, e.token, a.addresslist
--	FROM emailaddresses e JOIN addresslistentries a USING (emailaddress);

INSERT INTO lusergroups (name) VALUES
	('lis-unix'),
	('tisem-ict-office'),
	('lis-servicedesk'),
	('Jeroen Egmond 2 (myself)'),
	('Wessel Dankers (myself)');

INSERT INTO lusers (name, description, primarygroup, admin) VALUES
	('jegmond2', 'Jeroen Egmond 2', (SELECT lusergroup FROM lusergroups WHERE name = 'Jeroen Egmond 2 (myself)'), TRUE),
	('wsl', 'Wessel Dankers', (SELECT lusergroup FROM lusergroups WHERE name = 'Wessel Dankers (myself)'), TRUE);

INSERT INTO lusergroupmembers (luser, lusergroup, admin) VALUES
	((SELECT luser FROM lusers WHERE name = 'jegmond2'), (SELECT lusergroup FROM lusergroups WHERE name = 'Jeroen Egmond 2 (myself)'), TRUE),
	((SELECT luser FROM lusers WHERE name = 'jegmond2'), (SELECT lusergroup FROM lusergroups WHERE name = 'lis-unix'), FALSE),
	((SELECT luser FROM lusers WHERE name = 'jegmond2'), (SELECT lusergroup FROM lusergroups WHERE name = 'tisem-ict-office'), TRUE),
	((SELECT luser FROM lusers WHERE name = 'wsl'), (SELECT lusergroup FROM lusergroups WHERE name = 'Wessel Dankers (myself)'), FALSE),
	((SELECT luser FROM lusers WHERE name = 'wsl'), (SELECT lusergroup FROM lusergroups WHERE name = 'lis-unix'), TRUE);

INSERT INTO addresslists (name, private, luser, lusergroup, mtime) VALUES
	('Testlijst-002-unix', FALSE, (SELECT luser FROM lusers WHERE name = 'jegmond2'), (SELECT lusergroup FROM lusergroups WHERE name = 'lis-unix'), CURRENT_TIMESTAMP - '10 minute'::INTERVAL),
	('Testlijst-001-tisem', FALSE, (SELECT luser FROM lusers WHERE name = 'jegmond2'), (SELECT lusergroup FROM lusergroups WHERE name = 'tisem-ict-office'), CURRENT_TIMESTAMP - '10 minute'::INTERVAL),
	('Testlijst-003-lissd', FALSE, (SELECT luser FROM lusers WHERE name = 'wsl'), (SELECT lusergroup FROM lusergroups WHERE name = 'lis-servicedesk'), CURRENT_TIMESTAMP - '10 minute'::INTERVAL),
	('Testlijst-004-ictoffice2', FALSE, (SELECT luser FROM lusers WHERE name = 'wsl'), (SELECT lusergroup FROM lusergroups WHERE name = 'tisem-ict-office'), CURRENT_TIMESTAMP - '10 minute'::INTERVAL);

INSERT INTO emailaddresses (address, token) VALUES
	('j.egmond@uvt.nl', NULL),
	('jegmond2@uvt.nl', NULL),
	('wsl@uvt.nl', NULL),
	('w.s.l.dankers@uvt.nl', NULL),
	('niet.bestaand.testadres.mailmin@uvt.nl', NULL);

INSERT INTO addresslistentries (addresslist, emailaddress) VALUES
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix'), (SELECT emailaddress FROM emailaddresses WHERE address = 'j.egmond@uvt.nl')),
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix'), (SELECT emailaddress FROM emailaddresses WHERE address = 'jegmond2@uvt.nl')),
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix'), (SELECT emailaddress FROM emailaddresses WHERE address = 'niet.bestaand.testadres.mailmin@uvt.nl')),
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-001-tisem'), (SELECT emailaddress FROM emailaddresses WHERE address = 'j.egmond@uvt.nl')),
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-003-lissd'), (SELECT emailaddress FROM emailaddresses WHERE address = 'j.egmond@uvt.nl')),
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-003-lissd'), (SELECT emailaddress FROM emailaddresses WHERE address = 'w.s.l.dankers@uvt.nl')),
	((SELECT addresslist FROM addresslists WHERE name = 'Testlijst-003-lissd'), (SELECT emailaddress FROM emailaddresses WHERE address = 'wsl@uvt.nl'));

INSERT INTO mailings (luser, lusergroup, mailsubject, mailfromname, mailfromemail, mailfromapproved, addresslist) VALUES (
	(SELECT luser FROM lusers WHERE name = 'jegmond2'),
	(SELECT lusergroup FROM lusergroups WHERE name = 'lis-unix'),
	'subject123',
	'Jeroen Egmond',
	'j.egmond@uvt.nl',
	TRUE,
	(SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix')
);
INSERT INTO mailings (luser, lusergroup, mailsubject, mailfromname, mailfromemail, mailfromapproved, addresslist) VALUES (
	(SELECT luser FROM lusers WHERE name = 'jegmond2'),
	(SELECT lusergroup FROM lusergroups WHERE name = 'Jeroen Egmond 2 (myself)'),
	'myself',
	'Jeroen Egmond',
	'j.egmond@uvt.nl',
	TRUE,
	(SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix')
);

INSERT INTO mailings (luser, lusergroup, mailsubject, mailfromname, mailfromemail, mailfromapproved, addresslist) VALUES (
	(SELECT luser FROM lusers WHERE name = 'wsl'),
	(SELECT lusergroup FROM lusergroups WHERE name = 'tisem-ict-office'),
	'mailing2',
	'Jeroen Egmond',
	'j.egmond@uvt.nl',
	TRUE,
	(SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix')
);

INSERT INTO mailings (luser, lusergroup, mailsubject, mailfromname, mailfromemail, mailfromapproved, addresslist, mtime, proofread_when, submitted_when, approved_when) VALUES (
	(SELECT luser FROM lusers WHERE name = 'wsl'),
	(SELECT lusergroup FROM lusergroups WHERE name = 'lis-unix'),
	'mailing3',
	'Jeroen Egmond',
	'j.egmond@uvt.nl',
	TRUE,
	(SELECT addresslist FROM addresslists WHERE name = 'Testlijst-002-unix'),
	CURRENT_TIMESTAMP - '4 minutes'::INTERVAL,
	CURRENT_TIMESTAMP - '3 minutes'::INTERVAL,
	CURRENT_TIMESTAMP - '2 minutes'::INTERVAL,
	CURRENT_TIMESTAMP - '1 minutes'::INTERVAL
);

INSERT INTO mailbodies (mailing, bodytext) VALUES ((SELECT mailing FROM mailings WHERE mailsubject = 'subject123'), 'Lorem ipsum');
INSERT INTO mailbodies (mailing, bodytext) VALUES ((SELECT mailing FROM mailings WHERE mailsubject = 'myself'), 'me myself and I');
INSERT INTO mailbodies (mailing, bodytext) VALUES ((SELECT mailing FROM mailings WHERE mailsubject = 'mailing2'), 'Hallo lezer, bla bla bla, Mvg, de schrijver');
INSERT INTO mailbodies (mailing, bodytext) VALUES ((SELECT mailing FROM mailings WHERE mailsubject = 'mailing3'), '<p>Hallo lezer, bla bla bla, <i>Mvg, de schrijver</i></p>');

INSERT INTO attachments (luser, mailing, filename, filesize, download, contents) VALUES ((SELECT luser FROM lusers WHERE name = 'jegmond2'), (SELECT mailing FROM mailings WHERE mailsubject = 'mailing3'), 'bestandsnaam.txt', 61, true, '\x534739704c43414e4367304b5a476c3049476c7a4947566c6269426864485268593268745a57353049485a76623349676257467062473170626942305a584e306377304b4451704b5a584a765a57344e43673d3d');

INSERT INTO attachmentkeys (attachment, key) VALUES ((SELECT attachment FROM attachments LIMIT 1), '56778c70-eb3b-41fb-bee4-e51bbe746f45');

COMMIT;
