BrainMinder/models/field.go

package models

import (
	"database/sql"
	"errors"

	"brainminder.speedtech.it/internal/database"
)

type FieldModel struct {
	DB *database.DB
}

type Field struct {
	TypeFieldId int64  `db:"type_field_id"`
	WidgetId    int64  `db:"widget_id"`
	TypeId      int64  `db:"type_id"`
	Widget      string `db:"widget"`
	Title       string `db:"title"`
	Position    int    `db:"position"`
	ShowOnList  int64  `db:"show_on_list"`
	ShowOnView  int64  `db:"show_on_view"`
	ValidValues string `db:"valid_values"`
	UiSection   string `db:"ui_section"`
	IsMultiple  int64  `db:"is_multiple"`
	ToRemove    int64
}

type FieldValue struct {
	TypeFieldId int64  `db:"type_field_id"`
	Value       string `db:"value"`
	Title       string `db:"title"`
	Widget      string `db:"widget"`
	Number      int64  `db:"number"`
	Counter     int    `db:"counter"`
}

func (model *FieldModel) ByTypeSection(typeId int64) (map[string][]Field, bool, error) {
	ctx, cancel := database.GetContext()
	defer cancel()

	query := `SELECT btf.id AS type_field_id, btf.widget_id, btf.type_id, bw.widget, btf.title, btf.position,
	btf.valid_values, btf.show_on_list, btf.show_on_view, btf.ui_section, btf.is_multiple 
	FROM bm_type_fields btf INNER JOIN bm_widgets bw ON btf.widget_id=bw.id WHERE btf.type_id=$1 ORDER BY btf.position`

	var rows []Field
	rowsBySection := make(map[string][]Field)

	err := model.DB.SelectContext(ctx, &rows, query, typeId)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}

	for _, row := range rows {
		if value, found := rowsBySection[row.UiSection]; found {
			rowsBySection[row.UiSection] = append(value, row)
		} else {
			rowsBySection[row.UiSection] = []Field{row}
		}
	}

	return rowsBySection, true, err
}

func (model *FieldModel) ByType(typeId int64) ([]Field, bool, error) {
	ctx, cancel := database.GetContext()
	defer cancel()

	query := `SELECT btf.id AS type_field_id, btf.widget_id, btf.type_id, bw.widget, btf.title, btf.position, 
	btf.valid_values, btf.show_on_list, btf.show_on_view, btf.ui_section, btf.is_multiple 
	FROM bm_type_fields btf INNER JOIN bm_widgets bw ON btf.widget_id=bw.id WHERE btf.type_id=$1 ORDER BY btf.position`

	var rows []Field

	err := model.DB.SelectContext(ctx, &rows, query, typeId)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}

	return rows, true, err
}

func (model *FieldModel) ByTypeOnList(typeId int64) ([]Field, bool, error) {
	ctx, cancel := database.GetContext()
	defer cancel()

	query := `SELECT btf.id AS type_field_id, btf.widget_id, btf.type_id, bw.widget, btf.title, btf.position, 
	btf.valid_values, btf.show_on_list, btf.show_on_view, btf.ui_section, btf.is_multiple
	FROM bm_type_fields btf INNER JOIN bm_widgets bw ON btf.widget_id=bw.id WHERE btf.show_on_list=1 AND btf.type_id=$1 ORDER BY btf.position`

	var rows []Field

	err := model.DB.SelectContext(ctx, &rows, query, typeId)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}

	return rows, true, err
}

func (model *FieldModel) SaveValues(itemId int64, typeFieldId int64, values map[int]string) bool {
	ctx, cancel := database.GetContext()
	defer cancel()

	qyDelete := `DELETE FROM bm_item_fields WHERE item_id=? AND type_field_id=?`

	_, err := model.DB.ExecContext(ctx, qyDelete, itemId, typeFieldId)
	if errors.Is(err, sql.ErrNoRows) {
		return false
	}

	qyInsert := `INSERT INTO bm_item_fields (item_id, type_field_id, counter, value) VALUES(?, ?, ?, ?)`

	for counter, value := range values {
		model.DB.MustExecContext(ctx, qyInsert, itemId, typeFieldId, counter, value)
	}

	return true
}

func (model *FieldModel) GetFieldValues(itemId int64, typeFieldId int64) map[int]string {
	ctx, cancel := database.GetContext()
	defer cancel()

	rows, _ := model.DB.QueryContext(ctx, "SELECT counter, value FROM bm_item_fields WHERE item_id=? AND type_field_id=?", itemId, typeFieldId)

	values := make(map[int]string)

	for rows.Next() {
		var counter int
		var value string
		rows.Scan(&counter, &value)

		values[counter] = value
	}

	return values
}

func (model *FieldModel) GetFieldsValues(itemId int64) []FieldValue {
	ctx, cancel := database.GetContext()
	defer cancel()

	var values []FieldValue
	q := `SELECT bw.widget, btf.title, bif.type_field_id, bif.value, bif.counter FROM bm_item_fields bif 
	INNER JOIN bm_type_fields btf on btf.id=bif.type_field_id 
	INNER JOIN bm_widgets bw on bw.id=btf.widget_id 
	WHERE bif.item_id=$1`
	model.DB.SelectContext(ctx, &values, q, itemId)

	return values
}

func (model *FieldModel) GetFieldsValuesAsMap(itemId int64) map[int64]map[int]string {
	values := model.GetFieldsValues(itemId)

	valuesMap := make(map[int64]map[int]string)

	for _, itemValue := range values {
		_, found := valuesMap[itemValue.TypeFieldId]
		if !found {
			valuesMap[itemValue.TypeFieldId] = make(map[int]string)
		}
		valuesMap[itemValue.TypeFieldId][itemValue.Counter] = itemValue.Value
	}

	return valuesMap
}