unified-media-manager/internal/migrate/reader.go

package migrate

import (
	"database/sql"
	"encoding/json"
	"fmt"

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

// SonarrSeries represents a series from Sonarr's SQLite database.
type SonarrSeries struct {
	ID               int64
	TVDBID          int64
	Title            string
	SortTitle        string
	Year             int
	Status           string
	Monitored        bool
	QualityProfileID int64
	RootFolderPath   string
	Tags             string // JSON array of tag IDs
	Overview         string
	Images           string // JSON
	Runtime          int
}

// SonarrEpisode represents an episode from Sonarr's SQLite database.
type SonarrEpisode struct {
	ID            int64
	SeriesID      int64
	SeasonNumber  int
	EpisodeNumber int
	Title         string
	AirDate       string
	Monitored     bool
	HasFile       bool
}

// EpisodeFile represents an episode file from Sonarr's SQLite database.
type EpisodeFile struct {
	ID           int64
	SeriesID     int64
	SeasonNumber int
	RelativePath string
	Path         string
	Size         int64
	Quality      string // JSON
	DateAdded    string
}

// RadarrMovie represents a movie from Radarr's SQLite database.
type RadarrMovie struct {
	ID               int64
	TMDBID          int64
	Title            string
	SortTitle        string
	Year             int
	Status           string
	Monitored        bool
	QualityProfileID int64
	RootFolderPath   string
	HasFile          bool
	MovieFileID      int64
	Overview         string
	Images           string // JSON
	Runtime          int
}

// MovieFile represents a movie file from Radarr's SQLite database.
type MovieFile struct {
	ID           int64
	MovieID      int64
	RelativePath string
	Path         string
	Size         int64
	Quality      string // JSON
	DateAdded    string
}

// LidarrArtist represents an artist from Lidarr's SQLite database.
type LidarrArtist struct {
	ID               int64
	ForeignArtistID  string // MusicBrainz ID
	Name             string
	Status           string
	Monitored        bool
	QualityProfileID int64
	RootFolderPath   string
	Overview         string
	Images           string
}

// LidarrAlbum represents an album from Lidarr's SQLite database.
type LidarrAlbum struct {
	ID             int64
	ArtistID       int64
	ForeignAlbumID string // MusicBrainz ID
	Title          string
	Year           int
	Monitored      bool
	AlbumType      string
}

// ReadarrBook represents a book from Readarr's SQLite database.
type ReadarrBook struct {
	ID                int64
	ForeignBookID     string // ISBN or Goodreads ID
	Title             string
	AuthorID          int64
	AuthorName        string
	Monitored         bool
	QualityProfileID  int64
	Overview          string
	Images            string
}

// ProwlarrIndexer represents an indexer from Prowlarr's SQLite database.
type ProwlarrIndexer struct {
	ID             int64
	Name           string
	Implementation string
	Settings       string // JSON with url, apiKey
	Enable         bool
	Priority       int
}

// ArrQualityProfile represents a quality profile from any arr app.
type ArrQualityProfile struct {
	ID     int64
	Name   string
	Items  string // JSON
	Cutoff int64
}

// ArrRootFolder represents a root folder from any arr app.
type ArrRootFolder struct {
	ID   int64
	Path string
}

// ArrBlocklistEntry represents a blocklist entry from any arr app.
type ArrBlocklistEntry struct {
	ID          int64
	Title       string
	Quality     string
	SourceTitle string
	Date        string
	TorrentHash string
	Size        int64
	Protocol    string
	Message     string
}

// ArrTag represents a tag from any arr app.
type ArrTag struct {
	ID    int64
	Label string
}

// TrackFile represents a music track file from Lidarr's SQLite database.
type TrackFile struct {
	ID           int64
	ArtistID     int64
	AlbumID      int64
	RelativePath string
	Path         string
	Size         int64
	Quality      string // JSON
	DateAdded    string
}

// BookFile represents a book file from Readarr's SQLite database.
type BookFile struct {
	ID           int64
	BookID       int64
	RelativePath string
	Path         string
	Size         int64
	Quality      string // JSON
	DateAdded    string
}

// QualityItem represents a node in the arr quality profile Items JSON tree.
type QualityItem struct {
	Quality *QualityDef    `json:"quality"`
	Items   []QualityItem  `json:"items"`
	Allowed bool           `json:"allowed"`
	ID      int64          `json:"id"`
	Name    string         `json:"name"`
}

// QualityDef represents a quality definition from arr.
type QualityDef struct {
	ID         int64  `json:"id"`
	Name       string `json:"name"`
	Source     string `json:"source"`
	Resolution int    `json:"resolution"`
}

// ArrReader reads from arr SQLite databases.
type ArrReader struct {
	db *sql.DB
}

// NewArrReader opens a read-only connection to an arr SQLite database.
func NewArrReader(dbPath string) (*ArrReader, error) {
	db, err := sql.Open("sqlite3", dbPath+"?mode=ro")
	if err != nil {
		return nil, fmt.Errorf("open sqlite %s: %w", dbPath, err)
	}
	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("ping sqlite %s: %w", dbPath, err)
	}
	return &ArrReader{db: db}, nil
}

