Function bodies 133 total

func main() {
	// Setup structured logging
	logger := slog.New(slog.NewJSONHandler(os.Stdout, &slog.HandlerOptions{
		Level: slog.LevelInfo,
	}))
	slog.SetDefault(logger)

	slog.Info("Starting EpisodeX server...")

	// Load configuration
	cfg, err := config.Load()
	if err != nil {
		slog.Error("Failed to load configuration", "error", err)
		os.Exit(1)
	}

	slog.Info("Configuration loaded", "port", cfg.Port, "db_path", cfg.DBPath)

	// Initialize database
	db, err := database.New(cfg.DBPath)
	if err != nil {
		slog.Error("Failed to initialize database", "error", err)
		os.Exit(1)
	}
	defer db.Close() //nolint:errcheck

	// Initialize backup manager
	backupManager := database.NewBackupManager(db, cfg.DBPath, cfg.BackupPath, cfg.BackupRetention)

	// Initialize TVDB client
	var tvdbClient *tvdb.Client
	if cfg.TVDBApiKey != "" {
		tvdbClient = tvdb.NewClient(cfg.TVDBApiKey)
		if err := tvdbClient.Login(); err != nil {
			slog.Warn("Failed to login to TVDB", "error", err)
			tvdbClient = ni

func NewServer(db *database.DB, sc *scanner.Scanner, tvdbClient *tvdb.Client) *Server {
	s := &Server{
		db:          db,
		scanner:     sc,
		tvdbClient:  tvdbClient,
		audioCutter: audio.New(),
		router:      chi.NewRouter(),
	}

	s.setupMiddleware()
	s.setupRoutes()

	return s
}

func (s *Server) loggingMiddleware(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		start := time.Now()

		ww := middleware.NewWrapResponseWriter(w, r.ProtoMajor)
		next.ServeHTTP(ww, r)

		duration := time.Since(start)

		slog.Info("HTTP request",
			"method", r.Method,
			"path", r.URL.Path,
			"status", ww.Status(),
			"duration", duration,
			"bytes", ww.BytesWritten(),
			"ip", r.RemoteAddr,
		)
	})
}

func (s *Server) handleHealth(w http.ResponseWriter, _ *http.Request) {
	// Check database connection
	if err := s.db.Ping(); err != nil {
		s.respondError(w, http.StatusServiceUnavailable, "database unavailable")
		return
	}

	s.respondJSON(w, http.StatusOK, map[string]interface{}{
		"status":  "ok",
		"version": "1.0.0",
		"time":    time.Now().UTC(),
	})
}

func (s *Server) handleListSeries(w http.ResponseWriter, _ *http.Request) {
	query := `
		SELECT s.id, s.tvdb_id, s.title, s.original_title, s.poster_url, s.status, s.total_seasons, s.created_at,
			(SELECT COUNT(*) FROM seasons sn WHERE sn.series_id = s.id AND sn.is_watched = 1) as watched_seasons
		FROM series s
		ORDER BY s.created_at DESC
	`

	rows, err := s.db.Query(query)
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to fetch series")
		return
	}
	defer rows.Close() //nolint:errcheck

	series := []map[string]interface{}{}
	for rows.Next() {
		var id int
		var tvdbID *int
		var title string
		var originalTitle, posterURL, status *string
		var totalSeasons, watchedSeasons int
		var createdAt time.Time

		if err := rows.Scan(&id, &tvdbID, &title, &originalTitle, &posterURL, &status, &totalSeasons, &createdAt, &watchedSeasons); err != nil {
			slog.Error("Failed to scan series row", "error", err)
			continue
		}

		item := map[string]interface{}{
			"id

func (s *Server) handleCreateSeries(w http.ResponseWriter, r *http.Request) {
	var req struct {
		TVDBId *int   `json:"tvdb_id"`
		Title  string `json:"title"`
	}

	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid request body")
		return
	}

	var seriesID int64
	var title, originalTitle, posterURL, status string
	var totalSeasons int

	// Determine creation mode based on request
	switch {
	case req.TVDBId != nil && *req.TVDBId > 0:
		// TVDB ID provided, fetch metadata from TVDB
		if s.tvdbClient == nil {
			s.respondError(w, http.StatusServiceUnavailable, "TVDB client not configured")
			return
		}

		details, err := s.tvdbClient.GetSeriesDetailsWithRussian(*req.TVDBId)
		if err != nil {
			slog.Error("Failed to fetch series from TVDB", "tvdb_id", *req.TVDBId, "error", err)
			s.respondError(w, http.StatusInternalServerError, "failed to fetch series metadata")
			return
		}

		// Name = Russian (or English fallback), Orig

func (s *Server) handleDeleteSeries(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}

	query := "DELETE FROM series WHERE id = ?"
	result, err := s.db.Exec(query, id)
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to delete series")
		return
	}

	rows, err := result.RowsAffected()
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to verify deletion")
		return
	}
	if rows == 0 {
		s.respondError(w, http.StatusNotFound, "series not found")
		return
	}

	slog.Info("Deleted series", "id", id)
	s.respondJSON(w, http.StatusOK, map[string]interface{}{
		"success": true,
	})
}

func (s *Server) handleGetAlerts(w http.ResponseWriter, _ *http.Request) {
	query := `
		SELECT id, type, message, created_at, dismissed
		FROM system_alerts
		WHERE dismissed = 0
		ORDER BY created_at DESC
		LIMIT 10
	`

	rows, err := s.db.Query(query)
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to fetch alerts")
		return
	}
	defer rows.Close() //nolint:errcheck

	alerts := []map[string]interface{}{}
	for rows.Next() {
		var id int
		var alertType, message string
		var createdAt time.Time
		var dismissed bool

		if err := rows.Scan(&id, &alertType, &message, &createdAt, &dismissed); err != nil {
			slog.Error("Failed to scan alert row", "error", err)
			continue
		}

		alerts = append(alerts, map[string]interface{}{
			"id":         id,
			"type":       alertType,
			"message":    message,
			"created_at": createdAt,
			"dismissed":  dismissed,
		})
	}

	if err := rows.Err(); err != nil {
		s.respondError(w, http.StatusInternalServerError, "error readi

func (s *Server) handleDismissAlert(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid alert ID")
		return
	}

	query := "UPDATE system_alerts SET dismissed = 1 WHERE id = ?"
	result, err := s.db.Exec(query, id)
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to dismiss alert")
		return
	}

	rows, err := result.RowsAffected()
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to verify dismissal")
		return
	}
	if rows == 0 {
		s.respondError(w, http.StatusNotFound, "alert not found")
		return
	}

	s.respondJSON(w, http.StatusOK, map[string]interface{}{
		"success": true,
	})
}

