Upstream sync required: cac557b7 #10

New issue

Open

opened 2026-03-13 06:13:56 -03:00 by github-actions[bot] · 0 comments

github-actions[bot] commented

2026-03-13 06:13:56 -03:00

(Migrated from github.com)

Upstream changes detected

The upstream develop branch has moved ahead of our pinned submodule commit.

	Commit
Submodule (current)	`bf195784`
Upstream develop (latest)	`cac557b7`
Full diff	Compare on GitHub

🗄️ New / changed Flyway migrations (2)

Port these to Liquibase in src/main/resources/db/changelog/changelogs/.

V131__Add_tts_position_cfi_to_user_book_file_progress.sql
V132__Add_scanned_on_to_books.sql

🏗️ Changed entities (3)

Review for new fields that may require Liquibase columns or ${datetime.type} / ${id.type} substitutions.

BookEntity.java
BookFileEntity.java
UserBookFileProgressEntity.java

🔍 Changed repositories (3)

Check for new nativeQuery = true annotations — these need JPQL rewrites in our overlay for SQLite/PostgreSQL compatibility.

AuthorRepository.java
NotebookEntryRepository.java
ReadingSessionRepository.java

✅ Checklist

Update submodule to cac557b7 (or the latest stable tag)
Port 2 new Flyway migration(s) to Liquibase changelogs
Review 3 changed entity file(s) for schema impact
Audit 3 changed repository file(s) for new native queries
Diff upstream build.gradle for dependency changes
Build and run full test suite (./gradlew test)

Full diff (watched paths only)