// Close closes the SQLite connection.
func (r *ArrReader) Close() error {
	if r.db != nil {
		return r.db.Close()
	}
	return nil
}

// ReadSonarrSeries reads all series from a Sonarr database.
func (r *ArrReader) ReadSonarrSeries() ([]SonarrSeries, error) {
	rows, err := r.db.Query(`
		SELECT Id, TvdbId, Title, COALESCE(SortTitle, Title), COALESCE(Year, 0),
		       COALESCE(Status, ''), Monitored,
		       COALESCE(QualityProfileId, 0), COALESCE(RootFolderPath, ''),
		       COALESCE(Tags, '[]'), COALESCE(Overview, ''),
		       COALESCE(Images, '[]'), COALESCE(Runtime, 0)
		FROM Series`)
	if err != nil {
		return nil, fmt.Errorf("query sonarr series: %w", err)
	}
	defer rows.Close()

	var series []SonarrSeries
	for rows.Next() {
		var s SonarrSeries
		if err := rows.Scan(&s.ID, &s.TVDBID, &s.Title, &s.SortTitle, &s.Year,
			&s.Status, &s.Monitored, &s.QualityProfileID, &s.RootFolderPath,
			&s.Tags, &s.Overview, &s.Images, &s.Runtime); err != nil {
			continue
		}
		series = append(series, s)
	}
	return series, nil
}

// ReadSonarrEpisodes reads episodes for a specific series.
func (r *ArrReader) ReadSonarrEpisodes(seriesID int64) ([]SonarrEpisode, error) {
	rows, err := r.db.Query(`
		SELECT Id, SeriesId, SeasonNumber, EpisodeNumber, Title,
		       COALESCE(AirDate, ''), Monitored, COALESCE(HasFile, 0)
		FROM Episodes WHERE SeriesId = ?`, seriesID)
	if err != nil {
		return nil, fmt.Errorf("query sonarr episodes: %w", err)
	}
	defer rows.Close()

	var episodes []SonarrEpisode
	for rows.Next() {
		var e SonarrEpisode
		if err := rows.Scan(&e.ID, &e.SeriesID, &e.SeasonNumber, &e.EpisodeNumber,
			&e.Title, &e.AirDate, &e.Monitored, &e.HasFile); err != nil {
			continue
		}
		episodes = append(episodes, e)
	}
	return episodes, nil
}

// ReadEpisodeFiles reads all episode files from a Sonarr database.
func (r *ArrReader) ReadEpisodeFiles() ([]EpisodeFile, error) {
	rows, err := r.db.Query(`
		SELECT Id, SeriesId, SeasonNumber, COALESCE(RelativePath, ''),
		       COALESCE(Path, ''), COALESCE(Size, 0),
		       COALESCE(Quality, '{}'), COALESCE(DateAdded, '')
		FROM EpisodeFiles`)
	if err != nil {
		return nil, fmt.Errorf("query episode files: %w", err)
	}
	defer rows.Close()

	var files []EpisodeFile
	for rows.Next() {
		var f EpisodeFile
		if err := rows.Scan(&f.ID, &f.SeriesID, &f.SeasonNumber, &f.RelativePath,
			&f.Path, &f.Size, &f.Quality, &f.DateAdded); err != nil {
			continue
		}
		files = append(files, f)
	}
	return files, nil
}

