#!/usr/bin/env bash

logger "ExecStartPost script executed init database"

DB_USER="postgres"
DB_NAME="access_syslog"
TABLE_NAME="json_systemlog"
DB_PORT=5435

function create_user_and_grant_privileges() {
	local database=$1
	echo "Creating user and database '$database'"
	psql -v ON_ERROR_STOP=1 -U $DB_USER -p $DB_PORT <<-EOSQL
	    CREATE USER "$database";
	    GRANT ALL PRIVILEGES ON DATABASE "$database" TO "$database";
EOSQL
}

function grant_all_privileges() {
    local database=$1
    echo "Granting all privileges to user '$database'"
    psql -v ON_ERROR_STOP=1 -U $DB_USER -p $DB_PORT -d "$database" <<-EOSQL
        GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "$database";
        GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "$database";
EOSQL
}

# Revoke on every start (not just first init) so consoles that were
# provisioned before this fix also lose the postgres role membership.
function revoke_superuser_role() {
    local database=$1
    echo "Ensuring '$database' is not a member of '$DB_USER'"
    psql -v ON_ERROR_STOP=1 -U $DB_USER -p $DB_PORT <<-EOSQL
	    DO \$\$
	    BEGIN
	        IF EXISTS (
	            SELECT 1 FROM pg_auth_members m
	            JOIN pg_roles r ON r.oid = m.roleid
	            JOIN pg_roles u ON u.oid = m.member
	            WHERE r.rolname = '$DB_USER' AND u.rolname = '$database'
	        ) THEN
	            EXECUTE format('REVOKE %I FROM %I', '$DB_USER', '$database');
	        END IF;
	    END
	    \$\$;
EOSQL
}


MAX_RETRIES=30
RETRY_COUNT=0
until pg_isready -q -U "$DB_USER" -p "$DB_PORT"; do
  RETRY_COUNT=$((RETRY_COUNT+1))
  if [ "$RETRY_COUNT" -ge "$MAX_RETRIES" ]; then
    echo "PostgreSQL failed to start after $MAX_RETRIES attempts."
    exit 1
  fi
  echo "Waiting for PostgreSQL to start... (attempt $RETRY_COUNT)"
  sleep 3
done

DB_EXIST=$(psql -U $DB_USER -p $DB_PORT -AXqtc "SELECT 1 FROM pg_database WHERE datname = '$DB_NAME';")

if [[ $DB_EXIST != 1 ]]; then
    echo "Database $DB_NAME does not exist. Creating..."
    psql -U $DB_USER -p $DB_PORT -AXqtc "CREATE DATABASE $DB_NAME;"
    create_user_and_grant_privileges $DB_NAME
    grant_all_privileges $DB_NAME
else
    echo "Database $DB_NAME already exists."
fi

revoke_superuser_role $DB_NAME

# drop old database to keep compatibility
DB_EXIST=$(psql -U $DB_USER -p $DB_PORT -AXqtc "SELECT 1 FROM pg_database WHERE datname = 'ulp-go-syslog';")
if [[ $DB_EXIST == 1 ]]; then
    psql -v ON_ERROR_STOP=1 -U $DB_USER -p $DB_PORT <<-EOSQL
      DROP DATABASE IF EXISTS "ulp-go-syslog";
EOSQL
  echo "Clean Previous database ulp-go-syslog"
fi

exit 0

