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 mut stmt = self.conn.prepare_cached(
918            "SELECT value,n FROM facets_cache WHERE kind=?1 AND substr(value,1,length(?2))=?2 ORDER BY value LIMIT ?3",
919        )?;
920        let rows = stmt.query_map(
921            params![kind, prefix, limit.min(i64::MAX as usize) as i64],
922            |r| {
923                Ok(Facet {
924                    value: r.get(0)?,
925                    count: r.get(1)?,
926                })
927            },
928        )?;
929        Ok(rows.collect::<rusqlite::Result<_>>()?)
930    }
931
932    pub fn favorite(&self, uuid: &str, add: bool) -> Result<()> {
933        self.station(uuid)?;
934        if add {
935            self.conn.execute(
936                "INSERT OR IGNORE INTO favorites(uuid,created_at) VALUES (?1,?2)",
937                params![uuid, chrono::Utc::now().to_rfc3339()],
938            )?;
939        } else {
940            self.conn
941                .execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
942        }
943        Ok(())
944    }
945
946    pub fn toggle_favorite(&mut self, uuid: &str) -> Result<bool> {
947        let tx = self
948            .conn
949            .transaction_with_behavior(TransactionBehavior::Immediate)?;
950        tx.query_row("SELECT 1 FROM stations WHERE uuid=?1", [uuid], |r| {
951            r.get::<_, u8>(0)
952        })
953        .optional()?
954        .context(DomainError::new(ErrorCode::StationNotFound))?;
955        let removed = tx.execute("DELETE FROM favorites WHERE uuid=?1", [uuid])?;
956        let added = removed == 0;
957        if added {
958            tx.execute(
959                "INSERT INTO favorites(uuid,created_at) VALUES (?1,?2)",
960                params![uuid, chrono::Utc::now().to_rfc3339()],
961            )?;
962        }
963        tx.commit()?;
964        Ok(added)
965    }
966
967    pub fn played(&self, uuid: &str) -> Result<()> {
968        self.conn.execute(
969            "INSERT INTO history(uuid,played_at) VALUES (?1,?2)",
970            params![uuid, chrono::Utc::now().to_rfc3339()],
971        )?;
972        let threshold = self.conn.last_insert_rowid().saturating_sub(1000);
973        self.conn
974            .execute("DELETE FROM history WHERE id <= ?1", [threshold])?;
975        Ok(())
976    }
977
978    pub fn add_backup(&self, uuid: &str, url: &str) -> Result<()> {
979        crate::config::validate_url(url)?;
980        self.conn.execute(
981            "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
982            params![uuid, url],
983        )?;
984        Ok(())
985    }
986
987    pub fn save_custom(&mut self, station: &Station, backups: Option<&[String]>) -> Result<()> {
988        let tx = self
989            .conn
990            .transaction_with_behavior(TransactionBehavior::Immediate)?;
991        let key = crate::service::normalized_url(station.primary_url())?;
992        if let Some(uuid) = tx.query_row(
993            "SELECT uuid FROM stations WHERE active=1 AND normalized_url=?1 AND uuid<>?2 LIMIT 1",
994            params![key, station.stationuuid], |r| r.get::<_, String>(0),
995        ).optional()? {
996            bail!(DomainError::new(ErrorCode::DuplicateStation).with("uuid", uuid));
997        }
998        Self::write_station_with_policy(&tx, "stations", station, true, true)?;
999        if let Some(backups) = backups {
1000            tx.execute("DELETE FROM streams WHERE uuid=?1", [&station.stationuuid])?;
1001            for url in backups {
1002                crate::config::validate_url(url)?;
1003                tx.execute(
1004                    "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1005                    params![station.stationuuid, url],
1006                )?;
1007            }
1008        }
1009        geo::changed(&tx)?;
1010        tx.commit()?;
1011        Ok(())
1012    }
1013
1014    pub fn remove_custom(&mut self, uuid: &str, keep_references: bool) -> Result<bool> {
1015        let tx = self
1016            .conn
1017            .transaction_with_behavior(TransactionBehavior::Immediate)?;
1018        let json: String = tx
1019            .query_row(
1020                "SELECT json FROM stations WHERE uuid=?1 AND custom=1",
1021                [uuid],
1022                |r| r.get(0),
1023            )
1024            .optional()?
1025            .context(DomainError::new(ErrorCode::NotCustom))?;
1026        let mut station: Station = serde_json::from_str(&json)?;
1027        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))?;
1028        tx.execute("DELETE FROM streams WHERE uuid=?1", [uuid])?;
1029        if referenced || keep_references {
1030            station.custom = false;
1031            station.active = false;
1032            station.url.clear();
1033            station.url_resolved.clear();
1034            Self::write_station_with_policy(&tx, "stations", &station, true, true)?;
1035        } else {
1036            let before = facets::station_values(&tx, uuid)?;
1037            tx.execute("DELETE FROM stations WHERE uuid=?1", [uuid])?;
1038            facets::update(&tx, &before, &Default::default())?;
1039        }
1040        geo::changed(&tx)?;
1041        tx.commit()?;
1042        Ok(referenced || keep_references)
1043    }
1044
1045    pub fn update_backups(
1046        &mut self,
1047        uuid: &str,
1048        add: &[String],
1049        remove: &[String],
1050    ) -> Result<Vec<String>> {
1051        self.station(uuid)?;
1052        let tx = self.conn.transaction()?;
1053        for url in remove {
1054            tx.execute(
1055                "DELETE FROM streams WHERE uuid=?1 AND url=?2",
1056                params![uuid, url],
1057            )?;
1058        }
1059        for url in add {
1060            crate::config::validate_url(url)?;
1061            tx.execute(
1062                "INSERT OR IGNORE INTO streams(uuid,url) VALUES (?1,?2)",
1063                params![uuid, url],
1064            )?;
1065        }
1066        tx.commit()?;
1067        self.backups(uuid)
1068    }
1069
1070    pub fn backups(&self, uuid: &str) -> Result<Vec<String>> {
1071        let mut stmt = self
1072            .conn
1073            .prepare("SELECT url FROM streams WHERE uuid=?1 ORDER BY rowid")?;
1074        Ok(stmt
1075            .query_map([uuid], |r| r.get(0))?
1076            .collect::<rusqlite::Result<_>>()?)
1077    }
1078
1079    pub fn save_recording(&self, recording: &Recording) -> Result<()> {
1080        self.conn.execute(
1081            "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",
1082            params![
1083                recording.id,
1084                recording.status,
1085                serde_json::to_string(recording)?,
1086                recording.recovery_pending
1087            ],
1088        )?;
1089        Ok(())
1090    }
1091
1092    pub fn search_count(&self, filter: &Filter) -> Result<usize> {
1093        let (conditions, args, source) = self.conditions(filter);
1094        Ok(self
1095            .conn
1096            .prepare_cached(&format!("SELECT count(*) FROM {source} WHERE {conditions}"))?
1097            .query_row(params_from_iter(args), |r| r.get(0))?)
1098    }
1099
1100    pub fn recordings(&self, limit: usize) -> Result<Vec<Recording>> {
1101        self.recordings_page(limit, 0)
1102    }
1103
1104    pub fn recordings_count(&self) -> Result<usize> {
1105        Ok(self
1106            .conn
1107            .query_row("SELECT count(*) FROM recordings", [], |r| r.get(0))?)
1108    }
1109
1110    pub fn recording(&self, id: &str) -> Result<Recording> {
1111        let row = self
1112            .conn
1113            .query_row(
1114                "SELECT json,recovery_pending FROM recordings WHERE id=?1",
1115                [id],
1116                |r| Ok((r.get(0)?, r.get(1)?)),
1117            )
1118            .optional()?
1119            .context(DomainError::new(ErrorCode::RecordingNotFound))?;
1120        Self::read_recording(row)
1121    }
1122
1123    pub fn remove_recording_index(&self, id: &str) -> Result<()> {
1124        self.conn
1125            .execute("DELETE FROM recordings WHERE id=?1", [id])?;
1126        Ok(())
1127    }
1128
1129    pub fn recordings_page(&self, limit: usize, offset: usize) -> Result<Vec<Recording>> {
1130        let mut stmt = self.conn.prepare_cached(&format!("SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT ?1 OFFSET ?2"))?;
1131        let rows = stmt.query_map(
1132            params![
1133                limit.clamp(1, 1000) as i64,
1134                offset.min(i64::MAX as usize) as i64
1135            ],
1136            |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)),
1137        )?;
1138        rows.map(|row| Self::read_recording(row?)).collect()
1139    }
1140
1141    fn read_recording((json, recovery_pending): (String, bool)) -> Result<Recording> {
1142        let mut record: Recording = serde_json::from_str(&json)?;
1143        record.recovery_pending = recovery_pending;
1144        Ok(record)
1145    }
1146
1147    pub fn unfinished_recordings(&self) -> Result<Vec<Recording>> {
1148        let mut stmt = self
1149            .conn
1150            .prepare("SELECT json,recovery_pending FROM recordings WHERE status IN ('recording','finalizing') OR recovery_pending=1")?;
1151        let rows = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, bool>(1)?)))?;
1152        rows.map(|row| Self::read_recording(row?)).collect()
1153    }
1154}
1155
1156#[cfg(test)]
1157mod tests {
1158    use super::*;
1159
1160    #[test]
1161    fn recording_order_index_migrates_without_losing_old_or_malformed_entries() {
1162        let dir = tempfile::tempdir().unwrap();
1163        let path = dir.path().join("radio.db");
1164        let store = Store::open(&path).unwrap();
1165        for (id, at) in [
1166            ("a", "2026-01-02"),
1167            ("b", "2026-01-02"),
1168            ("old", "2026-01-01"),
1169        ] {
1170            store
1171                .save_recording(&Recording {
1172                    id: id.into(),
1173                    stationuuid: "station".into(),
1174                    station_name: "Station".into(),
1175                    track: None,
1176                    source_url_resolved: "http://localhost/live".into(),
1177                    started_at: at.into(),
1178                    stopped_at: None,
1179                    media_duration_ms: Some(1000),
1180                    codec: "mp3".into(),
1181                    container: "mp3".into(),
1182                    path: id.into(),
1183                    final_path: None,
1184                    size_bytes: 1,
1185                    status: "complete".into(),
1186                    error: None,
1187                    reconnects: 0,
1188                    recovery_pending: false,
1189                })
1190                .unwrap();
1191        }
1192        store
1193            .conn
1194            .execute_batch(
1195                "DROP INDEX recording_started; PRAGMA user_version=7;
1196            INSERT INTO recordings(id,status,json) VALUES ('broken','complete','invalid');",
1197            )
1198            .unwrap();
1199        drop(store);
1200        let store = Store::open(&path).unwrap();
1201        assert_eq!(store.recordings_count().unwrap(), 4);
1202        let page = store.recordings_page(2, 0).unwrap();
1203        assert_eq!(
1204            page.iter().map(|r| r.id.as_str()).collect::<Vec<_>>(),
1205            ["b", "a"]
1206        );
1207        assert_eq!(store.recordings_page(1, 2).unwrap()[0].id, "old");
1208        assert!(store.recordings_page(1, 3).is_err());
1209        let plan: Vec<String> = store.conn.prepare(&format!(
1210            "EXPLAIN QUERY PLAN SELECT json,recovery_pending FROM recordings ORDER BY {RECORDING_ORDER} LIMIT 100"
1211        )).unwrap().query_map([], |r| r.get(3)).unwrap().collect::<rusqlite::Result<_>>().unwrap();
1212        assert!(plan.iter().any(|line| line.contains("recording_started")));
1213        assert!(!plan.iter().any(|line| line.contains("TEMP B-TREE")));
1214    }
1215
1216    #[test]
1217    fn territory_queries_union_indexed_country_and_trigram_candidates() {
1218        let dir = tempfile::tempdir().unwrap();
1219        let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1220        let rows: Vec<_> = (0..100)
1221            .map(|i| Station {
1222                stationuuid: uuid::Uuid::new_v4().to_string(),
1223                name: match i {
1224                    1 => "爱尔兰音乐".into(),
1225                    2 => "爱尔兰广播".into(),
1226                    _ => format!("catalog station {i}"),
1227                },
1228                countrycode: if i == 0 || i == 2 { "IE" } else { "FR" }.into(),
1229                clickcount: i,
1230                active: true,
1231                ..Station::default()
1232            })
1233            .collect();
1234        store.bootstrap(&rows, || false).unwrap();
1235        let filter = Filter {
1236            query: " 爱尔兰 ".into(),
1237            limit: 100,
1238            ..Filter::default()
1239        };
1240        let actual: Vec<_> = store
1241            .search(&filter)
1242            .unwrap()
1243            .into_iter()
1244            .map(|row| row.stationuuid)
1245            .collect();
1246        assert_eq!(actual, [2, 1, 0].map(|i| rows[i].stationuuid.clone()));
1247        assert_eq!(store.search_count(&filter).unwrap(), 3);
1248        let (conditions, args, source) = store.conditions(&filter);
1249        let plan: Vec<String> = store
1250            .conn
1251            .prepare(&format!(
1252                "EXPLAIN QUERY PLAN SELECT s.rowid FROM {source} WHERE {conditions}"
1253            ))
1254            .unwrap()
1255            .query_map(params_from_iter(args), |r| r.get(3))
1256            .unwrap()
1257            .collect::<rusqlite::Result<_>>()
1258            .unwrap();
1259        for index in [
1260            "SEARCH s USING INTEGER PRIMARY KEY (rowid=?)",
1261            "station_search VIRTUAL TABLE INDEX",
1262            "station_country (country=?)",
1263        ] {
1264            assert!(plan.iter().any(|step| step.contains(index)), "{plan:?}");
1265        }
1266    }
1267
1268    #[test]
1269    fn search_statistics_collection_allows_a_concurrent_writer() {
1270        use std::sync::{
1271            Arc,
1272            atomic::{AtomicBool, Ordering},
1273        };
1274
1275        let dir = tempfile::tempdir().unwrap();
1276        let path = dir.path().join("radio.db");
1277        let mut store = Store::open(&path).unwrap();
1278        let rows: Vec<_> = (0..256)
1279            .map(|i| Station {
1280                stationuuid: uuid::Uuid::new_v4().to_string(),
1281                name: format!("station {i:05} jazz"),
1282                active: true,
1283                ..Station::default()
1284            })
1285            .collect();
1286        store.bootstrap(&rows, || false).unwrap();
1287        let writer = Connection::open(&path).unwrap();
1288        writer.busy_timeout(std::time::Duration::ZERO).unwrap();
1289        let attempted = Arc::new(AtomicBool::new(false));
1290        let succeeded = Arc::new(AtomicBool::new(false));
1291        let (attempt, success) = (attempted.clone(), succeeded.clone());
1292        store.conn.progress_handler(
1293            1000,
1294            Some(move || {
1295                if !attempt.swap(true, Ordering::SeqCst) {
1296                    success.store(
1297                        writer
1298                            .execute("INSERT INTO meta VALUES ('concurrent','write')", [])
1299                            .is_ok(),
1300                        Ordering::SeqCst,
1301                    );
1302                }
1303                false
1304            }),
1305        );
1306        store.refresh_search_stats().unwrap();
1307        store.conn.progress_handler(0, None::<fn() -> bool>);
1308        assert!(attempted.load(Ordering::SeqCst));
1309        assert!(succeeded.load(Ordering::SeqCst));
1310        let actual: usize = store
1311            .conn
1312            .query_row(
1313                "SELECT n FROM station_search_stats WHERE term=''",
1314                [],
1315                |r| r.get(0),
1316            )
1317            .unwrap();
1318        assert_eq!(actual, rows.len());
1319        let differences: usize = store
1320            .conn
1321            .query_row(
1322                "SELECT count(*) FROM (
1323                    SELECT term,doc FROM station_search_terms
1324                    EXCEPT SELECT term,n FROM station_search_stats WHERE term<>'')",
1325                [],
1326                |r| r.get(0),
1327            )
1328            .unwrap();
1329        assert_eq!(differences, 0);
1330    }
1331
1332    #[test]
1333    fn failed_statistics_refresh_preserves_hints_and_successful_publication() {
1334        for sync in [false, true] {
1335            let dir = tempfile::tempdir().unwrap();
1336            let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1337            let row = |name: &str| Station {
1338                stationuuid: uuid::Uuid::new_v4().to_string(),
1339                name: name.into(),
1340                tags: name.into(),
1341                active: true,
1342                ..Station::default()
1343            };
1344            store.bootstrap(&[row("originalneedle")], || false).unwrap();
1345            let stats = || {
1346                store
1347                    .conn
1348                    .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1349                    .unwrap()
1350                    .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1351                    .unwrap()
1352                    .collect::<rusqlite::Result<Vec<_>>>()
1353                    .unwrap()
1354            };
1355            let before = stats();
1356            store
1357                .conn
1358                .execute_batch(
1359                    "CREATE TRIGGER reject_stats BEFORE INSERT ON station_search_stats
1360                BEGIN SELECT RAISE(ABORT,'statistics unavailable'); END;",
1361                )
1362                .unwrap();
1363            let replacement = row("replacementneedle");
1364            if sync {
1365                store.begin_sync().unwrap();
1366                store.stage(&[replacement]).unwrap();
1367                assert_eq!(store.finish_sync().unwrap(), 1);
1368                assert!(store.meta("last_sync").unwrap().is_some());
1369            } else {
1370                assert_eq!(store.bootstrap(&[replacement], || false).unwrap(), 1);
1371            }
1372            assert_eq!(
1373                store
1374                    .search_count(&Filter {
1375                        query: "replacementneedle".into(),
1376                        ..Filter::default()
1377                    })
1378                    .unwrap(),
1379                1
1380            );
1381            assert_eq!(
1382                store
1383                    .facets(crate::model::FacetKind::Tag, "replacement")
1384                    .unwrap()[0]
1385                    .count,
1386                1
1387            );
1388            let after: Vec<_> = store
1389                .conn
1390                .prepare("SELECT term,n FROM station_search_stats ORDER BY term")
1391                .unwrap()
1392                .query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
1393                .unwrap()
1394                .collect::<rusqlite::Result<_>>()
1395                .unwrap();
1396            assert_eq!(after, before);
1397        }
1398    }
1399
1400    #[test]
1401    fn read_snapshot_pins_revision_before_a_concurrent_commit() {
1402        let dir = tempfile::tempdir().unwrap();
1403        let path = dir.path().join("radio.db");
1404        let writer = Store::open(&path).unwrap();
1405        let reader = Store::open_reader(&path).unwrap();
1406        let revision = reader
1407            .read_snapshot(|store| {
1408                let revision = store.list_revision(&Filter::default())?;
1409                writer.upsert(&Station {
1410                    stationuuid: uuid::Uuid::new_v4().to_string(),
1411                    name: "new row".into(),
1412                    active: true,
1413                    ..Station::default()
1414                })?;
1415                assert_eq!(store.list_revision(&Filter::default())?, revision);
1416                assert_eq!(store.count()?, 0);
1417                Ok(revision)
1418            })
1419            .unwrap();
1420        assert_ne!(reader.list_revision(&Filter::default()).unwrap(), revision);
1421        assert_eq!(reader.count().unwrap(), 1);
1422    }
1423
1424    #[test]
1425    fn cancellation_before_sync_commit_rolls_back_all_derived_data() {
1426        let dir = tempfile::tempdir().unwrap();
1427        let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1428        let row = |name: &str, tag: &str| Station {
1429            stationuuid: uuid::Uuid::new_v4().to_string(),
1430            name: name.into(),
1431            tags: tag.into(),
1432            active: true,
1433            ..Station::default()
1434        };
1435        store.upsert(&row("originalneedle", "originaltag")).unwrap();
1436        store.begin_sync().unwrap();
1437        store
1438            .stage(&[row("replacementneedle", "replacementtag")])
1439            .unwrap();
1440        assert!(store.finish_sync_cancellable(|| true).is_err());
1441        assert_eq!(store.count().unwrap(), 1);
1442        for (query, expected) in [("originalneedle", 1), ("replacementneedle", 0)] {
1443            assert_eq!(
1444                store
1445                    .search_count(&Filter {
1446                        query: query.into(),
1447                        ..Filter::default()
1448                    })
1449                    .unwrap(),
1450                expected
1451            );
1452        }
1453        assert_eq!(
1454            store.facets(crate::model::FacetKind::Tag, "").unwrap()[0].value,
1455            "originaltag"
1456        );
1457        assert!(store.meta("last_sync").unwrap().is_none());
1458    }
1459
1460    #[test]
1461    fn sync_writes_only_changed_stations_and_facet_memberships() {
1462        let dir = tempfile::tempdir().unwrap();
1463        let mut store = Store::open(&dir.path().join("radio.db")).unwrap();
1464        let row = |name: &str| Station {
1465            stationuuid: uuid::Uuid::new_v4().to_string(),
1466            name: name.into(),
1467            tags: "jazz,jazz".into(),
1468            language: "english,en".into(),
1469            countrycode: "US".into(),
1470            active: true,
1471            ..Station::default()
1472        };
1473        let unchanged = row("unchanged");
1474        let mut changed = row("changed");
1475        let removed = row("removed");
1476        let mut revived = row("revived");
1477        revived.active = false;
1478        let mut custom = row("custom");
1479        custom.custom = true;
1480        for station in [&unchanged, &changed, &removed, &revived, &custom] {
1481            store.upsert(station).unwrap();
1482        }
1483        store
1484            .conn
1485            .execute_batch(
1486                "CREATE TABLE station_writes(uuid TEXT);
1487             CREATE TRIGGER audit_station_writes AFTER UPDATE ON stations
1488             BEGIN INSERT INTO station_writes VALUES(new.uuid); END;",
1489            )
1490            .unwrap();
1491        changed.tags = "rock,rock".into();
1492        changed.countrycode = "FR".into();
1493        revived.active = true;
1494        let mut collision = custom.clone();
1495        collision.custom = false;
1496        collision.tags = "wrong".into();
1497        let incoming = [unchanged.clone(), changed, revived, collision, row("new")];
1498        store.begin_sync().unwrap();
1499        store.stage(&incoming).unwrap();
1500        store.finish_sync().unwrap();
1501        let writes: Vec<String> = store
1502            .conn
1503            .prepare("SELECT uuid FROM station_writes")
1504            .unwrap()
1505            .query_map([], |r| r.get(0))
1506            .unwrap()
1507            .collect::<rusqlite::Result<_>>()
1508            .unwrap();
1509        assert_eq!(writes.len(), 3);
1510        assert!(!writes.contains(&unchanged.stationuuid));
1511        assert!(!writes.contains(&custom.stationuuid));
1512        assert!(!store.station(&removed.stationuuid).unwrap().active);
1513        assert_eq!(
1514            store.station(&custom.stationuuid).unwrap().tags,
1515            "jazz,jazz"
1516        );
1517        let facets = || {
1518            store
1519                .conn
1520                .prepare("SELECT kind,value,n FROM facets_cache ORDER BY kind,value")
1521                .unwrap()
1522                .query_map([], |r| {
1523                    Ok((
1524                        r.get::<_, u8>(0)?,
1525                        r.get::<_, String>(1)?,
1526                        r.get::<_, i64>(2)?,
1527                    ))
1528                })
1529                .unwrap()
1530                .collect::<rusqlite::Result<Vec<_>>>()
1531                .unwrap()
1532        };
1533        let delta_counts = facets();
1534        facets::rebuild(&store.conn).unwrap();
1535        assert_eq!(facets(), delta_counts);
1536        let geo_revision = store.geo_revision().unwrap();
1537        store.begin_sync().unwrap();
1538        store.stage(&incoming).unwrap();
1539        let before = store.conn.total_changes();
1540        store.finish_sync().unwrap();
1541        // Only last_sync changes: stations, indexes, facets, geographic and
1542        // search statistics remain untouched for an identical snapshot.
1543        assert_eq!(store.conn.total_changes() - before, 1);
1544        assert_eq!(store.geo_revision().unwrap(), geo_revision);
1545    }
1546}