dbfunc.go

package main

import (
	"database/sql"
	"fmt"
	"os"
	"strconv"
	"strings"

	//"github.com/lib/pq"
	_ "github.com/lib/pq"
	_ "modernc.org/sqlite"
)

type Database struct {
	MainDB *sql.DB // Main database connection
	FtsDB  *sql.DB
	PG     *sql.DB
}

func (db *Database) entryTidFromId(id int) int {
	var tid int
	_ = db.MainDB.QueryRow("SELECT tid FROM task WHERE id=?;", id).Scan(&tid)
	return tid
}

func (db *Database) keywordExists(title string) (string, bool) {
	var uuid string
	err := db.MainDB.QueryRow("SELECT uuid FROM keyword WHERE title=?;", title).Scan(&uuid)
	if err != nil {
		return "", false
	}
	return uuid, true
}

func (db *Database) filterTitle(filter string, tid int) string {
	row := db.MainDB.QueryRow(fmt.Sprintf("SELECT title FROM %s WHERE tid=?;", filter), tid)
	var title string
	err := row.Scan(&title)
	if err != nil {
		return ""
	}
	return title
}

func (db *Database) contextExists(title string) (string, bool) {
	var uuid string
	err := db.MainDB.QueryRow("SELECT uuid FROM context WHERE title=?;", title).Scan(&uuid)
	if err != nil {
		return "", false
	}
	return uuid, true
}

func (db *Database) folderExists(title string) (string, bool) {
	var uuid string
	err := db.MainDB.QueryRow("SELECT uuid FROM folder WHERE title=?;", title).Scan(&uuid)
	if err != nil {
		return "", false
	}
	return uuid, true
}

func (db *Database) contextList() map[string]struct{} {
	rows, _ := db.MainDB.Query("SELECT title FROM context;")
	defer rows.Close()

	contexts := make(map[string]struct{})
	for rows.Next() {
		var title string
		_ = rows.Scan(&title)
		contexts[title] = struct{}{}
	}
	return contexts
}

func (db *Database) folderList() map[string]struct{} {
	rows, _ := db.MainDB.Query("SELECT title FROM folder;")
	defer rows.Close()

	folders := make(map[string]struct{})
	for rows.Next() {
		var title string
		_ = rows.Scan(&title)
		folders[title] = struct{}{}
	}
	return folders
}

func (db *Database) keywordList() map[string]struct{} {
	rows, _ := db.MainDB.Query("SELECT title FROM keyword;")
	defer rows.Close()

	keywords := make(map[string]struct{})
	for rows.Next() {
		var title string
		_ = rows.Scan(&title)
		keywords[title] = struct{}{}
	}
	return keywords
}
func (db *Database) toggleStar(id int, state bool, table string) error {
	s := fmt.Sprintf("UPDATE %s SET star=?, modified=datetime('now') WHERE id=?;",
		table)
	_, err := db.MainDB.Exec(s, !state, id)
	return err
}

func (db *Database) toggleDeleted(id int, state bool, table string) error {
	s := fmt.Sprintf("UPDATE %s SET deleted=?, modified=datetime('now') WHERE id=?;", table)
	_, err := db.MainDB.Exec(s, !state, id)
	return err
}

func (db *Database) toggleArchived(id int, state bool, table string) error {
	s := fmt.Sprintf("UPDATE %s SET archived=?, modified=datetime('now') WHERE id=?;", table)
	_, err := db.MainDB.Exec(s, !state, id)
	return err
}

func (db *Database) updateTaskContextByUUID(uuid string, id int) error {
	_, err := db.MainDB.Exec("UPDATE task SET context_uuid=?, modified=datetime('now') WHERE id=?;",
		uuid, id)
	return err
}

func (db *Database) updateTaskFolderByUUID(uuid string, id int) error {
	_, err := db.MainDB.Exec("UPDATE task SET folder_uuid=?, modified=datetime('now') WHERE id=?;",
		uuid, id)
	return err
}

func (db *Database) updateNote(id int, text string) error {

	var nullableText sql.NullString
	if len(text) != 0 {
		nullableText.String = text
		nullableText.Valid = true
	}

	_, err := db.MainDB.Exec("UPDATE task SET note=?, modified=datetime('now') WHERE id=?;",
		nullableText, id)
	if err != nil {
		return err
	}

	/***************fts virtual table update*********************/
	entry_tid := db.entryTidFromId(id)
	_, err = db.FtsDB.Exec("UPDATE fts SET note=? WHERE tid=?;", text, entry_tid)
	return err
}

