BrainMinder/models/field.go

package models

import (
	"database/sql"
	"errors"

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

type FieldModel struct {
	DB *database.DB
}

type Field struct {
	Type_field_id int64  `db:"type_field_id"`
	Widget_id     int64  `db:"widget_id"`
	Type_id       int64  `db:"type_id"`
	Widget        string `db:"widget"`
	Title         string `db:"title"`
	Position      int    `db:"position"`
	Show_on_list  int64  `db:"show_on_list"`
	Show_on_view  int64  `db:"show_on_view"`
	Valid_values  string `db:"valid_values"`
	Ui_section    string `db:"ui_section"`
	Is_multiple   int64  `db:"is_multiple"`
	ToRemove      int64
}

type FieldValue struct {
	Type_field_id 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(type_id 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, type_id)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}

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

	return rowsBySection, true, err
}

func (model *FieldModel) ByType(type_id 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, type_id)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}

	return rows, true, err
}

func (model *FieldModel) ByTypeOnList(type_id 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, type_id)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}

	return rows, true, err
}

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

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

	_, err := model.DB.ExecContext(ctx, qy_delete, item_id, type_field_id)
	if errors.Is(err, sql.ErrNoRows) {
		return false
	}

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

	for counter, value := range values {
		model.DB.MustExecContext(ctx, qy_insert, item_id, type_field_id, counter, value)
	}

	return true
}

func (model *FieldModel) GetFieldValues(item_id int64, type_field_id 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=?", item_id, type_field_id)

	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(item_id 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, item_id)

	return values
}

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

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

	for _, item_value := range values {
		_, found := values_map[item_value.Type_field_id]
		if !found {
			values_map[item_value.Type_field_id] = make(map[int]string)
		}
		values_map[item_value.Type_field_id][item_value.Counter] = item_value.Value
	}

	return values_map
}