# Pastebin iCP6hstw
CREATE SCHEMA oauth;

CREATE TABLE oauth."user" (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	name TEXT NOT NULL, 
	email TEXT, 
	unconfirmed_email TEXT, 
	website TEXT, 
	member_since TIMESTAMP WITH TIME ZONE, 
	email_confirm_date TIMESTAMP WITH TIME ZONE, 
	last_login_date TIMESTAMP WITH TIME ZONE, 
	last_updated TIMESTAMP WITH TIME ZONE, 
	birth_date DATE, 
	gender INTEGER, 
	password TEXT NOT NULL, 
	ha1 TEXT NOT NULL, 
	deleted BOOLEAN, 
	PRIMARY KEY (id)
);

CREATE TABLE oauth.scope (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	name TEXT NOT NULL, 
	description TEXT NOT NULL, 
	PRIMARY KEY (id)
);

CREATE TABLE oauth.client (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	client_id TEXT NOT NULL, 
	client_secret TEXT, 
	owner_id INTEGER NOT NULL, 
	name TEXT NOT NULL, 
	description TEXT NOT NULL, 
	website TEXT, 
	redirect_uris TEXT[] NOT NULL, 
	client_id_issued_at TIMESTAMP WITH TIME ZONE NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(owner_id) REFERENCES oauth."user" (id) ON DELETE CASCADE
);

CREATE TABLE oauth.code (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	user_id INTEGER NOT NULL, 
	client_id INTEGER NOT NULL, 
	code TEXT NOT NULL, 
	redirect_uri TEXT NOT NULL, 
	response_type TEXT NOT NULL, 
	code_challenge TEXT, 
	code_challenge_method TEXT, 
	granted_at TIMESTAMP WITH TIME ZONE, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES oauth."user" (id) ON DELETE CASCADE, 
	FOREIGN KEY(client_id) REFERENCES oauth.client (id) ON DELETE CASCADE, 
	UNIQUE (code)
);

CREATE TABLE oauth.l_code_scope (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	code_id INTEGER NOT NULL, 
	scope_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(code_id) REFERENCES oauth.code (id) ON DELETE CASCADE, 
	FOREIGN KEY(scope_id) REFERENCES oauth.scope (id) ON DELETE CASCADE
);

CREATE TABLE oauth.token (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	user_id INTEGER NOT NULL, 
	client_id INTEGER NOT NULL, 
	access_token TEXT NOT NULL, 
	refresh_token TEXT, 
	issued_at TIMESTAMP WITH TIME ZONE, 
	expires_in INTEGER, 
	revoked BOOLEAN, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES oauth."user" (id) ON DELETE CASCADE, 
	FOREIGN KEY(client_id) REFERENCES oauth.client (id) ON DELETE CASCADE, 
	UNIQUE (access_token)
);

CREATE TABLE oauth.l_token_scope (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY, 
	token_id INTEGER NOT NULL, 
	scope_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(token_id) REFERENCES oauth.token (id) ON DELETE CASCADE, 
	FOREIGN KEY(scope_id) REFERENCES oauth.scope (id) ON DELETE CASCADE
);