amuse/db/db.go

package db

import (
	"notabug.org/apiote/amuse/datastructure"
	"notabug.org/apiote/amuse/config"

	"crypto/rand"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"math"
	"os"
	"sort"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

type CacheEntry struct {
	Etag string
	Data []byte
}

type EmptyError struct {
	message string
}

func (e EmptyError) Error() string {
	return e.message
}

type User struct {
	Username      string
	PasswordHash  string
	Sfa           string
	Avatar        []byte
	AvatarSmall   []byte
	IsAdmin       bool
	RecoveryCodes string
	Timezone      string
}

type Session struct {
	Id       string
	Username string
	Expiry   time.Time
	IsLong   bool
}

func Migrate() error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		return err
	}
	defer db.Close()

	_, err = db.Exec(`create table cache(uri text primary key, etag text, date date, response blob, last_hit date)`)
	if err != nil && err.Error() != "table cache already exists" {
		return err
	}
	_, err = db.Exec(`create table users(username text primary key, password text, sfa text, is_admin bool, recovery_codes text, avatar blob, avatar_small blob, timezone text)`)
	if err != nil && err.Error() != "table users already exists" {
		return err
	}
	_, err = db.Exec(`create table sessions(id text primary key, username text, expiry datetime, is_long boolean, foreign key(username) references users(username))`)
	if err != nil && err.Error() != "table sessions already exists" {
		return err
	}
	_, err = db.Exec(`create table wantlist(username text, item_type text, item_id text, primary key(username, item_type, item_id), foreign key(username) references users(username))`)
	if err != nil && err.Error() != "table wantlist already exists" {
		return err
	}
	_, err = db.Exec(`create table experiences(username text, item_type text, item_id text, time datetime, foreign key(username) references users(username), primary key(username, item_type, item_id, time))`)
	if err != nil && err.Error() != "table experiences already exists" {
		return err
	}
	_, err = db.Exec(`create table item_cache (item_type text, item_id text, cover text, status text, title text, year_start int, year_end int, based_on text, genres text, runtime int, collection int, part int, ref_count int, episodes int, primary key(item_type, item_id))`)
	if err != nil && err.Error() != "table item_cache already exists" {
		return err
	}
	return nil
}

func MakeAdmin(username string) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()
	_, err = db.Exec("update users set is_admin = 1 where username = ?", username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Update err %v\n", err)
		return err
	}
	rows, err := db.Query(`select is_admin from users where username = ?`, username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return err
	}
	defer rows.Close()

	if !rows.Next() {
		fmt.Fprintf(os.Stderr, "User %s does not exist\n", username)
		return errors.New("User does not exist")
	}
	var isAdmin bool
	err = rows.Scan(&isAdmin)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
		return err
	}
	fmt.Println(isAdmin)
	return nil
}

func InsertUser(username, password, sfaSecret, recoveryCodes string) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()
	_, err = db.Exec("insert into users values(?, ?, ?, 0, ?, '', '', 'UTC')", username, password, sfaSecret, recoveryCodes)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Inert err %v\n", err)
		return err
	}

	return nil
}

func GetUser(username string) (*User, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return nil, err
	}
	defer db.Close()
	rows, err := db.Query(`select password, sfa, recovery_codes, is_admin, avatar, avatar_small, timezone from users where username = ?`, username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return nil, err
	}
	defer rows.Close()
	if !rows.Next() {
		return nil, EmptyError{message: "User does not exist"}
	}
	user := User{Username: username}
	err = rows.Scan(&user.PasswordHash, &user.Sfa, &user.RecoveryCodes, &user.IsAdmin, &user.Avatar, &user.AvatarSmall, &user.Timezone)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
		return nil, err
	}
	return &user, nil
}

func UpdateRecoveryCodes(username, recoveryCodes string) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`update users set recovery_codes = ? where username = ?`, recoveryCodes, username)
	if err != nil {
		return err
	}

	return nil
}

