flancer/internal/app/db.go

package app

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"net/url"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

type User struct {
	ID          string `json:"id"`
	Username    string `json:"username"`
	DisplayName string `json:"display_name"`
	Role        string `json:"role"`
	Active      bool   `json:"active"`
	CreatedAtMS int64  `json:"created_at_ms"`
}

type Settings struct {
	Language        string `json:"language"`
	TimeFormat      string `json:"time_format"`
	RoundingMinutes int    `json:"rounding_minutes"`
	RoundUp         bool   `json:"round_up"`
	ShowWeekTotal   bool   `json:"show_week_total"`
	StickyDays      bool   `json:"sticky_days"`
	LongRunReminder bool   `json:"long_run_reminder"`
	MobileCompact   bool   `json:"mobile_compact"`
	ExportName      string `json:"export_name"`
	Timezone        string `json:"timezone"`
	ExportDate      bool   `json:"export_date"`
}

type Entry struct {
	ID       string `json:"id"`
	Client   string `json:"client"`
	Activity string `json:"activity"`
	StartMS  int64  `json:"start_ms"`
	EndMS    *int64 `json:"end_ms"`
	Created  int64  `json:"created_at_ms"`
	Updated  int64  `json:"updated_at_ms"`
}

type store struct{ db *sql.DB }

func openStore(path string) (*store, error) {
	// modernc.org/sqlite supports validated DSN shorthands for common PRAGMAs.
	u := &url.URL{Scheme: "file", Path: path}
	q := u.Query()
	q.Set("_fk", "1")
	q.Set("_journal", "WAL")
	q.Set("_timeout", "5000")
	q.Set("_sync", "NORMAL")
	q.Set("_dqs", "false")
	u.RawQuery = q.Encode()
	dsn := u.String()
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	// SQLite has a single writer. A small pool avoids accidental writer stampedes while
	// still allowing concurrent reads in WAL mode.
	db.SetMaxOpenConns(8)
	db.SetMaxIdleConns(4)
	db.SetConnMaxLifetime(0)

	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		db.Close()
		return nil, err
	}

	s := &store{db: db}
	if err := s.migrate(ctx); err != nil {
		db.Close()
		return nil, err
	}
	return s, nil
}

func (s *store) migrate(ctx context.Context) error {
	const schema = `
CREATE TABLE IF NOT EXISTS app_state (
    id INTEGER PRIMARY KEY CHECK(id = 1),
    setup_complete INTEGER NOT NULL DEFAULT 0 CHECK(setup_complete IN (0,1))
);
INSERT OR IGNORE INTO app_state(id, setup_complete) VALUES(1, 0);

CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE COLLATE NOCASE,
    display_name TEXT NOT NULL DEFAULT '',
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('admin','user')),
    active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
    created_at_ms INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS sessions (
    token_hash BLOB PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    csrf_token TEXT NOT NULL,
    created_at_ms INTEGER NOT NULL,
    expires_at_ms INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expiry ON sessions(expires_at_ms);

CREATE TABLE IF NOT EXISTS entries (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    client TEXT NOT NULL DEFAULT '',
    activity TEXT NOT NULL DEFAULT '',
    start_ms INTEGER NOT NULL,
    end_ms INTEGER,
    created_at_ms INTEGER NOT NULL,
    updated_at_ms INTEGER NOT NULL,
    CHECK(end_ms IS NULL OR end_ms >= start_ms)
);
CREATE INDEX IF NOT EXISTS idx_entries_user_start ON entries(user_id, start_ms DESC);
CREATE INDEX IF NOT EXISTS idx_entries_user_client ON entries(user_id, client COLLATE NOCASE);
CREATE UNIQUE INDEX IF NOT EXISTS idx_one_running_entry_per_user ON entries(user_id) WHERE end_ms IS NULL;

UPDATE app_state SET setup_complete=1 WHERE id=1 AND EXISTS(SELECT 1 FROM users);

CREATE TABLE IF NOT EXISTS user_settings (
    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    language TEXT NOT NULL DEFAULT 'de' CHECK(language IN ('de','en')),
    time_format TEXT NOT NULL DEFAULT '24' CHECK(time_format IN ('24','12')),
    rounding_minutes INTEGER NOT NULL DEFAULT 1 CHECK(rounding_minutes BETWEEN 1 AND 60),
    round_up INTEGER NOT NULL DEFAULT 0 CHECK(round_up IN (0,1)),
    show_week_total INTEGER NOT NULL DEFAULT 1 CHECK(show_week_total IN (0,1)),
    sticky_days INTEGER NOT NULL DEFAULT 1 CHECK(sticky_days IN (0,1)),
    long_run_reminder INTEGER NOT NULL DEFAULT 1 CHECK(long_run_reminder IN (0,1)),
    mobile_compact INTEGER NOT NULL DEFAULT 1 CHECK(mobile_compact IN (0,1)),
    export_name TEXT NOT NULL DEFAULT '',
    timezone TEXT NOT NULL DEFAULT 'UTC',
    export_date INTEGER NOT NULL DEFAULT 1 CHECK(export_date IN (0,1))
);
`
	if _, err := s.db.ExecContext(ctx, schema); err != nil {
		return err
	}
	return s.ensureMobileCompactColumn(ctx)
}