func (db *Database) getSyncItems(sort string, max int) []Row {
	rows, err := db.MainDB.Query(fmt.Sprintf("SELECT id, title, %s FROM sync_log ORDER BY %s DESC LIMIT %d", sort, sort, max))
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in getSyncItems: %v", err)
		return []Row{}
	}

	defer rows.Close()

	//org.rows = nil
	var orgRows []Row
	for rows.Next() {
		var row Row
		var sort string

		err = rows.Scan(&row.id, &row.title, &sort)

		if err != nil {
			app.Organizer.ShowMessage(BL, "Error in getSyncItems: %v", err)
			return []Row{}
		}

		row.sort = timeDelta(sort)
		orgRows = append(orgRows, row)
		//org.rows = append(org.rows, row)

	}
	return orgRows
}

func (db *Database) deleteSyncItem(id int) {
	_, err := db.MainDB.Exec("DELETE FROM sync_log  WHERE id=?;", id)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error deleting sync_log entry with id %d: %v", id, err)
		return
	}
	app.Organizer.ShowMessage(BL, "Deleted sync_log entry with id %d", id)
}

func (db *Database) filterEntries(taskView int, filter interface{}, showDeleted bool, sort string, sortPriority bool, max int) ([]Row, error) {

	s := fmt.Sprintf("SELECT task.id, task.title, task.star, task.deleted, task.archived, task.note, task.%s FROM task ", sort)

	switch taskView {
	case BY_CONTEXT:
		s += "JOIN context ON context.uuid=task.context_uuid WHERE context.title=?"
	case BY_FOLDER:
		s += "JOIN folder ON folder.uuid = task.folder_uuid WHERE folder.title=?"
	case BY_KEYWORD:
		s += "JOIN task_keyword ON task.tid=task_keyword.task_tid " +
			"JOIN keyword ON keyword.uuid=task_keyword.keyword_uuid " +
			"WHERE task.tid = task_keyword.task_tid AND " +
			"task_keyword.keyword_uuid = keyword.uuid AND keyword.title=?"
	case BY_RECENT:
		//s += "WHERE 1=1"
		s += "WHERE 1=?"
		filter = 1
	default:
		app.Organizer.ShowMessage(BL, "You asked for an unsupported db query")
		return []Row{}, nil //FIXME
	}

	if !showDeleted {
		s += " AND task.archived=false AND task.deleted=false"
	}
	if sortPriority {
		s += fmt.Sprintf(" ORDER BY task.star DESC, task.%s DESC LIMIT %d;", sort, max)
	} else {
		s += fmt.Sprintf(" ORDER BY task.%s DESC LIMIT %d;", sort, max) //01162022
	}
	rows, err := db.MainDB.Query(s, filter)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in getItems: %v", err)
		return []Row{}, err
	}
	defer rows.Close()

	var orgRows []Row
	for rows.Next() {
		var row Row
		var sort sql.NullString
		var note sql.NullString

		err = rows.Scan(
			&row.id,
			&row.title,
			&row.star,
			&row.deleted,
			&row.archived,
			&note,
			&sort,
		)

		if err != nil {
			app.Organizer.ShowMessage(BL, "Error in filterEntries: %v", err)
			//return orgRows
			return orgRows, err
		}

		if note.Valid && note.String != "" {
			row.hasImage = containsGoogleDriveImage(note.String)
		}

		if sort.Valid {
			row.sort = timeDelta(sort.String)
		} else {
			row.sort = ""
		}

		orgRows = append(orgRows, row)

	}
	return orgRows, nil
}

func (db *Database) updateTitle(row *Row) error {
	_, err := db.MainDB.Exec("UPDATE task SET title=?, modified=datetime('now') WHERE id=?", row.title, row.id)
	if err != nil {
		return err
	}

	/***************fts virtual table update*********************/
	entry_tid := db.entryTidFromId(row.id)

	_, err = db.FtsDB.Exec("UPDATE fts SET title=? WHERE tid=?;", row.title, entry_tid)
	if err != nil {
		return err
	}
	return nil
}