func CreateSession(username string, long bool) (Session, error) {
	sessionIdRaw := make([]byte, 64)
	rand.Read(sessionIdRaw)
	sessionId := hex.EncodeToString(sessionIdRaw)

	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return Session{}, err
	}
	defer db.Close()

	var length string
	if long {
		length = "30 days"
	} else {
		length = "1 day"
	}
	_, err = db.Exec(`insert into sessions values(?, ?, datetime('now', '`+length+`'), ?)`, sessionId, username, long)
	if err != nil {
		return Session{}, err
	}

	return Session{Id: sessionId, Username: username, IsLong: long}, nil
}

func GetSession(token string) (*Session, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return nil, err
	}
	defer db.Close()

	rows, err := db.Query(`select username, expiry, is_long from sessions where id = ?`, token)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return nil, err
	}
	defer rows.Close()
	if !rows.Next() {
		return nil, EmptyError{message: "Session does not exist"}
	}
	session := Session{Id: token}
	err = rows.Scan(&session.Username, &session.Expiry, &session.IsLong)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
		return nil, err
	}
	return &session, nil
}

func ClearSessions(username string) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`delete from sessions where username = ? and expiry < datetime('now')`, username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Delete err %v\n", err)
		return err
	}

	return nil
}

func RemoveSession(username, token string) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	rows, err := db.Exec(`delete from sessions where id = ? and username = ?`, token, username)
	affected, _ := rows.RowsAffected()
	if affected == 0 {
		return EmptyError{
			message: "No session " + token + " for user " + username,
		}
	}

	return err
}

func GetItemExperiences(username, itemId string, itemType datastructure.ItemType) (map[string][]time.Time, error) {
	times := map[string][]time.Time{}
	user, err := GetUser(username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Get user err: %v\n", err)
		return times, err
	}
	location, err := time.LoadLocation(user.Timezone)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Load location err: %v\n", err)
		return times, err
	}

	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return times, err
	}
	defer db.Close()
	rows, err := db.Query(`select time, item_id from experiences where username = ? and item_type = ? and (item_id = ? or item_id like ?)`, username, itemType, itemId, itemId+"/%")
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return times, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			t  time.Time
			id string
		)
		err := rows.Scan(&t, &id)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
			return times, err
		}
		t = t.In(location)
		times[id] = append(times[id], t)
	}

	for k, v := range times {
		sort.Slice(v, func(i, j int) bool {
			return v[i].After(v[j])
		})
		times[k] = v
	}
	return times, nil
}

func AddToExperiences(username, itemId string, itemType datastructure.ItemType, datetime time.Time) (int, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return 0, err
	}
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Transaction err %s\n", err)
		return 0, err
	}
	defer tx.Rollback()

	rows, err := tx.Query(`select time from experiences where item_type = ? and item_id like ? and username = ?`, itemType, itemId, username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return 0, err
	}
	defer rows.Close()

	watchedTimes := []time.Time{}
	for rows.Next() {
		var t time.Time
		err := rows.Scan(&t)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
			return 0, err
		}
		watchedTimes = append(watchedTimes, t)
	}

	if datetime.IsZero() && len(watchedTimes) > 0 {
		return 0, datastructure.ValueError{Message: "Cannot skip watched item"}
	}

	deletedRows, err := tx.Exec(`delete from experiences where username = ? and item_type = ? and item_id = ? and time = '0001-01-01 00:00:00+00:00'`, username, itemType, itemId)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Delete err %v\n", err)
		return 0, err
	}
	deletedRowsNumber, err := deletedRows.RowsAffected()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Delete err %v\n", err)
		return 0, err
	}

	insertedRows, err := tx.Exec(`insert into experiences values(?, ?, ?, ?)`, username, itemType, itemId, datetime)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Insert err %v\n", err)
		return 0, err
	}
	insertedRowsNumber, err := insertedRows.RowsAffected()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Insert err %v\n", err)
		return 0, err
	}

	tx.Commit()

	return int(insertedRowsNumber - deletedRowsNumber), nil
}

