import { useState, useMemo, useRef, useEffect } from "react"; import { useSQLQuery } from "@motherduck/react-sql-query"; import { BarChart, Bar, LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer, } from "recharts"; export const REQUIRED_DATABASES = [ { type: 'share', path: 'md:_share/diabetes_rx/b3febc00-f072-43e8-87b9-54d4b8e2e40b', alias: 'diabetes_rx' } ]; const N = (v) => (v != null ? Number(v) : 0); // ── Static filter option lists ── const STATES = ["CA", "WA", "OR"]; const TERRITORIES_BY_STATE = { CA: ["LA Metro", "Bay Area", "Central Valley", "San Diego", "North Bay", "Inland Empire", "Sacramento", "Orange County"], WA: ["Puget Sound", "Eastern WA", "SW Washington"], OR: ["Willamette Valley", "Southern Oregon", "Central Oregon", "Portland Metro"], }; const CITIES_BY_STATE = { CA: ["Bakersfield", "Fresno", "Irvine", "Long Beach", "Los Angeles", "Oakland", "Riverside", "Sacramento", "San Diego", "San Francisco", "San Jose", "Santa Rosa"], WA: ["Bellevue", "Olympia", "Seattle", "Spokane", "Tacoma", "Vancouver"], OR: ["Bend", "Eugene", "Medford", "Portland", "Salem"], }; const SPECIALTIES = ["Endocrinology", "Family Medicine", "Internal Medicine", "Nurse Practitioner", "Physician Assistant", "Cardiology", "Nephrology"]; const DRUG_CLASSES = ["GLP-1/GIP", "GLP-1", "Biguanide", "Basal Insulin", "SGLT2", "Rapid-Acting Insulin", "DPP-4", "SGLT2 Combination", "Sulfonylurea", "Human Insulin", "TZD", "DPP-4 Combination", "SGLT2/DPP-4 Combination"]; const TIME_PRESETS = [ { value: "last_30", label: "Last 30 days" }, { value: "last_90", label: "Last 90 days" }, { value: "last_6m", label: "Last 6 months" }, { value: "q1", label: "Q1 2025" }, { value: "q2", label: "Q2 2025" }, { value: "q3", label: "Q3 2025" }, { value: "q4", label: "Q4 2025" }, { value: "ytd", label: "Full Year 2025" }, { value: "custom", label: "Custom range…" }, ]; const DATA_MIN = "2025-01-01"; const DATA_MAX = "2025-12-31"; const SORT_OPTIONS = [ { key: "trx", label: "TRx" }, { key: "nbrx", label: "NBRx" }, { key: "nts", label: "NTS" }, { key: "switches", label: "Switches" }, ]; // ── Theme tokens ── const LIGHT = { bg: "#f4f3ee", panel: "#fdfcf9", ink: "#1a1916", inkSoft: "#5c5a55", inkFaint: "#8b8884", rule: "#e3e0d8", ruleSoft: "#eeece5", primary: "#0e4d6b", accent1: "#a23822", accent2: "#3f6634", accent3: "#b87333", hilite: "#fef3d7", chartGrid: "#eeece5", skeleton: "#e3e0d8", }; const DARK = { bg: "#15171a", panel: "#1d2024", ink: "#ece9e0", inkSoft: "#a8a39a", inkFaint: "#6f6b62", rule: "#2c2f34", ruleSoft: "#22252a", primary: "#5fb5d9", accent1: "#e07a5f", accent2: "#8db773", accent3: "#d4a373", hilite: "#3a3520", chartGrid: "#2c2f34", skeleton: "#2c2f34", }; const TABULAR = { fontVariantNumeric: "tabular-nums" }; const capsLabel = (c) => ({ fontSize: 10, letterSpacing: "0.12em", textTransform: "uppercase", color: c.inkFaint, fontWeight: 600, }); // ── SQL helpers ── const sq = (s) => `'${String(s).replace(/'/g, "''")}'`; function buildTimeClause(preset, customStart, customEnd) { const anchor = `DATE '${DATA_MAX}'`; switch (preset) { case "last_30": return `prescription_date >= ${anchor} - INTERVAL 30 DAY`; case "last_90": return `prescription_date >= ${anchor} - INTERVAL 90 DAY`; case "last_6m": return `prescription_date >= ${anchor} - INTERVAL 6 MONTH`; case "q1": return `prescription_date >= DATE '2025-01-01' AND prescription_date < DATE '2025-04-01'`; case "q2": return `prescription_date >= DATE '2025-04-01' AND prescription_date < DATE '2025-07-01'`; case "q3": return `prescription_date >= DATE '2025-07-01' AND prescription_date < DATE '2025-10-01'`; case "q4": return `prescription_date >= DATE '2025-10-01' AND prescription_date <= DATE '2025-12-31'`; case "ytd": return `prescription_date >= DATE '2025-01-01' AND prescription_date <= DATE '2025-12-31'`; case "custom": return `prescription_date >= DATE '${customStart}' AND prescription_date <= DATE '${customEnd}'`; } } function windowDays(preset, customStart, customEnd) { switch (preset) { case "last_30": return 30; case "last_90": return 90; case "last_6m": return 183; case "q1": case "q2": case "q3": case "q4": return 92; case "ytd": return 365; case "custom": { const a = new Date(customStart).getTime(); const b = new Date(customEnd).getTime(); return Math.max(1, Math.round((b - a) / 86400000)); } } } function trendBucketing(days) { if (days <= 90) return { trunc: "day", fmt: "%m-%d" }; if (days <= 200) return { trunc: "week", fmt: "%Y-%m-%d" }; return { trunc: "month", fmt: "%Y-%m" }; } function buildInClause(col, vals) { if (vals.length === 0) return ""; return `AND ${col} IN (${vals.map(sq).join(", ")})`; } function buildNotInClause(col, vals) { if (vals.length === 0) return ""; return `AND ${col} NOT IN (${vals.map(sq).join(", ")})`; } // ── Multi-select ── function MultiSelect({ label, options, selected, onChange, c }) { const [open, setOpen] = useState(false); const ref = useRef(null); useEffect(() => { function onClick(e) { if (ref.current && !ref.current.contains(e.target)) setOpen(false); } document.addEventListener("mousedown", onClick); return () => document.removeEventListener("mousedown", onClick); }, []); const toggle = (opt) => { onChange(selected.includes(opt) ? selected.filter(s => s !== opt) : [...selected, opt]); }; const summary = selected.length === 0 ? All : selected.length === 1 ? {selected[0]} : {selected.length} selected; return (