diff --git a/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java
index 2ae538c5..758bce9c 100644
--- a/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java
+++ b/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java
@@ -53,6 +53,9 @@ public class BookEntity {
     @Column(name = "added_on")
     private Instant addedOn;
 
+    @Column(name = "scanned_on")
+    private Instant scannedOn;
+
     @Column(name = "book_cover_hash", length = 20)
     private String bookCoverHash;
 
diff --git a/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java
index 07a55a99..1d1bfcbc 100644
--- a/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java
+++ b/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java
@@ -128,7 +128,7 @@ public class BookFileEntity {
                     .filter(java.nio.file.Files::isRegularFile)
                     .filter(p -> {
                         String name = p.getFileName().toString().toLowerCase();
-                        return name.endsWith(".mp3") || name.endsWith(".m4a") || name.endsWith(".m4b");
+                        return name.endsWith(".mp3") || name.endsWith(".m4a") || name.endsWith(".m4b") || name.endsWith(".opus");
                     })
                     .sorted()
                     .findFirst()
diff --git a/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java
index 0e2d376c..c07561b4 100644
--- a/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java
+++ b/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java
@@ -36,6 +36,9 @@ public class UserBookFileProgressEntity {
     @Column(name = "progress_percent")
     private Float progressPercent;
 
+    @Column(name = "tts_position_cfi", length = 1000)
+    private String ttsPositionCfi;
+
     @Column(name = "last_read_time")
     private Instant lastReadTime;
 }
diff --git a/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java b/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java
index 6ff2e106..476c5a39 100644
--- a/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java
@@ -30,4 +30,16 @@ public interface AuthorRepository extends JpaRepository<AuthorEntity, Long> {
 
     @Query("SELECT COUNT(b) > 0 FROM AuthorEntity a JOIN a.bookMetadataEntityList bm JOIN bm.book b WHERE a.id = :authorId AND b.library.id IN :libraryIds")
     boolean existsByIdAndLibraryIds(@Param("authorId") Long authorId, @Param("libraryIds") Set<Long> libraryIds);
+
+    @Query(value = "SELECT m.book_id AS bookId, a.name AS authorName " +
+                   "FROM book_metadata_author_mapping m " +
+                   "JOIN author a ON a.id = m.author_id " +
+                   "WHERE m.book_id IN :bookIds ORDER BY a.name",
+           nativeQuery = true)
+    List<AuthorBookProjection> findAuthorNamesByBookIds(@Param("bookIds") Set<Long> bookIds);
+
+    interface AuthorBookProjection {
+        Long getBookId();
+        String getAuthorName();
+    }
 }
diff --git a/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java b/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java
index 0f6323ab..017c2244 100644
--- a/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java
@@ -7,6 +7,7 @@ import org.springframework.data.jpa.repository.Query;
 import org.springframework.data.repository.Repository;
 import org.springframework.data.repository.query.Param;
 
+import java.time.Instant;
 import java.time.LocalDateTime;
 import java.util.List;
 import java.util.Set;
@@ -55,6 +56,13 @@ public interface NotebookEntryRepository extends Repository<AnnotationEntity, Lo
         String getBookTitle();
     }
 
+    interface BookWithCountProjection {
+        Long getBookId();
+        String getBookTitle();
+        int getNoteCount();
+        Instant getCoverUpdatedOn();
+    }
+
     @Query(value = "SELECT t.id, t.type, t.book_id AS bookId, t.book_title AS bookTitle, " +
                    "t.text, t.note, t.color, t.style, t.chapter_title AS chapterTitle, " +
                    "(SELECT bf.book_type FROM book_file bf WHERE bf.book_id = t.book_id ORDER BY bf.id LIMIT 1) AS primaryBookType, " +
@@ -83,4 +91,37 @@ public interface NotebookEntryRepository extends Repository<AnnotationEntity, Lo
     List<BookProjection> findBooksWithAnnotations(@Param("userId") Long userId,
                                                   @Param("search") String search,
                                                   Pageable pageable);
+
+    @Query(value = "SELECT t.book_id AS bookId, t.book_title AS bookTitle, " +
+                   "COUNT(*) AS noteCount, t.cover_updated_on AS coverUpdatedOn " +
+                   "FROM (" +
+                   "SELECT a.book_id, a.user_id, bm.title AS book_title, bm.cover_updated_on " +
+                   "FROM annotations a JOIN book_metadata bm ON bm.book_id = a.book_id " +
+                   "UNION ALL " +
+                   "SELECT n.book_id, n.user_id, bm.title AS book_title, bm.cover_updated_on " +
+                   "FROM book_notes_v2 n JOIN book_metadata bm ON bm.book_id = n.book_id " +
+                   "UNION ALL " +
+                   "SELECT b.book_id, b.user_id, bm.title AS book_title, bm.cover_updated_on " +
+                   "FROM book_marks b JOIN book_metadata bm ON bm.book_id = b.book_id" +
+                   ") t WHERE t.user_id = :userId" +
+                   " AND (:search IS NULL OR t.book_title LIKE :search ESCAPE '\\\\')" +
+                   " GROUP BY t.book_id, t.book_title, t.cover_updated_on" +
+                   " ORDER BY t.book_title",
+           countQuery = "SELECT COUNT(*) FROM (" +
+                        "SELECT DISTINCT t.book_id FROM (" +
+                        "SELECT a.book_id, a.user_id, bm.title AS book_title " +
+                        "FROM annotations a JOIN book_metadata bm ON bm.book_id = a.book_id " +
+                        "UNION ALL " +
+                        "SELECT n.book_id, n.user_id, bm.title AS book_title " +
+                        "FROM book_notes_v2 n JOIN book_metadata bm ON bm.book_id = n.book_id " +
+                        "UNION ALL " +
+                        "SELECT b.book_id, b.user_id, bm.title AS book_title " +
+                        "FROM book_marks b JOIN book_metadata bm ON bm.book_id = b.book_id" +
+                        ") t WHERE t.user_id = :userId" +
+                        " AND (:search IS NULL OR t.book_title LIKE :search ESCAPE '\\\\')" +
+                        ") cnt",
+           nativeQuery = true)
+    Page<BookWithCountProjection> findBooksWithAnnotationsPaginated(@Param("userId") Long userId,
+                                                                    @Param("search") String search,
+                                                                    Pageable pageable);
 }
diff --git a/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java b/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java
index c3b2e46d..e5007f07 100644
--- a/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java
+++ b/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java
@@ -213,4 +213,182 @@ public interface ReadingSessionRepository extends JpaRepository<ReadingSessionEn
             @Param("userId") Long userId,
             @Param("year") int year,
             @Param("tzOffset") String tzOffset);
