inventory-plus-plus/domains/platforms/etsy/store.go

package etsy

import (
	"context"
	"crypto/sha256"
	"errors"
	"fmt"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
)

type (
	EtsyUser struct {
		AcctID int64
		UserID int64
		ShopID int64
	}

	etsyAccessTokens struct {
		access  tokenAndExpiration
		refresh tokenAndExpiration
	}

	tokenAndExpiration struct {
		token      string
		expiration time.Time
	}

	oauth2Request struct {
		acctID     int64
		state      uuid.UUID
		expiration time.Time
		pkceCode   pkceCode
	}

	pkceCode struct {
		verifier  [32]byte
		challenge []byte
	}
)

func (p *Platform) GetUserPointerByAccountID(ctx context.Context, acctID int64) (*EtsyUser, error) {
	rows, err := p.db.Query(
		ctx,
		"SELECT user_id, shop_id FROM etsy_users WHERE account_id = @account_id",
		pgx.NamedArgs{
			"account_id": acctID,
		},
	)
	if err != nil {
		return nil, fmt.Errorf("failed to perform query: %w", err)
	}

	type Row struct {
		User_ID int64
		Shop_ID int64
	}

	r, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByNameLax[Row])
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("failed to scan row: %w", err)
	}

	return &EtsyUser{
		AcctID: acctID,
		UserID: r.User_ID,
		ShopID: r.Shop_ID,
	}, nil
}

func (p *Platform) saveNewEtsyUser(
	ctx context.Context,
	user EtsyUser,
	tokens etsyAccessTokens,
) (err error) {
	_, err = p.db.Exec(
		ctx,
		`
			WITH new_user (
				INSERT INTO etsy_users (
					account_id,
					user_id,
					shop_id
				)
				VALUES (
					@account_id,
					@user_id,
					@shop_id
				)
				RETURNING
					account_id,
					user_id,
					shop_id
			)
			INSERT INTO etsy_access_tokens (
				access_token,
				refresh_token,
				access_token_expiration,
				refresh_token_expiration
			)
			VALUE (
				@access_token,
				@refresh_token,
				@access_token_expiration,
				@refresh_token_expiration
			)
		`,
		pgx.NamedArgs{
			"account_id":               user.AcctID,
			"user_id":                  user.UserID,
			"shop_id":                  user.ShopID,
			"access_token":             tokens.access.token,
			"refresh_token":            tokens.refresh.token,
			"access_token_expiration":  tokens.access.expiration,
			"refresh_token_expiration": tokens.refresh.expiration,
		},
	)
	if err != nil {
		return fmt.Errorf("failed to insert new records: %w", err)
	}

	return nil
}

// TODO: clean these up on timer.
func (p *Platform) createNewOAuthRequest(ctx context.Context, acctID int64) (oauth2Request, error) {
	req := oauth2Request{
		acctID:     acctID,
		state:      uuid.New(),
		expiration: time.Now().UTC().Add(10 * time.Minute),
		pkceCode:   newPKCECode(),
	}

	_, err := p.db.Exec(
		ctx,
		`
			INSERT INTO etsy_oauth_requests (
				account_id,
				state,
				code_verifier,
				expiration
			)
			VALUES (
				@account_id,
				@state,
				@code_verifier,
				@expiration
			)
		`,
		pgx.NamedArgs{
			"account_id":    req.acctID,
			"state":         req.state[:],
			"code_verifier": req.pkceCode.verifier[:],
			"expiration":    req.expiration,
		},
	)
	if err != nil {
		return oauth2Request{}, fmt.Errorf("failed to insert record: %w", err)
	}

	return req, nil
}

func (p *Platform) getOauthRequest(ctx context.Context, state uuid.UUID) (req oauth2Request, ok bool, err error) {
	stateBytes := [16]byte(state)

	rows, err := p.db.Query(
		ctx,
		`
			SELECT
				account_id,
				code_verifier,
				expiration
			FROM
				etsy_oauth_requests
			WHERE
				state = @state
		`,
		pgx.NamedArgs{
			"state": stateBytes[:],
		},
	)
	if err != nil {
		return oauth2Request{}, false, fmt.Errorf("failed to perform query: %w", err)
	}

	type Row struct {
		Account_ID    int64
		Code_Verifier []byte
		Expiration    time.Time
	}

	r, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByNameLax[Row])
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return oauth2Request{}, false, nil
		}
	}

	if l := len(r.Code_Verifier); l != 32 {
		return oauth2Request{}, false, fmt.Errorf("code_verifier of unexpected length found: expected = 32, found = %d", l)
	}

	var verifier [32]byte
	copy(verifier[:], r.Code_Verifier)

	return oauth2Request{
		acctID:     r.Account_ID,
		state:      state,
		expiration: r.Expiration.UTC(),
		pkceCode: pkceCode{
			verifier:  verifier,
			challenge: generateCodeChallenge(verifier),
		},
	}, false, nil
}

func (p *Platform) InvalidateState(ctx context.Context, state string) error {
	stateUUID, err := uuid.Parse(state)
	if err != nil {
		return nil
	}

	return p.deleteOauthRequest(ctx, stateUUID)
}

func (p *Platform) deleteOauthRequest(ctx context.Context, state uuid.UUID) error {
	_, err := p.db.Exec(
		ctx,
		`
			DELETE FROM etsy_oauth_requests
			WHERE state = @state
		`,
		pgx.NamedArgs{
			"state": state,
		},
	)
	if err != nil {
		return fmt.Errorf("failed to execute query: %w", err)
	}

	return nil
}

func newPKCECode() pkceCode {
	var code pkceCode
	part1 := [16]byte(uuid.New())
	part2 := [16]byte(uuid.New())
	copy(code.verifier[0:16], part1[:])
	copy(code.verifier[16:32], part2[:])

	code.challenge = generateCodeChallenge(code.verifier)
	return code
}

func generateCodeChallenge(codeVerifier [32]byte) []byte {
	return generateSHA256Hash(codeVerifier[:])
}

func generateSHA256Hash(b []byte) []byte {
	h := sha256.New()
	h.Write(b)
	return h.Sum(nil)
}