// ensureMobileCompactColumn upgrades databases created before the mobile compact
// view existed. SQLite has no ADD COLUMN IF NOT EXISTS, so inspect the table first.
func (s *store) ensureMobileCompactColumn(ctx context.Context) error {
	rows, err := s.db.QueryContext(ctx, `PRAGMA table_info(user_settings)`)
	if err != nil {
		return err
	}
	found := false
	for rows.Next() {
		var cid, notNull, pk int
		var name, typ string
		var defaultValue sql.NullString
		if err := rows.Scan(&cid, &name, &typ, &notNull, &defaultValue, &pk); err != nil {
			rows.Close()
			return err
		}
		if name == "mobile_compact" {
			found = true
		}
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return err
	}
	if err := rows.Close(); err != nil {
		return err
	}
	if found {
		return nil
	}
	_, err = s.db.ExecContext(ctx, `ALTER TABLE user_settings ADD COLUMN mobile_compact INTEGER NOT NULL DEFAULT 1 CHECK(mobile_compact IN (0,1))`)
	return err
}

var errAlreadySetup = errors.New("instance already set up")

func (s *store) needsSetup(ctx context.Context) (bool, error) {
	var complete int
	err := s.db.QueryRowContext(ctx, `SELECT setup_complete FROM app_state WHERE id=1`).Scan(&complete)
	return complete == 0, err
}

func (s *store) bootstrapAdmin(ctx context.Context, username, display, passwordHash string) (User, error) {
	username = strings.TrimSpace(username)
	display = strings.TrimSpace(display)
	if display == "" {
		display = username
	}
	now := time.Now().UnixMilli()
	u := User{ID: newID(), Username: username, DisplayName: display, Role: "admin", Active: true, CreatedAtMS: now}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return User{}, err
	}
	defer tx.Rollback()
	res, err := tx.ExecContext(ctx, `UPDATE app_state SET setup_complete=1 WHERE id=1 AND setup_complete=0`)
	if err != nil {
		return User{}, err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return User{}, err
	}
	if n != 1 {
		return User{}, errAlreadySetup
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO users(id,username,display_name,password_hash,role,active,created_at_ms) VALUES(?,?,?,?,'admin',1,?)`, u.ID, u.Username, u.DisplayName, passwordHash, now); err != nil {
		return User{}, err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO user_settings(user_id,export_name) VALUES(?,?)`, u.ID, u.DisplayName); err != nil {
		return User{}, err
	}
	if err := tx.Commit(); err != nil {
		return User{}, err
	}
	return u, nil
}

