#!/usr/bin/env bash
set -euo pipefail
psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" \
  --set ON_ERROR_STOP=1 --set app_password="$LAB_APP_PASSWORD" <<'SQL'
CREATE ROLE lab_app LOGIN PASSWORD :'app_password';
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE TABLE account (id integer PRIMARY KEY, balance_cents bigint NOT NULL CHECK (balance_cents >= 0));
CREATE TABLE ledger (entry_key text PRIMARY KEY, account_id integer NOT NULL REFERENCES account, delta_cents bigint NOT NULL);
CREATE TABLE import_item (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, external_key text NOT NULL UNIQUE, amount_cents integer NOT NULL);
INSERT INTO account VALUES (1, 1000);
GRANT CONNECT ON DATABASE transaction_lab TO lab_app;
GRANT USAGE ON SCHEMA public TO lab_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO lab_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO lab_app;
SQL