func (s *Server) respondJSON(w http.ResponseWriter, status int, data interface{}) {
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(status)
	if err := json.NewEncoder(w).Encode(data); err != nil {
		slog.Error("Failed to encode JSON response", "error", err)
	}
}

func isValidHash(h string) bool {
	if h == "" || len(h) > 128 {
		return false
	}
	for _, c := range h {
		if (c < '0' || c > '9') && (c < 'a' || c > 'f') && (c < 'A' || c > 'F') {
			return false
		}
	}
	return true
}

func countWatchedSeasons(seasons []map[string]interface{}) int {
	count := 0
	for _, s := range seasons {
		if watched, ok := s["watched"].(bool); ok && watched {
			count++
		}
	}
	return count
}

func (s *Server) handleTriggerScan(w http.ResponseWriter, _ *http.Request) {
	if s.scanner == nil {
		s.respondError(w, http.StatusServiceUnavailable, "scanner not configured")
		return
	}

	slog.Info("Manual scan triggered")

	go func() {
		defer func() {
			if r := recover(); r != nil {
				slog.Error("Panic in scan", "error", r)
			}
		}()
		if err := s.scanner.Scan(); err != nil {
			slog.Error("Scan failed", "error", err)
		}
	}()

	s.respondJSON(w, http.StatusOK, map[string]interface{}{
		"success": true,
		"message": "Scan started",
	})
}

