Import listens from lastfm

8 files changed, 412 insertions, 67 deletions

diff --git a/apiv1.go b/apiv1.go
index 4cf8941..c209f53 100644
--- a/apiv1.go
+++ b/apiv1.go
@@ -219,6 +219,7 @@ func (app *App) GetScrobbleSong(s *Scrobble) {
 		TrackNumber: s.TrackNumber,
 		Duration:    s.Duration * 1000,
 		Mbid:        s.Mbid,
+		Image:       s.Image,
 	}
 	app.GetSong(&song)
 	s.SongId = song.Id
diff --git a/data.go b/data.go
index 5f1f0fb..3b4a783 100644
--- a/data.go
+++ b/data.go
@@ -46,6 +46,14 @@ type Song struct {
 	Image       string
 }
 
+type Import struct {
+	LfmName   string
+	From      time.Time
+	To        time.Time
+	LastFetch time.Time
+	Done      bool
+}
+
 type DataStore interface {
 	PutSession(*Session) error
 	GetSession(key string) (*Session, error)
@@ -62,6 +70,12 @@ type DataStore interface {
 	InsertUser(u *User) error
 	SaveUser(u *User) error
 
+	GetImport(i *Import) error
+	SaveImport(i *Import) error
+	InsertImport(i *Import) error
+	NewImport(name string) *Import
+	ImportStats(user int) (time.Time, int, error)
+
 	InsertUserSession(s *UserSession) error
 	Api
 }
@@ -179,12 +193,18 @@ func (store *SqlStore) NowPlaying(userId int) *Scrobble {
 }
 
 func (store *SqlStore) GetSongId(s *Song) error {
-	query := `
-	SELECT song_id FROM songs
-	WHERE artist=$1 AND album=$2 AND name=$3
-	`
-	row := store.QueryRow(query, s.Artist, s.Album, s.Name)
-	return row.Scan(&s.Id)
+	var query string
+	if s.Mbid != "" {
+		query = `SELECT song_id FROM songs WHERE mbid=$1`
+		row := store.QueryRow(query, s.Mbid)
+		return row.Scan(&s.Id)
+	} else {
+		query = `
+		SELECT song_id FROM songs
+		WHERE artist=$1 AND album=$2 AND name=$3`
+		row := store.QueryRow(query, s.Artist, s.Album, s.Name)
+		return row.Scan(&s.Id)
+	}
 }
 
 func (store *SqlStore) InsertSong(s *Song) error {
@@ -245,3 +265,71 @@ func (store *SqlStore) InsertUserSession(s *UserSession) error {
 	_, err := store.Exec(query, &s.Id, &s.UserId)
 	return err
 }
+
+func (store *SqlStore) GetImport(i *Import) error {
+	query := `
+	SELECT last_fetch
+	FROM scrobble_import WHERE lfm_name = $1 and "from"=$2 and "to"=$3`
+	row := store.QueryRow(query, i.LfmName, i.From, i.To)
+	return row.Scan(&i.LastFetch)
+}
+
+func (store *SqlStore) SaveImport(i *Import) error {
+	query := `UPDATE scrobble_import SET last_fetch=$1, done=$5
+	WHERE "from"=$2 and "to"=$3 and lfm_name=$4`
+	_, err := store.Exec(query, i.LastFetch, i.From, i.To, i.LfmName, i.Done)
+	return err
+}
+
+func (store *SqlStore) InsertImport(i *Import) error {
+	query := `
+	INSERT into scrobble_import (lfm_name, "from", "to", last_fetch, done)
+	VALUES ($1, $2, $3, $4, $5)`
+	_, err := store.Exec(query, i.LfmName, i.From, i.To, i.LastFetch, i.Done)
+	return err
+}
+
+func (store *SqlStore) NewImport(name string) *Import {
+	i := &Import{
+		LfmName: name,
+		To:      time.Now(),
+	}
+	query := `SELECT max("to") FROM scrobble_import WHERE lfm_name=$1`
+	row := store.QueryRow(query, i.LfmName)
+	if row.Scan(&i.From) == nil {
+		i.From = i.From.Add(time.Second)
+	}
+	if err := store.InsertImport(i); err != nil {
+		log.Println(err)
+	}
+	return i
+}
+
+func (store *SqlStore) ImportStats(user int) (time.Time, int, error) {
+	query := `
+	SELECT "last", ct
+	FROM (
+		SELECT max("to") "last", lfm_name
+		FROM scrobble_import
+		GROUP BY lfm_name
+	) i
+	JOIN (
+		SELECT count(*) ct, max(lfm_name) n
+		FROM scrobbles
+		JOIN scrobbling_sessions
+		ON scrobbles.session_key = scrobbling_sessions.session_key
+		JOIN  users
+		ON scrobbles.user_id = users.user_id
+		WHERE users.user_id = $1
+		AND client = 'import'
+	) s
+	ON lfm_name = n`
+	var t time.Time
+	var count int
+	row := store.QueryRow(query, user)
+	if err := row.Scan(&t, &count); err != nil {
+		return t, count, err
+	} else {
+		return t, count, nil
+	}
+}
diff --git a/lfmclient.go b/lfmclient.go
index 41ecd37..1b39ce9 100644
--- a/lfmclient.go
+++ b/lfmclient.go
@@ -2,10 +2,11 @@ package main
 
 import (
 	"encoding/json"
-	"fmt"
 	"io/ioutil"
 	"log"
 	"net/http"
+	"strconv"
+	"time"
 )
 
 type Image struct {
@@ -19,6 +20,7 @@ type AlbumInfo struct {
 	Name     string `json:"title"`
 	Url      string
 	Position `json:"@attr"`
+	Artist   string
 }
 
 type Date struct {
@@ -44,11 +46,29 @@ type Position struct {
 }
 
 type TrackInfo struct {
-	Name     string
-	Mbid     string
-	Duration string
-	Artist   ArtistInfo
-	Album    AlbumInfo
+	Name      string
+	Mbid      string
+	Duration  string
+	Artist    ArtistInfo
+	Album     AlbumInfo
+	PlayCount string
+	Listeners string
+}
+
+type RecentTrack struct {
+	Name   string
+	Mbid   string
+	Url    string
+	Artist struct {
+		Name string `json:"#text"`
+		Mbid string
+	}
+	Album struct {
+		Name string `json:"#text"`
+		Mbid string
+	}
+	Images []Image `json:"image"`
+	Date   Date
 }
 
 func (app *App) LfmQuery(payload map[string]string) []byte {
@@ -66,8 +86,7 @@ func (app *App) LfmQuery(payload map[string]string) []byte {
 	return body
 }
 
-func (a AlbumInfo) GetImage(size string) string {
-	images := a.Images
+func GetImage(images []Image, size string) string {
 	for _, image := range images {
 		if image.Size == size {
 			return image.Href
@@ -99,13 +118,75 @@ func (app *App) TrackInfo(artist, name string) TrackInfo {
 	return dst["track"]
 }
 
-func (app *App) RecentTracks(user string) {
-	r := app.LfmQuery(map[string]string{
+func (app *App) ImportRecentTracks(user *User) {
+	s := &Session{
+		UserId:        user.Id,
+		Client:        "import",
+		ClientVersion: "",
+		Key:           randomToken(16),
+		Protocol:      "1.2.1",
+	}
+	app.PutSession(s)
+	i := app.NewImport(user.LfmName)
+	payload := map[string]string{
 		"method": "user.getRecentTracks",
-		"limit":  "10",
-		"user":   user,
-	})
-	fmt.Println(string(r))
+		"limit":  "200",
+		"user":   user.LfmName,
+		"to":     strconv.Itoa(int(i.To.Unix())),
+	}
+	if i.From.IsZero() {
+		payload["from"] = "0"
+	} else {
+		payload["from"] = strconv.Itoa(int(i.From.Unix()))
+	}
+	var dst map[string]struct {
+		Attrs  PageAttrs     `json:"@attr"`
+		Tracks []RecentTrack `json:"track"`
+	}
+	scrobbles := make([]Scrobble, 0, 200)
+	var st time.Time
+	for {
+		scrobbles = scrobbles[:0]
+		if !i.LastFetch.IsZero() {
+			payload["to"] = strconv.Itoa(int(i.LastFetch.Unix()))
+		}
+		r := app.LfmQuery(payload)
+		json.Unmarshal(r, &dst)
+		tracks := dst["recenttracks"].Tracks
+		if len(tracks) == 0 {
+			i.Done = true
+			break
+		}
+		for _, t := range tracks {
+			ts, _ := strconv.Atoi(t.Date.Uts)
+			if ts == 0 {
+				continue
+			}
+			st = time.Unix(int64(ts), 0)
+			scrobble := Scrobble{
+				Artist:     NewCorrectable(t.Artist.Name),
+				Album:      NewCorrectable(t.Album.Name),
+				TrackName:  NewCorrectable(t.Name),
+				Mbid:       t.Mbid,
+				Time:       st,
+				UserId:     user.Id,
+				SessionKey: s.Key,
+				Image:      GetImage(t.Images, "medium"),
+			}
+			app.GetScrobbleSong(&scrobble)
+			scrobbles = append(scrobbles, scrobble)
+		}
+		if err := app.PutScrobbles(scrobbles); err != nil {
+			log.Println(err)
+		}
+		i.LastFetch = st
+		if err := app.SaveImport(i); err != nil {
+			log.Println(err)
+		}
+	}
+	if err := app.SaveImport(i); err != nil {
+		log.Println(err)
+	}
 }
 
 func (app *App) LovedTracks(user string) []TrackInfo {
@@ -125,10 +206,11 @@ func (app *App) LovedTracks(user string) []TrackInfo {
 }
 
 func (app *App) GetSong(s *Song) {
-	if s.Album != "" {
+	if s.Album != "" || s.Mbid != "" {
 		if app.GetSongId(s) != nil {
 			if s.Image == "" {
-				s.Image = app.AlbumInfo(s.Artist, s.Album).GetImage("medium")
+				s.Image = GetImage(app.AlbumInfo(s.Artist, s.Album).Images,
+					"medium")
 			}
 			if err := app.InsertSong(s); err != nil {
 				log.Println(err)
@@ -138,7 +220,9 @@ func (app *App) GetSong(s *Song) {
 		t := app.TrackInfo(s.Artist, s.Name)
 		s.Album = t.Album.Name
 		s.Mbid = t.Mbid
-		s.Image = t.Album.GetImage("medium")
-		app.InsertSong(s)
+		s.Image = GetImage(t.Album.Images, "medium")
+		if app.GetSongId(s) != nil {
+			app.InsertSong(s)
+		}
 	}
 }
diff --git a/migrations/20170617130303_import.sql b/migrations/20170617130303_import.sql
new file mode 100644
index 0000000..d897366
--- /dev/null
+++ b/migrations/20170617130303_import.sql
@@ -0,0 +1,19 @@
+
+-- +goose Up
+-- SQL in section 'Up' is executed when this migration is applied
+
+CREATE TABLE IF NOT EXISTS scrobble_import (
+	lfm_name text,
+	"from" timestamptz,
+	"to" timestamptz,
+	last_fetch timestamptz,
+	done bool,
+	PRIMARY KEY (lfm_name, "from", "to")
+);
+
+CREATE INDEX scrobbles_time ON scrobbles(time DESC);
+
+-- +goose Down
+-- SQL section 'Down' is executed when this migration is rolled back
+DROP TABLE scrobble_import;
+DROP INDEX scrobbles_time;
diff --git a/schema.sql b/schema.sql
index b65ae6b..24775db 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,51 +1,183 @@
-CREATE TABLE IF NOT EXISTS users (
-	user_id SERIAL PRIMARY KEY,
-	type text,
-	op_id text,
-	name text,
-	email text,
-	lfm_name text UNIQUE,
-	lfm_password text,
-	created timestamptz DEFAULT current_timestamp
+
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET idle_in_transaction_session_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET row_security = off;
+
+SET search_path = public, pg_catalog;
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+CREATE TABLE goose_db_version (
+    id integer NOT NULL,
+    version_id bigint NOT NULL,
+    is_applied boolean NOT NULL,
+    tstamp timestamp without time zone DEFAULT now()
+);
+
+CREATE SEQUENCE goose_db_version_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER SEQUENCE goose_db_version_id_seq OWNED BY goose_db_version.id;
+
+CREATE TABLE now_playing (
+    artist text,
+    album_artist text,
+    track_name text,
+    album text,
+    track_number integer,
+    duration integer,
+    received timestamp with time zone DEFAULT now(),
+    mbid text,
+    song_id integer,
+    session_key text,
+    user_id integer
+);
+
+CREATE TABLE scrobble_import (
+    lfm_name text NOT NULL,
+    "from" timestamp with time zone NOT NULL,
+    "to" timestamp with time zone NOT NULL,
+    last_fetch timestamp with time zone,
+    done boolean
+);
+
+CREATE TABLE scrobbles (
+    artist text,
+    album_artist text,
+    track_name text,
+    album text,
+    track_number integer,
+    duration integer,
+    "time" timestamp with time zone,
+    chosen boolean,
+    mbid text,
+    song_id integer,
+    session_key text,
+    user_id integer
 );
 
-CREATE TABLE IF NOT EXISTS scrobbling_sessions (
-    session_key text PRIMARY KEY,
-	user_id int REFERENCES users,
+CREATE TABLE scrobbling_sessions (
+    session_key text NOT NULL,
+    user_id integer,
     client text,
-	client_version text,
+    client_version text,
     protocol text,
-	created timestamptz DEFAULT current_timestamp
+    created timestamp with time zone DEFAULT now()
 );
 
-CREATE TABLE IF NOT EXISTS songs (
-	song_id SERIAL PRIMARY KEY,
-	artist text,
-	album text,
-	name text,
-	track_number text,
-	duration text,
-	mbid text,
-	image text
+CREATE TABLE songs (
+    song_id integer NOT NULL,
+    artist text,
+    album text,
+    name text,
+    track_number text,
+    duration text,
+    mbid text,
+    image text
 );
 
-CREATE TABLE IF NOT EXISTS scrobbles (
-	artist text,
-	album_artist text,
-	track_name text,
-	album text,
-	track_number int,
-	duration int,
-	time timestamptz,
-	chosen bool,
-	mbid text,
-	song_id int REFERENCES songs,
-	session_key text REFERENCES scrobbling_sessions,
-	user_id int REFERENCES users
+CREATE SEQUENCE songs_song_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER SEQUENCE songs_song_id_seq OWNED BY songs.song_id;
+
+CREATE TABLE user_sessions (
+    id text NOT NULL,
+    user_id integer,
+    created timestamp with time zone DEFAULT now()
 );
 
-CREATE TABLE IF NOT EXISTS user_sessions (
-	id text PRIMARY KEY,
-	user_id integer REFERENCES users,
-	created timestamptz DEFAULT current_timestamp
+CREATE TABLE users (
+    user_id integer NOT NULL,
+    type text,
+    op_id text,
+    name text,
+    email text,
+    lfm_name text,
+    lfm_password text,
+    created timestamp with time zone DEFAULT now()
 );
+
+CREATE SEQUENCE users_user_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;
+
+ALTER TABLE ONLY goose_db_version ALTER COLUMN id SET DEFAULT nextval('goose_db_version_id_seq'::regclass);
+
+ALTER TABLE ONLY songs ALTER COLUMN song_id SET DEFAULT nextval('songs_song_id_seq'::regclass);
+
+ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);
+
+ALTER TABLE ONLY goose_db_version
+    ADD CONSTRAINT goose_db_version_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY now_playing
+    ADD CONSTRAINT now_playing_user_id_key UNIQUE (user_id);
+
+ALTER TABLE ONLY users
+    ADD CONSTRAINT op UNIQUE (type, op_id);
+
+ALTER TABLE ONLY scrobble_import
+    ADD CONSTRAINT scrobble_import_pkey PRIMARY KEY (lfm_name, "from", "to");
+
+ALTER TABLE ONLY scrobbling_sessions
+    ADD CONSTRAINT scrobbling_sessions_pkey PRIMARY KEY (session_key);
+
+ALTER TABLE ONLY songs
+    ADD CONSTRAINT songs_pkey PRIMARY KEY (song_id);
+
+ALTER TABLE ONLY user_sessions
+    ADD CONSTRAINT user_sessions_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY users
+    ADD CONSTRAINT users_lfm_name_key UNIQUE (lfm_name);
+
+ALTER TABLE ONLY users
+    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
+
+CREATE INDEX scrobbles_time ON scrobbles USING btree ("time" DESC);
+
+ALTER TABLE ONLY now_playing
+    ADD CONSTRAINT now_playing_session_key_fkey FOREIGN KEY (session_key) REFERENCES scrobbling_sessions(session_key);
+
+ALTER TABLE ONLY now_playing
+    ADD CONSTRAINT now_playing_song_id_fkey FOREIGN KEY (song_id) REFERENCES songs(song_id);
+
+ALTER TABLE ONLY now_playing
+    ADD CONSTRAINT now_playing_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
+
+ALTER TABLE ONLY scrobbles
+    ADD CONSTRAINT scrobbles_session_key_fkey FOREIGN KEY (session_key) REFERENCES scrobbling_sessions(session_key);
+
+ALTER TABLE ONLY scrobbles
+    ADD CONSTRAINT scrobbles_song_id_fkey FOREIGN KEY (song_id) REFERENCES songs(song_id);
+
+ALTER TABLE ONLY scrobbles
+    ADD CONSTRAINT scrobbles_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
+
+ALTER TABLE ONLY scrobbling_sessions
+    ADD CONSTRAINT scrobbling_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
+
+ALTER TABLE ONLY user_sessions
+    ADD CONSTRAINT user_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);
+
diff --git a/static/style.css b/static/style.css
index 4072988..39fc0d5 100644
--- a/static/style.css
+++ b/static/style.css
@@ -175,6 +175,10 @@ input:focus+label {
 	padding: 0 16px;
 }
 
+form p {
+	padding: 0 16px;
+}
+
 .form-element {
 	display: flex;
 	flex-direction: column-reverse;
diff --git a/templates/settings.tmpl b/templates/settings.tmpl
index 9c68b16..da3dabb 100644
--- a/templates/settings.tmpl
+++ b/templates/settings.tmpl
@@ -29,9 +29,15 @@
 	</div>
 	</div>
 
+	<p>
+		{{if not .LastImport.IsZero}}
+			{{.ImportCount}} listens imported from Last.fm (last import on {{.LastImport.Format "Mon, Jan 2 15:04"}}).
+		{{end}}
+		<input type="submit" name="import" value="IMPORT">
+	</p>
 </section>
 <div class="row">
-<input type="submit" value="SAVE">
+<input type="submit" name="save" value="SAVE">
 </div>
 </form>
 {{template "footer"}}
diff --git a/web.go b/web.go
index 371ae9f..73871b3 100644
--- a/web.go
+++ b/web.go
@@ -111,7 +111,7 @@ func (app *App) settings(w http.ResponseWriter, r *http.Request) {
 		return
 	}
 
-	if r.Method == "POST" {
+	if r.Method == "POST" && r.FormValue("save") != "" {
 		u := &User{
 			Id:      se.UserId,
 			Name:    r.FormValue("name"),
@@ -128,14 +128,25 @@ func (app *App) settings(w http.ResponseWriter, r *http.Request) {
 		app.SetCookie(w, "session", se, 86400*30)
 	}
 
+	if r.Method == "POST" && r.FormValue("import") != "" {
+		u := &User{
+			Id: se.UserId,
+		}
+		app.GetUser(u)
+		go app.ImportRecentTracks(u)
+	}
+
 	user := &User{Id: se.UserId}
 	if err := app.GetUser(user); err != nil {
 		log.Println(err)
 	}
+	li, ct, err := app.ImportStats(user.Id)
 	err = app.Template.ExecuteTemplate(w, "settings.tmpl", struct {
 		Session *UserSession
 		*User
-	}{Session: se, User: user})
+		LastImport  time.Time
+		ImportCount int
+	}{Session: se, User: user, LastImport: li, ImportCount: ct})
 	if err != nil {
 		log.Println(err)
 	}