store.go — Database Layer

package main

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

	_ "github.com/lib/pq"
)

var ErrNotFound = errors.New("bookmark not found")

type BookmarkStore struct {
	db *sql.DB
}

func OpenDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("postgres", dsn)
	if err != nil {
		return nil, fmt.Errorf("open db: %w", err)
	}
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)
	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("ping db: %w", err)
	}
	return db, nil
}

func NewBookmarkStore(db *sql.DB) *BookmarkStore {
	return &BookmarkStore{db: db}
}

func (s *BookmarkStore) Init(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
		CREATE TABLE IF NOT EXISTS bookmarks (
			id         SERIAL PRIMARY KEY,
			url        TEXT NOT NULL,
			title      TEXT NOT NULL,
			created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
		)`)
	return err
}

func (s *BookmarkStore) Create(ctx context.Context, url, title string) (Bookmark, error) {
	var b Bookmark
	err := s.db.QueryRowContext(ctx,
		"INSERT INTO bookmarks (url, title) VALUES ($1, $2) RETURNING id, url, title, created_at",
		url, title,
	).Scan(&b.ID, &b.URL, &b.Title, &b.CreatedAt)
	if err != nil {
		return Bookmark{}, fmt.Errorf("create bookmark: %w", err)
	}
	return b, nil
}

func (s *BookmarkStore) GetByID(ctx context.Context, id int) (Bookmark, error) {
	var b Bookmark
	err := s.db.QueryRowContext(ctx,
		"SELECT id, url, title, created_at FROM bookmarks WHERE id = $1", id,
	).Scan(&b.ID, &b.URL, &b.Title, &b.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return Bookmark{}, ErrNotFound
	}
	if err != nil {
		return Bookmark{}, fmt.Errorf("get bookmark: %w", err)
	}
	return b, nil
}

func (s *BookmarkStore) List(ctx context.Context) ([]Bookmark, error) {
	rows, err := s.db.QueryContext(ctx,
		"SELECT id, url, title, created_at FROM bookmarks ORDER BY created_at DESC",
	)
	if err != nil {
		return nil, fmt.Errorf("list bookmarks: %w", err)
	}
	defer rows.Close()

	var bookmarks []Bookmark
	for rows.Next() {
		var b Bookmark
		if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan bookmark: %w", err)
		}
		bookmarks = append(bookmarks, b)
	}
	return bookmarks, rows.Err()
}

func (s *BookmarkStore) Delete(ctx context.Context, id int) error {
	result, err := s.db.ExecContext(ctx, "DELETE FROM bookmarks WHERE id = $1", id)
	if err != nil {
		return fmt.Errorf("delete bookmark: %w", err)
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrNotFound
	}
	return nil
}