func WatchWholeSerie(username, itemId string, episodes []string, itemType datastructure.ItemType, datetime time.Time) (int, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return 0, err
	}
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Transaction err %s\n", err)
		return 0, err
	}
	defer tx.Rollback()

	rows, err := tx.Query(`select item_id from experiences where item_type = ? and item_id like ? || '/%' and username = ?`, itemType, itemId, username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return 0, err
	}
	defer rows.Close()

	watched := map[string]int{}
	for rows.Next() {
		var watchedId string
		err := rows.Scan(&watchedId)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
			return 0, err
		}
		watched[watchedId]++
	}

	modifiedRows := 0

	for _, episodeId := range episodes {
		if watched[episodeId] > 0 {
			continue
		}
		_, err = tx.Exec(`insert into experiences values(?, ?, ?, ?)`, username, itemType, episodeId, datetime)
		if err != nil {
			if err.Error()[:6] != "UNIQUE" {
				fmt.Fprintf(os.Stderr, "Insert err %v\n", err)
				return 0, err
			} else {
				fmt.Fprintf(os.Stderr, "WARNING: Insert err: Unique constraint violation\n")
			}
		}
		modifiedRows++
	}

	err = tx.Commit()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Commit err %v\n", err)
		return 0, err
	}

	return modifiedRows, nil
}

func ClearSpecials(username, itemId string, episodes []string, itemType datastructure.ItemType) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Transaction err %s\n", err)
		return err
	}
	defer tx.Rollback()

	rows, err := tx.Query(`select item_id from experiences where item_type = ? and item_id like ? || '/S00E%' and username = ? and time = "0001-01-01 00:00:00+00:00"`, itemType, itemId, username)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err %v\n", err)
		return err
	}
	defer rows.Close()

	watched := []string{}
	for rows.Next() {
		var watchedId string
		err := rows.Scan(&watchedId)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Scan err %v\n", err)
			return err
		}
		watched = append(watched, watchedId)
	}

	seriesEpisodes := map[string]int{}
	for _, episode := range episodes {
		seriesEpisodes[episode]++
	}

	for _, episode := range watched {
		if seriesEpisodes[episode] == 0 {
			_, err = tx.Exec(`delete from experiences where item_type = ? and item_id = ? and username = ?`, itemType, episode, username)
			if err != nil {
				fmt.Fprintf(os.Stderr, "Delete err %v\n", err)
				return err
			}
		}
	}

	err = tx.Commit()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Commit err %v\n", err)
		return err
	}
	return nil
}

func AddToWantList(username, itemId string, itemType datastructure.ItemType) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`insert into wantlist values(?, ?, ?)`, username, itemType, itemId)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Insert err %v\n", err)
		return err
	}
	return nil
}

func RemoveFromWantList(username, itemId string, itemType datastructure.ItemType) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	result, err := db.Exec(`delete from wantlist where username = ? and item_type = ? and item_id = ?`, username, itemType, itemId)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Delete err %v\n", err)
		return err
	}
	rows, err := result.RowsAffected()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Delete err %v\n", err)
		return err
	}
	if rows == 0 {
		return EmptyError{
			message: "Empty delete",
		}
	}
	return nil
}

func IsOnWantList(username, itemId string, itemType datastructure.ItemType) (bool, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return false, err
	}
	defer db.Close()

	rows, err := db.Query(`select 1 from wantlist where username = ? and item_id = ? and item_type = ?`, username, itemId, string(itemType))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err: %v\n", err)
		return false, err
	}
	defer rows.Close()
	isOnlist := rows.Next()

	return isOnlist, nil
}

func SaveCacheItem(itemType datastructure.ItemType, itemId string, itemInfo datastructure.ItemInfo, refs int) error {
	if refs == 0 {
		return nil
	}

	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`insert into item_cache values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	                  on conflict(item_type, item_id) do update set ref_count = ref_count + ?`,
		itemType, itemId, itemInfo.Cover, itemInfo.Status, itemInfo.Title, itemInfo.YearStart, itemInfo.YearEnd, itemInfo.BasedOn, itemInfo.Genres, itemInfo.Runtime, itemInfo.Collection, itemInfo.Part, refs, itemInfo.Episodes, refs)
	if err != nil {
		return err
	}
	return nil
}

