smon/datapoint.go

package main

import (
	// External
	werr "git.ahall.se/go/wrappederror"
	"github.com/jackc/pgx/v5"

	// Standard
	"context"
	"database/sql"
	"errors"
	"strings"
	"time"
)

type DatapointType string

const (
	INT      DatapointType = "INT"
	STRING                 = "STRING"
	DATETIME               = "DATETIME"
)

type Datapoint struct {
	ID                   int
	Group                string
	Name                 string
	Datatype             DatapointType
	Comment              string
	LastValue            time.Time `db:"last_value"`
	DataPointID          int       `db:"datapoint_id"`
	DatapointValueJSON   []byte    `db:"datapoint_value_json"`
	LastDatapointValue   DatapointValue
	LastDatapointID      *int       `db:"last_value_id"`
	LastValueInt         *int       `db:"last_value_int"`
	LastValueString      *string    `db:"last_value_string"`
	LastValueDateTime    *time.Time `db:"last_value_datetime"`
	Found                bool
	NodataProblemSeconds int  `db:"nodata_problem_seconds"`
	NodataIsProblem      bool `db:"nodata_is_problem"`
}

type DatapointBrief struct {
	ID                int
	Name              string
	Comment           string
	Datatype          DatapointType
	LastValueInt      *int       `db:"last_value_int"`
	LastValueString   *string    `db:"last_value_string"`
	LastValueDateTime *time.Time `db:"last_value_datetime"`
	NodataIsProblem   bool       `db:"nodata_is_problem"`
}

type DatapointTiny struct {
	ID    int
	Value any
	Valid bool
}

type DatapointValue struct {
	ID            int
	DatapointID   int `db:"datapoint_id"`
	Ts            time.Time
	ValueInt      sql.NullInt64  `db:"value_int"`
	ValueString   sql.NullString `db:"value_string"`
	ValueDateTime sql.NullTime   `db:"value_datetime"`
	TemplateValue any
}

func (dp DatapointValue) Value() any { // {{{
	if dp.ValueInt.Valid {
		return dp.ValueInt.Int64
	}

	if dp.ValueString.Valid {
		return dp.ValueString.String
	}

	if dp.ValueDateTime.Valid {
		return dp.ValueDateTime.Time.In(smonConfig.timezoneLocation)
	}

	return nil
} // }}}
func (dp DatapointValue) FormattedTime() string { // {{{
	if dp.ValueDateTime.Valid {
		return dp.ValueDateTime.Time.In(smonConfig.timezoneLocation).Format("2006-01-02 15:04:05")
	}
	return "invalid time"
} // }}}
func (dp Datapoint) Update() (err error) { // {{{
	name := strings.TrimSpace(dp.Name)
	if name == "" {
		err = errors.New("Name can't be empty")
		return
	}

	if dp.ID == 0 {
		_, err = db.Exec(
			context.Background(),
			`INSERT INTO datapoint("group", name, datatype, nodata_problem_seconds, comment) VALUES($1, $2, $3, $4, $5)`,
			dp.Group,
			name,
			dp.Datatype,
			dp.NodataProblemSeconds,
			dp.Comment,
		)
	} else {
		/* Keep nodata_is_problem as is unless the nodata_problem_seconds is changed.
		 * Otherwise unnecessary nodata problems could be notified when updating unrelated
		 * datapoint properties. */
		_, err = db.Exec(
			context.Background(),
			`
			UPDATE datapoint
			SET
				"group"=$2,
				name=$3,
				datatype=$4,
				comment=$5,
				nodata_problem_seconds=$6,
				nodata_is_problem = (
					CASE
						WHEN $6 != nodata_problem_seconds THEN false
					ELSE
						nodata_is_problem
					END
				)
			WHERE
				id=$1
			`,
			dp.ID,
			dp.Group,
			name,
			dp.Datatype,
			dp.Comment,
			dp.NodataProblemSeconds,
		)
	}
	if err != nil {
		err = werr.Wrap(err)
	}

	return
} // }}}

