Postgrest sample sql dv

-- Here Do some cleanup
DROP DATABASE IF EXISTS trollrepellent;

CREATE DATABASE trollrepellent TEMPLATE = template0;

\connect trollrepellent

create extension if not exists pgcrypto;
create extension if not exists "uuid-ossp";


drop role if exists anon;
drop role if exists guest_user;

create role anon;
create role guest_user;

create schema basic_auth;

create table if not exists
basic_auth.users (
	id serial primary key,
	email varchar(255) unique check ( email ~* '^.+@.+\..+$' ),
	pass varchar(255) not null,
	name varchar(255) not null default '',
	role varchar(32) not null,
	verified boolean not null default false,
	created_at timestamp not null default current_timestamp
);

drop type if exists token_type_enum cascade;
create type token_type_enum as enum ('validation', 'reset', 'login');

create table if not exists
basic_auth.tokens (
	token uuid primary key,
	token_type token_type_enum not null,
	user_id int not null REFERENCES basic_auth.users (id)
		on delete cascade on update cascade,
	created_at timestamp not null default current_timestamp,
	expires_at timestamp not null
);

create table external_tokens (
	id SERIAL PRIMARY KEY,
	user_id integer REFERENCES basic_auth.users (id) ON DELETE CASCADE,
	site varchar(32) not null,
	token varchar(32) not null,
	created_at timestamp not null default current_timestamp
);

/*
 * Check User Role
 *
 */
create or replace function
basic_auth.check_role_exists() returns trigger
	language plpgsql
	as $$
begin
	if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
		raise foreign_key_violation using message =
			'unknown database role: ' || new.role;
		return null;
	end if;
	return new;
end
$$;

drop trigger if exists ensure_user_role_exists on basic_auth.users;
create constraint trigger ensure_user_role_exists
	after insert or update on basic_auth.users
	for each row
	execute procedure basic_auth.check_role_exists();


/*
 * Encrypt Password
 *
 */

create or replace function
basic_auth.encrypt_pass() returns trigger
	language plpgsql
	as $$
begin
	if TG_OP = 'INSERT' or new.pass <> old.pass then 
		new.pass = crypt(new.pass, gen_salt('md5'));
	end if;
	return new;
end
$$;


drop trigger if exists encrypt_pass on basic_auth.users;
create trigger encrypt_pass
	before insert or update on basic_auth.users
	for each row
	execute procedure basic_auth.encrypt_pass();

/*
 * generate Signin token when they create user the first time
 *
 */

create or replace function
basic_auth.send_validation() returns trigger
	language plpgsql
	as $$
declare
	user_id int;
	tok uuid;
begin
	select uuid_generate_v4() into tok;

	insert into basic_auth.tokens (token, token_type, user_id, expires_at)
		values (tok, 'login', new.id, now() + interval '1 week');

	perform pg_notify('validate',
		json_build_object(
			'email', new.email,
			'token', tok,
			'token_type', 'login'
		)::text
	);
	return new;
end
$$;

drop trigger if exists send_validation on basic_auth.users;
create trigger send_validation
	after insert on basic_auth.users
	for each row
	execute procedure basic_auth.send_validation();


create or replace function
basic_auth.user_role(email text, pass text) returns name
	language plpgsql
	as $$
begin
	return (
		select role from basic_auth.users
		where users.email = user_role.email
			and users.pass = crypt(user_role.pass, users.pass)
	);
end;
$$;


create or replace function
basic_auth.current_email() returns text
	language plpgsql
	as $$
begin
	return current_setting('postgres.claims.email');
exception
	-- handle unrecognized configuration parameter error
	when undefined_object then return '';
end;
$$;

create or replace function
request_password_reset(email text) returns void
	language plpgsql
	as $$
declare
	tok uuid;
begin 
	delete from basic_auth.tokens
	where token_type = 'reset'
		and tokens.email = request_password_reset.email;
	
	select uuid_generate_v4() into tok;

	insert into basic_auth.tokens (token, token_type, email, expires_at)
		values (tok, 'reset', request_password_reset.email, now + interval '4 hours');
	perform pg_notify(
		json_build_object(
			'email', request_password_reset.email,
			'token', tok,
			'token_type', 'reset'
		)::text
	);
end;
$$;

create or replace function
reset_password(email text, token uuid, pass text)
	returns void
	language plpgsql
	as $$
declare
	tok uuid;
begin
	if exists(select 1 from basic_auth.tokens
		where tokens.email = reset_password.email
			and tokens.token = reset_password.token
			and tokens.token_type = 'reset'
			and tokens.expires_at > now())
	then
		update basic_auth.users set pass=reset_password.pass
			where users.email = reset_password.email;

		delete from basic_auth.tokens
		where tokens.email = reset_password.email
			and tokens.token = reset_password.token
			and token_type = 'reset';
	else
		raise invalid_password using message =
		'invalid user or token';
	end if;

	/* Now Clear up all old tokens from password reset */
	delete from basic_auth.tokens
	where token_type = 'reset'
		and tokens.email = reset_password.email;
	
	select uuid_generate_v4() into tok;
	insert into basic_auth.tokens (token, token_type, email)
		values (tok, 'login', reset_password.email);
	
	perform pg_notify(
		json_build_object(
			'email', reset_password.email,
			'token', tok
		)::text
	);
end;
$$;

create or replace function
login(email_ text, pass_ text) returns json
language plpgsql as $$
declare
	return_id int;
	return_name varchar(255);
	new_session_id uuid;
	message varchar(255);
	success boolean;
	found_user basic_auth.users;
	member_can_login boolean;
	old_sessions uuid;
begin
	select false into success;
	select 'Invalid username or password' into message;

	select users.id
	from basic_auth.users as users
	where users.email = email_
	and users.pass = crypt(pass_, users.pass)
	into return_id;

	if not return_id is null then
		-- make sure the user has confirmed their email
		select users.verified
		from basic_auth.users as users
		where users.id = return_id
		into member_can_login;

		if member_can_login then
			-- You can login! and we have an ID
			select true into success;
			select 'Successfully logged in' into message;

			select *
			from basic_auth.users as users
			where users.id = return_id
			into found_user;

			-- Find and remove all old tokens
			if exists(select token from basic_auth.tokens where user_id = return_id and expires_at > now()) then
				update basic_auth.tokens
				set expires_at = now()
				where user_id = return_id
				and expires_at >= now();
			end if;

			-- generate a new session
			select uuid_generate_v4() into new_session_id;

			insert into basic_auth.tokens (token, token_type, user_id, expires_at)
				values (new_session_id, 'login', return_id, now() + interval '1 week');

		else
			select 'You must verify your email first' into message;

		end if;

	end if;


	return json_build_object(
		'id', return_id,
		'email', found_user.email,
		'success', success,
		'token', new_session_id,
		'message', message,
		'name', found_user.name
		)::text;
end;
$$;

create or replace function
signup(email text, pass text) returns void
language plpgsql as $$
begin
	insert into basic_auth.users (email, pass, role) values
		(signup.email, signup.pass, 'guest_user');
end;
$$;

insert into basic_auth.users (email, pass, name, role, verified)
values
('khasan222@gmail.com', 'testpassword1', 'Khalah Jones Golden', 'guest_user', true);
('puhrez@gmail.com', 'testpassword1', 'Michael Perez', 'guest_user', true);