// ReadRadarrMovies reads all movies from a Radarr database.
func (r *ArrReader) ReadRadarrMovies() ([]RadarrMovie, error) {
	rows, err := r.db.Query(`
		SELECT Id, TmdbId, Title, COALESCE(SortTitle, Title), COALESCE(Year, 0),
		       COALESCE(Status, ''), Monitored,
		       COALESCE(QualityProfileId, 0), COALESCE(RootFolderPath, ''),
		       COALESCE(HasFile, 0), COALESCE(MovieFileId, 0),
		       COALESCE(Overview, ''), COALESCE(Images, '[]'), COALESCE(Runtime, 0)
		FROM Movies`)
	if err != nil {
		return nil, fmt.Errorf("query radarr movies: %w", err)
	}
	defer rows.Close()

	var movies []RadarrMovie
	for rows.Next() {
		var m RadarrMovie
		if err := rows.Scan(&m.ID, &m.TMDBID, &m.Title, &m.SortTitle, &m.Year,
			&m.Status, &m.Monitored, &m.QualityProfileID, &m.RootFolderPath,
			&m.HasFile, &m.MovieFileID, &m.Overview, &m.Images, &m.Runtime); err != nil {
			continue
		}
		movies = append(movies, m)
	}
	return movies, nil
}

// ReadMovieFiles reads all movie files from a Radarr database.
func (r *ArrReader) ReadMovieFiles() ([]MovieFile, error) {
	rows, err := r.db.Query(`
		SELECT Id, MovieId, COALESCE(RelativePath, ''), COALESCE(Path, ''),
		       COALESCE(Size, 0), COALESCE(Quality, '{}'), COALESCE(DateAdded, '')
		FROM MovieFiles`)
	if err != nil {
		return nil, fmt.Errorf("query movie files: %w", err)
	}
	defer rows.Close()

	var files []MovieFile
	for rows.Next() {
		var f MovieFile
		if err := rows.Scan(&f.ID, &f.MovieID, &f.RelativePath, &f.Path,
			&f.Size, &f.Quality, &f.DateAdded); err != nil {
			continue
		}
		files = append(files, f)
	}
	return files, nil
}

// ReadLidarrArtists reads all artists from a Lidarr database.
func (r *ArrReader) ReadLidarrArtists() ([]LidarrArtist, error) {
	rows, err := r.db.Query(`
		SELECT Id, COALESCE(ForeignArtistId, ''), Name, COALESCE(Status, ''),
		       Monitored, COALESCE(QualityProfileId, 0), COALESCE(RootFolderPath, ''),
		       COALESCE(Overview, ''), COALESCE(Images, '[]')
		FROM Artists`)
	if err != nil {
		return nil, fmt.Errorf("query lidarr artists: %w", err)
	}
	defer rows.Close()

	var artists []LidarrArtist
	for rows.Next() {
		var a LidarrArtist
		if err := rows.Scan(&a.ID, &a.ForeignArtistID, &a.Name, &a.Status,
			&a.Monitored, &a.QualityProfileID, &a.RootFolderPath,
			&a.Overview, &a.Images); err != nil {
			continue
		}
		artists = append(artists, a)
	}
	return artists, nil
}

// ReadLidarrAlbums reads all albums from a Lidarr database.
func (r *ArrReader) ReadLidarrAlbums() ([]LidarrAlbum, error) {
	rows, err := r.db.Query(`
		SELECT Id, COALESCE(ArtistId, 0), COALESCE(ForeignAlbumId, ''),
		       Title, COALESCE(Year, 0), Monitored, COALESCE(AlbumType, '')
		FROM Albums`)
	if err != nil {
		return nil, fmt.Errorf("query lidarr albums: %w", err)
	}
	defer rows.Close()

	var albums []LidarrAlbum
	for rows.Next() {
		var a LidarrAlbum
		if err := rows.Scan(&a.ID, &a.ArtistID, &a.ForeignAlbumID,
			&a.Title, &a.Year, &a.Monitored, &a.AlbumType); err != nil {
			continue
		}
		albums = append(albums, a)
	}
	return albums, nil
}

