feat(yt/storage/videos): Validate in DB, that is_focused is UNIQUE

This makes the situation where two or more videos are focused impossible to represent in the db.

5 files changed, 117 insertions, 20 deletions

diff --git a/yt/src/storage/migrate/mod.rs b/yt/src/storage/migrate/mod.rs
index da6b0be..122170c 100644
--- a/yt/src/storage/migrate/mod.rs
+++ b/yt/src/storage/migrate/mod.rs
@@ -79,8 +79,11 @@ pub enum DbVersion {
 
     /// Introduced: 2025-02-18.
     Two,
+
+    /// Introduced: 2025-03-21.
+    Three,
 }
-const CURRENT_VERSION: DbVersion = DbVersion::Two;
+const CURRENT_VERSION: DbVersion = DbVersion::Three;
 
 async fn add_error_context(
     function: impl Future<Output = Result<()>>,
@@ -130,6 +133,7 @@ impl DbVersion {
             DbVersion::Zero => 0,
             DbVersion::One => 1,
             DbVersion::Two => 2,
+            DbVersion::Three => 3,
 
             DbVersion::Empty => unreachable!("A empty version does not have an associated integer"),
         }
@@ -140,10 +144,12 @@ impl DbVersion {
             (0, "yt") => Ok(DbVersion::Zero),
             (1, "yt") => Ok(DbVersion::One),
             (2, "yt") => Ok(DbVersion::Two),
+            (3, "yt") => Ok(DbVersion::Three),
 
             (0, other) => bail!("Db version is Zero, but got unknown namespace: '{other}'"),
             (1, other) => bail!("Db version is One, but got unknown namespace: '{other}'"),
             (2, other) => bail!("Db version is Two, but got unknown namespace: '{other}'"),
+            (3, other) => bail!("Db version is Three, but got unknown namespace: '{other}'"),
 
             (other, "yt") => bail!("Got unkown version for 'yt' namespace: {other}"),
             (num, nasp) => bail!("Got unkown version number ({num}) and namespace ('{nasp}')"),
@@ -169,8 +175,12 @@ impl DbVersion {
                 make_upgrade! {app, Self::One, Self::Two, "./sql/2_One_to_Two.sql"}
             }
 
-            // This is the current_version
             Self::Two => {
+                make_upgrade! {app, Self::Two, Self::Three, "./sql/3_Two_to_Three.sql"}
+            }
+
+            // This is the current_version
+            Self::Three => {
                 assert_eq!(self, CURRENT_VERSION);
                 assert_eq!(self, get_version(app).await?);
                 Ok(())
diff --git a/yt/src/storage/migrate/sql/3_Two_to_Three.sql b/yt/src/storage/migrate/sql/3_Two_to_Three.sql
new file mode 100644
index 0000000..445a9ec
--- /dev/null
+++ b/yt/src/storage/migrate/sql/3_Two_to_Three.sql
@@ -0,0 +1,64 @@
+-- yt - A fully featured command line YouTube client
+--
+-- Copyright (C) 2025 Benedikt Peetz <benedikt.peetz@b-peetz.de>
+-- SPDX-License-Identifier: GPL-3.0-or-later
+--
+-- This file is part of Yt.
+--
+-- You should have received a copy of the License along with this program.
+-- If not, see <https://www.gnu.org/licenses/gpl-3.0.txt>.
+
+
+-- 1. Create new table
+-- 2. Copy data
+-- 3. Drop old table
+-- 4. Rename new into old
+
+CREATE TABLE videos_new (
+    cache_path                  TEXT    UNIQUE                       CHECK (CASE
+                                                                              WHEN cache_path IS NOT NULL THEN status == 2
+                                                                              ELSE 1
+                                                                            END),
+    description                 TEXT,
+    duration                    REAL,
+    extractor_hash              TEXT    UNIQUE NOT NULL PRIMARY KEY,
+    last_status_change          INTEGER        NOT NULL,
+    parent_subscription_name    TEXT,
+    priority                    INTEGER        NOT NULL DEFAULT 0,
+    publish_date                INTEGER,
+    status                      INTEGER        NOT NULL DEFAULT 0    CHECK (status IN (0, 1, 2, 3, 4, 5) AND
+                                                                            CASE
+                                                                              WHEN status == 2 THEN cache_path IS NOT NULL
+                                                                              WHEN status != 2 THEN cache_path IS NULL
+                                                                              ELSE 1
+                                                                            END),
+    thumbnail_url               TEXT,
+    title                       TEXT           NOT NULL,
+    url                         TEXT    UNIQUE NOT NULL,
+    is_focused                  INTEGER UNIQUE          DEFAULT NULL CHECK (CASE
+                                                                              WHEN is_focused IS NOT NULL THEN is_focused == 1
+                                                                              ELSE 1
+                                                                            END),
+    watch_progress              INTEGER        NOT NULL DEFAULT 0    CHECK (watch_progress <= duration)
+) STRICT;
+
+INSERT INTO videos SELECT
+    videos.cache_path,
+    videos.description,
+    videos.duration,
+    videos.extractor_hash,
+    videos.last_status_change,
+    videos.parent_subscription_name,
+    videos.priority,
+    videos.publish_date,
+    videos.status,
+    videos.thumbnail_url,
+    videos.title,
+    videos.url,
+    dummy.is_focused,
+    videos.watch_progress
+FROM videos, (SELECT NULL AS is_focused) AS dummy;
+
+DROP TABLE videos;
+
+ALTER TABLE videos_new RENAME TO videos;
diff --git a/yt/src/storage/video_database/get/mod.rs b/yt/src/storage/video_database/get/mod.rs
index 6a4220e..759c048 100644
--- a/yt/src/storage/video_database/get/mod.rs
+++ b/yt/src/storage/video_database/get/mod.rs
@@ -64,7 +64,7 @@ macro_rules! video_from_record {
                 let optional = if let Some(cache_path) = &$record.cache_path {
                     Some((
                         PathBuf::from(cache_path),
-                        if $record.is_focused == 1 { true } else { false },
+                        if $record.is_focused == Some(1) { true } else { false },
                     ))
                 } else {
                     None
diff --git a/yt/src/storage/video_database/set/mod.rs b/yt/src/storage/video_database/set/mod.rs
index 3d68ce8..8c1be4a 100644
--- a/yt/src/storage/video_database/set/mod.rs
+++ b/yt/src/storage/video_database/set/mod.rs
@@ -19,17 +19,17 @@ use log::{debug, info};
 use sqlx::query;
 use tokio::fs;
 
-use crate::{
-    app::App,
-    storage::video_database::{VideoStatusMarker, extractor_hash::ExtractorHash},
-    video_from_record,
-};
+use crate::{app::App, storage::video_database::extractor_hash::ExtractorHash, video_from_record};
 
 use super::{Priority, Video, VideoOptions, VideoStatus};
 
 mod playlist;
 pub use playlist::*;
 
+const fn is_focused_to_value(is_focused: bool) -> Option<i8> {
+    if is_focused { Some(1) } else { None }
+}
+
 /// Set a new status for a video.
 /// This will only update the status time stamp/priority when the status or the priority has changed .
 pub async fn video_status(
@@ -74,9 +74,12 @@ pub async fn video_status(
             is_focused,
         } = &new_status
         {
-            (Some(cache_path_to_string(cache_path)?), *is_focused)
+            (
+                Some(cache_path_to_string(cache_path)?),
+                is_focused_to_value(*is_focused),
+            )
         } else {
-            (None, false)
+            (None, None)
         }
     };
 
@@ -260,10 +263,10 @@ pub async fn add_video(app: &App, video: Video) -> Result<()> {
                     })?
                     .to_string(),
             ),
-            is_focused,
+            is_focused_to_value(is_focused),
         )
     } else {
-        (None, false)
+        (None, None)
     };
 
     let duration: Option<f64> = video.duration.as_secs_f64();
diff --git a/yt/src/storage/video_database/set/playlist.rs b/yt/src/storage/video_database/set/playlist.rs
index 7e97239..547df21 100644
--- a/yt/src/storage/video_database/set/playlist.rs
+++ b/yt/src/storage/video_database/set/playlist.rs
@@ -28,12 +28,9 @@ pub async fn focused(
     new_video_hash: &ExtractorHash,
     old_video_hash: Option<&ExtractorHash>,
 ) -> Result<()> {
-    if let Some(old) = old_video_hash {
-        debug!("Unfocusing video: '{old}'");
-        unfocused(app, old).await?;
-    }
-    debug!("Focusing video: '{new_video_hash}'");
+    unfocused(app, old_video_hash).await?;
 
+    debug!("Focusing video: '{new_video_hash}'");
     let new_hash = new_video_hash.hash().to_string();
     query!(
         r#"
@@ -57,15 +54,38 @@ pub async fn focused(
 }
 
 /// Set a video to be no longer focused.
+/// This will use the supplied `video_hash` if it is [`Some`], otherwise it will simply un-focus
+/// the currently focused video.
 ///
 /// # Panics
 /// Only if internal assertions fail.
-pub async fn unfocused(app: &App, video_hash: &ExtractorHash) -> Result<()> {
-    let hash = video_hash.hash().to_string();
+pub async fn unfocused(app: &App, video_hash: Option<&ExtractorHash>) -> Result<()> {
+    let hash = if let Some(hash) = video_hash {
+        hash.hash().to_string()
+    } else {
+        let output = query!(
+            r#"
+                SELECT extractor_hash
+                FROM videos
+                WHERE is_focused = 1;
+            "#,
+        )
+        .fetch_optional(&app.database)
+        .await?;
+
+        if let Some(output) = output {
+            output.extractor_hash
+        } else {
+            // There is no unfocused video right now.
+            return Ok(());
+        }
+    };
+    debug!("Unfocusing video: '{hash}'");
+
     query!(
         r#"
             UPDATE videos
-            SET is_focused = 0
+            SET is_focused = NULL
             WHERE extractor_hash = ?;
         "#,
         hash