{label}

{open && (

{selected.length > 0 && (

onChange([])} > Clear all

)} {options.map(opt => { const checked = selected.includes(opt); return ( toggle(opt)} style={{ marginRight: 8, accentColor: c.primary }} /> {opt} ); })}

)}

); } // ── HCP exclusion typeahead ── function HCPExclusionInput({ excluded, onAdd, onRemove, suggestions, c }) { const [q, setQ] = useState(""); const [focused, setFocused] = useState(false); const ref = useRef(null); useEffect(() => { function onClick(e) { if (ref.current && !ref.current.contains(e.target)) setFocused(false); } document.addEventListener("mousedown", onClick); return () => document.removeEventListener("mousedown", onClick); }, []); const matches = useMemo(() => { if (!q.trim()) return []; const lo = q.toLowerCase(); return suggestions .filter(s => s.hcp_name.toLowerCase().includes(lo) && !excluded.includes(s.hcp_name)) .slice(0, 8); }, [q, suggestions, excluded]); return (

Exclude HCPs

{excluded.map(name => ( {name} ))} setQ(e.target.value)} onFocus={() => setFocused(true)} placeholder={excluded.length === 0 ? "Type a name to exclude…" : ""} style={{ flex: 1, minWidth: 120, background: "transparent", border: "none", outline: "none", color: c.ink, fontSize: 13, padding: "2px 4px", }} />

{focused && matches.length > 0 && (

{matches.map(m => (

{ onAdd(m.hcp_name); setQ(""); }} style={{ padding: "6px 10px", fontSize: 13, cursor: "pointer", color: c.ink }} onMouseEnter={(e) => e.currentTarget.style.background = c.hilite} onMouseLeave={(e) => e.currentTarget.style.background = "transparent"} > {m.hcp_name} {m.specialty} · {m.city}, {m.state}

))}

)}

); } // ── KPI tile ── function KPI({ label, value, share, accent, isLoading, c }) { return (

{label}

{isLoading ? (

) : (

{value.toLocaleString()}

)}

