"""
DRE Welcome Packet — document templating engine.
Three jobs:
1. Pre-populate the 6 welcome-packet documents from the client's original
submission (claims/clients/debtors tables).
2. Let the client edit any field; recovery-impacting changes are written to
audit_log so recovery staff can see exactly what changed.
3. Render the filled documents as HTML (live) and PDF (print/sign).
Data model:
- FIELD_CATALOG : canonical profile fields (label, source column, type,
required, recovery_impact).
- DOC_PLACEHOLDERS : per document, ordered list of (placeholder_token -> profile_key).
- packet_field_overrides table : stores the client's final value per field,
merged over pre-populated submission values.
The markdown templates in docs/welcome-packet/ use {{profile_key}} tokens in the
cells/words that should be pre-populated. Signature blocks, notary blocks, and
checkbox lists are completed at signing time and are left as-is.
"""
from __future__ import annotations
import os
import re
import subprocess
import uuid
from datetime import datetime
import markdown
from . import db as dbmod
from fastapi import APIRouter, Depends, HTTPException, Request, status
from fastapi.responses import HTMLResponse, JSONResponse, Response
from . import auth as authmod
from .db import get_conn
router = APIRouter()
DOCS_DIR = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "docs", "welcome-packet")
DOC_ORDER = [
"01-LPOA.md",
"02-Terms-of-Service.md",
"03-Fee-Schedule.md",
"04-Third-Party-Consent.md",
"05-Debtor-Info-Sheet.md",
"06-ACH-Authorization.md",
]
DOC_TITLES = {
"01-LPOA.md": "Limited Power of Attorney",
"02-Terms-of-Service.md": "Terms of Service",
"03-Fee-Schedule.md": "Schedule A — Fee Schedule",
"04-Third-Party-Consent.md": "Third-Party Sharing Consent",
"05-Debtor-Info-Sheet.md": "Debtor Information Sheet",
"06-ACH-Authorization.md": "ACH / Disbursement Authorization",
}
# ---------------------------------------------------------------------------
# Onboarding paperwork receipt tracking. One row per welcome-packet document
# per claim; staff mark each doc "received" once the client returns it
# (e-sign via DocuSeal or manual upload). Recovery escalation is gated on
# every document being received.
# ---------------------------------------------------------------------------
ONBOARDING_DOCS = [
("LPOA", "Limited Power of Attorney"),
("TOS", "Terms of Service"),
("FEE_SCHEDULE", "Schedule A — Fee Schedule"),
("THIRD_PARTY_CONSENT", "Third-Party Sharing Consent"),
("DEBTOR_INFO", "Debtor Information Sheet"),
("ACH", "ACH / Disbursement Authorization"),
]
def ensure_onboarding_docs(conn, claim_id: str) -> None:
"""Seed the 6 onboarding-doc rows for a claim if they don't exist yet."""
now = dbmod.utcnow_iso()
for key, _title in ONBOARDING_DOCS:
conn.execute(
"INSERT OR IGNORE INTO onboarding_docs (id, claim_id, doc_key, status, created_at) "
"VALUES (?, ?, ?, 'PENDING', ?)",
(dbmod.new_uuid(), claim_id, key, now),
)
def onboarding_status(conn, claim_id: str) -> dict:
"""Compute receipt status for a claim's onboarding paperwork."""
ensure_onboarding_docs(conn, claim_id)
rows = conn.execute(
"SELECT doc_key, status, docuseal_submission_id, docuseal_submitter_id, "
"docuseal_slug, docuseal_embed_src, docuseal_status, docuseal_sent_at "
"FROM onboarding_docs WHERE claim_id = ? ORDER BY rowid",
(claim_id,),
).fetchall()
title_by_key = {k: t for k, t in ONBOARDING_DOCS}
items = []
for r in rows:
item = {
"key": r["doc_key"],
"title": title_by_key.get(r["doc_key"], r["doc_key"]),
"status": r["status"],
}
if r["docuseal_submission_id"] is not None:
item["docuseal"] = {
"submission_id": r["docuseal_submission_id"],
"submitter_id": r["docuseal_submitter_id"],
"slug": r["docuseal_slug"],
"status": r["docuseal_status"],
"sent_at": r["docuseal_sent_at"],
"signing_url": _docuseal_signing_url(r["docuseal_slug"], r["docuseal_embed_src"]),
}
items.append(item)
received = sum(1 for i in items if i["status"] == "RECEIVED")
return {
"received": received,
"total": len(items),
"complete": received == len(items),
"outstanding": [i for i in items if i["status"] != "RECEIVED"],
"items": items,
}
def _docuseal_signing_url(slug: str | None, embed_src: str | None) -> str | None:
"""Canonical public signing URL for a DocuSeal submitter, or None if unsigned."""
if slug:
return f"https://sign.debtrecoveryexperts.com/s/{slug}"
return embed_src
# ---------------------------------------------------------------------------
# Field catalog. `source` is a "table.column" path resolvable against a claim
# row joined with client + debtor. `recovery_impact` marks fields where a change
# alters WHO we pursue, HOW MUCH, WHEN it was due, or whether a personal
# guarantee exists — i.e. anything that changes our collection strategy.
# ---------------------------------------------------------------------------
FIELD_CATALOG = {
# --- Client identity ---
"client_company_name": {"label": "Client Legal Name", "source": "client.company_name", "type": "text", "required": True, "recovery_impact": False},
"client_entity_type": {"label": "Client Entity Type", "source": None, "type": "text", "required": False, "recovery_impact": False},
"client_contact_name": {"label": "Client Contact / Print Name", "source": "client.contact_name", "type": "text", "required": True, "recovery_impact": False},
# --- Debtor identity (recovery-critical) ---
"debtor_name": {"label": "Debtor Legal Name", "source": "debtor.name", "type": "text", "required": True, "recovery_impact": True},
"debtor_entity_type": {"label": "Debtor Entity Type", "source": "debtor.business_type", "type": "text", "required": False, "recovery_impact": True},
"debtor_dba": {"label": "DBA / Trade Name", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_state": {"label": "State of Formation", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_registered_agent": {"label": "Registered Agent Name", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_registered_agent_address": {"label": "Registered Agent Address", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_ein": {"label": "EIN / Tax ID", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_website": {"label": "Debtor Website", "source": None, "type": "text", "required": False, "recovery_impact": False},
"debtor_contact_name": {"label": "Debtor Primary Contact", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_contact_title": {"label": "Debtor Contact Title", "source": None, "type": "text", "required": False, "recovery_impact": False},
"debtor_contact_phone": {"label": "Debtor Direct Phone", "source": "debtor.contact_phone", "type": "text", "required": False, "recovery_impact": True},
"debtor_contact_email": {"label": "Debtor Email", "source": "debtor.contact_email", "type": "text", "required": False, "recovery_impact": True},
"debtor_address": {"label": "Debtor Business Address", "source": "debtor.physical_address", "type": "text", "required": False, "recovery_impact": True},
"debtor_city_state_zip": {"label": "Debtor City / State / ZIP", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_alt_address": {"label": "Debtor Alternate Address", "source": None, "type": "text", "required": False, "recovery_impact": False},
"personal_guarantee": {"label": "Personal Guarantee Exists?", "source": None, "type": "checkbox", "options": ["Yes", "No", "Unsure"], "required": True, "recovery_impact": True},
"personal_guarantee_signer": {"label": "Personal Guarantee Signer", "source": None, "type": "text", "required": False, "recovery_impact": True},
"debtor_bank": {"label": "Debtor Bank / FI", "source": None, "type": "text", "required": False, "recovery_impact": False},
"debtor_ar_lenders": {"label": "Debtor Known AR / Lenders", "source": None, "type": "text", "required": False, "recovery_impact": False},
# --- Claim substance (recovery-critical) ---
"claim_amount_cents": {"label": "Claim Amount", "source": "claim.amount_cents", "type": "money", "required": True, "recovery_impact": True},
"invoice_date": {"label": "Invoice / Contract Date", "source": "claim.invoice_date", "type": "date", "required": False, "recovery_impact": True},
"dre_claim_number": {"label": "DRE Claim Number", "source": "claim.claim_number", "type": "text", "required": True, "recovery_impact": False},
# --- ACH / disbursement (not recovery strategy, but completion-gated) ---
"account_holder_name": {"label": "Account Holder Legal Name", "source": "client.company_name", "type": "text", "required": True, "recovery_impact": False},
"account_holder_entity_type": {"label": "Account Holder Entity Type", "source": None, "type": "text", "required": False, "recovery_impact": False},
"bank_name": {"label": "Bank Name", "source": None, "type": "text", "required": True, "recovery_impact": False},
"account_type": {"label": "Account Type", "source": None, "type": "checkbox", "options": ["Checking", "Savings"], "required": True, "recovery_impact": False},
"routing_number": {"label": "Routing (ABA) Number", "source": None, "type": "text", "required": True, "recovery_impact": False},
"account_number": {"label": "Account Number", "source": None, "type": "text", "required": True, "recovery_impact": False},
}
# Ordered placeholders per document. Each token in the markdown maps to a
# profile key. Only text/money/date fields are substituted inline; checkboxes
# and signatures are completed at signing.
DOC_PLACEHOLDERS = {
"01-LPOA.md": [
("client_legal_name", "client_company_name"),
("client_entity_type", "client_entity_type"),
("debtor_legal_name", "debtor_name"),
("claim_amount", "claim_amount_cents"),
("invoice_date", "invoice_date"),
("dre_claim_number", "dre_claim_number"),
],
"02-Terms-of-Service.md": [],
"03-Fee-Schedule.md": [],
"04-Third-Party-Consent.md": [],
"05-Debtor-Info-Sheet.md": [
("debtor_legal_name", "debtor_name"),
("debtor_entity_type", "debtor_entity_type"),
("debtor_dba", "debtor_dba"),
("debtor_state", "debtor_state"),
("debtor_registered_agent", "debtor_registered_agent"),
("debtor_registered_agent_address", "debtor_registered_agent_address"),
("debtor_ein", "debtor_ein"),
("debtor_website", "debtor_website"),
("debtor_contact_name", "debtor_contact_name"),
("debtor_contact_title", "debtor_contact_title"),
("debtor_contact_phone", "debtor_contact_phone"),
("debtor_contact_email", "debtor_contact_email"),
("debtor_address", "debtor_address"),
("debtor_city_state_zip", "debtor_city_state_zip"),
("debtor_alt_address", "debtor_alt_address"),
("debtor_bank", "debtor_bank"),
("debtor_ar_lenders", "debtor_ar_lenders"),
("personal_guarantee_signer", "personal_guarantee_signer"),
],
"06-ACH-Authorization.md": [
("account_holder_name", "account_holder_name"),
("account_holder_entity_type", "account_holder_entity_type"),
("bank_name", "bank_name"),
("routing_number", "routing_number"),
("account_number", "account_number"),
],
}
# ---------------------------------------------------------------------------
# Migration
# ---------------------------------------------------------------------------
PACKET_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS packet_field_overrides (
id TEXT PRIMARY KEY,
claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE,
profile_key TEXT NOT NULL,
value TEXT,
updated_by TEXT NOT NULL DEFAULT 'CLIENT' CHECK (updated_by IN ('CLIENT','STAFF')),
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
UNIQUE(claim_id, profile_key)
);
"""
def ensure_packet_table():
with dbmod.get_conn() as conn:
conn.execute(PACKET_TABLE_SQL)
conn.commit()
# ---------------------------------------------------------------------------
# Value resolution
# ---------------------------------------------------------------------------
def _format_value(profile_key: str, raw) -> str:
"""Format a raw DB value for display in the document."""
if raw is None:
return ""
if FIELD_CATALOG[profile_key]["type"] == "money":
try:
cents = int(raw)
return f"${cents / 100:,.2f}"
except (TypeError, ValueError):
return str(raw)
if FIELD_CATALOG[profile_key]["type"] == "date":
s = str(raw)
# dates may be ISO or already human; try to render nicely
for fmt in ("%Y-%m-%d", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%dT%H:%M:%SZ"):
try:
return datetime.strptime(s, fmt).strftime("%B %d, %Y")
except ValueError:
continue
return s
return str(raw)
def resolve_prepopulated(claim_row: dict, profile_key: str) -> str:
"""Pull the pre-populated value for a field from the original submission row."""
source = FIELD_CATALOG[profile_key]["source"]
if not source:
return ""
table, column = source.split(".", 1)
raw = claim_row.get(column)
return _format_value(profile_key, raw)
def load_claim_row(conn, claim_number: str) -> dict:
row = conn.execute(
"""
SELECT c.claim_number, c.amount_cents, c.invoice_date, c.client_reference,
c.description, c.status, c.tier, c.created_at,
cl.company_name AS company_name, cl.contact_name AS contact_name,
cl.email AS client_email, cl.phone AS client_phone,
d.name AS name, d.business_type AS business_type,
d.contact_email AS contact_email, d.contact_phone AS contact_phone,
d.physical_address AS physical_address
FROM claims c
JOIN clients cl ON cl.id = c.client_id
JOIN debtors d ON d.id = c.debtor_id
WHERE c.claim_number = ?
""",
(claim_number,),
).fetchone()
if row is None:
raise KeyError(claim_number)
return dict(row)
def merged_values(conn, claim_number: str) -> tuple[dict, dict]:
"""Pre-populated submission values, overlaid with client overrides."""
row = load_claim_row(conn, claim_number)
values = {}
for key in FIELD_CATALOG:
values[key] = resolve_prepopulated(row, key)
claim_id = _claim_id(conn, claim_number)
overrides = conn.execute(
"SELECT profile_key, value FROM packet_field_overrides WHERE claim_id = ?",
(claim_id,),
).fetchall()
for o in overrides:
values[o["profile_key"]] = o["value"] or ""
return values, row
def _claim_id(conn, claim_number: str) -> str:
return conn.execute("SELECT id FROM claims WHERE claim_number = ?", (claim_number,)).fetchone()["id"]
# ---------------------------------------------------------------------------
# Rendering
# ---------------------------------------------------------------------------
def fill_document(doc_filename: str, values: dict) -> str:
"""Substitute {{token}} placeholders in a markdown doc with values."""
path = os.path.join(DOCS_DIR, doc_filename)
with open(path, "r", encoding="utf-8") as fh:
text = fh.read()
placeholders = DOC_PLACEHOLDERS.get(doc_filename, [])
for token, profile_key in placeholders:
val = values.get(profile_key, "")
# Pre-populated value, or a blank fill-line if the client hasn't supplied it.
display = val if val != "" else "________________________"
text = text.replace("{{" + token + "}}", display)
# Any leftover un-mapped tokens -> blank
text = re.sub(r"\{\{[a-z0-9_]+\}\}", "________________________", text)
return text
def document_to_html(doc_filename: str, values: dict) -> str:
md_text = fill_document(doc_filename, values)
html = markdown.markdown(md_text, extensions=["tables", "sane_lists"])
return html
def packet_html(conn, claim_number: str) -> str:
"""Render all 6 documents as one styled HTML page (live preview)."""
values, row = merged_values(conn, claim_number)
sections = []
for doc in DOC_ORDER:
body = document_to_html(doc, values)
sections.append(
f'{DOC_TITLES[doc]}