newsbot-api/internal/repository/article.go

package repository

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

	"git.jamestombleson.com/jtom38/newsbot-api/internal/entity"
	"github.com/huandu/go-sqlbuilder"
)

const (
	ArticleOrderByPublishDateDesc = "pubDate desc"
	ArticleOrderByPublishDateAsc  = "pubDate asc"
)

type ArticlesRepo interface {
	GetById(ctx context.Context, id int64) (entity.ArticleEntity, error)
	GetByUrl(ctx context.Context, url string) (entity.ArticleEntity, error)
	ListTop(ctx context.Context, limit int) ([]entity.ArticleEntity, error)
	ListByPage(ctx context.Context, page, limit int) ([]entity.ArticleEntity, error)
	ListByPublishDate(ctx context.Context, page, limit int, orderBy string) ([]entity.ArticleEntity, error)
	ListBySource(ctx context.Context, page, limit, sourceId int, orderBy string) ([]entity.ArticleEntity, error)
	Create(ctx context.Context, sourceId int64, tags, title, url, thumbnailUrl, description, authorName, authorImageUrl string, pubDate time.Time, isVideo bool) (int64, error)
	CreateFromEntity(ctx context.Context, entity entity.ArticleEntity) (int64, error)
}

type ArticleRepository struct {
	conn          *sql.DB
	defaultLimit  int
	defaultOffset int
}

func NewArticleRepository(conn *sql.DB) ArticleRepository {
	return ArticleRepository{
		conn:          conn,
		defaultLimit:  50,
		defaultOffset: 50,
	}
}

func (ar ArticleRepository) GetById(ctx context.Context, id int64) (entity.ArticleEntity, error) {
	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("*")
	builder.From("articles").Where(
		builder.E("id", id),
	)
	builder.Limit(1)

	query, args := builder.Build()
	rows, err := ar.conn.QueryContext(ctx, query, args...)
	if err != nil {
		return entity.ArticleEntity{}, err
	}

	data := ar.processRows(rows)
	if len(data) == 0 {
		return entity.ArticleEntity{}, errors.New(ErrUserNotFound)
	}

	return data[0], nil
}

func (ar ArticleRepository) GetByUrl(ctx context.Context, url string) (entity.ArticleEntity, error) {
	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("*")
	builder.From("articles").Where(
		builder.E("url", url),
	)
	builder.Limit(1)

	query, args := builder.Build()
	rows, err := ar.conn.QueryContext(ctx, query, args...)
	if err != nil {
		return entity.ArticleEntity{}, err
	}

	data := ar.processRows(rows)
	if len(data) == 0 {
		return entity.ArticleEntity{}, errors.New(ErrUserNotFound)
	}

	return data[0], nil
}

func (ar ArticleRepository) ListTop(ctx context.Context, limit int) ([]entity.ArticleEntity, error) {
	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("*")
	builder.From("articles")
	builder.Limit(limit)

	query, args := builder.Build()
	rows, err := ar.conn.QueryContext(ctx, query, args...)
	if err != nil {
		return []entity.ArticleEntity{}, err
	}

	data := ar.processRows(rows)
	if len(data) == 0 {
		return []entity.ArticleEntity{}, errors.New(ErrUserNotFound)
	}

	return data, nil
}

func (ar ArticleRepository) ListByPage(ctx context.Context, page, limit int) ([]entity.ArticleEntity, error) {
	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("*")
	builder.From("articles")
	builder.OrderBy(ArticleOrderByPublishDateDesc)
	builder.Offset(page * limit)
	builder.Limit(limit)

	query, args := builder.Build()
	rows, err := ar.conn.QueryContext(ctx, query, args...)
	if err != nil {
		return []entity.ArticleEntity{}, err
	}

	data := ar.processRows(rows)
	if len(data) == 0 {
		return []entity.ArticleEntity{}, errors.New(ErrUserNotFound)
	}

	return data, nil
}

func (ar ArticleRepository) ListByPublishDate(ctx context.Context, page, limit int, orderBy string) ([]entity.ArticleEntity, error) {
	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("*")
	builder.From("articles")
	if orderBy != "" {
		builder.OrderBy(orderBy)
	}
	builder.Offset(page * limit)
	builder.Limit(limit)

	query, args := builder.Build()
	rows, err := ar.conn.QueryContext(ctx, query, args...)
	if err != nil {
		return []entity.ArticleEntity{}, err
	}

	data := ar.processRows(rows)
	if len(data) == 0 {
		return []entity.ArticleEntity{}, errors.New(ErrUserNotFound)
	}
	return data, nil
}

