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