metadata.yaml

title: Reading Stats
description: Reading statistics from Kobo Clara and Kindle eReaders

extra_css_urls:
  - /static/custom.css

databases:
  KoboReader:
    tables:
      content:
        hidden: true
      Event:
        hidden: true
      Activity:
        hidden: true
      Bookmark:
        hidden: true
      AnalyticsEvents:
        hidden: true
      Achievement:
        hidden: true
      Authors:
        hidden: true
      BookAuthors:
        hidden: true
      DbVersion:
        hidden: true
      DropboxItem:
        hidden: true
      GDriveItem:
        hidden: true
      KoboPlusAssetGroup:
        hidden: true
      KoboPlusAssets:
        hidden: true
      OverDriveCards:
        hidden: true
      OverDriveCheckoutBook:
        hidden: true
      OverDriveLibrary:
        hidden: true
      Reviews:
        hidden: true
      Rules:
        hidden: true
      Shelf:
        hidden: true
      ShelfContent:
        hidden: true
      SubscriptionProducts:
        hidden: true
      SyncQueue:
        hidden: true
      Tab:
        hidden: true
      Wishlist:
        hidden: true
      WordList:
        hidden: true
      content_keys:
        hidden: true
      content_settings:
        hidden: true
      ratings:
        hidden: true
      shortcover_page:
        hidden: true
      user:
        hidden: true
      volume_shortcovers:
        hidden: true
      volume_tabs:
        hidden: true
      AbTest:
        hidden: true

    queries:
      books_reading_progress:
        hide_sql: true
        title: Books - Reading Progress
        sql: |
          select
            c.Title,
            c.Attribution as Author,
            c.___PercentRead as "% Read",
            CAST(ROUND(w.total_words / 250.0) AS INTEGER) as "Pages (pb)",
            CAST(ROUND(w.total_words * c.___PercentRead / 100.0 / 250.0) AS INTEGER) as "Read (pb)",
            case
              when c.TimeSpentReading >= 3600
              then cast(c.TimeSpentReading / 3600 as text) || 'h '
                   || cast((c.TimeSpentReading % 3600) / 60 as text) || 'm'
              when c.TimeSpentReading >= 60
              then cast(c.TimeSpentReading / 60 as text) || 'm'
              when c.TimeSpentReading > 0
              then cast(c.TimeSpentReading as text) || 's'
              else '-'
            end as "Time Spent",
            case c.ReadStatus
              when 0 then 'Not started'
              when 1 then 'Reading'
              when 2 then 'Finished'
              else c.ReadStatus
            end as Status,
            c.DateLastRead as "Last Read"
          from content c
          left join (
            select BookTitle, SUM(WordCount) as total_words
            from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
            group by BookTitle
          ) w on w.BookTitle = c.Title
          where c.ContentType = 6 and c.ContentID like 'file://%'
          order by c.TimeSpentReading desc

      reading_sessions:
        hide_sql: true
        title: Reading Sessions
        sql: |
          select
            a.Timestamp,
            json_extract(a.Attributes, '$.title') as Book,
            json_extract(a.Metrics, '$.SecondsRead') as "Seconds Read",
            json_extract(a.Metrics, '$.PagesTurned') as "Screen Pages",
            CAST(ROUND(
              json_extract(a.Metrics, '$.PagesTurned') * r.wpp / 250.0
            ) AS INTEGER) as "Paperback Pages",
            CAST(ROUND(
              cast(json_extract(a.Metrics, '$.SecondsRead') as float) / nullif(
                json_extract(a.Metrics, '$.PagesTurned') * r.wpp / 250.0, 0)
            ) AS INTEGER) as "Sec/Page (pb)"
          from AnalyticsEvents a
          left join (
            select
              json_extract(a2.Attributes, '$.title') as title,
              w.total_words * b.percent_read / 100.0
                / SUM(json_extract(a2.Metrics, '$.PagesTurned')) as wpp
            from AnalyticsEvents a2
            join (
              select BookTitle, SUM(WordCount) as total_words
              from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
              group by BookTitle
            ) w on w.BookTitle = json_extract(a2.Attributes, '$.title')
            join (
              select Title, ___PercentRead as percent_read
              from content where ContentType = 6 and ContentID like 'file://%'
            ) b on b.Title = json_extract(a2.Attributes, '$.title')
            where a2.Type = 'LeaveContent' and json_extract(a2.Metrics, '$.PagesTurned') > 0
            group by json_extract(a2.Attributes, '$.title')
          ) r on r.title = json_extract(a.Attributes, '$.title')
          where a.Type = 'LeaveContent'
            and json_extract(a.Metrics, '$.SecondsRead') >= 60
          order by a.Timestamp desc

      dictionary_lookups:
        hide_sql: true
        title: Dictionary Lookups
        sql: |
          select
            Timestamp,
            json_extract(Attributes, '$.Word') as Word,
            json_extract(Attributes, '$.Dictionary') as Language
          from AnalyticsEvents
          where Type = 'DictionaryLookup'
          order by Timestamp desc

  KindleReader:
    tables:
      kindle_books:
        hidden: true
      kindle_page_history:
        hidden: true
      vocab_lookups:
        hidden: true
      clippings:
        hidden: true
      koreader_books:
        hidden: true
      koreader_page_stats:
        hidden: true
      collections:
        hidden: true

    queries:
      kindle_books_list:
        hide_sql: true
        title: Kindle Books - Reading Time
        sql: |
          select
            book_name as Book,
            cast(total_time / 3600 as text) || 'h '
              || cast((total_time % 3600) / 60 as text) || 'm' as "Time Spent",
            total_words as "Words Read",
            book_words as "Book Words",
            round(total_percent, 1) || '%' as "% Read",
            last_read as "Last Read"
          from kindle_books
          order by total_time desc

      kindle_highlights:
        hide_sql: true
        title: Kindle Highlights & Notes
        sql: |
          select
            book_title as Book,
            book_author as Author,
            clip_type as Type,
            content as Content,
            page as Page,
            added_date as Date
          from clippings
          where clip_type != 'bookmark'
          order by added_date desc

      kindle_vocabulary:
        hide_sql: true
        title: Vocabulary Lookups
        sql: |
          select
            word as Word,
            stem as Stem,
            language as Language,
            book_title as Book,
            usage_text as Context,
            lookup_date as Date
          from vocab_lookups
          order by timestamp desc

      kindle_reading_sessions:
        hide_sql: true
        title: KOReader Daily Sessions
        sql: |
          select
            date(start_time, 'unixepoch', 'localtime') as day,
            b.title as Book,
            round(sum(duration) / 60.0) as minutes
          from koreader_page_stats ps
          join koreader_books b on ps.id_book = b.id
          group by day, b.title
          order by day desc

