-- PostgreSQL Database and Schema Creation Script
-- This script creates a new database, user, and schema for AxiUm migration

\echo 'Starting database and schema creation...'

-- Drop existing database and user if they exist
-- First, terminate all sessions connected to the database
\echo 'Terminating all sessions connected to database: ' :"POSTGRES_DATABASE"
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = :'POSTGRES_DATABASE' 
  AND pid <> pg_backend_pid();

-- Now drop the database
DROP DATABASE IF EXISTS :"POSTGRES_DATABASE";
DROP USER IF EXISTS :"POSTGRES_USER";

\echo 'Creating database: ' :"POSTGRES_DATABASE" 
CREATE DATABASE :"POSTGRES_DATABASE";

-- connect to new DB
\echo 'Connecting to new database: ' :"POSTGRES_DATABASE"
\c :"POSTGRES_DATABASE";

\echo 'Creating required extensions...'
CREATE EXTENSION IF NOT EXISTS pg_background;
CREATE EXTENSION IF NOT EXISTS pg_dbms_lock;
CREATE EXTENSION IF NOT EXISTS pgtt;
CREATE EXTENSION IF NOT EXISTS spl_check;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

\echo 'Creating user: ' :"POSTGRES_USER"
CREATE USER :"POSTGRES_USER" WITH PASSWORD :'POSTGRES_USER_PASSWORD';

\echo 'Creating schema: ' :"POSTGRES_SCHEMA"
CREATE SCHEMA :"POSTGRES_SCHEMA" AUTHORIZATION :"POSTGRES_USER";
\echo 'Granting database privileges...'
GRANT ALL PRIVILEGES ON DATABASE :"POSTGRES_DATABASE" TO :"POSTGRES_USER";

\echo 'Granting schema privileges...'
GRANT USAGE ON SCHEMA :"POSTGRES_SCHEMA" TO :"POSTGRES_USER";
GRANT ALL PRIVILEGES ON SCHEMA :"POSTGRES_SCHEMA" TO :"POSTGRES_USER";
ALTER ROLE :"POSTGRES_USER" IN DATABASE :"POSTGRES_DATABASE" SET search_path TO :"POSTGRES_SCHEMA", public;
GRANT CREATE ON SCHEMA :"POSTGRES_SCHEMA" TO :"POSTGRES_USER";

\echo 'Granting object privileges...'
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA :"POSTGRES_SCHEMA" TO :"POSTGRES_USER";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA :"POSTGRES_SCHEMA" TO :"POSTGRES_USER";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA :"POSTGRES_SCHEMA" TO :"POSTGRES_USER";

\echo 'Granting Oracle compatibility privileges...'
GRANT SELECT ON DBA_USERS TO :"POSTGRES_USER";
GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO :"POSTGRES_USER";
GRANT EXECUTE ON PACKAGE SYS.UTL_SMTP TO :"POSTGRES_USER";
GRANT EXECUTE ON PACKAGE SYS.UTL_TCP TO :"POSTGRES_USER";
GRANT EXECUTE ON PACKAGE htf to :"POSTGRES_USER";
GRANT pg_monitor TO :"POSTGRES_USER";

\echo 'Granting pgtt schema privileges...'
GRANT ALL ON SCHEMA pgtt_schema TO :"POSTGRES_USER";

\echo 'Database and schema creation completed successfully!'