homethings/internal/repositories/thing_image.go

package repositories

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

	sq "github.com/Masterminds/squirrel"

	"git.dmitriygnatenko.ru/dima/homethings/internal/models"
)

const (
	thingImageTableName = "thing_image"
)

type ThingImageRepository struct {
	db *sql.DB
}

func InitThingImageRepository(db *sql.DB) *ThingImageRepository {
	return &ThingImageRepository{db: db}
}

func (r ThingImageRepository) BeginTx(ctx context.Context, level sql.IsolationLevel) (*sql.Tx, error) {
	return r.db.BeginTx(ctx, &sql.TxOptions{Isolation: level})
}

func (r ThingImageRepository) CommitTx(tx *sql.Tx) error {
	if tx == nil {
		return errors.New("empty transaction")
	}

	return tx.Commit()
}

func (r ThingImageRepository) Add(ctx context.Context, req models.AddThingImageRequest, tx *sql.Tx) error {
	query, args, err := sq.Insert(thingImageTableName).
		PlaceholderFormat(sq.Dollar).
		Columns("thing_id", "image").
		Values(req.ThingID, req.Image).
		ToSql()

	if err != nil {
		return err
	}

	if tx == nil {
		_, err = r.db.ExecContext(ctx, query, args...)
	} else {
		_, err = tx.ExecContext(ctx, query, args...)
	}

	return err
}

func (r ThingImageRepository) Get(ctx context.Context, imageID int) (*models.Image, error) {
	query, args, err := sq.Select("id", "image", "thing_id", "created_at").
		From(thingImageTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"id": imageID}).
		ToSql()

	if err != nil {
		return nil, err
	}

	var res models.Image

	err = r.db.QueryRowContext(ctx, query, args...).
		Scan(&res.ID, &res.Image, &res.ThingID, &res.CreatedAt)

	if err != nil {
		return nil, err
	}

	return &res, nil
}

func (r ThingImageRepository) GetByThingID(ctx context.Context, thingID int) ([]models.Image, error) {
	var res []models.Image

	query, args, err := sq.Select("id", "image", "thing_id", "created_at").
		From(thingImageTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"thing_id": thingID}).
		OrderBy("created_at DESC").
		ToSql()

	if err != nil {
		return nil, err
	}

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

	for rows.Next() {
		resRow := models.Image{}

		err = rows.Scan(
			&resRow.ID,
			&resRow.Image,
			&resRow.ThingID,
			&resRow.CreatedAt,
		)
		if err != nil {
			return nil, err
		}

		res = append(res, resRow)
	}

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

	return res, nil

}

func (r ThingImageRepository) GetByPlaceID(ctx context.Context, placeID int) ([]models.Image, error) {
	var res []models.Image

	query := "WITH RECURSIVE cte (id, parent_id) AS (" +
		"SELECT id, parent_id " +
		"FROM " + placeTableName + " " +
		"WHERE id = $1 " +
		"UNION ALL " +
		"SELECT p.id, p.parent_id " +
		"FROM " + placeTableName + " p " +
		"INNER JOIN cte ON p.parent_id = cte.id " +
		")" +
		"SELECT ti.id, ti.image, ti.thing_id, ti.created_at " +
		"FROM cte, " + placeThingTableName + " pt, " + thingImageTableName + " ti " +
		"WHERE pt.place_id = cte.id AND pt.thing_id = ti.thing_id " +
		"ORDER BY ti.created_at DESC"

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

	for rows.Next() {
		resRow := models.Image{}

		err = rows.Scan(
			&resRow.ID,
			&resRow.Image,
			&resRow.ThingID,
			&resRow.CreatedAt,
		)
		if err != nil {
			return nil, err
		}

		res = append(res, resRow)
	}

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

	return res, nil
}

func (r ThingImageRepository) Delete(ctx context.Context, imageID int, tx *sql.Tx) error {
	query, args, err := sq.Delete(thingImageTableName).
		PlaceholderFormat(sq.Dollar).
		Where(sq.Eq{"id": imageID}).
		ToSql()

	if err != nil {
		return err
	}

	if tx == nil {
		_, err = r.db.ExecContext(ctx, query, args...)
	} else {
		_, err = tx.ExecContext(ctx, query, args...)
	}

	return err
}