Skip to main content

radio_cli/
store.rs

1mod facets;
2mod geo;
3mod revisions;
4pub use revisions::ListRevision;
5
6use crate::error::{DomainError, ErrorCode};
7use crate::model::{Filter, Recording, Station};
8use anyhow::{Context, Result, bail};
9use rusqlite::{
10    Connection, OptionalExtension, TransactionBehavior, params, params_from_iter, types::Value,
11};
12use std::path::Path;
13
14const RECORDING_ORDER: &str =
15    "CASE WHEN json_valid(json) THEN json_extract(json,'$.started_at') END DESC,id DESC";
16
17pub struct Store {
18    conn: Connection,
19}
20
21impl Store {
22    pub(crate) fn open_writer(path: &Path) -> Result<Self> {
23        // The session owner has already migrated the database. A short lock
24        // budget lets background writers retry and observe shutdown promptly.
25        let conn = Connection::open(path)?;
26        conn.busy_timeout(std::time::Duration::from_millis(100))?;
27        conn.execute_batch("PRAGMA foreign_keys=ON; PRAGMA synchronous=NORMAL;")?;
28        Ok(Self { conn })
29    }
30
31    #[cfg(test)]
32    pub(crate) fn test_connection(&self) -> &Connection {
33        &self.conn
34    }
35
36    pub(crate) fn open_reader(path: &Path) -> Result<Self> {
37        let conn = Connection::open_with_flags(path, rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY)?;
38        conn.busy_timeout(std::time::Duration::from_millis(100))?;
39        conn.execute_batch("PRAGMA cache_size=-4096;")?;
40        Ok(Self { conn })
41    }
42
43    pub(crate) fn interrupt_handle(&self) -> rusqlite::InterruptHandle {
44        self.conn.get_interrupt_handle()
45    }
46
47    pub(crate) fn cancel_queries_when(&self, cancel: impl FnMut() -> bool + Send + 'static) {
48        self.conn.progress_handler(1000, Some(cancel));
49    }
50
51    pub(crate) fn read_snapshot<T>(&self, read: impl FnOnce(&Self) -> Result<T>) -> Result<T> {
52        let tx = self.conn.unchecked_transaction()?;
53        // BEGIN is deferred. Pin the WAL snapshot before reading revisions
54        // so a count cached for an older snapshot cannot be reused for new rows.
55        tx.query_row("SELECT 1 FROM sqlite_schema LIMIT 1", [], |_| Ok(()))?;
56        let value = read(self)?;
57        tx.commit()?;
58        Ok(value)
59    }
60
61    pub fn open(path: &Path) -> Result<Self> {
62        let mut conn = Connection::open(path)?;
63        conn.busy_timeout(std::time::Duration::from_secs(5))?;
64        conn.execute_batch(
65            "PRAGMA foreign_keys=ON; PRAGMA journal_mode=WAL;
66            PRAGMA cache_size=-4096; PRAGMA synchronous=NORMAL;",
67        )?;
68        let version: u32 = conn.pragma_query_value(None, "user_version", |r| r.get(0))?;
69        if version > 8 {
70            bail!(DomainError::new(ErrorCode::DatabaseTooNew));
71        }
72        if version < 8 {
73            let tx = conn.transaction_with_behavior(TransactionBehavior::Immediate)?;
74            // Another process may have migrated while we waited for the lock.
75            let version: u32 = tx.pragma_query_value(None, "user_version", |r| r.get(0))?;
76            match version {
77                0 => tx.execute_batch(
78                    "CREATE TABLE stations (
79                        uuid TEXT PRIMARY KEY, name TEXT NOT NULL, search TEXT NOT NULL,
80                        country TEXT NOT NULL, languages TEXT NOT NULL, tags TEXT NOT NULL,
81                        bitrate INTEGER NOT NULL, popularity INTEGER NOT NULL,
82                        custom INTEGER NOT NULL, active INTEGER NOT NULL, json TEXT NOT NULL);
83                    CREATE INDEX station_country ON stations(country, bitrate);
84                    CREATE INDEX station_popularity ON stations(popularity DESC);
85                    CREATE TABLE favorites (
86                        uuid TEXT PRIMARY KEY REFERENCES stations(uuid), created_at TEXT NOT NULL);
87                    CREATE TABLE history (
88                        id INTEGER PRIMARY KEY, uuid TEXT NOT NULL REFERENCES stations(uuid), played_at TEXT NOT NULL);
89                    CREATE INDEX history_station ON history(uuid);
90                    CREATE TABLE streams (
91                        uuid TEXT NOT NULL REFERENCES stations(uuid), url TEXT NOT NULL, PRIMARY KEY(uuid,url));
92                    CREATE TABLE recordings (
93                        id TEXT PRIMARY KEY, status TEXT NOT NULL, json TEXT NOT NULL,
94                        recovery_pending INTEGER NOT NULL DEFAULT 0 CHECK(recovery_pending IN (0,1)));
95                    CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);"
96                )?,
97                1 => tx.execute_batch(
98                    "ALTER TABLE recordings ADD COLUMN recovery_pending INTEGER NOT NULL DEFAULT 0 CHECK(recovery_pending IN (0,1));
99                    -- Translate legacy diagnostics once, never during normal queries.
100                    UPDATE recordings SET recovery_pending=1 WHERE status='interrupted' AND json_extract(json,'$.error') LIKE '恢复失败:%';"
101                )?,
102                2..=8 => {},
103                _ => bail!(DomainError::new(ErrorCode::DatabaseTooNew)),
104            }
105            if version < 3 {
106                tx.execute_batch(
107                    "ALTER TABLE stations ADD COLUMN normalized_url TEXT;
108                    CREATE INDEX station_url ON stations(normalized_url) WHERE active=1;",
109                )?;
110                let mut select = tx.prepare("SELECT uuid,json FROM stations")?;
111                let mut update =
112                    tx.prepare("UPDATE stations SET normalized_url=?2 WHERE uuid=?1")?;
113                let rows = select
114                    .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?)))?;
115                for row in rows {
116                    let (uuid, json) = row?;
117                    let station: Station = serde_json::from_str(&json)?;
118                    update.execute(params![
119                        uuid,
120                        crate::service::normalized_url(station.primary_url()).ok()
121                    ])?;
122                }
123            }
124            if version < 4 {
125                tx.execute_batch(
126                    "CREATE TABLE facets_cache (
127                    kind INTEGER NOT NULL,value TEXT NOT NULL,n INTEGER NOT NULL CHECK(n>=0),
128                    PRIMARY KEY(kind,value)) WITHOUT ROWID;",
129                )?;
130                facets::rebuild(&tx)?;
131            }
132            if version < 5 {
133                tx.execute_batch(
134                    "CREATE INDEX station_sort ON stations(popularity DESC,name,uuid,active,custom,bitrate);
135                    CREATE INDEX station_name ON stations(name COLLATE NOCASE,uuid,active,custom,bitrate);
136                    CREATE INDEX station_bitrate ON stations(bitrate DESC,name,uuid,active,custom);
137                    CREATE INDEX station_active ON stations(active) WHERE active=1;
138                    CREATE INDEX station_directory ON stations(active) WHERE active=1 AND custom=0;
139                    DROP INDEX IF EXISTS station_popularity;
140                    CREATE VIRTUAL TABLE station_search USING fts5(search,
141                        content='stations',content_rowid='rowid',columnsize=0,detail=none,
142                        tokenize='trigram case_sensitive 1');
143                    INSERT INTO station_search(station_search) VALUES ('rebuild');
144                    CREATE VIRTUAL TABLE station_search_terms USING fts5vocab(station_search,'row');
145                    CREATE TABLE station_search_stats(term TEXT PRIMARY KEY,n INTEGER NOT NULL) WITHOUT ROWID;
146                    INSERT INTO station_search_stats SELECT term,doc FROM station_search_terms;
147                    INSERT INTO station_search_stats VALUES ('',(SELECT count(*) FROM stations));
148                    CREATE TRIGGER station_search_insert AFTER INSERT ON stations BEGIN
149                        INSERT INTO station_search(rowid,search) VALUES (new.rowid,new.search);
150                    END;
151                    CREATE TRIGGER station_search_delete AFTER DELETE ON stations BEGIN
152                        INSERT INTO station_search(station_search,rowid,search) VALUES ('delete',old.rowid,old.search);
153                    END;
154                    CREATE TRIGGER station_search_update AFTER UPDATE OF search ON stations
155                    WHEN old.search IS NOT new.search BEGIN
156                        INSERT INTO station_search(station_search,rowid,search) VALUES ('delete',old.rowid,old.search);
157                        INSERT INTO station_search(rowid,search) VALUES (new.rowid,new.search);
158                    END;
159                    ANALYZE;",
160                )?;
161            }
162            if version < 6 {
163                geo::migrate(&tx)?;
164            }
165            if version < 7 {
166                revisions::migrate(&tx)?;
167            }
168            if version < 8 {
169                tx.execute_batch(&format!(
170                    "CREATE INDEX IF NOT EXISTS recording_started ON recordings({RECORDING_ORDER});"
171                ))?;
172            }
173            tx.pragma_update(None, "user_version", 8)?;
174            tx.commit()?;
175        }
176        Ok(Self { conn })
177    }
178
179    pub fn upsert(&self, station: &Station) -> Result<()> {
180        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
181        Self::write_station(&tx, "stations", station)?;
182        geo::changed(&tx)?;
183        tx.commit()?;
184        Ok(())
185    }
186
187    pub fn bootstrap(
188        &mut self,
189        rows: &[Station],
190        mut cancelled: impl FnMut() -> bool,
191    ) -> Result<usize> {
192        let tx = self.conn.transaction()?;
193        for row in rows {
194            if cancelled() {
195                bail!(DomainError::new(ErrorCode::Cancelled));
196            }
197            Self::write_station_with_policy(&tx, "stations", row, false, false)?;
198        }
199        if cancelled() {
200            bail!(DomainError::new(ErrorCode::Cancelled));
201        }
202        if rows.is_empty() {
203            bail!(DomainError::new(ErrorCode::EmptyCatalog));
204        }
205        facets::rebuild(&tx)?;
206        if cancelled() {
207            bail!(DomainError::new(ErrorCode::Cancelled));
208        }
209        geo::changed(&tx)?;
210        tx.commit()?;
211        // Advisory maintenance must not fail an already published catalog.
212        let _ = self.refresh_search_stats();
213        Ok(rows.len())
214    }
215
216    fn write_station(conn: &Connection, table: &str, s: &Station) -> Result<()> {
217        Self::write_station_with_policy(conn, table, s, false, table == "stations")
218    }
219
220    fn write_station_with_policy(
221        conn: &Connection,
222        table: &str,
223        s: &Station,
224        custom: bool,
225        maintain_facets: bool,
226    ) -> Result<()> {
227        if s.stationuuid.is_empty() || s.name.trim().is_empty() {
228            bail!(DomainError::new(ErrorCode::InvalidStation));
229        }
230        uuid::Uuid::parse_str(&s.stationuuid).context(DomainError::new(ErrorCode::InvalidUuid))?;
231        let before = if maintain_facets {
232            facets::station_values(conn, &s.stationuuid)?
233        } else {
234            Default::default()
235        };
236        let sql = format!("INSERT INTO {table} (uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y)
237            VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14)
238            ON CONFLICT(uuid) DO UPDATE SET name=excluded.name,search=excluded.search,
239            country=excluded.country,languages=excluded.languages,tags=excluded.tags,
240            bitrate=excluded.bitrate,popularity=excluded.popularity,custom=excluded.custom,active=excluded.active,json=excluded.json,normalized_url=excluded.normalized_url,geo_x=excluded.geo_x,geo_y=excluded.geo_y
241            WHERE {table}.custom=0 OR {custom}");
242        // Facet queries compare normalized prefixes with binary SQL equality.
243        // Keep country codes uppercase and language/tag tokens Unicode lowercase
244        // here, for both catalog imports and custom stations.
245        let normalize = |value: &str| {
246            value
247                .split(',')
248                .map(|v| v.trim().to_lowercase())
249                .filter(|v| !v.is_empty())
250                .collect::<Vec<_>>()
251                .join(",")
252        };
253        let languages = normalize(&format!("{},{}", s.language, s.languagecodes));
254        let coordinates = crate::geo::coordinates(s.geo_lat, s.geo_long);
255        conn.prepare_cached(&sql)?.execute(params![
256            s.stationuuid,
257            s.name,
258            s.search_text(),
259            s.countrycode.to_uppercase(),
260            format!(",{languages},"),
261            format!(",{},", normalize(&s.tags)),
262            s.bitrate,
263            s.clickcount.min(i64::MAX as u64),
264            s.custom,
265            s.active,
266            serde_json::to_string(s)?,
267            crate::service::normalized_url(s.primary_url()).ok(),
268            coordinates.map(|p| p.0),
269            coordinates.map(|p| p.1)
270        ])?;
271        if maintain_facets {
272            facets::update(
273                conn,
274                &before,
275                &facets::station_values(conn, &s.stationuuid)?,
276            )?;
277        }
278        Ok(())
279    }
280
281    pub fn set_meta(&self, key: &str, value: &str) -> Result<()> {
282        Self::write_meta(&self.conn, key, value)
283    }
284
285    fn write_meta(conn: &Connection, key: &str, value: &str) -> Result<()> {
286        conn.execute(
287            "INSERT INTO meta(key,value) VALUES (?1,?2) ON CONFLICT(key) DO UPDATE SET value=excluded.value WHERE meta.value IS NOT excluded.value",
288            params![key, value],
289        )?;
290        Ok(())
291    }
292
293    pub(crate) fn save_session(
294        &mut self,
295        preferences: &std::collections::BTreeMap<String, String>,
296        plays: &[(String, String)],
297    ) -> Result<()> {
298        let tx = self
299            .conn
300            .transaction_with_behavior(TransactionBehavior::Immediate)?;
301        for (uuid, at) in plays {
302            tx.execute(
303                "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
304                params![uuid, at],
305            )?;
306            let threshold = tx.last_insert_rowid().saturating_sub(1000);
307            tx.execute("DELETE FROM history WHERE id<=?1", [threshold])?;
308            Self::write_meta(&tx, "pref_last_station", uuid)?;
309        }
310        for (key, value) in preferences {
311            Self::write_meta(&tx, key, value)?;
312        }
313        tx.commit()?;
314        Ok(())
315    }
316
317    pub fn directory_count(&self) -> Result<usize> {
318        Ok(self.conn.query_row(
319            "SELECT count(*) FROM stations WHERE custom=0 AND active=1",
320            [],
321            |r| r.get(0),
322        )?)
323    }
324
325    pub fn incoming_count(&self) -> Result<usize> {
326        Ok(self
327            .conn
328            .query_row("SELECT count(*) FROM incoming", [], |r| r.get(0))?)
329    }
330
331    pub fn begin_sync(&self) -> Result<()> {
332        self.conn.execute_batch(
333            "DROP TABLE IF EXISTS temp.incoming;
334            CREATE TEMP TABLE incoming AS SELECT uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y FROM stations WHERE 0;
335            CREATE UNIQUE INDEX temp.incoming_uuid ON incoming(uuid);",
336        )?;
337        Ok(())
338    }
339
340    pub fn stage(&mut self, rows: &[Station]) -> Result<()> {
341        let tx = self.conn.transaction()?;
342        for row in rows {
343            let mut s = row.clone();
344            s.active = true;
345            s.custom = false;
346            Self::write_station(&tx, "incoming", &s)?;
347        }
348        tx.commit()?;
349        Ok(())
350    }
351
352    pub fn finish_sync(&mut self) -> Result<usize> {
353        self.finish_sync_cancellable(|| false)
354    }
355
356    pub(crate) fn finish_sync_cancellable(
357        &mut self,
358        mut cancelled: impl FnMut() -> bool,
359    ) -> Result<usize> {
360        let tx = self
361            .conn
362            .transaction_with_behavior(TransactionBehavior::Immediate)?;
363        let count: usize = tx.query_row("SELECT count(*) FROM incoming", [], |r| r.get(0))?;
364        if count == 0 {
365            bail!(DomainError::new(ErrorCode::EmptyCatalog));
366        }
367        let search_changed: bool = tx.query_row(
368            "SELECT EXISTS(SELECT 1 FROM incoming i LEFT JOIN stations s ON s.uuid=i.uuid
369             WHERE s.uuid IS NULL OR (s.custom=0 AND s.search IS NOT i.search))",
370            [],
371            |r| r.get(0),
372        )?;
373        let facet_delta = facets::publication_delta(&tx)?;
374        if cancelled() {
375            bail!(DomainError::new(ErrorCode::Cancelled));
376        }
377        let removed = tx.execute(
378            "UPDATE stations SET active=0 WHERE custom=0 AND active=1
379             AND NOT EXISTS(SELECT 1 FROM incoming i WHERE i.uuid=stations.uuid)",
380            [],
381        )?;
382        let updated = tx.execute(
383            "INSERT INTO stations (uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y) SELECT uuid,name,search,country,languages,tags,bitrate,popularity,custom,active,json,normalized_url,geo_x,geo_y FROM incoming WHERE true
384            ON CONFLICT(uuid) DO UPDATE SET name=excluded.name,search=excluded.search,
385            country=excluded.country,languages=excluded.languages,tags=excluded.tags,
386            bitrate=excluded.bitrate,popularity=excluded.popularity,active=1,json=excluded.json,normalized_url=excluded.normalized_url,geo_x=excluded.geo_x,geo_y=excluded.geo_y
387            WHERE stations.custom=0 AND (
388                stations.active<>1 OR stations.json IS NOT excluded.json OR
389                stations.name IS NOT excluded.name OR stations.search IS NOT excluded.search OR
390                stations.country IS NOT excluded.country OR stations.languages IS NOT excluded.languages OR
391                stations.tags IS NOT excluded.tags OR stations.bitrate IS NOT excluded.bitrate OR
392                stations.popularity IS NOT excluded.popularity OR stations.normalized_url IS NOT excluded.normalized_url OR
393                stations.geo_x IS NOT excluded.geo_x OR stations.geo_y IS NOT excluded.geo_y)", [],
394        )?;
395        tx.execute(
396            "INSERT OR REPLACE INTO meta(key,value) VALUES ('last_sync',?1)",
397            [chrono::Utc::now().to_rfc3339()],
398        )?;
399        facets::apply_delta(&tx, facet_delta)?;
400        if cancelled() {
401            bail!(DomainError::new(ErrorCode::Cancelled));
402        }
403        if removed + updated > 0 {
404            geo::changed(&tx)?;
405        }
406        tx.commit()?;
407        // Planner maintenance cannot turn a successful publication into a
408        // reported failure after its transaction has already committed.
409        if search_changed {
410            let _ = self.refresh_search_stats();
411        }
412        if removed + updated > 0 {
413            let _ = self.conn.execute_batch("PRAGMA optimize;");
414        }
415        Ok(count)
416    }
417
418    /// Connection activity for diagnostics, including preferences and SQL
419    /// maintenance. This is not a list cache key; use [`Self::list_revision`].
420    /// Values are meaningful only for repeated reads on the same connection.
421    pub fn revision(&self) -> Result<(u64, u64)> {
422        let external = self
423            .conn
424            .pragma_query_value(None, "data_version", |r| r.get(0))?;
425        Ok((external, self.conn.total_changes()))
426    }
427
428    pub fn meta(&self, key: &str) -> Result<Option<String>> {
429        Ok(self
430            .conn
431            .query_row("SELECT value FROM meta WHERE key=?1", [key], |r| r.get(0))
432            .optional()?)
433    }
434
435    pub fn count(&self) -> Result<usize> {
436        Ok(self
437            .conn
438            .query_row("SELECT count(*) FROM stations WHERE active=1", [], |r| {
439                r.get(0)
440            })?)
441    }
442
443    pub fn station(&self, uuid: &str) -> Result<Station> {
444        let row = self
445            .conn
446            .query_row(
447                "SELECT s.json,s.active,f.uuid IS NOT NULL FROM stations s
448            LEFT JOIN favorites f ON f.uuid=s.uuid WHERE s.uuid=?1",
449                [uuid],
450                |r| {
451                    Ok((
452                        r.get::<_, String>(0)?,
453                        r.get::<_, bool>(1)?,
454                        r.get::<_, bool>(2)?,
455                    ))
456                },
457            )
458            .optional()?
459            .context(DomainError::new(ErrorCode::StationNotFound))?;
460        let mut s: Station = serde_json::from_str(&row.0)?;
461        s.active = row.1;
462        s.favorite = row.2;
463        Ok(s)
464    }
465
466    fn conditions(&self, filter: &Filter) -> (String, Vec<Value>, &'static str) {
467        self.conditions_with_spatial(filter, true)
468    }
469
470    fn conditions_with_spatial(
471        &self,
472        filter: &Filter,
473        spatial: bool,
474    ) -> (String, Vec<Value>, &'static str) {
475        let mut clauses = vec![
476            "(s.active=1 OR s.custom=1 OR EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid))"
477                .to_owned(),
478        ];
479        let mut args = vec![];
480        // Token filters still use instr. Without a country index to narrow
481        // them, avoid random table lookups through a sorting index.
482        let mut source =
483            if filter.country.is_none() && (filter.language.is_some() || filter.tag.is_some()) {
484                "stations s NOT INDEXED"
485            } else {
486                "stations s"
487            };
488        if filter.favorites {
489            clauses.push("EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid)".into());
490        }
491        if filter.custom {
492            clauses.push("s.custom=1".into());
493        }
494        if filter.history {
495            clauses[0] = "1".into();
496        }
497        let mut text_candidates = false;
498        let query = filter.query.trim();
499        if !query.is_empty() {
500            // Resolve localized names at query time so existing catalogs do not
501            // need a resync or a rewrite of their original station metadata.
502            let codes: Vec<_> = crate::territories::matching_codes(query).collect();
503            let normalized = query.to_lowercase();
504            source = "stations s NOT INDEXED";
505            if !filter.history
506                && let Some(candidate) = self.trigram_candidate(&normalized)
507            {
508                text_candidates = true;
509                // A literal trigram narrows candidates; instr below checks
510                // the complete query without decoding positional postings.
511                let mut candidates =
512                    "SELECT rowid FROM station_search WHERE station_search MATCH ?".to_owned();
513                args.push(candidate.into());
514                if !codes.is_empty() {
515                    candidates.push_str(&format!(
516                        " UNION SELECT rowid FROM stations WHERE country IN ({})",
517                        vec!["?"; codes.len()].join(",")
518                    ));
519                    args.extend(codes.iter().map(|code| Value::from((*code).to_owned())));
520                }
521                clauses.push(
522                    if codes.is_empty()
523                        && !matches!(filter.geo, Some(crate::geo::GeoFilter::Cell(_)))
524                    {
525                        source =
526                            "station_search CROSS JOIN stations s ON s.rowid=station_search.rowid";
527                        "station_search MATCH ?".to_owned()
528                    } else {
529                        format!("s.rowid IN ({candidates})")
530                    },
531                );
532            }
533            if codes.is_empty() {
534                clauses.push("instr(s.search,?)>0".into());
535            } else {
536                let placeholders = vec!["?"; codes.len()].join(",");
537                clauses.push(format!(
538                    "(instr(s.search,?)>0 OR s.country IN ({placeholders}))"
539                ));
540            }
541            args.push(normalized.into());
542            args.extend(codes.into_iter().map(|code| Value::from(code.to_owned())));
543        }
544        if let Some(country) = &filter.country {
545            clauses.push("s.country=?".into());
546            args.push(country.to_uppercase().into());
547        }
548        for (col, value) in [("languages", &filter.language), ("tags", &filter.tag)] {
549            if let Some(value) = value {
550                clauses.push(format!("instr(s.{col},?)>0"));
551                args.push(format!(",{},", value.trim().to_lowercase()).into());
552            }
553        }
554        if filter.min_bitrate > 0 {
555            clauses.push("s.bitrate>=?".into());
556            args.push((filter.min_bitrate as i64).into());
557        }
558        if let Some(geo) = filter.geo {
559            clauses[0] = "s.active=1".into();
560            match geo {
561                crate::geo::GeoFilter::Missing => clauses.push("s.geo_x IS NULL".into()),
562                crate::geo::GeoFilter::Cell(bounds) if spatial => {
563                    clauses.push(geo::predicate(bounds, &mut args));
564                    // A full longitude scan through a non-covering spatial
565                    // index turns filtered world maps into random table reads.
566                    if (!text_candidates && bounds.width < crate::geo::WORLD_WIDTH)
567                        || geo::plain(filter)
568                    {
569                        source = "stations s INDEXED BY station_geo";
570                    }
571                    if filter.query.trim().is_empty()
572                        && let Some(country) = filter.country.as_ref().filter(|c| !c.is_empty())
573                        && self
574                            .conn
575                            .prepare_cached("SELECT n FROM facets_cache WHERE kind=0 AND value=?1")
576                            .and_then(|mut stmt| {
577                                stmt.query_row([country.to_uppercase()], |r| r.get::<_, usize>(0))
578                                    .optional()
579                            })
580                            .is_ok_and(|n| n.unwrap_or(0) <= geo::SORT_THRESHOLD)
581                    {
582                        // A small country is an upper bound on any of its map
583                        // cells. This hint only chooses an index, never rows.
584                        source = "stations s INDEXED BY station_country";
585                    }
586                }
587                crate::geo::GeoFilter::Cell(_) => {}
588            }
589        }
590        if filter.history {
591            // History retains at most 1000 entries. Drive the query from its
592            // distinct stations rather than probing history for every catalog
593            // row. CROSS JOIN preserves this bounded outer loop.
594            source = "(SELECT uuid,max(id) AS last_id FROM history GROUP BY uuid) recent
595                      CROSS JOIN stations s ON s.uuid=recent.uuid";
596        }
597        (clauses.join(" AND "), args, source)
598    }
599
600    /// One rare trigram supplies candidates; instr verifies the full literal.
601    /// Avoid positional phrase decoding for every row of a broad match.
602    fn trigram_candidate(&self, normalized: &str) -> Option<String> {
603        if normalized.chars().count() < 3 || normalized.contains('\0') {
604            return None;
605        }
606        let chars: Vec<_> = normalized.chars().collect();
607        let stride = ((chars.len() - 2) / 16).max(1);
608        let mut best = (usize::MAX, chars[..3].iter().collect::<String>());
609        // Bound vocabulary lookups for unusually long command-line queries.
610        let terms: std::collections::BTreeSet<String> = chars
611            .windows(3)
612            .step_by(stride)
613            .take(16)
614            .map(|part| part.iter().collect())
615            .collect();
616        for term in terms {
617            let frequency = self
618                .conn
619                .prepare_cached("SELECT n FROM station_search_stats WHERE term=?1")
620                .and_then(|mut s| s.query_row([&term], |r| r.get::<_, usize>(0)).optional());
621            // If statistics are unavailable, the literal remains a valid
622            // candidate. Query errors are reported by the actual FTS query.
623            let frequency = match frequency {
624                Ok(Some(n)) => n,
625                Ok(None) => 0,
626                Err(_) => continue,
627            };
628            if frequency < best.0 {
629                best = (frequency, term);
630            }
631            if frequency == 0 {
632                break;
633            }
634        }
635        let total: usize = self
636            .conn
637            .query_row(
638                "SELECT n FROM station_search_stats WHERE term=''",
639                [],
640                |r| r.get(0),
641            )
642            .unwrap_or(0);
643        // Statistics only choose a query strategy. Stale hints after a single
644        // station mutation never change the exact substring predicate.
645        if total > 0 && best.0 > total / 4 {
646            return None;
647        }
648        Some(format!("\"{}\"", best.1.replace('"', "\"\"")))
649    }
650
651    fn refresh_search_stats(&self) -> Result<()> {
652        // A single read statement pins a consistent snapshot for the vocabulary
653        // and row count. Collect before taking the main database's write lock:
654        // even a small vocabulary requires scanning FTS postings to count docs.
655        let stats = {
656            let mut stmt = self.conn.prepare(
657                "SELECT term,doc FROM station_search_terms
658                UNION ALL SELECT '',count(*) FROM stations",
659            )?;
660            stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))?
661                .collect::<rusqlite::Result<Vec<_>>>()?
662        };
663        // Publish all hints atomically. Concurrent station changes may make
664        // them stale, which only affects the choice of search strategy.
665        let tx = rusqlite::Transaction::new_unchecked(&self.conn, TransactionBehavior::Immediate)?;
666        tx.execute("DELETE FROM station_search_stats", [])?;
667        {
668            let mut stmt = tx.prepare("INSERT INTO station_search_stats VALUES (?1,?2)")?;
669            for (term, count) in stats {
670                stmt.execute(params![term, count])?;
671            }
672        }
673        tx.commit()?;
674        Ok(())
675    }
676
677    fn search_order(filter: &Filter) -> &'static str {
678        if filter.history {
679            "recent.last_id DESC"
680        } else {
681            match filter.sort {
682                crate::model::Sort::Name => "s.name COLLATE NOCASE,s.uuid",
683                crate::model::Sort::Bitrate => "s.bitrate DESC,s.name,s.uuid",
684                crate::model::Sort::Votes => "json_extract(s.json,'$.votes') DESC,s.name,s.uuid",
685                crate::model::Sort::Clickcount => "s.popularity DESC,s.name,s.uuid",
686                crate::model::Sort::Random => "random()",
687            }
688        }
689    }
690
691    /// Locate a station without loading every preceding page into memory.
692    pub fn station_position(&self, uuid: &str, filter: &Filter) -> Result<Option<usize>> {
693        use crate::model::Sort;
694        let (conditions, mut args, source) = self.conditions(filter);
695        if !filter.history && matches!(filter.sort, Sort::Clickcount | Sort::Bitrate | Sort::Name) {
696            let tx = self.conn.unchecked_transaction()?;
697            let mut target_args = args.clone();
698            target_args.push(uuid.to_owned().into());
699            let target = tx
700                .query_row(
701                    &format!(
702                        "SELECT s.popularity,s.bitrate,s.name FROM {source}
703                    WHERE {conditions} AND s.uuid=?"
704                    ),
705                    params_from_iter(target_args),
706                    |r| {
707                        Ok((
708                            r.get::<_, i64>(0)?,
709                            r.get::<_, i64>(1)?,
710                            r.get::<_, String>(2)?,
711                        ))
712                    },
713                )
714                .optional()?;
715            let Some((popularity, bitrate, name)) = target else {
716                return Ok(None);
717            };
718            let count = |predicate: &str, values: Vec<Value>| -> Result<usize> {
719                let mut values_with_filter = args.clone();
720                values_with_filter.extend(values);
721                Ok(tx
722                    .prepare_cached(&format!(
723                        "SELECT count(*) FROM {source}
724                    WHERE {conditions} AND ({predicate})"
725                    ))?
726                    .query_row(params_from_iter(values_with_filter), |r| r.get(0))?)
727            };
728            // Separate ranges let each count use the leading sort key. DESC
729            // on the numeric key and ASC on ties cannot use one tuple '<'.
730            let position = if filter.sort == Sort::Name {
731                count("s.name COLLATE NOCASE < ?", vec![name.clone().into()])?
732                    + count(
733                        "s.name COLLATE NOCASE = ? AND s.uuid < ?",
734                        vec![name.into(), uuid.to_owned().into()],
735                    )?
736            } else {
737                let (column, value) = if filter.sort == Sort::Bitrate {
738                    ("bitrate", bitrate)
739                } else {
740                    ("popularity", popularity)
741                };
742                count(&format!("s.{column}>?"), vec![value.into()])?
743                    + count(
744                        &format!("s.{column}=? AND (s.name,s.uuid)<(?,?)"),
745                        vec![value.into(), name.into(), uuid.to_owned().into()],
746                    )?
747            };
748            tx.commit()?;
749            return Ok(Some(position));
750        }
751        let order = Self::search_order(filter);
752        let sql = format!(
753            "WITH ranked AS (
754            SELECT s.uuid,row_number() OVER (ORDER BY {order})-1 AS position
755            FROM {source} WHERE {conditions}
756        ) SELECT position FROM ranked WHERE uuid=?"
757        );
758        args.push(uuid.to_owned().into());
759        Ok(self
760            .conn
761            .query_row(&sql, params_from_iter(args), |row| row.get::<_, i64>(0))
762            .optional()?
763            .map(|position| position as usize))
764    }
765
766    pub fn search(&self, filter: &Filter) -> Result<Vec<Station>> {
767        // The bounded geographic probe and its page must see the same rows,
768        // including when called directly outside the TUI reader's snapshot.
769        let _snapshot = if filter.geo.is_some() && self.conn.is_autocommit() {
770            Some(self.conn.unchecked_transaction()?)
771        } else {
772            None
773        };
774        let (mut conditions, mut args, mut source) = self.conditions(filter);
775        // Probe matching row IDs, not just geographic density: a city may have
776        // thousands of stations but only a few matching the user's filters.
777        // Small result sets reuse the probe; dense sets use a sort index and a
778        // compact spatial membership set to avoid sorting random table reads.
779        if let Some(crate::geo::GeoFilter::Cell(bounds)) = filter.geo
780            && !filter.history
781            && let Some(index) = match filter.sort {
782                crate::model::Sort::Clickcount => Some("stations s INDEXED BY station_sort"),
783                crate::model::Sort::Name => Some("stations s INDEXED BY station_name"),
784                crate::model::Sort::Bitrate => Some("stations s INDEXED BY station_bitrate"),
785                _ => None,
786            }
787        {
788            let probe = format!(
789                "SELECT s.rowid FROM {source} WHERE {conditions} LIMIT {}",
790                geo::SORT_THRESHOLD + 1
791            );
792            // Unfiltered probes are covering index scans: count inside SQLite
793            // instead of copying thousands of IDs we will immediately discard.
794            let ids = if geo::plain(filter) {
795                None
796            } else {
797                Some(
798                    self.conn
799                        .prepare_cached(&probe)?
800                        .query_map(params_from_iter(&args), |r| r.get::<_, i64>(0))?
801                        .collect::<rusqlite::Result<Vec<_>>>()?,
802                )
803            };
804            let n = if let Some(ids) = &ids {
805                ids.len()
806            } else {
807                self.conn
808                    .prepare_cached(&format!("SELECT count(*) FROM ({probe})"))?
809                    .query_row(params_from_iter(&args), |r| r.get::<_, usize>(0))?
810            };
811            if n == 0 {
812                return Ok(vec![]);
813            }
814            if n <= geo::SORT_THRESHOLD
815                && let Some(ids) = ids
816            {
817                conditions = format!("s.rowid IN ({})", vec!["?"; ids.len()].join(","));
818                args = ids.into_iter().map(Value::Integer).collect();
819                source = "stations s";
820            } else if n > geo::SORT_THRESHOLD {
821                let mut spatial_args = vec![];
822                let spatial = geo::predicate(bounds, &mut spatial_args);
823                let candidates = format!(
824                    "SELECT s.rowid FROM stations s INDEXED BY station_geo WHERE s.active=1 AND {spatial}"
825                );
826                // Membership already checks coordinates. Keep the remaining
827                // predicates so the sort scan fetches table rows only when a
828                // non-covering filter actually needs them.
829                (conditions, args, _) = self.conditions_with_spatial(filter, false);
830                conditions = format!("s.rowid IN ({candidates}) AND {conditions}");
831                spatial_args.extend(args);
832                args = spatial_args;
833                source = index;
834            }
835        }
836        let order = Self::search_order(filter);
837        // The random page has already been shuffled. Other sorts still need
838        // their outer order, including history which overrides filter.sort.
839        let page_order = if filter.history {
840            " ORDER BY page.last_id DESC".to_owned()
841        } else if filter.sort == crate::model::Sort::Random {
842            String::new()
843        } else {
844            format!(" ORDER BY {order}")
845        };
846        let history_column = if filter.history {
847            ",recent.last_id"
848        } else {
849            ""
850        };
851        // Sort compact row references, fetching JSON only for the selected page.
852        let sql = format!(
853            "WITH page AS MATERIALIZED (
854            SELECT s.rowid AS id{history_column} FROM {source}
855            WHERE {conditions} ORDER BY {order} LIMIT ? OFFSET ?)
856            SELECT s.json,s.active,EXISTS(SELECT 1 FROM favorites f WHERE f.uuid=s.uuid)
857            FROM page JOIN stations s ON s.rowid=page.id{page_order}"
858        );
859        args.push((filter.limit.clamp(1, 1000) as i64).into());
860        args.push((filter.offset.min(i64::MAX as usize) as i64).into());
861        let mut stmt = self.conn.prepare_cached(&sql)?;
862        let rows = stmt.query_map(params_from_iter(args), |r| {
863            Ok((
864                r.get::<_, String>(0)?,
865                r.get::<_, bool>(1)?,
866                r.get::<_, bool>(2)?,
867            ))
868        })?;
869        rows.map(|row| {
870            let (json, active, favorite) = row?;
871            let mut s: Station = serde_json::from_str(&json)?;
872            s.active = active;
873            s.favorite = favorite;
874            Ok(s)
875        })
876        .collect()
877    }
878
879    pub fn random(&self, filter: &Filter, exclude: &[String]) -> Result<Option<Station>> {
880        let (mut conditions, mut args, source) = self.conditions(filter);
881        if !exclude.is_empty() {
882            let placeholders = vec!["?"; exclude.len()].join(",");
883            conditions.push_str(&format!(" AND s.uuid NOT IN ({placeholders})"));
884            args.extend(exclude.iter().cloned().map(Value::Text));
885        }
886        let sql = format!(
887            "SELECT s.uuid FROM {source}
888            WHERE {conditions} ORDER BY random() LIMIT 1"
889        );
890        let selected = self
891            .conn
892            .query_row(&sql, params_from_iter(args), |r| r.get::<_, String>(0))
893            .optional()?;
894        selected.map(|id| self.station(&id)).transpose()
895    }
896
897    pub fn facets(
898        &self,
899        kind: crate::model::FacetKind,
900        prefix: &str,
901    ) -> Result<Vec<crate::model::Facet>> {
902        self.facets_limited(kind, prefix, usize::MAX)
903    }
904
905    pub fn facets_limited(
906        &self,
907        kind: crate::model::FacetKind,
908        prefix: &str,
909        limit: usize,
910    ) -> Result<Vec<crate::model::Facet>> {
911        use crate::model::{Facet, FacetKind};
912        let (kind, prefix) = match kind {
913            FacetKind::Country => (0, prefix.trim().to_uppercase()),
914            FacetKind::Language => (1, prefix.trim().to_lowercase()),
915            FacetKind::Tag => (2, prefix.trim().to_lowercase()),
916        };
917        let codes = if kind == 0 {
918            crate::territories::matching_prefix_codes(&prefix).collect::<Vec<_>>()
919        } else {
920            Vec::new()
921        };
922        // Match localized names before LIMIT, so countries beyond the first
923        // candidate page remain reachable. Values and counts stay catalog data.
924        let localized = if codes.is_empty() {
925            String::new()
926        } else {
927            let placeholders = (4..4 + codes.len())
928                .map(|index| format!("?{index}"))
929                .collect::<Vec<_>>()
930                .join(",");
931            format!(" OR value IN ({placeholders})")
932        };
933        let mut args: Vec<Value> = vec![
934            kind.into(),
935            prefix.into(),
936            (limit.min(i64::MAX as usize) as i64).into(),
937        ];
938        args.extend(codes.into_iter().map(|code| Value::from(code.to_owned())));
939        let mut stmt = self.conn.prepare_cached(&format!(
940            "SELECT value,n FROM facets_cache WHERE kind=?1
941             AND (substr(value,1,length(?2))=?2{localized}) ORDER BY value LIMIT ?3",
942        ))?;
943        let rows = stmt.query_map(params_from_iter(args), |r| {
944            Ok(Facet {
945                value: r.get(0)?,
946                count: r.get(1)?,
947            })
948        })?;
949        Ok(rows.collect::<rusqlite::Result<_>>()?)
950    }
951
952    pub fn favorite(&self, uuid: &str, add: bool) -> Result<()> {
953        self.station(uuid)?;
954        if add {
955            self.conn.execute(
956                "INSERT OR IGNORE INTO favorites(uuid,created_at) VALUES (?1,?2)",
957                params![uuid, chrono::Utc::now().to_rfc3339()],
958            )?;
959        } else {
960            self.conn
961                .execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
962        }
963        Ok(())
964    }
965
966    pub fn toggle_favorite(&mut self, uuid: &str) -> Result<bool> {
967        let tx = self
968            .conn
969            .transaction_with_behavior(TransactionBehavior::Immediate)?;
970        tx.query_row("SELECT 1 FROM stations WHERE uuid=?1", [uuid], |r| {
971            r.get::<_, u8>(0)
972        })
973        .optional()?
974        .context(DomainError::new(ErrorCode::StationNotFound))?;
975        let removed = tx.execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
976        let added = removed == 0;
977        if added {
978            tx.execute(
979                "INSERT INTO favorites(uuid,created_at) VALUES (?1,?2)",
980                params![uuid, chrono::Utc::now().to_rfc3339()],
981            )?;
982        }
983        tx.commit()?;
984        Ok(added)
985    }
986
987    pub fn played(&self, uuid: &str) -> Result<()> {
988        self.conn.execute(
989            "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
990            params![uuid, chrono::Utc::now().to_rfc3339()],
991        )?;
992        let threshold = self.conn.last_insert_rowid().saturating_sub(1000);
993        self.conn
994            .execute("DELETE FROM history WHERE id <= ?1", [threshold])?;
995        Ok(())
996    }
997
998    pub fn add_backup(&self, uuid: &str, url: &str) -> Result<()> {
999        crate::config::validate_url(url)?;
1000        self.conn.execute(
1001            "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1002            params![uuid, url],
1003        )?;
1004        Ok(())
1005    }
1006
1007    pub fn save_custom(&mut self, station: &Station, backups: Option<&[String]>) -> Result<()> {
1008        let tx = self
1009            .conn
1010            .transaction_with_behavior(TransactionBehavior::Immediate)?;
1011        let key = crate::service::normalized_url(station.primary_url())?;
1012        if let Some(uuid) = tx.query_row(
1013            "SELECT uuid FROM stations WHERE active=1 AND normalized_url=?1 AND uuid<>?2 LIMIT 1",
1014            params![key, station.stationuuid], |r| r.get::<_, String>(0),
1015        ).optional()? {
1016            bail!(DomainError::new(ErrorCode::DuplicateStation).with("uuid", uuid));
1017        }
1018        Self::write_station_with_policy(&tx, "stations", station, true, true)?;
1019        if let Some(backups) = backups {
1020            tx.execute("DELETE FROM streams WHERE uuid=?1", [&station.stationuuid])?;
1021            for url in backups {
1022                crate::config::validate_url(url)?;
1023                tx.execute(
1024                    "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1025                    params![station.stationuuid, url],
1026                )?;
1027            }
1028        }
1029        geo::changed(&tx)?;
1030        tx.commit()?;
1031        Ok(())
1032    }
1033
1034    pub fn remove_custom(&mut self, uuid: &str, keep_references: bool) -> Result<bool> {
1035        let tx = self
1036            .conn
1037            .transaction_with_behavior(TransactionBehavior::Immediate)?;
1038        let json: String = tx
1039            .query_row(
1040                "SELECT json FROM stations WHERE uuid=?1 AND custom=1",
1041                [uuid],
1042                |r| r.get(0),
1043            )
1044            .optional()?
1045            .context(DomainError::new(ErrorCode::NotCustom))?;
1046        let mut station: Station = serde_json::from_str(&json)?;
1047        let referenced: bool = tx.query_row("SELECT EXISTS(SELECT 1 FROM favorites WHERE uuid=?1) OR EXISTS(SELECT 1 FROM history WHERE uuid=?1) OR EXISTS(SELECT 1 FROM recordings WHERE json_extract(json,'$.stationuuid')=?1)", [uuid], |r| r.get(0))?;
1048        tx.execute("DELETE FROM streams WHERE uuid=?1", [uuid])?;
1049        if referenced || keep_references {
1050            station.custom = false;
1051            station.active = false;
1052            station.url.clear();
1053            station.url_resolved.clear();
1054            Self::write_station_with_policy(&tx, "stations", &station, true, true)?;
1055        } else {
1056            let before = facets::station_values(&tx, uuid)?;
1057            tx.execute("DELETE FROM stations WHERE uuid=?1", [uuid])?;
1058            facets::update(&tx, &before, &Default::default())?;
1059        }
1060        geo::changed(&tx)?;
1061        tx.commit()?;
1062        Ok(referenced || keep_references)
1063    }
1064
1065    pub fn update_backups(
1066        &mut self,
1067        uuid: &str,
1068        add: &[String],
1069        remove: &[String],
1070    ) -> Result<Vec<String>> {
1071        self.station(uuid)?;
1072        let tx = self.conn.transaction()?;
1073        for url in remove {
1074            tx.execute(
1075                "DELETE FROM streams WHERE uuid=?1 AND url=?2",
1076                params![uuid, url],
1077            )?;
1078        }
1079        for url in add {
1080            crate::config::validate_url(url)?;
1081            tx.execute(
1082                "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1083                params![uuid, url],
1084            )?;
1085        }
1086        tx.commit()?;
1087        self.backups(uuid)
1088    }
1089
1090    pub fn backups(&self, uuid: &str) -> Result<Vec<String>> {
1091        let mut stmt = self
1092            .conn
1093            .prepare("SELECT url FROM streams WHERE uuid=?1 ORDER BY rowid")?;
1094        Ok(stmt
1095            .query_map([uuid], |r| r.get(0))?
1096            .collect::<rusqlite::Result<_>>()?)
1097    }
1098
1099    pub fn save_recording(&self, recording: &Recording) -> Result<()> {
1100        self.conn.execute(
1101            "INSERT INTO recordings (id,status,json,recovery_pending) VALUES (?1,?2,?3,?4) ON CONFLICT(id) DO UPDATE SET status=excluded.status,json=excluded.json,recovery_pending=excluded.recovery_pending",
1102            params![
1103                recording.id,
1104                recording.status,
1105                serde_json::to_string(recording)?,
1106                recording.recovery_pending
1107            ],
1108        )?;
1109        Ok(())
1110    }
1111
1112    pub fn search_count(&self, filter: &Filter) -> Result<usize> {
1113        let (conditions, args, source) = self.conditions(filter);
1114        Ok(self
1115            .conn
1116            .prepare_cached(&format!("SELECT count(*) FROM {source} WHERE {conditions}"))?
1117            .query_row(params_from_iter(args), |r| r.get(0))?)
1118    }
1119
1120    pub fn recordings(&self, limit: usize) -> Result<Vec<Recording>> {
1121        self.recordings_page(limit, 0)
1122    }
1123
1124    pub fn recordings_count(&self) -> Result<usize> {
1125        Ok(self
1126            .conn
1127            .query_row("SELECT count(*) FROM recordings", [], |r| r.get(0))?)
1128    }
1129
1130    pub fn recording(&self, id: &str) -> Result<Recording> {
1131        let row = self
1132            .conn
1133            .query_row(
1134                "SELECT json,recovery_pending FROM recordings WHERE id=?1",
1135                [id],
1136                |r| Ok((r.get(0)?, r.get(1)?)),
1137            )
1138            .optional()?
1139            .context(DomainError::new(ErrorCode::RecordingNotFound))?;
1140        Self::read_recording(row)
1141    }
1142
1143    pub fn remove_recording_index(&self, id: &str) -> Result<()> {
1144        self.conn
1145            .execute("DELETE FROM recordings WHERE id=?1", [id])?;
1146        Ok(())
1147    }
1148
1149    pub fn recordings_page(&self, limit: usize, offset: usize) -> Result<Vec<Recording>> {
1150        let mut stmt = self.conn.prepare_cached(&format!("SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT ?1 OFFSET ?2"))?;
1151        let rows = stmt.query_map(
1152            params![
1153                limit.clamp(1, 1000) as i64,
1154                offset.min(i64::MAX as usize) as i64
1155            ],
1156            |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)),
1157        )?;
1158        rows.map(|row| Self::read_recording(row?)).collect()
1159    }
1160
1161    fn read_recording((json, recovery_pending): (String, bool)) -> Result<Recording> {
1162        let mut record: Recording = serde_json::from_str(&json)?;
1163        record.recovery_pending = recovery_pending;
1164        Ok(record)
1165    }
1166
1167    pub fn unfinished_recordings(&self) -> Result<Vec<Recording>> {
1168        let mut stmt = self
1169            .conn
1170            .prepare("SELECT json,recovery_pending FROM recordings WHERE status IN ('recording','finalizing') OR recovery_pending=1")?;
1171        let rows = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)))?;
1172        rows.map(|row| Self::read_recording(row?)).collect()
1173    }
1174}
1175
1176#[cfg(test)]
1177mod tests {
1178    use super::*;
1179
1180    #[test]
1181    fn recording_order_index_migrates_without_losing_old_or_malformed_entries() {
1182        let dir = tempfile::tempdir().unwrap();
1183        let path = dir.path().join("radio.db");
1184        let store = Store::open(&path).unwrap();
1185        for (id, at) in [
1186            ("a", "2026-01-02"),
1187            ("b", "2026-01-02"),
1188            ("old", "2026-01-01"),
1189        ] {
1190            store
1191                .save_recording(&Recording {
1192                    id: id.into(),
1193                    stationuuid: "station".into(),
1194                    station_name: "Station".into(),
1195                    track: None,
1196                    source_url_resolved: "http://localhost/live".into(),
1197                    started_at: at.into(),
1198                    stopped_at: None,
1199                    media_duration_ms: Some(1000),
1200                    codec: "mp3".into(),
1201                    container: "mp3".into(),
1202                    path: id.into(),
1203                    final_path: None,
1204                    size_bytes: 1,
1205                    status: "complete".into(),
1206                    error: None,
1207                    reconnects: 0,
1208                    recovery_pending: false,
1209                })
1210                .unwrap();
1211        }
1212        store
1213            .conn
1214            .execute_batch(
1215                "DROP INDEX recording_started; PRAGMA user_version=7;
1216            INSERT INTO recordings(id,status,json) VALUES ('broken','complete','invalid');",
1217            )
1218            .unwrap();
1219        drop(store);
1220        let store = Store::open(&path).unwrap();
1221        assert_eq!(store.recordings_count().unwrap(), 4);
1222        let page = store.recordings_page(2, 0).unwrap();
1223        assert_eq!(
1224            page.iter().map(|r| r.id.as_str()).collect::<Vec<_>>(),
1225            ["b", "a"]
1226        );
1227        assert_eq!(store.recordings_page(1, 2).unwrap()[0].id, "old");
1228        assert!(store.recordings_page(1, 3).is_err());
1229        let plan: Vec<String> = store.conn.prepare(&format!(
1230            "EXPLAIN QUERY PLAN SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT 100"
1231        )).unwrap().query_map([], |r| r.get(3)).unwrap().collect::<rusqlite::Result<_>>().unwrap();
1232        assert!(plan.iter().any(|line| line.contains("recording_started")));
1233        assert!(!plan.iter().any(|line| line.contains("TEMP B-TREE")));
1234    }
1235
1236    #[test]
1237    fn territory_queries_union_indexed_country_and_trigram_candidates() {
1238        let dir = tempfile::tempdir().unwrap();
1239        let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1240        let rows: Vec<_> = (0..100)
1241            .map(|i| Station {
1242                stationuuid: uuid::Uuid::new_v4().to_string(),
1243                name: match i {
1244                    1 => "爱尔兰音乐".into(),
1245                    2 => "爱尔兰广播".into(),
1246                    _ => format!("catalog station {i}"),
1247                },
1248                countrycode: if i == 0 || i == 2 { "IE" } else { "FR" }.into(),
1249                clickcount: i,
1250                active: true,
1251                ..Station::default()
1252            })
1253            .collect();
1254        store.bootstrap(&rows, || false).unwrap();
1255        let filter = Filter {
1256            query: " 爱尔兰 ".into(),
1257            limit: 100,
1258            ..Filter::default()
1259        };
1260        let actual: Vec<_> = store
1261            .search(&filter)
1262            .unwrap()
1263            .into_iter()
1264            .map(|row| row.stationuuid)
1265            .collect();
1266        assert_eq!(actual, [2, 1, 0].map(|i| rows[i].stationuuid.clone()));
1267        assert_eq!(store.search_count(&filter).unwrap(), 3);
1268        let (conditions, args, source) = store.conditions(&filter);
1269        let plan: Vec<String> = store
1270            .conn
1271            .prepare(&format!(
1272                "EXPLAIN QUERY PLAN SELECT s.rowid FROM {source} WHERE {conditions}"
1273            ))
1274            .unwrap()
1275            .query_map(params_from_iter(args), |r| r.get(3))
1276            .unwrap()
1277            .collect::<rusqlite::Result<_>>()
1278            .unwrap();
1279        for index in [
1280            "SEARCH s USING INTEGER PRIMARY KEY (rowid=?)",
1281            "station_search VIRTUAL TABLE INDEX",
1282            "station_country (country=?)",
1283        ] {
1284            assert!(plan.iter().any(|step| step.contains(index)), "{plan:?}");
1285        }
1286    }
1287
1288    #[test]
1289    fn search_statistics_collection_allows_a_concurrent_writer() {
1290        use std::sync::{
1291            Arc,
1292            atomic::{AtomicBool, Ordering},
1293        };
1294
1295        let dir = tempfile::tempdir().unwrap();
1296        let path = dir.path().join("radio.db");
1297        let mut store = Store::open(&path).unwrap();
1298        let rows: Vec<_> = (0..256)
1299            .map(|i| Station {
1300                stationuuid: uuid::Uuid::new_v4().to_string(),
1301                name: format!("station {i:05} jazz"),
1302                active: true,
1303                ..Station::default()
1304            })
1305            .collect();
1306        store.bootstrap(&rows, || false).unwrap();
1307        let writer = Connection::open(&path).unwrap();
1308        writer.busy_timeout(std::time::Duration::ZERO).unwrap();
1309        let attempted = Arc::new(AtomicBool::new(false));
1310        let succeeded = Arc::new(AtomicBool::new(false));
1311        let (attempt, success) = (attempted.clone(), succeeded.clone());
1312        store.conn.progress_handler(
1313            1000,
1314            Some(move || {
1315                if !attempt.swap(true, Ordering::SeqCst) {
1316                    success.store(
1317                        writer
1318                            .execute("INSERT INTO meta VALUES ('concurrent','write')", [])
1319                            .is_ok(),
1320                        Ordering::SeqCst,
1321                    );
1322                }
1323                false
1324            }),
1325        );
1326        store.refresh_search_stats().unwrap();
1327        store.conn.progress_handler(0, None::<fn() -> bool>);
1328        assert!(attempted.load(Ordering::SeqCst));
1329        assert!(succeeded.load(Ordering::SeqCst));
1330        let actual: usize = store
1331            .conn
1332            .query_row(
1333                "SELECT n FROM station_search_stats WHERE term=''",
1334                [],
1335                |r| r.get(0),
1336            )
1337            .unwrap();
1338        assert_eq!(actual, rows.len());
1339        let differences: usize = store
1340            .conn
1341            .query_row(
1342                "SELECT count(*) FROM (
1343                    SELECT term,doc FROM station_search_terms
1344                    EXCEPT SELECT term,n FROM station_search_stats WHERE term<>'')",
1345                [],
1346                |r| r.get(0),
1347            )
1348            .unwrap();
1349        assert_eq!(differences, 0);
1350    }
1351
1352    #[test]
1353    fn failed_statistics_refresh_preserves_hints_and_successful_publication() {
1354        for sync in [false, true] {
1355            let dir = tempfile::tempdir().unwrap();
1356            let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1357            let row = |name: &str| Station {
1358                stationuuid: uuid::Uuid::new_v4().to_string(),
1359                name: name.into(),
1360                tags: name.into(),
1361                active: true,
1362                ..Station::default()
1363            };
1364            store.bootstrap(&[row("originalneedle")], || false).unwrap();
1365            let stats = || {
1366                store
1367                    .conn
1368                    .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1369                    .unwrap()
1370                    .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1371                    .unwrap()
1372                    .collect::<rusqlite::Result<Vec<_>>>()
1373                    .unwrap()
1374            };
1375            let before = stats();
1376            store
1377                .conn
1378                .execute_batch(
1379                    "CREATE TRIGGER reject_stats BEFORE INSERT ON station_search_stats
1380                BEGIN SELECT RAISE(ABORT,'statistics unavailable'); END;",
1381                )
1382                .unwrap();
1383            let replacement = row("replacementneedle");
1384            if sync {
1385                store.begin_sync().unwrap();
1386                store.stage(&[replacement]).unwrap();
1387                assert_eq!(store.finish_sync().unwrap(), 1);
1388                assert!(store.meta("last_sync").unwrap().is_some());
1389            } else {
1390                assert_eq!(store.bootstrap(&[replacement], || false).unwrap(), 1);
1391            }
1392            assert_eq!(
1393                store
1394                    .search_count(&Filter {
1395                        query: "replacementneedle".into(),
1396                        ..Filter::default()
1397                    })
1398                    .unwrap(),
1399                1
1400            );
1401            assert_eq!(
1402                store
1403                    .facets(crate::model::FacetKind::Tag, "replacement")
1404                    .unwrap()[0]
1405                    .count,
1406                1
1407            );
1408            let after: Vec<_> = store
1409                .conn
1410                .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1411                .unwrap()
1412                .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1413                .unwrap()
1414                .collect::<rusqlite::Result<_>>()
1415                .unwrap();
1416            assert_eq!(after, before);
1417        }
1418    }
1419
1420    #[test]
1421    fn read_snapshot_pins_revision_before_a_concurrent_commit() {
1422        let dir = tempfile::tempdir().unwrap();
1423        let path = dir.path().join("radio.db");
1424        let writer = Store::open(&path).unwrap();
1425        let reader = Store::open_reader(&path).unwrap();
1426        let revision = reader
1427            .read_snapshot(|store| {
1428                let revision = store.list_revision(&Filter::default())?;
1429                writer.upsert(&Station {
1430                    stationuuid: uuid::Uuid::new_v4().to_string(),
1431                    name: "new row".into(),
1432                    active: true,
1433                    ..Station::default()
1434                })?;
1435                assert_eq!(store.list_revision(&Filter::default())?, revision);
1436                assert_eq!(store.count()?, 0);
1437                Ok(revision)
1438            })
1439            .unwrap();
1440        assert_ne!(reader.list_revision(&Filter::default()).unwrap(), revision);
1441        assert_eq!(reader.count().unwrap(), 1);
1442    }
1443
1444    #[test]
1445    fn cancellation_before_sync_commit_rolls_back_all_derived_data() {
1446        let dir = tempfile::tempdir().unwrap();
1447        let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1448        let row = |name: &str, tag: &str| Station {
1449            stationuuid: uuid::Uuid::new_v4().to_string(),
1450            name: name.into(),
1451            tags: tag.into(),
1452            active: true,
1453            ..Station::default()
1454        };
1455        store.upsert(&row("originalneedle", "originaltag")).unwrap();
1456        store.begin_sync().unwrap();
1457        store
1458            .stage(&[row("replacementneedle", "replacementtag")])
1459            .unwrap();
1460        assert!(store.finish_sync_cancellable(|| true).is_err());
1461        assert_eq!(store.count().unwrap(), 1);
1462        for (query, expected) in [("originalneedle", 1), ("replacementneedle", 0)] {
1463            assert_eq!(
1464                store
1465                    .search_count(&Filter {
1466                        query: query.into(),
1467                        ..Filter::default()
1468                    })
1469                    .unwrap(),
1470                expected
1471            );
1472        }
1473        assert_eq!(
1474            store.facets(crate::model::FacetKind::Tag, "").unwrap()[0].value,
1475            "originaltag"
1476        );
1477        assert!(store.meta("last_sync").unwrap().is_none());
1478    }
1479
1480    #[test]
1481    fn sync_writes_only_changed_stations_and_facet_memberships() {
1482        let dir = tempfile::tempdir().unwrap();
1483        let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1484        let row = |name: &str| Station {
1485            stationuuid: uuid::Uuid::new_v4().to_string(),
1486            name: name.into(),
1487            tags: "jazz,jazz".into(),
1488            language: "english,en".into(),
1489            countrycode: "US".into(),
1490            active: true,
1491            ..Station::default()
1492        };
1493        let unchanged = row("unchanged");
1494        let mut changed = row("changed");
1495        let removed = row("removed");
1496        let mut revived = row("revived");
1497        revived.active = false;
1498        let mut custom = row("custom");
1499        custom.custom = true;
1500        for station in [&unchanged, &changed, &removed, &revived, &custom] {
1501            store.upsert(station).unwrap();
1502        }
1503        store
1504            .conn
1505            .execute_batch(
1506                "CREATE TABLE station_writes(uuid TEXT);
1507             CREATE TRIGGER audit_station_writes AFTER UPDATE ON stations
1508             BEGIN INSERT INTO station_writes VALUES(new.uuid); END;",
1509            )
1510            .unwrap();
1511        changed.tags = "rock,rock".into();
1512        changed.countrycode = "FR".into();
1513        revived.active = true;
1514        let mut collision = custom.clone();
1515        collision.custom = false;
1516        collision.tags = "wrong".into();
1517        let incoming = [unchanged.clone(), changed, revived, collision, row("new")];
1518        store.begin_sync().unwrap();
1519        store.stage(&incoming).unwrap();
1520        store.finish_sync().unwrap();
1521        let writes: Vec<String> = store
1522            .conn
1523            .prepare("SELECT uuid FROM station_writes")
1524            .unwrap()
1525            .query_map([], |r| r.get(0))
1526            .unwrap()
1527            .collect::<rusqlite::Result<_>>()
1528            .unwrap();
1529        assert_eq!(writes.len(), 3);
1530        assert!(!writes.contains(&unchanged.stationuuid));
1531        assert!(!writes.contains(&custom.stationuuid));
1532        assert!(!store.station(&removed.stationuuid).unwrap().active);
1533        assert_eq!(
1534            store.station(&custom.stationuuid).unwrap().tags,
1535            "jazz,jazz"
1536        );
1537        let facets = || {
1538            store
1539                .conn
1540                .prepare("SELECT kind,value,n FROM facets_cache ORDER BY kind,value")
1541                .unwrap()
1542                .query_map([], |r| {
1543                    Ok((
1544                        r.get::<_, u8>(0)?,
1545                        r.get::<_, String>(1)?,
1546                        r.get::<_, i64>(2)?,
1547                    ))
1548                })
1549                .unwrap()
1550                .collect::<rusqlite::Result<Vec<_>>>()
1551                .unwrap()
1552        };
1553        let delta_counts = facets();
1554        facets::rebuild(&store.conn).unwrap();
1555        assert_eq!(facets(), delta_counts);
1556        let geo_revision = store.geo_revision().unwrap();
1557        store.begin_sync().unwrap();
1558        store.stage(&incoming).unwrap();
1559        let before = store.conn.total_changes();
1560        store.finish_sync().unwrap();
1561        // Only last_sync changes: stations, indexes, facets, geographic and
1562        // search statistics remain untouched for an identical snapshot.
1563        assert_eq!(store.conn.total_changes() - before, 1);
1564        assert_eq!(store.geo_revision().unwrap(), geo_revision);
1565    }
1566}