// Package todo keeps every visitor's todo list in one SQLite file.
//
// A list is identified by a ListKey: the SHA-256 of the visitor's cookie.
// Every query that reads or changes a todo names the list, so an id that
// belongs to someone else behaves exactly like an id that doesn't exist.
package todo

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"errors"
	"fmt"
	"net/url"
	"runtime"
	"strings"

	_ "modernc.org/sqlite" // registers the "sqlite" driver; pure Go, no cgo
)

// ErrNotFound means the id isn't in the caller's list.
var ErrNotFound = errors.New("todo: not found")

// ListKey names one visitor's list.
type ListKey [sha256.Size]byte

// KeyFor derives the list key from the visitor's secret cookie bytes. The
// database only ever sees the hash, never the cookie.
func KeyFor(secret []byte) ListKey { return sha256.Sum256(secret) }

// Todo is one item in a list.
type Todo struct {
	ID        int64
	Title     string
	Completed bool
}

// migrations only ever grow. Each entry runs once, in order, recorded in
// todo_migrations. Never edit or remove one that has shipped; append a
// new one. Names are prefixed so they can't collide with tables an
// earlier build may have left in the same file.
var migrations = []string{
	`CREATE TABLE todo_items (
		id        INTEGER PRIMARY KEY AUTOINCREMENT,
		list      BLOB    NOT NULL,
		title     TEXT    NOT NULL,
		completed INTEGER NOT NULL DEFAULT 0,
		created   INTEGER NOT NULL DEFAULT (unixepoch())
	);
	CREATE INDEX todo_items_by_list ON todo_items (list, id);`,
	// Lists the server has issued. A cookie whose hash isn't here is
	// replaced, so nobody can pick their own list identity. Lists that
	// already hold todos were issued by an earlier build, so they count.
	`CREATE TABLE todo_lists (
		key     BLOB    PRIMARY KEY,
		created INTEGER NOT NULL DEFAULT (unixepoch())
	) WITHOUT ROWID;
	INSERT INTO todo_lists (key) SELECT DISTINCT list FROM todo_items;`,
}

// Store is the todo database: one connection for writes, a small pool
// for reads. SQLite allows a single writer at a time, so funnelling
// writes through one connection turns lock contention into a queue.
type Store struct {
	w, r *sql.DB
}

// Open opens (creating if needed) the database at path in WAL mode and
// brings its schema up to date.
func Open(ctx context.Context, path string) (*Store, error) {
	// Every connection the pools open runs these pragmas first.
	dsn := func(pragmas ...string) string {
		q := url.Values{}
		for _, p := range append([]string{"busy_timeout(5000)", "synchronous(NORMAL)"}, pragmas...) {
			q.Add("_pragma", p)
		}
		return "file:" + path + "?" + q.Encode()
	}
	w, err := sql.Open("sqlite", dsn("journal_mode(WAL)")+"&_txlock=immediate")
	if err != nil {
		return nil, err
	}
	w.SetMaxOpenConns(1)
	r, err := sql.Open("sqlite", dsn("query_only(1)"))
	if err != nil {
		w.Close()
		return nil, err
	}
	r.SetMaxOpenConns(max(4, runtime.NumCPU()))
	s := &Store{w: w, r: r}
	if err := s.migrate(ctx); err != nil {
		s.Close()
		return nil, err
	}
	return s, nil
}

