morffix/server/stats.go

package server

import (
	"bytes"
	"context"
	"fmt"
	"html/template"
	"log/slog"
	"net/http"
	"slices"
	"strings"
	"time"

	"git.lastassault.de/speatzle/morffix/constants"
	"github.com/go-echarts/go-echarts/v2/charts"
	"github.com/go-echarts/go-echarts/v2/opts"
	"github.com/jackc/pgx/v5"
)

type StatsDisplay struct {
	Charts          []ChartData
	Libraries       []Library
	SelectedLibrary string
	Size            []int
	Duration        []int
	Count           []int
	Saved           []int
}

type ChartData struct {
	Element template.HTML
	Script  template.HTML
}

type PieValue struct {
	Name  string
	Value int
}

type PieIntValue struct {
	Id    int
	Value int
}

const CHART_COLOR_SUCCESS = "#7cffb2"
const CHART_COLOR_FAILED = "#ff6e76"
const CHART_COLOR_UNKNOWN = "#fddd60"

func generatePie(name string, data []opts.PieData) ChartData {
	pie := charts.NewPie()
	pie.SetGlobalOptions(
		charts.WithInitializationOpts(opts.Initialization{
			Theme:           "dark",
			BackgroundColor: "#111",
		}),
		charts.WithTitleOpts(opts.Title{
			Title: name,
		}))

	for i := range data {
		if data[i].Name == "Success" || data[i].Name == "Healthy" {
			data[i].ItemStyle = &opts.ItemStyle{
				Color: CHART_COLOR_SUCCESS,
			}
		} else if data[i].Name == "Failed" || data[i].Name == "Damaged" {
			data[i].ItemStyle = &opts.ItemStyle{
				Color: CHART_COLOR_FAILED,
			}
		} else if data[i].Name == "Unknown" || data[i].Name == "None" {
			data[i].ItemStyle = &opts.ItemStyle{
				Color: CHART_COLOR_UNKNOWN,
			}
		}
	}

	pie.AddSeries(name, data).SetSeriesOptions(
		charts.WithLabelOpts(
			opts.Label{
				Show:      opts.Bool(true),
				Formatter: "{b}: {c}",
			}),
	)

	snippet := pie.RenderSnippet()

	return ChartData{
		Element: template.HTML(snippet.Element),
		Script:  template.HTML(snippet.Script),
	}
}

