InitPostgres.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

DO $$ BEGIN
	CREATE TYPE UserTier AS ENUM ('FREE', 'ULTIMATE');
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS Users (
	Username VARCHAR(30) NOT NULL,
	Email VARCHAR(254) NOT NULL,
	Password CHAR(60) NOT NULL,
	Tier UserTier NOT NULL,
	PRIMARY KEY (Username)
);

DO $$ BEGIN
	CREATE TYPE GitHost AS ENUM ('GITHUB', 'GITLAB', 'BITBUCKET');
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS GitRepos (
	RepoID UUID DEFAULT gen_random_uuid(),
	Username VARCHAR(30) NOT NULL,
	GitHost GitHost NOT NULL,
	HostUsername VARCHAR NOT NULL,
	RepoName VARCHAR NOT NULL,
	Branch VARCHAR NOT NULL,
	LatestBuildID UUID,
	PRIMARY KEY (RepoID),
	FOREIGN KEY (Username) REFERENCES Users (Username)
);

DO $$ BEGIN
	CREATE TYPE BuildStatus AS ENUM ('SUCCESS', 'FAILED', 'TIMED_OUT', 'RUNTIME_ERROR');
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS Builds (
	BuildID UUID DEFAULT gen_random_uuid(),
	RepoID UUID NOT NULL,
	GitBranch VARCHAR NOT NULL,
	CommitHash CHAR(40) NOT NULL,
	Committer VARCHAR NOT NULL,
	StartTime TIMESTAMP WITH TIME ZONE NOT NULL,
	Status BuildStatus NOT NULL,
	PRIMARY KEY (BuildID),
	FOREIGN KEY (RepoID) REFERENCES GitRepos (RepoID)
);

CREATE TABLE IF NOT EXISTS Notes (
	NoteID UUID DEFAULT gen_random_uuid(),
	BuildID UUID NOT NULL,
	HTML TEXT NOT NULL,
	Markdown TEXT NOT NULL,
	Title TEXT NOT NULL,
	Private BOOLEAN NOT NULL DEFAULT TRUE,
	Slug TEXT NOT NULL,
	Categories VARCHAR[],
	PRIMARY KEY (NoteID),
	FOREIGN KEY (BuildID) REFERENCES Builds (BuildID)
);