code:
package database

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

	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/postgres"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	"golang.org/x/crypto/bcrypt"
)

// PGDatabase is implementation of Database interface
// for connecting to PostgreSQL database.
type PGDatabase struct {
	dbbase
}

// OpenPGDatabase creates and opens connection to a PostgreSQL Database.
func OpenPGDatabase(ctx context.Context, connString string) (pgDB *PGDatabase, err error) {
	// Open database and start transaction
	db, err := sqlx.ConnectContext(ctx, "postgres", connString)
	if err != nil {
		return nil, errors.WithStack(err)
	}

	db.SetMaxOpenConns(100)
	db.SetConnMaxLifetime(time.Second)

	pgDB = &PGDatabase{dbbase: dbbase{*db}}
	return pgDB, err
}

// Migrate runs migrations for this database engine
func (db *PGDatabase) Migrate() error {
	sourceDriver, err := iofs.New(migrations, "migrations/postgres")
	if err != nil {
		return errors.WithStack(err)
	}

	dbDriver, err := postgres.WithInstance(db.DB.DB, &postgres.Config{})
	if err != nil {
		return errors.WithStack(err)
	}

	migration, err := migrate.NewWithInstance(
		"iofs",
		sourceDriver,
		"postgres",
		dbDriver,
	)
	if err != nil {
		return errors.WithStack(err)
	}

	if err := migration.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
		return err
	}

	return nil
}

