tefter/server/store/store.go

// Package store is the SQLite access layer for tefterd.
package store

import (
	"crypto/rand"
	"crypto/sha256"
	"crypto/subtle"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

const SchemaVersion = 2

// historyKeep caps stored revisions per note; older ones are pruned on write.
const historyKeep = 50

type Note struct {
	ID         string   `json:"id"`
	Content    string   `json:"content"`
	Tags       []string `json:"tags"`
	CreatedAt  int64    `json:"created_at"`
	ModifiedAt int64    `json:"modified_at"`
	Deleted    bool     `json:"deleted"`
	Version    int64    `json:"version"`
}

// Revision is a superseded note state, recorded when an accepted push overwrites it.
type Revision struct {
	NoteID     string   `json:"note_id"`
	Version    int64    `json:"version"`
	Content    string   `json:"content"`
	Tags       []string `json:"tags"`
	ModifiedAt int64    `json:"modified_at"`
	ReplacedAt int64    `json:"replaced_at"`
}

type IncomingNote struct {
	ID          string   `json:"id"`
	Content     string   `json:"content"`
	Tags        []string `json:"tags"`
	CreatedAt   int64    `json:"created_at"`
	ModifiedAt  int64    `json:"modified_at"`
	Deleted     bool     `json:"deleted"`
	BaseVersion int64    `json:"baseVersion"`
}

type PushResult struct {
	ID             string `json:"id"`
	Status         string `json:"status"` // accepted | conflict
	Version        int64  `json:"version,omitempty"`
	ConflictCopyID string `json:"conflictCopyId,omitempty"`
	ServerNote     *Note  `json:"serverNote,omitempty"`
}

type Store struct {
	db *sql.DB
}

func Open(path string) (*Store, error) {
	// _pragma via DSN keeps every pool connection configured identically.
	dsn := fmt.Sprintf("file:%s?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(ON)", path)
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1) // single writer; personal scale
	s := &Store{db: db}
	if err := s.migrate(); err != nil {
		db.Close()
		return nil, err
	}
	return s, nil
}

func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	_, err := s.db.Exec(`
CREATE TABLE IF NOT EXISTS notes (
    id          TEXT PRIMARY KEY,
    content     TEXT NOT NULL DEFAULT '',
    tags        TEXT NOT NULL DEFAULT '[]',
    created_at  INTEGER NOT NULL,
    modified_at INTEGER NOT NULL,
    deleted     INTEGER NOT NULL DEFAULT 0,
    version     INTEGER NOT NULL,
    source_id   TEXT
);
CREATE INDEX IF NOT EXISTS idx_notes_version ON notes(version);
CREATE UNIQUE INDEX IF NOT EXISTS idx_notes_source ON notes(source_id) WHERE source_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS note_history (
    note_id     TEXT NOT NULL,
    version     INTEGER NOT NULL,
    content     TEXT NOT NULL DEFAULT '',
    tags        TEXT NOT NULL DEFAULT '[]',
    modified_at INTEGER NOT NULL,
    replaced_at INTEGER NOT NULL,
    PRIMARY KEY (note_id, version)
);
CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v TEXT);
INSERT OR IGNORE INTO meta (k, v) VALUES ('next_version', '1');
`)
	if err != nil {
		return err
	}
	return s.setMeta("schema_version", strconv.Itoa(SchemaVersion))
}

// --- meta helpers ---

func (s *Store) getMeta(k string) (string, error) {
	var v string
	err := s.db.QueryRow(`SELECT v FROM meta WHERE k = ?`, k).Scan(&v)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	return v, err
}

func (s *Store) setMeta(k, v string) error {
	_, err := s.db.Exec(`INSERT INTO meta (k, v) VALUES (?, ?) ON CONFLICT(k) DO UPDATE SET v = excluded.v`, k, v)
	return err
}

