`scripts/load-asc-appraisers.sh`

73 lines
#!/usr/bin/env bash
# TK-10560 — Load ASC SoCal appraisers into LOCAL realestate.rentv_licensed_targets (role='Appraiser').
# LOCAL staging/engine write (Mac2). Idempotent: deletes this source's Appraiser rows, then re-inserts.
# Wrapped in the rentv-deploy flock to coordinate with any concurrent rentv session.
# Reads JSONL from data/asc/appraisers_socal.jsonl. Does NOT touch prod (Kamatera) — that's claude-rentv.
set -euo pipefail
cd "$(dirname "$0")/.."
JSONL="data/asc/appraisers_socal.jsonl"
DB="${REALESTATE_DB:-realestate}"
LOCK="$HOME/.claude/locks/rentv-deploy.lock"
mkdir -p "$(dirname "$LOCK")"
[ -s "$JSONL" ] || { echo "MISSING/empty $JSONL"; exit 1; }
N=$(wc -l < "$JSONL")
echo "loading $N staged appraiser rows into LOCAL $DB.rentv_licensed_targets ..."

# Build a COPY-able TSV from JSONL via python, then load inside one transaction under flock.
TSV=/tmp/asc_appraisers_load.tsv
python3 - "$JSONL" "$TSV" <<'PY'
import sys, json
src, dst = sys.argv[1], sys.argv[2]
cols = ["source","role","entity_name","contact_name","license_no","license_type",
        "license_status","address","city","county","state","zip","market",
        "commercial_flag","within_300mi","source_url","raw"]
def esc(v):
    if v is None: return r"\N"
    if isinstance(v,bool): return "t" if v else "f"
    if isinstance(v,(dict,list)): v=json.dumps(v,ensure_ascii=False)
    s=str(v).replace("\\","\\\\").replace("\t","    ").replace("\n"," ").replace("\r"," ")
    return s
with open(src) as f, open(dst,"w") as o:
    for line in f:
        line=line.strip()
        if not line: continue
        r=json.loads(line)
        # empty license_no -> NULL so the UNIQUE(source,license_no) allows many unlicensed rows
        if not (r.get("license_no") or "").strip(): r["license_no"]=None
        o.write("\t".join(esc(r.get(c)) for c in cols)+"\n")
print("tsv written")
PY

flock "$LOCK" psql -h /tmp -d "$DB" -v ON_ERROR_STOP=1 <<SQL
BEGIN;
DELETE FROM rentv_licensed_targets
 WHERE role='Appraiser' AND source='ASC National Appraiser Registry';
CREATE TEMP TABLE _asc_load (
  source text, role text, entity_name text, contact_name text, license_no text,
  license_type text, license_status text, address text, city text, county text,
  state text, zip text, market text, commercial_flag boolean, within_300mi boolean,
  source_url text, raw jsonb
) ON COMMIT DROP;
\copy _asc_load FROM '$TSV' WITH (FORMAT text, NULL '\N')
-- RENTV is a COMMERCIAL RE directory: load only commercial-capable appraisers
-- (commercial_flag = Certified General — the sole license level that appraises
-- commercial/complex property without a value limit). The full CA-SoCal dataset
-- (incl. residential) stays staged in data/asc/appraisers_socal.jsonl. To include
-- residential too, drop the "WHERE commercial_flag" below.
INSERT INTO rentv_licensed_targets
  (source,role,entity_name,contact_name,license_no,license_type,license_status,
   address,city,county,state,zip,market,commercial_flag,within_300mi,source_url,raw)
SELECT source,role,entity_name,contact_name,license_no,license_type,license_status,
   address,city,county,state,zip,market,commercial_flag,within_300mi,source_url,raw
FROM _asc_load
WHERE commercial_flag;
SELECT setval(pg_get_serial_sequence('rentv_licensed_targets','id'),
       (SELECT max(id) FROM rentv_licensed_targets));
SELECT 'Appraiser rows loaded='||count(*),
       'commercial='||count(*) FILTER (WHERE commercial_flag),
       'markets='||count(DISTINCT market)
  FROM rentv_licensed_targets WHERE role='Appraiser';
COMMIT;
SQL
echo "LOCAL load done."