\set db_suffix _db
\set user_suffix _user

DROP DATABASE :v1:db_suffix;
DROP USER :v1:user_suffix;

CREATE DATABASE :v1:db_suffix;

-- connect to new DB
\c :v1:db_suffix;


CREATE EXTENSION pg_background;
CREATE EXTENSION pg_dbms_lock;
CREATE EXTENSION pgtt;
CREATE EXTENSION spl_check;


CREATE USER :v1:user_suffix WITH PASSWORD :'v1';
CREATE SCHEMA :v1 AUTHORIZATION :v1:user_suffix;
GRANT ALL PRIVILEGES ON DATABASE :v1:db_suffix TO :v1:user_suffix;
GRANT USAGE ON SCHEMA :v1 TO :v1:user_suffix;
GRANT ALL PRIVILEGES ON SCHEMA :v1 TO :v1:user_suffix;
ALTER ROLE :v1:user_suffix IN DATABASE :v1:db_suffix SET search_path TO :v1, public;
GRANT CREATE ON SCHEMA :v1 TO :v1:user_suffix;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA :v1 TO :v1:user_suffix;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA :v1 TO :v1:user_suffix;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA :v1 TO :v1:user_suffix;
GRANT SELECT ON DBA_USERS TO :v1:user_suffix;
GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO :v1:user_suffix;
GRANT EXECUTE ON PACKAGE SYS.UTL_SMTP TO :v1:user_suffix;
GRANT EXECUTE ON PACKAGE SYS.UTL_TCP TO :v1:user_suffix;
GRANT EXECUTE ON PACKAGE htf to :v1:user_suffix;
GRANT pg_monitor TO :v1:user_suffix;

GRANT ALL ON SCHEMA pgtt_schema TO :v1:user_suffix;