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