func generateStats(ctx context.Context, library_id string) ([]ChartData, error) {
	data := []ChartData{}

	rows, err := db.Query(ctx,
		`SELECT COALESCE(jsonb_path_query_first(ffprobe_data, '$.streams[*] ? (@.codec_type == "video") ? (@.disposition.attached_pic == 0).codec_name')::text, 'Unknown') AS name, COUNT(*) AS value
		FROM files
		WHERE ffprobe_data IS NOT NULL AND ($1 = -1 OR library_id = $1)
		GROUP BY 1;`, library_id)
	if err != nil {
		return nil, fmt.Errorf("Query Codecs: %w", err)
	}

	codecCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[PieValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Codec Data: %w", err)
	}
	res := []opts.PieData{}
	for _, v := range codecCounts {
		res = append(res, opts.PieData{
			Name:  strings.ReplaceAll(v.Name, "\"", ""),
			Value: v.Value,
		})
	}
	data = append(data, generatePie("Codecs", res))

	rows, err = db.Query(ctx,
		`SELECT COALESCE(jsonb_path_query_first(ffprobe_data, '$.streams[*] ? (@.codec_type == "video") ? (@.disposition.attached_pic == 0).width')::text, 'Unknown') AS name, COUNT(*) AS value
		FROM files
		WHERE ffprobe_data IS NOT NULL AND ($1 = -1 OR library_id = $1)
		GROUP BY 1;`, library_id)
	if err != nil {
		return nil, fmt.Errorf("Query Resolution: %w", err)
	}

	resolutionCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[PieValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Codec Data: %w", err)
	}
	res = []opts.PieData{}
	for _, v := range resolutionCounts {
		res = append(res, opts.PieData{
			Name:  strings.ReplaceAll(v.Name, "\"", ""),
			Value: v.Value,
		})
	}
	data = append(data, generatePie("Resolution", res))

	rows, err = db.Query(ctx,
		`SELECT COALESCE(jsonb_path_query_first(ffprobe_data, '$.format.format_name')::text, 'Unknown') AS name, COUNT(*) AS value
		FROM files
		WHERE ffprobe_data IS NOT NULL AND ($1 = -1 OR library_id = $1)
		GROUP BY 1;`, library_id)
	if err != nil {
		return nil, fmt.Errorf("Query Container: %w", err)
	}

	containerCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[PieValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Container Data: %w", err)
	}
	res = []opts.PieData{}
	for _, v := range containerCounts {
		res = append(res, opts.PieData{
			Name:  strings.ReplaceAll(v.Name, "\"", ""),
			Value: v.Value,
		})
	}
	data = append(data, generatePie("Container", res))

	rows, err = db.Query(ctx,
		`SELECT health AS id, COUNT(*) AS value
		FROM files WHERE ($1 = -1 OR library_id = $1)
		GROUP BY 1;`, library_id)
	if err != nil {
		return nil, fmt.Errorf("Query Health: %w", err)
	}

	healthCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[PieIntValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Health Data: %w", err)
	}
	res = []opts.PieData{}
	for _, v := range healthCounts {
		res = append(res, opts.PieData{
			Name:  constants.FileHealth(v.Id).String(),
			Value: v.Value,
		})
	}
	data = append(data, generatePie("Health", res))

	rows, err = db.Query(ctx,
		`SELECT transcode AS id, COUNT(*) AS value
		FROM files WHERE ($1 = -1 OR library_id = $1)
		GROUP BY 1;`, library_id)
	if err != nil {
		return nil, fmt.Errorf("Query Transcode: %w", err)
	}

	transcodeCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[PieIntValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Transcode Data: %w", err)
	}
	res = []opts.PieData{}
	for _, v := range transcodeCounts {
		res = append(res, opts.PieData{
			Name:  constants.FileTranscode(v.Id).String(),
			Value: v.Value,
		})
	}
	data = append(data, generatePie("Transcode Status", res))

	rows, err = db.Query(ctx,
		`SELECT tasks.status AS id, COUNT(*) AS value
		FROM tasks INNER JOIN files ON files.id = tasks.file_id
		WHERE ($1 = -1 OR files.library_id = $1)
		GROUP BY 1;`, library_id)
	if err != nil {
		return nil, fmt.Errorf("Query Task Status: %w", err)
	}

	taskStatusCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[PieIntValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Task Status Data: %w", err)
	}
	res = []opts.PieData{}
	for _, v := range taskStatusCounts {
		res = append(res, opts.PieData{
			Name:  constants.TaskStatus(v.Id).String(),
			Value: v.Value,
		})
	}
	data = append(data, generatePie("Task Status", res))

	type BarTaskRowValue struct {
		Date   time.Time
		Status constants.TaskStatus
		Count  int
	}

	rows, err = db.Query(ctx,
		`SELECT date_trunc('day', tasks.updated_at) date, tasks.status, COUNT(*) AS count
		FROM tasks INNER JOIN files ON files.id = tasks.file_id
		WHERE ($1 = -1 OR files.library_id = $1) AND tasks.updated_at > CURRENT_DATE - 7 AND (tasks.status = $2 OR tasks.status = $3)
		GROUP BY 1,2
		ORDER BY date;`, library_id, constants.TASK_STATUS_SUCCESS, constants.TASK_STATUS_FAILED)
	if err != nil {
		return nil, fmt.Errorf("Query Task Status Day: %w", err)
	}

	taskStatusDayCounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[BarTaskRowValue])
	if err != nil {
		return nil, fmt.Errorf("Collect Task Status Day Data: %w", err)
	}

	days := []string{}
	successBarData := []opts.BarData{}
	failedBarData := []opts.BarData{}

	for _, v := range taskStatusDayCounts {
		if !slices.Contains(days, v.Date.Format(time.DateOnly)) {
			days = append(days, v.Date.Format(time.DateOnly))
		}
		if v.Status == constants.TASK_STATUS_SUCCESS {
			successBarData = append(successBarData, opts.BarData{
				Value: v.Count,
				ItemStyle: &opts.ItemStyle{
					Color: CHART_COLOR_SUCCESS,
				},
			})
		} else if v.Status == constants.TASK_STATUS_FAILED {
			failedBarData = append(failedBarData, opts.BarData{
				Value: v.Count,
				ItemStyle: &opts.ItemStyle{
					Color: CHART_COLOR_FAILED,
				},
			})
		}
	}

	bar := charts.NewBar()
	bar.SetGlobalOptions(
		charts.WithInitializationOpts(opts.Initialization{
			Theme:           "dark",
			BackgroundColor: "#111",
		}),
		charts.WithTitleOpts(opts.Title{
			Title: "Task Success/Failed Last 7 Days",
		}),
	)
	bar.SetXAxis(days).
		AddSeries("Success", successBarData).
		AddSeries("Failed", failedBarData).
		SetSeriesOptions(charts.WithBarChartOpts(opts.BarChart{
			Stack: "stackA",
		}))

	snippet := bar.RenderSnippet()

	data = append(data, ChartData{
		Element: template.HTML(snippet.Element),
		Script:  template.HTML(snippet.Script),
	})

	return data, nil
}

