# Staging (prototypes, test data)
export PAPEROS_BASE_URL='https://staging.paperos.dev'

# Production
# export PAPEROS_BASE_URL='https://app.paperos.com'

// Staging (prototypes, test data):  https://staging.paperos.dev
// Production:                       https://app.paperos.com
var paperBase = process.env.PAPEROS_BASE_URL;

Authorization: Bearer <token>

// Express
var token = req.get("X-Auth-Request-Access-Token");

var resp = await fetch(`${paperBase}/api/v1/orgs?updated_since=0`, {
   headers: { Authorization: `Bearer ${token}` },
});

# For manual testing from a terminal, export a short-lived token you
# obtained yourself. Never commit it, log it, or paste it into chat.
export PAPEROS_TOKEN='xxxx.yyyy.zzzz'

curl "${PAPEROS_BASE_URL}/api/v1/orgs?updated_since=0" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

curl -X POST "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/access-token" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/access-token`;
var resp = await fetch(url, {
   method: "POST",
   headers: { Authorization: `Bearer ${token}` },
});
var orgToken = await resp.json();

{
   "access_token": "<ACCOUNT_ACCESS_TOKEN>",
   "token_type": "Bearer",
   "expires_in": 3600,
   "org_id": "org_01ewdxxpvgg2y19pbtbyddtvv8",
   "account_id": 97
}

curl "${PAPEROS_BASE_URL}/api/v1/orgs?updated_since=0" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs?updated_since=0`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var { orgs } = await resp.json();

{
   "updated_at": 1677000469,
   "orgs": [
      {
         "id": "org_01ewdxxpvgg2y19pbtbyddtvv8",
         "name": "Example Fund I, LP",
         "brand_id": "brand_00000000000000000000000000",
         "created_at": "2021-01-19T18:18:46.000Z",
         "updated_at": "2023-02-21T17:27:49.000Z"
      }
   ]
}

curl "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var org = await resp.json();

curl "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/reports" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/reports`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var { reports } = await resp.json();

{
   "org_id": "org_01ewdxxpvgg2y19pbtbyddtvv8",
   "reports": [
      {
         "id": 1234,
         "slug": "capital_statement_report",
         "name": "Capital Statements",
         "record_count": 42
      },
      {
         "id": 1235,
         "slug": "investor_list",
         "name": "Investor Info",
         "record_count": 18
      }
   ]
}

curl -G "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/reports/capital_statement_report" \
    --data-urlencode "offset=0" \
    --data-urlencode "limit=500" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var report = encodeURIComponent("capital_statement_report");
var params = new URLSearchParams({ offset: 0, limit: 500 });
var url = `${paperBase}/api/v1/orgs/${orgId}/reports/${report}?${params}`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var data = await resp.json();

{
   "org_id": "org_01ewdxxpvgg2y19pbtbyddtvv8",
   "report": {
      "id": 1234,
      "slug": "capital_statement_report",
      "name": "Capital Statements"
   },
   "columns": [
      { "key": "Name", "type": "string" },
      { "key": "Email", "type": "string" },
      { "key": "Ownership Percentage", "type": "string" },
      { "key": "Total Contributions", "type": "string" },
      { "key": "Inception To Date Distributions", "type": "string" },
      { "key": "Inception To Date Ending Balance", "type": "string" },
      { "key": "Capital Statement Document", "type": "string" }
   ],
   "record_count": 42,
   "offset": 0,
   "returned": 42,
   "masked_columns": [],
   "records": [
      {
         "id": 98765,
         "record_id": "rec_01hcey7qcfeeqmh1af6x3xafa2",
         "fields": {
            "Name": "Jane Doe",
            "Email": "jane@example.com",
            "Ownership Percentage": "12.5",
            "Total Contributions": "250000",
            "Inception To Date Distributions": "0",
            "Inception To Date Ending Balance": "250000",
            "Capital Statement Document": "File Uploaded"
         }
      }
   ]
}

curl -G "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/reports/capital_statement_report" \
    --data-urlencode "format=csv" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" \
    -o capital_statement_report.csv

var url = `${paperBase}/api/v1/orgs/${orgId}/reports/capital_statement_report?format=csv`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var csvText = await resp.text();

curl "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/batch-types" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/batch-types`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var { batch_types } = await resp.json();