// SaveBookmarks saves new or updated bookmarks to database.
// Returns the saved ID and error message if any happened.
func (db *PGDatabase) SaveBookmarks(ctx context.Context, create bool, bookmarks ...model.Bookmark) (result []model.Bookmark, err error) {
	result = []model.Bookmark{}
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Prepare statement
		stmtInsertBook, err := tx.Preparex(`INSERT INTO bookmark
			(url, title, excerpt, author, public, content, html, modified)
			VALUES($1, $2, $3, $4, $5, $6, $7, $8)
		RETURNING id`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtUpdateBook, err := tx.Preparex(`UPDATE bookmark SET
			url      = $1,
			title    = $2,
			excerpt  = $3,
			author   = $4,
			public   = $5,
			content  = $6,
			html     = $7,
			modified = $8
			WHERE id = $9`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtGetTag, err := tx.Preparex(`SELECT id FROM tag WHERE name = $1`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertTag, err := tx.Preparex(`INSERT INTO tag (name) VALUES ($1) RETURNING id`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertBookTag, err := tx.Preparex(`INSERT INTO bookmark_tag
			(tag_id, bookmark_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtDeleteBookTag, err := tx.Preparex(`DELETE FROM bookmark_tag
			WHERE bookmark_id = $1 AND tag_id = $2`)
		if err != nil {
			return errors.WithStack(err)
		}

		// Prepare modified time
		modifiedTime := time.Now().UTC().Format(model.DatabaseDateFormat)

		// Execute statements
		result = []model.Bookmark{}
		for _, book := range bookmarks {
			// URL and title
			if book.URL == "" {
				return errors.New("URL must not be empty")
			}

			if book.Title == "" {
				return errors.New("title must not be empty")
			}

			// Set modified time
			if book.Modified == "" {
				book.Modified = modifiedTime
			}

			// Save bookmark
			var err error
			if create {
				err = stmtInsertBook.QueryRowContext(ctx,
					book.URL, book.Title, book.Excerpt, book.Author,
					book.Public, book.Content, book.HTML, book.Modified).Scan(&book.ID)
			} else {
				_, err = stmtUpdateBook.ExecContext(ctx,
					book.URL, book.Title, book.Excerpt, book.Author,
					book.Public, book.Content, book.HTML, book.Modified, book.ID)
			}
			if err != nil {
				return errors.WithStack(err)
			}

			// Save book tags
			newTags := []model.Tag{}
			for _, tag := range book.Tags {
				// If it's deleted tag, delete and continue
				if tag.Deleted {
					_, err = stmtDeleteBookTag.ExecContext(ctx, book.ID, tag.ID)
					if err != nil {
						return errors.WithStack(err)
					}
					continue
				}

				// Normalize tag name
				tagName := strings.ToLower(tag.Name)
				tagName = strings.Join(strings.Fields(tagName), " ")

				// If tag doesn't have any ID, fetch it from database
				if tag.ID == 0 {
					err = stmtGetTag.GetContext(ctx, &tag.ID, tagName)
					if err != nil && !errors.Is(err, sql.ErrNoRows) {
						return errors.WithStack(err)
					}

					// If tag doesn't exist in database, save it
					if tag.ID == 0 {
						var tagID64 int64
						err = stmtInsertTag.GetContext(ctx, &tagID64, tagName)
						if err != nil {
							return errors.WithStack(err)
						}

						tag.ID = int(tagID64)
					}

					if _, err := stmtInsertBookTag.ExecContext(ctx, tag.ID, book.ID); err != nil {
						return errors.WithStack(err)
					}
				}

				newTags = append(newTags, tag)
			}

			book.Tags = newTags
			result = append(result, book)
		}

		return nil
	}); err != nil {
		return nil, errors.WithStack(err)
	}

	return result, nil
}

// GetBookmarks fetch list of bookmarks based on submitted options.
func (db *PGDatabase) GetBookmarks(ctx context.Context, opts GetBookmarksOptions) ([]model.Bookmark, error) {
	// Create initial query
	columns := []string{
		`id`,
		`url`,
		`title`,
		`excerpt`,
		`author`,
		`public`,
		`modified`,
		`content <> '' has_content`}

	if opts.WithContent {
		columns = append(columns, `content`, `html`)
	}

	query := `SELECT ` + strings.Join(columns, ",") + `
		FROM bookmark WHERE TRUE`

	// Add where clause
	arg := map[string]interface{}{}

	// Add where clause for IDs
	if len(opts.IDs) > 0 {
		query += ` AND id IN (:ids)`
		arg["ids"] = opts.IDs
	}

	// Add where clause for search keyword
	if opts.Keyword != "" {
		query += ` AND (
			url LIKE '%' || :kw || '%' OR
			title LIKE '%' || :kw || '%' OR
			excerpt LIKE '%' || :kw || '%' OR
			content LIKE '%' || :kw || '%'
		)`

		arg["kw"] = opts.Keyword
	}

	// Add where clause for tags.
	// First we check for * in excluded and included tags,
	// which means all tags will be excluded and included, respectively.
	excludeAllTags := false
	for _, excludedTag := range opts.ExcludedTags {
		if excludedTag == "*" {
			excludeAllTags = true
			opts.ExcludedTags = []string{}
			break
		}
	}

	includeAllTags := false
	for _, includedTag := range opts.Tags {
		if includedTag == "*" {
			includeAllTags = true
			opts.Tags = []string{}
			break
		}
	}

	// If all tags excluded, we will only show bookmark without tags.
	// In other hand, if all tags included, we will only show bookmark with tags.
	if excludeAllTags {
		query += ` AND id NOT IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	} else if includeAllTags {
		query += ` AND id IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	}

	// Now we only need to find the normal tags
	if len(opts.Tags) > 0 {
		query += ` AND id IN (
			SELECT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(:tags)
			GROUP BY bt.bookmark_id
			HAVING COUNT(bt.bookmark_id) = :ltags)`

		arg["tags"] = opts.Tags
		arg["ltags"] = len(opts.Tags)
	}

	if len(opts.ExcludedTags) > 0 {
		query += ` AND id NOT IN (
			SELECT DISTINCT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(:extags))`

		arg["extags"] = opts.ExcludedTags
	}

	// Add order clause
	switch opts.OrderMethod {
	case ByLastAdded:
		query += ` ORDER BY id DESC`
	case ByLastModified:
		query += ` ORDER BY modified DESC`
	default:
		query += ` ORDER BY id`
	}

	if opts.Limit > 0 && opts.Offset >= 0 {
		query += ` LIMIT :limit OFFSET :offset`
		arg["limit"] = opts.Limit
		arg["offset"] = opts.Offset
	}

	// Expand query, because some of the args might be an array
	var err error
	query, args, _ := sqlx.Named(query, arg)
	query, args, err = sqlx.In(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to expand query: %v", err)
	}
	query = db.Rebind(query)

	// Fetch bookmarks
	bookmarks := []model.Bookmark{}
	err = db.SelectContext(ctx, &bookmarks, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return nil, fmt.Errorf("failed to fetch data: %v", err)
	}

	// Fetch tags for each bookmarks
	stmtGetTags, err := db.PreparexContext(ctx, `SELECT t.id, t.name
		FROM bookmark_tag bt
		LEFT JOIN tag t ON bt.tag_id = t.id
		WHERE bt.bookmark_id = $1
		ORDER BY t.name`)
	if err != nil {
		return nil, fmt.Errorf("failed to prepare tag query: %v", err)
	}
	defer stmtGetTags.Close()

	for i, book := range bookmarks {
		book.Tags = []model.Tag{}
		err = stmtGetTags.SelectContext(ctx, &book.Tags, book.ID)
		if err != nil && err != sql.ErrNoRows {
			return nil, fmt.Errorf("failed to fetch tags: %v", err)
		}

		bookmarks[i] = book
	}

	return bookmarks, nil
}

// GetBookmarksCount fetch count of bookmarks based on submitted options.
func (db *PGDatabase) GetBookmarksCount(ctx context.Context, opts GetBookmarksOptions) (int, error) {
	// Create initial query
	query := `SELECT COUNT(id) FROM bookmark WHERE TRUE`

	arg := map[string]interface{}{}

	// Add where clause for IDs
	if len(opts.IDs) > 0 {
		query += ` AND id IN (:ids)`
		arg["ids"] = opts.IDs
	}

	// Add where clause for search keyword
	if opts.Keyword != "" {
		query += ` AND (
			url LIKE '%' || :kw || '%' OR
			title LIKE '%' || :kw || '%' OR
			excerpt LIKE '%' || :kw || '%' OR
			content LIKE '%' || :kw || '%'
		)`

		arg["lurl"] = "%" + opts.Keyword + "%"
		arg["kw"] = opts.Keyword
	}

	// Add where clause for tags.
	// First we check for * in excluded and included tags,
	// which means all tags will be excluded and included, respectively.
	excludeAllTags := false
	for _, excludedTag := range opts.ExcludedTags {
		if excludedTag == "*" {
			excludeAllTags = true
			opts.ExcludedTags = []string{}
			break
		}
	}

	includeAllTags := false
	for _, includedTag := range opts.Tags {
		if includedTag == "*" {
			includeAllTags = true
			opts.Tags = []string{}
			break
		}
	}

	// If all tags excluded, we will only show bookmark without tags.
	// In other hand, if all tags included, we will only show bookmark with tags.
	if excludeAllTags {
		query += ` AND id NOT IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	} else if includeAllTags {
		query += ` AND id IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	}

	// Now we only need to find the normal tags
	if len(opts.Tags) > 0 {
		query += ` AND id IN (
			SELECT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(:tags)
			GROUP BY bt.bookmark_id
			HAVING COUNT(bt.bookmark_id) = :ltags)`

		arg["tags"] = opts.Tags
		arg["ltags"] = len(opts.Tags)
	}

	if len(opts.ExcludedTags) > 0 {
		query += ` AND id NOT IN (
			SELECT DISTINCT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(:etags))`

		arg["etags"] = opts.ExcludedTags
	}

	// Expand query, because some of the args might be an array
	var err error
	query, args, err := sqlx.Named(query, arg)
	if err != nil {
		return 0, errors.WithStack(err)
	}

	query, args, err = sqlx.In(query, args...)
	if err != nil {
		return 0, errors.WithStack(err)
	}
	query = db.Rebind(query)

	// Fetch count
	var nBookmarks int
	err = db.GetContext(ctx, &nBookmarks, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return 0, errors.WithStack(err)
	}

	return nBookmarks, nil
}

// DeleteBookmarks removes all record with matching ids from database.
func (db *PGDatabase) DeleteBookmarks(ctx context.Context, ids ...int) (err error) {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Prepare queries
		delBookmark := `DELETE FROM bookmark`
		delBookmarkTag := `DELETE FROM bookmark_tag`

		// Delete bookmark(s)
		if len(ids) == 0 {
			_, err := tx.ExecContext(ctx, delBookmarkTag)
			if err != nil {
				return errors.WithStack(err)
			}

			_, err = tx.ExecContext(ctx, delBookmark)
			if err != nil {
				return errors.WithStack(err)
			}
		} else {
			delBookmark += ` WHERE id = $1`
			delBookmarkTag += ` WHERE bookmark_id = $1`

			stmtDelBookmark, err := tx.Preparex(delBookmark)
			if err != nil {
				return errors.WithStack(err)
			}
			stmtDelBookmarkTag, err := tx.Preparex(delBookmarkTag)
			if err != nil {
				return errors.WithStack(err)
			}

			for _, id := range ids {
				_, err = stmtDelBookmarkTag.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}

				_, err = stmtDelBookmark.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}
			}
		}

		return nil
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// GetBookmark fetches bookmark based on its ID or URL.
// Returns the bookmark and boolean whether it's exist or not.
func (db *PGDatabase) GetBookmark(ctx context.Context, id int, url string) (model.Bookmark, bool, error) {
	args := []interface{}{id}
	query := `SELECT
		id, url, title, excerpt, author, public,
		content, html, modified, content <> '' has_content
		FROM bookmark WHERE id = $1`

	if url != "" {
		query += ` OR url = $2`
		args = append(args, url)
	}

	book := model.Bookmark{}
	if err := db.GetContext(ctx, &book, query, args...); err != nil && err != sql.ErrNoRows {
		return book, false, errors.WithStack(err)
	}

	return book, book.ID != 0, nil
}

// SaveAccount saves new account to database. Returns error if any happened.
func (db *PGDatabase) SaveAccount(ctx context.Context, account model.Account) (err error) {
	// Hash password with bcrypt
	hashedPassword, err := bcrypt.GenerateFromPassword([]byte(account.Password), 10)
	if err != nil {
		return err
	}

	// Insert account to database
	_, err = db.ExecContext(ctx, `INSERT INTO account
		(username, password, owner, config) VALUES ($1, $2, $3, $4)
		ON CONFLICT(username) DO UPDATE SET
		password = $2,
		owner = $3`,
		account.Username, hashedPassword, account.Owner, account.Config)

	return errors.WithStack(err)
}

// SaveAccountSettings update settings for specific account  in database. Returns error if any happened
func (db *PGDatabase) SaveAccountSettings(ctx context.Context, account model.Account) (err error) {

	// Insert account to database
	_, err = db.ExecContext(ctx, `UPDATE account
   		SET config = $1
   		WHERE username = $2`,
		account.Config, account.Username)

	return errors.WithStack(err)
}

// GetAccounts fetch list of account (without its password) based on submitted options.
func (db *PGDatabase) GetAccounts(ctx context.Context, opts GetAccountsOptions) ([]model.Account, error) {
	// Create query
	args := []interface{}{}
	query := `SELECT id, username, owner, config FROM account WHERE TRUE`

	if opts.Keyword != "" {
		query += " AND username LIKE $1"
		args = append(args, "%"+opts.Keyword+"%")
	}

	if opts.Owner {
		query += " AND owner = TRUE"
	}

	query += ` ORDER BY username`

	// Fetch list account
	accounts := []model.Account{}
	err := db.SelectContext(ctx, &accounts, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	return accounts, nil
}

// GetAccount fetch account with matching username.
// Returns the account and boolean whether it's exist or not.
func (db *PGDatabase) GetAccount(ctx context.Context, username string) (model.Account, bool, error) {
	account := model.Account{}
	if err := db.GetContext(ctx, &account, `SELECT
		id, username, password, owner, config FROM account WHERE username = $1`,
		username,
	); err != nil {
		return account, false, errors.WithStack(err)
	}

	return account, account.ID != 0, nil
}

// DeleteAccounts removes all record with matching usernames.
func (db *PGDatabase) DeleteAccounts(ctx context.Context, usernames ...string) (err error) {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Delete account
		stmtDelete, _ := tx.Preparex(`DELETE FROM account WHERE username = $1`)
		for _, username := range usernames {
			if _, err := stmtDelete.ExecContext(ctx, username); err != nil {
				return errors.WithStack(err)
			}
		}

		return nil
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// CreateTags creates new tags from submitted objects.
func (db *PGDatabase) CreateTags(ctx context.Context, tags ...model.Tag) error {
	query := `INSERT INTO tag (name) VALUES (:name)`

	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		if _, err := tx.NamedExec(query, tags); err != nil {
			return errors.WithStack(err)
		}

		return nil
	}); err != nil {
		return errors.Wrap(errors.WithStack(err), "error running transaction")
	}

	return nil
}

// GetTags fetch list of tags and their frequency.
func (db *PGDatabase) GetTags(ctx context.Context) ([]model.Tag, error) {
	tags := []model.Tag{}
	query := `SELECT bt.tag_id id, t.name, COUNT(bt.tag_id) n_bookmarks
		FROM bookmark_tag bt
		LEFT JOIN tag t ON bt.tag_id = t.id
		GROUP BY bt.tag_id, t.name ORDER BY t.name`

	err := db.SelectContext(ctx, &tags, query)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	return tags, nil
}

// RenameTag change the name of a tag.
func (db *PGDatabase) RenameTag(ctx context.Context, id int, newName string) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		_, err := db.Exec(`UPDATE tag SET name = $1 WHERE id = $2`, newName, id)
		return errors.WithStack(err)
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}


code:
package database

import (
	"context"
	"errors"
	"log"
	"os"
	"path/filepath"
	"testing"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-migrate/migrate/v4"
	"github.com/stretchr/testify/assert"
)

func init() {
	connString := os.Getenv("SHIORI_TEST_PG_URL")
	if connString == "" {
		log.Fatal("psql tests can't run without a PSQL database, set SHIORI_TEST_PG_URL environment variable")
	}
}

func postgresqlTestDatabaseFactory(ctx context.Context) (DB, error) {
	db, err := OpenPGDatabase(ctx, os.Getenv("SHIORI_TEST_PG_URL"))
	if err != nil {
		return nil, err
	}

	_, err = db.Exec("DROP SCHEMA public CASCADE; CREATE SCHEMA public;")
	if err != nil {
		return nil, err
	}

	if err := db.Migrate(); err != nil && !errors.Is(migrate.ErrNoChange, err) {
		return nil, err
	}

	return db, nil
}

func TestPostgresDatabase(t *testing.T) {
	testDatabase(t, postgresqlTestDatabaseFactory)
}

func TestSaveAccountSettingsPg(t *testing.T) {
	ctx := context.TODO()

	db, err := postgresqlTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Mock data
	account := model.Account{
		Username: "testuser",
		Config:   model.UserConfig{},
	}

	// Successful case
	err = db.SaveAccountSettings(ctx, account)
	assert.NoError(t, err)

	// Initialize not correct database
	ctx = context.TODO()
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err = factory(ctx)
	assert.Nil(t, err)
	account = model.Account{
		Username: "testuser",
		Config:   model.UserConfig{},
	}
	err = db.SaveAccountSettings(ctx, account)
	assert.NotNil(t, err)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")
}
func TestGetAccountsPg(t *testing.T) {
	ctx := context.TODO()

	db, err := postgresqlTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Insert test accounts
	testAccounts := []model.Account{
		{Username: "foo", Password: "bar", Owner: false},
		{Username: "hello", Password: "world", Owner: false},
		{Username: "foo_bar", Password: "foobar", Owner: true},
	}
	for _, acc := range testAccounts {
		err := db.SaveAccount(ctx, acc)
		assert.Nil(t, err)
	}

	// Successful case
	// without opt
	accounts, err := db.GetAccounts(ctx, GetAccountsOptions{})
	assert.NoError(t, err)
	assert.Equal(t, 3, len(accounts))
	// with owner
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Owner: true})
	assert.NoError(t, err)
	assert.Equal(t, 1, len(accounts))
	// with opt
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "foo"})
	assert.NoError(t, err)
	assert.Equal(t, 2, len(accounts))
	// with opt and owner
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "hello", Owner: false})
	assert.NoError(t, err)
	assert.Equal(t, 1, len(accounts))
	// with not result
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "shiori"})
	assert.NoError(t, err)
	assert.Equal(t, 0, len(accounts))

	// Initialize not correct database
	ctx = context.TODO()
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err = factory(ctx)
	assert.Nil(t, err)
	// with invalid query
	opts := GetAccountsOptions{Keyword: "foo", Owner: true}
	_, err = db.GetAccounts(ctx, opts)
	assert.NotNil(t, err)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")
}

func TestGetAccountPg(t *testing.T) {
	ctx := context.TODO()

	db, err := postgresqlTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Insert test accounts
	testAccounts := []model.Account{
		{Username: "foo", Password: "bar", Owner: false},
		{Username: "hello", Password: "world", Owner: false},
		{Username: "foo_bar", Password: "foobar", Owner: true},
	}
	for _, acc := range testAccounts {
		err := db.SaveAccount(ctx, acc)
		assert.Nil(t, err)

		// Successful case
		account, exists, err := db.GetAccount(ctx, acc.Username)
		assert.Nil(t, err)
		assert.True(t, exists, "Expected account to exist")
		assert.Equal(t, acc.Username, account.Username)
	}
	// Falid case
	account, exists, err := db.GetAccount(ctx, "foobar")
	assert.NotNil(t, err)
	assert.False(t, exists, "Expected account to exist")
	assert.Empty(t, account.Username)
}

repo_name: orochi/shiori

license: mit


code:
package database

import (
	"context"
	"database/sql"
	"log"
	"strings"
	"time"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/sqlite"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	"golang.org/x/crypto/bcrypt"
)

// SQLiteDatabase is implementation of Database interface
// for connecting to SQLite3 database.
type SQLiteDatabase struct {
	dbbase
}

type bookmarkContent struct {
	ID      int    `db:"docid"`
	Content string `db:"content"`
	HTML    string `db:"html"`
}

type tagContent struct {
	ID int `db:"bookmark_id"`
	model.Tag
}

// OpenSQLiteDatabase creates and open connection to new SQLite3 database.
func OpenSQLiteDatabase(ctx context.Context, databasePath string) (sqliteDB *SQLiteDatabase, err error) {
	// Open database
	db, err := sqlx.ConnectContext(ctx, "sqlite", databasePath)
	if err != nil {
		return nil, errors.WithStack(err)
	}

	sqliteDB = &SQLiteDatabase{dbbase: dbbase{*db}}
	return sqliteDB, nil
}

// Migrate runs migrations for this database engine
func (db *SQLiteDatabase) Migrate() error {
	sourceDriver, err := iofs.New(migrations, "migrations/sqlite")
	if err != nil {
		return errors.WithStack(err)
	}

	dbDriver, err := sqlite.WithInstance(db.DB.DB, &sqlite.Config{})
	if err != nil {
		return errors.WithStack(err)
	}

	migration, err := migrate.NewWithInstance(
		"iofs",
		sourceDriver,
		"sqlite",
		dbDriver,
	)
	if err != nil {
		return errors.WithStack(err)
	}

	if err := migration.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
		return err
	}

	return nil
}

// SaveBookmarks saves new or updated bookmarks to database.
// Returns the saved ID and error message if any happened.
func (db *SQLiteDatabase) SaveBookmarks(ctx context.Context, create bool, bookmarks ...model.Bookmark) ([]model.Bookmark, error) {
	var result []model.Bookmark

	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Prepare statement

		stmtInsertBook, err := tx.PreparexContext(ctx, `INSERT INTO bookmark
			(url, title, excerpt, author, public, modified, has_content)
			VALUES(?, ?, ?, ?, ?, ?, ?) RETURNING id`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtUpdateBook, err := tx.PreparexContext(ctx, `UPDATE bookmark SET
			url = ?, title = ?,	excerpt = ?, author = ?,
			public = ?, modified = ?, has_content = ?
			WHERE id = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertBookContent, err := tx.PreparexContext(ctx, `INSERT OR REPLACE INTO bookmark_content
			(docid, title, content, html)
			VALUES (?, ?, ?, ?)`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtUpdateBookContent, err := tx.PreparexContext(ctx, `UPDATE bookmark_content SET
			title = ?, content = ?, html = ?
			WHERE docid = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtGetTag, err := tx.PreparexContext(ctx, `SELECT id FROM tag WHERE name = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertTag, err := tx.PreparexContext(ctx, `INSERT INTO tag (name) VALUES (?)`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertBookTag, err := tx.PreparexContext(ctx, `INSERT OR IGNORE INTO bookmark_tag
			(tag_id, bookmark_id) VALUES (?, ?)`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtDeleteBookTag, err := tx.PreparexContext(ctx, `DELETE FROM bookmark_tag
			WHERE bookmark_id = ? AND tag_id = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		// Prepare modified time
		modifiedTime := time.Now().UTC().Format(model.DatabaseDateFormat)

		// Execute statements

		for _, book := range bookmarks {
			// Check URL and title
			if book.URL == "" {
				return errors.New("URL must not be empty")
			}

			if book.Title == "" {
				return errors.New("title must not be empty")
			}

			// Set modified time
			if book.Modified == "" {
				book.Modified = modifiedTime
			}

			hasContent := book.Content != ""

			// Create or update bookmark
			var err error
			if create {
				err = stmtInsertBook.QueryRowContext(ctx,
					book.URL, book.Title, book.Excerpt, book.Author, book.Public, book.Modified, hasContent).Scan(&book.ID)
			} else {
				_, err = stmtUpdateBook.ExecContext(ctx,
					book.URL, book.Title, book.Excerpt, book.Author, book.Public, book.Modified, hasContent, book.ID)
			}
			if err != nil {
				return errors.WithStack(err)
			}

			// Try to update it first to check for existence, we can't do an UPSERT here because
			// bookmant_content is a virtual table
			res, err := stmtUpdateBookContent.ExecContext(ctx, book.Title, book.Content, book.HTML, book.ID)
			if err != nil {
				return errors.WithStack(err)
			}

			rows, err := res.RowsAffected()
			if err != nil {
				return errors.WithStack(err)
			}

			if rows == 0 {
				_, err = stmtInsertBookContent.ExecContext(ctx, book.ID, book.Title, book.Content, book.HTML)
				if err != nil {
					return errors.WithStack(err)
				}
			}

			// Save book tags
			newTags := []model.Tag{}
			for _, tag := range book.Tags {
				// If it's deleted tag, delete and continue
				if tag.Deleted {
					_, err = stmtDeleteBookTag.ExecContext(ctx, book.ID, tag.ID)
					if err != nil {
						return errors.WithStack(err)
					}
					continue
				}

				// Normalize tag name
				tagName := strings.ToLower(tag.Name)
				tagName = strings.Join(strings.Fields(tagName), " ")

				// If tag doesn't have any ID, fetch it from database
				if tag.ID == 0 {
					if err := stmtGetTag.GetContext(ctx, &tag.ID, tagName); err != nil && err != sql.ErrNoRows {
						return errors.WithStack(err)
					}

					// If tag doesn't exist in database, save it
					if tag.ID == 0 {
						res, err := stmtInsertTag.ExecContext(ctx, tagName)
						if err != nil {
							return errors.WithStack(err)
						}

						tagID64, err := res.LastInsertId()
						if err != nil && err != sql.ErrNoRows {
							return errors.WithStack(err)
						}

						tag.ID = int(tagID64)
					}

					if _, err := stmtInsertBookTag.ExecContext(ctx, tag.ID, book.ID); err != nil {
						return errors.WithStack(err)
					}
				}

				newTags = append(newTags, tag)
			}

			book.Tags = newTags
			result = append(result, book)
		}

		return nil
	}); err != nil {
		return nil, errors.WithStack(err)
	}

	return result, nil
}

// GetBookmarks fetch list of bookmarks based on submitted options.
func (db *SQLiteDatabase) GetBookmarks(ctx context.Context, opts GetBookmarksOptions) ([]model.Bookmark, error) {
	// Create initial query
	query := `SELECT
		b.id,
		b.url,
		b.title,
		b.excerpt,
		b.author,
		b.public,
		b.modified,
		b.has_content
		FROM bookmark b
		WHERE 1`

	// Add where clause
	args := []interface{}{}

	// Add where clause for IDs
	if len(opts.IDs) > 0 {
		query += ` AND b.id IN (?)`
		args = append(args, opts.IDs)
	}

	// Add where clause for search keyword
	if opts.Keyword != "" {
		query += ` AND (b.url LIKE '%' || ? || '%' OR b.excerpt LIKE '%' || ? || '%' OR b.id IN (
			SELECT docid id
			FROM bookmark_content
			WHERE title MATCH ? OR content MATCH ?))`

		args = append(args, opts.Keyword, opts.Keyword)

		// Replace dash with spaces since FTS5 uses `-name` as column identifier and double quote
		// since FTS5 uses double quote as string identifier
		// Reference: https://sqlite.org/fts5.html#fts5_strings
		ftsKeyword := strings.ReplaceAll(opts.Keyword, "-", " ")

		// Properly set double quotes for string literals in sqlite's fts
		ftsKeyword = strings.ReplaceAll(ftsKeyword, "\"", "\"\"")

		args = append(args, "\""+ftsKeyword+"\"", "\""+ftsKeyword+"\"")
	}

	// Add where clause for tags.
	// First we check for * in excluded and included tags,
	// which means all tags will be excluded and included, respectively.
	excludeAllTags := false
	for _, excludedTag := range opts.ExcludedTags {
		if excludedTag == "*" {
			excludeAllTags = true
			opts.ExcludedTags = []string{}
			break
		}
	}

	includeAllTags := false
	for _, includedTag := range opts.Tags {
		if includedTag == "*" {
			includeAllTags = true
			opts.Tags = []string{}
			break
		}
	}

	// If all tags excluded, we will only show bookmark without tags.
	// In other hand, if all tags included, we will only show bookmark with tags.
	if excludeAllTags {
		query += ` AND b.id NOT IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	} else if includeAllTags {
		query += ` AND b.id IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	}

	// Now we only need to find the normal tags
	if len(opts.Tags) > 0 {
		query += ` AND b.id IN (
			SELECT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?)
			GROUP BY bt.bookmark_id
			HAVING COUNT(bt.bookmark_id) = ?)`

		args = append(args, opts.Tags, len(opts.Tags))
	}

	if len(opts.ExcludedTags) > 0 {
		query += ` AND b.id NOT IN (
			SELECT DISTINCT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?))`

		args = append(args, opts.ExcludedTags)
	}

	// Add order clause
	switch opts.OrderMethod {
	case ByLastAdded:
		query += ` ORDER BY b.id DESC`
	case ByLastModified:
		query += ` ORDER BY b.modified DESC`
	default:
		query += ` ORDER BY b.id`
	}

	if opts.Limit > 0 && opts.Offset >= 0 {
		query += ` LIMIT ? OFFSET ?`
		args = append(args, opts.Limit, opts.Offset)
	}

	// Expand query, because some of the args might be an array
	query, args, err := sqlx.In(query, args...)
	if err != nil {
		return nil, errors.WithStack(err)
	}

	// Fetch bookmarks
	bookmarks := []model.Bookmark{}
	err = db.SelectContext(ctx, &bookmarks, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	// store bookmark IDs for further enrichment
	var bookmarkIds = make([]int, 0, len(bookmarks))
	for _, book := range bookmarks {
		bookmarkIds = append(bookmarkIds, book.ID)
	}

	if len(bookmarkIds) == 0 {
		return bookmarks, nil
	}

	// If content needed, fetch it separately
	// It's faster than join with virtual table
	if opts.WithContent {
		contents := make([]bookmarkContent, 0, len(bookmarks))
		contentMap := make(map[int]bookmarkContent, len(bookmarks))

		contentQuery, args, err := sqlx.In(`SELECT docid, content, html FROM bookmark_content WHERE docid IN (?)`, bookmarkIds)
		contentQuery = db.Rebind(contentQuery)
		if err != nil {
			return nil, errors.WithStack(err)
		}

		err = db.Select(&contents, contentQuery, args...)
		if err != nil && err != sql.ErrNoRows {
			return nil, errors.WithStack(err)
		}
		for _, content := range contents {
			contentMap[content.ID] = content
		}
		for i := range bookmarks[:] {
			book := &bookmarks[i]
			if bookmarkContent, found := contentMap[book.ID]; found {
				book.Content = bookmarkContent.Content
				book.HTML = bookmarkContent.HTML
			} else {
				log.Printf("not found content for bookmark %d, but it should be; check DB consistency", book.ID)
			}
		}

	}

	// Fetch tags for each bookmark
	tags := make([]tagContent, 0, len(bookmarks))
	tagsMap := make(map[int][]model.Tag, len(bookmarks))

	tagsQuery, tagArgs, err := sqlx.In(`SELECT bt.bookmark_id, t.id, t.name
		FROM bookmark_tag bt
		LEFT JOIN tag t ON bt.tag_id = t.id
		WHERE bt.bookmark_id IN (?)
		ORDER BY t.name`, bookmarkIds)
	tagsQuery = db.Rebind(tagsQuery)
	if err != nil {
		return nil, errors.WithStack(err)
	}

	err = db.Select(&tags, tagsQuery, tagArgs...)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}
	for _, fetchedTag := range tags {
		if tags, found := tagsMap[fetchedTag.ID]; found {
			tagsMap[fetchedTag.ID] = append(tags, fetchedTag.Tag)
		} else {
			tagsMap[fetchedTag.ID] = []model.Tag{fetchedTag.Tag}
		}
	}
	for i := range bookmarks[:] {
		book := &bookmarks[i]
		if tags, found := tagsMap[book.ID]; found {
			book.Tags = tags
		} else {
			book.Tags = []model.Tag{}
		}
	}

	return bookmarks, nil
}

// GetBookmarksCount fetch count of bookmarks based on submitted options.
func (db *SQLiteDatabase) GetBookmarksCount(ctx context.Context, opts GetBookmarksOptions) (int, error) {
	// Create initial query
	query := `SELECT COUNT(b.id)
		FROM bookmark b
		WHERE 1`

	// Add where clause
	args := []interface{}{}

	// Add where clause for IDs
	if len(opts.IDs) > 0 {
		query += ` AND b.id IN (?)`
		args = append(args, opts.IDs)
	}

	// Add where clause for search keyword
	if opts.Keyword != "" {
		query += ` AND (b.url LIKE '%' || ? || '%' OR b.excerpt LIKE '%' || ? || '%' OR b.id IN (
			SELECT docid id
			FROM bookmark_content
			WHERE title MATCH ? OR content MATCH ?))`

		args = append(args, opts.Keyword, opts.Keyword)

		// Replace dash with spaces since FTS5 uses `-name` as column identifier and double quote
		// since FTS5 uses double quote as string identifier
		// Reference: https://sqlite.org/fts5.html#fts5_strings
		ftsKeyword := strings.ReplaceAll(opts.Keyword, "-", " ")

		// Properly set double quotes for string literals in sqlite's fts
		ftsKeyword = strings.ReplaceAll(ftsKeyword, "\"", "\"\"")

		args = append(args, "\""+ftsKeyword+"\"", "\""+ftsKeyword+"\"")
	}

	// Add where clause for tags.
	// First we check for * in excluded and included tags,
	// which means all tags will be excluded and included, respectively.
	excludeAllTags := false
	for _, excludedTag := range opts.ExcludedTags {
		if excludedTag == "*" {
			excludeAllTags = true
			opts.ExcludedTags = []string{}
			break
		}
	}

	includeAllTags := false
	for _, includedTag := range opts.Tags {
		if includedTag == "*" {
			includeAllTags = true
			opts.Tags = []string{}
			break
		}
	}

	// If all tags excluded, we will only show bookmark without tags.
	// In other hand, if all tags included, we will only show bookmark with tags.
	if excludeAllTags {
		query += ` AND b.id NOT IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	} else if includeAllTags {
		query += ` AND b.id IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	}

	// Now we only need to find the normal tags
	if len(opts.Tags) > 0 {
		query += ` AND b.id IN (
			SELECT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?)
			GROUP BY bt.bookmark_id
			HAVING COUNT(bt.bookmark_id) = ?)`

		args = append(args, opts.Tags, len(opts.Tags))
	}

	if len(opts.ExcludedTags) > 0 {
		query += ` AND b.id NOT IN (
			SELECT DISTINCT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?))`

		args = append(args, opts.ExcludedTags)
	}

	// Expand query, because some of the args might be an array
	query, args, err := sqlx.In(query, args...)
	if err != nil {
		return 0, errors.WithStack(err)
	}

	// Fetch count
	var nBookmarks int
	err = db.GetContext(ctx, &nBookmarks, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return 0, errors.WithStack(err)
	}

	return nBookmarks, nil
}

// DeleteBookmarks removes all record with matching ids from database.
func (db *SQLiteDatabase) DeleteBookmarks(ctx context.Context, ids ...int) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Prepare queries
		delBookmark := `DELETE FROM bookmark`
		delBookmarkTag := `DELETE FROM bookmark_tag`
		delBookmarkContent := `DELETE FROM bookmark_content`

		// Delete bookmark(s)
		if len(ids) == 0 {
			_, err := tx.ExecContext(ctx, delBookmarkContent)
			if err != nil {
				return errors.WithStack(err)
			}

			_, err = tx.ExecContext(ctx, delBookmarkTag)
			if err != nil {
				return errors.WithStack(err)
			}

			_, err = tx.ExecContext(ctx, delBookmark)
			if err != nil {
				return errors.WithStack(err)
			}
		} else {
			delBookmark += ` WHERE id = ?`
			delBookmarkTag += ` WHERE bookmark_id = ?`
			delBookmarkContent += ` WHERE docid = ?`

			stmtDelBookmark, err := tx.Preparex(delBookmark)
			if err != nil {
				return errors.WithStack(err)
			}

			stmtDelBookmarkTag, err := tx.Preparex(delBookmarkTag)
			if err != nil {
				return errors.WithStack(err)
			}

			stmtDelBookmarkContent, err := tx.Preparex(delBookmarkContent)
			if err != nil {
				return errors.WithStack(err)
			}

			for _, id := range ids {
				_, err = stmtDelBookmarkContent.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}

				_, err = stmtDelBookmarkTag.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}

				_, err = stmtDelBookmark.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}
			}
		}

		return nil
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// GetBookmark fetches bookmark based on its ID or URL.
// Returns the bookmark and boolean whether it's exist or not.
func (db *SQLiteDatabase) GetBookmark(ctx context.Context, id int, url string) (model.Bookmark, bool, error) {
	args := []interface{}{id}
	query := `SELECT
		b.id, b.url, b.title, b.excerpt, b.author, b.public, b.modified,
		bc.content, bc.html, b.has_content
		FROM bookmark b
		LEFT JOIN bookmark_content bc ON bc.docid = b.id
		WHERE b.id = ?`

	if url != "" {
		query += ` OR b.url = ?`
		args = append(args, url)
	}

	book := model.Bookmark{}
	if err := db.GetContext(ctx, &book, query, args...); err != nil && err != sql.ErrNoRows {
		return book, false, errors.WithStack(err)
	}

	return book, book.ID != 0, nil
}

// SaveAccount saves new account to database. Returns error if any happened.
func (db *SQLiteDatabase) SaveAccount(ctx context.Context, account model.Account) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Hash password with bcrypt
		hashedPassword, err := bcrypt.GenerateFromPassword([]byte(account.Password), 10)
		if err != nil {
			return err
		}

		// Insert account to database
		_, err = tx.Exec(`INSERT INTO account
		(username, password, owner, config) VALUES (?, ?, ?, ?)
		ON CONFLICT(username) DO UPDATE SET
		password = ?, owner = ?`,
			account.Username, hashedPassword, account.Owner, account.Config,
			hashedPassword, account.Owner, account.Config)
		return errors.WithStack(err)
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// SaveAccountSettings update settings for specific account  in database. Returns error if any happened.
func (db *SQLiteDatabase) SaveAccountSettings(ctx context.Context, account model.Account) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Update account config in database for specific user
		_, err := tx.Exec(`UPDATE account
	   SET config = ?
	   WHERE username = ?`,
			account.Config, account.Username)
		return errors.WithStack(err)
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// GetAccounts fetch list of account (without its password) based on submitted options.
func (db *SQLiteDatabase) GetAccounts(ctx context.Context, opts GetAccountsOptions) ([]model.Account, error) {
	// Create query
	args := []interface{}{}
	query := `SELECT id, username, owner, config FROM account WHERE 1`

	if opts.Keyword != "" {
		query += " AND username LIKE ?"
		args = append(args, "%"+opts.Keyword+"%")
	}

	if opts.Owner {
		query += " AND owner = 1"
	}

	query += ` ORDER BY username`

	// Fetch list account
	accounts := []model.Account{}
	err := db.SelectContext(ctx, &accounts, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	return accounts, nil
}

// GetAccount fetch account with matching username.
// Returns the account and boolean whether it's exist or not.
func (db *SQLiteDatabase) GetAccount(ctx context.Context, username string) (model.Account, bool, error) {
	account := model.Account{}
	if err := db.GetContext(ctx, &account, `SELECT
		id, username, password, owner, config FROM account WHERE username = ?`,
		username,
	); err != nil {
		return account, false, errors.WithStack(err)
	}

	return account, account.ID != 0, nil
}

// DeleteAccounts removes all record with matching usernames.
func (db *SQLiteDatabase) DeleteAccounts(ctx context.Context, usernames ...string) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Delete account
		stmtDelete, err := tx.Preparex(`DELETE FROM account WHERE username = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		for _, username := range usernames {
			_, err := stmtDelete.ExecContext(ctx, username)
			if err != nil {
				return errors.WithStack(err)
			}
		}

		return nil
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// CreateTags creates new tags from submitted objects.
func (db *SQLiteDatabase) CreateTags(ctx context.Context, tags ...model.Tag) error {
	query := `INSERT INTO tag (name) VALUES `
	values := []interface{}{}

	for _, t := range tags {
		query += "(?),"
		values = append(values, t.Name)
	}
	query = query[0 : len(query)-1]

	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		stmt, err := tx.Preparex(query)
		if err != nil {
			return errors.Wrap(errors.WithStack(err), "error preparing query")
		}

		_, err = stmt.ExecContext(ctx, values...)
		if err != nil {
			return errors.Wrap(errors.WithStack(err), "error executing query")
		}

		return nil
	}); err != nil {
		return errors.Wrap(errors.WithStack(err), "error running transaction")
	}

	return nil
}

// GetTags fetch list of tags and their frequency.
func (db *SQLiteDatabase) GetTags(ctx context.Context) ([]model.Tag, error) {
	tags := []model.Tag{}
	query := `SELECT bt.tag_id id, t.name, COUNT(bt.tag_id) n_bookmarks
		FROM bookmark_tag bt
		LEFT JOIN tag t ON bt.tag_id = t.id
		GROUP BY bt.tag_id ORDER BY t.name`

	err := db.SelectContext(ctx, &tags, query)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	return tags, nil
}

// RenameTag change the name of a tag.
func (db *SQLiteDatabase) RenameTag(ctx context.Context, id int, newName string) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		_, err := tx.ExecContext(ctx, `UPDATE tag SET name = ? WHERE id = ?`, newName, id)
		return err
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}


code:
package database

import (
	"context"
	"os"
	"path/filepath"
	"testing"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-migrate/migrate/v4"
	"github.com/pkg/errors"
	"github.com/stretchr/testify/assert"
)

var sqliteDatabaseTestPath string

func init() {
	sqliteDatabaseTestPath = filepath.Join(os.TempDir(), "shiori.db")
}

func sqliteTestDatabaseFactory(ctx context.Context) (DB, error) {
	os.Remove(sqliteDatabaseTestPath)

	db, err := OpenSQLiteDatabase(ctx, sqliteDatabaseTestPath)
	if err != nil {
		return nil, err
	}

	if err := db.Migrate(); err != nil && !errors.Is(migrate.ErrNoChange, err) {
		return nil, err
	}

	return db, nil
}

func TestSqliteDatabase(t *testing.T) {
	testDatabase(t, sqliteTestDatabaseFactory)
	testSqliteGetBookmarksWithDash(t)
}

// testSqliteGetBookmarksWithDash ad-hoc test for SQLite that checks that a match search against
// the FTS5 engine does not fail by using dashes, making sqlite think that we are trying to avoid
// matching a column name. This works in a fun way and it seems that it depends on the tokens
// already scanned by the database, since trying to match for `go-shiori` with no bookmarks or only
// the shiori bookmark does not fail, but it fails if we add any other bookmark to the database, hence
// this test.
func testSqliteGetBookmarksWithDash(t *testing.T) {
	ctx := context.TODO()

	db, err := sqliteTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	_, err = db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	book = model.Bookmark{
		URL:   "https://github.com/jamiehannaford/what-happens-when-k8s",
		Title: "what-happens-when-k8s",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")
	savedBookmark := result[0]

	results, err := db.GetBookmarks(ctx, GetBookmarksOptions{
		Keyword: "what-happens-when",
	})

	assert.NoError(t, err, "Get bookmarks should not fail")
	assert.Len(t, results, 1, "results should contain one item")
	assert.Equal(t, savedBookmark.ID, results[0].ID, "bookmark should be the one saved")

}
func TestSQLiteDatabase_SaveAccount(t *testing.T) {

	ctx := context.TODO()

	// Initialize not correct database
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err := factory(ctx)
	assert.Nil(t, err)

	// Test falid database
	acc := model.Account{}
	err = db.SaveAccount(ctx, acc)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")

}

func TestSaveAccountSettings(t *testing.T) {
	ctx := context.TODO()

	db, err := sqliteTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Mock data
	account := model.Account{
		Username: "testuser",
		Config:   model.UserConfig{},
	}

	// Successful case
	err = db.SaveAccountSettings(ctx, account)
	assert.NoError(t, err)

	// Initialize not correct database
	ctx = context.TODO()
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err = factory(ctx)
	assert.Nil(t, err)
	account = model.Account{
		Username: "testuser",
		Config:   model.UserConfig{},
	}
	err = db.SaveAccountSettings(ctx, account)
	assert.NotNil(t, err)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")
}

func TestGetAccounts(t *testing.T) {
	ctx := context.TODO()

	db, err := sqliteTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Insert test accounts
	testAccounts := []model.Account{
		{Username: "foo", Password: "bar", Owner: false},
		{Username: "hello", Password: "world", Owner: false},
		{Username: "foo_bar", Password: "foobar", Owner: true},
	}
	for _, acc := range testAccounts {
		err := db.SaveAccount(ctx, acc)
		assert.Nil(t, err)
	}

	// Successful case
	// without opt
	accounts, err := db.GetAccounts(ctx, GetAccountsOptions{})
	assert.NoError(t, err)
	assert.Equal(t, 3, len(accounts))
	// with owner
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Owner: true})
	assert.NoError(t, err)
	assert.Equal(t, 1, len(accounts))
	// with opt
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "foo"})
	assert.NoError(t, err)
	assert.Equal(t, 2, len(accounts))
	// with opt and owner
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "hello", Owner: false})
	assert.NoError(t, err)
	assert.Equal(t, 1, len(accounts))
	// with not result
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "shiori"})
	assert.NoError(t, err)
	assert.Equal(t, 0, len(accounts))

	// Initialize not correct database
	ctx = context.TODO()
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err = factory(ctx)
	assert.Nil(t, err)
	// with invalid query
	opts := GetAccountsOptions{Keyword: "foo", Owner: true}
	_, err = db.GetAccounts(ctx, opts)
	assert.NotNil(t, err)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")
}