func (s *store) createUser(ctx context.Context, username, display, passwordHash, role string) (User, error) {
	username = strings.TrimSpace(username)
	display = strings.TrimSpace(display)
	if display == "" {
		display = username
	}
	if role != "admin" {
		role = "user"
	}
	now := time.Now().UnixMilli()
	u := User{ID: newID(), Username: username, DisplayName: display, Role: role, Active: true, CreatedAtMS: now}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return User{}, err
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx, `INSERT INTO users(id,username,display_name,password_hash,role,active,created_at_ms) VALUES(?,?,?,?,?,1,?)`, u.ID, u.Username, u.DisplayName, passwordHash, u.Role, now); err != nil {
		return User{}, err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO user_settings(user_id,export_name) VALUES(?,?)`, u.ID, u.DisplayName); err != nil {
		return User{}, err
	}
	if err := tx.Commit(); err != nil {
		return User{}, err
	}
	return u, nil
}

func (s *store) userForLogin(ctx context.Context, username string) (User, string, error) {
	var u User
	var hash string
	var active int
	err := s.db.QueryRowContext(ctx, `SELECT id,username,display_name,password_hash,role,active,created_at_ms FROM users WHERE username=?`, strings.TrimSpace(username)).Scan(&u.ID, &u.Username, &u.DisplayName, &hash, &u.Role, &active, &u.CreatedAtMS)
	u.Active = active == 1
	return u, hash, err
}

func (s *store) userByID(ctx context.Context, id string) (User, error) {
	var u User
	var active int
	err := s.db.QueryRowContext(ctx, `SELECT id,username,display_name,role,active,created_at_ms FROM users WHERE id=?`, id).Scan(&u.ID, &u.Username, &u.DisplayName, &u.Role, &active, &u.CreatedAtMS)
	u.Active = active == 1
	return u, err
}

func (s *store) listUsers(ctx context.Context) ([]User, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id,username,display_name,role,active,created_at_ms FROM users ORDER BY username COLLATE NOCASE`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []User
	for rows.Next() {
		var u User
		var active int
		if err := rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.Role, &active, &u.CreatedAtMS); err != nil {
			return nil, err
		}
		u.Active = active == 1
		out = append(out, u)
	}
	return out, rows.Err()
}

