dmitriygnatenko-v2/internal/repositories/article.go

package repositories

import (
	"context"
	"database/sql"

	sq "github.com/Masterminds/squirrel"

	"git.dmitriygnatenko.ru/dima/dmitriygnatenko-v2/internal/models"
)

const articleTableName = "article"

type ArticleRepository struct {
	db *sql.DB
}

func InitArticleRepository(db *sql.DB) *ArticleRepository {
	return &ArticleRepository{db: db}
}

func (a ArticleRepository) GetAllPreview(ctx context.Context) ([]models.ArticlePreview, error) {
	var res []models.ArticlePreview

	query, args, err := sq.Select("id", "url", "publish_time", "title", "preview_text", "image").
		From(articleTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"is_active": true}).
		OrderBy("publish_time DESC").
		ToSql()

	if err != nil {
		return nil, err
	}

	rows, err := a.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		row := models.ArticlePreview{}

		err = rows.Scan(&row.ID, &row.URL, &row.PublishTime, &row.Title, &row.PreviewText, &row.Image)
		if err != nil {
			return nil, err
		}

		res = append(res, row)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return res, nil
}

func (a ArticleRepository) GetAll(ctx context.Context) ([]models.Article, error) {
	var res []models.Article

	// nolint
	query, args, err := sq.Select("id", "url", "publish_time", "title", "image", "text", "preview_text", "meta_keywords", "meta_desc", "is_active").
		From(articleTableName).
		OrderBy("publish_time DESC").
		ToSql()

	rows, err := a.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		row := models.Article{}

		err = rows.Scan(&row.ID, &row.URL, &row.PublishTime, &row.Title, &row.Image, &row.Text, &row.PreviewText, &row.MetaKeywords, &row.MetaDescription, &row.IsActive)
		if err != nil {
			return nil, err
		}

		res = append(res, row)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return res, nil
}

func (a ArticleRepository) GetPreviewByTagID(ctx context.Context, tagID int) ([]models.ArticlePreview, error) {
	var res []models.ArticlePreview

	query := "SELECT a.id, a.url, a.publish_time, a.title, a.preview_text, a.image " +
		"FROM " + articleTableName + " a, " + articleTagTableName + " at " +
		"WHERE a.is_active = true AND at.article_id = a.id AND at.tag_id = $1 " +
		"ORDER BY a.publish_time DESC"

	rows, err := a.db.QueryContext(ctx, query, tagID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		row := models.ArticlePreview{}

		err = rows.Scan(&row.ID, &row.URL, &row.PublishTime, &row.Title, &row.PreviewText, &row.Image)
		if err != nil {
			return nil, err
		}

		res = append(res, row)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return res, nil
}

func (a ArticleRepository) GetByURL(ctx context.Context, url string) (*models.Article, error) {
	var res models.Article

	query, args, err := sq.Select("id", "url", "publish_time", "title", "image", "text", "preview_text", "meta_keywords", "meta_desc", "is_active").
		From(articleTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"url": url}).
		Limit(1).
		ToSql()

	if err != nil {
		return nil, err
	}

	err = a.db.QueryRowContext(ctx, query, args...).
		Scan(&res.ID, &res.URL, &res.PublishTime, &res.Title, &res.Image, &res.Text, &res.PreviewText, &res.MetaKeywords, &res.MetaDescription, &res.IsActive)

	if err != nil {
		return nil, err
	}

	return &res, nil
}

func (a ArticleRepository) GetByID(ctx context.Context, id int) (*models.Article, error) {
	var res models.Article

	query, args, err := sq.Select("id", "url", "publish_time", "title", "image", "text", "preview_text", "meta_keywords", "meta_desc", "is_active").
		From(articleTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"id": id}).
		Limit(1).
		ToSql()

	if err != nil {
		return nil, err
	}

	err = a.db.QueryRowContext(ctx, query, args...).
		Scan(&res.ID, &res.URL, &res.PublishTime, &res.Title, &res.Image, &res.Text, &res.PreviewText, &res.MetaKeywords, &res.MetaDescription, &res.IsActive)

	if err != nil {
		return nil, err
	}

	return &res, nil
}

func (a ArticleRepository) Add(ctx context.Context, m models.Article) (int, error) {
	query, args, err := sq.Insert(articleTableName).
		PlaceholderFormat(sq.Dollar).
		Columns("url", "publish_time", "title", "image", "text", "preview_text", "meta_keywords", "meta_desc", "is_active").
		Values(m.URL, m.PublishTime, m.Title, m.Image, m.Text, m.PreviewText, m.MetaKeywords, m.MetaDescription, m.IsActive).
		Suffix("RETURNING id").
		ToSql()

	if err != nil {
		return 0, err
	}

	var id int
	err = a.db.QueryRowContext(ctx, query, args...).Scan(&id)
	if err != nil {
		return 0, err
	}

	return id, nil
}

func (a ArticleRepository) Update(ctx context.Context, req models.Article) error {
	query, args, err := sq.Update(articleTableName).
		PlaceholderFormat(sq.Dollar).
		Set("url", req.URL).
		Set("publish_time", req.PublishTime).
		Set("title", req.Title).
		Set("image", req.Image).
		Set("text", req.Text).
		Set("preview_text", req.PreviewText).
		Set("meta_keywords", req.MetaKeywords).
		Set("meta_desc", req.MetaDescription).
		Set("is_active", req.IsActive).
		Where(sq.Eq{"id": req.ID}).
		ToSql()

	if err != nil {
		return err
	}

	_, err = a.db.ExecContext(ctx, query, args...)

	return err
}

func (a ArticleRepository) Delete(ctx context.Context, id int) error {
	query, args, err := sq.Delete(articleTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"id": id}).
		ToSql()

	if err != nil {
		return err
	}

	_, err = a.db.ExecContext(ctx, query, args...)

	return err
}