mtgjson_sdk/queries/
prices.rs

1//! Price queries against the DuckDB `all_prices_today` and `all_prices` parquet views.
2
3use std::collections::HashMap;
4
5use serde_json::Value;
6
7use crate::error::Result;
8use crate::sql_builder::SqlBuilder;
9
10// ---------------------------------------------------------------------------
11// PriceFilter
12// ---------------------------------------------------------------------------
13
14/// Optional filter parameters shared across price query methods.
15#[derive(Debug, Clone, Default)]
16pub struct PriceFilter {
17    pub provider: Option<String>,
18    pub finish: Option<String>,
19    pub price_type: Option<String>,
20}
21
22// ---------------------------------------------------------------------------
23// PriceQuery
24// ---------------------------------------------------------------------------
25
26/// Query interface for MTG card prices backed by the `all_prices_today` DuckDB view.
27pub struct PriceQuery<'a> {
28    conn: &'a crate::connection::Connection,
29}
30
31impl<'a> PriceQuery<'a> {
32    /// Create a new `PriceQuery` bound to the given connection.
33    pub fn new(conn: &'a crate::connection::Connection) -> Self {
34        Self { conn }
35    }
36
37    /// Get the full nested price structure for a card UUID.
38    ///
39    /// Returns a nested object keyed by `source -> provider -> price_type -> finish -> date -> price`.
40    pub fn get(&self, uuid: &str) -> Result<Value> {
41        self.conn.ensure_views(&["all_prices_today"])?;
42
43        let (sql, params) = SqlBuilder::new("all_prices_today")
44            .where_eq("uuid", uuid)
45            .order_by(&["date DESC"])
46            .build();
47
48        let rows = self.conn.execute(&sql, &params)?;
49
50        // Build a nested map: source -> provider -> currency -> price_type -> finish -> {date: price}
51        let mut result: HashMap<String, HashMap<String, HashMap<String, HashMap<String, HashMap<String, HashMap<String, f64>>>>>> =
52            HashMap::new();
53
54        for row in &rows {
55            let source = row.get("source").and_then(|v| v.as_str()).unwrap_or("");
56            let provider = row.get("provider").and_then(|v| v.as_str()).unwrap_or("");
57            let currency = row.get("currency").and_then(|v| v.as_str()).unwrap_or("");
58            let price_type = row.get("price_type").and_then(|v| v.as_str()).unwrap_or("");
59            let finish = row.get("finish").and_then(|v| v.as_str()).unwrap_or("");
60            let date = row.get("date").and_then(|v| v.as_str()).unwrap_or("");
61            let price = row
62                .get("price")
63                .and_then(|v| v.as_f64())
64                .unwrap_or(0.0);
65
66            result
67                .entry(source.to_string())
68                .or_default()
69                .entry(provider.to_string())
70                .or_default()
71                .entry(currency.to_string())
72                .or_default()
73                .entry(price_type.to_string())
74                .or_default()
75                .entry(finish.to_string())
76                .or_default()
77                .insert(date.to_string(), price);
78        }
79
80        Ok(serde_json::to_value(result).unwrap_or(Value::Null))
81    }
82
83    /// Get the most recent price for each provider/price_type/finish group for a card UUID.
84    ///
85    /// Optionally filtered by `provider`, `finish`, and `price_type` via [`PriceFilter`].
86    pub fn today(&self, uuid: &str, filter: &PriceFilter) -> Result<Vec<Value>> {
87        self.conn.ensure_views(&["all_prices_today"])?;
88
89        let mut parts = vec![
90            "SELECT * FROM all_prices_today".to_string(),
91            "WHERE uuid = ?".to_string(),
92            "AND date = (SELECT MAX(date) FROM all_prices_today WHERE uuid = ?)".to_string(),
93        ];
94        let mut params = vec![uuid.to_string(), uuid.to_string()];
95
96        append_filter(&mut parts, &mut params, filter);
97
98        let sql = parts.join(" ");
99        let rows = self.conn.execute(&sql, &params)?;
100        Ok(rows_to_values(rows))
101    }
102
103    /// Get price history for a card UUID, optionally filtered by date range and price filters.
104    pub fn history(
105        &self,
106        uuid: &str,
107        date_from: Option<&str>,
108        date_to: Option<&str>,
109        filter: &PriceFilter,
110    ) -> Result<Vec<Value>> {
111        self.conn.ensure_views(&["all_prices"])?;
112
113        let mut qb = SqlBuilder::new("all_prices");
114        qb.where_eq("uuid", uuid);
115        qb.order_by(&["date ASC"]);
116
117        if let Some(df) = date_from {
118            qb.where_gte("date", df);
119        }
120
121        if let Some(dt) = date_to {
122            qb.where_lte("date", dt);
123        }
124
125        if let Some(ref provider) = filter.provider {
126            qb.where_eq("provider", provider);
127        }
128        if let Some(ref finish) = filter.finish {
129            qb.where_eq("finish", finish);
130        }
131        if let Some(ref pt) = filter.price_type {
132            qb.where_eq("price_type", pt);
133        }
134
135        let (sql, params) = qb.build();
136        let rows = self.conn.execute(&sql, &params)?;
137        Ok(rows_to_values(rows))
138    }
139
140    /// Get aggregated price trend statistics for a card UUID.
141    ///
142    /// Returns `min_price`, `max_price`, `avg_price`, `first_date`, `last_date`, `data_points`.
143    pub fn price_trend(&self, uuid: &str, filter: &PriceFilter) -> Result<Value> {
144        self.conn.ensure_views(&["all_prices"])?;
145
146        let price_type = filter
147            .price_type
148            .as_deref()
149            .unwrap_or("retail");
150
151        let mut parts = vec![
152            "SELECT".to_string(),
153            "  MIN(price) AS min_price,".to_string(),
154            "  MAX(price) AS max_price,".to_string(),
155            "  AVG(price) AS avg_price,".to_string(),
156            "  MIN(date) AS first_date,".to_string(),
157            "  MAX(date) AS last_date,".to_string(),
158            "  COUNT(*) AS data_points".to_string(),
159            "FROM all_prices_today".to_string(),
160            "WHERE uuid = ? AND price_type = ?".to_string(),
161        ];
162        let mut params = vec![uuid.to_string(), price_type.to_string()];
163
164        if let Some(ref provider) = filter.provider {
165            parts.push("AND provider = ?".to_string());
166            params.push(provider.clone());
167        }
168        if let Some(ref finish) = filter.finish {
169            parts.push("AND finish = ?".to_string());
170            params.push(finish.clone());
171        }
172
173        let sql = parts.join(" ");
174        let rows = self.conn.execute(&sql, &params)?;
175        Ok(rows
176            .into_iter()
177            .next()
178            .map(|r| serde_json::to_value(r).unwrap_or(Value::Null))
179            .unwrap_or(Value::Null))
180    }
181
182    /// Find the cheapest printing of a card by name.
183    ///
184    /// Joins `cards` to `all_prices_today` and returns the printing with the lowest price.
185    pub fn cheapest_printing(&self, name: &str, filter: &PriceFilter) -> Result<Option<Value>> {
186        self.conn.ensure_views(&["cards", "all_prices_today"])?;
187
188        let provider = filter.provider.as_deref().unwrap_or("tcgplayer");
189        let finish = filter.finish.as_deref().unwrap_or("normal");
190        let price_type = filter.price_type.as_deref().unwrap_or("retail");
191
192        let sql = r#"
193            SELECT c.uuid, c.setCode, c.number, p.price, p.date
194            FROM cards c
195            JOIN all_prices_today p ON c.uuid = p.uuid
196            WHERE c.name = ? AND p.provider = ?
197              AND p.finish = ? AND p.price_type = ?
198              AND p.date = (SELECT MAX(p2.date) FROM all_prices_today p2
199                WHERE p2.uuid = c.uuid AND p2.provider = ?
200                AND p2.finish = ? AND p2.price_type = ?)
201            ORDER BY p.price ASC
202            LIMIT 1
203        "#;
204
205        let rows = self.conn.execute(
206            sql,
207            &[
208                name.to_string(),
209                provider.to_string(),
210                finish.to_string(),
211                price_type.to_string(),
212                provider.to_string(),
213                finish.to_string(),
214                price_type.to_string(),
215            ],
216        )?;
217        Ok(rows
218            .into_iter()
219            .next()
220            .map(|r| serde_json::to_value(r).unwrap_or(Value::Null)))
221    }
222
223    /// Find the cheapest available printing of each card (global leaderboard).
224    ///
225    /// Groups by card name and uses `arg_min()` for efficient single-pass aggregation.
226    /// Returns `name`, `cheapest_set`, `cheapest_number`, `cheapest_uuid`, `min_price`.
227    pub fn cheapest_printings(
228        &self,
229        filter: &PriceFilter,
230        limit: Option<usize>,
231        offset: Option<usize>,
232    ) -> Result<Vec<Value>> {
233        self.conn.ensure_views(&["cards", "all_prices_today"])?;
234
235        let provider = filter.provider.as_deref().unwrap_or("tcgplayer");
236        let finish = filter.finish.as_deref().unwrap_or("normal");
237        let price_type = filter.price_type.as_deref().unwrap_or("retail");
238        let limit = limit.unwrap_or(100);
239        let offset = offset.unwrap_or(0);
240
241        let sql = format!(
242            r#"
243            SELECT c.name,
244              arg_min(c.setCode, p.price) AS cheapest_set,
245              arg_min(c.number, p.price) AS cheapest_number,
246              arg_min(c.uuid, p.price) AS cheapest_uuid,
247              MIN(p.price) AS min_price
248            FROM cards c
249            JOIN all_prices_today p ON c.uuid = p.uuid
250            WHERE p.provider = ? AND p.finish = ? AND p.price_type = ?
251              AND p.date = (SELECT MAX(date) FROM all_prices_today)
252            GROUP BY c.name
253            ORDER BY min_price ASC
254            LIMIT {} OFFSET {}
255            "#,
256            limit, offset
257        );
258
259        let rows = self.conn.execute(
260            &sql,
261            &[
262                provider.to_string(),
263                finish.to_string(),
264                price_type.to_string(),
265            ],
266        )?;
267        Ok(rows_to_values(rows))
268    }
269
270    /// Find the most expensive printing of each card (global leaderboard).
271    ///
272    /// Groups by card name and uses `arg_max()` for efficient single-pass aggregation.
273    /// Returns `name`, `priciest_set`, `priciest_number`, `priciest_uuid`, `max_price`.
274    pub fn most_expensive_printings(
275        &self,
276        filter: &PriceFilter,
277        limit: Option<usize>,
278        offset: Option<usize>,
279    ) -> Result<Vec<Value>> {
280        self.conn.ensure_views(&["cards", "all_prices_today"])?;
281
282        let provider = filter.provider.as_deref().unwrap_or("tcgplayer");
283        let finish = filter.finish.as_deref().unwrap_or("normal");
284        let price_type = filter.price_type.as_deref().unwrap_or("retail");
285        let limit = limit.unwrap_or(100);
286        let offset = offset.unwrap_or(0);
287
288        let sql = format!(
289            r#"
290            SELECT c.name,
291              arg_max(c.setCode, p.price) AS priciest_set,
292              arg_max(c.number, p.price) AS priciest_number,
293              arg_max(c.uuid, p.price) AS priciest_uuid,
294              MAX(p.price) AS max_price
295            FROM cards c
296            JOIN all_prices_today p ON c.uuid = p.uuid
297            WHERE p.provider = ? AND p.finish = ? AND p.price_type = ?
298              AND p.date = (SELECT MAX(date) FROM all_prices_today)
299            GROUP BY c.name
300            ORDER BY max_price DESC
301            LIMIT {} OFFSET {}
302            "#,
303            limit, offset
304        );
305
306        let rows = self.conn.execute(
307            &sql,
308            &[
309                provider.to_string(),
310                finish.to_string(),
311                price_type.to_string(),
312            ],
313        )?;
314        Ok(rows_to_values(rows))
315    }
316}
317
318// ---------------------------------------------------------------------------
319// Helpers
320// ---------------------------------------------------------------------------
321
322fn append_filter(parts: &mut Vec<String>, params: &mut Vec<String>, filter: &PriceFilter) {
323    if let Some(ref provider) = filter.provider {
324        parts.push("AND provider = ?".to_string());
325        params.push(provider.clone());
326    }
327    if let Some(ref finish) = filter.finish {
328        parts.push("AND finish = ?".to_string());
329        params.push(finish.clone());
330    }
331    if let Some(ref pt) = filter.price_type {
332        parts.push("AND price_type = ?".to_string());
333        params.push(pt.clone());
334    }
335}
336
337fn rows_to_values(rows: Vec<HashMap<String, Value>>) -> Vec<Value> {
338    rows.into_iter()
339        .map(|r| serde_json::to_value(r).unwrap_or(Value::Null))
340        .collect()
341}
mtgjson_sdk/queries/prices.rs

mtgjson_sdk/queries/
prices.rs
