385 lines
11 KiB
Go
385 lines
11 KiB
Go
|
package woocommerce
|
||
|
|
|
||
|
|
import (
|
||
|
|
"context"
|
||
|
|
"encoding/json"
|
||
|
|
"strings"
|
||
|
|
"time"
|
||
|
|
|
||
|
|
"github.com/google/uuid"
|
||
|
|
)
|
||
|
|
|
||
|
|
type OrderListFilter struct {
|
||
|
|
Status string
|
||
|
|
Email string
|
||
|
|
Since *time.Time
|
||
|
|
Limit int
|
||
|
|
Offset int
|
||
|
|
}
|
||
|
|
|
||
|
|
type ReviewListFilter struct {
|
||
|
|
Status string
|
||
|
|
ProductID int64
|
||
|
|
MinRating int
|
||
|
|
Limit int
|
||
|
|
Offset int
|
||
|
|
}
|
||
|
|
|
||
|
|
type OrderRow struct {
|
||
|
|
ID uuid.UUID `json:"id"`
|
||
|
|
ExternalID int64 `json:"external_id"`
|
||
|
|
Status string `json:"status"`
|
||
|
|
Currency string `json:"currency"`
|
||
|
|
Total *string `json:"total,omitempty"`
|
||
|
|
CustomerID *int64 `json:"customer_id,omitempty"`
|
||
|
|
CustomerEmail *string `json:"customer_email,omitempty"`
|
||
|
|
CustomerName *string `json:"customer_name,omitempty"`
|
||
|
|
OrderedAt *time.Time `json:"ordered_at,omitempty"`
|
||
|
|
SyncedAt time.Time `json:"synced_at"`
|
||
|
|
Payload json.RawMessage `json:"payload"`
|
||
|
|
}
|
||
|
|
|
||
|
|
type ReviewRow struct {
|
||
|
|
ID uuid.UUID `json:"id"`
|
||
|
|
ExternalID int64 `json:"external_id"`
|
||
|
|
ProductID *int64 `json:"product_id,omitempty"`
|
||
|
|
ProductName string `json:"product_name"`
|
||
|
|
Status string `json:"status"`
|
||
|
|
Reviewer string `json:"reviewer"`
|
||
|
|
ReviewerEmail string `json:"reviewer_email"`
|
||
|
|
Rating *int `json:"rating,omitempty"`
|
||
|
|
Review string `json:"review"`
|
||
|
|
ReviewedAt *time.Time `json:"reviewed_at,omitempty"`
|
||
|
|
SyncedAt time.Time `json:"synced_at"`
|
||
|
|
Payload json.RawMessage `json:"payload"`
|
||
|
|
}
|
||
|
|
|
||
|
|
type AudienceCustomer struct {
|
||
|
|
Email string `json:"email"`
|
||
|
|
Name string `json:"name,omitempty"`
|
||
|
|
}
|
||
|
|
|
||
|
|
type AudienceResult struct {
|
||
|
|
Customers []AudienceCustomer `json:"customers"`
|
||
|
|
Total int `json:"total"`
|
||
|
|
Note string `json:"note"`
|
||
|
|
}
|
||
|
|
|
||
|
|
func normalizeOrderListFilter(f OrderListFilter) OrderListFilter {
|
||
|
|
if f.Limit <= 0 {
|
||
|
|
f.Limit = 50
|
||
|
|
}
|
||
|
|
if f.Limit > 200 {
|
||
|
|
f.Limit = 200
|
||
|
|
}
|
||
|
|
if f.Offset < 0 {
|
||
|
|
f.Offset = 0
|
||
|
|
}
|
||
|
|
return f
|
||
|
|
}
|
||
|
|
|
||
|
|
func (s *Service) ListOrders(ctx context.Context, companyID uuid.UUID, f OrderListFilter) ([]OrderRow, int, error) {
|
||
|
|
f = normalizeOrderListFilter(f)
|
||
|
|
args := []any{companyID}
|
||
|
|
where := []string{"company_id = $1"}
|
||
|
|
n := 2
|
||
|
|
if status := strings.TrimSpace(f.Status); status != "" {
|
||
|
|
where = append(where, "status = $"+itoa(n))
|
||
|
|
args = append(args, status)
|
||
|
|
n++
|
||
|
|
}
|
||
|
|
if email := strings.TrimSpace(strings.ToLower(f.Email)); email != "" {
|
||
|
|
where = append(where, "lower(customer_email) = $"+itoa(n))
|
||
|
|
args = append(args, email)
|
||
|
|
n++
|
||
|
|
}
|
||
|
|
if f.Since != nil {
|
||
|
|
where = append(where, "ordered_at >= $"+itoa(n))
|
||
|
|
args = append(args, *f.Since)
|
||
|
|
n++
|
||
|
|
}
|
||
|
|
whereSQL := strings.Join(where, " AND ")
|
||
|
|
|
||
|
|
var total int
|
||
|
|
if err := s.Pool.QueryRow(ctx, `SELECT count(*) FROM woo_orders WHERE `+whereSQL, args...).Scan(&total); err != nil {
|
||
|
|
return nil, 0, err
|
||
|
|
}
|
||
|
|
|
||
|
|
args = append(args, f.Limit, f.Offset)
|
||
|
|
rows, err := s.Pool.Query(ctx, `
|
||
|
|
SELECT id, external_id, status, currency, total::text, customer_id, customer_email, customer_name,
|
||
|
|
ordered_at, synced_at, payload
|
||
|
|
FROM woo_orders
|
||
|
|
WHERE `+whereSQL+`
|
||
|
|
ORDER BY ordered_at DESC NULLS LAST, external_id DESC
|
||
|
|
LIMIT $`+itoa(n)+` OFFSET $`+itoa(n+1), args...)
|
||
|
|
if err != nil {
|
||
|
|
return nil, 0, err
|
||
|
|
}
|
||
|
|
defer rows.Close()
|
||
|
|
|
||
|
|
out := make([]OrderRow, 0)
|
||
|
|
for rows.Next() {
|
||
|
|
var row OrderRow
|
||
|
|
if err := rows.Scan(
|
||
|
|
&row.ID, &row.ExternalID, &row.Status, &row.Currency, &row.Total, &row.CustomerID,
|
||
|
|
&row.CustomerEmail, &row.CustomerName, &row.OrderedAt, &row.SyncedAt, &row.Payload,
|
||
|
|
); err != nil {
|
||
|
|
return nil, 0, err
|
||
|
|
}
|
||
|
|
out = append(out, row)
|
||
|
|
}
|
||
|
|
return out, total, rows.Err()
|
||
|
|
}
|
||
|
|
|
||
|
|
func (s *Service) ListReviews(ctx context.Context, companyID uuid.UUID, f ReviewListFilter) ([]ReviewRow, int, error) {
|
||
|
|
if f.Limit <= 0 {
|
||
|
|
f.Limit = 50
|
||
|
|
}
|
||
|
|
if f.Limit > 200 {
|
||
|
|
f.Limit = 200
|
||
|
|
}
|
||
|
|
if f.Offset < 0 {
|
||
|
|
f.Offset = 0
|
||
|
|
}
|
||
|
|
args := []any{companyID}
|
||
|
|
where := []string{"company_id = $1"}
|
||
|
|
n := 2
|
||
|
|
if status := strings.TrimSpace(f.Status); status != "" {
|
||
|
|
where = append(where, "status = $"+itoa(n))
|
||
|
|
args = append(args, status)
|
||
|
|
n++
|
||
|
|
}
|
||
|
|
if f.ProductID > 0 {
|
||
|
|
where = append(where, "product_id = $"+itoa(n))
|
||
|
|
args = append(args, f.ProductID)
|
||
|
|
n++
|
||
|
|
}
|
||
|
|
if f.MinRating > 0 {
|
||
|
|
where = append(where, "rating >= $"+itoa(n))
|
||
|
|
args = append(args, f.MinRating)
|
||
|
|
n++
|
||
|
|
}
|
||
|
|
whereSQL := strings.Join(where, " AND ")
|
||
|
|
|
||
|
|
var total int
|
||
|
|
if err := s.Pool.QueryRow(ctx, `SELECT count(*) FROM product_reviews WHERE `+whereSQL, args...).Scan(&total); err != nil {
|
||
|
|
return nil, 0, err
|
||
|
|
}
|
||
|
|
|
||
|
|
args = append(args, f.Limit, f.Offset)
|
||
|
|
rows, err := s.Pool.Query(ctx, `
|
||
|
|
SELECT id, external_id, product_id, product_name, status, reviewer, reviewer_email,
|
||
|
|
rating, review, reviewed_at, synced_at, payload
|
||
|
|
FROM product_reviews
|
||
|
|
WHERE `+whereSQL+`
|
||
|
|
ORDER BY reviewed_at DESC NULLS LAST, external_id DESC
|
||
|
|
LIMIT $`+itoa(n)+` OFFSET $`+itoa(n+1), args...)
|
||
|
|
if err != nil {
|
||
|
|
return nil, 0, err
|
||
|
|
}
|
||
|
|
defer rows.Close()
|
||
|
|
|
||
|
|
out := make([]ReviewRow, 0)
|
||
|
|
for rows.Next() {
|
||
|
|
var row ReviewRow
|
||
|
|
if err := rows.Scan(
|
||
|
|
&row.ID, &row.ExternalID, &row.ProductID, &row.ProductName, &row.Status, &row.Reviewer,
|
||
|
|
&row.ReviewerEmail, &row.Rating, &row.Review, &row.ReviewedAt, &row.SyncedAt, &row.Payload,
|
||
|
|
); err != nil {
|
||
|
|
return nil, 0, err
|
||
|
|
}
|
||
|
|
out = append(out, row)
|
||
|
|
}
|
||
|
|
return out, total, rows.Err()
|
||
|
|
}
|
||
|
|
|
||
|
|
// AudienceBoughtCategories returns distinct customers who bought category X
|
||
|
|
// and (optionally) did not buy category Y. Best-effort from synced orders:
|
||
|
|
// matches line-item categories JSON and/or local processed_products.category by SKU/product_id.
|
||
|
|
func (s *Service) AudienceBoughtCategories(ctx context.Context, companyID uuid.UUID, boughtCategory, notBoughtCategory string, limit int) (AudienceResult, error) {
|
||
|
|
boughtCategory = strings.TrimSpace(boughtCategory)
|
||
|
|
notBoughtCategory = strings.TrimSpace(notBoughtCategory)
|
||
|
|
if boughtCategory == "" {
|
||
|
|
return AudienceResult{Customers: []AudienceCustomer{}, Note: "bought_category is required"}, nil
|
||
|
|
}
|
||
|
|
if limit <= 0 {
|
||
|
|
limit = 500
|
||
|
|
}
|
||
|
|
if limit > 5000 {
|
||
|
|
limit = 5000
|
||
|
|
}
|
||
|
|
|
||
|
|
const note = "best-effort from synced Woo orders (line-item categories + processed_products by sku/product_id)"
|
||
|
|
|
||
|
|
rows, err := s.Pool.Query(ctx, `
|
||
|
|
WITH buyers AS (
|
||
|
|
SELECT DISTINCT lower(o.customer_email) AS email, COALESCE(o.customer_name, '') AS name
|
||
|
|
FROM woo_orders o
|
||
|
|
JOIN woo_order_items i ON i.order_id = o.id AND i.company_id = o.company_id
|
||
|
|
LEFT JOIN processed_products p
|
||
|
|
ON p.company_id = o.company_id
|
||
|
|
AND (
|
||
|
|
(i.sku <> '' AND p.product_id = i.sku)
|
||
|
|
OR (i.product_id IS NOT NULL AND p.product_id = i.product_id::text)
|
||
|
|
)
|
||
|
|
WHERE o.company_id = $1
|
||
|
|
AND o.customer_email IS NOT NULL AND o.customer_email <> ''
|
||
|
|
AND o.status IN ('completed', 'processing', 'on-hold')
|
||
|
|
AND (
|
||
|
|
EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM jsonb_array_elements(
|
||
|
|
CASE
|
||
|
|
WHEN jsonb_typeof(i.categories) = 'array' THEN i.categories
|
||
|
|
ELSE '[]'::jsonb
|
||
|
|
END
|
||
|
|
) cat(val)
|
||
|
|
WHERE lower(cat.val #>> '{}') = lower($2)
|
||
|
|
)
|
||
|
|
OR lower(COALESCE(p.category, '')) = lower($2)
|
||
|
|
)
|
||
|
|
),
|
||
|
|
excluded AS (
|
||
|
|
SELECT DISTINCT lower(o.customer_email) AS email
|
||
|
|
FROM woo_orders o
|
||
|
|
JOIN woo_order_items i ON i.order_id = o.id AND i.company_id = o.company_id
|
||
|
|
LEFT JOIN processed_products p
|
||
|
|
ON p.company_id = o.company_id
|
||
|
|
AND (
|
||
|
|
(i.sku <> '' AND p.product_id = i.sku)
|
||
|
|
OR (i.product_id IS NOT NULL AND p.product_id = i.product_id::text)
|
||
|
|
)
|
||
|
|
WHERE o.company_id = $1
|
||
|
|
AND $3 <> ''
|
||
|
|
AND o.customer_email IS NOT NULL AND o.customer_email <> ''
|
||
|
|
AND o.status IN ('completed', 'processing', 'on-hold')
|
||
|
|
AND (
|
||
|
|
EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM jsonb_array_elements(
|
||
|
|
CASE
|
||
|
|
WHEN jsonb_typeof(i.categories) = 'array' THEN i.categories
|
||
|
|
ELSE '[]'::jsonb
|
||
|
|
END
|
||
|
|
) cat(val)
|
||
|
|
WHERE lower(cat.val #>> '{}') = lower($3)
|
||
|
|
)
|
||
|
|
OR lower(COALESCE(p.category, '')) = lower($3)
|
||
|
|
)
|
||
|
|
)
|
||
|
|
SELECT b.email, b.name
|
||
|
|
FROM buyers b
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM excluded e WHERE e.email = b.email)
|
||
|
|
ORDER BY b.email
|
||
|
|
LIMIT $4`, companyID, boughtCategory, notBoughtCategory, limit)
|
||
|
|
if err != nil {
|
||
|
|
return AudienceResult{}, err
|
||
|
|
}
|
||
|
|
defer rows.Close()
|
||
|
|
|
||
|
|
out := make([]AudienceCustomer, 0)
|
||
|
|
for rows.Next() {
|
||
|
|
var c AudienceCustomer
|
||
|
|
if err := rows.Scan(&c.Email, &c.Name); err != nil {
|
||
|
|
return AudienceResult{}, err
|
||
|
|
}
|
||
|
|
out = append(out, c)
|
||
|
|
}
|
||
|
|
if err := rows.Err(); err != nil {
|
||
|
|
return AudienceResult{}, err
|
||
|
|
}
|
||
|
|
return AudienceResult{Customers: out, Total: len(out), Note: note}, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
// AudienceAnyOrdersExcept returns distinct customers with any qualifying Woo order,
|
||
|
|
// optionally excluding those who bought notBoughtCategory (best-effort category match).
|
||
|
|
func (s *Service) AudienceAnyOrdersExcept(ctx context.Context, companyID uuid.UUID, notBoughtCategory string, limit int) (AudienceResult, error) {
|
||
|
|
notBoughtCategory = strings.TrimSpace(notBoughtCategory)
|
||
|
|
if limit <= 0 {
|
||
|
|
limit = 500
|
||
|
|
}
|
||
|
|
if limit > 5000 {
|
||
|
|
limit = 5000
|
||
|
|
}
|
||
|
|
|
||
|
|
const note = "best-effort from synced Woo orders (any order, optional not_bought_category exclude)"
|
||
|
|
|
||
|
|
rows, err := s.Pool.Query(ctx, `
|
||
|
|
WITH buyers AS (
|
||
|
|
SELECT DISTINCT lower(o.customer_email) AS email, COALESCE(o.customer_name, '') AS name
|
||
|
|
FROM woo_orders o
|
||
|
|
WHERE o.company_id = $1
|
||
|
|
AND o.customer_email IS NOT NULL AND o.customer_email <> ''
|
||
|
|
AND o.status IN ('completed', 'processing', 'on-hold')
|
||
|
|
),
|
||
|
|
excluded AS (
|
||
|
|
SELECT DISTINCT lower(o.customer_email) AS email
|
||
|
|
FROM woo_orders o
|
||
|
|
JOIN woo_order_items i ON i.order_id = o.id AND i.company_id = o.company_id
|
||
|
|
LEFT JOIN processed_products p
|
||
|
|
ON p.company_id = o.company_id
|
||
|
|
AND (
|
||
|
|
(i.sku <> '' AND p.product_id = i.sku)
|
||
|
|
OR (i.product_id IS NOT NULL AND p.product_id = i.product_id::text)
|
||
|
|
)
|
||
|
|
WHERE o.company_id = $1
|
||
|
|
AND $2 <> ''
|
||
|
|
AND o.customer_email IS NOT NULL AND o.customer_email <> ''
|
||
|
|
AND o.status IN ('completed', 'processing', 'on-hold')
|
||
|
|
AND (
|
||
|
|
EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM jsonb_array_elements(
|
||
|
|
CASE
|
||
|
|
WHEN jsonb_typeof(i.categories) = 'array' THEN i.categories
|
||
|
|
ELSE '[]'::jsonb
|
||
|
|
END
|
||
|
|
) cat(val)
|
||
|
|
WHERE lower(cat.val #>> '{}') = lower($2)
|
||
|
|
)
|
||
|
|
OR lower(COALESCE(p.category, '')) = lower($2)
|
||
|
|
)
|
||
|
|
)
|
||
|
|
SELECT b.email, b.name
|
||
|
|
FROM buyers b
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM excluded e WHERE e.email = b.email)
|
||
|
|
ORDER BY b.email
|
||
|
|
LIMIT $3`, companyID, notBoughtCategory, limit)
|
||
|
|
if err != nil {
|
||
|
|
return AudienceResult{}, err
|
||
|
|
}
|
||
|
|
defer rows.Close()
|
||
|
|
|
||
|
|
out := make([]AudienceCustomer, 0)
|
||
|
|
for rows.Next() {
|
||
|
|
var c AudienceCustomer
|
||
|
|
if err := rows.Scan(&c.Email, &c.Name); err != nil {
|
||
|
|
return AudienceResult{}, err
|
||
|
|
}
|
||
|
|
out = append(out, c)
|
||
|
|
}
|
||
|
|
if err := rows.Err(); err != nil {
|
||
|
|
return AudienceResult{}, err
|
||
|
|
}
|
||
|
|
return AudienceResult{Customers: out, Total: len(out), Note: note}, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
func itoa(n int) string {
|
||
|
|
if n == 0 {
|
||
|
|
return "0"
|
||
|
|
}
|
||
|
|
const digits = "0123456789"
|
||
|
|
if n < 10 {
|
||
|
|
return digits[n : n+1]
|
||
|
|
}
|
||
|
|
var b [12]byte
|
||
|
|
i := len(b)
|
||
|
|
for n > 0 {
|
||
|
|
i--
|
||
|
|
b[i] = byte('0' + n%10)
|
||
|
|
n /= 10
|
||
|
|
}
|
||
|
|
return string(b[i:])
|
||
|
|
}
|