func DatapointAdd[T any](name string, value T) (err error) { // {{{
	type dpRequest = struct {
		ID    int
		value any
	}

	row := db.QueryRow(context.Background(), `SELECT id, datatype FROM datapoint WHERE name=$1`, name)
	var dpID int
	var dpType DatapointType

	err = row.Scan(&dpID, &dpType)
	if err != nil {
		err = werr.Wrap(err).WithData(struct {
			Name  string
			Value any
		}{name, value})
		return
	}

	switch dpType {
	case INT:
		_, err = db.Exec(context.Background(), `INSERT INTO datapoint_value(datapoint_id, value_int) VALUES($1, $2)`, dpID, value)
	case STRING:
		_, err = db.Exec(context.Background(), `INSERT INTO datapoint_value(datapoint_id, value_string) VALUES($1, $2)`, dpID, value)
	case DATETIME:
		// Time value is required to be a RFC 3339 formatted time string
		var t time.Time
		valueStr, ok := any(value).([]byte)
		if !ok {
			return werr.New("DATETIME value not a string").WithData(dpRequest{dpID, value})
		}
		t, err = stringToTime(string(valueStr))
		if err != nil {
			return werr.Wrap(err).WithData(dpRequest{dpID, value}).Log()
		}

		_, err = db.Exec(context.Background(), `INSERT INTO datapoint_value(datapoint_id, value_datetime) VALUES($1, $2)`, dpID, t)
	}
	if err != nil {
		err = werr.Wrap(err).WithData(dpRequest{dpID, value})
		return
	}

	return
} // }}}

