Single-binary self-hosted market watcher for stocks, ETFs, indexes, and futures: live charts, key stats, fundamentals, SEC filings, and SSE streaming.
axumdockerfinancerustself-hostedsqlitestocksvite
1use std::collections::HashMap;
2
3use axum::{
4 extract::{Path, Query, State},
5 http::StatusCode,
6 response::sse::Sse,
7 response::{IntoResponse, Response},
8 routing::{get, post},
9 Json, Router,
10};
11use chrono::Datelike as _;
12use serde::{Deserialize, Serialize};
13
14use crate::compute;
15use crate::db::now_ms;
16use crate::guard::{EndpointGuard, Permit};
17use crate::market;
18use crate::models::{self, SymbolRow};
19use crate::providers::http;
20use crate::providers::yahoo::{SymbolLookup, YahooProvider};
21use crate::render::{not_found, render};
22use crate::{scheduler, AppState};
23
24pub fn router() -> Router<AppState> {
25 Router::new()
26 .route("/s/{ticker}", get(symbol_page))
27 .route("/api/symbols", post(add_symbol))
28 .route("/api/symbols/{ticker}/history", get(history_api))
29 .route("/api/symbols/{ticker}/growth", get(growth_api))
30 .route("/api/symbols/{ticker}/refresh", get(refresh_stream))
31}
32
33/// Stats for the symbol page header and key-stats visualizations. Until
34/// intraday data exists (Phase 5), "day" figures come from the most recent
35/// daily bar. The `*_pos` fields are 0..100 marker positions for the Paper
36/// Ledger range bars; they are derived here so the template stays declarative.
37#[derive(Debug, Serialize)]
38struct Stats {
39 date: String,
40 open: f64,
41 high: f64,
42 low: f64,
43 close: f64,
44 volume: i64,
45 prev_close: Option<f64>,
46 change_abs: Option<f64>,
47 change_pct: Option<f64>,
48 /// Open relative to the prior close (the overnight gap).
49 open_change_pct: Option<f64>,
50 high_52w: f64,
51 low_52w: f64,
52 /// Mean daily volume over the recent ~3-month window.
53 avg_volume: i64,
54 /// Marker positions on the day's low..high range bar.
55 day_open_pos: f64,
56 day_close_pos: f64,
57 /// Marker positions on the 52-week low..high range bar.
58 yr_close_pos: f64,
59 yr_prev_pos: Option<f64>,
60 /// Today's volume on a 0..2x-average scale (the average sits at 50).
61 vol_fill_pct: f64,
62 /// Today's volume as a multiple of the average, e.g. 1.3 for 1.3x.
63 vol_ratio: Option<f64>,
64}
65
66/// The chart's indicators distilled into a colour-coded read: an overall
67/// trend verdict, an RSI momentum gauge, and one signal tile per moving average
68/// (where the price sits vs it) plus the 50/200 cross. Built from the daily
69/// closes; `None` until a symbol has enough history.
70#[derive(Serialize)]
71struct IndicatorRead {
72 /// Overall trend verdict ("Bullish" / "Mixed" / "Bearish") + its tone and a
73 /// one-line tally ("3 of 4 trend signals bullish").
74 verdict: String,
75 verdict_tone: String,
76 verdict_note: String,
77 /// RSI(14): the value, its 0–100 position (for the gauge), bucket label,
78 /// tone, and a plain-language verdict.
79 rsi: f64,
80 rsi_pos: f64,
81 rsi_label: String,
82 rsi_tone: String,
83 rsi_note: String,
84 /// One colour-coded tile per moving-average signal.
85 signals: Vec<IndicatorSignal>,
86}
87
88/// One signal tile: the indicator label, its current value, a short status word
89/// (Above / Below / Golden cross / …), the tone colour, and a plain meaning.
90#[derive(Serialize)]
91struct IndicatorSignal {
92 label: String,
93 value: String,
94 status: String,
95 tone: String,
96 note: String,
97}
98
99/// Build the colour-coded indicator read from the daily closes (oldest first),
100/// the current price, and whether the symbol is dollar-priced. Needs enough
101/// history for RSI(14)/EMA(21); the 50/200 averages join once they exist.
102fn build_indicator_read(
103 highs: &[f64],
104 lows: &[f64],
105 closes: &[f64],
106 price: f64,
107 dollar: bool,
108) -> Option<IndicatorRead> {
109 if closes.len() < 30 || price <= 0.0 {
110 return None;
111 }
112 let rsi = compute::rsi(closes, 14).last().copied().flatten()?;
113 let fmt = |v: f64| {
114 let n = format!("{v:.2}");
115 if dollar {
116 format!("${n}")
117 } else {
118 n
119 }
120 };
121
122 // RSI verdict: 70+/30- are the textbook overbought/oversold extremes; the
123 // 45–55 middle is balanced, with a "leaning" read on either side.
124 let (rsi_label, rsi_tone, rsi_note) = if rsi >= 70.0 {
125 ("Overbought", "down",
126 format!("RSI {rsi:.0} — overbought: momentum is stretched and may be due for a pullback."))
127 } else if rsi <= 30.0 {
128 ("Oversold", "up",
129 format!("RSI {rsi:.0} — oversold: selling looks stretched and may be due for a bounce."))
130 } else if rsi >= 55.0 {
131 ("Leaning bullish", "up",
132 format!("RSI {rsi:.0} — firm momentum, not yet overbought."))
133 } else if rsi <= 45.0 {
134 ("Leaning bearish", "down",
135 format!("RSI {rsi:.0} — soft momentum, not yet oversold."))
136 } else {
137 ("Neutral", "steady",
138 format!("RSI {rsi:.0} — momentum is balanced between buyers and sellers."))
139 };
140
141 let ema21 = compute::ema(closes, 21).last().copied().flatten();
142 let sma50 = compute::sma(closes, 50).last().copied().flatten();
143 let sma200 = compute::sma(closes, 200).last().copied().flatten();
144
145 // One tile per average that exists, each relating the current price to it.
146 let mut signals = Vec::new();
147 let mut bull = 0u32;
148 let mut total = 0u32;
149 let mut ma_tile = |label: &str, val: f64, span: &str| {
150 let above = price >= val;
151 if above {
152 bull += 1;
153 }
154 total += 1;
155 signals.push(IndicatorSignal {
156 label: label.to_string(),
157 value: fmt(val),
158 status: if above { "Above" } else { "Below" }.to_string(),
159 tone: if above { "up" } else { "down" }.to_string(),
160 note: format!("{span} trend is {}", if above { "up" } else { "down" }),
161 });
162 };
163 if let Some(v) = ema21 {
164 ma_tile("EMA 21", v, "Near-term");
165 }
166 if let Some(v) = sma50 {
167 ma_tile("SMA 50", v, "Medium-term");
168 }
169 if let Some(v) = sma200 {
170 ma_tile("SMA 200", v, "Long-term");
171 }
172
173 // The 50-vs-200 posture (the golden/death-cross regime).
174 if let (Some(f), Some(s)) = (sma50, sma200) {
175 let golden = f >= s;
176 if golden {
177 bull += 1;
178 }
179 total += 1;
180 signals.push(IndicatorSignal {
181 label: "50 / 200-day".to_string(),
182 value: String::new(),
183 status: if golden { "Golden cross" } else { "Death cross" }.to_string(),
184 tone: if golden { "up" } else { "down" }.to_string(),
185 note: if golden {
186 "50-day above the 200-day — bullish".to_string()
187 } else {
188 "50-day below the 200-day — bearish".to_string()
189 },
190 });
191 }
192
193 // Supertrend posture: which side of the ATR band price closed on. Folds
194 // into the same bullish/bearish tally as the moving-average signals.
195 let st = compute::supertrend(highs, lows, closes, compute::SUPERTREND_PERIOD, compute::SUPERTREND_MULT)
196 .last()
197 .copied()
198 .flatten();
199 if let Some(p) = st {
200 if p.up {
201 bull += 1;
202 }
203 total += 1;
204 signals.push(IndicatorSignal {
205 label: "Supertrend".to_string(),
206 value: fmt(p.value),
207 status: if p.up { "Uptrend" } else { "Downtrend" }.to_string(),
208 tone: if p.up { "up" } else { "down" }.to_string(),
209 note: if p.up {
210 "Price is holding above the Supertrend band — bullish".to_string()
211 } else {
212 "Price is below the Supertrend band — bearish".to_string()
213 },
214 });
215 }
216
217 // Overall verdict from the trend tally.
218 let (verdict, verdict_tone) = if total == 0 {
219 ("No signal", "steady")
220 } else if bull == total {
221 ("Bullish", "up")
222 } else if bull == 0 {
223 ("Bearish", "down")
224 } else {
225 ("Mixed", "warn")
226 };
227 let verdict_note = format!("{bull} of {total} trend signals bullish");
228
229 Some(IndicatorRead {
230 verdict: verdict.to_string(),
231 verdict_tone: verdict_tone.to_string(),
232 verdict_note,
233 rsi,
234 rsi_pos: rsi.clamp(0.0, 100.0),
235 rsi_label: rsi_label.to_string(),
236 rsi_tone: rsi_tone.to_string(),
237 rsi_note,
238 signals,
239 })
240}
241
242/// The live quote shown in the symbol header, when one exists. The header
243/// carries `data-field` hooks, so the stream client patches these in place as
244/// fresh quotes arrive; this is just the server-rendered starting point.
245#[derive(Debug, Serialize)]
246struct HeaderQuote {
247 price: f64,
248 change_abs: Option<f64>,
249 change_pct: Option<f64>,
250 /// A short human label for the quote's freshness, e.g. "Live", "At close".
251 state_label: String,
252}
253
254#[derive(sqlx::FromRow)]
255struct QuoteRow {
256 price: f64,
257 prev_close: Option<f64>,
258 /// When this quote was sourced (epoch-ms), so the header's freshness label
259 /// reflects the quote's real age, not just the wall-clock session.
260 fetched_at: Option<i64>,
261}
262
263/// How recent a stored quote must be to read as current during an open session.
264/// The intraday poll refreshes watched symbols about every 5 minutes and the
265/// home sweep every 15; past this a quote is no longer "live", and the header
266/// says so rather than asserting a freshness the number does not have.
267const QUOTE_FRESH_MS: i64 = 15 * 60 * 1000;
268
269/// A short freshness label for the symbol header, honest about the quote's age.
270/// Yahoo's chart endpoint carries no market-state field, so the *session* comes
271/// from our own clock (`market.rs`); the *freshness* comes from how long ago the
272/// quote was actually sourced (`quoted_at`, epoch-ms). A stale quote during an
273/// open session reads "Delayed" instead of a false "Live". After the close the
274/// shown number IS the day's close, so its age does not change the "At close"
275/// reading.
276fn quote_state_label(quoted_at: Option<i64>) -> &'static str {
277 use market::Session::{Closed, Post, Pre, Regular};
278 let fresh = quoted_at.is_some_and(|t| now_ms() - t <= QUOTE_FRESH_MS);
279 match market::session_at(chrono::Utc::now()) {
280 Pre => if fresh { "Pre-market" } else { "Delayed" },
281 Regular => if fresh { "Live" } else { "Delayed" },
282 Post => if fresh { "After hours" } else { "Delayed" },
283 Closed => "At close",
284 }
285}
286
287// ── fundamentals + filings (Phase 7) ──────────────────────────────────────
288
289/// Placeholder glyph for a value the company did not report — an em dash, an
290/// unambiguous "no data" mark (a middle dot read as a stray decimal point).
291const DASH: &str = "\u{2014}";
292
293/// How fresh a struck NAV must be before a price-vs-NAV premium/discount is
294/// trustworthy. NAV is struck once per trading day, so a NAV older than this is
295/// stale and any "premium" against it is really just price drift since then; the
296/// premium drops to `None` rather than assert a bogus figure. Shared by the
297/// "About this fund" premium line and the ETF quality read's tracking factor.
298const NAV_FRESH_MS: i64 = 3 * 24 * 3600 * 1000;
299
300/// A symbol whose most recent daily bar is older than this (calendar days) is
301/// treated as dormant: likely delisted, renamed, or halted. Comfortably past a
302/// stacked holiday weekend so a normally-trading symbol never trips it. Yahoo
303/// still serves a frozen chart for a dead ticker (so the page would otherwise
304/// look live), which is exactly why this banner exists.
305const STALE_DATA_DAYS: i64 = 7;
306
307/// A "no recent trading data" banner for the symbol header: the date of the last
308/// bar we hold and how many days stale it is, so a dormant/delisted symbol reads
309/// honestly instead of showing a frozen chart as if it were current.
310#[derive(Serialize)]
311struct StaleData {
312 last_date: String,
313 days: i64,
314}
315
316/// Whether a period-over-period rise in a metric is good news, for the
317/// financials-table growth cue.
318#[derive(Clone, Copy)]
319enum Trend {
320 /// A rise reads as good: revenue, earnings, dividends.
321 RiseGood,
322 /// A rise reads as bad: liabilities.
323 RiseBad,
324 /// No good/bad reading — total assets and equity, where a rise can be
325 /// debt-funded or a fall can be a shareholder-friendly buyback. The cue
326 /// still shows the direction, just without a colour.
327 Neutral,
328}
329
330impl Trend {
331 /// How a rise vs the prior period reads: `good`, `bad`, or `` (no colour).
332 fn rise(self) -> &'static str {
333 match self {
334 Trend::RiseGood => "good",
335 Trend::RiseBad => "bad",
336 Trend::Neutral => "",
337 }
338 }
339 /// How a fall vs the prior period reads.
340 fn fall(self) -> &'static str {
341 match self {
342 Trend::RiseGood => "bad",
343 Trend::RiseBad => "good",
344 Trend::Neutral => "",
345 }
346 }
347}
348
349/// One cell of a financials table: a formatted figure and its period-over-
350/// period growth cue.
351#[derive(Serialize)]
352struct FundCell {
353 /// The formatted figure, or [`DASH`] where nothing was reported.
354 display: String,
355 /// Direction vs the column to its left: `up`, `down`, or `` (the first
356 /// column, a flat figure, or a missing value on either side).
357 dir: &'static str,
358 /// How that move reads for this metric: `good`, `bad`, or `` (no colour).
359 sense: &'static str,
360}
361
362/// One row of a financials table: a metric label and one cell per period.
363#[derive(Serialize)]
364struct FundRow {
365 label: String,
366 cells: Vec<FundCell>,
367}
368
369/// A financials table (annual or quarterly) as period columns and metric rows.
370#[derive(Serialize)]
371struct FundTable {
372 /// Column headers, oldest period first.
373 periods: Vec<String>,
374 rows: Vec<FundRow>,
375}
376
377/// Everything the symbol page's fundamentals + financials sections need.
378#[derive(Serialize)]
379struct FundamentalsView {
380 /// Fiscal period the annual ratios are based on, e.g. `FY2024`.
381 basis: Option<String>,
382 /// What the P/E's earnings figure is, e.g. `TTM through Jun 2025` (trailing
383 /// twelve months) or `FY2024` when fewer than four quarters are available.
384 pe_basis: Option<String>,
385 /// The most recent reported period (e.g. `Jun 2025`), for the freshness note.
386 earnings_period: Option<String>,
387 /// True when the latest reported quarter is old enough that the price-based
388 /// ratios may lag a more recent quarter, so the page can flag it.
389 earnings_stale: bool,
390 ratios: Vec<compute::Ratio>,
391 annual: FundTable,
392 quarterly: FundTable,
393 has_annual: bool,
394 has_quarterly: bool,
395}
396
397/// Trailing-twelve-month diluted EPS: the sum of the four most recent quarterly
398/// diluted-EPS facts (the derived Q4 included), with the latest quarter's period
399/// end. `None` with fewer than four quarters. Diluted EPS does not decompose
400/// perfectly quarter to quarter, but a TTM sum tracks current earnings far better
401/// than a fiscal-year figure that can be 6-12 months stale — which is exactly what
402/// makes a live-price P/E drift. Returns `(ttm_eps, latest_period_end)`.
403fn ttm_eps_diluted(facts: &[models::FundFact]) -> Option<(f64, String)> {
404 let mut q: Vec<(&str, f64)> = facts
405 .iter()
406 .filter(|f| f.fiscal_qtr.is_some() && f.metric == "eps_diluted")
407 .map(|f| (f.period_end.as_str(), f.value))
408 .collect();
409 // Most recent period first, one row per quarter.
410 q.sort_by(|a, b| b.0.cmp(a.0));
411 q.dedup_by(|a, b| a.0 == b.0);
412 if q.len() < 4 {
413 return None;
414 }
415 let four = &q[..4];
416 // The four quarters must be *consecutive*, or their sum is not a real
417 // trailing twelve months: a missing quarter would splice together periods
418 // spanning more than a year and still label it "TTM". Require each adjacent
419 // pair of period-ends to sit about one quarter apart (~80–100 days); when
420 // they don't, return None so the caller falls back to full-year EPS.
421 let parse = |d: &str| chrono::NaiveDate::parse_from_str(d, "%Y-%m-%d").ok();
422 for pair in four.windows(2) {
423 let gap = (parse(pair[0].0)? - parse(pair[1].0)?).num_days();
424 if !(80..=100).contains(&gap) {
425 return None;
426 }
427 }
428 Some((four.iter().map(|(_, v)| *v).sum(), four[0].0.to_string()))
429}
430
431/// A `YYYY-MM-DD` period end as a short `Mon YYYY` label (e.g. `Jun 2025`).
432fn fmt_period(end: &str) -> String {
433 chrono::NaiveDate::parse_from_str(end, "%Y-%m-%d")
434 .map(|d| d.format("%b %Y").to_string())
435 .unwrap_or_else(|_| end.to_string())
436}
437
438/// Days since a `YYYY-MM-DD` period end. Large numbers mean a missing or stale
439/// recent filing. Returns `i64::MAX` when the date does not parse (treated stale).
440fn period_age_days(end: &str) -> i64 {
441 match chrono::NaiveDate::parse_from_str(end, "%Y-%m-%d") {
442 Ok(d) => (chrono::Utc::now().date_naive() - d).num_days(),
443 Err(_) => i64::MAX,
444 }
445}
446
447/// One SEC filing shaped for the page.
448#[derive(Serialize)]
449struct FilingView {
450 /// The raw form type, e.g. `10-K`, shown as a badge.
451 form: String,
452 /// A plain-English title derived from the form.
453 title: String,
454 filed_at: String,
455 period_of_report: Option<String>,
456 url: String,
457}
458
459#[derive(sqlx::FromRow)]
460struct FilingRow {
461 form: String,
462 filed_at: String,
463 period_of_report: Option<String>,
464 url: String,
465}
466
467/// The metrics shown as rows of the financials table, in order. `is_money` is
468/// `true` for a whole-dollar figure (shown compact, e.g. `$391.0B`) and
469/// `false` for a per-share figure (shown as plain dollars, e.g. `$6.08`).
470/// `in_quarterly` is `false` for the balance-sheet rows: only the fiscal
471/// year-end balance is collected, so those rows appear in the annual table
472/// only (see `providers::sec::classify`). `trend` sets the period-over-period
473/// growth cue's good/bad reading.
474struct TableMetric {
475 metric: &'static str,
476 label: &'static str,
477 is_money: bool,
478 in_quarterly: bool,
479 trend: Trend,
480}
481
482const FUND_TABLE_METRICS: &[TableMetric] = &[
483 TableMetric { metric: "revenue", label: "Revenue", is_money: true, in_quarterly: true, trend: Trend::RiseGood },
484 TableMetric { metric: "net_income", label: "Net income", is_money: true, in_quarterly: true, trend: Trend::RiseGood },
485 TableMetric { metric: "eps_diluted", label: "Diluted EPS", is_money: false, in_quarterly: true, trend: Trend::RiseGood },
486 TableMetric { metric: "dividends_per_share", label: "Dividend / share", is_money: false, in_quarterly: true, trend: Trend::RiseGood },
487 TableMetric { metric: "assets", label: "Total assets", is_money: true, in_quarterly: false, trend: Trend::Neutral },
488 TableMetric { metric: "liabilities", label: "Total liabilities", is_money: true, in_quarterly: false, trend: Trend::RiseBad },
489 TableMetric { metric: "equity", label: "Shareholder equity", is_money: true, in_quarterly: false, trend: Trend::Neutral },
490];
491
492/// Format a whole-dollar figure compactly: `391035000000.0` -> `$391.0B`.
493fn fmt_usd_compact(v: f64) -> String {
494 let sign = if v < 0.0 { "-" } else { "" };
495 let a = v.abs();
496 let (n, suffix) = if a >= 1e12 {
497 (a / 1e12, "T")
498 } else if a >= 1e9 {
499 (a / 1e9, "B")
500 } else if a >= 1e6 {
501 (a / 1e6, "M")
502 } else if a >= 1e3 {
503 (a / 1e3, "K")
504 } else {
505 (a, "")
506 };
507 if suffix.is_empty() {
508 format!("{sign}${n:.0}")
509 } else {
510 format!("{sign}${n:.1}{suffix}")
511 }
512}
513
514/// Format a per-share figure: `6.08` -> `$6.08`.
515fn fmt_per_share(v: f64) -> String {
516 format!("${v:.2}")
517}
518
519/// A plain-English title for a filing, derived from its form type.
520fn filing_title(form: &str) -> String {
521 let base = if form.starts_with("10-K") {
522 "Annual report"
523 } else if form.starts_with("10-Q") {
524 "Quarterly report"
525 } else if form.starts_with("8-K") {
526 "Current report"
527 } else if form.starts_with("DEF 14A") {
528 "Proxy statement"
529 } else if form.starts_with("20-F") || form.starts_with("40-F") {
530 "Annual report"
531 } else if form.starts_with("6-K") {
532 "Interim report"
533 } else if form.starts_with("NPORT") {
534 "Portfolio holdings report"
535 } else if form.starts_with("N-CEN") {
536 "Annual fund census"
537 } else if form.starts_with("N-CSR") {
538 "Shareholder report"
539 } else if form.starts_with("485") {
540 "Prospectus"
541 } else {
542 "Filing"
543 };
544 if form.ends_with("/A") {
545 format!("{base} (amended)")
546 } else {
547 base.to_string()
548 }
549}
550
551/// Build one financials table for the given periods (each `(fiscal_year,
552/// period_label)`, oldest first), pulling formatted cells from `lookup`. The
553/// quarterly table omits the balance-sheet rows, which are only collected per
554/// fiscal year. Each cell also carries a period-over-period growth cue
555/// computed against the column to its left.
556fn fund_table(
557 periods: &[(i64, String)],
558 lookup: &HashMap<(String, String), f64>,
559 quarterly: bool,
560) -> FundTable {
561 let rows = FUND_TABLE_METRICS
562 .iter()
563 .filter(|m| !quarterly || m.in_quarterly)
564 .map(|m| {
565 // Walk periods oldest-first, carrying the prior period's value so
566 // each cell can be marked up / down against the one before it.
567 let mut prev: Option<f64> = None;
568 let cells = periods
569 .iter()
570 .map(|(_, period)| {
571 let value = lookup
572 .get(&(m.metric.to_string(), period.clone()))
573 .copied();
574 let display = match value {
575 Some(v) if m.is_money => fmt_usd_compact(v),
576 Some(v) => fmt_per_share(v),
577 None => DASH.to_string(),
578 };
579 let (dir, sense) = match (prev, value) {
580 (Some(p), Some(v)) if v > p => ("up", m.trend.rise()),
581 (Some(p), Some(v)) if v < p => ("down", m.trend.fall()),
582 _ => ("", ""),
583 };
584 prev = value;
585 FundCell { display, dir, sense }
586 })
587 .collect();
588 FundRow {
589 label: m.label.to_string(),
590 cells,
591 }
592 })
593 .collect();
594 FundTable {
595 periods: periods.iter().map(|(_, p)| p.clone()).collect(),
596 rows,
597 }
598}
599
600/// The flow / per-share metrics whose Q4 can be derived as the full fiscal
601/// year minus its first three quarters. The balance-sheet metrics are excluded
602/// — a year-end balance is a snapshot, not a sum of quarters.
603const Q4_DERIVABLE: &[&str] = &["revenue", "net_income", "eps_diluted", "dividends_per_share"];
604
605/// Derive the missing Q4 facts. SEC XBRL carries no discrete fourth quarter:
606/// there is no Q4 10-Q, so Q4 lives only inside the 10-K's full-year figure.
607/// For every fiscal year with the full year and all three prior quarters
608/// present, Q4 is `FY - (Q1 + Q2 + Q3)`. Diluted EPS does
609/// not decompose perfectly — the diluted share count drifts quarter to quarter
610/// — but the residual is small and the plan calls for showing it.
611fn derive_q4(facts: &[models::FundFact]) -> Vec<models::FundFact> {
612 // (metric, fiscal_year) -> fiscal_qtr (None = full year) -> (value, period_end).
613 let mut by: HashMap<(&str, i64), HashMap<Option<i64>, (f64, String)>> = HashMap::new();
614 for f in facts {
615 by.entry((f.metric.as_str(), f.fiscal_year))
616 .or_default()
617 .insert(f.fiscal_qtr, (f.value, f.period_end.clone()));
618 }
619 let mut derived = Vec::new();
620 for ((metric, year), vals) in by {
621 // A genuine Q4 row (rare, but XBRL does carry a few) always wins.
622 if !Q4_DERIVABLE.contains(&metric) || vals.contains_key(&Some(4)) {
623 continue;
624 }
625 let (Some(fy), Some(q1), Some(q2), Some(q3)) = (
626 vals.get(&None),
627 vals.get(&Some(1)),
628 vals.get(&Some(2)),
629 vals.get(&Some(3)),
630 ) else {
631 continue;
632 };
633 derived.push(models::FundFact {
634 metric: metric.to_string(),
635 period: format!("Q4-{year}"),
636 fiscal_year: year,
637 fiscal_qtr: Some(4),
638 value: fy.0 - q1.0 - q2.0 - q3.0,
639 // The synthetic Q4 ends on the FY's period end (Q4 closes the fiscal year).
640 period_end: fy.1.clone(),
641 });
642 }
643 derived
644}
645
646/// Assemble the fundamentals view from a company's stored facts plus the
647/// latest price. `None` when the company has no fundamentals stored yet.
648fn build_fundamentals(facts: &[models::FundFact], price: Option<f64>) -> Option<FundamentalsView> {
649 if facts.is_empty() {
650 return None;
651 }
652
653 // SEC XBRL has no discrete Q4; derive it and fold the
654 // derived rows in, so the quarterly periods and the cell lookup below pick
655 // them up exactly like a stored fact.
656 let derived = derive_q4(facts);
657 let facts: Vec<models::FundFact> = facts.iter().cloned().chain(derived).collect();
658 let facts: &[models::FundFact] = &facts;
659
660 // (metric, period) -> value, for table-cell lookup.
661 let mut lookup: HashMap<(String, String), f64> = HashMap::new();
662 for f in facts {
663 lookup.insert((f.metric.clone(), f.period.clone()), f.value);
664 }
665
666 // Distinct annual periods, oldest first, most recent 5 kept.
667 let mut annual: Vec<(i64, String)> = facts
668 .iter()
669 .filter(|f| f.fiscal_qtr.is_none())
670 .map(|f| (f.fiscal_year, f.period.clone()))
671 .collect();
672 annual.sort();
673 annual.dedup();
674 let annual: Vec<(i64, String)> = annual.into_iter().rev().take(5).rev().collect();
675
676 // Distinct quarterly periods, oldest first, most recent 8 kept.
677 let mut quarterly: Vec<(i64, i64, String)> = facts
678 .iter()
679 .filter_map(|f| f.fiscal_qtr.map(|q| (f.fiscal_year, q, f.period.clone())))
680 .collect();
681 quarterly.sort();
682 quarterly.dedup();
683 let quarterly: Vec<(i64, String)> = quarterly
684 .into_iter()
685 .rev()
686 .take(8)
687 .rev()
688 .map(|(y, _, p)| (y, p))
689 .collect();
690
691 // Balance-sheet + margin ratios run off the most recent full fiscal year (the
692 // shared helper, so the home ranking grades stocks the same way). The P/E,
693 // though, divides a LIVE price, so it gets trailing-twelve-month EPS when we
694 // have four quarters: dividing today's price by a 6-12-month-old fiscal-year
695 // EPS is what made the P/E look wrong after a price move.
696 let latest_fy = annual.last().map(|(y, _)| *y);
697 let ttm = ttm_eps_diluted(facts);
698 let inputs = models::latest_annual_inputs(facts, price).map(|mut i| {
699 if let Some((eps, _)) = ttm {
700 i.eps_diluted = Some(eps);
701 }
702 i
703 });
704 let ratios = inputs.map(|i| compute::compute_ratios(&i)).unwrap_or_default();
705
706 let pe_basis = match &ttm {
707 Some((_, end)) => Some(format!("TTM through {}", fmt_period(end))),
708 None => latest_fy.map(|y| format!("FY{y}")),
709 };
710 // The most recent reported period across all facts, and whether it is old
711 // enough that the price-based ratios may lag a newer quarter. Companies file
712 // ~45 days after a quarter ends, so a latest period older than ~150 days
713 // means a recent quarter is missing or the SEC pull is stale.
714 let latest_period_end = facts.iter().map(|f| f.period_end.as_str()).max();
715 let earnings_period = latest_period_end.map(fmt_period);
716 let earnings_stale = latest_period_end.is_some_and(|d| period_age_days(d) > 150);
717
718 Some(FundamentalsView {
719 basis: latest_fy.map(|y| format!("FY{y}")),
720 pe_basis,
721 earnings_period,
722 earnings_stale,
723 ratios,
724 has_annual: !annual.is_empty(),
725 has_quarterly: !quarterly.is_empty(),
726 annual: fund_table(&annual, &lookup, false),
727 quarterly: fund_table(&quarterly, &lookup, true),
728 })
729}
730
731// ── ETF fund profile (Phase 18) ────────────────────────────────────────────
732
733/// A `fund_profiles` row as stored.
734#[derive(sqlx::FromRow)]
735struct FundProfileRow {
736 /// `portfolio` or `commodity_trust`.
737 kind: String,
738 net_assets: Option<f64>,
739 holdings_count: Option<i64>,
740 report_date: Option<String>,
741 /// JSON `[[bucket, percent], ...]`.
742 asset_mix: Option<String>,
743 /// JSON `[[label, percent], ...]`, from each holding's N-PORT
744 /// `<issuerCat>` (Phase 28). Often degenerate on an equity ETF.
745 sector_mix: Option<String>,
746 /// JSON `[[label, percent], ...]`, from each holding's N-PORT
747 /// `<invCountry>` (Phase 28). Often US-dominant.
748 geography_mix: Option<String>,
749}
750
751/// A `fund_holdings` row as stored.
752#[derive(sqlx::FromRow)]
753struct HoldingRow {
754 rank: i64,
755 name: String,
756 pct: Option<f64>,
757 value_usd: Option<f64>,
758}
759
760/// One asset-class slice of an ETF's portfolio mix.
761#[derive(Serialize)]
762struct AssetSlice {
763 label: String,
764 /// Percent string, e.g. `99.8%`.
765 pct: String,
766 /// Segment width 0..100 for the mix bar.
767 width: f64,
768}
769
770/// One holding row shaped for the page.
771#[derive(Serialize)]
772struct HoldingView {
773 rank: i64,
774 name: String,
775 /// Weight as a percent string, e.g. `8.42%`.
776 weight: String,
777 /// Bar width 0..100, scaled so the largest holding shown fills the rail.
778 bar_pct: f64,
779 /// Position value, compact USD, e.g. `$3.3B`.
780 value: String,
781}
782
783/// Everything the symbol page's ETF fund-profile section needs.
784#[derive(Serialize)]
785struct FundView {
786 /// A physical-commodity grantor trust (GLD, SLV): holds bullion, not a
787 /// securities portfolio, so it has no holdings and no asset mix.
788 is_commodity: bool,
789 /// Net assets / AUM, compact USD. `None` when the fund reported none.
790 net_assets: Option<String>,
791 holdings_count: Option<i64>,
792 /// The N-PORT "as of" date, `YYYY-MM-DD`.
793 report_date: Option<String>,
794 asset_mix: Vec<AssetSlice>,
795 /// N-PORT issuer-category mix (Phase 28). Empty / single-bucket on an
796 /// equity ETF where everything rolls up to one bucket — the template
797 /// hides the panel in that case rather than rendering a flat bar.
798 sector_mix: Vec<AssetSlice>,
799 /// N-PORT issuer-country mix (Phase 28). Empty / US-only on a domestic
800 /// ETF; hidden by the template the same way.
801 geography_mix: Vec<AssetSlice>,
802 holdings: Vec<HoldingView>,
803}
804
805/// Parse a stored mix JSON column into the page's `AssetSlice` row shape.
806/// Phase 28 calls this for asset / sector / geography mixes alike.
807fn parse_mix(json: Option<&str>) -> Vec<AssetSlice> {
808 json.and_then(|j| serde_json::from_str::<Vec<(String, f64)>>(j).ok())
809 .unwrap_or_default()
810 .into_iter()
811 .map(|(label, pct)| AssetSlice {
812 pct: format!("{pct:.1}%"),
813 width: pct.clamp(0.0, 100.0),
814 label,
815 })
816 .collect()
817}
818
819/// Assemble the fund view from a stored profile row and its holdings.
820fn build_fund(profile: FundProfileRow, holdings: Vec<HoldingRow>) -> FundView {
821 let asset_mix = parse_mix(profile.asset_mix.as_deref());
822 let sector_mix = parse_mix(profile.sector_mix.as_deref());
823 let geography_mix = parse_mix(profile.geography_mix.as_deref());
824
825 // Holdings: each weight bar is scaled to the largest holding shown, so the
826 // top position fills the rail and the rest read against it.
827 let max_pct = holdings.iter().filter_map(|h| h.pct).fold(0.0_f64, f64::max);
828 let holdings = holdings
829 .into_iter()
830 .map(|h| HoldingView {
831 rank: h.rank,
832 name: h.name,
833 weight: h.pct.map_or_else(|| DASH.to_string(), |p| format!("{p:.2}%")),
834 bar_pct: match h.pct {
835 Some(p) if max_pct > 0.0 => (p / max_pct * 100.0).clamp(0.0, 100.0),
836 _ => 0.0,
837 },
838 value: h.value_usd.map_or_else(|| DASH.to_string(), fmt_usd_compact),
839 })
840 .collect();
841
842 FundView {
843 is_commodity: profile.kind == "commodity_trust",
844 net_assets: profile.net_assets.map(fmt_usd_compact),
845 holdings_count: profile.holdings_count,
846 report_date: profile.report_date,
847 asset_mix,
848 sector_mix,
849 geography_mix,
850 holdings,
851 }
852}
853
854// ── ETF fund metadata + trailing returns (Phase 28) ────────────────────────
855
856/// A `fund_metadata` row as stored.
857#[derive(sqlx::FromRow)]
858struct FundMetadataRow {
859 expense_ratio: Option<f64>,
860 yield_pct: Option<f64>,
861 trailing_yield_pct: Option<f64>,
862 nav_price: Option<f64>,
863 inception_date: Option<String>,
864 category: Option<String>,
865 fund_family: Option<String>,
866 strategy_summary: Option<String>,
867 /// When the daily `fund_nav` job last refreshed `nav_price` (Phase 4). The
868 /// quality read's tracking factor is only graded against a fresh NAV; a
869 /// stale one drops the factor rather than asserting a bogus premium.
870 nav_synced_at: Option<i64>,
871}
872
873/// The "About this fund" section of the ETF symbol page. Every field is
874/// pre-formatted, so the template stays declarative; an unpopulated field
875/// becomes `—` rather than a hole in the layout.
876#[derive(Serialize)]
877struct FundMetaView {
878 expense_ratio: String,
879 yield_pct: String,
880 nav_price: Option<f64>,
881 /// Pre-formatted premium / discount, e.g. `+0.12%`, with a good/ok/bad
882 /// `Grade` so the template can colour-band it. `None` when no NAV.
883 premium: Option<PremiumView>,
884 inception_date: Option<String>,
885 category: Option<String>,
886 fund_family: Option<String>,
887 strategy_summary: Option<String>,
888}
889
890#[derive(Serialize)]
891struct PremiumView {
892 /// Signed pre-formatted percent, e.g. `+0.12%` / `-0.45%`.
893 text: String,
894 /// Grade for the semantic colour band: Good (tight), Ok, Bad (wide).
895 grade: compute::Grade,
896}
897
898fn build_fund_meta(row: FundMetadataRow, price: Option<f64>) -> FundMetaView {
899 let pct = |v: Option<f64>, dp: usize| -> String {
900 v.map_or_else(|| DASH.to_string(), |x| format!("{:.*}%", dp, x * 100.0))
901 };
902 // Premium / discount: live price against the latest NAV, but only when that
903 // NAV is fresh (struck within NAV_FRESH_MS). Comparing a live price to a
904 // days-old NAV yields a meaningless premium, so a stale NAV drops the line to
905 // `None` rather than show drift as a premium — the same gate the ETF quality
906 // read's tracking factor uses, so the two never disagree. Live price falls
907 // back to the daily close when no quote yet, just as the ratio cards do.
908 let nav_fresh = row
909 .nav_synced_at
910 .is_some_and(|t| now_ms() - t <= NAV_FRESH_MS);
911 let premium = price
912 .filter(|_| nav_fresh)
913 .and_then(|p| compute::premium_discount_pct(p, row.nav_price))
914 .map(|pct| PremiumView {
915 text: format!("{:+.2}%", pct),
916 grade: compute::premium_grade(pct),
917 });
918 FundMetaView {
919 expense_ratio: pct(row.expense_ratio, 2),
920 yield_pct: pct(row.yield_pct.or(row.trailing_yield_pct), 2),
921 nav_price: row.nav_price,
922 premium,
923 inception_date: row.inception_date,
924 category: row.category,
925 fund_family: row.fund_family,
926 strategy_summary: row.strategy_summary,
927 }
928}
929
930/// One row of the trailing-returns table, pre-formatted.
931#[derive(Serialize)]
932struct ReturnRow {
933 label: &'static str,
934 /// Cumulative percent move, e.g. `+18.27%`. `—` when missing.
935 pct: String,
936 /// Annualised percent for periods over 1 year, blank `""` for the YTD /
937 /// 1m / 3m rows (where annualising is misleading) and `—` when missing.
938 annualised: String,
939 /// Whether `pct` is positive (green), negative (red), or unknown (none).
940 dir: i8,
941}
942
943fn fmt_pct(v: Option<f64>) -> (String, i8) {
944 match v {
945 Some(v) => {
946 let dir = if v > 0.0 { 1 } else if v < 0.0 { -1 } else { 0 };
947 (format!("{:+.2}%", v), dir)
948 }
949 None => (DASH.to_string(), 0),
950 }
951}
952
953fn build_returns(r: &compute::TrailingReturns) -> Vec<ReturnRow> {
954 let row = |label: &'static str, tr: Option<compute::TrailingReturn>, annualised: bool| {
955 let (pct, dir) = fmt_pct(tr.map(|t| t.pct));
956 let annualised = if annualised {
957 match tr {
958 Some(t) => format!("{:+.2}%", t.annualised_pct),
959 None => DASH.to_string(),
960 }
961 } else {
962 String::new()
963 };
964 ReturnRow {
965 label,
966 pct,
967 annualised,
968 dir,
969 }
970 };
971 vec![
972 row("1 month", r.m1, false),
973 row("3 months", r.m3, false),
974 row("Year to date", r.ytd, false),
975 row("1 year", r.y1, false),
976 row("3 years", r.y3, true),
977 row("5 years", r.y5, true),
978 row("10 years", r.y10, true),
979 row("Since inception", r.since_inception, true),
980 ]
981}
982
983// ── dividend payouts (Phase 26) ────────────────────────────────────────────
984
985/// One dividend payment, shaped for the page.
986#[derive(Serialize)]
987struct DividendRow {
988 /// Ex-dividend date, `YYYY-MM-DD` (the template's `shortdate` filter
989 /// formats it for display).
990 ex_date: String,
991 /// Per-share amount, formatted as plain dollars, e.g. `$0.24`.
992 amount: String,
993}
994
995/// Everything the symbol page's Dividends section needs.
996#[derive(Serialize)]
997struct DividendsView {
998 /// Whether the Yahoo dividend sweep has reached this stock yet — picks the
999 /// "not synced yet" pending note apart from a genuine no-dividends history.
1000 synced: bool,
1001 /// The inferred pace read: cadence, prior-year and YTD totals, projection,
1002 /// and the on-track grade.
1003 pace: compute::DividendPace,
1004 /// Prior-year total per share, formatted, e.g. `$0.92`. Empty string when
1005 /// there were no payouts in the prior calendar year.
1006 prior_year_display: String,
1007 /// YTD total per share, formatted.
1008 ytd_display: String,
1009 /// Calendar year YTD belongs to (e.g. `2026`).
1010 current_year: i32,
1011 /// Projected current-year total, formatted; `None` when the projection is.
1012 projection_display: Option<String>,
1013 /// Signed percent change vs prior year, e.g. `+4.3%`; `None` when the
1014 /// projection is.
1015 pct_change_display: Option<String>,
1016 /// All payouts on file, newest first.
1017 history: Vec<DividendRow>,
1018}
1019
1020/// Load the Dividends section for a stock. Returns `None`
1021/// when there is nothing to show *and* the sweep has already run: a stock that
1022/// pays no dividend gets no section. A pending stock (sweep has not reached it
1023/// yet) still returns a `DividendsView` so the template can render the "not
1024/// synced yet" note in place.
1025async fn build_dividends(
1026 pool: &sqlx::SqlitePool,
1027 ticker: &str,
1028 synced: bool,
1029) -> Option<DividendsView> {
1030 // Newest first for the per-event history; the pace math wants oldest first.
1031 let rows: Vec<(String, f64)> = sqlx::query_as(
1032 "SELECT ex_date, amount FROM dividends WHERE ticker = ? ORDER BY ex_date DESC",
1033 )
1034 .bind(ticker)
1035 .fetch_all(pool)
1036 .await
1037 .unwrap_or_default();
1038
1039 // Pending sweep on a stock with no payouts yet — show the pending note.
1040 if rows.is_empty() && !synced {
1041 let pace = compute::dividend_pace(&[], chrono::Utc::now().date_naive());
1042 return Some(DividendsView {
1043 synced: false,
1044 pace,
1045 prior_year_display: String::new(),
1046 ytd_display: String::new(),
1047 current_year: chrono::Utc::now().date_naive().year(),
1048 projection_display: None,
1049 pct_change_display: None,
1050 history: Vec::new(),
1051 });
1052 }
1053 // A swept stock with no payouts pays no dividend — hide the section
1054 // entirely rather than render a heading over an empty table.
1055 if rows.is_empty() {
1056 return None;
1057 }
1058
1059 let oldest_first: Vec<(String, f64)> = rows.iter().rev().cloned().collect();
1060 let pace = compute::dividend_pace(&oldest_first, chrono::Utc::now().date_naive());
1061 // Per-share dividends are usually quoted to the cent; monthly REITs sometimes
1062 // pay sub-cent amounts (e.g. `$0.0625`), so a sub-cent figure widens to 4dp.
1063 let fmt_div = |v: f64| if v < 0.01 { format!("${v:.4}") } else { format!("${v:.2}") };
1064 let history: Vec<DividendRow> = rows
1065 .iter()
1066 .map(|(d, a)| DividendRow {
1067 ex_date: d.clone(),
1068 amount: fmt_div(*a),
1069 })
1070 .collect();
1071 // Totals and the projection are annual sums of those per-share amounts;
1072 // keep the same precision rule so a small payout's effect is not rounded off.
1073 let fmt_money = fmt_div;
1074 Some(DividendsView {
1075 synced,
1076 prior_year_display: fmt_money(pace.prior_year_total),
1077 ytd_display: fmt_money(pace.ytd_total),
1078 projection_display: pace.projection.map(fmt_money),
1079 pct_change_display: pace.pct_change.map(|p| format!("{p:+.1}%")),
1080 current_year: chrono::Utc::now().date_naive().year(),
1081 history,
1082 pace,
1083 })
1084}
1085
1086// ── company leadership (Phase 14) ──────────────────────────────────────────
1087
1088/// A `leadership` row as stored.
1089#[derive(sqlx::FromRow)]
1090struct LeadershipRow {
1091 name: String,
1092 is_director: i64,
1093 is_officer: i64,
1094 officer_title: Option<String>,
1095}
1096
1097/// One person on the leadership roster, shaped for the page.
1098#[derive(Serialize)]
1099struct LeaderView {
1100 /// Display name, title-cased from the as-filed upper-case form.
1101 name: String,
1102 /// Role line, e.g. `Chief Executive Officer · Director`.
1103 role: String,
1104 /// Sort key only: officers ahead of directors, chiefs first. `serde(skip)`
1105 /// keeps it out of the template context.
1106 #[serde(skip)]
1107 rank: u8,
1108}
1109
1110/// One 8-K item-5.02 leadership-change event, shaped for the page.
1111#[derive(Serialize)]
1112struct ChangeView {
1113 filed_at: String,
1114 url: String,
1115}
1116
1117/// Everything the symbol page's Leadership section needs.
1118#[derive(Serialize)]
1119struct LeadershipView {
1120 /// Whether the SEC leadership sweep has reached this stock yet — picks the
1121 /// "not synced yet" pending note apart from a genuine empty roster.
1122 synced: bool,
1123 roster: Vec<LeaderView>,
1124 /// Recent officer/director changes, newest first.
1125 changes: Vec<ChangeView>,
1126}
1127
1128/// Title-case a name filed in SEC's upper-case form: `COOK TIMOTHY D` ->
1129/// `Cook Timothy D`. The first letter of each word, and of each part after an
1130/// apostrophe or hyphen, is capitalized — so `O'BRIEN` reads `O'Brien`. The
1131/// order is left as filed (last name first); reordering it is unreliable for
1132/// compound surnames and generational suffixes.
1133fn title_case(s: &str) -> String {
1134 let mut out = String::with_capacity(s.len());
1135 let mut cap_next = true;
1136 for ch in s.chars() {
1137 if ch.is_whitespace() || ch == '\'' || ch == '-' {
1138 out.push(ch);
1139 cap_next = true;
1140 } else if cap_next {
1141 out.extend(ch.to_uppercase());
1142 cap_next = false;
1143 } else {
1144 out.extend(ch.to_lowercase());
1145 }
1146 }
1147 out
1148}
1149
1150/// Sort rank for the roster: officers ahead of directors, with the chief
1151/// executive / financial / operating officers ahead of the other officers.
1152/// Both the spelled-out titles and the abbreviations are matched, since filers
1153/// use either (`Chief Executive Officer` or `CEO and Chairman`).
1154fn role_rank(is_director: bool, is_officer: bool, title: Option<&str>) -> u8 {
1155 let t = title.unwrap_or("").to_lowercase();
1156 let has = |needles: &[&str]| needles.iter().any(|n| t.contains(n));
1157 if has(&["chief executive", "ceo"]) {
1158 0
1159 } else if has(&["chief financial", "cfo"]) {
1160 1
1161 } else if has(&["chief operating", "coo"]) {
1162 2
1163 } else if is_officer {
1164 3
1165 } else if is_director {
1166 4
1167 } else {
1168 5
1169 }
1170}
1171
1172/// The role line for a roster row: the officer title (when the filer gave one)
1173/// and `Director`, joined — e.g. `Chief Financial Officer · Director`.
1174fn role_text(is_director: bool, is_officer: bool, title: Option<&str>) -> String {
1175 let mut parts: Vec<String> = Vec::new();
1176 match title.map(str::trim).filter(|t| !t.is_empty()) {
1177 Some(t) => parts.push(t.to_string()),
1178 None if is_officer => parts.push("Officer".to_string()),
1179 None => {}
1180 }
1181 if is_director {
1182 parts.push("Director".to_string());
1183 }
1184 if parts.is_empty() {
1185 parts.push("Insider".to_string());
1186 }
1187 parts.join(" \u{00b7} ")
1188}
1189
1190/// Load the Leadership section for a stock: the current officer/board roster
1191/// and the recent 8-K item-5.02 change events. The roster is filtered to
1192/// insiders seen filing within the recency window, so people who left long ago
1193/// drop off (ownership filings carry no explicit departure signal).
1194async fn build_leadership(pool: &sqlx::SqlitePool, ticker: &str, synced: bool) -> LeadershipView {
1195 // ~18 months: long enough that an annually-filing director still shows,
1196 // short enough that a departed insider ages out.
1197 let cutoff = (chrono::Utc::now().date_naive() - chrono::Duration::days(550)).to_string();
1198 let rows: Vec<LeadershipRow> = sqlx::query_as(
1199 "SELECT name, is_director, is_officer, officer_title FROM leadership \
1200 WHERE ticker = ? AND last_seen >= ?",
1201 )
1202 .bind(ticker)
1203 .bind(&cutoff)
1204 .fetch_all(pool)
1205 .await
1206 .unwrap_or_default();
1207
1208 let mut roster: Vec<LeaderView> = rows
1209 .into_iter()
1210 .map(|r| {
1211 let (is_dir, is_off) = (r.is_director != 0, r.is_officer != 0);
1212 LeaderView {
1213 rank: role_rank(is_dir, is_off, r.officer_title.as_deref()),
1214 role: role_text(is_dir, is_off, r.officer_title.as_deref()),
1215 name: title_case(&r.name),
1216 }
1217 })
1218 .collect();
1219 roster.sort_by(|a, b| a.rank.cmp(&b.rank).then_with(|| a.name.cmp(&b.name)));
1220
1221 let changes: Vec<ChangeView> = sqlx::query_as::<_, (String, String)>(
1222 "SELECT filed_at, url FROM filings \
1223 WHERE ticker = ? AND form LIKE '8-K%' AND items LIKE '%5.02%' \
1224 ORDER BY filed_at DESC, accession DESC LIMIT 8",
1225 )
1226 .bind(ticker)
1227 .fetch_all(pool)
1228 .await
1229 .unwrap_or_default()
1230 .into_iter()
1231 .map(|(filed_at, url)| ChangeView { filed_at, url })
1232 .collect();
1233
1234 LeadershipView {
1235 synced,
1236 roster,
1237 changes,
1238 }
1239}
1240
1241// ── earnings dates (Phase 25) ─────────────────────────────────────────────
1242
1243/// One past earnings date shaped for the page.
1244#[derive(Serialize)]
1245struct PastEarningsRow {
1246 /// `YYYY-MM-DD`; the template's `shortdate` filter formats it.
1247 date: String,
1248 /// Days from today; positive for past dates.
1249 days_ago: i64,
1250}
1251
1252/// Everything the symbol-page Earnings section needs. Stocks only — every
1253/// caller gates the build on `kind == "stock"`.
1254#[derive(Serialize)]
1255struct EarningsView {
1256 /// Most recent past earnings date (`YYYY-MM-DD`), with a days-ago figure.
1257 most_recent: Option<PastEarningsRow>,
1258 /// Next-expected earnings date (`YYYY-MM-DD`) and days-from-today.
1259 next_date: Option<String>,
1260 next_days: Option<i64>,
1261 /// Where the next date came from: `yahoo` (authoritative), `estimate`
1262 /// (cadence projection), or `unknown` (Yahoo has no date and we cannot
1263 /// estimate one — too few priors).
1264 next_source: &'static str,
1265 /// The last few past earnings dates, newest first. Capped to 4 (one
1266 /// trailing year of a quarterly cadence) per the design pass.
1267 past: Vec<PastEarningsRow>,
1268 /// All past earnings dates surfaced to the chart as ink pips above
1269 /// each matching candle. Kept here so the route's history API can
1270 /// echo them into the chart payload.
1271 chart_dates: Vec<String>,
1272 /// When this stock's earnings-calendar sync last ran, for the section
1273 /// caption. NULL when Yahoo has never been hit for this stock; the page
1274 /// then shows the past dates and the cadence estimate without the "as of"
1275 /// line so it does not lie about a sync that did not happen.
1276 earnings_synced_at: Option<i64>,
1277}
1278
1279/// How many past earnings dates to list on the page. Four covers one trailing
1280/// year of a quarterly cadence; the chart pips show all of them up to the
1281/// chart's visible range.
1282const EARNINGS_PAST_LIMIT: usize = 4;
1283
1284/// Load past earnings dates from `filings.items LIKE '%2.02%'` (Phase 14
1285/// stored 8-K item codes). Newest first; capped to a generous window so a
1286/// company that moved its reporting day still produces a clean median.
1287async fn load_past_earnings(pool: &sqlx::SqlitePool, ticker: &str) -> Vec<String> {
1288 sqlx::query_scalar(
1289 "SELECT filed_at FROM filings \
1290 WHERE ticker = ? AND form LIKE '8-K%' AND items LIKE '%2.02%' \
1291 ORDER BY filed_at DESC, accession DESC LIMIT 16",
1292 )
1293 .bind(ticker)
1294 .fetch_all(pool)
1295 .await
1296 .unwrap_or_default()
1297}
1298
1299/// Build the Earnings section for a stock. Returns `None` when SEC has not
1300/// synced yet (no past dates to anchor the section) and Yahoo also carries
1301/// no next date — the section is hidden cleanly in that case.
1302async fn build_earnings(
1303 pool: &sqlx::SqlitePool,
1304 ticker: &str,
1305 next_earnings_at: Option<i64>,
1306 earnings_synced_at: Option<i64>,
1307) -> Option<EarningsView> {
1308 let past_dates = load_past_earnings(pool, ticker).await;
1309 if past_dates.is_empty() && next_earnings_at.is_none() {
1310 return None;
1311 }
1312 let today = chrono::Utc::now().date_naive();
1313 let days_between = |d: &str| -> Option<i64> {
1314 chrono::NaiveDate::parse_from_str(d, "%Y-%m-%d")
1315 .ok()
1316 .map(|nd| (nd - today).num_days())
1317 };
1318
1319 let most_recent = past_dates.first().and_then(|d| {
1320 days_between(d).map(|gap| PastEarningsRow {
1321 date: d.clone(),
1322 days_ago: -gap, // gap is negative for past dates; flip to days-ago.
1323 })
1324 });
1325
1326 // Resolve the next date: Yahoo primary, cadence-estimate fallback.
1327 let (next_date, next_source) = match next_earnings_at {
1328 Some(ts) => {
1329 let date = chrono::DateTime::from_timestamp_millis(ts)
1330 .map(|dt| dt.naive_utc().date().format("%Y-%m-%d").to_string());
1331 (date, "yahoo")
1332 }
1333 None => {
1334 let date_refs: Vec<&str> = past_dates.iter().map(String::as_str).collect();
1335 match compute::next_earnings_estimate(&date_refs) {
1336 Some(d) => (Some(d), "estimate"),
1337 None => (None, "unknown"),
1338 }
1339 }
1340 };
1341 let next_days = next_date.as_deref().and_then(days_between);
1342
1343 let past: Vec<PastEarningsRow> = past_dates
1344 .iter()
1345 .take(EARNINGS_PAST_LIMIT)
1346 .filter_map(|d| {
1347 days_between(d).map(|gap| PastEarningsRow {
1348 date: d.clone(),
1349 days_ago: -gap,
1350 })
1351 })
1352 .collect();
1353
1354 Some(EarningsView {
1355 most_recent,
1356 next_date,
1357 next_days,
1358 next_source,
1359 past,
1360 chart_dates: past_dates,
1361 earnings_synced_at,
1362 })
1363}
1364
1365// ── per-ticker anomaly feed (Phase 16) ────────────────────────────────────
1366
1367/// One row in the anomaly feed, as shaped for the template. Wraps
1368/// `compute::AnomalyEvent` with no extra fields — re-exposed so the template
1369/// can iterate a single concrete type regardless of which compute helper
1370/// (or the leadership-filings SELECT below) produced the row.
1371type AnomalyRow = compute::AnomalyEvent;
1372
1373#[derive(Serialize)]
1374struct AnomalyView {
1375 events: Vec<AnomalyRow>,
1376}
1377
1378/// Display cap on the merged feed. Severity-rank-then-newest the four
1379/// streams together, then trim to this many before rendering.
1380const ANOMALY_MAX_EVENTS: usize = 20;
1381/// How far back the feed reaches.
1382const ANOMALY_WINDOW_DAYS: i64 = 365;
1383
1384/// Build the symbol-page anomaly feed: large price moves and new 6-month
1385/// lows for every symbol with a daily history; YoY fundamentals jumps and
1386/// 8-K item-5.02 leadership changes additionally for stocks. The feed is
1387/// trimmed to the past year and capped at [`ANOMALY_MAX_EVENTS`]. Returns
1388/// `None` when no events qualify, so the template hides the section.
1389async fn build_anomalies(
1390 pool: &sqlx::SqlitePool,
1391 ticker: &str,
1392 kind: &str,
1393 bars_newest_first: &[(String, f64, f64, f64, f64, i64)],
1394 facts: &[models::FundFact],
1395) -> Option<AnomalyView> {
1396 let today = chrono::Utc::now().date_naive();
1397 let cutoff_date = today - chrono::Duration::days(ANOMALY_WINDOW_DAYS);
1398 let cutoff = cutoff_date.format("%Y-%m-%d").to_string();
1399
1400 // Price + drawdown events want oldest-first closes paired with dates.
1401 let oldest_first: Vec<(String, f64)> = bars_newest_first
1402 .iter()
1403 .rev()
1404 .map(|(d, _, _, _, c, _)| (d.clone(), *c))
1405 .collect();
1406 let closes: Vec<f64> = oldest_first.iter().map(|(_, c)| *c).collect();
1407 let dates_refs: Vec<&str> = oldest_first.iter().map(|(d, _)| d.as_str()).collect();
1408
1409 let mut events: Vec<AnomalyRow> = Vec::new();
1410 events.extend(compute::price_anomalies(&closes, &dates_refs));
1411 events.extend(compute::drawdown_anomalies(&closes, &dates_refs));
1412
1413 // Fundamentals events and leadership events are stocks-only.
1414 if kind == "stock" {
1415 events.extend(models::fundamentals_anomalies(facts));
1416 let lead_rows: Vec<(String, String)> = sqlx::query_as(
1417 "SELECT filed_at, url FROM filings \
1418 WHERE ticker = ? AND form LIKE '8-K%' AND items LIKE '%5.02%' \
1419 AND filed_at >= ? \
1420 ORDER BY filed_at DESC, accession DESC",
1421 )
1422 .bind(ticker)
1423 .bind(&cutoff)
1424 .fetch_all(pool)
1425 .await
1426 .unwrap_or_default();
1427 for (filed_at, url) in lead_rows {
1428 events.push(AnomalyRow {
1429 date: filed_at,
1430 glyph: "leader",
1431 polarity: "neutral",
1432 headline: "Officer or director change reported in an 8-K".to_string(),
1433 url: Some(url),
1434 // Hand-picked: above a typical 5-8% one-day move so a leadership
1435 // change is not crowded off the list, below a major drawdown.
1436 severity: 7.5,
1437 });
1438 }
1439 }
1440
1441 // Trim to the past year window.
1442 events.retain(|e| e.date.as_str() >= cutoff.as_str());
1443 if events.is_empty() {
1444 return None;
1445 }
1446 // Newest first; ties broken by severity so the bigger event of the same
1447 // day reads first. Then cap.
1448 events.sort_by(|a, b| {
1449 b.date
1450 .cmp(&a.date)
1451 .then_with(|| b.severity.partial_cmp(&a.severity).unwrap_or(std::cmp::Ordering::Equal))
1452 });
1453 events.truncate(ANOMALY_MAX_EVENTS);
1454
1455 Some(AnomalyView { events })
1456}
1457
1458async fn symbol_page(Path(ticker): Path<String>, State(state): State<AppState>) -> Response {
1459 let ticker = ticker.to_uppercase();
1460
1461 let symbol = sqlx::query_as::<_, SymbolRow>("SELECT * FROM symbols WHERE ticker = ?")
1462 .bind(&ticker)
1463 .fetch_optional(&state.pool)
1464 .await
1465 .ok()
1466 .flatten();
1467 let Some(symbol) = symbol else {
1468 return not_found(&state);
1469 };
1470
1471 // The latest stored live quote, if the symbol has ever been quoted. The
1472 // header prefers it over the last daily close.
1473 let quote = sqlx::query_as::<_, QuoteRow>(
1474 "SELECT price, prev_close, fetched_at FROM quotes WHERE ticker = ?",
1475 )
1476 .bind(&ticker)
1477 .fetch_optional(&state.pool)
1478 .await
1479 .ok()
1480 .flatten()
1481 .map(|q| {
1482 let change = q.prev_close.map(|p| compute::change(q.price, p));
1483 HeaderQuote {
1484 price: q.price,
1485 change_abs: change.map(|c| c.abs),
1486 change_pct: change.map(|c| c.pct),
1487 state_label: quote_state_label(q.fetched_at).to_string(),
1488 }
1489 });
1490
1491 // Most recent ~1.5 years of daily bars, newest first.
1492 let bars: Vec<(String, f64, f64, f64, f64, i64)> = sqlx::query_as(
1493 "SELECT d, open, high, low, close, volume FROM daily_prices \
1494 WHERE ticker = ? ORDER BY d DESC LIMIT 400",
1495 )
1496 .bind(&ticker)
1497 .fetch_all(&state.pool)
1498 .await
1499 .unwrap_or_default();
1500
1501 let stats = bars.first().map(|latest| {
1502 let (date, open, high, low, close, volume) = latest.clone();
1503 let prev_close = bars.get(1).map(|b| b.4);
1504 let change = prev_close.map(|p| compute::change(close, p));
1505 // 52-week range from the most recent ~252 trading days.
1506 let window = &bars[..bars.len().min(252)];
1507 let high_52w = window.iter().map(|b| b.2).fold(f64::NEG_INFINITY, f64::max);
1508 let low_52w = window.iter().map(|b| b.3).fold(f64::INFINITY, f64::min);
1509 // Average daily volume over the recent ~3-month window (65 sessions).
1510 let vol_window = &bars[..bars.len().min(65)];
1511 let avg_volume =
1512 vol_window.iter().map(|b| b.5).sum::<i64>() / vol_window.len().max(1) as i64;
1513 let vol_ratio = (avg_volume > 0).then(|| volume as f64 / avg_volume as f64);
1514 Stats {
1515 date,
1516 open,
1517 high,
1518 low,
1519 close,
1520 volume,
1521 prev_close,
1522 change_abs: change.map(|c| c.abs),
1523 change_pct: change.map(|c| c.pct),
1524 open_change_pct: prev_close.map(|p| compute::change(open, p).pct),
1525 high_52w,
1526 low_52w,
1527 avg_volume,
1528 day_open_pos: compute::pos(open, low, high),
1529 day_close_pos: compute::pos(close, low, high),
1530 yr_close_pos: compute::pos(close, low_52w, high_52w),
1531 yr_prev_pos: prev_close.map(|p| compute::pos(p, low_52w, high_52w)),
1532 // Cap the bar at 2x the average so an outlier session stays on-rail.
1533 vol_fill_pct: vol_ratio.map_or(0.0, |r| (r / 2.0 * 100.0).clamp(0.0, 100.0)),
1534 vol_ratio,
1535 }
1536 });
1537
1538 // Fundamentals are stocks-only; an ETF gets a fund profile instead; an
1539 // index gets neither. Filings cover both stocks and ETFs.
1540 let is_stock = symbol.kind == "stock";
1541 let is_etf = symbol.kind == "etf";
1542 // Ratios price off the live quote, falling back to the last daily close.
1543 let price = quote
1544 .as_ref()
1545 .map(|q| q.price)
1546 .or_else(|| stats.as_ref().map(|s| s.close));
1547
1548 // Plain-language read of the chart's indicators (RSI verdict + price vs each
1549 // moving average), shown beneath the chart. Built from the daily closes
1550 // (oldest first) against the current price; `None` without enough history.
1551 let indicators = price.and_then(|p| {
1552 // `bars` is newest-first; the indicator maths want oldest-first.
1553 let highs: Vec<f64> = bars.iter().rev().map(|b| b.2).collect();
1554 let lows: Vec<f64> = bars.iter().rev().map(|b| b.3).collect();
1555 let closes: Vec<f64> = bars.iter().rev().map(|b| b.4).collect();
1556 build_indicator_read(&highs, &lows, &closes, p, symbol.kind != "index")
1557 });
1558
1559 // Stock fundamentals are loaded once and shared by the ratio cards
1560 // (`build_fundamentals`) and the anomaly feed's YoY detector
1561 // (`build_anomalies` via `models::fundamentals_anomalies`).
1562 let facts: Vec<models::FundFact> = if is_stock {
1563 sqlx::query_as(
1564 "SELECT metric, period, fiscal_year, fiscal_qtr, value, period_end \
1565 FROM fundamentals WHERE ticker = ?",
1566 )
1567 .bind(&ticker)
1568 .fetch_all(&state.pool)
1569 .await
1570 .unwrap_or_default()
1571 } else {
1572 Vec::new()
1573 };
1574 let fundamentals = if is_stock {
1575 build_fundamentals(&facts, price)
1576 } else {
1577 None
1578 };
1579
1580 // The overall strong / fair / weak standing (Phase 20): the ratios above
1581 // rolled up, with the daily-close trajectory folded into its score. Shown
1582 // as a single badge over the ratio cards. `bars` is newest-first, so it is
1583 // reversed into an oldest-first close series.
1584 let closes_oldest_first: Vec<f64> = if is_stock {
1585 bars.iter().rev().map(|b| b.4).collect()
1586 } else {
1587 Vec::new()
1588 };
1589 let standing = fundamentals
1590 .as_ref()
1591 .and_then(|f| compute::standing(&f.ratios, &closes_oldest_first));
1592
1593 // The stock health read (Phase 17): the ratios + trajectory of the
1594 // standing above, plus a leadership-stability signal read off the recent
1595 // 8-K item-5.02 change count from Phase 14. `None` until the leadership
1596 // sweep has reached this stock; the composite then drops that component
1597 // cleanly instead of penalising an unsynced stock. Stocks only.
1598 let leadership_changes_recent: Option<usize> = if is_stock
1599 && symbol.leadership_synced_at.is_some()
1600 {
1601 let cutoff = (chrono::Utc::now().date_naive()
1602 - chrono::Duration::days(compute::LEADERSHIP_STABILITY_DAYS))
1603 .to_string();
1604 sqlx::query_scalar::<_, i64>(
1605 "SELECT COUNT(*) FROM filings \
1606 WHERE ticker = ? AND form LIKE '8-K%' AND items LIKE '%5.02%' \
1607 AND filed_at >= ?",
1608 )
1609 .bind(&ticker)
1610 .bind(&cutoff)
1611 .fetch_one(&state.pool)
1612 .await
1613 .ok()
1614 .map(|n| n.max(0) as usize)
1615 } else {
1616 None
1617 };
1618 let health = fundamentals.as_ref().and_then(|f| {
1619 compute::health_read(&f.ratios, &closes_oldest_first, leadership_changes_recent)
1620 });
1621
1622 let filings: Vec<FilingView> = if is_stock || is_etf {
1623 sqlx::query_as::<_, FilingRow>(
1624 "SELECT form, filed_at, period_of_report, url FROM filings \
1625 WHERE ticker = ? ORDER BY filed_at DESC, accession DESC LIMIT 18",
1626 )
1627 .bind(&ticker)
1628 .fetch_all(&state.pool)
1629 .await
1630 .unwrap_or_default()
1631 .into_iter()
1632 .map(|r| FilingView {
1633 title: filing_title(&r.form),
1634 form: r.form,
1635 filed_at: r.filed_at,
1636 period_of_report: r.period_of_report,
1637 url: r.url,
1638 })
1639 .collect()
1640 } else {
1641 Vec::new()
1642 };
1643
1644 // Raw ETF figures captured as the fund / metadata blocks build them, then
1645 // rolled into the Phase 4 quality read below. Kept as scalars so the read
1646 // can be computed once both SEC (profile/holdings) and Yahoo (metadata)
1647 // sources are loaded, without re-querying.
1648 let mut etf_net_assets: Option<f64> = None;
1649 let mut etf_top10_pct: Option<f64> = None;
1650 let mut etf_expense_ratio: Option<f64> = None;
1651 let mut etf_nav: Option<f64> = None;
1652 let mut etf_nav_synced_at: Option<i64> = None;
1653
1654 // The ETF fund profile, when the SEC sweep has reached this symbol.
1655 let fund = if is_etf {
1656 let profile = sqlx::query_as::<_, FundProfileRow>(
1657 "SELECT kind, net_assets, holdings_count, report_date, \
1658 asset_mix, sector_mix, geography_mix \
1659 FROM fund_profiles WHERE ticker = ?",
1660 )
1661 .bind(&ticker)
1662 .fetch_optional(&state.pool)
1663 .await
1664 .ok()
1665 .flatten();
1666 match profile {
1667 Some(profile) => {
1668 let holdings = sqlx::query_as::<_, HoldingRow>(
1669 "SELECT rank, name, pct, value_usd FROM fund_holdings \
1670 WHERE ticker = ? ORDER BY rank",
1671 )
1672 .bind(&ticker)
1673 .fetch_all(&state.pool)
1674 .await
1675 .unwrap_or_default();
1676 etf_net_assets = profile.net_assets;
1677 // Top-10 concentration for the diversification factor: the
1678 // summed weight of the ten largest holdings (rows are rank-
1679 // ordered, `pct` already in percent units). `None` when the
1680 // fund reported no holdings (a commodity trust), so that factor
1681 // drops out of the blend rather than reading as zero.
1682 let top10: f64 = holdings.iter().take(10).filter_map(|h| h.pct).sum();
1683 etf_top10_pct = (top10 > 0.0).then_some(top10);
1684 Some(build_fund(profile, holdings))
1685 }
1686 None => None,
1687 }
1688 } else {
1689 None
1690 };
1691
1692 // ETF fund metadata + trailing returns (Phase 28). Both keyed by the
1693 // same `is_etf` gate; the fund_metadata row exists once the new Yahoo
1694 // job has swept this symbol. An unswept ETF shows the section's
1695 // "pending" note in the template.
1696 let fund_meta = if is_etf {
1697 let row = sqlx::query_as::<_, FundMetadataRow>(
1698 "SELECT expense_ratio, yield_pct, trailing_yield_pct, nav_price, \
1699 inception_date, category, fund_family, strategy_summary, \
1700 nav_synced_at \
1701 FROM fund_metadata WHERE ticker = ?",
1702 )
1703 .bind(&ticker)
1704 .fetch_optional(&state.pool)
1705 .await
1706 .ok()
1707 .flatten();
1708 match row {
1709 Some(row) => {
1710 etf_expense_ratio = row.expense_ratio;
1711 etf_nav = row.nav_price;
1712 etf_nav_synced_at = row.nav_synced_at;
1713 Some(build_fund_meta(row, price))
1714 }
1715 None => None,
1716 }
1717 } else {
1718 None
1719 };
1720
1721 // Phase 4 — ETF quality read: cost-weighted blend of cost, tracking (price
1722 // vs NAV premium), diversification (top-10 concentration), and size (AUM).
1723 // Mirrors the stock health donut. `None` until ≥2 factors grade, so a fund
1724 // the sweeps have barely reached gets no badge rather than a hollow one.
1725 let etf_quality = if is_etf {
1726 // Only read a price-vs-NAV premium (the tracking factor) against a
1727 // *fresh* NAV: NAV is struck daily, so a stale one makes the premium
1728 // meaningless. We let the factor drop out rather than assert a bogus
1729 // tracking verdict. NAV is re-fetched on demand when an ETF page is
1730 // viewed and stale; when it is behind (fresh deploy, guard tripped),
1731 // tracking simply reads "—". The freshness window is shared with the
1732 // "About this fund" premium line (see NAV_FRESH_MS) so they agree.
1733 let nav_fresh =
1734 etf_nav_synced_at.is_some_and(|t| crate::db::now_ms() - t <= NAV_FRESH_MS);
1735 let premium_pct = if nav_fresh {
1736 price.and_then(|p| compute::premium_discount_pct(p, etf_nav))
1737 } else {
1738 None
1739 };
1740 compute::etf_quality(etf_expense_ratio, premium_pct, etf_top10_pct, etf_net_assets)
1741 } else {
1742 None
1743 };
1744 // Trailing returns reach back as far as the fund's daily history goes
1745 // (since inception, ten years, ...), so they pull the *full* series for
1746 // this symbol rather than the 400-bar window the chart's key stats use.
1747 // ETFs only.
1748 let returns = if is_etf {
1749 let full: Vec<(String, f64)> = sqlx::query_as(
1750 "SELECT d, close FROM daily_prices WHERE ticker = ? ORDER BY d ASC",
1751 )
1752 .bind(&ticker)
1753 .fetch_all(&state.pool)
1754 .await
1755 .unwrap_or_default();
1756 if full.len() >= 2 {
1757 let dated: Vec<compute::DatedClose<'_>> = full
1758 .iter()
1759 .map(|(d, c)| compute::DatedClose {
1760 date: d,
1761 close: *c,
1762 })
1763 .collect();
1764 let today = chrono::Utc::now().date_naive().format("%Y-%m-%d").to_string();
1765 Some(build_returns(&compute::trailing_returns(&dated, &today)))
1766 } else {
1767 None
1768 }
1769 } else {
1770 None
1771 };
1772
1773 // The leadership roster + change feed (Phase 14): stocks only, like the
1774 // fundamentals above.
1775 let leadership = if is_stock {
1776 Some(build_leadership(&state.pool, &ticker, symbol.leadership_synced_at.is_some()).await)
1777 } else {
1778 None
1779 };
1780
1781 // Dividend / distribution payouts (Phase 26 + Phase 28): now covers
1782 // stocks AND ETFs. Indexes and futures have no concept and get no
1783 // section; an unswept symbol shows a pending note in place; a swept one
1784 // with no payouts in the past five years hides the section.
1785 let dividends = if is_stock || is_etf {
1786 build_dividends(&state.pool, &ticker, symbol.dividends_synced_at.is_some()).await
1787 } else {
1788 None
1789 };
1790
1791 // Per-ticker anomaly feed (Phase 16). All instruments get price-based
1792 // events (large daily moves, new 6-month lows); stocks additionally get
1793 // YoY fundamentals jumps and 8-K item-5.02 leadership changes. Returns
1794 // `None` when the symbol has no qualifying events in the past year so
1795 // the template hides the section.
1796 let anomalies = build_anomalies(&state.pool, &ticker, &symbol.kind, &bars, &facts).await;
1797
1798 // Earnings dates (Phase 25). Stocks only; the past dates ride for free
1799 // off the existing 8-K item-2.02 filings (Phase 14 stored the `items`
1800 // column), the next date is either Yahoo's `calendarEvents` or a cadence
1801 // estimate from those past dates. The chart pips also read off the past
1802 // dates carried in `earnings.chart_dates`.
1803 let earnings = if is_stock {
1804 build_earnings(
1805 &state.pool,
1806 &ticker,
1807 symbol.next_earnings_at,
1808 symbol.earnings_synced_at,
1809 )
1810 .await
1811 } else {
1812 None
1813 };
1814
1815 // Dormant / delisted signal: the most recent daily bar is well in the past.
1816 // Yahoo serves a frozen chart for a dead ticker, so without this the page
1817 // would look live. `None` for a normally-trading symbol.
1818 let stale_data = symbol.history_last_date.as_deref().and_then(|d| {
1819 let last = chrono::NaiveDate::parse_from_str(d, "%Y-%m-%d").ok()?;
1820 let days = (chrono::Utc::now().date_naive() - last).num_days();
1821 (days > STALE_DATA_DAYS).then(|| StaleData {
1822 last_date: d.to_string(),
1823 days,
1824 })
1825 });
1826
1827 let extra = minijinja::context! {
1828 title => ticker,
1829 symbol => symbol,
1830 stale_data => stale_data,
1831 stats => stats,
1832 indicators => indicators,
1833 quote => quote,
1834 fundamentals => fundamentals,
1835 standing => standing,
1836 health => health,
1837 fund => fund,
1838 fund_meta => fund_meta,
1839 etf_quality => etf_quality,
1840 returns => returns,
1841 leadership => leadership,
1842 dividends => dividends,
1843 anomalies => anomalies,
1844 earnings => earnings,
1845 filings => filings,
1846 };
1847 render(&state, "pages/symbol.html", &format!("/s/{ticker}"), extra)
1848}
1849
1850#[derive(Deserialize)]
1851struct HistoryQuery {
1852 range: Option<String>,
1853}
1854
1855/// A bar's time on the chart axis. Daily bars are calendar dates
1856/// (`YYYY-MM-DD`); intraday bars (the 1D / 1W ranges) are UNIX seconds, which
1857/// is the other form lightweight-charts accepts for an intraday time scale.
1858/// `#[serde(untagged)]` so each variant serialises as a bare string or number
1859/// — no tag wrapper the chart would have to unpick.
1860#[derive(Serialize)]
1861#[serde(untagged)]
1862enum BarTime {
1863 Date(String),
1864 Unix(i64),
1865}
1866
1867/// One OHLCV point shaped for lightweight-charts.
1868#[derive(Serialize)]
1869struct Candle {
1870 time: BarTime,
1871 open: f64,
1872 high: f64,
1873 low: f64,
1874 close: f64,
1875 volume: i64,
1876}
1877
1878/// One point of a derived overlay/indicator series, shaped for
1879/// lightweight-charts (`time` is `YYYY-MM-DD`). Sparse: bars with no value
1880/// yet (an average's warm-up period) are simply omitted.
1881#[derive(Serialize)]
1882struct LinePoint {
1883 time: String,
1884 value: f64,
1885}
1886
1887/// One earnings-date marker for the chart. `YYYY-MM-DD` (matches the
1888/// candle `time` field), so the client can index it against the candle
1889/// series directly.
1890#[derive(Serialize)]
1891struct EarningsMarker {
1892 time: String,
1893}
1894
1895/// One Supertrend point for the chart: the band value plus whether the trend is
1896/// up, so the client can split the single line into a green (uptrend) and a red
1897/// (downtrend) series with a clean break at each flip.
1898#[derive(Serialize)]
1899struct SuperTrendPoint {
1900 time: String,
1901 value: f64,
1902 up: bool,
1903}
1904
1905/// The symbol chart payload (Phase 8 + Phase 28): the candles for the
1906/// selected range plus the indicator overlays, each already trimmed to the
1907/// visible window. Phase 28 adds an optional benchmark series — the
1908/// curated index a fund tracks, normalised to the same start point as the
1909/// fund's first visible close — rendered as a relative-performance line
1910/// only when the symbol has a `symbols.benchmark` configured.
1911#[derive(Serialize)]
1912struct HistoryResponse {
1913 candles: Vec<Candle>,
1914 sma50: Vec<LinePoint>,
1915 sma200: Vec<LinePoint>,
1916 ema21: Vec<LinePoint>,
1917 rsi14: Vec<LinePoint>,
1918 /// Supertrend band (ATR 10 / 3×). Each point carries its trend side so the
1919 /// client draws it green below price in an uptrend, red above in a
1920 /// downtrend. Empty on the intraday ranges (a daily-only overlay).
1921 #[serde(skip_serializing_if = "Vec::is_empty")]
1922 supertrend: Vec<SuperTrendPoint>,
1923 /// Benchmark closes scaled to the same starting price as the visible
1924 /// candles, so the two lines start together and drift apart on relative
1925 /// performance. Empty when no benchmark is configured or no benchmark
1926 /// history overlaps the range.
1927 #[serde(skip_serializing_if = "Vec::is_empty")]
1928 benchmark: Vec<LinePoint>,
1929 /// Curated benchmark ticker label for the chart legend (e.g. `^SPX`).
1930 /// Absent when no benchmark is configured.
1931 #[serde(skip_serializing_if = "Option::is_none")]
1932 benchmark_ticker: Option<String>,
1933 /// Past earnings-date markers for the chart (Phase 25). Each is a
1934 /// `YYYY-MM-DD` matching one of the visible candles; the client draws
1935 /// a small ink dot above each matching bar. Stocks only; empty otherwise.
1936 #[serde(skip_serializing_if = "Vec::is_empty")]
1937 earnings: Vec<EarningsMarker>,
1938 /// The prior daily close (Phase 6). Carried only for the intraday ranges,
1939 /// where the chart draws it as a dashed reference line so the day's move is
1940 /// legible against where the symbol opened the session.
1941 #[serde(skip_serializing_if = "Option::is_none")]
1942 prev_close: Option<f64>,
1943 /// True when `candles` carry intraday (UNIX-seconds) times rather than
1944 /// daily dates (Phase 6). The chart switches its axis/labels accordingly
1945 /// and suppresses the daily-only overlays.
1946 intraday: bool,
1947}
1948
1949/// Earliest `YYYY-MM-DD` to *show* for a range button. `None` means no limit.
1950fn range_cutoff(range: &str) -> Option<String> {
1951 let today = chrono::Utc::now().date_naive();
1952 match range {
1953 "MAX" => None,
1954 "YTD" => Some(format!("{:04}-01-01", today.year())),
1955 "1M" => Some((today - chrono::Duration::days(31)).to_string()),
1956 "3M" => Some((today - chrono::Duration::days(93)).to_string()),
1957 "6M" => Some((today - chrono::Duration::days(186)).to_string()),
1958 "3Y" => Some((today - chrono::Duration::days(1098)).to_string()),
1959 "5Y" => Some((today - chrono::Duration::days(1830)).to_string()),
1960 // 1Y is the default for any unrecognised value.
1961 _ => Some((today - chrono::Duration::days(366)).to_string()),
1962 }
1963}
1964
1965/// Trading days of history the longest indicator (the 200-day average) needs
1966/// before the visible window, expressed as calendar days with comfortable
1967/// slack for weekends and holidays.
1968const INDICATOR_LOOKBACK_DAYS: i64 = 320;
1969
1970async fn history_api(
1971 Path(ticker): Path<String>,
1972 Query(q): Query<HistoryQuery>,
1973 State(state): State<AppState>,
1974) -> Response {
1975 let ticker = ticker.to_uppercase();
1976 let range = q.range.unwrap_or_else(|| "1Y".to_string());
1977
1978 // The 1D / 1W ranges (Phase 6) draw today's real-time 15-minute bars on an
1979 // intraday axis, live-ticked by the quote stream — a different data source
1980 // (`intraday_bars`) and time format from the daily candles, so they take
1981 // their own path and the daily indicator machinery never runs for them.
1982 if range == "1D" || range == "1W" {
1983 return intraday_history(&state.pool, &ticker, &range).await;
1984 }
1985
1986 let display_cutoff = range_cutoff(&range);
1987
1988 // Indicators need history *before* the visible window or their first
1989 // values would be blank — a 200-day average needs 200 prior bars. Fetch a
1990 // fixed lookback before the range cutoff, compute over the whole set, then
1991 // trim every series back to the visible window.
1992 let fetch_cutoff = display_cutoff.as_deref().map(|c| {
1993 chrono::NaiveDate::parse_from_str(c, "%Y-%m-%d")
1994 .map(|d| (d - chrono::Duration::days(INDICATOR_LOOKBACK_DAYS)).to_string())
1995 .unwrap_or_else(|_| c.to_string())
1996 });
1997
1998 // Daily rows as tuples (date, OHLCV). The indicator maths and the benchmark
1999 // overlay key off the date strings, so they are kept in a parallel `dates`
2000 // vec and the `Candle`s (whose `time` is now the `BarTime` enum) are built
2001 // from the same rows at the end.
2002 let rows: Vec<(String, f64, f64, f64, f64, i64)> = match &fetch_cutoff {
2003 Some(cutoff) => {
2004 sqlx::query_as(
2005 "SELECT d, open, high, low, close, volume FROM daily_prices \
2006 WHERE ticker = ? AND d >= ? ORDER BY d ASC",
2007 )
2008 .bind(&ticker)
2009 .bind(cutoff)
2010 .fetch_all(&state.pool)
2011 .await
2012 }
2013 None => {
2014 sqlx::query_as(
2015 "SELECT d, open, high, low, close, volume FROM daily_prices \
2016 WHERE ticker = ? ORDER BY d ASC",
2017 )
2018 .bind(&ticker)
2019 .fetch_all(&state.pool)
2020 .await
2021 }
2022 }
2023 .unwrap_or_default();
2024
2025 let dates: Vec<String> = rows.iter().map(|r| r.0.clone()).collect();
2026 let highs: Vec<f64> = rows.iter().map(|r| r.2).collect();
2027 let lows: Vec<f64> = rows.iter().map(|r| r.3).collect();
2028 let closes: Vec<f64> = rows.iter().map(|r| r.4).collect();
2029
2030 // First bar inside the visible window; everything before it is lookback,
2031 // fetched only so the indicators are correct from the very first shown bar.
2032 let start = match &display_cutoff {
2033 Some(c) => dates
2034 .iter()
2035 .position(|d| d.as_str() >= c.as_str())
2036 .unwrap_or(dates.len()),
2037 None => 0,
2038 };
2039
2040 // Zip a raw indicator series to its bar dates, dropping warm-up `None`s
2041 // and the lookback bars, leaving only points inside the visible window.
2042 let line = |series: Vec<Option<f64>>| -> Vec<LinePoint> {
2043 series
2044 .into_iter()
2045 .enumerate()
2046 .skip(start)
2047 .filter_map(|(i, v)| {
2048 v.map(|value| LinePoint {
2049 time: dates[i].clone(),
2050 value,
2051 })
2052 })
2053 .collect()
2054 };
2055
2056 // Benchmark overlay (Phase 28). Loaded only when the symbol has a
2057 // curated `symbols.benchmark` set, and only when the visible range
2058 // actually has anchor bars to scale against. The benchmark series is
2059 // pinned to the fund's first visible close so the two lines start
2060 // together and drift apart on relative performance.
2061 let benchmark_ticker: Option<String> = sqlx::query_scalar(
2062 "SELECT benchmark FROM symbols WHERE ticker = ?",
2063 )
2064 .bind(&ticker)
2065 .fetch_optional(&state.pool)
2066 .await
2067 .ok()
2068 .flatten()
2069 .flatten();
2070 let benchmark = match (&benchmark_ticker, dates.get(start)) {
2071 (Some(bench), Some(_)) => {
2072 load_benchmark_series(&state.pool, bench, &dates[start..], closes[start]).await
2073 }
2074 _ => Vec::new(),
2075 };
2076
2077 // Earnings-date pips (Phase 25). Stocks only; each pip is dated to an
2078 // 8-K item-2.02 filing date (Phase 14 already stored those in
2079 // `filings.items`). The chart maps each `time` to its matching candle.
2080 let kind: Option<String> = sqlx::query_scalar("SELECT kind FROM symbols WHERE ticker = ?")
2081 .bind(&ticker)
2082 .fetch_optional(&state.pool)
2083 .await
2084 .ok()
2085 .flatten();
2086 let earnings = if kind.as_deref() == Some("stock") {
2087 let dates = load_past_earnings(&state.pool, &ticker).await;
2088 dates
2089 .into_iter()
2090 .map(|time| EarningsMarker { time })
2091 .collect()
2092 } else {
2093 Vec::new()
2094 };
2095
2096 // Supertrend, trimmed to the visible window the same way as the lines but
2097 // keeping each bar's trend side so the client can colour it.
2098 let supertrend: Vec<SuperTrendPoint> =
2099 compute::supertrend(&highs, &lows, &closes, compute::SUPERTREND_PERIOD, compute::SUPERTREND_MULT)
2100 .into_iter()
2101 .enumerate()
2102 .skip(start)
2103 .filter_map(|(i, p)| {
2104 p.map(|p| SuperTrendPoint {
2105 time: dates[i].clone(),
2106 value: p.value,
2107 up: p.up,
2108 })
2109 })
2110 .collect();
2111
2112 let resp = HistoryResponse {
2113 sma50: line(compute::sma(&closes, 50)),
2114 sma200: line(compute::sma(&closes, 200)),
2115 ema21: line(compute::ema(&closes, 21)),
2116 rsi14: line(compute::rsi(&closes, 14)),
2117 supertrend,
2118 candles: rows
2119 .into_iter()
2120 .skip(start)
2121 .map(|(d, open, high, low, close, volume)| Candle {
2122 time: BarTime::Date(d),
2123 open,
2124 high,
2125 low,
2126 close,
2127 volume,
2128 })
2129 .collect(),
2130 benchmark,
2131 benchmark_ticker,
2132 earnings,
2133 prev_close: None,
2134 intraday: false,
2135 };
2136
2137 Json(resp).into_response()
2138}
2139
2140/// Calendar days of intraday history the 1W range shows. `intraday_bars` is
2141/// pruned to a 14-day window (see `INTRADAY_RETENTION_DAYS`), so a week sits
2142/// comfortably inside what is stored.
2143const INTRADAY_WEEK_DAYS: i64 = 7;
2144
2145/// Serve the 1D / 1W intraday ranges (Phase 6) from `intraday_bars`. The bars
2146/// are stored as UTC epoch-*milliseconds*; lightweight-charts wants UNIX
2147/// *seconds* for an intraday axis, so each `ts` is divided by 1000. 1D shows
2148/// the most recent trading day present (so a weekend correctly shows Friday);
2149/// 1W shows a rolling seven days. None of the daily-only overlays apply, so the
2150/// indicator series come back empty and the chart hides their toggles.
2151async fn intraday_history(pool: &sqlx::SqlitePool, ticker: &str, range: &str) -> Response {
2152 use chrono::{TimeZone as _, Utc};
2153 use chrono_tz::America::New_York;
2154
2155 let now_ms = chrono::Utc::now().timestamp_millis();
2156 let cutoff_ms: i64 = if range == "1W" {
2157 now_ms - INTRADAY_WEEK_DAYS * 86_400_000
2158 } else {
2159 // 1D: the New-York midnight that opens the most recent day with bars,
2160 // so the view is exactly that session (and weekends show Friday).
2161 let latest: Option<i64> =
2162 sqlx::query_scalar("SELECT MAX(ts) FROM intraday_bars WHERE ticker = ?")
2163 .bind(ticker)
2164 .fetch_optional(pool)
2165 .await
2166 .ok()
2167 .flatten();
2168 match latest {
2169 Some(ms) => Utc
2170 .timestamp_millis_opt(ms)
2171 .single()
2172 .map(|dt| {
2173 let day = dt.with_timezone(&New_York).date_naive();
2174 New_York
2175 .from_local_datetime(&day.and_hms_opt(0, 0, 0).unwrap())
2176 .single()
2177 .map(|midnight| midnight.timestamp_millis())
2178 .unwrap_or(ms)
2179 })
2180 .unwrap_or(now_ms),
2181 None => now_ms,
2182 }
2183 };
2184
2185 let rows: Vec<(i64, f64, f64, f64, f64, i64)> = sqlx::query_as(
2186 "SELECT ts, open, high, low, close, volume FROM intraday_bars \
2187 WHERE ticker = ? AND ts >= ? ORDER BY ts ASC",
2188 )
2189 .bind(ticker)
2190 .bind(cutoff_ms)
2191 .fetch_all(pool)
2192 .await
2193 .unwrap_or_default();
2194
2195 let candles = rows
2196 .into_iter()
2197 .map(|(ts, open, high, low, close, volume)| Candle {
2198 time: BarTime::Unix(ts / 1000),
2199 open,
2200 high,
2201 low,
2202 close,
2203 volume,
2204 })
2205 .collect();
2206
2207 // The prior daily close anchors the session reference line. During a live
2208 // session today's bar is not yet in `daily_prices`, so the most recent row
2209 // is genuinely the previous close.
2210 let prev_close: Option<f64> =
2211 sqlx::query_scalar("SELECT close FROM daily_prices WHERE ticker = ? ORDER BY d DESC LIMIT 1")
2212 .bind(ticker)
2213 .fetch_optional(pool)
2214 .await
2215 .ok()
2216 .flatten();
2217
2218 let resp = HistoryResponse {
2219 candles,
2220 sma50: Vec::new(),
2221 sma200: Vec::new(),
2222 ema21: Vec::new(),
2223 rsi14: Vec::new(),
2224 supertrend: Vec::new(),
2225 benchmark: Vec::new(),
2226 benchmark_ticker: None,
2227 earnings: Vec::new(),
2228 prev_close,
2229 intraday: true,
2230 };
2231
2232 Json(resp).into_response()
2233}
2234
2235/// Load a benchmark index's daily closes across the same date span as
2236/// `visible_dates` (the fund's visible candle dates), then scale each close so the
2237/// series starts at `fund_anchor` — the fund's first visible close — and
2238/// only the *relative* movement past that point is plotted. An empty
2239/// benchmark history or no overlap returns an empty vec, which the
2240/// `skip_serializing_if` on the response field then drops cleanly.
2241async fn load_benchmark_series(
2242 pool: &sqlx::SqlitePool,
2243 benchmark: &str,
2244 visible_dates: &[String],
2245 fund_anchor: f64,
2246) -> Vec<LinePoint> {
2247 let Some(first) = visible_dates.first() else {
2248 return Vec::new();
2249 };
2250 let last = visible_dates.last().unwrap_or(first);
2251 let rows: Vec<(String, f64)> = sqlx::query_as(
2252 "SELECT d, close FROM daily_prices \
2253 WHERE ticker = ? AND d >= ? AND d <= ? ORDER BY d ASC",
2254 )
2255 .bind(benchmark)
2256 .bind(first)
2257 .bind(last)
2258 .fetch_all(pool)
2259 .await
2260 .unwrap_or_default();
2261 if rows.len() < 2 {
2262 return Vec::new();
2263 }
2264 let bench_anchor = rows[0].1;
2265 if bench_anchor <= 0.0 {
2266 return Vec::new();
2267 }
2268 let scale = fund_anchor / bench_anchor;
2269 rows.into_iter()
2270 .map(|(d, c)| LinePoint {
2271 time: d,
2272 value: c * scale,
2273 })
2274 .collect()
2275}
2276
2277// ── ETF growth-of-$10k chart (Phase 28) ────────────────────────────────────
2278
2279/// Response body for `GET /api/symbols/{ticker}/growth`. Two series scaled
2280/// so the first point of each reads as $10,000, drawn together so a fund's
2281/// since-inception path can be eyeballed against its benchmark's.
2282#[derive(Serialize)]
2283struct GrowthResponse {
2284 /// Fund growth series, oldest first.
2285 fund: Vec<compute::GrowthPoint>,
2286 /// Benchmark growth series across the same date span, anchored
2287 /// separately to $10,000 at its own first bar. Empty when the fund has
2288 /// no curated benchmark or no benchmark history overlaps.
2289 #[serde(skip_serializing_if = "Vec::is_empty")]
2290 benchmark: Vec<compute::GrowthPoint>,
2291 #[serde(skip_serializing_if = "Option::is_none")]
2292 benchmark_ticker: Option<String>,
2293}
2294
2295/// Build the growth-of-$10k series over the longest available daily history
2296/// for `ticker`. ETF symbol page only; on a symbol with no daily history
2297/// (e.g. a future) the series is empty and the panel hides itself.
2298async fn growth_api(Path(ticker): Path<String>, State(state): State<AppState>) -> Response {
2299 let ticker = ticker.to_uppercase();
2300 let rows: Vec<(String, f64)> = sqlx::query_as(
2301 "SELECT d, close FROM daily_prices WHERE ticker = ? ORDER BY d ASC",
2302 )
2303 .bind(&ticker)
2304 .fetch_all(&state.pool)
2305 .await
2306 .unwrap_or_default();
2307 let bars: Vec<compute::DatedClose<'_>> = rows
2308 .iter()
2309 .map(|(d, c)| compute::DatedClose {
2310 date: d.as_str(),
2311 close: *c,
2312 })
2313 .collect();
2314 let fund = compute::growth_of_10k(&bars);
2315
2316 let (benchmark_ticker, benchmark) = match (
2317 sqlx::query_scalar::<_, Option<String>>("SELECT benchmark FROM symbols WHERE ticker = ?")
2318 .bind(&ticker)
2319 .fetch_optional(&state.pool)
2320 .await
2321 .ok()
2322 .flatten()
2323 .flatten(),
2324 fund.first(),
2325 ) {
2326 (Some(bench), Some(first)) => {
2327 // Anchor benchmark to the same first-bar date as the fund, so the
2328 // two lines start together; benchmark history before the fund's
2329 // inception is ignored.
2330 let bench_rows: Vec<(String, f64)> = sqlx::query_as(
2331 "SELECT d, close FROM daily_prices \
2332 WHERE ticker = ? AND d >= ? ORDER BY d ASC",
2333 )
2334 .bind(&bench)
2335 .bind(&first.date)
2336 .fetch_all(&state.pool)
2337 .await
2338 .unwrap_or_default();
2339 let bench_bars: Vec<compute::DatedClose<'_>> = bench_rows
2340 .iter()
2341 .map(|(d, c)| compute::DatedClose {
2342 date: d.as_str(),
2343 close: *c,
2344 })
2345 .collect();
2346 (Some(bench), compute::growth_of_10k(&bench_bars))
2347 }
2348 _ => (None, Vec::new()),
2349 };
2350
2351 Json(GrowthResponse {
2352 fund,
2353 benchmark,
2354 benchmark_ticker,
2355 })
2356 .into_response()
2357}
2358
2359// ── add a symbol to the universe (Phase 9) ─────────────────────────────────
2360
2361/// `POST /api/symbols` request body.
2362#[derive(Deserialize)]
2363struct AddSymbolBody {
2364 ticker: String,
2365}
2366
2367/// `POST /api/symbols` response. `ok` is the success flag the Search page's
2368/// script keys on; `error` carries a human message on a failure.
2369#[derive(Serialize)]
2370struct AddSymbolResponse {
2371 ok: bool,
2372 #[serde(skip_serializing_if = "Option::is_none")]
2373 ticker: Option<String>,
2374 #[serde(skip_serializing_if = "Option::is_none")]
2375 name: Option<String>,
2376 #[serde(skip_serializing_if = "Option::is_none")]
2377 kind: Option<String>,
2378 /// True when this call created the symbol; false when it already existed.
2379 added: bool,
2380 #[serde(skip_serializing_if = "Option::is_none")]
2381 error: Option<String>,
2382}
2383
2384/// Normalise and validate a user-supplied ticker. Accepts uppercase letters,
2385/// digits and `. - ^ =` (covering `BRK.B`, `^SPX`, the Yahoo future `CL=F` and
2386/// the like); rejects the empty string, an over-long one, an unexpected
2387/// character, or one carrying no alphanumeric. Returns the normalised
2388/// (trimmed, uppercased) form.
2389///
2390/// `pub(crate)` so the Search page offers an "Add" affordance for exactly the
2391/// strings this endpoint would accept.
2392pub(crate) fn valid_ticker(raw: &str) -> Option<String> {
2393 let t = raw.trim().to_uppercase();
2394 if t.is_empty() || t.len() > 15 {
2395 return None;
2396 }
2397 let charset_ok = t
2398 .chars()
2399 .all(|c| c.is_ascii_alphanumeric() || matches!(c, '.' | '-' | '^' | '='));
2400 let has_alnum = t.chars().any(|c| c.is_ascii_alphanumeric());
2401 (charset_ok && has_alnum).then_some(t)
2402}
2403
2404/// A failed `POST /api/symbols` response with a status and a human message.
2405fn add_err(status: StatusCode, msg: impl Into<String>) -> Response {
2406 (
2407 status,
2408 Json(AddSymbolResponse {
2409 ok: false,
2410 ticker: None,
2411 name: None,
2412 kind: None,
2413 added: false,
2414 error: Some(msg.into()),
2415 }),
2416 )
2417 .into_response()
2418}
2419
2420/// `POST /api/symbols` — add a symbol to the tracked universe.
2421///
2422/// The Search page calls this when a query names a ticker the universe does
2423/// not hold yet. The ticker is validated against Yahoo — one request that also
2424/// yields its name, kind, exchange and currency — then the symbol row is
2425/// inserted, the quote that same lookup returned is stored, and the symbol's
2426/// full backfill (deep daily history and all SEC data) is pulled synchronously
2427/// before the response, so its page is complete the moment the add returns
2428/// (see `scheduler::backfill_symbol`). Every outbound call
2429/// goes through the shared endpoint guard (see the anti-spam policy).
2430/// The outcome of ensuring a symbol is in the tracked universe.
2431pub(crate) struct EnsureOutcome {
2432 pub ticker: String,
2433 pub name: String,
2434 pub kind: String,
2435 /// True when this call created the symbol; false when it already existed.
2436 pub added: bool,
2437}
2438
2439/// Ensure `ticker` is a tracked symbol, adding it to the universe if missing.
2440///
2441/// Idempotent: an already-tracked symbol returns at once. A new one is validated
2442/// against Yahoo (one guarded lookup that also yields its name / kind / exchange
2443/// / currency), inserted as a user-added (`is_seeded = 0`) row, has the lookup's
2444/// quote + bars stored, and its full backfill (deep history + SEC data) pulled
2445/// synchronously, so its page is complete on return. Errors come back as a
2446/// (status, message) pair the caller surfaces. Shared by `POST /api/symbols`
2447/// (the Search "Add") and the dashboard watchlist add.
2448pub(crate) async fn ensure_symbol(
2449 state: &AppState,
2450 raw_ticker: &str,
2451) -> Result<EnsureOutcome, (StatusCode, String)> {
2452 let Some(ticker) = valid_ticker(raw_ticker) else {
2453 return Err((
2454 StatusCode::BAD_REQUEST,
2455 "That does not look like a ticker symbol.".into(),
2456 ));
2457 };
2458
2459 // Already tracked? Idempotent — report it so the caller can just navigate.
2460 let existing: Option<(String, String)> =
2461 sqlx::query_as("SELECT name, kind FROM symbols WHERE ticker = ?")
2462 .bind(&ticker)
2463 .fetch_optional(&state.pool)
2464 .await
2465 .ok()
2466 .flatten();
2467 if let Some((name, kind)) = existing {
2468 return Ok(EnsureOutcome { ticker, name, kind, added: false });
2469 }
2470
2471 // One guarded Yahoo lookup: validates the symbol and describes it.
2472 let yahoo = YahooProvider::new(http::build_client(&state.config));
2473 let guard = EndpointGuard::with_budget(state.pool.clone(), "yahoo", scheduler::YAHOO_BUDGET);
2474 match guard.acquire().await {
2475 Ok(Permit::Granted) => {}
2476 Ok(Permit::Denied(_)) => {
2477 return Err((
2478 StatusCode::SERVICE_UNAVAILABLE,
2479 "The market data source is busy right now. Try again in a few minutes.".into(),
2480 ));
2481 }
2482 Err(e) => {
2483 tracing::error!("ensure_symbol guard for {ticker}: {e:#}");
2484 return Err((
2485 StatusCode::INTERNAL_SERVER_ERROR,
2486 "Something went wrong. Try again shortly.".into(),
2487 ));
2488 }
2489 }
2490
2491 let (info, data) = match yahoo.lookup(&ticker).await {
2492 Err(e) => {
2493 let _ = guard.record_failure(&e).await;
2494 tracing::warn!("ensure_symbol lookup {ticker}: {e:#}");
2495 return Err((
2496 StatusCode::BAD_GATEWAY,
2497 "Could not reach the market data source. Try again shortly.".into(),
2498 ));
2499 }
2500 Ok(outcome) => {
2501 // The endpoint answered — even an "unknown symbol" is a healthy
2502 // reply, so the guard records a success either way.
2503 let _ = guard.record_success().await;
2504 match outcome {
2505 SymbolLookup::Found { info, data } => (info, data),
2506 SymbolLookup::Unknown => {
2507 return Err((
2508 StatusCode::NOT_FOUND,
2509 format!("No symbol called {ticker} was found."),
2510 ));
2511 }
2512 SymbolLookup::Unsupported(raw_kind) => {
2513 let what = raw_kind.to_lowercase();
2514 return Err((StatusCode::UNPROCESSABLE_ENTITY, format!(
2515 "{ticker} is a {what}. Only stocks, ETFs, indexes, and futures can be added right now."
2516 )));
2517 }
2518 }
2519 }
2520 };
2521
2522 // Insert the symbol. User-added, so `is_seeded = 0`.
2523 let now = now_ms();
2524 let inserted = sqlx::query(
2525 "INSERT INTO symbols (ticker, name, kind, exchange, currency, is_seeded, created_at, updated_at) \
2526 VALUES (?, ?, ?, ?, ?, 0, ?, ?) ON CONFLICT(ticker) DO NOTHING",
2527 )
2528 .bind(&ticker)
2529 .bind(&info.name)
2530 .bind(&info.kind)
2531 .bind(&info.exchange)
2532 .bind(&info.currency)
2533 .bind(now)
2534 .bind(now)
2535 .execute(&state.pool)
2536 .await;
2537 if let Err(e) = inserted {
2538 tracing::error!("ensure_symbol insert {ticker}: {e:#}");
2539 return Err((
2540 StatusCode::INTERNAL_SERVER_ERROR,
2541 "Could not save the symbol. Try again shortly.".into(),
2542 ));
2543 }
2544
2545 // Store the quote (and bars) the lookup already paid for, then pull the full
2546 // backfill before returning, so the symbol's page is complete at once.
2547 if let Err(e) = scheduler::store_quote(&state.pool, &ticker, &data.quote).await {
2548 tracing::warn!("ensure_symbol store_quote {ticker}: {e:#}");
2549 }
2550 if !data.bars.is_empty() {
2551 if let Err(e) = scheduler::store_intraday(&state.pool, &ticker, &data.bars).await {
2552 tracing::warn!("ensure_symbol store_intraday {ticker}: {e:#}");
2553 }
2554 }
2555 scheduler::backfill_symbol(&state.pool, &state.config, &ticker, &info.kind).await;
2556
2557 tracing::info!("ensure_symbol: added {ticker} ({}, {})", info.name, info.kind);
2558 Ok(EnsureOutcome { ticker, name: info.name, kind: info.kind, added: true })
2559}
2560
2561/// `POST /api/symbols` — add a symbol to the tracked universe (the Search "Add").
2562async fn add_symbol(State(state): State<AppState>, Json(body): Json<AddSymbolBody>) -> Response {
2563 match ensure_symbol(&state, &body.ticker).await {
2564 Ok(o) => Json(AddSymbolResponse {
2565 ok: true,
2566 ticker: Some(o.ticker),
2567 name: Some(o.name),
2568 kind: Some(o.kind),
2569 added: o.added,
2570 error: None,
2571 })
2572 .into_response(),
2573 Err((status, msg)) => add_err(status, msg),
2574 }
2575}
2576
2577#[derive(Deserialize)]
2578struct RefreshQuery {
2579 /// `1` = the manual Refresh button: re-pull every source regardless of
2580 /// staleness. Default `0` = a page load: pull the live price always, the
2581 /// slow SEC / metadata sources only when their stored copy is stale.
2582 #[serde(default)]
2583 force: u8,
2584}
2585
2586/// `GET /api/symbols/{ticker}/refresh` — Server-Sent Events driving the symbol
2587/// page's loading bar. Asks the scheduler which steps to run for this symbol
2588/// (kind + staleness + `force`), then runs each in turn, emitting a `step`
2589/// event before and after so the bar advances and names what it is doing. A
2590/// final `done` event tells the page whether a deep (server-rendered) section
2591/// changed — if so it reloads to show it; otherwise the live price was already
2592/// patched in place over the stream and no reload is needed.
2593async fn refresh_stream(
2594 Path(ticker): Path<String>,
2595 Query(q): Query<RefreshQuery>,
2596 State(state): State<AppState>,
2597) -> Response {
2598 let ticker = ticker.to_uppercase();
2599 let force = q.force != 0;
2600
2601 let kind: Option<String> = sqlx::query_scalar("SELECT kind FROM symbols WHERE ticker = ?")
2602 .bind(&ticker)
2603 .fetch_optional(&state.pool)
2604 .await
2605 .ok()
2606 .flatten();
2607 let Some(kind) = kind else {
2608 return not_found(&state);
2609 };
2610
2611 let steps = scheduler::refresh_plan(&state.pool, &state.config, &ticker, &kind, force).await;
2612 let any_deep = steps.iter().any(|s| s.deep);
2613
2614 let body = async_stream::stream! {
2615 let total = steps.len();
2616 // Announce the plan up front so the bar can size itself.
2617 yield sse_json("plan", &format!("{{\"total\":{total}}}"));
2618
2619 for (i, st) in steps.iter().enumerate() {
2620 yield sse_json(
2621 "step",
2622 &format!(
2623 "{{\"i\":{},\"n\":{},\"label\":{},\"state\":\"running\"}}",
2624 i + 1, total, json_str(st.label)
2625 ),
2626 );
2627 let status =
2628 scheduler::refresh_step(&state.pool, &state.config, &state.hub, &ticker, &kind, st.key)
2629 .await;
2630 yield sse_json(
2631 "step",
2632 &format!(
2633 "{{\"i\":{},\"n\":{},\"label\":{},\"state\":{}}}",
2634 i + 1, total, json_str(st.label), json_str(status)
2635 ),
2636 );
2637 }
2638
2639 yield sse_json("done", &format!("{{\"reload\":{}}}", any_deep));
2640 };
2641
2642 Sse::new(body)
2643 .keep_alive(axum::response::sse::KeepAlive::default())
2644 .into_response()
2645}
2646
2647/// Build a named SSE event with a raw JSON `data` payload.
2648fn sse_json(event: &str, data: &str) -> Result<axum::response::sse::Event, std::convert::Infallible> {
2649 Ok(axum::response::sse::Event::default().event(event).data(data))
2650}
2651
2652/// Minimal JSON string escaper for the short, known labels/statuses streamed
2653/// above (no control characters in play; just quote the value safely).
2654fn json_str(s: &str) -> String {
2655 serde_json::to_string(s).unwrap_or_else(|_| "\"\"".to_string())
2656}
2657
2658#[cfg(test)]
2659mod tests {
2660 use super::*;
2661
2662 fn q(period_end: &str, value: f64) -> models::FundFact {
2663 models::FundFact {
2664 metric: "eps_diluted".to_string(),
2665 period: format!("Q-{period_end}"),
2666 fiscal_year: 2024,
2667 fiscal_qtr: Some(1),
2668 value,
2669 period_end: period_end.to_string(),
2670 }
2671 }
2672
2673 #[test]
2674 fn ttm_eps_sums_four_consecutive_quarters() {
2675 let facts = vec![
2676 q("2024-12-31", 1.0),
2677 q("2024-09-30", 0.9),
2678 q("2024-06-30", 0.8),
2679 q("2024-03-31", 0.7),
2680 ];
2681 let ttm = ttm_eps_diluted(&facts).expect("four consecutive quarters → a TTM");
2682 assert!((ttm.0 - 3.4).abs() < 1e-9);
2683 assert_eq!(ttm.1, "2024-12-31"); // labelled through the newest quarter
2684 }
2685
2686 #[test]
2687 fn ttm_eps_rejects_a_gap() {
2688 // A missing quarter (jump from 2024-06-30 back to 2023-09-30) spans more
2689 // than a year, so it must NOT be summed as a trailing twelve months.
2690 let facts = vec![
2691 q("2024-12-31", 1.0),
2692 q("2024-09-30", 0.9),
2693 q("2024-06-30", 0.8),
2694 q("2023-09-30", 0.6),
2695 ];
2696 assert!(ttm_eps_diluted(&facts).is_none(), "a non-consecutive run is not a TTM");
2697 }
2698
2699 #[test]
2700 fn ttm_eps_needs_four_quarters() {
2701 let facts = vec![q("2024-12-31", 1.0), q("2024-09-30", 0.9)];
2702 assert!(ttm_eps_diluted(&facts).is_none());
2703 }
2704}