code:
package domains

import (
	"context"
	"fmt"
	"time"

	"github.com/go-shiori/shiori/internal/database"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-jwt/jwt/v5"
	"github.com/pkg/errors"
	"github.com/sirupsen/logrus"
	"golang.org/x/crypto/bcrypt"
)

type AccountsDomain struct {
	logger *logrus.Logger
	db     database.DB
	secret []byte
}

type JWTClaim struct {
	jwt.RegisteredClaims

	Account *model.Account
}

func (d *AccountsDomain) CheckToken(ctx context.Context, userJWT string) (*model.Account, error) {
	token, err := jwt.ParseWithClaims(userJWT, &JWTClaim{}, func(token *jwt.Token) (interface{}, error) {
		// Validate algorithm
		if _, ok := token.Method.(*jwt.SigningMethodHMAC); !ok {
			return nil, fmt.Errorf("Unexpected signing method: %v", token.Header["alg"])
		}

		return d.secret, nil
	})
	if err != nil {
		return nil, errors.Wrap(err, "error parsing token")
	}

	if claims, ok := token.Claims.(*JWTClaim); ok && token.Valid {
		if claims.Account.ID > 0 {
			return claims.Account, nil
		}
		if err != nil {
			return nil, err
		}

		return claims.Account, nil
	}
	return nil, fmt.Errorf("error obtaining user from JWT claims")
}

