# SneakerPicks Feed Pipeline Plan v3

## Overview
Complete rewrite of the feed import pipeline to solve three core problems:
1. **Non-sneaker products** imported without filtering
2. **Size variants** stored as separate offers (duplicate PDPs)
3. **No cross-shop deduplication** (same sneaker = multiple products)

## Architecture

```
Feed Sources → Fetch → Filter → Normalize → Deduplicate → Upsert → Price History
                         ↓
                   Reject Log
```

## 1. Category-Based Filtering

### Database Changes
- Add `category TEXT` to `products` — stores the feed's product_type/google_product_category
- Add `product_type TEXT` to `products` — raw product_type from feed

### Filter Logic (at import time)
Products are **accepted** if ANY of these match:
- `product_type` or `google_product_category` contains sneaker patterns:
  - `sneaker`, `trainer`, `running shoe`, `sportschoenen`, `footwear > athletic`
  - `Apparel & Accessories > Shoes` (Google taxonomy level 1-2 only if sublevel = sneaker/athletic)
- Brand is a known sneaker brand AND name doesn't match exclusion patterns
- Fallback: name contains sneaker model identifiers (Air Max, Dunk, 574, Gel-Lyte, etc.)

Products are **rejected** if:
- Category clearly non-footwear: clothing, accessories, bags, home, fragrance
- Name matches clothing/accessory patterns: t-shirt, hoodie, jacket, etc.
- Brand is non-sneaker (Barracuda dress shoes, some Hogan models → review)

### Reject Logging
- Table `import_rejects` tracks: feed_id, external_id, name, brand, reason, created_at
- Admin can review and whitelist/blacklist

## 2. Size Deduplication

### Problem
Van Arendonk sends each size as a separate product entry with different external_id.
Result: "Mikakus 018 Wit/Blauw" appears 4x as separate offers.

### Solution
Group feed items by `(brand + normalized_name + shop)`:
- Aggregate all size variants into the `sizes` JSONB column on `product_offers`
- Format: `[{"size": "EU 42", "ean": "1234567890123", "url": "https://...", "price": 59.99}]`
- Use the **lowest price** among variants as the offer price
- Store ONE offer per product-per-shop

### Normalization for grouping
- Strip size info from product name (e.g., "Nike Dunk Low 42" → "Nike Dunk Low")
- Normalize whitespace, case
- Use brand + cleaned_name as group key

## 3. Cross-Shop Product Matching

### Match Strategy (priority order)
1. **EAN/GTIN match** — if two offers share an EAN → same product
2. **Brand + model + colorway fingerprint** — normalized and hashed
   - Extract model from name (remove brand prefix, size, colorway suffixes)
   - Normalize colorway (lowercase, strip parentheses)
   - `match_fingerprint = md5(brand_lower + "|" + model_lower + "|" + colorway_lower)`
3. **Fuzzy fallback** — for future implementation (not v3)

### Result
One `products` row with multiple `product_offers` from different feeds/shops.

## 4. Feed Manager

### `feeds` table config JSONB
```json
{
  "category_whitelist": ["sneaker", "trainer", "footwear"],
  "category_blacklist": ["clothing", "accessories"],
  "field_mappings": {
    "name": "title",
    "brand": "brand",
    "price": "sale_price|price",
    "ean": "gtin",
    "size": "size",
    "category": "product_type",
    "google_category": "google_product_category"
  },
  "provider_type": "webgains|tradetracker|awin"
}
```

### Sync Log Enhancement
`sync_logs` stores per-run: total_fetched, accepted, rejected, deduped, errors, duration.

## 5. Price History

On each sync:
- For every offer upserted, insert into `price_history(product_id, feed_id, price, recorded_at)`
- Only insert if price changed since last record (avoid bloat)
- This powers the PDP price chart

## 6. Implementation Plan

### Step 1: Schema Migration
```sql
ALTER TABLE products ADD COLUMN IF NOT EXISTS category TEXT;
ALTER TABLE products ADD COLUMN IF NOT EXISTS product_type TEXT;

CREATE TABLE IF NOT EXISTS import_rejects (
  id SERIAL PRIMARY KEY,
  feed_id INTEGER REFERENCES feeds(id),
  external_id VARCHAR(255),
  name TEXT,
  brand TEXT,
  category TEXT,
  reason TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_rejects_feed ON import_rejects(feed_id);
```

### Step 2: New importer `feed-sync-v3.py`
- Single script handles all providers (Webgains, TradeTracker, Awin)
- Modular: fetcher → filter → normalizer → deduper → upserter
- Idempotent: can re-run safely (upsert logic)

### Step 3: Re-import & Cleanup
- Run v3 for Van Arendonk with filtering + size dedup
- Delete non-sneaker products from DB
- Verify product count is sane

### Step 4: Schedule
- Cron job every 4 hours
- Each run: fetch all enabled feeds, filter, dedupe, upsert, log