// ReadReadarrBooks reads all books from a Readarr database.
func (r *ArrReader) ReadReadarrBooks() ([]ReadarrBook, error) {
	rows, err := r.db.Query(`
		SELECT b.Id, COALESCE(b.ForeignBookId, ''), b.Title,
		       COALESCE(b.AuthorId, 0), COALESCE(a.Name, ''),
		       b.Monitored, COALESCE(b.QualityProfileId, 0),
		       COALESCE(b.Overview, ''), COALESCE(b.Images, '[]')
		FROM Books b
		LEFT JOIN Authors a ON b.AuthorId = a.Id`)
	if err != nil {
		return nil, fmt.Errorf("query readarr books: %w", err)
	}
	defer rows.Close()

	var books []ReadarrBook
	for rows.Next() {
		var b ReadarrBook
		if err := rows.Scan(&b.ID, &b.ForeignBookID, &b.Title,
			&b.AuthorID, &b.AuthorName, &b.Monitored, &b.QualityProfileID,
			&b.Overview, &b.Images); err != nil {
			continue
		}
		books = append(books, b)
	}
	return books, nil
}

// ReadProwlarrIndexers reads all indexers from a Prowlarr database.
func (r *ArrReader) ReadProwlarrIndexers() ([]ProwlarrIndexer, error) {
	rows, err := r.db.Query(`
		SELECT Id, Name, COALESCE(Implementation, ''), COALESCE(Settings, '{}'),
		       COALESCE(Enable, 1), COALESCE(Priority, 0)
		FROM Indexers`)
	if err != nil {
		return nil, fmt.Errorf("query prowlarr indexers: %w", err)
	}
	defer rows.Close()

	var indexers []ProwlarrIndexer
	for rows.Next() {
		var idx ProwlarrIndexer
		if err := rows.Scan(&idx.ID, &idx.Name, &idx.Implementation, &idx.Settings,
			&idx.Enable, &idx.Priority); err != nil {
			continue
		}
		indexers = append(indexers, idx)
	}
	return indexers, nil
}

// ReadQualityProfiles reads quality profiles from any arr database.
func (r *ArrReader) ReadQualityProfiles() ([]ArrQualityProfile, error) {
	rows, err := r.db.Query(`
		SELECT Id, Name, COALESCE(Items, '[]'), COALESCE(Cutoff, 0)
		FROM QualityProfiles`)
	if err != nil {
		return nil, fmt.Errorf("query quality profiles: %w", err)
	}
	defer rows.Close()

	var profiles []ArrQualityProfile
	for rows.Next() {
		var p ArrQualityProfile
		if err := rows.Scan(&p.ID, &p.Name, &p.Items, &p.Cutoff); err != nil {
			continue
		}
		profiles = append(profiles, p)
	}
	return profiles, nil
}

// ReadRootFolders reads root folders from any arr database.
func (r *ArrReader) ReadRootFolders() ([]ArrRootFolder, error) {
	rows, err := r.db.Query(`SELECT Id, Path FROM RootFolders`)
	if err != nil {
		return nil, fmt.Errorf("query root folders: %w", err)
	}
	defer rows.Close()

	var folders []ArrRootFolder
	for rows.Next() {
		var f ArrRootFolder
		if err := rows.Scan(&f.ID, &f.Path); err != nil {
			continue
		}
		folders = append(folders, f)
	}
	return folders, nil
}

// ReadBlocklist reads blocklist entries from any arr database.
func (r *ArrReader) ReadBlocklist() ([]ArrBlocklistEntry, error) {
	rows, err := r.db.Query(`
		SELECT Id,
		       COALESCE(SeriesTitle, COALESCE(SourceTitle, '')),
		       COALESCE(Quality, '{}'),
		       COALESCE(SourceTitle, ''),
		       COALESCE(Date, ''),
		       COALESCE(TorrentHash, ''),
		       COALESCE(Size, 0),
		       COALESCE(Protocol, 'torrent'),
		       COALESCE(Message, '')
		FROM Blocklist`)
	if err != nil {
		return nil, fmt.Errorf("query blocklist: %w", err)
	}
	defer rows.Close()

	var entries []ArrBlocklistEntry
	for rows.Next() {
		var e ArrBlocklistEntry
		if err := rows.Scan(&e.ID, &e.Title, &e.Quality, &e.SourceTitle,
			&e.Date, &e.TorrentHash, &e.Size, &e.Protocol, &e.Message); err != nil {
			continue
		}
		entries = append(entries, e)
	}
	return entries, nil
}