func UpdateCacheItem(itemType datastructure.ItemType, itemId string, itemInfo datastructure.ItemInfo) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	db.Exec(`update item_cache set cover = ?, status = ?, title = ?, year_start = ?, year_end = ?, based_on = ?, genres = ?, runtime = ?, collection = ?, part = ?, episodes = ? where item_type = ? and item_id = ?`, itemInfo.Cover, itemInfo.Status, itemInfo.Title, itemInfo.YearStart, itemInfo.YearEnd, itemInfo.BasedOn, itemInfo.Genres, itemInfo.Runtime, itemInfo.Collection, itemInfo.Part, itemInfo.Episodes, itemType, itemId)

	return nil
}

func RemoveCacheItem(itemType datastructure.ItemType, itemId string) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`update item_cache set ref_count = ref_count - 1 where item_id = ?`, itemId)

	return err
}

func CleanItemCache() error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`delete from item_cache where ref_count <= 0`)

	return err
}

func GetCacheItem(itemType datastructure.ItemType, itemId string) (*datastructure.ItemInfo, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return nil, err
	}
	defer db.Close()

	var (
		itemInfo   datastructure.ItemInfo
		itemTypeDb datastructure.ItemType
		itemIdDb   string
		refCount   int
	)

	row := db.QueryRow(`select * from cache where item_type = ? and item_id = ?`, itemType, itemId)

	err = row.Scan(&itemTypeDb, &itemIdDb, &itemInfo.Cover, &itemInfo.Status, &itemInfo.Title, &itemInfo.YearStart, &itemInfo.YearEnd, &itemInfo.BasedOn, &itemInfo.Genres, &itemInfo.Runtime, &itemInfo.Collection, &itemInfo.Part, refCount)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		} else {
			return nil, err
		}
	}
	return &itemInfo, nil
}

// ====

func GetCacheEntry(uri string) (*CacheEntry, error) {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return nil, err
	}
	defer db.Close()

	row := db.QueryRow(`select etag, response from cache where uri = ?`, uri)

	var cacheEntry CacheEntry
	err = row.Scan(&cacheEntry.Etag, &cacheEntry.Data)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		} else {
			return nil, err
		}
	}

	return &cacheEntry, err
}

func CleanCache() error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	row := db.QueryRow(`select count(*) from cache`)

	var count int
	err = row.Scan(&count)
	if err != nil {
		return err
	}

	for count > 10000 {
		_, err = db.Exec(`delete from cache where last_update = (select min(last_update) from cache)`)
		if err != nil {
			return err
		}
		count--
	}

	return nil
}

func SaveCacheEntry(uri, etag string, data []byte) error {
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return err
	}
	defer db.Close()

	_, err = db.Exec(`insert into cache values(?, ?, null, ?, datetime('now'))
	on conflict(uri) do update set etag = excluded.etag, response = excluded.response, last_hit = excluded.last_hit`, uri, etag, data)
	return err
}

func GetWatchlist(username, filter string, page int) (datastructure.Watchlist, error) {
	watchlist := datastructure.Watchlist{}
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return watchlist, err
	}
	defer db.Close()

	if page <= 0 {
		page = 1
	}

	offset := (page - 1) * 18

	//todo filter, order by

	var whereClause string
	if filter != "" {
		whereClause = "and c1.title like '%" + filter + "%'"
	}

	var pages float64
	row := db.QueryRow(`select distinct count(*) from wantlist w natural join item_cache c1 where c1.item_type = 'film' and w.username = ? `+whereClause, username)
	err = row.Scan(&pages)
	if err != nil {
		return watchlist, err
	}
	watchlist.Pages = int(math.Ceil(pages / 18))

	rows, err := db.Query(`select distinct c1.item_id, c1.cover, c1.status, c1.title, c1.year_start, c1.based_on, c1.genres, c1.runtime, c1.part, c2.part from (wantlist w natural join item_cache c1) left join (experiences e natural join item_cache c2) on(c1.part-1 = c2.part and c1.collection = c2.collection and e.username = w.username) where c1.item_type = 'film' and w.username = ? `+whereClause+` order by c1.title limit ?,18`, username, offset)

	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err: %v\n", err)
		return watchlist, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			entry    datastructure.WatchlistEntry
			prevPart *int
		)
		err := rows.Scan(&entry.Id, &entry.Cover, &entry.Status, &entry.Title, &entry.YearStart, &entry.BasedOn, &entry.Genres, &entry.Runtime, &entry.Part, &prevPart)
		if err != nil {
			fmt.Println("Scan error")
			return datastructure.Watchlist{}, err
		}

		if entry.Part > 0 && prevPart == nil {
			entry.HasPrevious = true
		}
		watchlist.List = append(watchlist.List, entry)
	}

	return watchlist, nil
}