func (d *AccountsDomain) GetAccountFromCredentials(ctx context.Context, username, password string) (*model.Account, error) {
	account, _, err := d.db.GetAccount(ctx, username)
	if err != nil {
		return nil, fmt.Errorf("username and password do not match")
	}

	if err := bcrypt.CompareHashAndPassword([]byte(account.Password), []byte(password)); err != nil {
		return nil, fmt.Errorf("username and password do not match")
	}

	return &account, nil
}

func (d *AccountsDomain) CreateTokenForAccount(account *model.Account, expiration time.Time) (string, error) {
	claims := jwt.MapClaims{
		"account": account.ToDTO(),
		"exp":     expiration.UTC().Unix(),
	}

	token := jwt.NewWithClaims(jwt.SigningMethodHS256, claims)

	t, err := token.SignedString(d.secret)
	if err != nil {
		d.logger.WithError(err).Error("error signing token")
	}

	return t, err
}

func NewAccountsDomain(logger *logrus.Logger, secretKey string, db database.DB) AccountsDomain {
	return AccountsDomain{
		logger: logger,
		db:     db,
		secret: []byte(secretKey),
	}
}


code:
package domains

import (
	"fmt"
	"os"
	"path/filepath"
	"strconv"

	"github.com/go-shiori/shiori/internal/core"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
)

type ArchiverDomain struct {
	dataDir string
	logger  *logrus.Logger
}

func (d *ArchiverDomain) DownloadBookmarkArchive(book model.Bookmark) (*model.Bookmark, error) {
	content, contentType, err := core.DownloadBookmark(book.URL)
	if err != nil {
		return nil, fmt.Errorf("error downloading url: %s", err)
	}

	processRequest := core.ProcessRequest{
		DataDir:     d.dataDir,
		Bookmark:    book,
		Content:     content,
		ContentType: contentType,
	}

	result, isFatalErr, err := core.ProcessBookmark(processRequest)
	content.Close()

	if err != nil && isFatalErr {
		return nil, fmt.Errorf("failed to process: %v", err)
	}

	return &result, nil
}

func (d *ArchiverDomain) GetBookmarkArchive(book model.Bookmark) error {
	archivePath := filepath.Join(d.dataDir, "archive", strconv.Itoa(book.ID))

	info, err := os.Stat(archivePath)
	if !os.IsNotExist(err) && !info.IsDir() {
		return fmt.Errorf("archive not found")
	}

	return nil
}

func NewArchiverDomain(logger *logrus.Logger, dataDir string) ArchiverDomain {
	return ArchiverDomain{
		dataDir: dataDir,
		logger:  logger,
	}
}


code:
package context

import "github.com/go-shiori/shiori/internal/model"

