# === (1) GANTI sesuai kebutuhanmu ===
$DB   = "appdb"                 # nama database
$USER = "appuser"               # nama user aplikasi
$PASS = "StrongPass123!"        # password user aplikasi

# === (2) Login pakai superuser 'postgres' di port 5433 ===
$env:PGPASSWORD = "PASSWORD_POSTGRES_KAMU"   # ganti dgn password user postgres
$PSQL = "C:\Program Files\PostgreSQL\16\bin\psql.exe"

# escape tanda kutip tunggal di password agar aman di SQL
$PASS_SQL = $PASS -replace "'", "''"

# === (3) Buat USER + DATABASE ===
& $PSQL -h localhost -p 5433 -U postgres -d postgres -v ON_ERROR_STOP=1 -c "CREATE ROLE $USER LOGIN PASSWORD '$PASS_SQL';"
& $PSQL -h localhost -p 5433 -U postgres -d postgres -v ON_ERROR_STOP=1 -c "CREATE DATABASE $DB WITH OWNER $USER ENCODING 'UTF8';"

# === (4) Atur hak & schema di DB baru ===
& $PSQL -h localhost -p 5433 -U postgres -d $DB -v ON_ERROR_STOP=1 -c "ALTER SCHEMA public OWNER TO $USER;"
& $PSQL -h localhost -p 5433 -U postgres -d $DB -v ON_ERROR_STOP=1 -c "GRANT ALL PRIVILEGES ON DATABASE $DB TO $USER;"
& $PSQL -h localhost -p 5433 -U postgres -d $DB -v ON_ERROR_STOP=1 -c "GRANT ALL ON SCHEMA public TO $USER;"

# (opsional) ekstensi umum
& $PSQL -h localhost -p 5433 -U postgres -d $DB -v ON_ERROR_STOP=1 -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"
& $PSQL -h localhost -p 5433 -U postgres -d $DB -v ON_ERROR_STOP=1 -c "CREATE EXTENSION IF NOT EXISTS ""uuid-ossp"";"

# bersihkan env var
Remove-Item Env:\PGPASSWORD