func (db *Database) insertTitle(row *Row, context_uuid, folder_uuid string) error { // should return err

	var id int
	err := db.MainDB.QueryRow("INSERT INTO task (title, folder_uuid, context_uuid, star, added) "+
		"VALUES (?, ?, ?, ?, datetime('now')) RETURNING id;",
		row.title, folder_uuid, context_uuid, row.star).Scan(&id)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error inserting into DB: %v", err)
		//return -1
		return err
	}
	row.id = id
	row.dirty = false

	/**** note if we are inserting a new entry that there is nothing in the fts table to update
	/***************fts virtual table update*********************
	entry_tid := db.entryTidFromId(id)

	_, err = db.FtsDB.Exec("UPDATE fts SET title=? WHERE tid=?;", row.title, entry_tid)
	if err != nil {
		return err
	}
	*/
	return nil
}

func (db *Database) insertSyncEntry(title, note string) {
	_, err := db.MainDB.Exec("INSERT INTO sync_log (title, note, modified) VALUES (?, ?, datetime('now'));",
		title, note)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Wrote sync log to db")
	}
}

func (db *Database) readNoteIntoString(id int) string {
	if id == -1 {
		return "" // id given to new and unsaved entries
	}

	row := db.MainDB.QueryRow("SELECT note FROM task WHERE id=?;", id)
	var note sql.NullString
	err := row.Scan(&note)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error retrieving note for id %d: %v", id, err)
		return ""
	}
	return note.String
}

func (db *Database) readSyncLog(id int) string {
	row := db.MainDB.QueryRow("SELECT note FROM sync_log WHERE id=?;", id)
	var note string
	err := row.Scan(&note)
	if err != nil {
		return ""
	}
	return note
}

func (db *Database) getEntryInfo(id int) NewEntry {
	if id == -1 {
		return NewEntry{}
	}
	// Note: folder_tid and context_tid are deprecated in favor of uuid-based lookups
	// We no longer query them here - use taskFolder() and taskContext() instead
	row := db.MainDB.QueryRow("SELECT id, tid, title, star, added, archived, deleted, modified FROM task WHERE id=?;", id)

	var e NewEntry
	var tid sql.NullInt64
	err := row.Scan(
		&e.id,
		&tid,
		&e.title,
		&e.star,
		&e.added,
		&e.archived,
		&e.deleted,
		&e.modified,
	)
	e.tid = int(tid.Int64)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in getEntryInfo for id %d: %v", id, err)
		return NewEntry{}
	}
	return e
}

func (db *Database) taskFolder(id int) string {
	// Query folder title by joining on uuid
	row := db.MainDB.QueryRow("SELECT folder.title FROM task JOIN folder on task.folder_uuid = folder.uuid WHERE task.id=?;", id)
	var title string
	err := row.Scan(&title)
	if err != nil {
		return ""
	}
	return title
}

func (db *Database) taskContext(id int) string {
	row := db.MainDB.QueryRow("SELECT context.title FROM task JOIN context on task.context_uuid = context.uuid WHERE task.id=?;", id)
	var title string
	err := row.Scan(&title)
	if err != nil {
		return ""
	}
	return title
}

/*
func getContextTid(id int) int {
	row := db.QueryRow("SELECT context_tid FROM task WHERE id=?;", id)
	var tid int
	err := row.Scan(&tid)
	if err != nil {
		return -1
	}
	return tid
}
*/
// not currently in use
func (db *Database) getTitle(id int) string {
	row := db.MainDB.QueryRow("SELECT title FROM task WHERE id=?;", id)
	var title string
	err := row.Scan(&title)
	if err != nil {
		return ""
	}
	return title
}

func (db *Database) getTaskKeywords(id int) string {

	entry_tid := db.entryTidFromId(id) /////////////////////////////////////////////////////

	//rows, err := db.Query("SELECT keyword.name FROM task_keyword LEFT OUTER JOIN keyword ON "+
	//	"keyword.id=task_keyword.keyword_id WHERE task_keyword.task_id=?;", id)

	rows, err := db.MainDB.Query("SELECT keyword.title FROM task_keyword LEFT OUTER JOIN keyword ON "+
		"keyword.uuid=task_keyword.keyword_uuid WHERE task_keyword.task_tid=?;", entry_tid)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in getTaskKeywords for entry id %d: %v", id, err)
		return ""
	}
	defer rows.Close()

	kk := []string{}
	for rows.Next() {
		var title string

		err = rows.Scan(&title)
		kk = append(kk, title)
	}
	if len(kk) == 0 {
		return ""
	}
	return strings.Join(kk, ",")
}