// UserIsLogged returns a boolean indicating if the user is authenticated or not
func (c *Context) UserIsLogged() bool {
	_, exists := c.Get(model.ContextAccountKey)
	return exists
}

func (c *Context) GetAccount() *model.Account {
	if c.account == nil && c.UserIsLogged() {
		c.account = c.MustGet(model.ContextAccountKey).(*model.Account)
	}

	return c.account
}


code:
package context

import (
	"testing"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/stretchr/testify/require"
)

func TestUserIsLogged(t *testing.T) {
	t.Run("test user is logged", func(t *testing.T) {
		c := New()
		c.Set(model.ContextAccountKey, "test")
		require.True(t, c.UserIsLogged())
	})

	t.Run("test user is not logged", func(t *testing.T) {
		c := New()
		require.False(t, c.UserIsLogged())
	})
}

func TestGetAccount(t *testing.T) {
	t.Run("test get account (logged in)", func(t *testing.T) {
		account := model.Account{
			Username: "shiori",
		}
		c := New()
		c.Set(model.ContextAccountKey, &account)
		require.Equal(t, account, *c.GetAccount())
	})

	t.Run("test get account (not logged in)", func(t *testing.T) {
		c := New()
		require.Nil(t, c.GetAccount())
	})
}


code:
package context

import (
	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/model"
)

// Context is a wrapper of gin.Context that contains authentication information
type Context struct {
	*gin.Context

	account *model.Account
}

// NewContextFromGin returns a new Context instance from gin.Context
func NewContextFromGin(c *gin.Context) *Context {
	return &Context{
		Context: c,
	}
}

// New returns a new Context instance
func New() *Context {
	return NewContextFromGin(&gin.Context{})
}


code:
package middleware

import (
	"net/http"
	"strings"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/http/context"
	"github.com/go-shiori/shiori/internal/http/response"
	"github.com/go-shiori/shiori/internal/model"
)

// AuthMiddleware provides basic authentication capabilities to all routes underneath
// its usage, only allowing authenticated users access and set a custom local context
// `account` with the account model for the logged in user.
func AuthMiddleware(deps *config.Dependencies) gin.HandlerFunc {
	return func(c *gin.Context) {
		token := getTokenFromHeader(c)
		if token == "" {
			token = getTokenFromCookie(c)
		}

		account, err := deps.Domains.Auth.CheckToken(c, token)
		if err != nil {
			return
		}

		c.Set(model.ContextAccountKey, account)
	}
}

// AuthenticationRequired provides a middleware that checks if the user is logged in, returning
// a 401 error if not.
func AuthenticationRequired() gin.HandlerFunc {
	return func(c *gin.Context) {
		ctx := context.NewContextFromGin(c)
		if !ctx.UserIsLogged() {
			response.SendError(c, http.StatusUnauthorized, nil)
			return
		}
	}
}

// getTokenFromHeader returns the token from the Authorization header, if any.
func getTokenFromHeader(c *gin.Context) string {
	authorization := c.GetHeader(model.AuthorizationHeader)
	if authorization == "" {
		return ""
	}

	authParts := strings.SplitN(authorization, " ", 2)
	if len(authParts) != 2 && authParts[0] != model.AuthorizationTokenType {
		return ""
	}

	return authParts[1]
}

// getTokenFromCookie returns the token from the token cookie, if any.
func getTokenFromCookie(c *gin.Context) string {
	cookie, err := c.Cookie("token")
	if err != nil {
		return ""
	}

	return cookie
}


code:
package middleware

import (
	"context"
	"net/http"
	"net/http/httptest"
	"testing"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/go-shiori/shiori/internal/testutil"
	"github.com/sirupsen/logrus"
	"github.com/stretchr/testify/require"
)

func TestAuthenticationRequiredMiddleware(t *testing.T) {
	t.Run("test unauthorized", func(t *testing.T) {
		g := testutil.NewGin()
		g.Use(AuthenticationRequired())
		w := testutil.PerformRequest(g, "GET", "/")
		require.Equal(t, http.StatusUnauthorized, w.Code)
	})

	t.Run("test authorized", func(t *testing.T) {
		g := testutil.NewGin()
		// Fake a logged in user in the context, which is the way the AuthMiddleware works.
		g.Use(func(ctx *gin.Context) {
			ctx.Set(model.ContextAccountKey, "test")
		})
		g.Use(AuthenticationRequired())
		g.GET("/", func(c *gin.Context) {
			c.Status(http.StatusOK)
		})
		w := testutil.PerformRequest(g, "GET", "/")
		require.Equal(t, http.StatusOK, w.Code)
	})
}

func TestAuthMiddleware(t *testing.T) {
	ctx := context.TODO()
	logger := logrus.New()
	_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
	middleware := AuthMiddleware(deps)

	t.Run("test no authorization method", func(t *testing.T) {
		w := httptest.NewRecorder()
		c, router := gin.CreateTestContext(w)
		req := httptest.NewRequest(http.MethodGet, "/", nil)
		router.Use(middleware)
		router.ServeHTTP(w, req)

		_, exists := c.Get("account")
		require.False(t, exists)
	})

	t.Run("test authorization header", func(t *testing.T) {
		account := model.Account{Username: "shiori"}
		token, err := deps.Domains.Auth.CreateTokenForAccount(&account, time.Now().Add(time.Minute))
		require.NoError(t, err)
		w := httptest.NewRecorder()
		c, _ := gin.CreateTestContext(w)
		c.Request, _ = http.NewRequest("GET", "/", nil)
		c.Request.Header.Set(model.AuthorizationHeader, model.AuthorizationTokenType+" "+token)
		middleware(c)
		_, exists := c.Get(model.ContextAccountKey)
		require.True(t, exists)
	})

	t.Run("test authorization cookie", func(t *testing.T) {
		account := model.Account{Username: "shiori"}
		token, err := deps.Domains.Auth.CreateTokenForAccount(&account, time.Now().Add(time.Minute))
		require.NoError(t, err)
		w := httptest.NewRecorder()
		c, _ := gin.CreateTestContext(w)
		c.Request, _ = http.NewRequest("GET", "/", nil)
		c.Request.AddCookie(&http.Cookie{
			Name:   "token",
			Value:  token,
			MaxAge: int(time.Now().Add(time.Minute).Unix()),
		})
		middleware(c)
		_, exists := c.Get(model.ContextAccountKey)
		require.True(t, exists)
	})
}


code:
package response

import (
	"github.com/gin-gonic/gin"
)

type Response struct {
	// Response payload
	// Ok if the response was successful or not
	Ok bool `json:"ok"`

	// Message the payload of the response, depending on the endpoint/response status
	Message interface{} `json:"message"`

	// ErrorParams parameters defined if the response is not successful to help client's debugging
	ErrorParams map[string]string `json:"error_params,omitempty"`

	// statusCode used for the http response status code
	statusCode int
}

func (m *Response) IsError() bool {
	return m.Ok
}

func (m *Response) Send(c *gin.Context) {
	c.Status(m.statusCode)
	c.JSON(m.statusCode, m)
}

func NewResponse(ok bool, message interface{}, errorParams map[string]string, statusCode int) *Response {
	return &Response{
		Ok:          ok,
		Message:     message,
		ErrorParams: errorParams,
		statusCode:  statusCode,
	}
}


code:
package response

import (
	"net/http"

	"github.com/gin-gonic/gin"
)

const internalServerErrorMessage = "Internal server error, please contact an administrator"

// New provides a shortcut to a successful response object
func New(ok bool, statusCode int, data interface{}) *Response {
	return NewResponse(ok, data, nil, statusCode)
}

// Send provides a shortcut to send a (potentially) successful response
func Send(ctx *gin.Context, statusCode int, data interface{}) {
	New(true, statusCode, data).Send(ctx)
}

// SendError provides a shortcut to send an unsuccessful response
func SendError(ctx *gin.Context, statusCode int, data interface{}) {
	New(false, statusCode, data).Send(ctx)
}

// SendErrorWithParams the same as above but for errors that require error parameters
func SendErrorWithParams(ctx *gin.Context, statusCode int, data interface{}, errorParams map[string]string) {
	NewResponse(false, data, errorParams, statusCode).Send(ctx)
}

// SendInternalServerError directly sends an internal server error response
func SendInternalServerError(ctx *gin.Context) {
	SendError(ctx, http.StatusInternalServerError, internalServerErrorMessage)
}


code:
package api_v1

import (
	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/http/middleware"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
)

type APIRoutes struct {
	logger       *logrus.Logger
	deps         *config.Dependencies
	loginHandler model.LegacyLoginHandler
}

func (r *APIRoutes) Setup(g *gin.RouterGroup) model.Routes {
	// Account API handles authentication in each route
	r.handle(g, "/auth", NewAuthAPIRoutes(r.logger, r.deps, r.loginHandler))

	// From here on, all routes require authentication
	g.Use(middleware.AuthenticationRequired())
	r.handle(g, "/bookmarks", NewBookmarksPIRoutes(r.logger, r.deps))
	r.handle(g, "/tags", NewTagsPIRoutes(r.logger, r.deps))

	return r
}

func (s *APIRoutes) handle(g *gin.RouterGroup, path string, routes model.Routes) {
	group := g.Group(path)
	routes.Setup(group)
}

func NewAPIRoutes(logger *logrus.Logger, deps *config.Dependencies, loginHandler model.LegacyLoginHandler) *APIRoutes {
	return &APIRoutes{
		logger:       logger,
		deps:         deps,
		loginHandler: loginHandler,
	}
}


code:
package api_v1

import (
	"fmt"
	"net/http"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/http/context"
	"github.com/go-shiori/shiori/internal/http/response"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
)

type AuthAPIRoutes struct {
	logger             *logrus.Logger
	deps               *config.Dependencies
	legacyLoginHandler model.LegacyLoginHandler
}

func (r *AuthAPIRoutes) Setup(group *gin.RouterGroup) model.Routes {
	group.GET("/me", r.meHandler)
	group.POST("/login", r.loginHandler)
	group.POST("/refresh", r.refreshHandler)
	group.PATCH("/account", r.settingsHandler)
	return r
}

type loginRequestPayload struct {
	Username   string `json:"username"    validate:"required"`
	Password   string `json:"password"    validate:"required"`
	RememberMe bool   `json:"remember_me"`
}

func (p *loginRequestPayload) IsValid() error {
	if p.Username == "" {
		return fmt.Errorf("username should not be empty")
	}
	if p.Password == "" {
		return fmt.Errorf("password should not be empty")
	}
	return nil
}

type loginResponseMessage struct {
	Token      string `json:"token"`
	SessionID  string `json:"session"` // Deprecated, used only for legacy APIs
	Expiration int64  `json:"expires"` // Deprecated, used only for legacy APIs
}

type settingRequestPayload struct {
	Config model.UserConfig `json:"config"`
}

// loginHandler godoc
//
//	@Summary	Login to an account using username and password
//	@Tags		Auth
//	@Accept		json
//	@Produce	json
//	@Param		payload	body		loginRequestPayload		false	"Login data"
//	@Success	200		{object}	loginResponseMessage	"Login successful"
//	@Failure	400		{object}	nil						"Invalid login data"
//	@Router		/api/v1/auth/login [post]
func (r *AuthAPIRoutes) loginHandler(c *gin.Context) {
	var payload loginRequestPayload
	if err := c.ShouldBindJSON(&payload); err != nil {
		response.SendInternalServerError(c)
		return
	}

	if err := payload.IsValid(); err != nil {
		response.SendError(c, http.StatusBadRequest, err.Error())
		return
	}

	account, err := r.deps.Domains.Auth.GetAccountFromCredentials(c, payload.Username, payload.Password)
	if err != nil {
		response.SendError(c, http.StatusBadRequest, err.Error())
		return
	}

	expiration := time.Now().Add(time.Hour)
	if payload.RememberMe {
		expiration = time.Now().Add(time.Hour * 24 * 30)
	}

	token, err := r.deps.Domains.Auth.CreateTokenForAccount(account, expiration)
	if err != nil {
		response.SendInternalServerError(c)
		return
	}

	sessionID, err := r.legacyLoginHandler(*account, time.Hour*24*30)
	if err != nil {
		r.logger.WithError(err).Error("failed execute legacy login handler")
		response.SendInternalServerError(c)
		return
	}

	responseMessage := loginResponseMessage{
		Token:      token,
		SessionID:  sessionID,
		Expiration: expiration.Unix(),
	}

	response.Send(c, http.StatusOK, responseMessage)
}

// refreshHandler godoc
//
//	@Summary					Refresh a token for an account
//	@Tags						Auth
//	@securityDefinitions.apikey	ApiKeyAuth
//	@Produce					json
//	@Success					200	{object}	loginResponseMessage	"Refresh successful"
//	@Failure					403	{object}	nil						"Token not provided/invalid"
//	@Router						/api/v1/auth/refresh [post]
func (r *AuthAPIRoutes) refreshHandler(c *gin.Context) {
	ctx := context.NewContextFromGin(c)
	if !ctx.UserIsLogged() {
		response.SendError(c, http.StatusForbidden, nil)
		return
	}

	expiration := time.Now().Add(time.Hour * 72)
	account, _ := c.Get(model.ContextAccountKey)
	token, err := r.deps.Domains.Auth.CreateTokenForAccount(account.(*model.Account), expiration)
	if err != nil {
		response.SendInternalServerError(c)
		return
	}

	responseMessage := loginResponseMessage{
		Token: token,
	}

	response.Send(c, http.StatusAccepted, responseMessage)
}

