-- +brant Up
CREATE TYPE visibility AS ENUM (
	'PUBLIC',
	'PRIVATE',
	'UNLISTED'
);

CREATE TYPE user_type AS ENUM (
	'PENDING',
	'USER',
	'ADMIN',
	'SUSPENDED'
);

CREATE TYPE access_mode AS ENUM (
	'RO',
	'RW'
);

CREATE TABLE "user" (
	id integer PRIMARY KEY,
	username varchar(256) UNIQUE,
	created timestamp NOT NULL,
	updated timestamp NOT NULL,
	email varchar(256) NOT NULL UNIQUE,
	user_type user_type NOT NULL,
	url varchar(256),
	location varchar(256),
	bio varchar(4096),
	suspension_notice varchar(4096)
);
CREATE INDEX ix_user_username ON "user"(username);

CREATE TABLE repository (
	id serial PRIMARY KEY,
	created timestamp NOT NULL,
	updated timestamp NOT NULL,
	name varchar(64) NOT NULL,
	description varchar(1024),
	owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	path varchar(1024) NOT NULL UNIQUE,
	visibility visibility NOT NULL,
	CONSTRAINT uq_repo_owner_id_name UNIQUE (owner_id, name)
);

CREATE TABLE access (
	id serial PRIMARY KEY,
	created timestamp NOT NULL,
	updated timestamp NOT NULL,
	repo_id integer NOT NULL REFERENCES repository(id) ON DELETE CASCADE,
	user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	mode access_mode NOT NULL,
	CONSTRAINT uq_access_user_id_repo_id UNIQUE (user_id, repo_id)
);

CREATE TABLE dolt_key (
	id serial PRIMARY KEY,
	created timestamp NOT NULL,
	user_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
	kid varchar(64) NOT NULL UNIQUE,
	pubkey bytea NOT NULL,
	comment varchar(256),
	last_used timestamp
);

-- +brant Down
DROP TABLE dolt_key;
DROP TABLE access;
DROP TABLE repository;
DROP TABLE "user";
DROP TYPE access_mode;
DROP TYPE user_type;
DROP TYPE visibility;