`routes/recalls.js`

96 lines
// /recalls — read-only viewer for recall_match × recall_event × purchase.

const express = require('express');
const db = require('../lib/db');

const router = express.Router();
const DEV_USER_ID = 'user_steve';

router.get('/recalls', async (req, res) => {
  const status = req.query.status || null;
  const params = [DEV_USER_ID];
  let where = `rm.user_id = $1`;
  if (status) {
    params.push(status);
    where += ` AND rm.status = $${params.length}`;
  }
  const r = await db.query(
    `SELECT rm.id AS match_id, rm.confidence, rm.status, rm.matched_at,
            re.id AS recall_id, re.authority, re.external_id, re.title, re.hazard, re.remedy, re.url, re.published_at,
            p.id AS purchase_id, p.merchant_name, p.purchase_date, p.total_amount, p.currency
       FROM recall_match rm
       JOIN recall_event re ON re.id = rm.recall_id
  LEFT JOIN purchase p ON p.id = rm.asset_id AND rm.asset_table = 'purchase'
      WHERE ${where}
      ORDER BY rm.matched_at DESC
      LIMIT 200`,
    params
  );

  const counts = await db.query(
    `SELECT status, count(*)::int AS n FROM recall_match WHERE user_id = $1 GROUP BY status ORDER BY status`,
    [DEV_USER_ID]
  );

  // FDA medication recalls (openFDA drug-enforcement), cross-referenced to your med list and
  // grouped per medication. 'Class I' sorts before 'Class II'/'Class III' so MIN = worst severity.
  // Matching is by drug NAME (ingredient) → advisory ("this drug has been recalled"), not lot-specific.
  const medRecalls = (await db.query(
    `SELECT m.name, m.generic_name, coalesce(pp.full_name,'—') AS person,
            count(*)::int AS n, min(mr.classification) AS worst,
            max(mr.recall_initiation_date) AS latest,
            (array_agg(mr.url    ORDER BY mr.recall_initiation_date DESC NULLS LAST))[1] AS sample_url,
            (array_agg(mr.reason ORDER BY mr.recall_initiation_date DESC NULLS LAST))[1] AS sample_reason
       FROM medication_recall mr
       JOIN medication m ON m.id = mr.medication_id
  LEFT JOIN person pp ON pp.id = m.person_id
      WHERE mr.user_id = $1
      GROUP BY m.name, m.generic_name, pp.full_name
      ORDER BY min(mr.classification) ASC, count(*) DESC`,
    [DEV_USER_ID]
  )).rows;
  const medRecallTotal = medRecalls.reduce((s, x) => s + x.n, 0);

  // NDC-PRECISE: fills whose ACTUAL dispensed product (NDC) is on an FDA recall list. This is the
  // actionable signal (vs the by-name advisory above). Grouped per product, most-severe first.
  // One row per (product, person). Status/assessment now vary per fill (a 2023 vs 2026 fill of the
  // same drug can differ), so we aggregate to the WORST relevance and surface how many fills are
  // affected. rk() = relevance rank (possible > confirm > cleared).
  const rk = `(CASE pf.ndc_recall_status WHEN 'possible' THEN 3 WHEN 'confirm' THEN 2 ELSE 1 END)`;
  const ndcFlags = (await db.query(
    `SELECT pf.drug_name, pf.ndc, pf.ndc_recall_class, coalesce(pp.full_name,'—') AS person,
            count(*)::int AS fills,
            count(*) FILTER (WHERE pf.ndc_recall_status <> 'cleared')::int AS relevant_fills,
            min(pf.fill_date) AS first_fill, max(pf.fill_date) AS last_fill,
            (array_agg(pf.ndc_recall_links   ORDER BY pf.fill_date DESC))[1]        AS links,
            (array_agg(pf.ndc_recall_status  ORDER BY ${rk} DESC))[1]              AS status,
            (array_agg(pf.ndc_recall_assessment ORDER BY ${rk} DESC))[1]           AS assessment,
            (array_agg(pf.ndc_recall_number  ORDER BY ${rk} DESC))[1]              AS ndc_recall_number
       FROM prescription_fill pf LEFT JOIN person pp ON pp.id = pf.person_id
      WHERE pf.user_id = $1 AND pf.ndc_recalled
      GROUP BY pf.drug_name, pf.ndc, pf.ndc_recall_class, pp.full_name
      ORDER BY max(${rk}) DESC,
               (CASE pf.ndc_recall_class WHEN 'Class I' THEN 1 WHEN 'Class II' THEN 2 ELSE 3 END),
               max(pf.fill_date) DESC`,
    [DEV_USER_ID]
  )).rows;

  res.render('recalls', { matches: r.rows, counts: counts.rows, activeStatus: status, medRecalls, medRecallTotal, ndcFlags });
});

router.get('/api/recalls', async (_req, res) => {
  const r = await db.query(
    `SELECT rm.id AS match_id, rm.confidence, rm.status, rm.matched_at,
            re.id AS recall_id, re.title, re.url, p.merchant_name
       FROM recall_match rm
       JOIN recall_event re ON re.id = rm.recall_id
  LEFT JOIN purchase p ON p.id = rm.asset_id AND rm.asset_table = 'purchase'
      WHERE rm.user_id = $1
      ORDER BY rm.matched_at DESC LIMIT 200`,
    [DEV_USER_ID]
  );
  res.json(r.rows);
});

module.exports = router;