func nextVersionTx(tx *sql.Tx) (int64, error) {
	var cur string
	if err := tx.QueryRow(`SELECT v FROM meta WHERE k = 'next_version'`).Scan(&cur); err != nil {
		return 0, err
	}
	n, err := strconv.ParseInt(cur, 10, 64)
	if err != nil {
		return 0, err
	}
	if _, err := tx.Exec(`UPDATE meta SET v = ? WHERE k = 'next_version'`, strconv.FormatInt(n+1, 10)); err != nil {
		return 0, err
	}
	return n, nil
}

// --- token auth ---

// EnsureToken returns a freshly generated token if none exists yet ("" otherwise).
func (s *Store) EnsureToken() (string, error) {
	h, err := s.getMeta("token_hash")
	if err != nil {
		return "", err
	}
	if h != "" {
		return "", nil
	}
	return s.RotateToken()
}

// RotateToken generates a new bearer token, stores its SHA-256, returns the plaintext.
func (s *Store) RotateToken() (string, error) {
	raw := make([]byte, 32)
	if _, err := rand.Read(raw); err != nil {
		return "", err
	}
	tok := hex.EncodeToString(raw)
	sum := sha256.Sum256([]byte(tok))
	if err := s.setMeta("token_hash", hex.EncodeToString(sum[:])); err != nil {
		return "", err
	}
	return tok, nil
}

func (s *Store) CheckToken(tok string) bool {
	h, err := s.getMeta("token_hash")
	if err != nil || h == "" {
		return false
	}
	sum := sha256.Sum256([]byte(tok))
	return subtle.ConstantTimeCompare([]byte(hex.EncodeToString(sum[:])), []byte(h)) == 1
}

// --- notes ---

func tagsJSON(tags []string) string {
	if tags == nil {
		tags = []string{}
	}
	b, _ := json.Marshal(tags)
	return string(b)
}

func scanNote(scan func(dest ...any) error) (Note, error) {
	var n Note
	var tags string
	var deleted int
	if err := scan(&n.ID, &n.Content, &tags, &n.CreatedAt, &n.ModifiedAt, &deleted, &n.Version); err != nil {
		return n, err
	}
	n.Deleted = deleted != 0
	if err := json.Unmarshal([]byte(tags), &n.Tags); err != nil || n.Tags == nil {
		n.Tags = []string{}
	}
	return n, nil
}

// Changes returns notes with version > since, ordered by version, up to limit.
func (s *Store) Changes(since int64, limit int) (notes []Note, cursor int64, more bool, err error) {
	if limit <= 0 || limit > 500 {
		limit = 500
	}
	rows, err := s.db.Query(
		`SELECT id, content, tags, created_at, modified_at, deleted, version
		 FROM notes WHERE version > ? ORDER BY version LIMIT ?`, since, limit+1)
	if err != nil {
		return nil, 0, false, err
	}
	defer rows.Close()
	notes = []Note{}
	for rows.Next() {
		n, err := scanNote(rows.Scan)
		if err != nil {
			return nil, 0, false, err
		}
		notes = append(notes, n)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, false, err
	}
	if len(notes) > limit {
		notes = notes[:limit]
		more = true
	}
	cursor = since
	if len(notes) > 0 {
		cursor = notes[len(notes)-1].Version
	}
	return notes, cursor, more, nil
}

func getNoteTx(tx *sql.Tx, id string) (*Note, error) {
	row := tx.QueryRow(`SELECT id, content, tags, created_at, modified_at, deleted, version FROM notes WHERE id = ?`, id)
	n, err := scanNote(row.Scan)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &n, nil
}

