spsays-bff/internal/repository/shorturl_repo.go

package repository

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/prabhupadasays/spsays-bff/pkg/shorturl"
)

// ShortenedURLRow mirrors the analytics-relevant columns of shortened_urls.
type ShortenedURLRow struct {
	ID         int64
	Code       string
	LongURL    string
	Disabled   bool
	ExpiresAt  *time.Time
	ClickCount int64
}

// ShortURLRepo wraps Postgres CRUD for the shortener.
type ShortURLRepo struct {
	pg *Postgres
}

func NewShortURLRepo(pg *Postgres) *ShortURLRepo {
	return &ShortURLRepo{pg: pg}
}

// Insert persists a new short URL. Returns the new row's id. If a unique
// violation occurs (race: another worker already inserted this code) the
// caller can detect via IsUniqueViolation(err).
func (r *ShortURLRepo) Insert(
	ctx context.Context,
	code string,
	payload shorturl.PendingPayload,
	parsed shorturl.Parsed,
) (int64, error) {
	const q = `
		INSERT INTO shortened_urls (
			code, long_url,
			path, content_type, book_code, division, chapter, verse, slug,
			has_highlight, highlight_param,
			creator_ip, creator_ua, creator_referer, creator_country,
			created_at
		) VALUES (
			$1, $2,
			$3, $4, $5, $6, $7, $8, $9,
			$10, $11,
			NULLIF($12,'')::inet, NULLIF($13,''), NULLIF($14,''), NULLIF($15,''),
			$16
		)
		RETURNING id
	`
	var id int64
	err := r.pg.Pool.QueryRow(ctx, q,
		code, payload.URL,
		parsed.Path, parsed.ContentType,
		nullableStr(parsed.BookCode),
		nullableStr(parsed.Division),
		nullableStr(parsed.Chapter),
		nullableStr(parsed.Verse),
		nullableStr(parsed.Slug),
		parsed.HasHighlight, nullableStr(parsed.HighlightParam),
		payload.CreatorIP, payload.CreatorUA, payload.CreatorReferer, payload.CreatorCountry,
		payload.CreatedAt,
	).Scan(&id)
	if err != nil {
		return 0, fmt.Errorf("insert shortened_url: %w", err)
	}
	return id, nil
}

// GetByCode returns the row for code, or pgx.ErrNoRows if missing.
func (r *ShortURLRepo) GetByCode(ctx context.Context, code string) (*ShortenedURLRow, error) {
	const q = `
		SELECT id, code, long_url, disabled, expires_at, click_count
		FROM shortened_urls
		WHERE code = $1
		LIMIT 1
	`
	var row ShortenedURLRow
	err := r.pg.Pool.QueryRow(ctx, q, code).Scan(
		&row.ID, &row.Code, &row.LongURL, &row.Disabled, &row.ExpiresAt, &row.ClickCount,
	)
	if err != nil {
		return nil, err
	}
	return &row, nil
}

// RecordClick inserts a row in shortened_url_clicks and bumps the aggregate
// counters on the parent row in a single transaction. The parent lookup uses
// the code (not id) so this works even if the click worker is processing an
// event for a URL inserted by a different process.
func (r *ShortURLRepo) RecordClick(ctx context.Context, ev shorturl.ClickEvent) error {
	tx, err := r.pg.Pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback(ctx) }()

	var id int64
	err = tx.QueryRow(ctx, `SELECT id FROM shortened_urls WHERE code = $1`, ev.Code).Scan(&id)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			// Parent row hasn't landed in PG yet (still pending). Skip silently;
			// the click is lost. (Rare: only when a resolution and the parent
			// insert race within the worker drain interval.)
			return nil
		}
		return err
	}

	if _, err := tx.Exec(ctx, `
		INSERT INTO shortened_url_clicks (
			short_url_id, code, clicked_at, client_ip, client_ua, referer, country, is_bot
		) VALUES (
			$1, $2, $3, NULLIF($4,'')::inet, NULLIF($5,''), NULLIF($6,''), NULLIF($7,''), $8
		)`,
		id, ev.Code, ev.ClickedAt, ev.IP, ev.UA, ev.Referer, ev.Country, ev.IsBot,
	); err != nil {
		return err
	}

	if _, err := tx.Exec(ctx, `
		UPDATE shortened_urls
		   SET click_count       = click_count + 1,
		       human_click_count = human_click_count + CASE WHEN $2 THEN 0 ELSE 1 END,
		       last_clicked_at   = $1,
		       first_clicked_at  = COALESCE(first_clicked_at, $1)
		 WHERE id = $3
	`, ev.ClickedAt, ev.IsBot, id); err != nil {
		return err
	}

	return tx.Commit(ctx)
}

// IsUniqueViolation reports whether err is a Postgres unique constraint error.
func IsUniqueViolation(err error) bool {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		return pgErr.Code == "23505"
	}
	return false
}

func nullableStr(s string) any {
	if s == "" {
		return nil
	}
	return s
}