/*
	Doesn't appear to be used

func getTaskKeywordTids(id int) []int {

		entry_tid := entryTidFromId(id) /////////////////////////////////////////////////////

		keyword_tids := []int{}
		rows, err := db.Query("SELECT keyword_tid FROM task_keyword LEFT OUTER JOIN keyword ON "+
			"keyword.tid=task_keyword.keyword_tid WHERE task_keyword.task_tid=?;", entry_tid)
		if err != nil {
			sess.showOrgMessage("Error in getTaskKeywordIds for entry id %d: %v", id, err)
			return keyword_tids
		}
		defer rows.Close()

		for rows.Next() {
			var tid int
			err = rows.Scan(&tid)
			keyword_tids = append(keyword_tids, tid)
		}
		return keyword_tids
	}
*/
func (db *Database) updateFtsTitle(st string, row *Row) error {
	tid := db.entryTidFromId(row.id)
	r := db.FtsDB.QueryRow("SELECT highlight(fts, 0, '\x1b[48;5;31m', '\x1b[49m') FROM fts WHERE fts MATCH ? and tid=?;", st, tid)
	var ftsTitle string
	err := r.Scan(&ftsTitle)
	if err != nil {
		return err
	}
	row.ftsTitle = ftsTitle
	return nil
}

func (db *Database) searchEntries(st, sort string, showDeleted, help bool) ([]Row, error) {
	rows, err := db.FtsDB.Query("SELECT tid, highlight(fts, 0, '\x1b[48;5;31m', '\x1b[49m') "+
		"FROM fts WHERE fts MATCH ? ORDER BY bm25(fts, 2.0, 1.0, 5.0);", st)
	if err != nil {
		return []Row{}, err
	}
	defer rows.Close()

	var ftsTids []int
	var ftsTitles = make(map[int]string)

	for rows.Next() {
		var ftsTid int
		var ftsTitle string

		err = rows.Scan(
			&ftsTid,
			&ftsTitle,
		)

		if err != nil {
			return []Row{}, err
		}
		ftsTids = append(ftsTids, ftsTid)
		ftsTitles[ftsTid] = ftsTitle
	}

	if len(ftsTids) == 0 {
		return []Row{}, nil
	}

	// As noted above, if the item is deleted (gone) from the db it's id will not be found if it's still in fts
	stmt := fmt.Sprintf("SELECT task.id, task.tid, task.title, task.star, task.deleted, task.archived, task.note, task.%s FROM task WHERE ", sort)
	if help {
		stmt += "task.context_tid = 16 and task.tid IN ("
	} else {
		stmt += "task.tid IN ("
	}

	max := len(ftsTids) - 1
	for i := 0; i < max; i++ {
		stmt += strconv.Itoa(ftsTids[i]) + ", "
	}

	stmt += strconv.Itoa(ftsTids[max]) + ")"
	if showDeleted {
		stmt += " ORDER BY "
	} else {
		stmt += " AND task.archived=false AND task.deleted=false ORDER BY "
	}

	for i := 0; i < max; i++ {
		stmt += "task.tid = " + strconv.Itoa(ftsTids[i]) + " DESC, "
	}
	stmt += "task.tid = " + strconv.Itoa(ftsTids[max]) + " DESC"

	rows, err = db.MainDB.Query(stmt)
	if err != nil {
		return []Row{}, err
	}
	var orgRows []Row
	for rows.Next() {
		var row Row
		var sort string
		var note sql.NullString

		err = rows.Scan(
			&row.id,
			&row.tid,
			&row.title,
			&row.star,
			&row.deleted,
			&row.archived,
			&note,
			&sort,
		)

		if err != nil {
			return []Row{}, err
		}

		if note.Valid && note.String != "" {
			row.hasImage = containsGoogleDriveImage(note.String)
		}

		row.sort = timeDelta(sort)
		row.ftsTitle = ftsTitles[row.tid]

		orgRows = append(orgRows, row)
	}
	return orgRows, nil
}