// recordHistoryTx snapshots the note state that is about to be overwritten,
// then prunes revisions beyond historyKeep.
func recordHistoryTx(tx *sql.Tx, n *Note, now time.Time) error {
	if _, err := tx.Exec(
		`INSERT OR REPLACE INTO note_history (note_id, version, content, tags, modified_at, replaced_at) VALUES (?,?,?,?,?,?)`,
		n.ID, n.Version, n.Content, tagsJSON(n.Tags), n.ModifiedAt, now.UnixMilli()); err != nil {
		return err
	}
	_, err := tx.Exec(
		`DELETE FROM note_history WHERE note_id = ? AND version NOT IN (
		   SELECT version FROM note_history WHERE note_id = ? ORDER BY version DESC LIMIT ?)`,
		n.ID, n.ID, historyKeep)
	return err
}

// History returns a note's superseded revisions, newest first.
func (s *Store) History(noteID string) ([]Revision, error) {
	rows, err := s.db.Query(
		`SELECT note_id, version, content, tags, modified_at, replaced_at
		 FROM note_history WHERE note_id = ? ORDER BY version DESC`, noteID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	revs := []Revision{}
	for rows.Next() {
		var r Revision
		var tags string
		if err := rows.Scan(&r.NoteID, &r.Version, &r.Content, &tags, &r.ModifiedAt, &r.ReplacedAt); err != nil {
			return nil, err
		}
		if err := json.Unmarshal([]byte(tags), &r.Tags); err != nil || r.Tags == nil {
			r.Tags = []string{}
		}
		revs = append(revs, r)
	}
	return revs, rows.Err()
}

// conflictTitle appends the marker to the first non-empty line of content.
func conflictTitle(content string, at time.Time) string {
	marker := at.Format(" (conflicted copy 2006-01-02 15:04)")
	lines := strings.Split(content, "\n")
	for i, l := range lines {
		if strings.TrimSpace(l) != "" {
			lines[i] = l + marker
			return strings.Join(lines, "\n")
		}
	}
	return strings.TrimSpace(marker)
}

func newUUID() string {
	b := make([]byte, 16)
	_, _ = rand.Read(b)
	b[6] = (b[6] & 0x0f) | 0x40
	b[8] = (b[8] & 0x3f) | 0x80
	h := hex.EncodeToString(b)
	return h[0:8] + "-" + h[8:12] + "-" + h[12:16] + "-" + h[16:20] + "-" + h[20:]
}

// ApplyBatch applies pushed notes under the SPEC §4 conflict rules, in one transaction.
func (s *Store) ApplyBatch(in []IncomingNote, now time.Time) ([]PushResult, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	results := make([]PushResult, 0, len(in))
	for _, inc := range in {
		cur, err := getNoteTx(tx, inc.ID)
		if err != nil {
			return nil, err
		}

		switch {
		case cur == nil:
			// Rule 1: unknown id → insert.
			v, err := nextVersionTx(tx)
			if err != nil {
				return nil, err
			}
			if _, err := tx.Exec(
				`INSERT INTO notes (id, content, tags, created_at, modified_at, deleted, version) VALUES (?,?,?,?,?,?,?)`,
				inc.ID, inc.Content, tagsJSON(inc.Tags), inc.CreatedAt, inc.ModifiedAt, b2i(inc.Deleted), v); err != nil {
				return nil, err
			}
			results = append(results, PushResult{ID: inc.ID, Status: "accepted", Version: v})

		case inc.BaseVersion == cur.Version:
			// Rule 2: clean fast-forward.
			if err := recordHistoryTx(tx, cur, now); err != nil {
				return nil, err
			}
			v, err := nextVersionTx(tx)
			if err != nil {
				return nil, err
			}
			if _, err := tx.Exec(
				`UPDATE notes SET content=?, tags=?, modified_at=?, deleted=?, version=? WHERE id=?`,
				inc.Content, tagsJSON(inc.Tags), inc.ModifiedAt, b2i(inc.Deleted), v, inc.ID); err != nil {
				return nil, err
			}
			results = append(results, PushResult{ID: inc.ID, Status: "accepted", Version: v})

		case inc.Deleted:
			// Rule 4a: stale delete vs newer server change → edit wins, delete dropped.
			sn := *cur
			results = append(results, PushResult{ID: inc.ID, Status: "conflict", ServerNote: &sn})

		case cur.Deleted:
			// Rule 4b: stale edit vs server tombstone → edit wins, overwrites the tombstone.
			if err := recordHistoryTx(tx, cur, now); err != nil {
				return nil, err
			}
			v, err := nextVersionTx(tx)
			if err != nil {
				return nil, err
			}
			if _, err := tx.Exec(
				`UPDATE notes SET content=?, tags=?, modified_at=?, deleted=0, version=? WHERE id=?`,
				inc.Content, tagsJSON(inc.Tags), inc.ModifiedAt, v, inc.ID); err != nil {
				return nil, err
			}
			results = append(results, PushResult{ID: inc.ID, Status: "accepted", Version: v})

		default:
			// Rule 3: true edit/edit conflict → server content stays; incoming becomes a
			// conflict copy with a new id, `conflict` tag and a marker on its first line.
			copyID := newUUID()
			v, err := nextVersionTx(tx)
			if err != nil {
				return nil, err
			}
			tags := inc.Tags
			if !contains(tags, "conflict") {
				tags = append(append([]string{}, tags...), "conflict")
			}
			if _, err := tx.Exec(
				`INSERT INTO notes (id, content, tags, created_at, modified_at, deleted, version) VALUES (?,?,?,?,?,?,?)`,
				copyID, conflictTitle(inc.Content, now), tagsJSON(tags), inc.ModifiedAt, inc.ModifiedAt, 0, v); err != nil {
				return nil, err
			}
			sn := *cur
			results = append(results, PushResult{ID: inc.ID, Status: "conflict", ConflictCopyID: copyID, ServerNote: &sn})
		}
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return results, nil
}

// UpsertImported inserts an imported note keyed by source_id; returns false if it
// already exists (dedupe — SPEC §6 idempotent import).
func (s *Store) UpsertImported(sourceID string, n Note) (bool, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return false, err
	}
	defer tx.Rollback()

	var existing string
	err = tx.QueryRow(`SELECT id FROM notes WHERE source_id = ?`, sourceID).Scan(&existing)
	if err == nil {
		return false, tx.Commit() // already imported
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return false, err
	}
	v, err := nextVersionTx(tx)
	if err != nil {
		return false, err
	}
	if n.ID == "" {
		n.ID = newUUID()
	}
	if _, err := tx.Exec(
		`INSERT INTO notes (id, content, tags, created_at, modified_at, deleted, version, source_id) VALUES (?,?,?,?,?,?,?,?)`,
		n.ID, n.Content, tagsJSON(n.Tags), n.CreatedAt, n.ModifiedAt, b2i(n.Deleted), v, sourceID); err != nil {
		return false, err
	}
	return true, tx.Commit()
}

// Compact purges tombstones older than the given number of days, plus the
// history of any note that no longer exists.
func (s *Store) Compact(olderThanDays int) (int64, error) {
	cutoff := time.Now().AddDate(0, 0, -olderThanDays).UnixMilli()
	res, err := s.db.Exec(`DELETE FROM notes WHERE deleted = 1 AND modified_at < ?`, cutoff)
	if err != nil {
		return 0, err
	}
	if _, err := s.db.Exec(`DELETE FROM note_history WHERE note_id NOT IN (SELECT id FROM notes)`); err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// Backup writes a consistent snapshot via VACUUM INTO.
func (s *Store) Backup(path string) error {
	_, err := s.db.Exec(`VACUUM INTO ?`, path)
	return err
}

func (s *Store) NoteCount() (total, deleted int64, err error) {
	err = s.db.QueryRow(`SELECT COUNT(*), COALESCE(SUM(deleted), 0) FROM notes`).Scan(&total, &deleted)
	return
}

func b2i(b bool) int {
	if b {
		return 1
	}
	return 0
}

func contains(ss []string, s string) bool {
	for _, x := range ss {
		if x == s {
			return true
		}
	}
	return false
}