plugins:
  datasette-dashboards:
    reading-stats:
      title: Kobo Reading Stats
      description: ""
      allow_fullscreen: true
      layout:
        - [total-books, books-finished, books-reading, total-time]
        - [time-chart, time-chart, progress-chart, progress-chart]
        - [daily-time, daily-time, daily-pages, daily-pages]
        - [hourly-heatmap, hourly-heatmap, hourly-heatmap, hourly-heatmap]
        - [session-speed, session-speed, dict-words, dict-words]
        - [recently-read, recently-read, currently-reading, currently-reading]
      charts:
        total-books:
          title: Total Books
          db: KoboReader
          query: >
            select count(*) as count
            from content
            where ContentType = 6 and ContentID like 'file://%'
          library: metric
          display:
            field: count
            suffix: " books"

        books-finished:
          title: Finished
          db: KoboReader
          query: >
            select count(*) as count
            from content
            where ContentType = 6 and ContentID like 'file://%' and ReadStatus = 2
          library: metric
          display:
            field: count
            suffix: " done"

        books-reading:
          title: In Progress
          db: KoboReader
          query: >
            select count(*) as count
            from content
            where ContentType = 6 and ContentID like 'file://%' and ReadStatus = 1
          library: metric
          display:
            field: count
            suffix: " reading"

        total-time:
          title: Total Reading Time
          db: KoboReader
          query: >
            select cast(round(sum(TimeSpentReading) / 3600.0) as int) as hours
            from content
            where ContentType = 6 and ContentID like 'file://%'
          library: metric
          display:
            field: hours
            suffix: " hours"

        time-chart:
          title: Reading Time by Book
          db: KoboReader
          query: |
            select
              c.Title as book,
              round(c.TimeSpentReading / 3600.0, 1) as hours,
              CAST(ROUND(w.total_words / 250.0) AS INTEGER) as pages
            from content c
            left join (
              select BookTitle, SUM(WordCount) as total_words
              from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
              group by BookTitle
            ) w on w.BookTitle = c.Title
            where c.ContentType = 6 and c.ContentID like 'file://%' and c.TimeSpentReading > 0
            order by c.TimeSpentReading desc
          library: vega-lite
          display:
            params:
              - name: hover
                select:
                  type: point
                  "on": pointerover
                  clear: pointerout
            mark:
              type: bar
              cursor: pointer
            encoding:
              y:
                field: book
                type: nominal
                sort: "-x"
              x:
                field: hours
                type: quantitative
                title: Hours
              tooltip:
                - {field: book, type: nominal, title: Book}
                - {field: hours, type: quantitative, title: Hours}
                - {field: pages, type: quantitative, title: "Paperback pages"}
              fillOpacity:
                condition:
                  param: hover
                  value: 1
                value: 0.7

        progress-chart:
          title: Reading Progress (paperback pages)
          db: KoboReader
          query: |
            select
              c.Title as book,
              CAST(ROUND(w.total_words * c.___PercentRead / 100.0 / 250.0) AS INTEGER) as pages_read,
              CAST(ROUND(w.total_words / 250.0) AS INTEGER) as pages_total,
              c.___PercentRead as pct
            from content c
            join (
              select BookTitle, SUM(WordCount) as total_words
              from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
              group by BookTitle
            ) w on w.BookTitle = c.Title
            where c.ContentType = 6 and c.ContentID like 'file://%' and c.TimeSpentReading > 0
            order by pages_read desc
          library: vega-lite
          display:
            layer:
              - mark:
                  type: bar
                  color: "#e0e0e0"
                encoding:
                  y:
                    field: book
                    type: nominal
                    sort:
                      field: pages_read
                      order: descending
                  x:
                    field: pages_total
                    type: quantitative
                    title: Pages
                  tooltip:
                    - {field: book, type: nominal, title: Book}
                    - {field: pages_total, type: quantitative, title: "Total pages"}
                    - {field: pages_read, type: quantitative, title: "Pages read"}
                    - {field: pct, type: quantitative, title: "% Read"}
              - mark:
                  type: bar
                  color: "#4c78a8"
                encoding:
                  y:
                    field: book
                    type: nominal
                    sort:
                      field: pages_read
                      order: descending
                  x:
                    field: pages_read
                    type: quantitative
                  tooltip:
                    - {field: book, type: nominal, title: Book}
                    - {field: pages_total, type: quantitative, title: "Total pages"}
                    - {field: pages_read, type: quantitative, title: "Pages read"}
                    - {field: pct, type: quantitative, title: "% Read"}
              - mark:
                  type: text
                  align: left
                  dx: 3
                  fontSize: 11
                  color: "#333"
                transform:
                  - calculate: "datum.pct + '%'"
                    as: label
                encoding:
                  y:
                    field: book
                    type: nominal
                    sort:
                      field: pages_read
                      order: descending
                  x:
                    field: pages_read
                    type: quantitative
                  text:
                    field: label
                    type: nominal

        daily-time:
          title: Daily Reading Time
          db: KoboReader
          query: |
            select
              date(Timestamp) as day,
              round(sum(json_extract(Metrics, '$.SecondsRead')) / 60.0) as minutes,
              json_extract(Attributes, '$.title') as book
            from AnalyticsEvents
            where Type = 'LeaveContent'
              and json_extract(Metrics, '$.SecondsRead') >= 60
            group by day, book
            order by day
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
            mark:
              type: bar
              tooltip: true
            encoding:
              x:
                field: day
                type: ordinal
                title: null
              y:
                field: minutes
                type: quantitative
                title: Minutes
              color:
                field: book
                type: nominal
                title: Book
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15

        daily-pages:
          title: Daily Pages Read (paperback)
          db: KoboReader
          query: |
            with book_ratio as (
              select
                json_extract(a.Attributes, '$.title') as title,
                w.total_words * b.percent_read / 100.0
                  / SUM(json_extract(a.Metrics, '$.PagesTurned')) as wpp
              from AnalyticsEvents a
              join (
                select BookTitle, SUM(WordCount) as total_words
                from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
                group by BookTitle
              ) w on w.BookTitle = json_extract(a.Attributes, '$.title')
              join (
                select Title, ___PercentRead as percent_read
                from content where ContentType = 6 and ContentID like 'file://%'
              ) b on b.Title = json_extract(a.Attributes, '$.title')
              where a.Type = 'LeaveContent' and json_extract(a.Metrics, '$.PagesTurned') > 0
              group by json_extract(a.Attributes, '$.title')
            )
            select
              date(a.Timestamp) as day,
              json_extract(a.Attributes, '$.title') as book,
              CAST(ROUND(SUM(json_extract(a.Metrics, '$.PagesTurned')) * r.wpp / 250.0) AS INTEGER) as pages
            from AnalyticsEvents a
            join book_ratio r on r.title = json_extract(a.Attributes, '$.title')
            where a.Type = 'LeaveContent' and json_extract(a.Metrics, '$.SecondsRead') >= 60
            group by day, json_extract(a.Attributes, '$.title')
            order by day
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
            mark:
              type: bar
              tooltip: true
            encoding:
              x:
                field: day
                type: ordinal
                title: null
              y:
                field: pages
                type: quantitative
                title: Pages
              color:
                field: book
                type: nominal
                title: Book
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15

        hourly-heatmap:
          title: Reading by Hour of Day
          db: KoboReader
          query: |
            select
              cast(strftime('%H', Timestamp) as integer) as hour,
              date(Timestamp) as day,
              round(sum(json_extract(Metrics, '$.SecondsRead')) / 60.0) as minutes
            from AnalyticsEvents
            where Type = 'LeaveContent'
              and json_extract(Metrics, '$.SecondsRead') >= 60
            group by day, hour
            order by day, hour
          library: vega-lite
          display:
            mark:
              type: rect
              tooltip: true
            encoding:
              x:
                field: day
                type: ordinal
                title: null
              y:
                field: hour
                type: ordinal
                title: Hour
                sort: "ascending"
              color:
                field: minutes
                type: quantitative
                title: Minutes
                scale:
                  scheme: blues

        session-speed:
          title: Reading Speed (sec per paperback page)
          db: KoboReader
          query: |
            with book_ratio as (
              select
                json_extract(a.Attributes, '$.title') as title,
                w.total_words * b.percent_read / 100.0
                  / SUM(json_extract(a.Metrics, '$.PagesTurned')) as wpp
              from AnalyticsEvents a
              join (
                select BookTitle, SUM(WordCount) as total_words
                from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
                group by BookTitle
              ) w on w.BookTitle = json_extract(a.Attributes, '$.title')
              join (
                select Title, ___PercentRead as percent_read
                from content where ContentType = 6 and ContentID like 'file://%'
              ) b on b.Title = json_extract(a.Attributes, '$.title')
              where a.Type = 'LeaveContent' and json_extract(a.Metrics, '$.PagesTurned') > 0
              group by json_extract(a.Attributes, '$.title')
            )
            select
              a.Timestamp as ts,
              json_extract(a.Attributes, '$.title') as book,
              CAST(ROUND(
                cast(json_extract(a.Metrics, '$.SecondsRead') as float)
                / nullif(json_extract(a.Metrics, '$.PagesTurned') * r.wpp / 250.0, 0)
              ) AS INTEGER) as sec_per_page,
              CAST(ROUND(json_extract(a.Metrics, '$.PagesTurned') * r.wpp / 250.0) AS INTEGER) as pages
            from AnalyticsEvents a
            join book_ratio r on r.title = json_extract(a.Attributes, '$.title')
            where a.Type = 'LeaveContent'
              and json_extract(a.Metrics, '$.SecondsRead') >= 60
              and json_extract(a.Metrics, '$.PagesTurned') > 2
            order by a.Timestamp
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
              - name: brush
                select:
                  type: interval
                  encodings: [x]
            mark:
              type: point
              filled: true
              cursor: pointer
            encoding:
              x:
                field: ts
                type: temporal
                title: null
              y:
                field: sec_per_page
                type: quantitative
                title: Sec / Page
              color:
                condition:
                  param: brush
                  field: book
                  type: nominal
                  title: Book
                value: lightgray
              size:
                field: pages
                type: quantitative
                title: Pages
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15
              tooltip:
                - {field: book, type: nominal, title: Book}
                - {field: ts, type: temporal, title: Time}
                - {field: sec_per_page, type: quantitative, title: "Sec/page"}
                - {field: pages, type: quantitative, title: Pages}

        dict-words:
          title: Dictionary Lookups
          db: KoboReader
          query: |
            select
              json_extract(Attributes, '$.Word') as word,
              count(*) as lookups
            from AnalyticsEvents
            where Type = 'DictionaryLookup'
            group by word
            order by lookups desc
            limit 30
          library: vega-lite
          display:
            params:
              - name: hover
                select:
                  type: point
                  "on": pointerover
                  clear: pointerout
            mark:
              type: bar
              cursor: pointer
            encoding:
              y:
                field: word
                type: nominal
                sort: "-x"
                title: null
              x:
                field: lookups
                type: quantitative
                title: Lookups
              tooltip:
                - {field: word, type: nominal, title: Word}
                - {field: lookups, type: quantitative, title: Lookups}
              fillOpacity:
                condition:
                  param: hover
                  value: 1
                value: 0.7

        recently-read:
          title: Recently Read
          db: KoboReader
          query: |
            select
              c.Title,
              c.Attribution as Author,
              CAST(ROUND(w.total_words * c.___PercentRead / 100.0 / 250.0) AS INTEGER)
                || ' / ' || CAST(ROUND(w.total_words / 250.0) AS INTEGER) as "Pages (pb)",
              c.___PercentRead || '%' as Progress,
              cast(c.TimeSpentReading / 3600 as text) || 'h '
                || cast((c.TimeSpentReading % 3600) / 60 as text) || 'm'
                as "Time Spent",
              case c.ReadStatus
                when 0 then 'Not started'
                when 1 then 'Reading'
                when 2 then 'Finished'
              end as Status,
              c.DateLastRead as "Last Read"
            from content c
            left join (
              select BookTitle, SUM(WordCount) as total_words
              from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
              group by BookTitle
            ) w on w.BookTitle = c.Title
            where c.ContentType = 6 and c.ContentID like 'file://%' and c.TimeSpentReading > 0
            order by c.DateLastRead desc
          library: table
          display: {}

        currently-reading:
          title: Currently Reading
          db: KoboReader
          query: |
            select
              c.Title,
              c.Attribution as Author,
              CAST(ROUND(w.total_words * c.___PercentRead / 100.0 / 250.0) AS INTEGER)
                || ' / ' || CAST(ROUND(w.total_words / 250.0) AS INTEGER) as "Pages (pb)",
              c.___PercentRead || '%' as Progress,
              cast(c.TimeSpentReading / 3600 as text) || 'h '
                || cast((c.TimeSpentReading % 3600) / 60 as text) || 'm'
                as "Time Spent",
              c.DateLastRead as "Last Read"
            from content c
            left join (
              select BookTitle, SUM(WordCount) as total_words
              from content where ContentType IN (9, 899) and WordCount > 0 and BookTitle IS NOT NULL
              group by BookTitle
            ) w on w.BookTitle = c.Title
            where c.ContentType = 6 and c.ContentID like 'file://%' and c.ReadStatus = 1
            order by c.DateLastRead desc
          library: table
          display: {}

    kindle-stats:
      title: Kindle Reading Stats
      description: ""
      allow_fullscreen: true
      layout:
        - [k-total-books, k-books-finished, k-books-reading, k-total-time]
        - [k-time-chart, k-time-chart, k-progress-chart, k-progress-chart]
        - [k-daily-koreader, k-daily-koreader, k-daily-pages, k-daily-pages]
        - [k-hourly-heatmap, k-hourly-heatmap, k-hourly-heatmap, k-hourly-heatmap]
        - [k-session-speed, k-session-speed, k-top-words, k-top-words]
        - [k-recently-read, k-recently-read, k-currently-reading, k-currently-reading]
        - [k-timeline, k-timeline, k-timeline, k-timeline]
        - [k-vocab-by-book, k-vocab-by-book, k-clippings-timeline, k-clippings-timeline]
        - [k-book-list, k-book-list, k-highlights-list, k-highlights-list]
      charts:
        k-total-books:
          title: Total Books
          db: KindleReader
          query: >
            select count(*) as count from kindle_books
          library: metric
          display:
            field: count
            suffix: " books"

        k-total-time:
          title: Total Reading Time
          db: KindleReader
          query: >
            select cast(round(sum(total_time) / 3600.0) as int) as hours from kindle_books
          library: metric
          display:
            field: hours
            suffix: " hours"

        k-books-finished:
          title: Finished
          db: KindleReader
          query: >
            select count(*) as count from kindle_books
            where book_words > 0 and total_words * 100.0 / book_words >= 80
          library: metric
          display:
            field: count
            suffix: " done"

        k-books-reading:
          title: In Progress
          db: KindleReader
          query: >
            select count(*) as count from kindle_books
            where book_words > 0 and total_words > 0
            and total_words * 100.0 / book_words < 80
          library: metric
          display:
            field: count
            suffix: " reading"

        k-time-chart:
          title: Reading Time by Book
          db: KindleReader
          query: |
            select
              book_name as book,
              round(total_time / 3600.0, 1) as hours
            from kindle_books
            where total_time > 0
            order by total_time desc
          library: vega-lite
          display:
            params:
              - name: hover
                select:
                  type: point
                  "on": pointerover
                  clear: pointerout
            mark:
              type: bar
              cursor: pointer
            encoding:
              y:
                field: book
                type: nominal
                sort: "-x"
              x:
                field: hours
                type: quantitative
                title: Hours
              tooltip:
                - {field: book, type: nominal, title: Book}
                - {field: hours, type: quantitative, title: Hours}
              fillOpacity:
                condition:
                  param: hover
                  value: 1
                value: 0.7

        k-progress-chart:
          title: Reading Progress (est. paperback pages)
          db: KindleReader
          query: |
            select
              book_name as book,
              cast(round(total_words / 250.0) as integer) as pages_read,
              cast(round(book_words / 250.0) as integer) as pages_total,
              round(total_words * 100.0 / book_words, 1) as pct
            from kindle_books
            where total_time > 0 and book_words > 0
            order by pages_read desc
          library: vega-lite
          display:
            layer:
              - mark:
                  type: bar
                  color: "#e0e0e0"
                encoding:
                  y:
                    field: book
                    type: nominal
                    sort:
                      field: pages_read
                      order: descending
                  x:
                    field: pages_total
                    type: quantitative
                    title: Pages
                  tooltip:
                    - {field: book, type: nominal, title: Book}
                    - {field: pages_total, type: quantitative, title: "Total pages"}
                    - {field: pages_read, type: quantitative, title: "Pages read"}
                    - {field: pct, type: quantitative, title: "% Read"}
              - mark:
                  type: bar
                  color: "#4c78a8"
                encoding:
                  y:
                    field: book
                    type: nominal
                    sort:
                      field: pages_read
                      order: descending
                  x:
                    field: pages_read
                    type: quantitative
                  tooltip:
                    - {field: book, type: nominal, title: Book}
                    - {field: pages_total, type: quantitative, title: "Total pages"}
                    - {field: pages_read, type: quantitative, title: "Pages read"}
                    - {field: pct, type: quantitative, title: "% Read"}
              - mark:
                  type: text
                  align: left
                  dx: 3
                  fontSize: 11
                  color: "#333"
                transform:
                  - calculate: "datum.pct + '%'"
                    as: label
                encoding:
                  y:
                    field: book
                    type: nominal
                    sort:
                      field: pages_read
                      order: descending
                  x:
                    field: pages_read
                    type: quantitative
                  text:
                    field: label
                    type: nominal

        k-timeline:
          title: Reading Timeline
          db: KindleReader
          query: |
            select
              book_name as book,
              time,
              position
            from kindle_page_history
            order by time
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
            mark:
              type: circle
              size: 20
              tooltip: true
            encoding:
              x:
                field: time
                type: temporal
                title: null
              y:
                field: book
                type: nominal
                sort: "-x"
              color:
                field: book
                type: nominal
                title: Book
                legend: null
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15
              tooltip:
                - {field: book, type: nominal, title: Book}
                - {field: time, type: temporal, title: Time}

        k-daily-koreader:
          title: Daily Reading (KOReader)
          db: KindleReader
          query: |
            select
              date(start_time, 'unixepoch', 'localtime') as day,
              b.title as book,
              round(sum(duration) / 60.0) as minutes
            from koreader_page_stats ps
            join koreader_books b on ps.id_book = b.id
            group by day, b.title
            order by day
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
            mark:
              type: bar
              tooltip: true
            encoding:
              x:
                field: day
                type: ordinal
                title: null
              y:
                field: minutes
                type: quantitative
                title: Minutes
              color:
                field: book
                type: nominal
                title: Book
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15

        k-daily-pages:
          title: Daily Pages Read
          db: KindleReader
          query: |
            select
              date(start_time, 'unixepoch', 'localtime') as day,
              b.title as book,
              count(distinct ps.page) as pages
            from koreader_page_stats ps
            join koreader_books b on ps.id_book = b.id
            group by day, b.title
            order by day
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
            mark:
              type: bar
              tooltip: true
            encoding:
              x:
                field: day
                type: ordinal
                title: null
              y:
                field: pages
                type: quantitative
                title: Pages
              color:
                field: book
                type: nominal
                title: Book
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15

        k-hourly-heatmap:
          title: Reading by Hour of Day
          db: KindleReader
          query: |
            select
              cast(strftime('%H', start_time, 'unixepoch', 'localtime') as integer) as hour,
              date(start_time, 'unixepoch', 'localtime') as day,
              round(sum(duration) / 60.0) as minutes
            from koreader_page_stats
            group by day, hour
            order by day, hour
          library: vega-lite
          display:
            mark:
              type: rect
              tooltip: true
            encoding:
              x:
                field: day
                type: ordinal
                title: null
              y:
                field: hour
                type: ordinal
                title: Hour
                sort: "ascending"
              color:
                field: minutes
                type: quantitative
                title: Minutes
                scale:
                  scheme: blues

        k-session-speed:
          title: Reading Speed (sec per page)
          db: KindleReader
          query: |
            select
              date(start_time, 'unixepoch', 'localtime') as day,
              datetime(start_time, 'unixepoch', 'localtime') as ts,
              b.title as book,
              round(avg(duration)) as sec_per_page,
              count(distinct ps.page) as pages
            from koreader_page_stats ps
            join koreader_books b on ps.id_book = b.id
            where duration > 0 and duration < 300
            group by day, b.title
            order by day
          library: vega-lite
          display:
            params:
              - name: bookFilter
                select:
                  type: point
                  fields: [book]
                bind: legend
              - name: brush
                select:
                  type: interval
                  encodings: [x]
            mark:
              type: point
              filled: true
              cursor: pointer
            encoding:
              x:
                field: ts
                type: temporal
                title: null
              y:
                field: sec_per_page
                type: quantitative
                title: Sec / Page
              color:
                condition:
                  param: brush
                  field: book
                  type: nominal
                  title: Book
                value: lightgray
              size:
                field: pages
                type: quantitative
                title: Pages
              opacity:
                condition:
                  param: bookFilter
                  value: 1
                value: 0.15
              tooltip:
                - {field: book, type: nominal, title: Book}
                - {field: ts, type: temporal, title: Time}
                - {field: sec_per_page, type: quantitative, title: "Sec/page"}
                - {field: pages, type: quantitative, title: Pages}

        k-recently-read:
          title: Recently Read
          db: KindleReader
          query: |
            select
              book_name as Title,
              cast(total_time / 3600 as text) || 'h '
                || cast((total_time % 3600) / 60 as text) || 'm' as "Time Spent",
              case when book_words > 0
                then round(total_words * 100.0 / book_words, 1) || '%'
                else '-'
              end as Progress,
              case
                when book_words > 0 and total_words * 100.0 / book_words >= 80 then 'Finished'
                when total_words > 0 then 'Reading'
                else 'Not started'
              end as Status,
              last_read as "Last Read"
            from kindle_books
            where total_time > 0
            order by last_read desc
          library: table
          display: {}

        k-currently-reading:
          title: Currently Reading
          db: KindleReader
          query: |
            select
              book_name as Title,
              cast(total_time / 3600 as text) || 'h '
                || cast((total_time % 3600) / 60 as text) || 'm' as "Time Spent",
              case when book_words > 0
                then round(total_words * 100.0 / book_words, 1) || '%'
                else '-'
              end as Progress,
              last_read as "Last Read"
            from kindle_books
            where book_words > 0 and total_words > 0
              and total_words * 100.0 / book_words < 80
            order by last_read desc
          library: table
          display: {}

        k-vocab-by-book:
          title: Vocabulary Lookups by Book
          db: KindleReader
          query: |
            select
              book_title as book,
              count(*) as lookups
            from vocab_lookups
            group by book_title
            order by lookups desc
            limit 20
          library: vega-lite
          display:
            params:
              - name: hover
                select:
                  type: point
                  "on": pointerover
                  clear: pointerout
            mark:
              type: bar
              cursor: pointer
            encoding:
              y:
                field: book
                type: nominal
                sort: "-x"
                title: null
              x:
                field: lookups
                type: quantitative
                title: Lookups
              tooltip:
                - {field: book, type: nominal, title: Book}
                - {field: lookups, type: quantitative, title: Lookups}
              fillOpacity:
                condition:
                  param: hover
                  value: 1
                value: 0.7

        k-top-words:
          title: Most Looked-Up Words
          db: KindleReader
          query: |
            select
              word,
              count(*) as lookups
            from vocab_lookups
            group by word
            order by lookups desc
            limit 30
          library: vega-lite
          display:
            params:
              - name: hover
                select:
                  type: point
                  "on": pointerover
                  clear: pointerout
            mark:
              type: bar
              cursor: pointer
            encoding:
              y:
                field: word
                type: nominal
                sort: "-x"
                title: null
              x:
                field: lookups
                type: quantitative
                title: Lookups
              tooltip:
                - {field: word, type: nominal, title: Word}
                - {field: lookups, type: quantitative, title: Lookups}
              fillOpacity:
                condition:
                  param: hover
                  value: 1
                value: 0.7

        k-clippings-timeline:
          title: Clippings Over Time
          db: KindleReader
          query: |
            select
              substr(added_date, 1, 7) as month,
              book_title as book,
              count(*) as clips
            from clippings
            where clip_type != 'bookmark' and added_date is not null
            group by month, book_title
            order by month
          library: vega-lite
          display:
            mark:
              type: bar
              tooltip: true
            encoding:
              x:
                field: month
                type: ordinal
                title: null
              y:
                field: clips
                type: quantitative
                title: Clips
              color:
                field: book
                type: nominal
                title: Book
                legend: null

        k-book-list:
          title: All Books
          db: KindleReader
          query: |
            select
              book_name as Book,
              cast(total_time / 3600 as text) || 'h '
                || cast((total_time % 3600) / 60 as text) || 'm' as "Time",
              total_words as "Words Read",
              book_words as "Book Words",
              round(total_percent, 1) || '%' as "% Read",
              last_read as "Last Read"
            from kindle_books
            order by total_time desc
          library: table
          display: {}

        k-highlights-list:
          title: Highlights & Notes
          db: KindleReader
          query: |
            select
              book_title as Book,
              clip_type as Type,
              content as Content,
              added_date as Date
            from clippings
            where clip_type != 'bookmark' and content is not null
            order by added_date desc
          library: table
          display: {}