func (db *Database) getContainers(view View) []Row {
	stmt := fmt.Sprintf("SELECT id, title, star, deleted, modified FROM %s ORDER BY title COLLATE NOCASE ASC;", view)
	rows, err := db.MainDB.Query(stmt)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error SELECTING id, title, star, deleted, modified FROM %s", view)
		return []Row{}
	}
	defer rows.Close()

	var orgRows []Row
	for rows.Next() {
		var r Row
		var sort string
		rows.Scan(
			&r.id,
			&r.title,
			&r.star,
			&r.deleted,
			&sort,
		)
		r.sort = timeDelta(sort)
		orgRows = append(orgRows, r)
	}
	return orgRows
}

func (db *Database) getAltContainers(altView View) []AltRow {
	var orgAltRows []AltRow
	stmt := fmt.Sprintf("SELECT id, title, star FROM %s ORDER BY title COLLATE NOCASE ASC;", altView)
	rows, err := db.MainDB.Query(stmt)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error SELECTING id, title, star FROM %s", altView)
		return []AltRow{}
	}
	defer rows.Close()
	for rows.Next() {
		var r AltRow
		rows.Scan(
			&r.id,
			&r.title,
			&r.star,
		)

		orgAltRows = append(orgAltRows, r)
	}
	return orgAltRows
}

func (db *Database) getContainerInfo(id int, view View) Container {

	/*
		type Container struct {
			id       int
			tid      int
			title    string
			star     bool
			created  string
			deleted  bool
			modified string
			count    int
		}
	*/

	if id == -1 {
		return Container{}
	}

	var countQuery string
	switch view {
	case CONTEXT:
		// Join on uuid for context
		countQuery = "SELECT COUNT(*) FROM task JOIN context ON context.uuid = task.context_uuid WHERE context.id=?;"
	case FOLDER:
		// Join on uuid for folder
		countQuery = "SELECT COUNT(*) FROM task JOIN folder ON folder.uuid = task.folder_uuid WHERE folder.id=?;"
	case KEYWORD:
		// Join on uuid for keyword
		countQuery = "SELECT COUNT(*) FROM task_keyword WHERE keyword_uuid=(SELECT uuid FROM keyword WHERE id=?);"
	default:
		app.Organizer.ShowMessage(BL, "Somehow you are in a view I can't handle")
		return Container{}
	}

	var c Container

	row := db.MainDB.QueryRow(countQuery, id)
	err := row.Scan(&c.count)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in getContainerInfo: %v", err)
		return Container{}
	}

	//stmt := fmt.Sprintf("SELECT %s FROM %s WHERE id=?;", columns, table)
	stmt := fmt.Sprintf("SELECT id, tid, title, star, deleted, modified FROM %s WHERE id=?;", view)
	row = db.MainDB.QueryRow(stmt, id)
	var tid sql.NullInt64
	err = row.Scan(
		&c.id,
		&tid,
		&c.title,
		&c.star,
		&c.deleted,
		&c.modified,
	)
	c.tid = int(tid.Int64)

	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in getContainerInfo: %v", err)
		return Container{}
	}
	return c
}

func (db *Database) addTaskKeywordByUUID(keyword_uuid string, entry_id int, update_fts bool) {
	entry_tid := db.entryTidFromId(entry_id)

	// Look up keyword_tid from uuid (needed because task_keyword.keyword_tid is NOT NULL)
	var keyword_tid int
	err := db.MainDB.QueryRow("SELECT tid FROM keyword WHERE uuid=?;", keyword_uuid).Scan(&keyword_tid)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in addTaskKeywordByUUID - looking up keyword tid: %v", err)
		return
	}

	_, err = db.MainDB.Exec("INSERT OR IGNORE INTO task_keyword (task_tid, keyword_tid, keyword_uuid) VALUES (?, ?, ?);",
		entry_tid, keyword_tid, keyword_uuid)

	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in addTaskKeywordByUUID - INSERT or IGNORE INTO task_keyword: %v", err)
		return
	}

	_, err = db.MainDB.Exec("UPDATE task SET modified = datetime('now') WHERE id=?;", entry_id)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in addTaskKeywordByUUID - Update task modified: %v", err)
		return
	}

	// *************fts virtual table update**********************
	if !update_fts {
		return
	}
	s := db.getTaskKeywords(entry_id)
	//_, err = fts_db.Exec("UPDATE fts SET tag=? WHERE lm_id=?;", s, entry_id)
	_, err = db.FtsDB.Exec("UPDATE fts SET tag=? WHERE tid=?;", s, entry_tid)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in addTaskKeywordByUUID - fts Update: %v", err)
	}
}

