vibedns/internal/database/store_admin.go

package database

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

	"github.com/owen/vibedns/internal/models"
)

// Admin returns the administrator account, or ErrNotFound before first setup.
func (db *DB) Admin(ctx context.Context) (models.Admin, error) {
	var a models.Admin
	var created, updated int64
	var lastLogin sql.NullInt64
	var mustChange int

	err := db.QueryRowContext(ctx, `
		SELECT username, password_hash, must_change_password, created_at, updated_at, last_login_at
		FROM admin_user WHERE id = 1`).
		Scan(&a.Username, &a.PasswordHash, &mustChange, &created, &updated, &lastLogin)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return a, ErrNotFound
	case err != nil:
		return a, fmt.Errorf("load administrator: %w", err)
	}
	a.MustChangePassword = mustChange != 0
	a.CreatedAt = time.Unix(created, 0)
	a.UpdatedAt = time.Unix(updated, 0)
	a.LastLoginAt = unixPtr(lastLogin)
	return a, nil
}

// CreateAdmin inserts the single administrator row. It fails if one exists.
func (db *DB) CreateAdmin(ctx context.Context, username, passwordHash string, mustChange bool) error {
	_, err := db.ExecContext(ctx, `
		INSERT INTO admin_user (id, username, password_hash, must_change_password)
		VALUES (1, ?, ?, ?)`, username, passwordHash, boolInt(mustChange))
	if err != nil {
		if isUniqueViolation(err) {
			return ErrConflict
		}
		return fmt.Errorf("create administrator: %w", err)
	}
	return nil
}

// UpdateAdminCredentials replaces the username and/or password hash.
func (db *DB) UpdateAdminCredentials(ctx context.Context, username, passwordHash string, mustChange bool) error {
	res, err := db.ExecContext(ctx, `
		UPDATE admin_user
		SET username = ?, password_hash = ?, must_change_password = ?, updated_at = unixepoch()
		WHERE id = 1`, username, passwordHash, boolInt(mustChange))
	if err != nil {
		return fmt.Errorf("update administrator: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

// TouchAdminLogin records a successful authentication.
func (db *DB) TouchAdminLogin(ctx context.Context) error {
	_, err := db.ExecContext(ctx, `UPDATE admin_user SET last_login_at = unixepoch() WHERE id = 1`)
	return err
}

// --- API tokens ---------------------------------------------------------

// CreateAPIToken stores a new token. Only the prefix and hash are persisted.
func (db *DB) CreateAPIToken(ctx context.Context, name, description, prefix, hash string) (models.APIToken, error) {
	res, err := db.ExecContext(ctx, `
		INSERT INTO api_tokens (name, description, token_prefix, token_hash)
		VALUES (?, ?, ?, ?)`, name, description, prefix, hash)
	if err != nil {
		if isUniqueViolation(err) {
			return models.APIToken{}, ErrConflict
		}
		return models.APIToken{}, fmt.Errorf("create API token: %w", err)
	}
	id, _ := res.LastInsertId()
	return db.APIToken(ctx, id)
}

// APIToken loads one token by ID.
func (db *DB) APIToken(ctx context.Context, id int64) (models.APIToken, error) {
	rows, err := db.queryTokens(ctx, `WHERE id = ?`, id)
	if err != nil {
		return models.APIToken{}, err
	}
	if len(rows) == 0 {
		return models.APIToken{}, ErrNotFound
	}
	return rows[0], nil
}

// APITokens lists all tokens, newest first.
func (db *DB) APITokens(ctx context.Context) ([]models.APIToken, error) {
	return db.queryTokens(ctx, `ORDER BY created_at DESC, id DESC`)
}

func (db *DB) queryTokens(ctx context.Context, where string, args ...any) ([]models.APIToken, error) {
	q := `SELECT id, name, description, token_prefix, enabled, created_at, last_used_at FROM api_tokens ` + where
	rows, err := db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("list API tokens: %w", err)
	}
	defer rows.Close()

	var out []models.APIToken
	for rows.Next() {
		var t models.APIToken
		var enabled int
		var created int64
		var lastUsed sql.NullInt64
		if err := rows.Scan(&t.ID, &t.Name, &t.Description, &t.Prefix, &enabled, &created, &lastUsed); err != nil {
			return nil, err
		}
		t.Enabled = enabled != 0
		t.CreatedAt = time.Unix(created, 0)
		t.LastUsedAt = unixPtr(lastUsed)
		out = append(out, t)
	}
	return out, rows.Err()
}

// APITokenCandidate is a stored token hash keyed by its lookup prefix.
type APITokenCandidate struct {
	ID   int64
	Name string
	Hash string
}

// APITokensByPrefix returns enabled tokens whose prefix matches. The prefix
// narrows the search; the caller still verifies the hash in constant time.
func (db *DB) APITokensByPrefix(ctx context.Context, prefix string) ([]APITokenCandidate, error) {
	rows, err := db.QueryContext(ctx,
		`SELECT id, name, token_hash FROM api_tokens WHERE token_prefix = ? AND enabled = 1`, prefix)
	if err != nil {
		return nil, fmt.Errorf("lookup API token: %w", err)
	}
	defer rows.Close()

	var out []APITokenCandidate
	for rows.Next() {
		var c APITokenCandidate
		if err := rows.Scan(&c.ID, &c.Name, &c.Hash); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// TouchAPIToken records that a token was just used. Errors are non-fatal to the
// request path, so callers may ignore them.
func (db *DB) TouchAPIToken(ctx context.Context, id int64) error {
	_, err := db.ExecContext(ctx, `UPDATE api_tokens SET last_used_at = unixepoch() WHERE id = ?`, id)
	return err
}

// SetAPITokenEnabled enables or disables a token without deleting it.
func (db *DB) SetAPITokenEnabled(ctx context.Context, id int64, enabled bool) error {
	res, err := db.ExecContext(ctx, `UPDATE api_tokens SET enabled = ? WHERE id = ?`, boolInt(enabled), id)
	if err != nil {
		return fmt.Errorf("update API token: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

// DeleteAPIToken permanently revokes a token.
func (db *DB) DeleteAPIToken(ctx context.Context, id int64) error {
	res, err := db.ExecContext(ctx, `DELETE FROM api_tokens WHERE id = ?`, id)
	if err != nil {
		return fmt.Errorf("delete API token: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

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

// isUniqueViolation detects SQLite UNIQUE/PRIMARY KEY constraint failures
// without depending on driver-specific error types.
func isUniqueViolation(err error) bool {
	if err == nil {
		return false
	}
	msg := strings.ToLower(err.Error())
	return strings.Contains(msg, "unique constraint failed") ||
		strings.Contains(msg, "constraint failed: unique")
}