func (ar ArticleRepository) ListBySource(ctx context.Context, page, limit, sourceId int, orderBy string) ([]entity.ArticleEntity, error) {
	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("*")
	builder.From("articles")
	builder.JoinWithOption("InnerJoin", "sources", "articles.sourceId=sources.Id")
	if orderBy != "" {
		builder.OrderBy(orderBy)
	}
	builder.Where(
		builder.Equal("SourceId", sourceId),
	)
	builder.Offset(50)
	builder.Limit(page * limit)

	query, args := builder.Build()
	rows, err := ar.conn.QueryContext(ctx, query, args...)
	if err != nil {
		return []entity.ArticleEntity{}, err
	}

	data := ar.processRows(rows)
	if len(data) == 0 {
		return []entity.ArticleEntity{}, errors.New(ErrUserNotFound)
	}
	return data, nil
}

func (ar ArticleRepository) Create(ctx context.Context, sourceId int64, tags, title, url, thumbnailUrl, description, authorName, authorImageUrl string, pubDate time.Time, isVideo bool) (int64, error) {
	dt := time.Now()
	queryBuilder := sqlbuilder.NewInsertBuilder()
	queryBuilder.InsertInto("articles")
	queryBuilder.Cols("UpdatedAt", "CreatedAt", "DeletedAt", "SourceId", "Tags", "Title", "Url", "PubDate", "IsVideo", "ThumbnailUrl", "Description", "AuthorName", "AuthorImageUrl")
	queryBuilder.Values(dt, dt, timeZero, sourceId, tags, title, url, pubDate, isVideo, thumbnailUrl, description, authorName, authorImageUrl)
	query, args := queryBuilder.Build()

	_, err := ar.conn.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, err
	}

	return 1, nil
}

func (ar ArticleRepository) CreateFromEntity(ctx context.Context, entity entity.ArticleEntity) (int64, error) {
	dt := time.Now()
	queryBuilder := sqlbuilder.NewInsertBuilder()
	queryBuilder.InsertInto("articles")
	queryBuilder.Cols("UpdatedAt", "CreatedAt", "DeletedAt", "SourceId", "Tags", "Title", "Url", "PubDate", "IsVideo", "ThumbnailUrl", "Description", "AuthorName", "AuthorImageUrl")
	queryBuilder.Values(dt, dt, timeZero, entity.SourceID, entity.Tags, entity.Title, entity.Url, entity.PubDate, entity.IsVideo, entity.Thumbnail, entity.Description, entity.AuthorName, entity.AuthorImageUrl)
	query, args := queryBuilder.Build()

	_, err := ar.conn.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, err
	}

	return 1, nil
}

func (ur ArticleRepository) processRows(rows *sql.Rows) []entity.ArticleEntity {
	items := []entity.ArticleEntity{}

	for rows.Next() {
		var id int64
		var createdAt time.Time
		var updatedAt time.Time
		var deletedAt time.Time
		var sourceId int64
		var tags string
		var title string
		var url string
		var pubDate time.Time
		var isVideo bool
		var thumbnail string
		var description string
		var authorName string
		var authorImageUrl string
		err := rows.Scan(
			&id, &createdAt, &updatedAt,
			&deletedAt, &sourceId, &tags,
			&title, &url, &pubDate,
			&isVideo, &thumbnail, &description,
			&authorName, &authorImageUrl)
		if err != nil {
			fmt.Println(err)
		}

		item := entity.ArticleEntity{
			ID:             id,
			CreatedAt:      createdAt,
			UpdatedAt:      updatedAt,
			DeletedAt:      deletedAt,
			SourceID:       sourceId,
			Tags:           tags,
			Title:          title,
			Url:            url,
			PubDate:        pubDate,
			IsVideo:        isVideo,
			Thumbnail:      thumbnail,
			Description:    description,
			AuthorName:     authorName,
			AuthorImageUrl: authorImageUrl,
		}

		items = append(items, item)
	}

	return items
}