func GetReadlist(username, filter string, page int) (datastructure.Readlist, error) {
	readlist := datastructure.Readlist{}
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return readlist, err
	}
	defer db.Close()

	if page <= 0 {
		page = 1
	}

	var pages float64
	row := db.QueryRow(`select count(*) from wantlist where item_type = 'book' and username = ?`, username)
	err = row.Scan(&pages)
	if err != nil {
		return readlist, err
	}
	readlist.Pages = int(math.Ceil(pages / 18))

	offset := (page - 1) * 18

	//todo filter, order by

	var whereClause string
	if filter != "" {
		whereClause = "and c1.title like '%" + filter + "%'"
	}

	rows, err := db.Query(`select distinct c1.item_id, c1.cover, c1.status, c1.title, c1.year_start, c1.based_on, c1.genres, c1.runtime, c1.part, c2.part from (wantlist w natural join item_cache c1) left join (experiences e natural join item_cache c2) on(c1.part-1 = c2.part and c1.collection = c2.collection and e.username = w.username) where c1.item_type = 'book' and w.username = ? `+whereClause+` order by c1.title limit ?,18`, username, offset)

	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err: %v\n", err)
		return readlist, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			entry    datastructure.ReadlistEntry
			prevPart *int
		)
		err := rows.Scan(&entry.Id, &entry.Cover, &entry.Status, &entry.Title, &entry.YearStart, &entry.BasedOn, &entry.Genres, &entry.Runtime, &entry.Part, &prevPart)
		if err != nil {
			fmt.Println("Scan error")
			return datastructure.Readlist{}, err
		}

		if entry.Part > 0 && prevPart == nil {
			entry.HasPrevious = true
		}
		readlist.List = append(readlist.List, entry)
	}

	return readlist, nil
}

