285 lines
8.1 KiB
Go
285 lines
8.1 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
|
|
"git.nakama.town/fmartingr/hako/internal/model"
|
|
"github.com/huandu/go-sqlbuilder"
|
|
)
|
|
|
|
// LinkStore handles database operations for links
|
|
type LinkStore struct {
|
|
readDB *sql.DB
|
|
writeDB *sql.DB
|
|
}
|
|
|
|
// NewLinkStore creates a new LinkStore
|
|
func NewLinkStore(readDB, writeDB *sql.DB) *LinkStore {
|
|
return &LinkStore{
|
|
readDB: readDB,
|
|
writeDB: writeDB,
|
|
}
|
|
}
|
|
|
|
// Create creates a new link
|
|
func (s *LinkStore) Create(ctx context.Context, link *model.Link) error {
|
|
ib := sqlbuilder.NewInsertBuilder()
|
|
ib.InsertInto("links")
|
|
ib.Cols("id", "url", "user_id", "total_size", "created_at", "updated_at")
|
|
ib.Values(link.ID, link.URL, link.UserID, link.TotalSize, link.CreatedAt.Format("2006-01-02 15:04:05"), link.UpdatedAt.Format("2006-01-02 15:04:05"))
|
|
|
|
query, args := ib.Build()
|
|
_, err := s.writeDB.ExecContext(ctx, query, args...)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to create link: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// GetByID retrieves a link by ID
|
|
func (s *LinkStore) GetByID(ctx context.Context, id string) (*model.Link, error) {
|
|
sb := sqlbuilder.NewSelectBuilder()
|
|
sb.Select("id", "url", "user_id", "total_size", "created_at", "updated_at")
|
|
sb.From("links")
|
|
sb.Where(sb.Equal("id", id))
|
|
|
|
query, args := sb.Build()
|
|
row := s.readDB.QueryRowContext(ctx, query, args...)
|
|
|
|
var link model.Link
|
|
var createdAt, updatedAt string
|
|
|
|
err := row.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt)
|
|
if err != nil {
|
|
if err == sql.ErrNoRows {
|
|
return nil, fmt.Errorf("link not found")
|
|
}
|
|
return nil, fmt.Errorf("failed to get link: %w", err)
|
|
}
|
|
|
|
// Parse timestamps
|
|
link.CreatedAt, _ = parseTimestamp(createdAt)
|
|
link.UpdatedAt, _ = parseTimestamp(updatedAt)
|
|
|
|
return &link, nil
|
|
}
|
|
|
|
// GetByURL retrieves a link by URL
|
|
func (s *LinkStore) GetByURL(ctx context.Context, url string) (*model.Link, error) {
|
|
sb := sqlbuilder.NewSelectBuilder()
|
|
sb.Select("id", "url", "user_id", "total_size", "created_at", "updated_at")
|
|
sb.From("links")
|
|
sb.Where(sb.Equal("url", url))
|
|
|
|
query, args := sb.Build()
|
|
row := s.readDB.QueryRowContext(ctx, query, args...)
|
|
|
|
var link model.Link
|
|
var createdAt, updatedAt string
|
|
|
|
err := row.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt)
|
|
if err != nil {
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil // Not found is not an error
|
|
}
|
|
return nil, fmt.Errorf("failed to get link by URL: %w", err)
|
|
}
|
|
|
|
// Parse timestamps
|
|
link.CreatedAt, _ = parseTimestamp(createdAt)
|
|
link.UpdatedAt, _ = parseTimestamp(updatedAt)
|
|
|
|
return &link, nil
|
|
}
|
|
|
|
// List retrieves links for a user with pagination
|
|
func (s *LinkStore) List(ctx context.Context, opts LinkListOptions) ([]*model.Link, error) {
|
|
opts.Defaults()
|
|
if err := opts.IsValid(); err != nil {
|
|
return nil, fmt.Errorf("invalid options: %w", err)
|
|
}
|
|
|
|
// If search query is provided, use raw SQL with FTS5 support
|
|
if opts.SearchQuery != "" {
|
|
return s.listWithSearch(ctx, opts)
|
|
}
|
|
|
|
sb := sqlbuilder.NewSelectBuilder()
|
|
sb.Select("l.id", "l.url", "l.user_id", "l.total_size", "l.created_at", "l.updated_at")
|
|
sb.From("links l")
|
|
|
|
// Join with link_categories if filtering by category
|
|
if opts.CategoryID != "" {
|
|
sb.JoinWithOption(sqlbuilder.InnerJoin, "link_categories lc", "l.id = lc.link_id")
|
|
sb.Where(sb.And(sb.Equal("l.user_id", opts.UserID), sb.Equal("lc.category_id", opts.CategoryID)))
|
|
} else {
|
|
sb.Where(sb.Equal("l.user_id", opts.UserID))
|
|
}
|
|
|
|
sb.OrderBy("l.created_at DESC")
|
|
sb.Limit(opts.Limit)
|
|
sb.Offset(opts.Offset)
|
|
|
|
query, args := sb.Build()
|
|
rows, err := s.readDB.QueryContext(ctx, query, args...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to list links: %w", err)
|
|
}
|
|
defer func() { _ = rows.Close() }()
|
|
|
|
var links []*model.Link
|
|
for rows.Next() {
|
|
var link model.Link
|
|
var createdAt, updatedAt string
|
|
|
|
if err := rows.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt); err != nil {
|
|
return nil, fmt.Errorf("failed to scan link: %w", err)
|
|
}
|
|
|
|
// Parse timestamps
|
|
link.CreatedAt, _ = parseTimestamp(createdAt)
|
|
link.UpdatedAt, _ = parseTimestamp(updatedAt)
|
|
|
|
links = append(links, &link)
|
|
}
|
|
|
|
return links, nil
|
|
}
|
|
|
|
// listWithSearch performs a search across URLs, titles, and content using FTS5
|
|
func (s *LinkStore) listWithSearch(ctx context.Context, opts LinkListOptions) ([]*model.Link, error) {
|
|
builder := newSearchBuilder()
|
|
builder.addCategoryFilter(opts.CategoryID)
|
|
builder.addSearchFilter(opts.SearchQuery, opts.UserID)
|
|
builder.addUserFilter(opts.UserID)
|
|
builder.addLabelsFilter(opts.LabelIDs)
|
|
|
|
query, args := builder.buildSelectQuery(opts.Limit, opts.Offset)
|
|
|
|
rows, err := s.readDB.QueryContext(ctx, query, args...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to list links with search: %w", err)
|
|
}
|
|
defer func() { _ = rows.Close() }()
|
|
|
|
var links []*model.Link
|
|
for rows.Next() {
|
|
var link model.Link
|
|
var createdAt, updatedAt string
|
|
|
|
if err := rows.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt); err != nil {
|
|
return nil, fmt.Errorf("failed to scan link: %w", err)
|
|
}
|
|
|
|
// Parse timestamps
|
|
link.CreatedAt, _ = parseTimestamp(createdAt)
|
|
link.UpdatedAt, _ = parseTimestamp(updatedAt)
|
|
|
|
links = append(links, &link)
|
|
}
|
|
|
|
return links, nil
|
|
}
|
|
|
|
// Count returns the total number of links for a user
|
|
func (s *LinkStore) Count(ctx context.Context, opts LinkCountOptions) (int, error) {
|
|
opts.Defaults()
|
|
if err := opts.IsValid(); err != nil {
|
|
return 0, fmt.Errorf("invalid options: %w", err)
|
|
}
|
|
|
|
// If search query is provided, use raw SQL with FTS5 support
|
|
if opts.SearchQuery != "" {
|
|
return s.countWithSearch(ctx, opts)
|
|
}
|
|
|
|
sb := sqlbuilder.NewSelectBuilder()
|
|
sb.Select("COUNT(DISTINCT l.id)")
|
|
sb.From("links l")
|
|
|
|
// Join with link_categories if filtering by category
|
|
if opts.CategoryID != "" {
|
|
sb.JoinWithOption(sqlbuilder.InnerJoin, "link_categories lc", "l.id = lc.link_id")
|
|
sb.Where(sb.And(sb.Equal("l.user_id", opts.UserID), sb.Equal("lc.category_id", opts.CategoryID)))
|
|
} else {
|
|
sb.Where(sb.Equal("l.user_id", opts.UserID))
|
|
}
|
|
|
|
query, args := sb.Build()
|
|
row := s.readDB.QueryRowContext(ctx, query, args...)
|
|
|
|
var count int
|
|
if err := row.Scan(&count); err != nil {
|
|
return 0, fmt.Errorf("failed to count links: %w", err)
|
|
}
|
|
|
|
return count, nil
|
|
}
|
|
|
|
// countWithSearch counts links matching the search query
|
|
func (s *LinkStore) countWithSearch(ctx context.Context, opts LinkCountOptions) (int, error) {
|
|
builder := newSearchBuilder()
|
|
builder.addCategoryFilter(opts.CategoryID)
|
|
builder.addSearchFilter(opts.SearchQuery, opts.UserID)
|
|
builder.addUserFilter(opts.UserID)
|
|
builder.addLabelsFilter(opts.LabelIDs)
|
|
|
|
query, args := builder.buildCountQuery()
|
|
|
|
row := s.readDB.QueryRowContext(ctx, query, args...)
|
|
|
|
var count int
|
|
if err := row.Scan(&count); err != nil {
|
|
return 0, fmt.Errorf("failed to count links with search: %w", err)
|
|
}
|
|
|
|
return count, nil
|
|
}
|
|
|
|
// UpdateTotalSize updates the total_size field for a link
|
|
func (s *LinkStore) UpdateTotalSize(ctx context.Context, linkID string, totalSize int64) error {
|
|
ub := sqlbuilder.NewUpdateBuilder()
|
|
ub.Update("links")
|
|
ub.Set(ub.Assign("total_size", totalSize))
|
|
ub.Where(ub.Equal("id", linkID))
|
|
|
|
query, args := ub.Build()
|
|
_, err := s.writeDB.ExecContext(ctx, query, args...)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to update total_size: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// RecalculateTotalSize recalculates and updates the total_size for a link based on all archive files
|
|
func (s *LinkStore) RecalculateTotalSize(ctx context.Context, linkID string, archiveFileStore *ArchiveFileStore) error {
|
|
totalSize, err := archiveFileStore.GetTotalSizeByLinkID(ctx, linkID)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to calculate total size: %w", err)
|
|
}
|
|
|
|
if err := s.UpdateTotalSize(ctx, linkID, totalSize); err != nil {
|
|
return fmt.Errorf("failed to update total size: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// Delete deletes a link and its associated archives
|
|
func (s *LinkStore) Delete(ctx context.Context, id string) error {
|
|
db := sqlbuilder.NewDeleteBuilder()
|
|
db.DeleteFrom("links")
|
|
db.Where(db.Equal("id", id))
|
|
|
|
query, args := db.Build()
|
|
_, err := s.writeDB.ExecContext(ctx, query, args...)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to delete link: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|