func (s *Server) handleGetUpdates(w http.ResponseWriter, _ *http.Request) {
	// Episode-based updates: a series appears if it has seasons with
	// season_number > max_watched AND aired_episodes > 0.
	// Requires at least one watched season (season_number > 0).
	query := `
		SELECT s.id, s.tvdb_id, s.title, s.original_title, s.poster_url, s.status,
			s.aired_seasons,
			(SELECT MAX(sn.season_number) FROM seasons sn WHERE sn.series_id = s.id AND sn.is_watched = 1 AND sn.season_number > 0) as max_watched
		FROM series s
		WHERE (SELECT COUNT(*) FROM seasons sn WHERE sn.series_id = s.id AND sn.is_watched = 1 AND sn.season_number > 0) > 0
		AND EXISTS (
			SELECT 1 FROM seasons sn
			WHERE sn.series_id = s.id
			AND sn.season_number > COALESCE(
				(SELECT MAX(sn2.season_number) FROM seasons sn2 WHERE sn2.series_id = s.id AND sn2.is_watched = 1 AND sn2.season_number > 0),
				0
			)
			AND sn.aired_episodes > 0
			AND sn.is_watched = 0
		)
		ORDER BY s.updated_at DESC
	`

	rows, err := s.d

func (s *Server) queryAiredSeasonsAfter(seriesID, maxWatched int) ([]seasonUpdate, error) {
	rows, err := s.db.Query(`
		SELECT season_number, aired_episodes FROM seasons
		WHERE series_id = ? AND season_number > ? AND aired_episodes > 0 AND is_watched = 0
		ORDER BY season_number
	`, seriesID, maxWatched)
	if err != nil {
		return nil, err
	}
	defer rows.Close() //nolint:errcheck

	var result []seasonUpdate
	for rows.Next() {
		var su seasonUpdate
		if err := rows.Scan(&su.SeasonNumber, &su.AiredEpisodes); err != nil {
			slog.Warn("Failed to scan season update row", "series_id", seriesID, "error", err)
			continue
		}
		result = append(result, su)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if result == nil {
		result = []seasonUpdate{}
	}
	return result, nil
}

func (s *Server) handleCheckUpdates(w http.ResponseWriter, _ *http.Request) {
	slog.Info("Manual TVDB check triggered")

	if s.tvdbClient == nil {
		s.respondError(w, http.StatusServiceUnavailable, "TVDB client not configured")
		return
	}

	// Run check in background (includes auto-sync for stale series)
	go func() {
		defer func() {
			if r := recover(); r != nil {
				slog.Error("Panic in TVDB check", "error", r)
			}
		}()
		CheckForTVDBUpdates(s.db, s.tvdbClient, true)
	}()

	s.respondJSON(w, http.StatusOK, map[string]interface{}{
		"success": true,
		"message": "Check started",
	})
}

func (s *Server) handleListSeasons(w http.ResponseWriter, r *http.Request) {
	sid, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}

	// Get series info including poster
	var totalSeasons int
	var seriesPosterURL *string
	err = s.db.QueryRow(`SELECT total_seasons, poster_url FROM series WHERE id = ?`, sid).Scan(&totalSeasons, &seriesPosterURL)
	if err == sql.ErrNoRows {
		s.respondError(w, http.StatusNotFound, "series not found")
		return
	}
	if err != nil {
		slog.Error("Failed to fetch series for seasons", "id", sid, "error", err)
		s.respondError(w, http.StatusInternalServerError, "failed to fetch series")
		return
	}

	// Get owned seasons from seasons table with voice actor JOIN (includes cached poster_url)
	query := `
		SELECT sn.season_number, sn.folder_path, sn.is_watched, sn.is_owned, sn.voice_actor_id, va.name, sn.discovered_at, sn.poster_url
		FROM seasons sn
		LEFT JOIN voice_

func (s *Server) handleSearch(w http.ResponseWriter, r *http.Request) {
	query := r.URL.Query().Get("q")
	if query == "" {
		s.respondJSON(w, http.StatusOK, []interface{}{})
		return
	}

	if s.tvdbClient == nil {
		s.respondError(w, http.StatusServiceUnavailable, "TVDB client not configured")
		return
	}

	results, err := s.tvdbClient.SearchSeries(query)
	if err != nil {
		slog.Error("TVDB search failed", "query", query, "error", err)
		s.respondError(w, http.StatusInternalServerError, "search failed")
		return
	}

	// Format results for API response
	response := make([]map[string]interface{}, 0, len(results))
	for _, result := range results {
		response = append(response, map[string]interface{}{
			"id":     result.TVDBId,
			"name":   result.Name,
			"poster": result.Image,
			"year":   result.Year,
			"status": result.Status,
		})
	}

	s.respondJSON(w, http.StatusOK, response)
}

func (s *Server) handleGetAudioTracks(w http.ResponseWriter, r *http.Request) {
	sid, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}
	snum, err := strconv.Atoi(chi.URLParam(r, "num"))
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid season number")
		return
	}

	// Get folder path from seasons table
	var folderPath *string
	err = s.db.QueryRow(`
		SELECT folder_path FROM seasons
		WHERE series_id = ? AND season_number = ?
	`, sid, snum).Scan(&folderPath)

	if err == sql.ErrNoRows {
		s.respondError(w, http.StatusNotFound, "season not found")
		return
	}
	if err != nil {
		slog.Error("Failed to fetch season for audio tracks", "series_id", sid, "season", snum, "error", err)
		s.respondError(w, http.StatusInternalServerError, "failed to fetch season")
		return
	}
	if folderPath == nil {
		s.respondError(w, http.StatusNotFound, "season has no folder path")
		return
	}

	

func (s *Server) handleGetSeason(w http.ResponseWriter, r *http.Request) {
	sid, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}
	snum, err := strconv.Atoi(chi.URLParam(r, "num"))
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid season number")
		return
	}

	var folderPath *string
	var isWatched, isOwned bool
	var voiceActorID *int
	var voiceActorName *string
	var discoveredAt *time.Time
	err = s.db.QueryRow(`
		SELECT sn.folder_path, sn.is_watched, sn.is_owned, sn.voice_actor_id, va.name, sn.discovered_at
		FROM seasons sn
		LEFT JOIN voice_actors va ON sn.voice_actor_id = va.id
		WHERE sn.series_id = ? AND sn.season_number = ?
	`, sid, snum).Scan(&folderPath, &isWatched, &isOwned, &voiceActorID, &voiceActorName, &discoveredAt)

	if err == sql.ErrNoRows {
		s.respondError(w, http.StatusNotFound, "season not found")
		return
	}
	if err != nil {
		slog.Error("Failed to

func (s *Server) handleUpdateSeason(w http.ResponseWriter, r *http.Request) {
	sid, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}
	snum, err := strconv.Atoi(chi.URLParam(r, "num"))
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid season number")
		return
	}

	var req struct {
		VoiceActorID *int `json:"voice_actor_id"`
	}

	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid request body")
		return
	}

	// Verify season exists
	var exists bool
	err = s.db.QueryRow(`
		SELECT COUNT(*) > 0 FROM seasons
		WHERE series_id = ? AND season_number = ?
	`, sid, snum).Scan(&exists)
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to check season existence")
		return
	}
	if !exists {
		s.respondError(w, http.StatusNotFound, "season not found")
		return
	}

	// Treat voice_actor_id <= 0 as 

func (s *Server) handleListVoices(w http.ResponseWriter, _ *http.Request) {
	rows, err := s.db.Query(`SELECT id, name FROM voice_actors ORDER BY name`)
	if err != nil {
		s.respondError(w, http.StatusInternalServerError, "failed to fetch voices")
		return
	}
	defer rows.Close() //nolint:errcheck

	voices := []map[string]interface{}{}
	for rows.Next() {
		var id int
		var name string
		if err := rows.Scan(&id, &name); err != nil {
			slog.Error("Failed to scan voice actor row", "error", err)
			continue
		}
		voices = append(voices, map[string]interface{}{
			"id":   id,
			"name": name,
		})
	}

	if err := rows.Err(); err != nil {
		s.respondError(w, http.StatusInternalServerError, "error reading voices")
		return
	}

	s.respondJSON(w, http.StatusOK, voices)
}

func (s *Server) handleGenerateAudioPreview(w http.ResponseWriter, r *http.Request) {
	sid, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}
	snum, err := strconv.Atoi(chi.URLParam(r, "num"))
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid season number")
		return
	}

	var req struct {
		FilePath   string `json:"file_path"`
		TrackIndex int    `json:"track_index"`
	}

	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid request body")
		return
	}

	// Validate file path is within the season's folder to prevent path traversal
	var folderPath *string
	err = s.db.QueryRow(`
		SELECT folder_path FROM seasons
		WHERE series_id = ? AND season_number = ?
	`, sid, snum).Scan(&folderPath)
	if err != nil || folderPath == nil {
		s.respondError(w, http.StatusNotFound, "season not found or no folder path")
		return
	}

func (s *Server) handleServeAudioPreview(w http.ResponseWriter, r *http.Request) {
	hash := chi.URLParam(r, "hash")

	// Validate hash format to prevent path traversal
	if !isValidHash(hash) {
		s.respondError(w, http.StatusBadRequest, "invalid hash format")
		return
	}

	filePath, err := s.audioCutter.GetPreviewPath(hash)
	if err != nil {
		s.respondError(w, http.StatusNotFound, "preview not found")
		return
	}

	w.Header().Set("Content-Type", "audio/mpeg")
	w.Header().Set("Content-Disposition", "inline; filename=preview.mp3")
	http.ServeFile(w, r, filePath)
}

func (s *Server) handleProcessAudioStream(w http.ResponseWriter, r *http.Request) {
	r.Body = http.MaxBytesReader(w, r.Body, 1<<20) // 1MB limit

	seriesID := chi.URLParam(r, "id")
	seasonNum := chi.URLParam(r, "num")

	var req struct {
		TrackID      int  `json:"track_id"`
		KeepOriginal bool `json:"keep_original"`
	}

	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid request body")
		return
	}

	if req.TrackID <= 0 {
		s.respondError(w, http.StatusBadRequest, "track_id is required")
		return
	}

	// Validate series ID and season number before database query
	sid, err := strconv.ParseInt(seriesID, 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid series ID")
		return
	}
	snum, err := strconv.ParseInt(seasonNum, 10, 64)
	if err != nil {
		s.respondError(w, http.StatusBadRequest, "invalid season number")
		return
	}

	// Get folder path from seasons table
	var folderPath *string
	err = s.db.QueryR

func mustJSON(v interface{}) string {
	b, err := json.Marshal(v)
	if err != nil {
		panic(err)
	}
	return string(b)
}

func CheckForTVDBUpdates(db *database.DB, tvdbClient *tvdb.Client, autoSync bool) TVDBCheckResult {
	if !tvdbCheckMu.TryLock() {
		slog.Info("TVDB check already in progress, skipping")
		return TVDBCheckResult{Skipped: true}
	}
	defer tvdbCheckMu.Unlock()

	var result TVDBCheckResult

	type seriesRow struct {
		id, tvdbID, airedSeasons, maxWatched int
		title                                string
		updatedAt                            time.Time
	}

	rows, err := db.Query(`
		SELECT s.id, s.tvdb_id, s.title, s.aired_seasons,
			COALESCE((SELECT MAX(sn.season_number) FROM seasons sn WHERE sn.series_id = s.id AND sn.is_watched = 1 AND sn.season_number > 0), 0),
			s.updated_at
		FROM series s
		WHERE s.tvdb_id IS NOT NULL
	`)
	if err != nil {
		slog.Error("Failed to fetch series for TVDB check", "error", err)
		return result
	}

	var seriesList []seriesRow
	for rows.Next() {
		var s seriesRow
		if err := rows.Scan(&s.id, &s.tvdbID, &s.title, &s.airedSeasons, &s.maxWatched, &s.updatedAt); 

func loadAiredEpisodesFromDB(db *database.DB, seriesID int) (map[int]int, error) {
	rows, err := db.Query(`
		SELECT season_number, aired_episodes FROM seasons
		WHERE series_id = ? AND season_number > 0
	`, seriesID)
	if err != nil {
		return nil, err
	}
	defer rows.Close() //nolint:errcheck

	result := make(map[int]int)
	for rows.Next() {
		var sn, count int
		if err := rows.Scan(&sn, &count); err != nil {
			return nil, err
		}
		result[sn] = count
	}
	return result, rows.Err()
}

func SyncSeriesMetadata(db *database.DB, tvdbClient *tvdb.Client, seriesID int64, tvdbID int) error {
	// Fetch extended data from TVDB
	extended, err := tvdbClient.GetSeriesExtendedFull(tvdbID)
	if err != nil {
		return fmt.Errorf("failed to fetch series from TVDB: %w", err)
	}

	// Fetch episodes to count aired episodes per season.
	// On error, log a warning and preserve existing values from DB.
	airedPerSeason := make(map[int]int)
	episodesFailed := false
	episodes, err := tvdbClient.GetSeriesEpisodes(tvdbID)
	if err != nil {
		slog.Warn("Failed to fetch episodes from TVDB, preserving existing aired_episodes",
			"tvdb_id", tvdbID, "error", err)
		episodesFailed = true
		// Load existing aired_episodes so upsertSeasonTx preserves them.
		if existing, loadErr := loadAiredEpisodesFromDB(db, int(seriesID)); loadErr == nil {
			airedPerSeason = existing
		}
	} else {
		airedPerSeason = tvdb.CountAiredEpisodesBySeason(episodes)
	}

	// Get Russian translation
	rusTrans, _ := tvdbClient.

func SyncUnsyncedSeries(db *database.DB, tvdbClient *tvdb.Client) {
	unsyncedSeries, err := db.GetUnsyncedSeries()
	if err != nil {
		slog.Error("Failed to get unsynced series", "error", err)
		return
	}

	if len(unsyncedSeries) == 0 {
		slog.Info("No unsynced series found")
		return
	}

	slog.Info("Starting startup sync for unsynced series", "count", len(unsyncedSeries))

	if !tvdbCheckMu.TryLock() {
		slog.Info("TVDB check already in progress, skipping startup sync")
		return
	}
	defer tvdbCheckMu.Unlock()

	var synced, errors int
	for i := range unsyncedSeries {
		s := &unsyncedSeries[i]
		slog.Info("Syncing unsynced series", "progress", fmt.Sprintf("%d/%d", i+1, len(unsyncedSeries)), "title", s.Title, "tvdb_id", *s.TVDBId)
		if err := SyncSeriesMetadata(db, tvdbClient, s.ID, *s.TVDBId); err != nil {
			slog.Error("Failed to sync unsynced series", "series_id", s.ID, "title", s.Title, "error", err)
			errors++
			continue
		}
		// Mark series as synced even if TVDB returned no overvi

func New() *AudioCutter {
	tempDir := filepath.Join(os.TempDir(), "episodex-audio")
	_ = os.MkdirAll(tempDir, 0o750)

	return &AudioCutter{
		mkvmergePath: "mkvmerge", // Assumes mkvmerge is in PATH
		ffmpegPath:   "ffmpeg",   // Assumes ffmpeg is in PATH
		tempDir:      tempDir,
	}
}

func (ac *AudioCutter) GetAudioTracks(filePath string) ([]AudioTrack, error) {
	// Check if file exists
	if _, err := os.Stat(filePath); os.IsNotExist(err) {
		return nil, fmt.Errorf("file does not exist: %s", filePath)
	}

	// Run mkvmerge -J to get file info
	cmd := exec.Command(ac.mkvmergePath, "-J", filePath) //nolint:gosec // controlled input
	output, err := cmd.Output()
	if err != nil {
		return nil, fmt.Errorf("failed to run mkvmerge: %w", err)
	}

	// Parse JSON output
	var info MKVInfo
	if err := json.Unmarshal(output, &info); err != nil {
		return nil, fmt.Errorf("failed to parse mkvmerge output: %w", err)
	}

	// Extract audio tracks
	var audioTracks []AudioTrack
	for _, track := range info.Tracks {
		if track.Type == "audio" {
			audioTracks = append(audioTracks, AudioTrack{
				ID:       track.ID,
				Type:     track.Type,
				Codec:    track.Codec,
				Language: track.Properties.Language,
				Name:     track.Properties.TrackName,
				Channels: track.Properties.AudioChanne

func (ac *AudioCutter) ScanFolderAudioTracks(folderPath string) (map[string][]AudioTrack, error) {
	if _, err := os.Stat(folderPath); os.IsNotExist(err) {
		return nil, fmt.Errorf("folder does not exist: %s", folderPath)
	}

	results := make(map[string][]AudioTrack)

	// Walk through folder
	err := filepath.Walk(folderPath, func(path string, info os.FileInfo, err error) error {
		if err != nil {
			return err
		}

		// Only process MKV files
		if !info.IsDir() && strings.ToLower(filepath.Ext(path)) == ".mkv" {
			tracks, err := ac.GetAudioTracks(path)
			if err != nil {
				// Log error but continue processing other files
				return nil
			}
			results[path] = tracks
		}

		return nil
	})

	if err != nil {
		return nil, fmt.Errorf("failed to scan folder: %w", err)
	}

	return results, nil
}

func (ac *AudioCutter) RemoveAudioTracks(filePath string, keepTrackID int, keepOriginal bool) error {
	// Check if file exists
	if _, err := os.Stat(filePath); os.IsNotExist(err) {
		return fmt.Errorf("file does not exist: %s", filePath)
	}

	// Get all tracks to build the command
	tracks, err := ac.GetAudioTracks(filePath)
	if err != nil {
		return fmt.Errorf("failed to get audio tracks: %w", err)
	}

	// Verify that the track to keep exists
	trackExists := false
	for _, track := range tracks {
		if track.ID == keepTrackID {
			trackExists = true
			break
		}
	}

	if !trackExists {
		return fmt.Errorf("track ID %d does not exist in file", keepTrackID)
	}

	// Create output file path (temporary)
	dir := filepath.Dir(filePath)
	base := filepath.Base(filePath)
	tempFile := filepath.Join(dir, ".tmp_"+base)
	defer func() { _ = os.Remove(tempFile) }() // Clean up temp file on failure; no-op after successful rename

	// Build track selection arguments
	// We need to keep video, subtitles, an

func (ac *AudioCutter) ProcessFolder(folderPath string, keepTrackID int, keepOriginal bool) (processed, failed []string, err error) {
	if _, err := os.Stat(folderPath); os.IsNotExist(err) {
		return nil, nil, fmt.Errorf("folder does not exist: %s", folderPath)
	}

	// Walk through folder
	err = filepath.Walk(folderPath, func(path string, info os.FileInfo, err error) error {
		if err != nil {
			return err
		}

		// Only process MKV files
		if !info.IsDir() && strings.ToLower(filepath.Ext(path)) == ".mkv" {
			if err := ac.RemoveAudioTracks(path, keepTrackID, keepOriginal); err != nil {
				failed = append(failed, path)
			} else {
				processed = append(processed, path)
			}
		}

		return nil
	})

	if err != nil {
		return processed, failed, fmt.Errorf("failed to process folder: %w", err)
	}

	return processed, failed, nil
}

func (ac *AudioCutter) GeneratePreview(filePath string, trackIndex, duration int) (string, error) {
	if _, err := os.Stat(filePath); os.IsNotExist(err) {
		return "", fmt.Errorf("file does not exist: %s", filePath)
	}

	// Create hash for caching
	hash := fmt.Sprintf("%x", sha256.Sum256([]byte(fmt.Sprintf("%s_%d", filePath, trackIndex))))
	outputFile := filepath.Join(ac.tempDir, hash+".mp3")

	// Check if preview already exists
	if _, err := os.Stat(outputFile); err == nil {
		return hash, nil
	}

	// Extract audio preview using ffmpeg
	// -ss 60: start at 1 minute
	// -t duration: extract for specified duration
	// -map 0:a:trackIndex: select audio track by index
	cmd := exec.Command(ac.ffmpegPath, //nolint:gosec // controlled input
		"-y",
		"-i", filePath,
		"-ss", "60",
		"-t", fmt.Sprintf("%d", duration),
		"-map", fmt.Sprintf("0:a:%d", trackIndex),
		"-acodec", "libmp3lame",
		"-q:a", "4",
		outputFile,
	)

	output, err := cmd.CombinedOutput()
	if err != nil {
		return "", fmt.Er

func (ac *AudioCutter) GetPreviewPath(hash string) (string, error) {
	previewPath := filepath.Join(ac.tempDir, hash+".mp3")

	if _, err := os.Stat(previewPath); os.IsNotExist(err) {
		return "", fmt.Errorf("preview not found: %s", hash)
	}

	return previewPath, nil
}

func (ac *AudioCutter) CleanupOldPreviews() error {
	files, err := filepath.Glob(filepath.Join(ac.tempDir, "*.mp3"))
	if err != nil {
		return err
	}

	for _, file := range files {
		info, err := os.Stat(file)
		if err != nil {
			continue
		}

		// Remove files older than 24 hours
		if info.ModTime().Before(time.Now().Add(-24 * time.Hour)) {
			os.Remove(file) //nolint:errcheck // removal failure is non-critical
		}
	}

	return nil
}

func Load() (*Config, error) {
	// Try to load .env file, ignore error if it doesn't exist
	_ = godotenv.Load()

	cfg := &Config{
		Port:              getEnv("PORT", "8080"),
		Host:              getEnv("HOST", "0.0.0.0"),
		DBPath:            getEnv("DB_PATH", "./data/episodex.db"),
		BackupPath:        getEnv("BACKUP_PATH", "./data/backups"),
		BackupRetention:   getEnvAsInt("BACKUP_RETENTION", 10),
		BackupHour:        getEnvAsInt("BACKUP_HOUR", 3),
		MediaPath:         getEnv("MEDIA_PATH", "/Volumes/Plex/TV Show"),
		TVDBApiKey:        getEnv("TVDB_API_KEY", ""),
		ScanIntervalHours: getEnvAsInt("SCAN_INTERVAL_HOURS", 1),
		TVDBCheckHour:     getEnvAsInt("TVDB_CHECK_HOUR", 5),
	}

	// Validate required fields
	if err := cfg.Validate(); err != nil {
		return nil, err
	}

	return cfg, nil
}

func (c *Config) Validate() error {
	if c.DBPath == "" {
		return fmt.Errorf("DB_PATH is required")
	}

	if c.MediaPath == "" {
		return fmt.Errorf("MEDIA_PATH is required")
	}

	if c.BackupRetention < 1 {
		return fmt.Errorf("BACKUP_RETENTION must be at least 1")
	}

	if c.BackupHour < 0 || c.BackupHour > 23 {
		return fmt.Errorf("BACKUP_HOUR must be between 0 and 23")
	}

	if c.TVDBCheckHour < 0 || c.TVDBCheckHour > 23 {
		return fmt.Errorf("TVDB_CHECK_HOUR must be between 0 and 23")
	}

	if c.ScanIntervalHours < 1 {
		return fmt.Errorf("SCAN_INTERVAL_HOURS must be at least 1")
	}

	return nil
}

func getEnv(key, defaultValue string) string {
	if value := os.Getenv(key); value != "" {
		return value
	}
	return defaultValue
}

func getEnvAsInt(key string, defaultValue int) int {
	if value := os.Getenv(key); value != "" {
		if intVal, err := strconv.Atoi(value); err == nil {
			return intVal
		}
	}
	return defaultValue
}

func NewBackupManager(db *DB, dbPath, backupPath string, retention int) *BackupManager {
	return &BackupManager{
		db:             db,
		dbPath:         dbPath,
		backupPath:     backupPath,
		retention:      retention,
		alertOnFailure: true,
	}
}

func (bm *BackupManager) Backup() error {
	// Ensure backup directory exists
	if err := os.MkdirAll(bm.backupPath, 0o755); err != nil {
		return fmt.Errorf("failed to create backup directory: %w", err)
	}

	// Generate backup filename with timestamp
	timestamp := time.Now().Format("20060102_150405")
	backupFile := filepath.Join(bm.backupPath, fmt.Sprintf("episodex_%s.db", timestamp))

	slog.Info("Starting database backup", "file", backupFile)

	// Use VACUUM INTO for an atomic, consistent backup that includes WAL contents.
	// A simple file copy would miss pending WAL writes.
	if _, err := bm.db.Exec("VACUUM INTO ?", backupFile); err != nil {
		bm.createAlert("backup_failed", fmt.Sprintf("Backup failed: %v", err))
		return fmt.Errorf("failed to vacuum into backup: %w", err)
	}

	// Get file size
	fileInfo, err := os.Stat(backupFile)
	if err != nil {
		return fmt.Errorf("failed to get backup file info: %w", err)
	}

	// Check integrity of the backup
	integrityOK, err := bm.checkIntegrit

func (bm *BackupManager) checkIntegrity(backupFile string) (bool, error) {
	// Open backup file directly without running migrations —
	// we only need to check integrity, not modify the backup
	sqlDB, err := sql.Open("sqlite", backupFile)
	if err != nil {
		return false, err
	}
	defer sqlDB.Close() //nolint:errcheck // closing temporary integrity-check connection

	var result string
	err = sqlDB.QueryRow("PRAGMA integrity_check").Scan(&result)
	if err != nil {
		return false, err
	}

	return result == "ok", nil
}

func (bm *BackupManager) recordBackup(filename string, size int64, integrityOK bool) error {
	query := `
		INSERT INTO backups (filename, size_bytes, integrity_ok, created_at)
		VALUES (?, ?, ?, CURRENT_TIMESTAMP)
	`

	integrityInt := 0
	if integrityOK {
		integrityInt = 1
	}

	_, err := bm.db.Exec(query, filename, size, integrityInt)
	return err
}

func (bm *BackupManager) rotateBackups() error {
	// Get all backup files
	files, err := filepath.Glob(filepath.Join(bm.backupPath, "episodex_*.db"))
	if err != nil {
		return err
	}

	if len(files) <= bm.retention {
		return nil // Nothing to rotate
	}

	// Sort files by modification time (newest first)
	sort.Slice(files, func(i, j int) bool {
		infoI, errI := os.Stat(files[i])
		infoJ, errJ := os.Stat(files[j])
		if errI != nil || errJ != nil {
			return false
		}
		return infoI.ModTime().After(infoJ.ModTime())
	})

	// Remove old backups
	filesToDelete := files[bm.retention:]
	for _, file := range filesToDelete {
		if err := os.Remove(file); err != nil {
			slog.Warn("Failed to remove old backup", "file", file, "error", err)
		} else {
			slog.Info("Removed old backup", "file", file)
		}
	}

	return nil
}

func (bm *BackupManager) createAlert(alertType, message string) {
	if !bm.alertOnFailure {
		return
	}

	query := `
		INSERT INTO system_alerts (type, message, created_at)
		VALUES (?, ?, CURRENT_TIMESTAMP)
	`

	_, err := bm.db.Exec(query, alertType, message)
	if err != nil {
		slog.Error("Failed to create alert", "error", err)
	}
}

func (bm *BackupManager) GetBackupHistory(limit int) ([]BackupInfo, error) {
	query := `
		SELECT id, filename, size_bytes, integrity_ok, created_at
		FROM backups
		ORDER BY created_at DESC
		LIMIT ?
	`

	rows, err := bm.db.Query(query, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close() //nolint:errcheck // closing read-only rows

	var backups []BackupInfo
	for rows.Next() {
		var b BackupInfo
		if err := rows.Scan(&b.ID, &b.Filename, &b.SizeBytes, &b.IntegrityOK, &b.CreatedAt); err != nil {
			return nil, err
		}
		backups = append(backups, b)
	}

	return backups, rows.Err()
}

func New(dbPath string) (*DB, error) {
	// Ensure the directory exists
	dir := filepath.Dir(dbPath)
	if err := os.MkdirAll(dir, 0o755); err != nil {
		return nil, fmt.Errorf("failed to create database directory: %w", err)
	}

	// Open database connection
	sqlDB, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// Configure connection pool
	sqlDB.SetMaxOpenConns(1) // SQLite works best with single connection
	sqlDB.SetMaxIdleConns(1)

	db := &DB{DB: sqlDB}

	// Enable foreign key enforcement (required per-connection in SQLite)
	if _, err := sqlDB.Exec("PRAGMA foreign_keys = ON"); err != nil {
		sqlDB.Close() //nolint:errcheck
		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
	}

	// Initialize tables (creates tables and indexes)
	if err := db.initTables(); err != nil {
		sqlDB.Close() //nolint:errcheck // best-effort cleanup on init failure
		return nil, fmt.Errorf("failed to initialize tables: %w", err