-- dolt.sr.ht full initial schema.
--
-- This is the authoritative DDL for a fresh install. The brant migration in
-- migrations/0001_initial.sql applies the same objects incrementally; keep the
-- two in sync. The "user" table mirrors meta's users (core-go's
-- FetchMetaProfile lazily inserts rows keyed by meta's user id).

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,               -- meta's user id, inserted explicitly (not serial)
	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,   -- absolute on-disk NBS store dir
	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,      -- base32(SHA-512/224(pubkey)), dolt alphabet
	pubkey bytea NOT NULL,                -- 32-byte ed25519 public key
	comment varchar(256),
	last_used timestamp
);