// meHandler godoc
//
//	@Summary					Get information for the current logged in user
//	@Tags						Auth
//	@securityDefinitions.apikey	ApiKeyAuth
//	@Produce					json
//	@Success					200	{object}	model.Account
//	@Failure					403	{object}	nil	"Token not provided/invalid"
//	@Router						/api/v1/auth/me [get]
func (r *AuthAPIRoutes) meHandler(c *gin.Context) {
	ctx := context.NewContextFromGin(c)
	if !ctx.UserIsLogged() {
		response.SendError(c, http.StatusForbidden, nil)
		return
	}

	response.Send(c, http.StatusOK, ctx.GetAccount())
}

// settingsHandler godoc
//
//	@Summary					Perform actions on the currently logged-in user.
//	@Tags						Auth
//	@securityDefinitions.apikey	ApiKeyAuth
//	@Param						payload	body	settingRequestPayload	false	"Config data"
//	@Produce					json
//	@Success					200	{object}	model.Account
//	@Failure					403	{object}	nil	"Token not provided/invalid"
//	@Router						/api/v1/auth/account [patch]
func (r *AuthAPIRoutes) settingsHandler(c *gin.Context) {
	ctx := context.NewContextFromGin(c)
	if !ctx.UserIsLogged() {
		response.SendError(c, http.StatusForbidden, nil)
	}
	var payload settingRequestPayload
	if err := c.ShouldBindJSON(&payload); err != nil {
		response.SendInternalServerError(c)
	}

	account := ctx.GetAccount()
	account.Config = payload.Config

	err := r.deps.Database.SaveAccountSettings(c, *account)
	if err != nil {
		response.SendInternalServerError(c)
	}

	response.Send(c, http.StatusOK, ctx.GetAccount())
}

func NewAuthAPIRoutes(logger *logrus.Logger, deps *config.Dependencies, loginHandler model.LegacyLoginHandler) *AuthAPIRoutes {
	return &AuthAPIRoutes{
		logger:             logger,
		deps:               deps,
		legacyLoginHandler: loginHandler,
	}
}


code:
package api_v1

import (
	"bytes"
	"context"
	"encoding/json"
	"net/http"
	"net/http/httptest"
	"testing"
	"time"

	"github.com/go-shiori/shiori/internal/http/middleware"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/go-shiori/shiori/internal/testutil"
	"github.com/sirupsen/logrus"
	"github.com/stretchr/testify/require"
)

func noopLegacyLoginHandler(_ model.Account, _ time.Duration) (string, error) {
	return "", nil
}

func TestAccountsRoute(t *testing.T) {
	logger := logrus.New()
	ctx := context.TODO()

	t.Run("login invalid", func(t *testing.T) {
		g := testutil.NewGin()
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
		router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
		router.Setup(g.Group("/"))
		w := httptest.NewRecorder()
		body := []byte(`{"username": "gopher"}`)
		req := httptest.NewRequest("POST", "/login", bytes.NewBuffer(body))
		g.ServeHTTP(w, req)

		require.Equal(t, 400, w.Code)
	})

	t.Run("login incorrect", func(t *testing.T) {
		g := testutil.NewGin()
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
		router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
		router.Setup(g.Group("/"))
		w := httptest.NewRecorder()
		body := []byte(`{"username": "gopher", "password": "shiori"}`)
		req := httptest.NewRequest("POST", "/login", bytes.NewBuffer(body))
		g.ServeHTTP(w, req)

		require.Equal(t, 400, w.Code)
	})

	t.Run("login correct", func(t *testing.T) {
		g := testutil.NewGin()
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
		router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
		router.Setup(g.Group("/"))

		// Create an account manually to test
		account := model.Account{
			Username: "shiori",
			Password: "gopher",
			Owner:    true,
		}
		require.NoError(t, deps.Database.SaveAccount(ctx, account))

		w := httptest.NewRecorder()
		body := []byte(`{"username": "shiori", "password": "gopher"}`)
		req := httptest.NewRequest("POST", "/login", bytes.NewBuffer(body))
		g.ServeHTTP(w, req)

		require.Equal(t, 200, w.Code)
	})

	t.Run("check /me (correct token)", func(t *testing.T) {
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)

		g := testutil.NewGin()
		g.Use(middleware.AuthMiddleware(deps))

		router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
		router.Setup(g.Group("/"))

		// Create an account manually to test
		account := model.Account{
			Username: "shiori",
			Password: "gopher",
			Owner:    true,
		}
		require.NoError(t, deps.Database.SaveAccount(ctx, account))

		token, err := deps.Domains.Auth.CreateTokenForAccount(&account, time.Now().Add(time.Minute))
		require.NoError(t, err)

		req := httptest.NewRequest("GET", "/me", nil)
		req.Header.Add("Authorization", "Bearer "+token)
		w := httptest.NewRecorder()
		g.ServeHTTP(w, req)

		require.Equal(t, 200, w.Code)
	})

	t.Run("check /me (incorrect token)", func(t *testing.T) {
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)

		g := testutil.NewGin()
		g.Use(middleware.AuthMiddleware(deps))

		router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
		router.Setup(g.Group("/"))

		req := httptest.NewRequest("GET", "/me", nil)
		w := httptest.NewRecorder()
		g.ServeHTTP(w, req)

		require.Equal(t, 403, w.Code)
	})
}

func TestLoginRequestPayload(t *testing.T) {
	// Test empty payload
	t.Run("test empty payload", func(t *testing.T) {
		payload := loginRequestPayload{}
		err := payload.IsValid()
		require.Error(t, err)
	})

	// Test empty username
	t.Run("test empty username", func(t *testing.T) {
		payload := loginRequestPayload{
			Password: "gopher",
		}
		err := payload.IsValid()
		require.Error(t, err)
	})

	// Test empty password
	t.Run("test empty password", func(t *testing.T) {
		payload := loginRequestPayload{
			Username: "shiori",
		}
		err := payload.IsValid()
		require.Error(t, err)
	})

	// Test valid payload
	t.Run("test valid payload", func(t *testing.T) {
		payload := loginRequestPayload{
			Username: "shiori",
			Password: "gopher",
		}
		err := payload.IsValid()
		require.NoError(t, err)
	})
}

func TestRefreshHandler(t *testing.T) {
	logger := logrus.New()
	ctx := context.TODO()
	g := testutil.NewGin()

	_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
	router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
	g.Use(middleware.AuthMiddleware(deps)) // Requires AuthMiddleware to manipulate context
	router.Setup(g.Group("/"))

	t.Run("empty headers", func(t *testing.T) {
		w := testutil.PerformRequest(g, "POST", "/refresh")
		require.Equal(t, http.StatusForbidden, w.Code)
	})

	t.Run("token invalid", func(t *testing.T) {
		w := testutil.PerformRequest(g, "POST", "/refresh")
		require.Equal(t, http.StatusForbidden, w.Code)
	})

	t.Run("token valid", func(t *testing.T) {
		token, err := deps.Domains.Auth.CreateTokenForAccount(&model.Account{
			Username: "shiori",
		}, time.Now().Add(time.Minute))
		require.NoError(t, err)

		w := testutil.PerformRequest(g, "POST", "/refresh", testutil.WithHeader(model.AuthorizationHeader, model.AuthorizationTokenType+" "+token))

		require.Equal(t, http.StatusAccepted, w.Code)
	})
}

func TestSettingsHandler(t *testing.T) {
	logger := logrus.New()
	ctx := context.TODO()
	g := testutil.NewGin()

	_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
	router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
	g.Use(middleware.AuthMiddleware(deps))
	router.Setup(g.Group("/"))

	t.Run("token valid", func(t *testing.T) {
		token, err := deps.Domains.Auth.CreateTokenForAccount(&model.Account{
			Username: "shiori",
		}, time.Now().Add(time.Minute))
		require.NoError(t, err)

		type settingRequestPayload struct {
			Config model.UserConfig `json:"config"`
		}
		payload := settingRequestPayload{
			Config: model.UserConfig{
				// add your configuration data here
			},
		}
		payloadJSON, err := json.Marshal(payload)
		if err != nil {
			logrus.Printf("problem")
		}

		w := testutil.PerformRequest(g, "PATCH", "/account", testutil.WithBody(string(payloadJSON)), testutil.WithHeader(model.AuthorizationHeader, model.AuthorizationTokenType+" "+token))

		require.Equal(t, http.StatusOK, w.Code)

	})

	t.Run("config not valid", func(t *testing.T) {
		token, err := deps.Domains.Auth.CreateTokenForAccount(&model.Account{
			Username: "shiori",
		}, time.Now().Add(time.Minute))
		require.NoError(t, err)

		w := testutil.PerformRequest(g, "PATCH", "/account", testutil.WithBody("notValidConfig"), testutil.WithHeader(model.AuthorizationHeader, model.AuthorizationTokenType+" "+token))

		require.Equal(t, http.StatusInternalServerError, w.Code)

	})
	t.Run("Test configure change in database", func(t *testing.T) {
		// Create a tmp database
		g := testutil.NewGin()
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
		router := NewAuthAPIRoutes(logger, deps, noopLegacyLoginHandler)
		g.Use(middleware.AuthMiddleware(deps))
		router.Setup(g.Group("/"))

		// Create an account manually to test
		account := model.Account{
			Username: "shiori",
			Password: "gopher",
			Owner:    true,
			Config: model.UserConfig{
				ShowId:        true,
				ListMode:      true,
				HideThumbnail: true,
				HideExcerpt:   true,
				NightMode:     true,
				KeepMetadata:  true,
				UseArchive:    true,
				CreateEbook:   true,
				MakePublic:    true,
			},
		}
		require.NoError(t, deps.Database.SaveAccount(ctx, account))

		// Get current user config
		user, _, err := deps.Database.GetAccount(ctx, "shiori")
		require.NoError(t, err)
		require.Equal(t, user.Config, account.Config)

		// Send Request to update config for user
		token, err := deps.Domains.Auth.CreateTokenForAccount(&user, time.Now().Add(time.Minute))
		require.NoError(t, err)

		payloadJSON := []byte(`{
			"config": {
			"ShowId": false,
			"ListMode": false,
			"HideThumbnail": false,
			"HideExcerpt": false,
			"NightMode": false,
			"KeepMetadata": false,
			"UseArchive": false,
			"CreateEbook": false,
			"MakePublic": false
			  }
			}`)

		w := httptest.NewRecorder()
		req := httptest.NewRequest(http.MethodPatch, "/account", bytes.NewBuffer(payloadJSON))
		req.Header.Set("Content-Type", "application/json")
		req.Header.Add("Authorization", "Bearer "+token)
		g.ServeHTTP(w, req)

		require.Equal(t, 200, w.Code)
		user, _, err = deps.Database.GetAccount(ctx, "shiori")

		require.NoError(t, err)
		require.NotEqual(t, user.Config, account.Config)

	})
}


code:
package api_v1

import (
	"fmt"
	"log"
	"net/http"
	"os"
	fp "path/filepath"
	"strconv"
	"sync"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/core"
	"github.com/go-shiori/shiori/internal/database"
	"github.com/go-shiori/shiori/internal/http/context"
	"github.com/go-shiori/shiori/internal/http/response"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
)

type BookmarksAPIRoutes struct {
	logger *logrus.Logger
	deps   *config.Dependencies
}

func (r *BookmarksAPIRoutes) Setup(g *gin.RouterGroup) model.Routes {
	g.GET("/", r.listHandler)
	g.PUT("/cache", r.updateCache)
	g.POST("/", r.createHandler)
	g.DELETE("/:id", r.deleteHandler)
	return r
}

type updateCachePayload struct {
	Ids           []int `json:"ids"    validate:"required"`
	KeepMetadata  bool  `json:"keep_metadata"`
	CreateArchive bool  `json:"create_archive"`
	CreateEbook   bool  `json:"create_ebook"`
	SkipExist     bool  `json:"skip_exist"`
}

func (p *updateCachePayload) IsValid() error {
	if len(p.Ids) == 0 {
		return fmt.Errorf("id should not be empty")
	}
	for _, id := range p.Ids {
		if id <= 0 {
			return fmt.Errorf("id should not be 0 or negative")
		}
	}
	return nil
}

func (r *BookmarksAPIRoutes) listHandler(c *gin.Context) {
	bookmarks, err := r.deps.Database.GetBookmarks(c, database.GetBookmarksOptions{})
	if err != nil {
		r.logger.WithError(err).Error("error getting bookmarks")
		response.SendInternalServerError(c)
		return
	}

	response.Send(c, 200, bookmarks)
}