{
   "batch_types": [
      {
         "type": "capital_statements",
         "name": "Capital Statements",
         "template_url": "/api/v1/orgs/org_01ewdxxpvgg2y19pbtbyddtvv8/batch-types/capital_statements/template"
      }
   ]
}

curl "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/batch-types/capital_statements/template" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/batch-types/capital_statements/template`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var template = await resp.json();
var headers = template.columns.map((c) => c.header);

{
   "type": "capital_statements",
   "columns": [
      { "header": "Capital Statement.name", "required": true },
      { "header": "Capital Statement.period_ending_date", "required": true },
      { "header": "Investor.name", "required": true },
      { "header": "Investor.email", "required": true },
      { "header": "Investor.send_email_yes_or_no", "required": true }
   ]
}

curl -X POST "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/batches?dry_run=true&type=capital_statements&file_name=q3-statements.csv" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" \
    -H "Content-Type: text/csv" \
    --data-binary @q3-statements.csv |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/batches?dry_run=true`;
var resp = await fetch(url, {
   method: "POST",
   headers: {
      Authorization: `Bearer ${token}`,
      "Content-Type": "application/json",
   },
   body: JSON.stringify({
      type: "capital_statements",
      file_name: "q3-statements.csv",
      csv: csvText,
   }),
});
var check = await resp.json();

{
   "dry_run": true,
   "type": "capital_statements",
   "rows": 12,
   "columns": [
      "Capital Statement.name",
      "Capital Statement.period_ending_date",
      "Investor.name",
      "Investor.email",
      "Investor.send_email_yes_or_no"
   ],
   "missing": []
}

{
   "status": 400,
   "code": "MISSING_COLUMNS",
   "message": "The CSV is missing required columns for this batch type.",
   "missing": ["Investor.email"],
   "template_url": "/api/v1/orgs/org_01ewdxxpvgg2y19pbtbyddtvv8/batch-types/capital_statements/template"
}

{
   "status": 400,
   "code": "BLANK_REQUIRED_VALUES",
   "message": "Some rows leave required columns empty. Fill them in or drop those rows.",
   "blanks": [{ "line": 2, "column": "Investor.name" }]
}

curl -X POST "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/batches?type=capital_statements&file_name=q3-statements.csv" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" \
    -H "Content-Type: text/csv" \
    --data-binary @q3-statements.csv |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/batches`;
var resp = await fetch(url, {
   method: "POST",
   headers: {
      Authorization: `Bearer ${token}`,
      "Content-Type": "application/json",
   },
   body: JSON.stringify({
      type: "capital_statements",
      file_name: "q3-statements.csv",
      csv: csvText,
   }),
});
var submission = await resp.json();

{
   "org_id": "org_01ewdxxpvgg2y19pbtbyddtvv8",
   "type": "capital_statements",
   "file_name": "q3-statements.csv",
   "file_id": "1234567890",
   "rows": 12,
   "batch_url": "https://staging.paperos.dev/portal",
   "failed_lines": [],
   "warnings": []
}

curl -G "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/records" \
    --data-urlencode "type=individual" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var params = new URLSearchParams({ type: "individual" });
var url = `${paperBase}/api/v1/orgs/${orgId}/records?${params}`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var records = await resp.json();

[
   {
      "id": 17413,
      "name": "Jane Doe",
      "resource_type_id": 1,
      "account_id": 97,
      "created_at": "2023-09-22T19:50:13.000Z",
      "updated_at": "2023-09-22T19:50:13.000Z",
      "finalized": 0,
      "archived": 0,
      "is_draft": 0,
      "features": {
         "name": "Jane Doe",
         "email": "jane@example.com"
      }
   }
]

curl "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/records/${rec_id}" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/records/${recId}`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var record = await resp.json();

curl -X POST "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/records" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" \
    -H "Content-Type: application/json" \
    --data-raw '{
        "type": "individual",
        "name": "Jane Doe",
        "fields": {
            "email": "jane@example.com"
        }
    }' |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/records`;
var resp = await fetch(url, {
   method: "POST",
   headers: {
      Authorization: `Bearer ${token}`,
      "Content-Type": "application/json",
   },
   body: JSON.stringify({
      type: "individual",
      name: "Jane Doe",
      fields: { email: "jane@example.com" },
   }),
});
var { rec_id } = await resp.json();