func DatapointsRetrieve() (dps []Datapoint, err error) { // {{{
	dps = []Datapoint{}
	var rows pgx.Rows
	rows, err = db.Query(
		context.Background(),
		`
		SELECT
			id, name, datatype, last_value, "group", comment, nodata_problem_seconds,
			last_value_id AS v_id,
			CASE
				WHEN last_value_id IS NULL THEN null
				ELSE last_value
			END AS ts,
			last_value_int AS value_int,
			last_value_string AS value_string,
			last_value_datetime AS value_datetime
		FROM datapoint
		ORDER BY
			"group" ASC,
			name ASC
	`)
	if err != nil {
		err = werr.Wrap(err)
	}
	defer rows.Close()

	type DbRes struct {
		ID                   int
		Group                string
		Name                 string
		Datatype             DatapointType
		Comment              string
		LastValue            time.Time `db:"last_value"`
		NodataProblemSeconds int       `db:"nodata_problem_seconds"`

		VID           sql.NullInt64 `db:"v_id"`
		Ts            sql.NullTime
		ValueInt      sql.NullInt64  `db:"value_int"`
		ValueString   sql.NullString `db:"value_string"`
		ValueDateTime sql.NullTime   `db:"value_datetime"`
	}

	dps, err = pgx.CollectRows(
		rows,
		func(row pgx.CollectableRow) (dp Datapoint, err error) {
			var dpv DatapointValue
			var res DbRes
			res, err = pgx.RowToStructByName[DbRes](row)
			if err != nil {
				err = werr.Wrap(err)
				return
			}

			dp.ID = res.ID
			dp.Name = res.Name
			dp.Group = res.Group
			dp.Datatype = res.Datatype
			dp.Comment = res.Comment
			dp.LastValue = res.LastValue
			dp.Found = true
			dp.NodataProblemSeconds = res.NodataProblemSeconds

			if res.VID.Valid {
				dpv.ID = int(res.VID.Int64)
				dpv.Ts = res.Ts.Time
				dpv.ValueInt = res.ValueInt
				dpv.ValueString = res.ValueString
				dpv.ValueDateTime = res.ValueDateTime

				dp.LastDatapointValue = dpv
			}
			return
		})

	if err != nil {
		err = werr.Wrap(err)
		return
	}

	return
} // }}}
func DatapointRetrieve(id int, name string) (dp Datapoint, err error) { // {{{
	var query string
	var param any
	if id > 0 {
		query = `SELECT id, "group", name, "datatype", comment, last_value, nodata_problem_seconds, nodata_is_problem, true AS found FROM public.datapoint WHERE id = $1`
		param = id
		dp.ID = id
	} else {
		query = `SELECT id, "group", name, "datatype", comment, last_value, nodata_problem_seconds, nodata_is_problem, true AS found FROM public.datapoint WHERE name = $1`
		param = name
	}

	rows, _ := db.Query(context.Background(), query, param)
	dp, err = pgx.CollectExactlyOneRow(rows, pgx.RowToStructByNameLax[Datapoint])
	if err != nil {
		err = werr.Wrap(err)
		return
	}

	if err == sql.ErrNoRows {
		dp = Datapoint{
			Name: name,
		}
		err = nil
		return
	}

	if err != nil {
		err = werr.Wrap(err).WithData(name)
		return
	}

	rows, _ = db.Query(
		context.Background(),
		`
			SELECT *
			FROM datapoint_value
			WHERE datapoint_id = $1
			ORDER BY ts DESC
			LIMIT 1
		`,
		dp.ID,
	)
	dp.LastDatapointValue, err = pgx.CollectExactlyOneRow(rows, pgx.RowToStructByNameLax[DatapointValue])
	if err == sql.ErrNoRows {
		err = nil
		return
	}

	if err != nil {
		err = werr.Wrap(err).WithData(dp.ID)
		return
	}

	return
} // }}}
func DatapointDelete(id int) (err error) { // {{{
	var dpName string
	err = db.QueryRow(context.Background(), `SELECT name FROM public.datapoint WHERE id = $1`, id).Scan(&dpName)
	if err != nil {
		err = werr.Wrap(err).WithData(id)
		return
	}

	var triggerNames []string
	rows, _ := db.Query(context.Background(), `SELECT name FROM public.trigger WHERE datapoints ? $1`, dpName)
	triggerNames, err = pgx.CollectRows(rows, pgx.RowTo[string])
	if err != nil {
		err = werr.Wrap(err).WithData(id)
		return
	}

	if len(triggerNames) > 0 {
		return werr.New("Datapoint '%s' used in the following triggers: %s", dpName, strings.Join(triggerNames, ", "))
	}

	_, err = db.Exec(context.Background(), `DELETE FROM datapoint WHERE id=$1`, id)
	if err != nil {
		err = werr.Wrap(err).WithData(id)
		return
	}

	return
} // }}}
func DatapointValues(id int, from, to time.Time) (values []DatapointValue, err error) { // {{{
	_, err = db.Exec(context.Background(), `SELECT set_config('timezone', $1, false)`, smonConfig.Timezone().String())
	if err != nil {
		err = werr.Wrap(err).WithData(smonConfig.Timezone().String())
		return
	}

	rows, _ := db.Query(
		context.Background(),
		`
		SELECT
			id,
			datapoint_id,
			ts,
			value_int,
			value_string,
			value_datetime
		FROM datapoint_value
		WHERE
			datapoint_id=$1 AND
			ts >= $2 AND
			ts <= $3
		ORDER BY
			ts DESC
		`,
		id,
		from,
		to,
	)
	values, err = pgx.CollectRows(rows, pgx.RowToStructByName[DatapointValue])
	if err != nil {
		err = werr.Wrap(err).WithData(id)
		return
	}

	return
} // }}}

// DatapointsValue only returns ID and type and is used primarily for the dashboard.
func DatapointsValue(ids []int) (values []DatapointTiny, err error) { // {{{
	values = []DatapointTiny{}

	rows, _ := db.Query(
		context.Background(),
		`
		SELECT
			id,
			name,
			comment,
			datatype,
			last_value_int,
			last_value_string,
			last_value_datetime,
			nodata_is_problem
		FROM datapoint
		WHERE
			id = ANY($1)
		`,
		ids,
	)

	var briefs []DatapointBrief
	briefs, err = pgx.CollectRows(rows, pgx.RowToStructByNameLax[DatapointBrief])
	if err != nil {
		err = werr.Wrap(err)
		return
	}

	for _, brief := range briefs {
		tiny := DatapointTiny{}
		tiny.ID = brief.ID
		tiny.Valid = !brief.NodataIsProblem

		switch brief.Datatype {
		case "INT":
			tiny.Value = brief.LastValueInt
		case "STRING":
			tiny.Value = brief.LastValueString
		case "DATETIME":
			tiny.Value = brief.LastValueDateTime
		}

		values = append(values, tiny)
	}

	return
} // }}}