# Baskit product pipeline

Scrapes South African grocery retailers into per-store SQLite DBs, matches products
**across retailers by barcode**, and loads the unified catalogue into MySQL for the
web app.

The MVP seeds from a shared curated shopping list (`mvp_terms.py`, ~264
high-frequency search terms) rather than full-store crawls.

## Status

| Retailer | Platform | How it's scraped | Products | Barcode coverage |
|---|---|---|---|---|
| **Pick n Pay** | SAP Hybris/Spartacus | Public OCC JSON API via sitemaps (`pnp_scraper.py`) | ~34.5k | 98% |
| **Checkers** | Next.js + Sixty60 catalog (AWS WAF) | Playwright passes WAF, replays `get-products-filter` JSON (`checkers_scraper.py`) | ~12.5k | 100% |
| **Woolworths** | Constructor.io search (off Cloudflare) | Plain HTTP, no browser (`woolworths_scraper.py`) | ~5.6k | 100% |
| **SPAR** | SPAR2U (app-only) | Not built — no web endpoint | — | — |

Cross-retailer matcher: `baskit_match.py` → `data/baskit.db` + price-comparison CSVs
in `exports/`.

## Layout

```
pipeline/
  config.py             - .env loader (DB paths, MySQL, scrape tuning)
  run_pipeline.py       - orchestrator (run once or hourly loop)
  mvp_terms.py          - shared curated MVP search-term list
  pnp_scraper.py        - Pick n Pay (Hybris OCC API)
  checkers_scraper.py   - Checkers / Sixty60 (Playwright + catalog JSON)
  woolworths_scraper.py - Woolworths (Constructor.io, plain HTTP)
  baskit_match.py       - unified catalogue + cross-retailer barcode matcher
  load_mysql.py         - load baskit.db → MySQL (catalogue, mvp_catalogue)
  .env.example          - copy to .env and adjust
  Dockerfile            - Playwright base image for Checkers
  docker-compose.yml    - hourly worker service
  data/                 - SQLite DBs (docker volume)
  exports/              - JSONL / CSV dumps + price-comparison CSV
  requirements.txt
```

## Configuration (.env)

Copy the example and edit:

```powershell
cd C:\wamp64\www\baskit_worker\pipeline
copy .env.example .env
```

| Variable | Default | Purpose |
|---|---|---|
| `BASKIT_DATA_DIR` | `data` | SQLite working directory |
| `BASKIT_EXPORTS_DIR` | `exports` | CSV/JSONL output directory |
| `BASKIT_PNP_DB` | `data/pnp.db` | Pick n Pay SQLite path |
| `BASKIT_CHECKERS_DB` | `data/checkers.db` | Checkers SQLite path |
| `BASKIT_WOOLWORTHS_DB` | `data/woolworths.db` | Woolworths SQLite path |
| `BASKIT_DB` | `data/baskit.db` | Unified catalogue SQLite path |
| `BASKIT_MYSQL_HOST` | `mysql` | MySQL host (`mysql` in Compose; `127.0.0.1` for WAMP) |
| `BASKIT_MYSQL_PORT` | `3306` | MySQL port |
| `BASKIT_MYSQL_USER` | `root` | MySQL user |
| `BASKIT_MYSQL_PASSWORD` | `baskit` | MySQL password |
| `BASKIT_MYSQL_DB` | `baskit` | MySQL database name |
| `BASKIT_MYSQL_PUBLISH_PORT` | `3307` | Host port for bundled MySQL (Compose only) |
| `BASKIT_RETAILERS` | `checkers,woolworths` | Comma-separated scrapers to run |
| `BASKIT_REFRESH` | `true` | Re-queue done terms/seeds each cycle |
| `BASKIT_RUN_MYSQL_LOAD` | `true` | Load results into MySQL after match |
| `BASKIT_PIPELINE_INTERVAL_HOURS` | `1` | Hours between scheduled runs |
| `BASKIT_PER_TERM_CAP` | `100` | Max products per search term |
| `BASKIT_HEADLESS` | `true` | Headless Chromium for Checkers |

## Setup (local)

```powershell
cd C:\wamp64\www\baskit_worker\pipeline
py -3.13 -m venv .venv
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
python -m playwright install chromium   # needed only for Checkers
copy .env.example .env
# Edit .env — set BASKIT_MYSQL_HOST=127.0.0.1 for local (non-Docker) runs
```

## Run manually

```powershell
# Individual scrapers (resumable; --refresh re-fetches prices)
python checkers_scraper.py scrape --refresh
python woolworths_scraper.py scrape --refresh
python pnp_scraper.py fetch-sitemaps   # PnP only: seed from sitemaps first
python pnp_scraper.py scrape --refresh

# Build unified catalogue + exports
python baskit_match.py build
python baskit_match.py mvp               # -> exports/baskit_mvp_catalogue_*.csv
python baskit_match.py compare           # -> exports/baskit_price_comparison_*.csv
python baskit_match.py status

# Load into MySQL
python load_mysql.py

# Or run the full pipeline in one shot
python run_pipeline.py run
```

## Docker (hourly schedule)

The stack includes a **MySQL 8** service (`mysql`) plus the hourly **worker**.
MySQL data persists in the `mysql_data` volume. The worker waits for MySQL to
be healthy before starting.

```powershell
cd C:\wamp64\www\baskit_worker\pipeline
copy .env.example .env
# Defaults: bundled MySQL (host=mysql, password=baskit, DB=baskit)
# To use WAMP MySQL instead: set BASKIT_MYSQL_HOST=host.docker.internal
#   and remove/comment the BASKIT_MYSQL_HOST override in docker-compose.yml

docker compose build
docker compose up -d          # starts mysql + hourly worker
docker compose logs -f worker

# Connect from the Windows host (e.g. web app / phpMyAdmin)
#   host=127.0.0.1  port=3307  user=root  password=baskit  database=baskit

# One-off test run (no daemon)
docker compose run --rm worker python run_pipeline.py run
```

Volumes persist SQLite state (`data/`), CSV exports (`exports/`), and MySQL
data (`mysql_data`) across restarts.

## How matching works

`baskit_match.py` loads every retailer's flat `products` table into one
`catalogue` table, normalises each barcode (digits only, leading zeros stripped,
length ≥ 8), then joins on the normalised barcode.

- **Branded packaged goods** share a manufacturer EAN/GTIN across retailers.
- **Variable-weight / in-store items** (barcode starting with `2`) are excluded.
- **Private label** appears in only one retailer (expected).

`compare` emits one row per shared barcode with each retailer's price, min/max,
and spread. `mvp` selects the ~825-SKU capped catalogue per bucket.

## Refresh behaviour

Scrapers mark terms/seeds `done` after a successful fetch. On the next hourly
cycle, `BASKIT_REFRESH=true` (or `--refresh` on the CLI) resets `done` rows to
`pending` so current prices are re-fetched. Without refresh, a second run is a
no-op.

## Important caveats

- **Prices are per default store/region** for each retailer.
- **Checkers needs Chromium** (Playwright); Woolworths and PnP do not.
- **PnP is opt-in** for hourly runs (`BASKIT_RETAILERS`) — full sitemap scrape
  takes hours. Default schedule runs Checkers + Woolworths only.
- **`robots.txt`**: use modest `--delay` values; tune via `.env`.

## Pick n Pay (detail)

1. `fetch-sitemaps` — download 7 product sitemaps, populate seeds table.
2. `scrape` — hit Hybris OCC API per seed; state in `data/pnp.db`.

```powershell
python pnp_scraper.py fetch-sitemaps
python pnp_scraper.py scrape --refresh
python pnp_scraper.py export --csv
```