type apiCreateBookmarkPayload struct {
	URL           string      `json:"url"`
	Title         string      `json:"title"`
	Excerpt       string      `json:"excerpt"`
	Tags          []model.Tag `json:"tags"`
	CreateArchive bool        `json:"create_archive"`
	MakePublic    int         `json:"public"`
	Async         bool        `json:"async"`
}

func (payload *apiCreateBookmarkPayload) ToBookmark() (*model.Bookmark, error) {
	bookmark := &model.Bookmark{
		URL:           payload.URL,
		Title:         payload.Title,
		Excerpt:       payload.Excerpt,
		Tags:          payload.Tags,
		Public:        payload.MakePublic,
		CreateArchive: payload.CreateArchive,
	}

	log.Println(bookmark.URL)

	var err error
	bookmark.URL, err = core.RemoveUTMParams(bookmark.URL)
	if err != nil {
		return nil, err
	}

	// Ensure title is not empty
	if bookmark.Title == "" {
		bookmark.Title = bookmark.URL
	}

	return bookmark, nil
}

func newAPICreateBookmarkPayload() *apiCreateBookmarkPayload {
	return &apiCreateBookmarkPayload{
		CreateArchive: false,
		Async:         true,
	}
}

func (r *BookmarksAPIRoutes) createHandler(c *gin.Context) {
	payload := newAPICreateBookmarkPayload()
	if err := c.ShouldBindJSON(&payload); err != nil {
		r.logger.WithError(err).Error("Error parsing payload")
		response.SendError(c, 400, "Couldn't understand request")
		return
	}

	bookmark, err := payload.ToBookmark()
	if err != nil {
		r.logger.WithError(err).Error("Error creating bookmark from request")
		response.SendError(c, 400, "Couldn't understand request parameters")
		return
	}

	results, err := r.deps.Database.SaveBookmarks(c, true, *bookmark)
	if err != nil || len(results) == 0 {
		r.logger.WithError(err).WithField("payload", payload).Error("Error creating bookmark")
		response.SendInternalServerError(c)
		return
	}

	book := results[0]

	if payload.Async {
		go func() {
			bookmark, err := r.deps.Domains.Archiver.DownloadBookmarkArchive(book)
			if err != nil {
				r.logger.WithError(err).Error("Error downloading bookmark")
				return
			}
			if _, err := r.deps.Database.SaveBookmarks(c, false, *bookmark); err != nil {
				r.logger.WithError(err).Error("Error saving bookmark")
			}
		}()
	} else {
		// Workaround. Download content after saving the bookmark so we have the proper database
		// id already set in the object regardless of the database engine.
		book, err := r.deps.Domains.Archiver.DownloadBookmarkArchive(book)
		if err != nil {
			r.logger.WithError(err).Error("Error downloading bookmark")
		} else if _, err := r.deps.Database.SaveBookmarks(c, false, *book); err != nil {
			r.logger.WithError(err).Error("Error saving bookmark")
		}
	}

	response.Send(c, 201, book)
}

func (r *BookmarksAPIRoutes) deleteHandler(c *gin.Context) {
	bookmarkIDParam, exists := c.Params.Get("id")
	if !exists {
		response.SendError(c, 400, "Incorrect bookmark ID")
		return
	}

	bookmarkID, err := strconv.Atoi(bookmarkIDParam)
	if err != nil {
		response.SendInternalServerError(c)
		return
	}

	_, found, err := r.deps.Database.GetBookmark(c, bookmarkID, "")
	if err != nil {
		response.SendError(c, 400, "Incorrect bookmark ID")
		return
	}

	if !found {
		response.SendError(c, 404, "Bookmark not found")
		return
	}

	if err := r.deps.Database.DeleteBookmarks(c, bookmarkID); err != nil {
		r.logger.WithError(err).Error("Error deleting bookmark")
		response.SendInternalServerError(c)
		return
	}

	response.Send(c, 200, "Bookmark deleted")
}

func NewBookmarksPIRoutes(logger *logrus.Logger, deps *config.Dependencies) *BookmarksAPIRoutes {
	return &BookmarksAPIRoutes{
		logger: logger,
		deps:   deps,
	}
}

// updateCache godoc
//
//	@Summary					Update Cache and Ebook on server.
//	@Tags						Auth
//	@securityDefinitions.apikey	ApiKeyAuth
//	@Param						payload	body	updateCachePayload	true "Update Cache Payload"`
//	@Produce					json
//	@Success					200	{object}	model.Bookmark
//	@Failure					403	{object}	nil	"Token not provided/invalid"
//	@Router						/api/v1/bookmarks/cache [put]
func (r *BookmarksAPIRoutes) updateCache(c *gin.Context) {
	ctx := context.NewContextFromGin(c)
	if !ctx.UserIsLogged() {
		response.SendError(c, http.StatusForbidden, nil)
		return
	}

	// Get server config
	logger := logrus.New()
	cfg := config.ParseServerConfiguration(ctx, logger)

	var payload updateCachePayload
	if err := c.ShouldBindJSON(&payload); err != nil {
		response.SendInternalServerError(c)
		return
	}

	if err := payload.IsValid(); err != nil {
		response.SendError(c, http.StatusBadRequest, err.Error())
		return
	}

	// send request to database and get bookmarks
	filter := database.GetBookmarksOptions{
		IDs:         payload.Ids,
		WithContent: true,
	}

	bookmarks, err := r.deps.Database.GetBookmarks(c, filter)
	if len(bookmarks) == 0 {
		r.logger.WithError(err).Error("Bookmark not found")
		response.SendError(c, 404, "Bookmark not found")
		return
	}

	if err != nil {
		r.logger.WithError(err).Error("error getting bookmakrs")
		response.SendInternalServerError(c)
		return
	}
	// TODO: limit request to 20

	// Fetch data from internet
	mx := sync.RWMutex{}
	wg := sync.WaitGroup{}
	chDone := make(chan struct{})
	chProblem := make(chan int, 10)
	semaphore := make(chan struct{}, 10)

	for i, book := range bookmarks {
		wg.Add(1)

		// Mark whether book will be archived or ebook generate request
		book.CreateArchive = payload.CreateArchive
		book.CreateEbook = payload.CreateEbook

		go func(i int, book model.Bookmark, keep_metadata bool) {
			// Make sure to finish the WG
			defer wg.Done()

			// Register goroutine to semaphore
			semaphore <- struct{}{}
			defer func() {
				<-semaphore
			}()

			// Download data from internet
			content, contentType, err := core.DownloadBookmark(book.URL)
			if err != nil {
				chProblem <- book.ID
				return
			}

			request := core.ProcessRequest{
				DataDir:     cfg.Storage.DataDir,
				Bookmark:    book,
				Content:     content,
				ContentType: contentType,
				KeepTitle:   keep_metadata,
				KeepExcerpt: keep_metadata,
			}

			if payload.SkipExist && book.CreateEbook {
				strID := strconv.Itoa(book.ID)
				ebookPath := fp.Join(request.DataDir, "ebook", strID+".epub")
				_, err = os.Stat(ebookPath)
				if err == nil {
					request.Bookmark.CreateEbook = false
					request.Bookmark.HasEbook = true
				}
			}

			book, _, err = core.ProcessBookmark(request)
			content.Close()

			if err != nil {
				chProblem <- book.ID
				return
			}

			// Update list of bookmarks
			mx.Lock()
			bookmarks[i] = book
			mx.Unlock()
		}(i, book, payload.KeepMetadata)
	}
	// Receive all problematic bookmarks
	idWithProblems := []int{}
	go func() {
		for {
			select {
			case <-chDone:
				return
			case id := <-chProblem:
				idWithProblems = append(idWithProblems, id)
			}
		}
	}()

	// Wait until all download finished
	wg.Wait()
	close(chDone)

	// Update database
	_, err = r.deps.Database.SaveBookmarks(ctx, false, bookmarks...)
	if err != nil {
		r.logger.WithError(err).Error("error update bookmakrs on deatabas")
		response.SendInternalServerError(c)
		return
	}

	response.Send(c, 200, bookmarks)
}


code:
package api_v1

import (
	"net/http"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/http/response"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
)

type TagsAPIRoutes struct {
	logger *logrus.Logger
	deps   *config.Dependencies
}

func (r *TagsAPIRoutes) Setup(g *gin.RouterGroup) model.Routes {
	g.GET("/", r.listHandler)
	g.POST("/", r.createHandler)
	return r
}

// @Summary					List tags
// @Tags						Tags
// @securityDefinitions.apikey	ApiKeyAuth
// @Produce					json
// @Success					200	{object}	model.Tag	"List of tags"
// @Failure					403	{object}	nil			"Token not provided/invalid"
// @Router						/api/v1/tags [get]
func (r *TagsAPIRoutes) listHandler(c *gin.Context) {
	tags, err := r.deps.Database.GetTags(c)
	if err != nil {
		response.SendInternalServerError(c)
		return
	}

	response.Send(c, http.StatusOK, tags)
}

// @Summary					Create tag
// @Tags						Tags
// @securityDefinitions.apikey	ApiKeyAuth
// @Produce					json
// @Success					200	{object}	model.Tag	"Created tag"
// @Failure					400	{object}	nil			"Token not provided/invalid"
// @Failure					403	{object}	nil			"Token not provided/invalid"
// @Router						/api/v1/tags [post]
func (r *TagsAPIRoutes) createHandler(c *gin.Context) {
	var tag model.Tag
	if err := c.BindJSON(&tag); err != nil {
		response.SendError(c, http.StatusBadRequest, nil)
		return
	}

	err := r.deps.Database.CreateTags(c, tag)
	if err != nil {
		response.SendInternalServerError(c)
		return
	}

	response.Send(c, http.StatusCreated, nil)
}

func NewTagsPIRoutes(logger *logrus.Logger, deps *config.Dependencies) *TagsAPIRoutes {
	return &TagsAPIRoutes{
		logger: logger,
		deps:   deps,
	}
}


code:
package api_v1

import (
	"context"
	"net/http"
	"testing"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/testutil"
	"github.com/sirupsen/logrus"
	"github.com/stretchr/testify/require"
)

func TestTagList(t *testing.T) {
	logger := logrus.New()
	ctx := context.TODO()

	t.Run("empty tag list", func(t *testing.T) {
		g := gin.New()
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)
		router := NewTagsPIRoutes(logger, deps)
		router.Setup(g.Group("/"))
		w := testutil.PerformRequest(g, "GET", "/")
		require.Equal(t, http.StatusOK, w.Code)

		response, err := testutil.NewTestResponseFromReader(w.Body)
		require.NoError(t, err)

		response.AssertMessageIsEmptyList(t)
	})

	t.Run("return tags", func(t *testing.T) {
		ctx := context.TODO()

		g := gin.New()
		_, deps := testutil.GetTestConfigurationAndDependencies(t, ctx, logger)

		router := NewTagsPIRoutes(logger, deps)
		router.Setup(g.Group("/"))
		w := testutil.PerformRequest(g, "GET", "/")
		require.Equal(t, http.StatusOK, w.Code)

		response, err := testutil.NewTestResponseFromReader(w.Body)
		require.NoError(t, err)

		response.AssertMessageIsEmptyList(t)
	})
}


code:
package routes

import (
	"net/http"
	"strconv"

	fp "path/filepath"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/http/context"
	"github.com/go-shiori/shiori/internal/http/response"
	"github.com/go-shiori/shiori/internal/model"
	ws "github.com/go-shiori/shiori/internal/webserver"
	"github.com/sirupsen/logrus"
)

type BookmarkRoutes struct {
	logger *logrus.Logger
	deps   *config.Dependencies
}

func (r *BookmarkRoutes) Setup(group *gin.RouterGroup) model.Routes {
	//group.GET("/:id/archive", r.bookmarkArchiveHandler)
	//group.GET("/:id/content", r.bookmarkContentHandler)
	group.GET("/:id/ebook", r.bookmarkEbookHandler)
	return r
}

// func (r *BookmarkRoutes) bookmarkContentHandler(c *gin.Context) {
// 	ctx := context.NewContextFromGin(c)

// 	bookmarkIDParam, present := c.Params.Get("id")
// 	if !present {
// 		response.SendError(c, 400, "Invalid bookmark ID")
// 		return
// 	}

// 	bookmarkID, err := strconv.Atoi(bookmarkIDParam)
// 	if err != nil {
// 		r.logger.WithError(err).Error("error parsing bookmark ID parameter")
// 		response.SendInternalServerError(c)
// 		return
// 	}

// 	if bookmarkID == 0 {
// 		response.SendError(c, 404, nil)
// 		return
// 	}

// 	bookmark, found, err := r.deps.Database.GetBookmark(c, bookmarkID, "")
// 	if err != nil || !found {
// 		response.SendError(c, 404, nil)
// 		return
// 	}

// 	if bookmark.Public != 1 && !ctx.UserIsLogged() {
// 		response.SendError(c, http.StatusForbidden, nil)
// 		return
// 	}

