# Fitment Operational Guide

This document defines required developer actions for fitment lifecycle events.

It must align strictly with the existing MySQL schema in database: `distributor_inventory`.

Do NOT reference any non-existent columns or tables.

Use only the following real tables where applicable:

Core Tables:
- `products`
- `product_fitment`
- `product_fitment_raw`
- `product_fitment_context_gpt`
- `product_fitment_gpt_context`
- `fitment_evidence`
- `fitment_reconciliation`
- `fitment_resolved`
- `fitment_review_queue`
- `fitment_overrides`
- `fitment_llm_audit_log`

Distributor Source Tables:
- `turn14_fitment_raw`
- `turn14_items`
- `motorstate_items`

## Section 1 - When A New Product Is Imported

**Trigger:**  
A new row is inserted into `products`.

Required actions:

1. Confirm `products` fields:
   - `id`
   - `product_id`
   - `product_title`
   - `product_description_vendor`
   - `is_universal`
   - `vendor_id`
   - `vendor`

2. If distributor fitment exists (Turn14):

   Insert deterministic rows into `product_fitment`:

   ```sql
   INSERT INTO product_fitment (
       distributor,
       distributor_sku,
       internal_product_id,
       year_from,
       year_to,
       make,
       model,
       fitment_status,
       confidence,
       source_column
   )
   SELECT
       'turn14',
       distributor_sku,
       internal_product_id,
       year_from,
       year_to,
       make,
       model,
       'fits',
       'explicit',
       'turn14_fitment_raw'
   FROM turn14_fitment_raw
   WHERE internal_product_id = :product_id;
   ```

3. If Motorstate description contains potential fitment text:

   Insert raw extraction row:

   ```sql
   INSERT INTO product_fitment_raw (
       distributor,
       distributor_sku,
       internal_product_id,
       source_columns,
       raw_text,
       extracted_json,
       requires_review
   )
   VALUES (
       'motorstate',
       :part_number,
       :internal_product_id,
       JSON_ARRAY('product_description'),
       :product_description,
       JSON_OBJECT(),
       1
   );
   ```

4. Run LLM extraction to populate:
   - `product_fitment_raw.extracted_json`
   - `product_fitment_raw.gpt_model`
   - `product_fitment_raw.requires_review = 0`

5. Insert evidence rows into `fitment_evidence` using `extracted_json`.

## Section 2 - When Distributor Fitment Changes

**Trigger:**  
New rows inserted into `turn14_fitment_raw`  
OR distributor product record updated.

Required actions:

1. Delete existing deterministic rows:

   ```sql
   DELETE FROM product_fitment
   WHERE internal_product_id = :product_id
   AND distributor = 'turn14';
   ```

2. Re-run deterministic `INSERT` from `turn14_fitment_raw`.

3. If description changed:  
   Delete existing GPT raw rows:

   ```sql
   DELETE FROM product_fitment_raw
   WHERE internal_product_id = :product_id
   AND distributor = 'motorstate';
   ```

4. Re-run LLM extraction.

## Section 3 - Reconciliation Pipeline

After evidence exists:

1. Build reconciliation rows:

   ```sql
   INSERT INTO fitment_reconciliation (
       run_id,
       internal_product_id,
       year_from,
       year_to,
       make,
       model,
       sources_present,
       sources_agree,
       agreement_score,
       confidence_adjustment,
       reconciliation_status
   )
   VALUES (...);
   ```

2. Insert resolved runtime rows:

   ```sql
   INSERT INTO fitment_resolved (
       product_id,
       year,
       make,
       model,
       final_confidence,
       confidence_band,
       evidence_count,
       sources_json,
       evidence_ids_json,
       resolution_reason,
       override_flag
   )
   VALUES (...);
   ```

Confidence band mapping:
- `>= .95` `CERTAIN`
- `>= .85` `HIGH`
- `>= .70` `MEDIUM`
- else `REVIEW`

## Section 4 - When Product Description Changes

**Trigger:**  
`products.product_description_vendor` changes.

Required actions:

1. Mark universal guidance dirty (if applicable):

   ```sql
   UPDATE products
   SET universal_guidance_dirty = 1
   WHERE id = :product_id
   AND is_universal = 1;
   ```

2. Delete old GPT extraction rows:

   ```sql
   DELETE FROM product_fitment_raw
   WHERE internal_product_id = :product_id;
   ```

3. Re-run extraction + reconciliation.

## Section 5 - Universal Guidance Lifecycle

Universal guidance fields:

- `products.universal_guidance_gpt`
- `products.universal_guidance_generated_at`
- `products.universal_guidance_model`
- `products.universal_guidance_source_hash`
- `products.universal_guidance_dirty`

When:

`is_universal = 1`  
AND `universal_guidance_dirty = 1`

System must:

1. Regenerate guidance via LLM.
2. Update:

   ```sql
   UPDATE products
   SET
       universal_guidance_gpt = :html,
       universal_guidance_generated_at = NOW(),
       universal_guidance_model = :model,
       universal_guidance_source_hash = :hash,
       universal_guidance_dirty = 0
   WHERE id = :product_id;
   ```

## Section 6 - Regression Validation

Before deployment:

1. Lint runtime file:

   ```bash
   php -l fitment/runtime/resolve_fitment.php
   ```

2. Run HTTP regression:

   ```bash
   ./genie/fitment_http_regression_tests.sh
   ```

3. Validate:
   - Deterministic fits return expected status
   - Unknown responses remain valid JSON
   - Universal guidance appears only when:
     - `status = 'unknown'`
     - AND `products.is_universal = 1`
   - No 500 errors

## Strict Rules

- Do not reference non-existent tables.
- Do not invent columns.
- Do not introduce new runtime fields.
- Explanation is appended HTML only.
- Fitment engine remains deterministic-first.