+
+    // ========================================================================
+    // Listening (audiobook) stats
+    // ========================================================================
+
+    @Query(value = """
+            SELECT DATE(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as date,
+                   COUNT(*) as sessions,
+                   COALESCE(ROUND(SUM(duration_seconds) / 60.0), 0) as durationMinutes
+            FROM reading_sessions
+            WHERE user_id = :userId
+            AND book_type = 'AUDIOBOOK'
+            AND YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :year
+            AND MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :month
+            GROUP BY DATE(CONVERT_TZ(start_time, '+00:00', :tzOffset))
+            ORDER BY date
+            """, nativeQuery = true)
+    List<ListeningHeatmapDto> findListeningSessionsByUserAndMonth(
+            @Param("userId") Long userId,
+            @Param("year") int year,
+            @Param("month") int month,
+            @Param("tzOffset") String tzOffset);
+
+    @Query(value = """
+            SELECT YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as year,
+                   WEEK(CONVERT_TZ(start_time, '+00:00', :tzOffset), 3) as week,
+                   COALESCE(SUM(duration_seconds), 0) as totalDurationSeconds,
+                   COUNT(*) as sessions
+            FROM reading_sessions
+            WHERE user_id = :userId
+            AND book_type = 'AUDIOBOOK'
+            AND CONVERT_TZ(start_time, '+00:00', :tzOffset) >= DATE_SUB(NOW(), INTERVAL :weeks WEEK)
+            GROUP BY year, week
+            ORDER BY year, week
+            """, nativeQuery = true)
+    List<WeeklyListeningTrendDto> findWeeklyListeningTrend(
+            @Param("userId") Long userId,
+            @Param("weeks") int weeks,
+            @Param("tzOffset") String tzOffset);
+
+    @Query(value = """
+            SELECT rs.book_id as bookId,
+                   COALESCE(bm.title, 'Unknown') as title,
+                   COALESCE(MAX(rs.end_progress), 0) as maxProgress,
+                   COALESCE(MAX(bf.duration_seconds), 0) as totalDurationSeconds,
+                   SUM(rs.duration_seconds) as listenedDurationSeconds
+            FROM reading_sessions rs
+            JOIN book b ON rs.book_id = b.id
+            LEFT JOIN book_metadata bm ON bm.book_id = b.id
+            LEFT JOIN book_file bf ON bf.book_id = b.id AND bf.book_type = 'AUDIOBOOK'
+            WHERE rs.user_id = :userId
+            AND rs.book_type = 'AUDIOBOOK'
+            GROUP BY rs.book_id, bm.title
+            """, nativeQuery = true)
+    List<AudiobookProgressDto> findAudiobookProgressByUser(@Param("userId") Long userId);
+
+    @Query(value = """
+            SELECT YEAR(COALESCE(ubp.date_finished, ubp.read_status_modified_time)) as year,
+                   MONTH(COALESCE(ubp.date_finished, ubp.read_status_modified_time)) as month,
+                   COUNT(*) as booksCompleted
+            FROM user_book_progress ubp
+            WHERE ubp.user_id = :userId
+            AND ubp.read_status = 'READ'
+            AND COALESCE(ubp.date_finished, ubp.read_status_modified_time) IS NOT NULL
+            AND EXISTS (
+                SELECT 1 FROM reading_sessions rs
+                WHERE rs.book_id = ubp.book_id
+                AND rs.user_id = ubp.user_id
+                AND rs.book_type = 'AUDIOBOOK'
+            )
+            GROUP BY year, month
+            ORDER BY year DESC, month DESC
+            """, nativeQuery = true)
+    List<MonthlyCompletedAudiobookDto> findMonthlyCompletedAudiobooks(@Param("userId") Long userId);
+
+    @Query(value = """
+            SELECT YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as year,
+                   MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as month,
+                   COALESCE(SUM(duration_seconds), 0) as totalDurationSeconds
+            FROM reading_sessions
+            WHERE user_id = :userId
+            AND book_type = 'AUDIOBOOK'
+            GROUP BY year, month
+            ORDER BY year DESC, month DESC
+            """, nativeQuery = true)
+    List<MonthlyListeningDurationDto> findMonthlyListeningDurations(
+            @Param("userId") Long userId,
+            @Param("tzOffset") String tzOffset);
+
+    @Query(value = """
+            SELECT
+                HOUR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as hourOfDay,
+                COUNT(*) as sessionCount,
+                SUM(duration_seconds) as totalDurationSeconds
+            FROM reading_sessions
+            WHERE user_id = :userId
+            AND book_type = 'AUDIOBOOK'
+            AND (:year IS NULL OR YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :year)
+            AND (:month IS NULL OR MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :month)
+            GROUP BY HOUR(CONVERT_TZ(start_time, '+00:00', :tzOffset))
+            ORDER BY hourOfDay
+            """, nativeQuery = true)
+    List<PeakReadingHourDto> findListeningPeakHoursByUser(
+            @Param("userId") Long userId,
+            @Param("year") Integer year,
+            @Param("month") Integer month,
+            @Param("tzOffset") String tzOffset);
+
+    @Query("""
+            SELECT
+                c.name as genre,
+                COUNT(DISTINCT b.id) as bookCount,
+                COUNT(rs) as totalSessions,
+                SUM(rs.durationSeconds) as totalDurationSeconds
+            FROM ReadingSessionEntity rs
+            JOIN rs.book b
+            JOIN b.metadata.categories c
+            WHERE rs.user.id = :userId
+            AND rs.bookType = org.booklore.model.enums.BookFileType.AUDIOBOOK
+            GROUP BY c.name
+            ORDER BY totalDurationSeconds DESC
+            """)
+    List<GenreStatisticsDto> findListeningGenreStatisticsByUser(@Param("userId") Long userId);
+
+    @Query(value = """
+            SELECT a.name as authorName,
+                   COUNT(DISTINCT rs.book_id) as bookCount,
+                   COUNT(*) as totalSessions,
+                   COALESCE(SUM(rs.duration_seconds), 0) as totalDurationSeconds
+            FROM reading_sessions rs
+            JOIN book_metadata_author_mapping bam ON bam.book_id = rs.book_id
+            JOIN author a ON a.id = bam.author_id
+            WHERE rs.user_id = :userId
+            AND rs.book_type = 'AUDIOBOOK'
+            GROUP BY a.name
+            ORDER BY totalDurationSeconds DESC
+            """, nativeQuery = true)
+    List<ListeningAuthorDto> findListeningAuthorStatsByUser(@Param("userId") Long userId);
+
+    @Query(value = """
+            SELECT
+                HOUR(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset))
+                    + MINUTE(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) / 60.0 as hourOfDay,
+                rs.duration_seconds / 60.0 as durationMinutes,
+                DAYOFWEEK(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) as dayOfWeek
+            FROM reading_sessions rs
+            WHERE rs.user_id = :userId
+            AND rs.book_type = 'AUDIOBOOK'
+            ORDER BY rs.start_time DESC
+            LIMIT 500
+            """, nativeQuery = true)
+    List<SessionScatterDto> findListeningSessionScatterByUser(
+            @Param("userId") Long userId,
+            @Param("tzOffset") String tzOffset);
+
+    @Query(value = """
+            SELECT rs.book_id as bookId,
+                   COALESCE(bm.title, 'Unknown') as title,
+                   bm.page_count as pageCount,
+                   MIN(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) as firstSessionDate,
+                   MAX(CONVERT_TZ(rs.end_time, '+00:00', :tzOffset)) as lastSessionDate,
+                   COUNT(*) as totalSessions,
+                   COALESCE(SUM(rs.duration_seconds), 0) as totalDurationSeconds,
+                   COALESCE(MAX(rs.end_progress), 0) / 100.0 as maxProgress,
+                   ubp.read_status as readStatus
+            FROM reading_sessions rs
+            JOIN book b ON rs.book_id = b.id
+            LEFT JOIN book_metadata bm ON bm.book_id = b.id
+            LEFT JOIN user_book_progress ubp ON ubp.book_id = rs.book_id AND ubp.user_id = rs.user_id
+            WHERE rs.user_id = :userId
+            AND YEAR(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) = :year
+            GROUP BY rs.book_id, bm.title, bm.page_count, ubp.read_status
+            ORDER BY firstSessionDate
+            """, nativeQuery = true)
+    List<BookTimelineDto> findBookTimelineByUserAndYear(
+            @Param("userId") Long userId,
+            @Param("year") int year,
+            @Param("tzOffset") String tzOffset);
 }