{
   "success": true,
   "rec_id": "rec_01hcey7qcfeeqmh1af6x3xafa2"
}

curl -X PATCH "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/records/${rec_id}" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" \
    -H "Content-Type: application/json" \
    --data-raw '{
        "fields": {
            "email": "jane.doe@example.com"
        }
    }' |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/records/${recId}`;
var resp = await fetch(url, {
   method: "PATCH",
   headers: {
      Authorization: `Bearer ${token}`,
      "Content-Type": "application/json",
   },
   body: JSON.stringify({
      fields: { email: "jane.doe@example.com" },
   }),
});
var result = await resp.json();

{
   "success": true,
   "changes": ["email"]
}

curl "${PAPEROS_BASE_URL}/api/v1/orgs/${org_id}/documents" \
    -H "Authorization: Bearer ${PAPEROS_TOKEN}" |
    jq

var url = `${paperBase}/api/v1/orgs/${orgId}/documents`;
var resp = await fetch(url, {
   headers: { Authorization: `Bearer ${token}` },
});
var { documents } = await resp.json();

{
   "success": true,
   "total": 1,
   "count": 1,
   "type": "[]<document>",
   "documents": [
      {
         "path": "/Investors/Capital Statements",
         "filename": "Q3 Capital Statement - Jane Doe.pdf",
         "recipients": [],
         "url": "https://staging.paperos.dev/api/public/documents/820701358552/eyJ0eXAiOiJKV1Qi...",
         "pub_id": "doc_0000000000p6a290bsjjqmkfk1"
      }
   ]
}

-- One row per PaperOS record you've pulled from a report.
CREATE TABLE paperos_records (
    id                  BIGSERIAL PRIMARY KEY,
    org_id              TEXT        NOT NULL,  -- e.g. org_01ewdx...
    paperos_record_id   TEXT        NOT NULL,  -- records[].record_id (rec_...)
    report_slug         TEXT        NOT NULL,  -- e.g. capital_statement_report
    fields              JSONB       NOT NULL,  -- records[].fields, keyed by display label
    last_synced_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    removed_upstream_at TIMESTAMPTZ,           -- set when missing from a full pull
    UNIQUE (org_id, paperos_record_id)
);

-- One row per batch upload attempt; prevents double submission.
CREATE TABLE batch_uploads (
    id           BIGSERIAL PRIMARY KEY,
    org_id       TEXT        NOT NULL,
    batch_type   TEXT        NOT NULL,          -- e.g. capital_statements
    file_name    TEXT        NOT NULL,
    csv_sha256   TEXT        NOT NULL,          -- hash of the exact CSV sent
    row_count    INTEGER,
    status       TEXT        NOT NULL DEFAULT 'pending',
                 -- pending | submitted | succeeded | failed | unknown
    file_id      TEXT,                          -- from the 201 response
    batch_url    TEXT,                          -- from the 201 response
    response     JSONB,                         -- failed_lines, warnings, error
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (org_id, batch_type, csv_sha256)
);

// npm install express pg
import crypto from "node:crypto";
import express from "express";
import pg from "pg";

const PAPEROS_BASE_URL = process.env.PAPEROS_BASE_URL; // https://staging.paperos.dev
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const app = express();
app.use(express.json({ limit: "5mb" }));

// Report fields are keyed by display label ("Name", "Total Contributions", ...).
// Map the labels you use to your own names in one place; labels can be renamed
// in PaperOS, so a missing label becomes null instead of an error.
const CAPITAL_STATEMENT_FIELDS = {
   name: "Name",
   email: "Email",
   ownership_pct: "Ownership Percentage",
   total_contributions: "Total Contributions",
   itd_distributions: "Inception To Date Distributions",
   itd_ending_balance: "Inception To Date Ending Balance",
};

function pickFields(fields, mapping) {
   const out = {};
   for (const [col, label] of Object.entries(mapping)) {
      out[col] = fields?.[label] ?? null;
   }
   return out;
}