func (s *store) setUserActive(ctx context.Context, id string, active bool) error {
	v := 0
	if active {
		v = 1
	}
	res, err := s.db.ExecContext(ctx, `UPDATE users SET active=? WHERE id=?`, v, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	if !active {
		_, _ = s.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id=?`, id)
	}
	return nil
}

func (s *store) resetPassword(ctx context.Context, id, hash string) error {
	res, err := s.db.ExecContext(ctx, `UPDATE users SET password_hash=? WHERE id=?`, hash, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	_, _ = s.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id=?`, id)
	return nil
}

func (s *store) settings(ctx context.Context, userID string) (Settings, error) {
	var x Settings
	var up, week, sticky, reminder, mobileCompact, exportDate int
	err := s.db.QueryRowContext(ctx, `SELECT language,time_format,rounding_minutes,round_up,show_week_total,sticky_days,long_run_reminder,mobile_compact,export_name,timezone,export_date FROM user_settings WHERE user_id=?`, userID).Scan(
		&x.Language, &x.TimeFormat, &x.RoundingMinutes, &up, &week, &sticky, &reminder, &mobileCompact, &x.ExportName, &x.Timezone, &exportDate,
	)
	x.RoundUp = up == 1
	x.ShowWeekTotal = week == 1
	x.StickyDays = sticky == 1
	x.LongRunReminder = reminder == 1
	x.MobileCompact = mobileCompact == 1
	x.ExportDate = exportDate == 1
	return x, err
}

func (s *store) updateSettings(ctx context.Context, userID string, x Settings) error {
	boolInt := func(v bool) int {
		if v {
			return 1
		}
		return 0
	}
	_, err := s.db.ExecContext(ctx, `UPDATE user_settings SET language=?,time_format=?,rounding_minutes=?,round_up=?,show_week_total=?,sticky_days=?,long_run_reminder=?,mobile_compact=?,export_name=?,timezone=?,export_date=? WHERE user_id=?`,
		x.Language, x.TimeFormat, x.RoundingMinutes, boolInt(x.RoundUp), boolInt(x.ShowWeekTotal), boolInt(x.StickyDays), boolInt(x.LongRunReminder), boolInt(x.MobileCompact), strings.TrimSpace(x.ExportName), x.Timezone, boolInt(x.ExportDate), userID)
	return err
}

func (s *store) runningEntry(ctx context.Context, userID string) (*Entry, error) {
	var e Entry
	err := s.db.QueryRowContext(ctx, `SELECT id,client,activity,start_ms,end_ms,created_at_ms,updated_at_ms FROM entries WHERE user_id=? AND end_ms IS NULL LIMIT 1`, userID).Scan(&e.ID, &e.Client, &e.Activity, &e.StartMS, &e.EndMS, &e.Created, &e.Updated)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	return &e, err
}

func (s *store) startEntry(ctx context.Context, userID, client, activity string, startMS int64) (Entry, error) {
	now := time.Now().UnixMilli()
	if startMS == 0 {
		startMS = now
	}
	e := Entry{ID: newID(), Client: strings.TrimSpace(client), Activity: strings.TrimSpace(activity), StartMS: startMS, Created: now, Updated: now}
	_, err := s.db.ExecContext(ctx, `INSERT INTO entries(id,user_id,client,activity,start_ms,end_ms,created_at_ms,updated_at_ms) VALUES(?,?,?,?,?,NULL,?,?)`, e.ID, userID, e.Client, e.Activity, e.StartMS, now, now)
	return e, err
}

func (s *store) stopEntry(ctx context.Context, userID, id string, endMS int64) (Entry, error) {
	if endMS == 0 {
		endMS = time.Now().UnixMilli()
	}
	now := time.Now().UnixMilli()
	res, err := s.db.ExecContext(ctx, `UPDATE entries SET end_ms=?,updated_at_ms=? WHERE id=? AND user_id=? AND end_ms IS NULL AND start_ms<=?`, endMS, now, id, userID, endMS)
	if err != nil {
		return Entry{}, err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return Entry{}, sql.ErrNoRows
	}
	return s.entryByID(ctx, userID, id)
}

func (s *store) entryByID(ctx context.Context, userID, id string) (Entry, error) {
	var e Entry
	err := s.db.QueryRowContext(ctx, `SELECT id,client,activity,start_ms,end_ms,created_at_ms,updated_at_ms FROM entries WHERE id=? AND user_id=?`, id, userID).Scan(&e.ID, &e.Client, &e.Activity, &e.StartMS, &e.EndMS, &e.Created, &e.Updated)
	return e, err
}

func (s *store) updateEntry(ctx context.Context, userID, id, client, activity string, startMS int64, endMS *int64) (Entry, error) {
	if startMS <= 0 || (endMS != nil && *endMS < startMS) {
		return Entry{}, fmt.Errorf("invalid time range")
	}
	now := time.Now().UnixMilli()
	res, err := s.db.ExecContext(ctx, `UPDATE entries SET client=?,activity=?,start_ms=?,end_ms=?,updated_at_ms=? WHERE id=? AND user_id=?`, strings.TrimSpace(client), strings.TrimSpace(activity), startMS, endMS, now, id, userID)
	if err != nil {
		return Entry{}, err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return Entry{}, sql.ErrNoRows
	}
	return s.entryByID(ctx, userID, id)
}

func (s *store) deleteEntry(ctx context.Context, userID, id string) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM entries WHERE id=? AND user_id=?`, id, userID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

type entryFilter struct {
	Query   string
	Client  string
	FromMS  int64
	ToMS    int64
	Limit   int
	Offset  int
	SortAsc bool
	Compact bool
}

type entryPage struct {
	Entries         []Entry `json:"entries"`
	TotalCount      int     `json:"total_count"`
	TotalDurationMS int64   `json:"total_duration_ms"`
}

func (s *store) listEntries(ctx context.Context, userID string, f entryFilter, cfg Settings) (entryPage, error) {
	where, args := buildEntryWhere(userID, f)
	var total int
	if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM entries `+where+` AND end_ms IS NOT NULL`, args...).Scan(&total); err != nil {
		return entryPage{}, err
	}

	// Totals are calculated row-wise to keep the rounding semantics identical to exports.
	rowsDur, err := s.db.QueryContext(ctx, `SELECT start_ms,end_ms FROM entries `+where+` AND end_ms IS NOT NULL`, args...)
	if err != nil {
		return entryPage{}, err
	}
	var totalDur int64
	for rowsDur.Next() {
		var start, end int64
		if err := rowsDur.Scan(&start, &end); err != nil {
			rowsDur.Close()
			return entryPage{}, err
		}
		totalDur += roundedDuration(end-start, cfg.RoundingMinutes, cfg.RoundUp)
	}
	rowsDur.Close()
	if err := rowsDur.Err(); err != nil {
		return entryPage{}, err
	}

	qargs := append(append([]any{}, args...), f.Limit, f.Offset)
	rows, err := s.db.QueryContext(ctx, `SELECT id,client,activity,start_ms,end_ms,created_at_ms,updated_at_ms FROM entries `+where+` AND end_ms IS NOT NULL ORDER BY start_ms DESC,id DESC LIMIT ? OFFSET ?`, qargs...)
	if err != nil {
		return entryPage{}, err
	}
	defer rows.Close()
	out := entryPage{TotalCount: total, TotalDurationMS: totalDur, Entries: []Entry{}}
	for rows.Next() {
		var e Entry
		if err := rows.Scan(&e.ID, &e.Client, &e.Activity, &e.StartMS, &e.EndMS, &e.Created, &e.Updated); err != nil {
			return entryPage{}, err
		}
		out.Entries = append(out.Entries, e)
	}
	return out, rows.Err()
}