diff --git a/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql b/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql
new file mode 100644
index 00000000..e77fc1c0
--- /dev/null
+++ b/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql
@@ -0,0 +1 @@
+ALTER TABLE user_book_file_progress ADD COLUMN IF NOT EXISTS tts_position_cfi VARCHAR(1000) NULL;
diff --git a/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql b/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql
new file mode 100644
index 00000000..9819c28c
--- /dev/null
+++ b/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql
@@ -0,0 +1 @@
+ALTER TABLE book ADD COLUMN IF NOT EXISTS scanned_on TIMESTAMP NULL;

## Upstream changes detected The upstream `develop` branch has moved ahead of our pinned submodule commit. | | Commit | |---|---| | **Submodule (current)** | [`bf195784`](https://github.com/booklore-app/booklore/commit/bf195784) | | **Upstream develop (latest)** | [`cac557b7`](https://github.com/booklore-app/booklore/commit/cac557b7) | | **Full diff** | [Compare on GitHub](https://github.com/booklore-app/booklore/compare/bf195784...cac557b7) | ### 🗄️ New / changed Flyway migrations (2) Port these to Liquibase in `src/main/resources/db/changelog/changelogs/`. - `V131__Add_tts_position_cfi_to_user_book_file_progress.sql` - `V132__Add_scanned_on_to_books.sql` ### 🏗️ Changed entities (3) Review for new fields that may require Liquibase columns or `${datetime.type}` / `${id.type}` substitutions. - `BookEntity.java` - `BookFileEntity.java` - `UserBookFileProgressEntity.java` ### 🔍 Changed repositories (3) Check for new `nativeQuery = true` annotations — these need JPQL rewrites in our overlay for SQLite/PostgreSQL compatibility. - `AuthorRepository.java` - `NotebookEntryRepository.java` - `ReadingSessionRepository.java` ### ✅ Checklist - [ ] Update submodule to `cac557b7` (or the latest stable tag) - [ ] Port 2 new Flyway migration(s) to Liquibase changelogs - [ ] Review 3 changed entity file(s) for schema impact - [ ] Audit 3 changed repository file(s) for new native queries - [ ] Diff upstream `build.gradle` for dependency changes - [ ] Build and run full test suite (`./gradlew test`) <details> <summary>Full diff (watched paths only)</summary> ```diff diff --git a/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java index 2ae538c5..758bce9c 100644 --- a/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java +++ b/booklore-api/src/main/java/org/booklore/model/entity/BookEntity.java @@ -53,6 +53,9 @@ public class BookEntity { @Column(name = "added_on") private Instant addedOn; + @Column(name = "scanned_on") + private Instant scannedOn; + @Column(name = "book_cover_hash", length = 20) private String bookCoverHash; diff --git a/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java index 07a55a99..1d1bfcbc 100644 --- a/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java +++ b/booklore-api/src/main/java/org/booklore/model/entity/BookFileEntity.java @@ -128,7 +128,7 @@ public class BookFileEntity { .filter(java.nio.file.Files::isRegularFile) .filter(p -> { String name = p.getFileName().toString().toLowerCase(); - return name.endsWith(".mp3") || name.endsWith(".m4a") || name.endsWith(".m4b"); + return name.endsWith(".mp3") || name.endsWith(".m4a") || name.endsWith(".m4b") || name.endsWith(".opus"); }) .sorted() .findFirst() diff --git a/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java b/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java index 0e2d376c..c07561b4 100644 --- a/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java +++ b/booklore-api/src/main/java/org/booklore/model/entity/UserBookFileProgressEntity.java @@ -36,6 +36,9 @@ public class UserBookFileProgressEntity { @Column(name = "progress_percent") private Float progressPercent; + @Column(name = "tts_position_cfi", length = 1000) + private String ttsPositionCfi; + @Column(name = "last_read_time") private Instant lastReadTime; } diff --git a/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java b/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java index 6ff2e106..476c5a39 100644 --- a/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java +++ b/booklore-api/src/main/java/org/booklore/repository/AuthorRepository.java @@ -30,4 +30,16 @@ public interface AuthorRepository extends JpaRepository<AuthorEntity, Long> { @Query("SELECT COUNT(b) > 0 FROM AuthorEntity a JOIN a.bookMetadataEntityList bm JOIN bm.book b WHERE a.id = :authorId AND b.library.id IN :libraryIds") boolean existsByIdAndLibraryIds(@Param("authorId") Long authorId, @Param("libraryIds") Set<Long> libraryIds); + + @Query(value = "SELECT m.book_id AS bookId, a.name AS authorName " + + "FROM book_metadata_author_mapping m " + + "JOIN author a ON a.id = m.author_id " + + "WHERE m.book_id IN :bookIds ORDER BY a.name", + nativeQuery = true) + List<AuthorBookProjection> findAuthorNamesByBookIds(@Param("bookIds") Set<Long> bookIds); + + interface AuthorBookProjection { + Long getBookId(); + String getAuthorName(); + } } diff --git a/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java b/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java index 0f6323ab..017c2244 100644 --- a/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java +++ b/booklore-api/src/main/java/org/booklore/repository/NotebookEntryRepository.java @@ -7,6 +7,7 @@ import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.Repository; import org.springframework.data.repository.query.Param; +import java.time.Instant; import java.time.LocalDateTime; import java.util.List; import java.util.Set; @@ -55,6 +56,13 @@ public interface NotebookEntryRepository extends Repository<AnnotationEntity, Lo String getBookTitle(); } + interface BookWithCountProjection { + Long getBookId(); + String getBookTitle(); + int getNoteCount(); + Instant getCoverUpdatedOn(); + } + @Query(value = "SELECT t.id, t.type, t.book_id AS bookId, t.book_title AS bookTitle, " + "t.text, t.note, t.color, t.style, t.chapter_title AS chapterTitle, " + "(SELECT bf.book_type FROM book_file bf WHERE bf.book_id = t.book_id ORDER BY bf.id LIMIT 1) AS primaryBookType, " + @@ -83,4 +91,37 @@ public interface NotebookEntryRepository extends Repository<AnnotationEntity, Lo List<BookProjection> findBooksWithAnnotations(@Param("userId") Long userId, @Param("search") String search, Pageable pageable); + + @Query(value = "SELECT t.book_id AS bookId, t.book_title AS bookTitle, " + + "COUNT(*) AS noteCount, t.cover_updated_on AS coverUpdatedOn " + + "FROM (" + + "SELECT a.book_id, a.user_id, bm.title AS book_title, bm.cover_updated_on " + + "FROM annotations a JOIN book_metadata bm ON bm.book_id = a.book_id " + + "UNION ALL " + + "SELECT n.book_id, n.user_id, bm.title AS book_title, bm.cover_updated_on " + + "FROM book_notes_v2 n JOIN book_metadata bm ON bm.book_id = n.book_id " + + "UNION ALL " + + "SELECT b.book_id, b.user_id, bm.title AS book_title, bm.cover_updated_on " + + "FROM book_marks b JOIN book_metadata bm ON bm.book_id = b.book_id" + + ") t WHERE t.user_id = :userId" + + " AND (:search IS NULL OR t.book_title LIKE :search ESCAPE '\\\\')" + + " GROUP BY t.book_id, t.book_title, t.cover_updated_on" + + " ORDER BY t.book_title", + countQuery = "SELECT COUNT(*) FROM (" + + "SELECT DISTINCT t.book_id FROM (" + + "SELECT a.book_id, a.user_id, bm.title AS book_title " + + "FROM annotations a JOIN book_metadata bm ON bm.book_id = a.book_id " + + "UNION ALL " + + "SELECT n.book_id, n.user_id, bm.title AS book_title " + + "FROM book_notes_v2 n JOIN book_metadata bm ON bm.book_id = n.book_id " + + "UNION ALL " + + "SELECT b.book_id, b.user_id, bm.title AS book_title " + + "FROM book_marks b JOIN book_metadata bm ON bm.book_id = b.book_id" + + ") t WHERE t.user_id = :userId" + + " AND (:search IS NULL OR t.book_title LIKE :search ESCAPE '\\\\')" + + ") cnt", + nativeQuery = true) + Page<BookWithCountProjection> findBooksWithAnnotationsPaginated(@Param("userId") Long userId, + @Param("search") String search, + Pageable pageable); } diff --git a/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java b/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java index c3b2e46d..e5007f07 100644 --- a/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java +++ b/booklore-api/src/main/java/org/booklore/repository/ReadingSessionRepository.java @@ -213,4 +213,182 @@ public interface ReadingSessionRepository extends JpaRepository<ReadingSessionEn @Param("userId") Long userId, @Param("year") int year, @Param("tzOffset") String tzOffset); + + // ======================================================================== + // Listening (audiobook) stats + // ======================================================================== + + @Query(value = """ + SELECT DATE(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as date, + COUNT(*) as sessions, + COALESCE(ROUND(SUM(duration_seconds) / 60.0), 0) as durationMinutes + FROM reading_sessions + WHERE user_id = :userId + AND book_type = 'AUDIOBOOK' + AND YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :year + AND MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :month + GROUP BY DATE(CONVERT_TZ(start_time, '+00:00', :tzOffset)) + ORDER BY date + """, nativeQuery = true) + List<ListeningHeatmapDto> findListeningSessionsByUserAndMonth( + @Param("userId") Long userId, + @Param("year") int year, + @Param("month") int month, + @Param("tzOffset") String tzOffset); + + @Query(value = """ + SELECT YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as year, + WEEK(CONVERT_TZ(start_time, '+00:00', :tzOffset), 3) as week, + COALESCE(SUM(duration_seconds), 0) as totalDurationSeconds, + COUNT(*) as sessions + FROM reading_sessions + WHERE user_id = :userId + AND book_type = 'AUDIOBOOK' + AND CONVERT_TZ(start_time, '+00:00', :tzOffset) >= DATE_SUB(NOW(), INTERVAL :weeks WEEK) + GROUP BY year, week + ORDER BY year, week + """, nativeQuery = true) + List<WeeklyListeningTrendDto> findWeeklyListeningTrend( + @Param("userId") Long userId, + @Param("weeks") int weeks, + @Param("tzOffset") String tzOffset); + + @Query(value = """ + SELECT rs.book_id as bookId, + COALESCE(bm.title, 'Unknown') as title, + COALESCE(MAX(rs.end_progress), 0) as maxProgress, + COALESCE(MAX(bf.duration_seconds), 0) as totalDurationSeconds, + SUM(rs.duration_seconds) as listenedDurationSeconds + FROM reading_sessions rs + JOIN book b ON rs.book_id = b.id + LEFT JOIN book_metadata bm ON bm.book_id = b.id + LEFT JOIN book_file bf ON bf.book_id = b.id AND bf.book_type = 'AUDIOBOOK' + WHERE rs.user_id = :userId + AND rs.book_type = 'AUDIOBOOK' + GROUP BY rs.book_id, bm.title + """, nativeQuery = true) + List<AudiobookProgressDto> findAudiobookProgressByUser(@Param("userId") Long userId); + + @Query(value = """ + SELECT YEAR(COALESCE(ubp.date_finished, ubp.read_status_modified_time)) as year, + MONTH(COALESCE(ubp.date_finished, ubp.read_status_modified_time)) as month, + COUNT(*) as booksCompleted + FROM user_book_progress ubp + WHERE ubp.user_id = :userId + AND ubp.read_status = 'READ' + AND COALESCE(ubp.date_finished, ubp.read_status_modified_time) IS NOT NULL + AND EXISTS ( + SELECT 1 FROM reading_sessions rs + WHERE rs.book_id = ubp.book_id + AND rs.user_id = ubp.user_id + AND rs.book_type = 'AUDIOBOOK' + ) + GROUP BY year, month + ORDER BY year DESC, month DESC + """, nativeQuery = true) + List<MonthlyCompletedAudiobookDto> findMonthlyCompletedAudiobooks(@Param("userId") Long userId); + + @Query(value = """ + SELECT YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as year, + MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as month, + COALESCE(SUM(duration_seconds), 0) as totalDurationSeconds + FROM reading_sessions + WHERE user_id = :userId + AND book_type = 'AUDIOBOOK' + GROUP BY year, month + ORDER BY year DESC, month DESC + """, nativeQuery = true) + List<MonthlyListeningDurationDto> findMonthlyListeningDurations( + @Param("userId") Long userId, + @Param("tzOffset") String tzOffset); + + @Query(value = """ + SELECT + HOUR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) as hourOfDay, + COUNT(*) as sessionCount, + SUM(duration_seconds) as totalDurationSeconds + FROM reading_sessions + WHERE user_id = :userId + AND book_type = 'AUDIOBOOK' + AND (:year IS NULL OR YEAR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :year) + AND (:month IS NULL OR MONTH(CONVERT_TZ(start_time, '+00:00', :tzOffset)) = :month) + GROUP BY HOUR(CONVERT_TZ(start_time, '+00:00', :tzOffset)) + ORDER BY hourOfDay + """, nativeQuery = true) + List<PeakReadingHourDto> findListeningPeakHoursByUser( + @Param("userId") Long userId, + @Param("year") Integer year, + @Param("month") Integer month, + @Param("tzOffset") String tzOffset); + + @Query(""" + SELECT + c.name as genre, + COUNT(DISTINCT b.id) as bookCount, + COUNT(rs) as totalSessions, + SUM(rs.durationSeconds) as totalDurationSeconds + FROM ReadingSessionEntity rs + JOIN rs.book b + JOIN b.metadata.categories c + WHERE rs.user.id = :userId + AND rs.bookType = org.booklore.model.enums.BookFileType.AUDIOBOOK + GROUP BY c.name + ORDER BY totalDurationSeconds DESC + """) + List<GenreStatisticsDto> findListeningGenreStatisticsByUser(@Param("userId") Long userId); + + @Query(value = """ + SELECT a.name as authorName, + COUNT(DISTINCT rs.book_id) as bookCount, + COUNT(*) as totalSessions, + COALESCE(SUM(rs.duration_seconds), 0) as totalDurationSeconds + FROM reading_sessions rs + JOIN book_metadata_author_mapping bam ON bam.book_id = rs.book_id + JOIN author a ON a.id = bam.author_id + WHERE rs.user_id = :userId + AND rs.book_type = 'AUDIOBOOK' + GROUP BY a.name + ORDER BY totalDurationSeconds DESC + """, nativeQuery = true) + List<ListeningAuthorDto> findListeningAuthorStatsByUser(@Param("userId") Long userId); + + @Query(value = """ + SELECT + HOUR(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) + + MINUTE(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) / 60.0 as hourOfDay, + rs.duration_seconds / 60.0 as durationMinutes, + DAYOFWEEK(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) as dayOfWeek + FROM reading_sessions rs + WHERE rs.user_id = :userId + AND rs.book_type = 'AUDIOBOOK' + ORDER BY rs.start_time DESC + LIMIT 500 + """, nativeQuery = true) + List<SessionScatterDto> findListeningSessionScatterByUser( + @Param("userId") Long userId, + @Param("tzOffset") String tzOffset); + + @Query(value = """ + SELECT rs.book_id as bookId, + COALESCE(bm.title, 'Unknown') as title, + bm.page_count as pageCount, + MIN(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) as firstSessionDate, + MAX(CONVERT_TZ(rs.end_time, '+00:00', :tzOffset)) as lastSessionDate, + COUNT(*) as totalSessions, + COALESCE(SUM(rs.duration_seconds), 0) as totalDurationSeconds, + COALESCE(MAX(rs.end_progress), 0) / 100.0 as maxProgress, + ubp.read_status as readStatus + FROM reading_sessions rs + JOIN book b ON rs.book_id = b.id + LEFT JOIN book_metadata bm ON bm.book_id = b.id + LEFT JOIN user_book_progress ubp ON ubp.book_id = rs.book_id AND ubp.user_id = rs.user_id + WHERE rs.user_id = :userId + AND YEAR(CONVERT_TZ(rs.start_time, '+00:00', :tzOffset)) = :year + GROUP BY rs.book_id, bm.title, bm.page_count, ubp.read_status + ORDER BY firstSessionDate + """, nativeQuery = true) + List<BookTimelineDto> findBookTimelineByUserAndYear( + @Param("userId") Long userId, + @Param("year") int year, + @Param("tzOffset") String tzOffset); } diff --git a/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql b/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql new file mode 100644 index 00000000..e77fc1c0 --- /dev/null +++ b/booklore-api/src/main/resources/db/migration/V131__Add_tts_position_cfi_to_user_book_file_progress.sql @@ -0,0 +1 @@ +ALTER TABLE user_book_file_progress ADD COLUMN IF NOT EXISTS tts_position_cfi VARCHAR(1000) NULL; diff --git a/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql b/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql new file mode 100644 index 00000000..9819c28c --- /dev/null +++ b/booklore-api/src/main/resources/db/migration/V132__Add_scanned_on_to_books.sql @@ -0,0 +1 @@ +ALTER TABLE book ADD COLUMN IF NOT EXISTS scanned_on TIMESTAMP NULL; ``` </details>