#!/usr/bin/env bash
# wget -O /tmp/postgres_new_db_user.sh https://git.technozone.com.au/vijay/Scripts/raw/branch/main/postgres_new_db_user && bash /tmp/postgres_new_db_user.sh && rm -f /tmp/postgres_new_db_user.sh
# The script lists the docker containers to select or run it on the postgres server direclty.


#!/bin/sh

echo "=== PostgreSQL Docker Database & User Setup (POSIX SH Mode) ==="

printf "Enter new database name: "
read DB_NAME
printf "Enter new username: "
read DB_USER
printf "Enter new password: "
read DB_PASS

echo ""
echo "Scanning Docker containers exposing PostgreSQL ports (5432)..."

# Capture containers with 5432 published
CONTAINER_LIST=$(docker ps --format "{{.Names}} {{.Ports}}" | grep "5432/tcp" | awk '{print $1}')

# Check if any match
if [ -z "$CONTAINER_LIST" ]; then
    echo "❌ No containers exposing port 5432 found."
    echo "Available containers:"
    docker ps --format " - {{.Names}} | {{.Image}} | {{.Ports}}"
    exit 1
fi

echo ""
echo "Select Postgres container:"

i=1
echo "$CONTAINER_LIST" | while IFS= read -r cname; do
    echo "$i) $cname"
    i=$((i+1))
done

# Re-print list into an indexed variable storage
i=1
for cname in $CONTAINER_LIST; do
    eval "C$i=\"$cname\""
    i=$((i+1))
done

TOTAL=$((i-1))

printf "Enter choice [1-$TOTAL]: "
read choice

# Validate choice
case $choice in
    ''|*[!0-9]*)
        echo "❌ Invalid input"
        exit 1
        ;;
esac

if [ "$choice" -lt 1 ] || [ "$choice" -gt "$TOTAL" ]; then
    echo "❌ Choice out of range"
    exit 1
fi

# Get selected container
eval "PG_CONTAINER=\$C$choice"

echo ""
echo "✔ Selected container: $PG_CONTAINER"

# Wrapper for docker psql
run_psql() {
    docker exec -i "$PG_CONTAINER" sh -c "$1"
}

echo "Creating role if missing..."
run_psql "psql -U postgres -tc \"SELECT 1 FROM pg_roles WHERE rolname='$DB_USER'\" | grep -q 1 || psql -U postgres -c \"CREATE ROLE $DB_USER WITH LOGIN PASSWORD '$DB_PASS';\""

echo "Creating database if missing..."
run_psql "psql -U postgres -tc \"SELECT 1 FROM pg_database WHERE datname='$DB_NAME'\" | grep -q 1 || psql -U postgres -c \"CREATE DATABASE $DB_NAME OWNER $DB_USER;\""

echo "Granting privileges..."
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT CONNECT, TEMPORARY ON DATABASE $DB_NAME TO $DB_USER;\""
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT USAGE, CREATE ON SCHEMA public TO $DB_USER;\""
run_psql "psql -U postgres -d $DB_NAME -c \"ALTER SCHEMA public OWNER TO $DB_USER;\""
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $DB_USER;\""
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $DB_USER;\""

run_psql "PGPASSWORD='$DB_PASS' psql -U $DB_USER -d $DB_NAME -c \"ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO $DB_USER;\""
run_psql "PGPASSWORD='$DB_PASS' psql -U $DB_USER -d $DB_NAME -c \"ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO $DB_USER;\""

echo ""
echo "=== Setup Complete ==="
echo "Database: $DB_NAME"
echo "User: $DB_USER"
echo "Container: $PG_CONTAINER"
echo "Permissions: Full access granted"