/*
// not in use but worked
func getNoteSearchPositions__(id int) [][]int {
	row := fts_db.QueryRow("SELECT rowid FROM fts WHERE lm_id=?;", id)
	var rowid int
	err := row.Scan(&rowid)
	if err != nil {
		return [][]int{}
	}
	var word_positions [][]int
	for i, term := range strings.Split(app.Session.fts_search_terms, " ") {
		word_positions = append(word_positions, []int{})
		rows, err := fts_db.Query("SELECT offset FROM fts_v WHERE doc=? AND term=? AND col='note';", rowid, term)
		if err != nil {
			app.Organizer.ShowMessage(BL, "Error in getNoteSearchPositions - 'SELECT offset FROM fts_v': %v", err)
			return [][]int{}
		}
		defer rows.Close()

		for rows.Next() {
			var offset int
			err = rows.Scan(&offset)
			if err != nil {
				app.Organizer.ShowMessage(BL, "Error in getNoteSearchPositions - 'rows.Scan(&offset)': %v", err)
				continue
			}
			word_positions[i] = append(word_positions[i], offset)
		}
	}
	return word_positions
}
*/

func (db *Database) updateContainerTitle(row *Row, view View) error {
	if row.id == -1 {
		err := db.insertContainer(row, view)
		return err
	}
	stmt := fmt.Sprintf("UPDATE %s SET title=?, modified=datetime('now') WHERE id=?", view)
	_, err := db.MainDB.Exec(stmt, row.title, row.id)
	return err
}

func (db *Database) insertContainer(row *Row, view View) error {
	newUUID := generateUUID()
	stmt := fmt.Sprintf("INSERT INTO %s (title, uuid, star, deleted, modified) ", view)
	stmt += "VALUES (?, ?, ?, False, datetime('now')) RETURNING id;"
	var id int
	err := db.MainDB.QueryRow(stmt, row.title, newUUID, row.star).Scan(&id)
	if err != nil {
		//sess.showOrgMessage("Error in insertContainer: %v", err)
		return err
	}
	row.id = id
	row.dirty = false

	return nil
}

func (db *Database) deleteKeywords(id int) int {
	entry_tid := db.entryTidFromId(id)
	res, err := db.MainDB.Exec("DELETE FROM task_keyword WHERE task_tid=?;", entry_tid)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error deleting from task_keyword: %v", err)
		return -1
	}
	rowsAffected, _ := res.RowsAffected()
	_, err = db.MainDB.Exec("UPDATE task SET modified=datetime('now') WHERE id=?;", id)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error updating entry modified column in deleteKeywords: %v", err)
		return -1
	}

	//_, err = fts_db.Exec("UPDATE fts SET tag='' WHERE lm_id=?", id)
	_, err = db.FtsDB.Exec("UPDATE fts SET tag='' WHERE tid=?", entry_tid)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error updating fts in deleteKeywords: %v", err)
		return -1
	}
	return int(rowsAffected)
}

// not in use but worked
func highlightTerms__(text string, word_positions [][]int) string {

	delimiters := " |,.;?:()[]{}&#/`-'\"—_<>$~@=&*^%+!\t\n\\" //must have \f if using it as placeholder

	for _, v := range word_positions {
		app.Organizer.ShowMessage(BR, "%v", word_positions)

		// start and end are positions in the text
		// word_num is what word number we are at in the text
		//wp is the position that we are currently looking for to highlight

		word_num := -1 //word position in text
		end := -1
		var start int

		// need to be non-punctuation because syntax highlighting
		// appears to strip some punctuation
		pre := "uuu"
		post := "yyy"
		add := len(pre) + len(post)
		for _, wp := range v {

			for {
				// I don't think the check below is necessary but we'll see
				if end >= len(text)-1 {
					break
				}

				start = start + end + 1
				end = strings.IndexAny(text[start:], delimiters)
				if end == -1 {
					end = len(text) - 1
				}

				if end != 0 { //if end = 0 we were sitting on a delimiter like a space
					word_num++
				}

				if wp == word_num {
					//text = text[:start+end] + "\x1b[48;5;235m" + text[start+end:]
					text = fmt.Sprintf("%s%s%s", text[:start+end], post, text[start+end:])
					//text = text[:start] + "\x1b[48;5;31m" + text[start:]
					text = fmt.Sprintf("%s%s%s", text[:start], pre, text[start:])
					end += add
					break // this breaks out of loop that was looking for the current highlighted word position
				}
			}
		}
	}
	return text
}

