API Filtering: gọi dữ liệu như dân sành cà phê

func parseFilters(q url.Values) [Filter {
    var filters [Filter
    for key, values := range q {
        switch {
        case strings.HasSuffix(key, "_gte"):
            field := strings.TrimSuffix(key, "_gte")
            filters = append(filters, Filter{field, ">=", parseFloat(values[0)})
        case strings.HasSuffix(key, "_lte"):
            field := strings.TrimSuffix(key, "_lte")
            filters = append(filters, Filter{field, "<=", parseFloat(values[0)})
        case strings.HasSuffix(key, "_in"):
            field := strings.TrimSuffix(key, "_in")
            filters = append(filters, Filter{field, "IN", strings.Split(values[0, ",")})
        case strings.HasSuffix(key, "_like"):
            field := strings.TrimSuffix(key, "_like")
            filters = append(filters, Filter{field, "LIKE", "%" + values[0 + "%"})
        default:
            // exact match
            filters = append(filters, Filter{key, "=", values[0})
        }
    }
    return filters
}

func applySort(db *gorm.DB, q url.Values) *gorm.DB {
    sortField := q.Get("sort")
    order := q.Get("order")

    if sortField == "" {
        return db.Order("created_at DESC") // default
    }

    // whitelist fields - đừng để client sort cột arbitrary!
    allowed := map[stringbool{
        "created_at": true, "price": true, "name": true, "updated_at": true,
    }
    if !allowed[sortField {
        return db // ignore invalid field
    }

    if order == "asc" {
        return db.Order(sortField + " ASC")
    }
    return db.Order(sortField + " DESC")
}

-- Index cột sort phổ biến để tránh full scan
CREATE INDEX idx_products_created_at ON products(created_at DESC);
CREATE INDEX idx_products_price ON products(price ASC);

func applyFieldSelection(db *gorm.DB, q url.Values) *gorm.DB {
    fields := q.Get("fields")
    if fields == "" {
        return db // return all columns
    }

    selected := strings.Split(fields, ",")
    allowed := map[stringbool{
        "id": true, "name": true, "price": true,
        "thumbnail": true, "status": true, "created_at": true,
    }

    var columns [string
    for _, f := range selected {
        if allowed[strings.TrimSpace(f) {
            columns = append(columns, strings.TrimSpace(f))
        }
    }

    if len(columns) == 0 {
        return db // fallback to all if invalid
    }
    return db.Select(columns)
}

func ListProducts(ctx context.Context, q url.Values) ([Product, int64, error) {
    db := db.WithContext(ctx).Model(&Product{})

    db = applyFilters(db, q)      // 1. Filter
    db = applySort(db, q)         // 2. Sort
    // 3. Paginate (SAU filter+sort)
    var total int64
    db.Count(&total)

    var products [Product
    offset, limit := parsePagination(q)
    db.Offset(offset).Limit(limit).Find(&products)

    return products, total, nil
}

func TestListProducts_FilterByStatus(t *testing.T) {
    // seed 3 active + 2 inactive
    seedProducts(t, 3, "active")
    seedProducts(t, 2, "inactive")

    req, _ := http.NewRequest("GET", "/api/products?status=active", nil)
    products, _, err := handler.ListProducts(req.Context(), req.URL.Query())

    require.NoError(t, err)
    assert.Len(t, products, 3)
}