func (s *store) allEntries(ctx context.Context, userID string, f entryFilter) ([]Entry, error) {
	where, args := buildEntryWhere(userID, f)
	order := "DESC"
	if f.SortAsc {
		order = "ASC"
	}
	rows, err := s.db.QueryContext(ctx, `SELECT id,client,activity,start_ms,end_ms,created_at_ms,updated_at_ms FROM entries `+where+` AND end_ms IS NOT NULL ORDER BY start_ms `+order+`,id `+order, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Entry
	for rows.Next() {
		var e Entry
		if err := rows.Scan(&e.ID, &e.Client, &e.Activity, &e.StartMS, &e.EndMS, &e.Created, &e.Updated); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

func buildEntryWhere(userID string, f entryFilter) (string, []any) {
	where := `WHERE user_id=?`
	args := []any{userID}
	if f.FromMS > 0 {
		where += ` AND start_ms>=?`
		args = append(args, f.FromMS)
	}
	if f.ToMS > 0 {
		where += ` AND start_ms<?`
		args = append(args, f.ToMS)
	}
	if client := strings.TrimSpace(f.Client); client != "" {
		where += ` AND client = ? COLLATE NOCASE`
		args = append(args, client)
	}
	if q := strings.TrimSpace(f.Query); q != "" {
		where += ` AND (client LIKE ? ESCAPE '\' COLLATE NOCASE OR activity LIKE ? ESCAPE '\' COLLATE NOCASE)`
		q = "%" + escapeLike(q) + "%"
		args = append(args, q, q)
	}
	return where, args
}

func escapeLike(s string) string {
	s = strings.ReplaceAll(s, `\`, `\\`)
	s = strings.ReplaceAll(s, `%`, `\%`)
	s = strings.ReplaceAll(s, `_`, `\_`)
	return s
}

func (s *store) recentClients(ctx context.Context, userID string) ([]string, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT client FROM entries WHERE user_id=? AND TRIM(client)<>'' GROUP BY client COLLATE NOCASE ORDER BY MAX(start_ms) DESC LIMIT 50`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var x string
		if err := rows.Scan(&x); err != nil {
			return nil, err
		}
		out = append(out, x)
	}
	return out, rows.Err()
}

func roundedDuration(ms int64, minutes int, up bool) int64 {
	if ms <= 0 {
		return 0
	}
	if minutes < 1 {
		minutes = 1
	}
	interval := int64(minutes) * 60_000
	if interval <= 60_000 {
		return ms
	}
	if up {
		return ((ms + interval - 1) / interval) * interval
	}
	return ((ms + interval/2) / interval) * interval
}

func (s *store) createFinishedEntry(ctx context.Context, userID, client, activity string, startMS, endMS int64) (Entry, error) {
	if startMS <= 0 || endMS < startMS {
		return Entry{}, fmt.Errorf("invalid time range")
	}
	now := time.Now().UnixMilli()
	e := Entry{ID: newID(), Client: strings.TrimSpace(client), Activity: strings.TrimSpace(activity), StartMS: startMS, EndMS: &endMS, Created: now, Updated: now}
	_, err := s.db.ExecContext(ctx, `INSERT INTO entries(id,user_id,client,activity,start_ms,end_ms,created_at_ms,updated_at_ms) VALUES(?,?,?,?,?,?,?,?)`, e.ID, userID, e.Client, e.Activity, startMS, endMS, now, now)
	return e, err
}