// current method in use
func (db *Database) highlightTerms2(id int) string {
	if id == -1 {
		return "" // id given to new and unsaved entries
	}
	entry_tid := db.entryTidFromId(id)

	//row := fts_db.QueryRow("SELECT highlight(fts, 1, 'qx', 'qy') "+
	//	"FROM fts WHERE lm_id=$1 AND fts MATCH $2;", id, sess.fts_search_terms)
	row := db.FtsDB.QueryRow("SELECT highlight(fts, 1, 'qx', 'qy') "+
		"FROM fts WHERE tid=$1 AND fts MATCH $2;", entry_tid, app.Session.fts_search_terms)

	var note sql.NullString
	err := row.Scan(&note)
	if err != nil {
		app.Organizer.ShowMessage(BL, "Error in SELECT highlight(fts ...:%v", err)
		return ""
	}
	return note.String
}

// not currently in use but more general than generateWWString
// has a length parameter and takes a ret param
func generateWWString_(text string, width int, length int, ret string) string {

	if text == "" {
		return ""
	}

	if length <= 0 {
		length = maxInt
	}

	ss := strings.Split(text, "\n")
	var ab strings.Builder

	y := 0
	filerow := 0

	for _, s := range ss {
		if filerow == len(ss) {
			return ab.String()
		}

		if s == "" {
			if y == length-1 {
				return ab.String()
			}
			ab.WriteString(ret)
			filerow++
			y++
			continue
		}

		pos := 0
		prev_pos := 0

		for {
			if prev_pos+width > len(s)-1 {
				ab.WriteString(s[prev_pos:])
				if y == length-1 {
					return ab.String()
				}
				ab.WriteString(ret)
				y++
				filerow++
				break
			}
			pos = strings.LastIndex(s[:prev_pos+width], " ")
			if pos == -1 || pos == prev_pos-1 {
				pos = prev_pos + width - 1
			}

			ab.WriteString(s[prev_pos : pos+1])

			if y == length-1 {
				return ab.String()
			}
			ab.WriteString(ret)
			y++
			prev_pos = pos + 1
		}
	}
	return ab.String()
}

func (db *Database) copyEntry(sourceID int) (int, error) {
	// Get source entry info
	entry := db.getEntryInfo(sourceID)
	if entry.id == 0 {
		return -1, fmt.Errorf("source entry not found")
	}

	// Get the note content
	note := db.readNoteIntoString(sourceID)

	// Insert new entry with copied data
	newTitle := "Copy of " + entry.title
	var newID int
	err := db.MainDB.QueryRow(
		`INSERT INTO task (title, folder_tid, context_tid, star, note, added)
		 VALUES (?, ?, ?, ?, ?, datetime('now')) RETURNING id;`,
		newTitle, entry.folder_tid, entry.context_tid, entry.star, note).Scan(&newID)
	if err != nil {
		return -1, err
	}

	return newID, nil
}

func (db *Database) updateCodeFile(id int, text string) {
	var filePath string
	lang := Languages[db.taskContext(id)]
	if lang == "cpp" {
		filePath = "/home/slzatz/clangd_examples/test.cpp"
	} else if lang == "go" {
		filePath = "/home/slzatz/vmgo_go_code/main.go"
	} else if lang == "python" {
		filePath = "/home/slzatz/python_fragments/main.py"
	} else {
		app.Organizer.ShowMessage(BL, "I don't recognize %q", db.taskContext(id))
		return
	}

	f, err := os.OpenFile(filePath, os.O_RDWR|os.O_CREATE, 0755)
	if err != nil {
		app.Organizer.ShowMessage(BL, "error opening file %s: %w", filePath, err)
		return
	}
	defer f.Close()

	f.Truncate(0)
	f.WriteString(text)
	f.Sync()
}