Mini Video Library

package library

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"
)

type Video struct {
	ID                                                       int64
	Path, Filename, Title                                    string
	Size                                                     int64
	Hash                                                     string
	ModifiedAt                                               int64
	Duration                                                 float64
	VideoCodec                                               string
	Width, Height                                            int
	FPS                                                      float64
	Bitrate                                                  int64
	Container                                                string
	HDR                                                      bool
	AudioJSON, SubtitleJSON, ChaptersJSON, Thumbnail, Poster string
	AddedAt                                                  int64
	LastPlayed                                               sql.NullInt64
	PlaybackPosition                                         float64
	Watched, Favorite                                        bool
	Tags, Description, ExternalIDs, AniDBJSON                string
	ParsedSeries, ReleaseGroup, DetectedQuality              string
	ParsedSeason, ParsedEpisode                              int
}
type Query struct {
	Search, Resolution, Codec, State, Sort, Collection string
	Limit, Offset                                      int
}

func applyCollection(where []string, collection string) []string {
	switch collection {
	case "continue":
		return append(where, "playback_position>0 AND watched=0")
	case "recent":
		return append(where, fmt.Sprintf("added_at>=%d", time.Now().AddDate(0, 0, -30).Unix()))
	case "unwatched":
		return append(where, "watched=0")
	case "favorites":
		return append(where, "favorite=1")
	case "no-cover":
		return append(where, "poster='' AND thumbnail=''")
	case "episodes":
		return append(where, "parsed_episode>0")
	case "4k":
		return append(where, "width>=3840")
	}
	return where
}

type Service struct{ db *sql.DB }
type Problem struct {
	Path     string `json:"path"`
	Error    string `json:"error"`
	FailedAt int64  `json:"failed_at"`
	Size     int64  `json:"size"`
	Ignored  bool   `json:"ignored"`
}
type Stats struct {
	Videos    int     `json:"videos"`
	Bytes     int64   `json:"bytes"`
	Duration  float64 `json:"duration"`
	Watched   int     `json:"watched"`
	Favorites int     `json:"favorites"`
	Problems  int     `json:"problems"`
	FourK     int     `json:"four_k"`
	FullHD    int     `json:"full_hd"`
}
type Duplicate struct {
	Hash  string `json:"hash"`
	Count int    `json:"count"`
	Bytes int64  `json:"bytes"`
	Paths string `json:"paths"`
}

func New(db *sql.DB) *Service { return &Service{db: db} }

const columns = `id,path,filename,title,size,hash,modified_at,duration,video_codec,width,height,fps,bitrate,container,hdr,audio_json,subtitle_json,chapters_json,thumbnail,poster,added_at,last_played,playback_position,watched,favorite,tags,description,external_ids,anidb_json,parsed_series,parsed_season,parsed_episode,release_group,detected_quality`

