track Spotify play history similar to Now Playing?

[lydavid]

• don't increment the same track's listen count if the elapsed time is less than its track length. Rather than calculating this ourselves, https://developer.spotify.com/documentation/android/tutorials/android-media-notifications provides us timeSent which we can use as part of the primary key when inserting
• don't increment the current track if nothing is playing. Even if nothing is playing, our broadcast receiver will have Spotify's last played track
• would be nice to have broadcast listener start from home screen, or in background, so that no matter what screen user is on, it will keep recording. see now playing
• instead of counting how many times the user listened to a track, track it chronologically similar to Spotify's Recently played. later, we can group by track_id if we want that info
• overlay to nav to search artist/rg/rec? is presenter overkill here?

[lydavid]

SQLDelight seems to generate this upsert function with a Long type if I used number_of_listens = :numberOfListens + 1,, whereas number_of_listens = number_of_listens + 1, will make it an Int, which is what I want.

upsert {
  UPDATE spotify_history
  SET artist_name = :artistName,
    album_name = :albumName,
    track_name = :trackName,
    track_length = :trackLength,
    number_of_listens = number_of_listens + 1,
    last_listened = :lastListened
  WHERE track_id = :trackId;

  INSERT OR IGNORE INTO spotify_history
  VALUES (
    :trackId,
    :artistName,
    :albumName,
    :trackName,
    :trackLength,
    :numberOfListens,
    :lastListened
  );
}

The table for reference:

CREATE TABLE IF NOT EXISTS spotify_history (
  `track_id` TEXT PRIMARY KEY NOT NULL,
  `artist_name` TEXT,
  `album_name` TEXT,
  `track_name` TEXT,
  `track_length` INTEGER AS Int,
  `number_of_listens` INTEGER AS Int NOT NULL,
  `last_listened` INTEGER AS Instant NOT NULL
);

[lydavid]

An interesting observation while working with sql and lazy Compose is we will often use the primary key as the id.
eg. id = "$trackId$lastListened",