{share && {share}}

); } function ThemeToggle({ dark, onToggle, c }) { return ( ); } export default function HCPPrescriberExplorer() { const [dark, setDark] = useState(false); const c = dark ? DARK : LIGHT; const [states, setStates] = useState([]); const [cities, setCities] = useState([]); const [territories, setTerritories] = useState([]); const [specialties, setSpecialties] = useState([]); const [drugClasses, setDrugClasses] = useState(["GLP-1/GIP"]); const [brands, setBrands] = useState([]); const [excludedHCPs, setExcludedHCPs] = useState([]); const [timePreset, setTimePreset] = useState("last_30"); const [customStart, setCustomStart] = useState(DATA_MIN); const [customEnd, setCustomEnd] = useState(DATA_MAX); const [sortKey, setSortKey] = useState("trx"); const timeLabel = useMemo(() => { if (timePreset === "custom") return `${customStart} → ${customEnd}`; return TIME_PRESETS.find(p => p.value === timePreset)?.label ?? ""; }, [timePreset, customStart, customEnd]); const availableTerritories = useMemo(() => { if (states.length === 0) return Object.values(TERRITORIES_BY_STATE).flat(); return states.flatMap(s => TERRITORIES_BY_STATE[s] || []); }, [states]); const availableCities = useMemo(() => { if (states.length === 0) return Object.values(CITIES_BY_STATE).flat(); return states.flatMap(s => CITIES_BY_STATE[s] || []); }, [states]); // ── Shared WHERE clauses ── const whereClauses = useMemo(() => { const parts = []; parts.push(buildTimeClause(timePreset, customStart, customEnd)); const stateClause = buildInClause("state", states); const cityClause = buildInClause("city", cities); const territoryClause = buildInClause("territory", territories); const specialtyClause = buildInClause("specialty", specialties); const drugClassClause = buildInClause("drug_class", drugClasses); const brandClause = buildInClause("brand_name", brands); const exclHcpClause = buildNotInClause("hcp_name", excludedHCPs); return [stateClause, cityClause, territoryClause, specialtyClause, drugClassClause, brandClause, exclHcpClause] .filter(Boolean) .reduce((acc, c) => acc + " " + c, parts[0]); }, [timePreset, customStart, customEnd, states, cities, territories, specialties, drugClasses, brands, excludedHCPs]); // ── KPI query ── const kpiQuery = useSQLQuery(` SELECT COUNT(*) AS total_trx, COUNT(*) FILTER (WHERE is_new_to_brand) AS total_nbrx, COUNT(*) FILTER (WHERE is_new_to_therapy) AS total_nts, COUNT(*) FILTER (WHERE is_switch) AS total_switches, COUNT(DISTINCT hcp_id) AS active_hcps FROM "diabetes_rx"."main"."silver_prescription_events" WHERE ${whereClauses} `); // ── Trend query ── const days = windowDays(timePreset, customStart, customEnd); const bucket = trendBucketing(days); const trendStart = timePreset === "custom" ? `DATE '${customStart}'` : timePreset === "last_30" ? `DATE '${DATA_MAX}' - INTERVAL 30 DAY` : timePreset === "last_90" ? `DATE '${DATA_MAX}' - INTERVAL 90 DAY` : timePreset === "last_6m" ? `DATE '${DATA_MAX}' - INTERVAL 6 MONTH` : timePreset === "q1" ? `DATE '2025-01-01'` : timePreset === "q2" ? `DATE '2025-04-01'` : timePreset === "q3" ? `DATE '2025-07-01'` : timePreset === "q4" ? `DATE '2025-10-01'` : `DATE '2025-01-01'`; const trendEnd = timePreset === "custom" ? `DATE '${customEnd}'` : timePreset === "q1" ? `DATE '2025-03-31'` : timePreset === "q2" ? `DATE '2025-06-30'` : timePreset === "q3" ? `DATE '2025-09-30'` : `DATE '${DATA_MAX}'`; const intervalUnit = bucket.trunc === "day" ? "1 DAY" : bucket.trunc === "week" ? "1 WEEK" : "1 MONTH"; const trendQuery = useSQLQuery(` WITH spine AS ( SELECT date_trunc('${bucket.trunc}', d) AS bucket FROM generate_series(${trendStart}, ${trendEnd}, INTERVAL ${intervalUnit}) t(d) GROUP BY 1 ) SELECT strftime(s.bucket, '${bucket.fmt}') AS period, COALESCE(COUNT(e.prescription_event_id), 0) AS trx, COALESCE(COUNT(*) FILTER (WHERE e.is_new_to_brand), 0) AS nbrx, COALESCE(COUNT(*) FILTER (WHERE e.is_new_to_therapy), 0) AS nts FROM spine s LEFT JOIN "diabetes_rx"."main"."silver_prescription_events" e ON date_trunc('${bucket.trunc}', e.prescription_date) = s.bucket AND ${whereClauses} GROUP BY 1 ORDER BY 1 `); // ── Brand summary query ── const brandQuery = useSQLQuery(` SELECT brand_name, COUNT(*) AS trx, COUNT(*) FILTER (WHERE is_new_to_brand) AS nbrx, COUNT(*) FILTER (WHERE is_new_to_therapy) AS nts, COUNT(*) FILTER (WHERE is_switch) AS switches, COUNT(DISTINCT hcp_id) AS prescribers FROM "diabetes_rx"."main"."silver_prescription_events" WHERE ${whereClauses} GROUP BY 1 ORDER BY trx DESC LIMIT 10 `); // ── Top prescribers query (server-side sort) ── const hcpQuery = useSQLQuery(` WITH prescriber_brand_totals AS ( SELECT hcp_name, specialty, city, state, territory, brand_name, COUNT(*) AS trx, COUNT(*) FILTER (WHERE is_new_to_brand) AS nbrx, COUNT(*) FILTER (WHERE is_new_to_therapy) AS nts, COUNT(*) FILTER (WHERE is_switch) AS switches FROM "diabetes_rx"."main"."silver_prescription_events" WHERE ${whereClauses} GROUP BY 1,2,3,4,5,6 ) SELECT * FROM prescriber_brand_totals ORDER BY ${sortKey} DESC LIMIT 25 `); // ── Brand options query (depends on selected drug classes) ── const brandOptionsQuery = useSQLQuery(` SELECT DISTINCT brand_name FROM "diabetes_rx"."main"."silver_prescription_events" WHERE 1=1 ${buildInClause("drug_class", drugClasses)} ORDER BY brand_name `); // ── HCP lookup for typeahead (all distinct HCPs in current filter scope, minus time) ── const hcpLookupQuery = useSQLQuery(` SELECT DISTINCT hcp_name, specialty, city, state FROM "diabetes_rx"."main"."silver_prescription_events" WHERE 1=1 ${buildInClause("state", states)} ${buildInClause("city", cities)} ${buildInClause("territory", territories)} ${buildInClause("specialty", specialties)} ${buildInClause("drug_class", drugClasses)} ${buildInClause("brand_name", brands)} ORDER BY hcp_name LIMIT 5000 `); // ── Unwrap ── const kpiRow = (Array.isArray(kpiQuery.data) ? kpiQuery.data : [])[0] ?? {}; const trxTotal = N(kpiRow.total_trx); const pct = (n) => trxTotal > 0 ? `${((n / trxTotal) * 100).toFixed(1)}% of TRx` : "—"; const trendRows = Array.isArray(trendQuery.data) ? trendQuery.data : []; const trendData = trendRows.map(r => ({ period: r.period, TRx: N(r.trx), NBRx: N(r.nbrx), NTS: N(r.nts), })); const brandRows = Array.isArray(brandQuery.data) ? brandQuery.data : []; const hcpRows = Array.isArray(hcpQuery.data) ? hcpQuery.data : []; const availableBrands = (Array.isArray(brandOptionsQuery.data) ? brandOptionsQuery.data : []) .map(r => r.brand_name); const hcpLookup = (Array.isArray(hcpLookupQuery.data) ? hcpLookupQuery.data : []); // Grouped bar data — top 25 by current sort key, with friendly labels const hcpBarData = hcpRows.map(r => ({ hcp: String(r.hcp_name || "").replace(/, MD$/, "").replace(/, DO$/, "").replace(/, NP$/, "").replace(/, PA$/, ""), TRx: N(r.trx), NBRx: N(r.nbrx), NTS: N(r.nts), Switches: N(r.switches), })); const filterChips = useMemo(() => { const chips = []; chips.push({ label: "Period", value: timeLabel }); if (drugClasses.length) chips.push({ label: "Class", value: drugClasses.join(", ") }); if (brands.length) chips.push({ label: "Brand", value: brands.join(", ") }); if (states.length) chips.push({ label: "State", value: states.join("/") }); if (cities.length) chips.push({ label: "City", value: cities.length === 1 ? cities[0] : `${cities.length} selected` }); if (territories.length) chips.push({ label: "Territory", value: territories.length === 1 ? territories[0] : `${territories.length} selected` }); if (specialties.length) chips.push({ label: "Specialty", value: specialties.length === 1 ? specialties[0] : `${specialties.length} selected` }); if (excludedHCPs.length) chips.push({ label: "Excluded", value: `${excludedHCPs.length} HCP${excludedHCPs.length === 1 ? "" : "s"}` }); return chips; }, [timeLabel, drugClasses, brands, states, cities, territories, specialties, excludedHCPs]); const NumCell = ({ v, active }) => ( {v.toLocaleString()} ); const seriesColors = { TRx: c.primary, NBRx: c.accent1, NTS: c.accent2, Switches: c.accent3, }; return (

{/* Masthead */}

Diabetes · West Coast · 2025

HCP Prescriber Explorer

Active Filters

setDark(!dark)} c={c} />