// Call the Developer API as the signed-in user.
// The token is read per request and never stored or logged.
async function paperos(req, path, init = {}) {
   const token = req.get("X-Auth-Request-Access-Token");
   if (!token) {
      throw Object.assign(new Error("not signed in"), { status: 401 });
   }
   const resp = await fetch(`${PAPEROS_BASE_URL}${path}`, {
      ...init,
      headers: { ...init.headers, Authorization: `Bearer ${token}` },
      // Form uploads generate PDFs before returning (~7s for one form, longer
      // for more forms), so allow several minutes.
      signal: init.signal ?? AbortSignal.timeout(10 * 60 * 1000),
   });
   const type = resp.headers.get("content-type") || "";
   const body = type.includes("json") ? await resp.json() : await resp.text();
   if (!resp.ok) {
      throw Object.assign(new Error(body?.message || `PaperOS ${resp.status}`), {
         status: resp.status,
         code: body?.code,
         body,
      });
   }
   return body;
}

// GET /api/orgs/:orgId/reports -> the org's reports (for a picker in your UI)
app.get("/api/orgs/:orgId/reports", async (req, res, next) => {
   try {
      const orgId = encodeURIComponent(req.params.orgId);
      const { reports } = await paperos(req, `/api/v1/orgs/${orgId}/reports`);
      res.json(reports);
   } catch (err) {
      next(err);
   }
});

// POST /api/orgs/:orgId/sync/:report -> full pull of one report
app.post("/api/orgs/:orgId/sync/:report", async (req, res, next) => {
   const { orgId, report } = req.params;
   const startedAt = new Date();
   const pageSize = 5000;
   try {
      // 1. fetch every page
      const records = [];
      let reportSlug;
      for (let offset = 0; ; offset += pageSize) {
         const page = await paperos(
            req,
            `/api/v1/orgs/${encodeURIComponent(orgId)}/reports/` +
               `${encodeURIComponent(report)}?offset=${offset}&limit=${pageSize}`,
         );
         reportSlug = page.report.slug;
         records.push(...page.records);
         if (page.returned < pageSize || records.length >= page.record_count) {
            break;
         }
      }

      // 2. upsert in one transaction, 3. soft-delete what disappeared
      const db = await pool.connect();
      try {
         await db.query("BEGIN");
         for (const rec of records) {
            await db.query(
               `INSERT INTO paperos_records
                   (org_id, paperos_record_id, report_slug, fields, last_synced_at)
                VALUES ($1, $2, $3, $4, $5)
                ON CONFLICT (org_id, paperos_record_id) DO UPDATE
                   SET fields = EXCLUDED.fields,
                       report_slug = EXCLUDED.report_slug,
                       last_synced_at = EXCLUDED.last_synced_at,
                       removed_upstream_at = NULL`,
               [orgId, rec.record_id, reportSlug, rec.fields, startedAt],
            );
         }
         await db.query(
            `UPDATE paperos_records
                SET removed_upstream_at = now()
              WHERE org_id = $1 AND report_slug = $2
                AND last_synced_at < $3 AND removed_upstream_at IS NULL`,
            [orgId, reportSlug, startedAt],
         );
         await db.query("COMMIT");
      } catch (err) {
         await db.query("ROLLBACK");
         throw err;
      } finally {
         db.release();
      }

      res.json({ report: reportSlug, synced: records.length });
   } catch (err) {
      next(err);
   }
});

// GET /api/orgs/:orgId/capital-statements -> synced rows in your own shape
app.get("/api/orgs/:orgId/capital-statements", async (req, res, next) => {
   try {
      const { rows } = await pool.query(
         `SELECT paperos_record_id, fields FROM paperos_records
           WHERE org_id = $1 AND report_slug = 'capital_statement_report'
             AND removed_upstream_at IS NULL`,
         [req.params.orgId],
      );
      res.json(
         rows.map((r) => ({
            paperos_record_id: r.paperos_record_id,
            ...pickFields(r.fields, CAPITAL_STATEMENT_FIELDS),
         })),
      );
   } catch (err) {
      next(err);
   }
});