// ReadTags reads tags from any arr database.
func (r *ArrReader) ReadTags() ([]ArrTag, error) {
	rows, err := r.db.Query(`SELECT Id, Label FROM Tags`)
	if err != nil {
		return nil, fmt.Errorf("query tags: %w", err)
	}
	defer rows.Close()

	var tags []ArrTag
	for rows.Next() {
		var t ArrTag
		if err := rows.Scan(&t.ID, &t.Label); err != nil {
			continue
		}
		tags = append(tags, t)
	}
	return tags, nil
}

// ReadTrackFiles reads track files from a Lidarr database.
func (r *ArrReader) ReadTrackFiles() ([]TrackFile, error) {
	rows, err := r.db.Query(`
		SELECT Id, COALESCE(ArtistId, 0), COALESCE(AlbumId, 0),
		       COALESCE(RelativePath, ''), COALESCE(Path, ''),
		       COALESCE(Size, 0), COALESCE(Quality, '{}'), COALESCE(DateAdded, '')
		FROM TrackFiles`)
	if err != nil {
		return nil, fmt.Errorf("query track files: %w", err)
	}
	defer rows.Close()

	var files []TrackFile
	for rows.Next() {
		var f TrackFile
		if err := rows.Scan(&f.ID, &f.ArtistID, &f.AlbumID, &f.RelativePath,
			&f.Path, &f.Size, &f.Quality, &f.DateAdded); err != nil {
			continue
		}
		files = append(files, f)
	}
	return files, nil
}

// ReadBookFiles reads book files from a Readarr database.
func (r *ArrReader) ReadBookFiles() ([]BookFile, error) {
	rows, err := r.db.Query(`
		SELECT Id, COALESCE(BookId, 0),
		       COALESCE(RelativePath, ''), COALESCE(Path, ''),
		       COALESCE(Size, 0), COALESCE(Quality, '{}'), COALESCE(DateAdded, '')
		FROM BookFiles`)
	if err != nil {
		return nil, fmt.Errorf("query book files: %w", err)
	}
	defer rows.Close()

	var files []BookFile
	for rows.Next() {
		var f BookFile
		if err := rows.Scan(&f.ID, &f.BookID, &f.RelativePath, &f.Path,
			&f.Size, &f.Quality, &f.DateAdded); err != nil {
			continue
		}
		files = append(files, f)
	}
	return files, nil
}

// ParseIndexerSettings extracts URL and API key from Prowlarr indexer settings JSON.
func ParseIndexerSettings(settingsJSON string) (url, apiKey string) {
	var settings map[string]interface{}
	if err := json.Unmarshal([]byte(settingsJSON), &settings); err != nil {
		return "", ""
	}
	if u, ok := settings["url"].(string); ok {
		url = u
	}
	if k, ok := settings["apiKey"].(string); ok {
		apiKey = k
	}
	return url, apiKey
}

// ExtractAllowedQualities recursively extracts allowed quality names from arr Items JSON.
func ExtractAllowedQualities(items []QualityItem) []string {
	var names []string
	for _, item := range items {
		if item.Allowed && item.Quality != nil && item.Quality.Name != "" {
			names = append(names, item.Quality.Name)
		}
		if len(item.Items) > 0 {
			names = append(names, ExtractAllowedQualities(item.Items)...)
		}
	}
	return names
}

// FindCutoffName finds the quality name matching the cutoff ID in the Items tree.
func FindCutoffName(items []QualityItem, cutoffID int64) string {
	for _, item := range items {
		if item.Quality != nil && item.Quality.ID == cutoffID {
			return item.Quality.Name
		}
		if len(item.Items) > 0 {
			if name := FindCutoffName(item.Items, cutoffID); name != "" {
				return name
			}
		}
	}
	return ""
}

// ParseQualityItems parses the Items JSON from an arr quality profile.
func ParseQualityItems(itemsJSON string) []QualityItem {
	var items []QualityItem
	if err := json.Unmarshal([]byte(itemsJSON), &items); err != nil {
		return nil
	}
	return items
}