func handleStats(w http.ResponseWriter, r *http.Request) {
	id := r.PathValue("id")
	if id == "" {
		id = "-1"
	}

	data, err := generateStats(r.Context(), id)
	if err != nil {
		slog.ErrorContext(r.Context(), "Generate Stats:", "err", err)
		http.Error(w, "Generate Stats: "+err.Error(), http.StatusInternalServerError)
		return
	}

	rows, err := db.Query(r.Context(),
		`SELECT *
		FROM libraries;`)
	if err != nil {
		slog.ErrorContext(r.Context(), "Query Libraries", "err", err)
		http.Error(w, "Query Libraries: "+err.Error(), http.StatusInternalServerError)
		return
	}

	libraries, err := pgx.CollectRows(rows, pgx.RowToStructByName[Library])
	if err != nil {
		slog.ErrorContext(r.Context(), "Collect Libraries", "err", err)
		http.Error(w, "Collect Libraries: "+err.Error(), http.StatusInternalServerError)
		return
	}

	var size int
	var count int
	var duration int
	err = db.QueryRow(r.Context(),
		`SELECT SUM(size) AS size,
		COUNT(id) as count,
		0 as duration
		FROM files WHERE ($1 = -1 OR files.library_id = $1) AND status = $2`, id, constants.FILE_STATUS_EXISTS).Scan(&size, &count, &duration)
	if err != nil {
		size = 0
		count = 0
		duration = 0
	}

	var saved int
	err = db.QueryRow(r.Context(),
		`SELECT (SUM(old_size) - COALESCE(SUM(size), 0)) AS saved
		FROM files WHERE ($1 = -1 OR files.library_id = $1) AND status = $2 AND old_size IS NOT NULL`, id, constants.FILE_STATUS_EXISTS).Scan(&saved)
	if err != nil {
		saved = 0
	}

	buf := bytes.Buffer{}
	err = templates.ExecuteTemplate(&buf, constants.STATS_TEMPLATE_NAME, StatsDisplay{
		Libraries:       libraries,
		SelectedLibrary: id,
		Size:            splitInt(size),
		Count:           splitInt(count),
		Duration:        splitInt(duration),
		Saved:           splitInt(saved),
		Charts:          data,
	})
	if err != nil {
		slog.ErrorContext(r.Context(), "Executing Stats Template", "err", err)
		http.Error(w, "Error Executing Template: "+err.Error(), http.StatusInternalServerError)
		return
	}

	_, err = w.Write(buf.Bytes())
	if err != nil {
		slog.ErrorContext(r.Context(), "Writing http Response", "err", err)
	}
}