func scanVideo(s interface{ Scan(...any) error }) (Video, error) {
	var v Video
	err := s.Scan(&v.ID, &v.Path, &v.Filename, &v.Title, &v.Size, &v.Hash, &v.ModifiedAt, &v.Duration, &v.VideoCodec, &v.Width, &v.Height, &v.FPS, &v.Bitrate, &v.Container, &v.HDR, &v.AudioJSON, &v.SubtitleJSON, &v.ChaptersJSON, &v.Thumbnail, &v.Poster, &v.AddedAt, &v.LastPlayed, &v.PlaybackPosition, &v.Watched, &v.Favorite, &v.Tags, &v.Description, &v.ExternalIDs, &v.AniDBJSON, &v.ParsedSeries, &v.ParsedSeason, &v.ParsedEpisode, &v.ReleaseGroup, &v.DetectedQuality)
	return v, err
}
func (s *Service) Get(ctx context.Context, id int64) (Video, error) {
	return scanVideo(s.db.QueryRowContext(ctx, "SELECT "+columns+" FROM videos WHERE id=?", id))
}
func (s *Service) List(ctx context.Context, q Query) ([]Video, error) {
	where := []string{"1=1"}
	where = applyCollection(where, q.Collection)
	args := []any{}
	if q.Search != "" {
		where = append(where, "(filename LIKE ? OR title LIKE ? OR tags LIKE ? OR description LIKE ? OR parsed_series LIKE ?)")
		x := "%" + q.Search + "%"
		args = append(args, x, x, x, x, x)
	}
	if q.Codec != "" {
		where = append(where, "video_codec=?")
		args = append(args, q.Codec)
	}
	switch q.Resolution {
	case "4k":
		where = append(where, "width>=3840")
	case "1080p":
		where = append(where, "width>=1920 AND width<3840")
	case "720p":
		where = append(where, "width>=1280 AND width<1920")
	case "sd":
		where = append(where, "width<1280")
	}
	switch q.State {
	case "favorite":
		where = append(where, "favorite=1")
	case "watched":
		where = append(where, "watched=1")
	case "unwatched":
		where = append(where, "watched=0")
	}
	order := "added_at DESC"
	if q.Sort == "recent" {
		order = "COALESCE(last_played,0) DESC"
	}
	if q.Sort == "name" {
		order = "filename COLLATE NOCASE"
	}
	if q.Limit <= 0 || q.Limit > 200 {
		q.Limit = 60
	}
	args = append(args, q.Limit, q.Offset)
	rows, err := s.db.QueryContext(ctx, "SELECT "+columns+" FROM videos WHERE "+strings.Join(where, " AND ")+" ORDER BY "+order+" LIMIT ? OFFSET ?", args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Video
	for rows.Next() {
		v, e := scanVideo(rows)
		if e != nil {
			return nil, e
		}
		out = append(out, v)
	}
	return out, rows.Err()
}

func (s *Service) Count(ctx context.Context, q Query) (int, error) {
	where := []string{"1=1"}
	where = applyCollection(where, q.Collection)
	args := []any{}
	if q.Search != "" {
		where = append(where, "(filename LIKE ? OR title LIKE ? OR tags LIKE ? OR description LIKE ? OR parsed_series LIKE ?)")
		x := "%" + q.Search + "%"
		args = append(args, x, x, x, x, x)
	}
	if q.Codec != "" {
		where = append(where, "video_codec=?")
		args = append(args, q.Codec)
	}
	switch q.Resolution {
	case "4k":
		where = append(where, "width>=3840")
	case "1080p":
		where = append(where, "width>=1920 AND width<3840")
	case "720p":
		where = append(where, "width>=1280 AND width<1920")
	case "sd":
		where = append(where, "width<1280")
	}
	switch q.State {
	case "favorite":
		where = append(where, "favorite=1")
	case "watched":
		where = append(where, "watched=1")
	case "unwatched":
		where = append(where, "watched=0")
	}
	var count int
	err := s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM videos WHERE "+strings.Join(where, " AND "), args...).Scan(&count)
	return count, err
}
func (s *Service) UpdateProgress(ctx context.Context, id int64, pos float64, watched *bool) error {
	w := "watched"
	args := []any{pos, time.Now().Unix()}
	if watched != nil {
		w = "?"
		args = append(args, *watched)
	}
	args = append(args, id)
	r, err := s.db.ExecContext(ctx, "UPDATE videos SET playback_position=?,last_played=?,watched="+w+" WHERE id=?", args...)
	if err != nil {
		return err
	}
	n, _ := r.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}
func (s *Service) ToggleFavorite(ctx context.Context, id int64) error {
	r, e := s.db.ExecContext(ctx, "UPDATE videos SET favorite=1-favorite WHERE id=?", id)
	if e == nil {
		n, _ := r.RowsAffected()
		if n == 0 {
			return fmt.Errorf("video not found")
		}
	}
	return e
}
func (s *Service) Problems(ctx context.Context) ([]Problem, error) {
	rows, e := s.db.QueryContext(ctx, "SELECT path,error,failed_at,size,ignored FROM scan_errors ORDER BY failed_at DESC")
	if e != nil {
		return nil, e
	}
	defer rows.Close()
	var out []Problem
	for rows.Next() {
		var p Problem
		if e = rows.Scan(&p.Path, &p.Error, &p.FailedAt, &p.Size, &p.Ignored); e != nil {
			return nil, e
		}
		out = append(out, p)
	}
	return out, rows.Err()
}
func (s *Service) IgnoreProblem(ctx context.Context, path string) error {
	_, e := s.db.ExecContext(ctx, "UPDATE scan_errors SET ignored=1 WHERE path=?", path)
	return e
}
func (s *Service) ClearProblems(ctx context.Context) error {
	_, e := s.db.ExecContext(ctx, "DELETE FROM scan_errors WHERE ignored=0")
	return e
}
func (s *Service) Stats(ctx context.Context) (Stats, error) {
	var x Stats
	e := s.db.QueryRowContext(ctx, `SELECT COUNT(*),COALESCE(SUM(size),0),COALESCE(SUM(duration),0),COALESCE(SUM(watched),0),COALESCE(SUM(favorite),0),COALESCE(SUM(width>=3840),0),COALESCE(SUM(width>=1920 AND width<3840),0) FROM videos`).Scan(&x.Videos, &x.Bytes, &x.Duration, &x.Watched, &x.Favorites, &x.FourK, &x.FullHD)
	if e == nil {
		e = s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM scan_errors WHERE ignored=0").Scan(&x.Problems)
	}
	return x, e
}
func (s *Service) Duplicates(ctx context.Context) ([]Duplicate, error) {
	rows, e := s.db.QueryContext(ctx, `SELECT hash,COUNT(*),SUM(size),GROUP_CONCAT(path,char(10)) FROM videos WHERE hash<>'' GROUP BY hash HAVING COUNT(*)>1 ORDER BY SUM(size) DESC LIMIT 200`)
	if e != nil {
		return nil, e
	}
	defer rows.Close()
	var out []Duplicate
	for rows.Next() {
		var d Duplicate
		if e = rows.Scan(&d.Hash, &d.Count, &d.Bytes, &d.Paths); e != nil {
			return nil, e
		}
		out = append(out, d)
	}
	return out, rows.Err()
}
func (s *Service) UpdateMetadata(ctx context.Context, id int64, title, description, tags string) error {
	_, e := s.db.ExecContext(ctx, "UPDATE videos SET title=?,description=?,tags=? WHERE id=?", strings.TrimSpace(title), strings.TrimSpace(description), strings.TrimSpace(tags), id)
	return e
}
func (s *Service) ResetProgress(ctx context.Context, id int64) error {
	_, e := s.db.ExecContext(ctx, "UPDATE videos SET playback_position=0,last_played=NULL,watched=0 WHERE id=?", id)
	return e
}