// 	response.Send(c, 200, bookmark.Content)
// }

// func (r *BookmarkRoutes) bookmarkArchiveHandler(c *gin.Context) {}

func NewBookmarkRoutes(logger *logrus.Logger, deps *config.Dependencies) *BookmarkRoutes {
	return &BookmarkRoutes{
		logger: logger,
		deps:   deps,
	}
}

func (r *BookmarkRoutes) bookmarkEbookHandler(c *gin.Context) {
	ctx := context.NewContextFromGin(c)

	// Get server config
	logger := logrus.New()
	cfg := config.ParseServerConfiguration(ctx, logger)
	DataDir := cfg.Storage.DataDir

	bookmarkIDParam, present := c.Params.Get("id")
	if !present {
		response.SendError(c, http.StatusBadRequest, "Invalid bookmark ID")
		return
	}

	bookmarkID, err := strconv.Atoi(bookmarkIDParam)
	if err != nil {
		r.logger.WithError(err).Error("error parsing bookmark ID parameter")
		response.SendInternalServerError(c)
		return
	}

	if bookmarkID == 0 {
		response.SendError(c, http.StatusNotFound, nil)
		return
	}

	bookmark, found, err := r.deps.Database.GetBookmark(c, bookmarkID, "")
	if err != nil || !found {
		response.SendError(c, http.StatusNotFound, nil)
		return
	}

	if bookmark.Public != 1 && !ctx.UserIsLogged() {
		response.SendError(c, http.StatusUnauthorized, nil)
		return
	}

	ebookPath := fp.Join(DataDir, "ebook", bookmarkIDParam+".epub")
	if !ws.FileExists(ebookPath) {
		response.SendError(c, http.StatusNotFound, nil)
		return
	}
	filename := bookmark.Title + ".epub"
	c.FileAttachment(ebookPath, filename)
}


code:
package routes

import (
	"embed"
	"html/template"
	"net/http"
	"path/filepath"

	"github.com/gin-contrib/gzip"
	"github.com/gin-contrib/static"
	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/model"
	views "github.com/go-shiori/shiori/internal/view"
	"github.com/sirupsen/logrus"
)

type assetsFS struct {
	http.FileSystem
	logger *logrus.Logger
}

func (fs assetsFS) Exists(prefix string, path string) bool {
	_, err := fs.Open(path)
	if err != nil {
		logrus.WithError(err).WithField("path", path).WithField("prefix", prefix).Error("requested frontend file not found")
	}
	return err == nil
}

func (fs assetsFS) Open(name string) (http.File, error) {
	f, err := fs.FileSystem.Open(filepath.Join("assets", name))
	if err != nil {
		logrus.WithError(err).WithField("path", name).Error("requested frontend file not found")
	}
	return f, err
}

func newAssetsFS(logger *logrus.Logger, fs embed.FS) static.ServeFileSystem {
	return assetsFS{
		logger:     logger,
		FileSystem: http.FS(fs),
	}
}

type FrontendRoutes struct {
	logger *logrus.Logger
	cfg    *config.Config
}

func (r *FrontendRoutes) loadTemplates(e *gin.Engine) {
	tmpl, err := template.New("html").Delims("$$", "$$").ParseFS(views.Templates, "*.html")
	if err != nil {
		r.logger.WithError(err).Error("Failed to parse templates")
		return
	}
	e.SetHTMLTemplate(tmpl)
}

func (r *FrontendRoutes) Setup(e *gin.Engine) {
	group := e.Group("/")
	e.Delims("$$", "$$")
	r.loadTemplates(e)
	// e.LoadHTMLGlob("internal/view/*.html")
	group.Use(gzip.Gzip(gzip.DefaultCompression))
	group.GET("/login", func(ctx *gin.Context) {
		ctx.HTML(http.StatusOK, "login.html", gin.H{
			"RootPath": r.cfg.Http.RootPath,
			"Version":  model.BuildVersion,
		})
	})
	group.GET("/", func(ctx *gin.Context) {
		ctx.HTML(http.StatusOK, "index.html", gin.H{
			"RootPath": r.cfg.Http.RootPath,
			"Version":  model.BuildVersion,
		})
	})
	e.StaticFS("/assets", newAssetsFS(r.logger, views.Assets))
}

func NewFrontendRoutes(logger *logrus.Logger, cfg *config.Config) *FrontendRoutes {
	return &FrontendRoutes{
		logger: logger,
		cfg:    cfg,
	}
}


code:
package routes

import (
	"context"
	"net/http"
	"net/http/httptest"
	"testing"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/testutil"
	"github.com/sirupsen/logrus"
	"github.com/stretchr/testify/require"
)

func TestFrontendRoutes(t *testing.T) {
	logger := logrus.New()

	cfg, _ := testutil.GetTestConfigurationAndDependencies(t, context.Background(), logger)

	g := gin.Default()
	router := NewFrontendRoutes(logger, cfg)
	router.Setup(g)

	t.Run("/", func(t *testing.T) {
		w := httptest.NewRecorder()
		req, _ := http.NewRequest("GET", "/", nil)
		g.ServeHTTP(w, req)
		require.Equal(t, 200, w.Code)
	})

	t.Run("/login", func(t *testing.T) {
		w := httptest.NewRecorder()
		req, _ := http.NewRequest("GET", "/login", nil)
		g.ServeHTTP(w, req)
		require.Equal(t, 200, w.Code)
	})

	t.Run("/css/style.css", func(t *testing.T) {
		w := httptest.NewRecorder()
		req, _ := http.NewRequest("GET", "/assets/css/style.css", nil)
		g.ServeHTTP(w, req)
		require.Equal(t, 200, w.Code)
	})
}


code:
package routes

import (
	"net/http"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/go-shiori/shiori/internal/webserver"
	"github.com/gofrs/uuid/v5"
	"github.com/julienschmidt/httprouter"
	"github.com/pkg/errors"
	"github.com/sirupsen/logrus"
)

type LegacyAPIRoutes struct {
	logger        *logrus.Logger
	cfg           *config.Config
	deps          *config.Dependencies
	legacyHandler *webserver.Handler
}

func (r *LegacyAPIRoutes) convertHttprouteParams(params gin.Params) httprouter.Params {
	routerParams := httprouter.Params{}
	for _, p := range params {
		routerParams = append(routerParams, httprouter.Param{
			Key:   p.Key,
			Value: p.Value,
		})
	}
	return routerParams
}

func (r *LegacyAPIRoutes) handle(handler func(w http.ResponseWriter, r *http.Request, ps httprouter.Params)) gin.HandlerFunc {
	return func(ctx *gin.Context) {
		handler(ctx.Writer, ctx.Request, r.convertHttprouteParams(ctx.Params))
	}
}

func (r *LegacyAPIRoutes) HandleLogin(account model.Account, expTime time.Duration) (string, error) {
	// Create session ID
	sessionID, err := uuid.NewV4()
	if err != nil {
		return "", errors.Wrap(err, "failed to create session ID")
	}

	// Save session ID to cache
	strSessionID := sessionID.String()
	r.legacyHandler.SessionCache.Set(strSessionID, account, expTime)

	return strSessionID, nil
}

func (r *LegacyAPIRoutes) HandleLogout(c *gin.Context) error {
	sessionID := r.legacyHandler.GetSessionID(c.Request)
	r.legacyHandler.SessionCache.Delete(sessionID)
	return nil
}

func (r *LegacyAPIRoutes) Setup(g *gin.Engine) {
	r.legacyHandler = webserver.GetLegacyHandler(webserver.Config{
		DB:       r.deps.Database,
		DataDir:  r.cfg.Storage.DataDir,
		RootPath: r.cfg.Http.RootPath,
		Log:      false, // Already done by gin
	}, r.deps)
	r.legacyHandler.PrepareSessionCache()
	r.legacyHandler.PrepareTemplates()

	legacyGroup := g.Group("/")

	// Use a custom recovery handler to expose the errors that the frontend catch to redirect to
	// the login page and display messages.
	// This will be improved in the new API.
	legacyGroup.Use(gin.CustomRecovery(func(c *gin.Context, err any) {
		c.Data(http.StatusInternalServerError, "text/plain", []byte(err.(error).Error()))
	}))

	legacyGroup.POST("/api/logout", r.handle(r.legacyHandler.ApiLogout))

	// router.GET(jp("/bookmark/:id/thumb"), withLogging(hdl.serveThumbnailImage))
	legacyGroup.GET("/bookmark/:id/thumb", r.handle(r.legacyHandler.ServeThumbnailImage))
	// router.GET(jp("/bookmark/:id/content"), withLogging(hdl.serveBookmarkContent))
	legacyGroup.GET("/bookmark/:id/content", r.handle(r.legacyHandler.ServeBookmarkContent))
	// router.GET(jp("/bookmark/:id/archive/*filepath"), withLogging(hdl.serveBookmarkArchive))
	legacyGroup.GET("/bookmark/:id/archive/*filepath", r.handle(r.legacyHandler.ServeBookmarkArchive))
	// legacyGroup.GET("/bookmark/:id/archive/", r.handle(r.legacyHandler.ServeBookmarkArchive))

	// router.GET(jp("/api/tags"), withLogging(hdl.apiGetTags))
	legacyGroup.GET("/api/tags", r.handle(r.legacyHandler.ApiGetTags))
	// router.PUT(jp("/api/tag"), withLogging(hdl.apiRenameTag))
	legacyGroup.PUT("/api/tags", r.handle(r.legacyHandler.ApiRenameTag))
	// router.GET(jp("/api/bookmarks"), withLogging(hdl.apiGetBookmarks))
	legacyGroup.GET("/api/bookmarks", r.handle(r.legacyHandler.ApiGetBookmarks))
	// router.POST(jp("/api/bookmarks"), withLogging(hdl.apiInsertBookmark))
	legacyGroup.POST("/api/bookmarks", r.handle(r.legacyHandler.ApiInsertBookmark))
	// router.DELETE(jp("/api/bookmarks"), withLogging(hdl.apiDeleteBookmark))
	legacyGroup.DELETE("/api/bookmarks", r.handle(r.legacyHandler.ApiDeleteBookmark))
	// router.PUT(jp("/api/bookmarks"), withLogging(hdl.apiUpdateBookmark))
	legacyGroup.PUT("/api/bookmarks", r.handle(r.legacyHandler.ApiUpdateBookmark))
	// router.PUT(jp("/api/bookmarks/tags"), withLogging(hdl.apiUpdateBookmarkTags))
	legacyGroup.PUT("/api/bookmarks/tags", r.handle(r.legacyHandler.ApiUpdateBookmarkTags))
	// router.POST(jp("/api/bookmarks/ext"), withLogging(hdl.apiInsertViaExtension))
	legacyGroup.POST("/api/bookmarks/ext", r.handle(r.legacyHandler.ApiInsertViaExtension))
	// router.DELETE(jp("/api/bookmarks/ext"), withLogging(hdl.apiDeleteViaExtension))
	legacyGroup.DELETE("/api/bookmarks/ext", r.handle(r.legacyHandler.ApiDeleteViaExtension))

	// router.GET(jp("/api/accounts"), withLogging(hdl.apiGetAccounts))
	legacyGroup.GET("/api/accounts", r.handle(r.legacyHandler.ApiGetAccounts))
	// router.PUT(jp("/api/accounts"), withLogging(hdl.apiUpdateAccount))
	legacyGroup.PUT("/api/accounts", r.handle(r.legacyHandler.ApiUpdateAccount))
	// router.POST(jp("/api/accounts"), withLogging(hdl.apiInsertAccount))
	legacyGroup.POST("/api/accounts", r.handle(r.legacyHandler.ApiInsertAccount))
	// router.DELETE(jp("/api/accounts"), withLogging(hdl.apiDeleteAccount))
	legacyGroup.DELETE("/api/accounts", r.handle(r.legacyHandler.ApiDeleteAccount))
}

func NewLegacyAPIRoutes(logger *logrus.Logger, deps *config.Dependencies, cfg *config.Config) *LegacyAPIRoutes {
	return &LegacyAPIRoutes{
		logger: logger,
		cfg:    cfg,
		deps:   deps,
	}
}


code:
package routes

import (
	"github.com/gin-gonic/gin"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
	swaggerfiles "github.com/swaggo/files"
	ginSwagger "github.com/swaggo/gin-swagger"

	_ "github.com/go-shiori/shiori/docs/swagger"
)

type SwaggerAPIRoutes struct {
	logger *logrus.Logger
}

func (r *SwaggerAPIRoutes) Setup(g *gin.RouterGroup) model.Routes {
	g.Use(func(c *gin.Context) {
		if c.Request.URL.Path == "/swagger" || c.Request.URL.Path == "/swagger/" {
			c.Redirect(302, "/swagger/index.html")
			return
		}
	})
	g.GET("/*any", ginSwagger.WrapHandler(swaggerfiles.Handler))
	return r
}

func NewSwaggerAPIRoutes(logger *logrus.Logger) *SwaggerAPIRoutes {
	return &SwaggerAPIRoutes{
		logger: logger,
	}
}