func GetTvQueue(username, filter string, page int) (datastructure.TvQueue, error) {
	tvQueue := datastructure.TvQueue{}
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return tvQueue, err
	}
	defer db.Close()

	if page <= 0 {
		page = 1
	}

	var pages float64
	row := db.QueryRow(`select count(*) from wantlist where item_type = 'tvserie' and username = ?`, username)
	err = row.Scan(&pages)
	if err != nil {
		return tvQueue, err
	}
	tvQueue.Pages = int(math.Ceil(pages / 18))

	offset := (page - 1) * 18

	//todo filter, order by

	var whereClause string
	if filter != "" {
		whereClause = "and c1.title like '%" + filter + "%'"
	}

	rows, err := db.Query(`select item_id, cover, status, based_on, genres, title, year_start, year_end, substr(e.id, 1, pos-1) as series_id, episodes from wantlist w left join (select item_id as id, instr(item_id, '/') as pos from experiences where item_type = 'tvserie' group by substr(id, 1, pos-1)) e on item_id = series_id natural join item_cache c where item_type = 'tvserie' and username = ? `+whereClause+` order by title limit ?,18`, username, offset)

	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err: %v\n", err)
		return tvQueue, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			entry            datastructure.TvQueueEntry
			episodes_watched *int
			episodes_skipped *int
			series_id        *int
		)
		err := rows.Scan(&entry.Id, &entry.Cover, &entry.Status, &entry.BasedOn, &entry.Genres, &entry.Title, &entry.YearStart, &entry.YearEnd, &series_id, &entry.Episodes)
		if err != nil {
			fmt.Println("Scan error")
			return datastructure.TvQueue{}, err
		}

		if series_id != nil {
			row := db.QueryRow(`select count(time) from experiences where item_type = 'tvserie' and username = ? and item_id like ? || '/%' and time != '0001-01-01 00:00:00+00:00'`, username, *series_id)
			err = row.Scan(&episodes_watched)
			if err != nil {
				fmt.Println("Scan error")
				return datastructure.TvQueue{}, err
			}
			row = db.QueryRow(`select count(time) from experiences where item_type = 'tvserie' and username = ? and item_id like ? || '/%' and time == '0001-01-01 00:00:00+00:00'`, username, *series_id)
			err = row.Scan(&episodes_skipped)
			if err != nil {
				fmt.Println("Scan error")
				return datastructure.TvQueue{}, err
			}

			if episodes_watched == nil {
				entry.WatchedEpisodes = 0
			} else {
				entry.WatchedEpisodes = *episodes_watched
			}
			if episodes_skipped == nil {
				entry.SkippedEpisodes = 0
			} else {
				entry.SkippedEpisodes = *episodes_skipped
			}
		}

		tvQueue.List = append(tvQueue.List, entry)
	}

	return tvQueue, nil
}

func GetWantlistUris() ([]string, error) {
	uris := []string{}
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return uris, err
	}
	defer db.Close()

	rows, err := db.Query(`select item_type, item_id from wantlist where item_type in ('film', 'tvserie')`)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err: %v\n", err)
		return uris, err
	}
	defer rows.Close()
	
	for rows.Next() {
		var (
			itemType string
			itemId string
		)
		err := rows.Scan(&itemType, &itemId)
		if err != nil {
			fmt.Println("Scan error")
			return uris, err
		}
		uris = append(uris, itemType+"/"+itemId)
	}
	return uris, nil
}


func GetUserExperiences(username, filter string, page int) (datastructure.Experiences, error) {
	experiences := datastructure.Experiences{}
	db, err := sql.Open("sqlite3", config.DataHome+"/amuse.db")
	if err != nil {
		fmt.Fprintf(os.Stderr, "DB open err\n")
		return experiences, err
	}
	defer db.Close()

	if page <= 0 {
		page = 1
	}

	var pages float64
	row := db.QueryRow(`select count(*) from experiences where username = ? and time != '0001-01-01 00:00:00+00:00'`, username)
	err = row.Scan(&pages)
	if err != nil {
		return experiences, err
	}
	experiences.Pages = int(math.Ceil(pages / 18))

	offset := (page - 1) * 18

	//todo filter, order by

	var whereClause string
	if filter != "" {
		whereClause = "and c1.title like '%" + filter + "%'"
	}

	rows, err := db.Query(`select case when substr(e.item_id, 1, pos-1) = '' then e.item_id else substr(e.item_id, 1, pos-1) end as id, substr(e.item_id, pos+1) as code, e.item_type, time, title, year_start, collection, part from (select *, instr(item_id, '/') as pos from experiences) e join item_cache c on id = c.item_id and e.item_type = c.item_type where username = ? `+whereClause+` order by time desc limit ?,18;`, username, offset)

	if err != nil {
		fmt.Fprintf(os.Stderr, "Select err: %v\n", err)
		return experiences, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			entry datastructure.ExperiencesEntry
		)
		err := rows.Scan(&entry.Id, &entry.Code, &entry.Type, &entry.Datetime, &entry.Title, &entry.YearStart, &entry.Collection, &entry.Part)
		entry.Part += 1
		if err != nil {
			fmt.Println("Scan error")
			return datastructure.Experiences{}, err
		}

		if !entry.Datetime.IsZero() {
			experiences.List = append(experiences.List, entry)
		}
	}

	return experiences, nil

}