`scripts/load-abrams-rx.js`

118 lines
#!/usr/bin/env node
// One-off importer: Shangoo Pharmacy "PAT PROFILE FOR TAX" (2023-07-08 → 2026-07-08)
// → person (Steve, Natalia) + prescription_fill (every dated fill) + medication (deduped list).
//
// Source data: /tmp/abrams_fills.json (OCR-parsed from ~/Downloads/abrams.pdf, reconciled
// to the document totals: Natalia 220 fills / plan-paid $136,328.74). Idempotent — re-running
// replaces this source's fills (SOURCE tag) and only adds medications that don't already exist.
//
// Usage: node scripts/load-abrams-rx.js [path-to-fills.json]

const fs = require('fs');
const db = require('../lib/db');
const audit = require('../lib/audit');
const { id } = require('../lib/ids');

const USER_ID = 'user_steve';
const SOURCE = 'shangoo-pharmacy-tax-2023-2026';
const PHARMACY = 'Shangoo Pharmacy';
const FILE = process.argv[2] || '/tmp/abrams_fills.json';

// PDF header identity → person records
const PEOPLE = {
  'Steve Abrams':   { relation: 'self',   dob: '1971-01-03' },
  'Natalia Abrams': { relation: 'spouse', dob: '1980-04-25' },
};
const ADDR = '18406 Bessemer St, Reseda, CA 91335';

const num = (v) => (v == null || v === '' ? null : Number(v));

async function findOrCreatePerson(fullName) {
  const found = await db.query(
    `SELECT id FROM person WHERE user_id=$1 AND lower(full_name)=lower($2) LIMIT 1`, [USER_ID, fullName]);
  if (found.rows[0]) return found.rows[0].id;
  const meta = PEOPLE[fullName] || { relation: 'other', dob: null };
  const pid = id('person');
  await db.query(
    `INSERT INTO person (id, user_id, relation, full_name, dob, notes)
     VALUES ($1,$2,$3,$4,$5,$6)`,
    [pid, USER_ID, meta.relation, fullName, meta.dob, `Imported from ${PHARMACY} tax profile. ${ADDR}`]);
  console.log(`  + person: ${fullName} (${meta.relation})`);
  return pid;
}

// strip a strength/dose out of the printed drug string for the medication list
const doseOf = (d) => {
  const m = String(d || '').match(/\b(\d[\d.]*\s?(?:MG|MCG|UNIT|%|ML)[\/\-\d.A-Z ]*?)(?:\s+(?:TABS?|CAPS?|CPEP|TAB|CAP|SPR|GEL|SUS|STRP|ODT|CPDR|SUSP)\b|$)/i);
  return m ? m[1].trim() : null;
};
// dedup key for the medication list — uppercase, collapse ws, drop stray punctuation
const medKey = (d) => String(d || '').toUpperCase().replace(/[.,;:]/g, '').replace(/\s+/g, ' ').trim();

async function main() {
  const fills = JSON.parse(fs.readFileSync(FILE, 'utf8'));
  console.log(`loading ${fills.length} fills from ${FILE}`);

  // 1. people
  const personId = {};
  for (const name of new Set(fills.map((f) => f.person).filter(Boolean))) {
    personId[name] = await findOrCreatePerson(name);
  }

  // 2. fills — idempotent: clear this source first, then insert all
  await db.query(`DELETE FROM prescription_fill WHERE user_id=$1 AND source=$2`, [USER_ID, SOURCE]);
  let n = 0;
  for (const f of fills) {
    await db.query(
      `INSERT INTO prescription_fill
        (id,user_id,person_id,fill_date,drug_name,rx_number,refill,quantity,days_supply,doctor,pharmacy,ndc,dea,plan_name,plan_paid,patient_paid,source,raw_text)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)`,
      [id('prescription_fill'), USER_ID, personId[f.person] || null, f.date || null, f.drug || '(unknown)',
       f.rx_number || null, num(f.refill), num(f.qty), num(f.days_supply), f.doctor || null, PHARMACY,
       f.ndc || null, f.dea || null, f.plan || null, num(f.cost), 0.00, SOURCE, f.raw || null]);
    n++;
  }
  console.log(`  loaded ${n} prescription_fill rows`);

  // 3. deduped medication list (skip the DELIVERY FEE service line-items)
  const seenMedKeys = new Set(
    (await db.query(`SELECT person_id, upper(name) u FROM medication WHERE user_id=$1`, [USER_ID]))
      .rows.map((r) => `${r.person_id}|${r.u}`));
  const groups = new Map();  // `${personId}|${medKey}` → {person_id, name, latest, earliest, doctor, rx}
  for (const f of fills) {
    if (!f.drug || /DELIVERY FEE/i.test(f.drug)) continue;
    const pid = personId[f.person] || null;
    const k = `${pid}|${medKey(f.drug)}`;
    const g = groups.get(k) || { person_id: pid, name: f.drug, earliest: f.date, latest: f.date, doctor: f.doctor, rx: f.rx_number };
    if (f.date && (!g.latest || f.date > g.latest)) { g.latest = f.date; g.doctor = f.doctor || g.doctor; g.rx = f.rx_number || g.rx; }
    if (f.date && (!g.earliest || f.date < g.earliest)) g.earliest = f.date;
    groups.set(k, g);
  }
  let med = 0;
  for (const [k, g] of groups) {
    if (seenMedKeys.has(`${g.person_id}|${g.name.toUpperCase()}`)) continue;
    await db.query(
      `INSERT INTO medication (id,user_id,person_id,name,dosage,prescriber,pharmacy,rx_number,start_date,is_active,notes)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
      [id('medication'), USER_ID, g.person_id, g.name, doseOf(g.name), g.doctor || null, PHARMACY,
       g.rx || null, g.earliest || null, true, `Auto-derived from ${PHARMACY} fill history (last fill ${g.latest}).`]);
    med++;
  }
  console.log(`  added ${med} deduped medication rows (of ${groups.size} unique drugs)`);

  // 4. audit
  await audit.log({ actorType: 'user', actorId: USER_ID, objectType: 'prescription_fill', objectId: SOURCE,
    eventType: 'prescription_import', metadata: { medical: true, consent: 'user-supplied', source: SOURCE, fills: n, medications: med } });

  // 5. reconciliation summary
  const sum = await db.query(
    `SELECT p.full_name, count(*)::int fills, coalesce(sum(pf.plan_paid),0)::numeric(12,2) plan_paid
       FROM prescription_fill pf LEFT JOIN person p ON p.id=pf.person_id
      WHERE pf.user_id=$1 AND pf.source=$2 GROUP BY p.full_name ORDER BY 1`, [USER_ID, SOURCE]);
  console.log('\nReconciliation:');
  for (const r of sum.rows) console.log(`  ${r.full_name}: ${r.fills} fills · plan-paid $${Number(r.plan_paid).toLocaleString()}`);
  await db.pool?.end?.();
}

main().then(() => process.exit(0)).catch((e) => { console.error('LOAD FAILED:', e.message); process.exit(1); });