func (s *Store) migrate(ctx context.Context) error {
	tx, err := s.w.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS todo_migrations (
		version INTEGER PRIMARY KEY,
		applied INTEGER NOT NULL DEFAULT (unixepoch())
	)`); err != nil {
		return err
	}
	var have int
	if err := tx.QueryRowContext(ctx, `SELECT COALESCE(MAX(version), 0) FROM todo_migrations`).Scan(&have); err != nil {
		return err
	}
	if have > len(migrations) {
		return fmt.Errorf("todo: database is at schema %d, this build knows %d", have, len(migrations))
	}
	for i := have; i < len(migrations); i++ {
		if _, err := tx.ExecContext(ctx, migrations[i]); err != nil {
			return fmt.Errorf("todo: migration %d: %w", i+1, err)
		}
		if _, err := tx.ExecContext(ctx, `INSERT INTO todo_migrations (version) VALUES (?)`, i+1); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// Close checkpoints and closes the database.
func (s *Store) Close() error {
	return errors.Join(s.r.Close(), s.w.Close())
}

// Ping checks that the database answers.
func (s *Store) Ping(ctx context.Context) error { return s.r.PingContext(ctx) }

// CreateList records a newly issued list.
func (s *Store) CreateList(ctx context.Context, k ListKey) error {
	_, err := s.w.ExecContext(ctx, `INSERT INTO todo_lists (key) VALUES (?)`, k[:])
	return err
}

// ListExists reports whether the server issued this list.
func (s *Store) ListExists(ctx context.Context, k ListKey) (bool, error) {
	var n int
	err := s.r.QueryRowContext(ctx, `SELECT count(*) FROM todo_lists WHERE key = ?`, k[:]).Scan(&n)
	return n > 0, err
}

// List returns every todo in the list, oldest first.
func (s *Store) List(ctx context.Context, k ListKey) ([]Todo, error) {
	rows, err := s.r.QueryContext(ctx,
		`SELECT id, title, completed FROM todo_items WHERE list = ? ORDER BY id`, k[:])
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Todo
	for rows.Next() {
		var t Todo
		if err := rows.Scan(&t.ID, &t.Title, &t.Completed); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// CleanTitle trims a submitted title and replaces any invalid UTF-8.
// An empty result means "no title".
func CleanTitle(title string) string {
	return strings.TrimSpace(strings.ToValidUTF8(title, "�"))
}

// Add appends a todo. A title that is empty after cleaning adds nothing.
func (s *Store) Add(ctx context.Context, k ListKey, title string) error {
	if title = CleanTitle(title); title == "" {
		return nil
	}
	_, err := s.w.ExecContext(ctx, `INSERT INTO todo_items (list, title) VALUES (?, ?)`, k[:], title)
	return err
}

// Toggle flips one todo between active and completed.
func (s *Store) Toggle(ctx context.Context, k ListKey, id int64) error {
	return one(s.w.ExecContext(ctx,
		`UPDATE todo_items SET completed = 1 - completed WHERE id = ? AND list = ?`, id, k[:]))
}

// Rename sets a todo's title. A title that is empty after cleaning
// deletes the todo instead, as TodoMVC does.
func (s *Store) Rename(ctx context.Context, k ListKey, id int64, title string) error {
	if title = CleanTitle(title); title == "" {
		return s.Delete(ctx, k, id)
	}
	return one(s.w.ExecContext(ctx,
		`UPDATE todo_items SET title = ? WHERE id = ? AND list = ?`, title, id, k[:]))
}

// Delete removes one todo.
func (s *Store) Delete(ctx context.Context, k ListKey, id int64) error {
	return one(s.w.ExecContext(ctx, `DELETE FROM todo_items WHERE id = ? AND list = ?`, id, k[:]))
}

// ToggleAll marks every todo completed, or, if every one already is,
// marks them all active.
func (s *Store) ToggleAll(ctx context.Context, k ListKey) error {
	_, err := s.w.ExecContext(ctx,
		`UPDATE todo_items
		    SET completed = EXISTS (SELECT 1 FROM todo_items WHERE list = ?1 AND completed = 0)
		  WHERE list = ?1`, k[:])
	return err
}

// ClearCompleted deletes every completed todo.
func (s *Store) ClearCompleted(ctx context.Context, k ListKey) error {
	_, err := s.w.ExecContext(ctx, `DELETE FROM todo_items WHERE list = ? AND completed = 1`, k[:])
	return err
}

// one turns "no row matched" into ErrNotFound.
func one(res sql.Result, err error) error {
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}