{filterChips.map((ch, i) => ( {ch.label}:{ch.value} ))}

{/* Time period + HCP exclusion strip */}

Time Period

{timePreset === "custom" && ( <>

Start

setCustomStart(e.target.value)} style={{ background: c.panel, border: `1px solid ${c.rule}`, color: c.ink, padding: "4px 10px", fontSize: 13, minHeight: 32 }} />

End

setCustomEnd(e.target.value)} style={{ background: c.panel, border: `1px solid ${c.rule}`, color: c.ink, padding: "4px 10px", fontSize: 13, minHeight: 32 }} />

)} setExcludedHCPs([...excludedHCPs, name])} onRemove={(name) => setExcludedHCPs(excludedHCPs.filter(n => n !== name))} suggestions={hcpLookup} c={c} />

{/* Filter bar */}

{ setStates(v); setTerritories([]); setCities([]); }} /> { setDrugClasses(v); setBrands([]); }} />

{/* KPIs */}

{/* Trend panel */}

Trend · {timeLabel}

━TRx ━NBRx ━NTS

{trendQuery.isLoading ? (

) : ( v >= 1000 ? `${(v / 1000).toFixed(1)}k` : `${v}`} /> v.toLocaleString()} contentStyle={{ background: c.panel, border: `1px solid ${c.ink}`, fontSize: 12, color: c.ink }} labelStyle={{ color: c.ink, fontWeight: 600 }} /> )}

