#!/bin/bash
set -e

# n8n Database Initialization
# Creates the n8n role and its own n8n database (workflow storage).
# Runs at first boot of a database volume (/docker-entrypoint-initdb.d) and,
# in local development, again on every `vibecarbon up` (scripts/docker-up.js).
# A server never reruns it: what must hold for tables created later is
# enforced by the app's migration runner, not here (see block 3).
# The role's password is its own, N8N_DB_PASSWORD (generated by `vibecarbon add`,
# healed by `vibecarbon upgrade`), never the superuser's: the db accepts
# password logins for any role from the compose network, so a shared password
# would let whoever holds the add-on's credential log in as `postgres`. The
# db service gets it in its env from the add-on's compose file.
: "${N8N_DB_PASSWORD:?N8N_DB_PASSWORD is not set: run vibecarbon upgrade}"

# The n8n role is also the APP_DB_* credential
# workflows use to reach the app's data (docker-compose.n8n.yml), which anyone
# who can edit a workflow can drive. So it is never a superuser: a superuser
# can run `COPY ... TO PROGRAM`, a shell in the db container. BYPASSRLS lets it
# see every business row (row-level security would otherwise show it none),
# and the grants below decide which tables it may touch at all. A role
# created by hand or by an older version is brought back to this. Quoted
# heredoc: no variable expansion.
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<'EOSQL'
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'n8n') THEN
        CREATE ROLE n8n WITH LOGIN;
    END IF;
END
$$;
ALTER ROLE n8n NOSUPERUSER BYPASSRLS;
EOSQL

# Set the password and create the role's own database. The password is read from the
# container env by psql (\getenv, so it is never in the process list) into a
# variable quoted with :'pw', so psql does the SQL-literal escaping and the
# shell never splices it into SQL (quoted heredoc). The ALTER ROLE still
# carries it as a literal, and a failing or slow statement's text goes to the
# server log (log_min_error_statement = error, log_min_duration_statement), so
# this session turns both off first. CREATE DATABASE cannot run inside a transaction, so this is its
# own call.
psql -v ON_ERROR_STOP=1 \
  --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<'EOSQL'
SET log_min_error_statement = panic;
SET log_min_duration_statement = -1;
\getenv pw N8N_DB_PASSWORD
ALTER ROLE n8n WITH PASSWORD :'pw';
SELECT 'CREATE DATABASE n8n WITH OWNER n8n ENCODING ''UTF8'' TEMPLATE template0'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'n8n')\gexec
GRANT ALL PRIVILEGES ON DATABASE n8n TO n8n;
EOSQL

# The app's tables: read and write, including the ones a later migration creates
# (the default privileges cover tables the app's role, postgres, creates).
# The blanket grant also reaches the server-only tables (RLS on and no
# policies: the Better Auth tables with password hashes, session and OAuth
# tokens and TOTP secrets, and failed_login_attempts), so the same
# transaction (-1) takes those back, through the baseline's
# app.revoke_server_only_tables(), which reads the set from the catalog. If
# the revoke fails, the grant is rolled back with it. On a first boot this
# runs before the app has migrated: there are no tables yet and no function,
# and the migration runner calls the function itself after every run, which
# also covers every table a later migration adds. Quoted heredoc, so the
# shell leaves the $$ quoting alone.
psql -1 -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<'EOSQL'
GRANT CONNECT ON DATABASE postgres TO n8n;
GRANT USAGE ON SCHEMA public TO n8n;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO n8n;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO n8n;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO n8n;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO n8n;
DO $$
BEGIN
    IF to_regprocedure('app.revoke_server_only_tables()') IS NOT NULL THEN
        PERFORM app.revoke_server_only_tables();
    END IF;
END
$$;
EOSQL
