-- agda-db-1.0.sql

-- Hostname: ---
-- Port: ---
-- Username: ---
-- Pasword: ---
-- Visual diagram made with app.quickdatabasediagrams.com


-- Creates Tables
CREATE TABLE users (
	ukey SERIAL,
	ufname varchar(24) NOT NULL,
	ulname varchar(24) NOT NULL,
	udisplay varchar(64),
	upicture varchar UNIQUE,
	uemail varchar(128) NOT NULL UNIQUE,
	uphone varchar(16),
	uposition varchar(64),
	urole varchar(32) NOT NULL,
	upassword varchar(128) NOT NULL,
	ulastlogin timestamp,
	ualtuser integer,
	uavailable boolean,
	PRIMARY KEY (ukey),
	FOREIGN KEY (ualtuser) REFERENCES users(ukey)
		ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE type (
	tptype varchar(32),
	tpsubtype varchar(32),
	tpsections text[] NOT NULL,
	PRIMARY KEY (tptype, tpsubtype)
);

CREATE TABLE matter (
	mtkey SERIAL,
	mttype varchar(32) NOT NULL,
	mtsubtype varchar(32) NOT NULL,
	mtname varchar(128) NOT NULL,
	mttitle varchar(256) NOT NULL,
	mtcontract varchar(128) NOT NULL UNIQUE,
	mtuser integer NOT NULL,
	mtassignee integer NOT NULL,
	mtlastuser integer NOT NULL,
	mtcreatedate timestamp NOT NULL,
	mtlastedited timestamp NOT NULL,
	mtpriority varchar(16),
	mtdeadline date,
	mtsections json NOT NULL,
	PRIMARY KEY (mtkey),
	FOREIGN KEY (mttype, mtsubtype) REFERENCES type(tptype, tpsubtype)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (mtuser) REFERENCES users(ukey)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (mtassignee) REFERENCES users(ukey)
		ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE history (
	hskey SERIAL,
	hsmatter integer NOT NULL,
	hsuser integer NOT NULL,
	hslastuser integer NOT NULL,
	hstimestamp timestamp NOT NULL,
	hslink varchar NOT NULL,
	hsversion varchar NOT NULL UNIQUE,
	PRIMARY KEY (hskey),
	FOREIGN KEY (hsmatter) REFERENCES matter(mtkey)
		ON DELETE CASCADE ON UPDATE CASCADE
);


-- DROP TABLE users, type, matter, history;