{/* HCP grouped bar chart */}

HCP Activity Breakdown · Top {hcpBarData.length}

■TRx ■NBRx ■NTS ■Switches

{hcpQuery.isLoading ? (

) : hcpBarData.length === 0 ? (

No prescribers match the current filter selection.

) : ( v.toLocaleString()} contentStyle={{ background: c.panel, border: `1px solid ${c.ink}`, fontSize: 12, color: c.ink }} labelStyle={{ color: c.ink, fontWeight: 600 }} cursor={{ fill: c.hilite, opacity: 0.4 }} /> )}

{/* Brand summary panel */}

Brands in Selection

{brandQuery.isLoading ? (

) : brandRows.length === 0 ? (

No brands match the current filter selection.

) : ( {brandRows.map((r, i) => ( ))}

Brand	TRx	NBRx	NTS	Switches	Prescribers
{r.brand_name}	{N(r.trx).toLocaleString()}	{N(r.nbrx).toLocaleString()}	{N(r.nts).toLocaleString()}	{N(r.switches).toLocaleString()}	{N(r.prescribers).toLocaleString()}

)}

{/* Top prescribers panel */}

Top Prescribers

Sort by {SORT_OPTIONS.map(o => ( ))}

{hcpQuery.isLoading ? (

) : hcpRows.length === 0 ? (

No prescribers match the current filter selection.

) : ( {hcpRows.map((r, i) => ( ))}

HCP	Specialty	Location	Brand	TRx	NBRx	NTS	Switches
{String(i + 1).padStart(2, "0")} {r.hcp_name}	{r.specialty}	{r.city}, {r.state}	{r.brand_name}	{N(r.switches).toLocaleString()}

)}

); }

Sample HCP Prescriber Explorer

Comments