product_profile_ingestion/README.md

# Product Profile Ingestion

Data ingestion pipeline for an agricultural treatment assistant. It reads local product profile JSON files and label-chunk JSONL files, then loads them into the configured backends:

- **Weaviate** — stores the semantic `retrieval_summary` from product profiles in the `ProductProfile` collection for vector search.
- **Microsoft SQL Server** — stores structured product metadata in `products`, per-crop use rules in `product_uses`, and label text chunks in `label_chunks`.

The pipeline is **idempotent**: a hash manifest (`.processed_manifest.json`) tracks which files have already been ingested, so re-running skips unchanged files unless `--force` is used.

## Prerequisites

- Python 3.10+
- [Docker](https://www.docker.com/) (for local Weaviate)
- Microsoft SQL Server with the `products`, `product_uses`, and `label_chunks` tables already created
- [ODBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server) (e.g. ODBC Driver 17 or 18)

## Setup

1. **Clone the repository** and enter the project directory.

2. **Create a virtual environment** and install dependencies:

   ```bash
   python -m venv venv
   # Windows
   venv\Scripts\activate
   # macOS / Linux
   source venv/bin/activate

   pip install -r requirements.txt
   ```

3. **Configure environment variables**:

   ```bash
   cp .env.example .env
   ```

   Edit `.env` with your Gemini API key, Weaviate connection settings, and SQL Server credentials.

4. **Start Weaviate** (uses the Gemini `text2vec-google` vectorizer; requires v1.32+ for `taskType` support):

   ```bash
   docker compose up -d
   ```

5. **Create the Weaviate collection** (or let `ingest.py` create it on first run):

   ```bash
   python setup_weaviate.py
   ```

   The `ProductProfile` collection is configured to embed `retrieval_summary` with `gemini-embedding-001` and the `RETRIEVAL_DOCUMENT` task type. If you change embedding settings on an existing database, recreate the collection:

   ```bash
   python setup_weaviate.py --recreate
   python ingest.py --force
   ```

## Usage

Place product profile JSON files under `data/productProfiles/` and label-chunk JSONL files under `data/chunks/`, then run:

```bash
# Process new or changed profile and chunk files
python ingest.py

# Scan different directories
python ingest.py --profiles-dir path/to/profiles
python ingest.py --chunks-dir path/to/chunks

# Process a single file
python ingest.py --file data/productProfiles/ramin_sc_0916.json
python ingest.py --chunk-file data/chunks/delan_pro_16562.jsonl

# Reprocess everything, ignoring the manifest
python ingest.py --force
```

Profiles are processed before chunks in each run. Chunk rows reference `product_id` as a foreign key to `products`, so the matching product profile should be ingested first (the default run order handles this when both files are present).

Exit code `0` means all targeted files succeeded; `1` means at least one file failed (partial SQL transactions are rolled back per file).

## Configuration

| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `GEMINI_API_KEY` | Yes | — | API key for Weaviate's Gemini vectorizer |
| `WEAVIATE_HOST` | No | `localhost` | Weaviate HTTP host |
| `WEAVIATE_HTTP_PORT` | No | `8080` | Weaviate HTTP port |
| `WEAVIATE_GRPC_PORT` | No | `50051` | Weaviate gRPC port |
| `SQL_DRIVER` | Yes | — | ODBC driver name (e.g. `ODBC Driver 17 for SQL Server`) |
| `SQL_SERVER` | Yes | — | SQL Server hostname |
| `SQL_DATABASE` | Yes | — | Database name |
| `SQL_USERNAME` | Yes | — | SQL login |
| `SQL_PASSWORD` | Yes | — | SQL password |

## Product profile JSON format

Each file must include at least `product_id`, `product_name`, and `retrieval_summary`. See `data/productProfiles/ramin_sc_0916.json` for a full example with metadata arrays and `uses` rules.

**Weaviate** receives:

- `product_id`, `product_name`, `retrieval_summary`

**SQL `products`** receives global metadata (registration, manufacturer, action flags, spray volumes, JSON-encoded arrays for ingredients, crops, diseases, etc.).

**SQL `product_uses`** receives one row per entry in the `uses` array (crop, disease, dose ranges, treatment intervals, growth stages, and related fields).

## Label chunk JSONL format

Each `.jsonl` file contains one JSON object per line. See `data/chunks/delan_pro_16562.jsonl` for an example.

**SQL `label_chunks`** receives one row per line with:

| JSON field | SQL column | Notes |
|------------|------------|-------|
| `chunk_id` | `chunk_id` | Primary key |
| `product_id` | `product_id` | Foreign key to `products` |
| `product_name` | `product_name` | |
| `target_crops` | `target_crops` | Stored as JSON string via `json.dumps()` |
| `target_diseases` | `target_diseases` | Stored as JSON string via `json.dumps()` |
| `chunk_type` | `chunk_type` | |
| `chunk_text` | `chunk_text` | |

On re-ingestion, all existing `label_chunks` rows for the file's `product_id` are deleted and replaced with the fresh set from the file.

## Project layout

```
product_profile_ingestion/
├── data/
│   ├── productProfiles/    # Product profile JSON files (*.json)
│   └── chunks/            # Label-chunk JSONL files (*.jsonl)
├── ingest.py              # Main ingestion script
├── setup_weaviate.py      # ProductProfile collection schema (Gemini embeddings)
├── requirements.txt       # Python dependencies
├── docker-compose.yml     # Local Weaviate instance
├── .env.example           # Environment variable template
└── .processed_manifest.json  # Generated at runtime (gitignored)
```

## Dependencies

- [weaviate-client](https://weaviate.io/developers/weaviate/client-libraries/python) — Weaviate Python client
- [pyodbc](https://github.com/mkleehammer/pyodbc) — SQL Server connectivity
- [python-dotenv](https://github.com/theskumar/python-dotenv) — Load `.env` configuration