function toCsv(headers, rows) {
   const cell = (v) => {
      const s = v == null ? "" : String(v);
      return /[",\r\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
   };
   return [headers, ...rows.map((r) => headers.map((h) => r[h]))]
      .map((line) => line.map(cell).join(","))
      .join("\r\n");
}

// POST /api/orgs/:orgId/statements
// body: { file_name, rows: [{ "Investor.name": "...", "Investor.email": "...", ... }] }
// Each row's keys are template headers; map your bank/accounting data to them first.
// Use each investor's "Full Legal Name" / "Email Address" from investor_list exactly,
// or the upload creates a duplicate investor. Investor.send_email_yes_or_no = "Yes"
// emails investors their statements: keep it "No" while testing.
app.post("/api/orgs/:orgId/statements", async (req, res, next) => {
   const { orgId } = req.params;
   const type = "capital_statements";
   const base = `/api/v1/orgs/${encodeURIComponent(orgId)}`;
   let uploadId;
   let posted = false;
   try {
      // 1. template -> CSV with exactly those headers
      const template = await paperos(req, `${base}/batch-types/${type}/template`);
      const headers = template.columns.map((c) => c.header);
      const csv = toCsv(headers, req.body.rows);
      const fileName = req.body.file_name || "statements.csv";
      const hash = crypto.createHash("sha256").update(csv).digest("hex");

      // 2. ledger row; the unique key blocks re-sending the same CSV
      const ins = await pool.query(
         `INSERT INTO batch_uploads (org_id, batch_type, file_name, csv_sha256)
          VALUES ($1, $2, $3, $4)
          ON CONFLICT (org_id, batch_type, csv_sha256) DO NOTHING
          RETURNING id`,
         [orgId, type, fileName, hash],
      );
      if (!ins.rowCount) {
         return res.status(409).json({ error: "This exact CSV was already uploaded." });
      }
      uploadId = ins.rows[0].id;

      const payload = () => ({
         method: "POST",
         headers: { "Content-Type": "application/json" },
         body: JSON.stringify({ type, file_name: fileName, csv }),
      });

      // 3. dry run: validates, writes nothing
      //    (MISSING_COLUMNS, BLANK_REQUIRED_VALUES etc. throw here)
      const check = await paperos(req, `${base}/batches?dry_run=true`, payload());
      await pool.query(
         `UPDATE batch_uploads SET row_count = $2, status = 'submitted',
                 updated_at = now() WHERE id = $1`,
         [uploadId, check.rows],
      );

      // 4. upload ONCE (not idempotent: never retry automatically)
      posted = true;
      const batch = await paperos(req, `${base}/batches`, payload());
      await pool.query(
         `UPDATE batch_uploads
             SET status = 'succeeded', file_id = $2, batch_url = $3,
                 response = $4, updated_at = now()
           WHERE id = $1`,
         [uploadId, batch.file_id, batch.batch_url,
          { failed_lines: batch.failed_lines, warnings: batch.warnings }],
      );

      // 5. then re-pull the affected report (see the sync route above)
      res.status(201).json(batch);
   } catch (err) {
      if (uploadId && !posted) {
         // Failed before the real upload: nothing was sent, free the ledger slot.
         await pool.query("DELETE FROM batch_uploads WHERE id = $1", [uploadId]);
      } else if (uploadId) {
         // A 4xx means PaperOS rejected it. Anything else (5xx, timeout, network)
         // means we don't know whether it landed: check the report before resending.
         const status = err.status >= 400 && err.status < 500 ? "failed" : "unknown";
         await pool.query(
            `UPDATE batch_uploads SET status = $2, response = $3, updated_at = now()
              WHERE id = $1`,
            [uploadId, status, JSON.stringify(err.body ?? { message: err.message })],
         );
      }
      next(err);
   }
});

app.use((err, req, res, next) => {
   // Log the code and message only; never log request headers (they hold the token).
   console.error("request failed:", err.status, err.code, err.message);
   if (err.status === 401) {
      // Return through SSO; sign in again if renewal fails. Do not loop on reload.
      return res.status(401).json({ code: "UNAUTHORIZED" });
   }
   res.status(err.status || 500).json({
      code: err.code || "INTERNAL",
      message: err.message,
      ...(err.body?.missing && { missing: err.body.missing }),
      ...(err.body?.blanks && { blanks: err.body.blanks }),
   });
});

app.listen(process.env.PORT || 8000);

{
   "status": 400,
   "code": "MISSING_COLUMNS",
   "message": "The CSV is missing required columns for this batch type.",
   "missing": ["Investor.email"]
}