# Scenario: Retail Inventory Intelligence

A retail inventory management scenario that tracks branch-level stock, prices, and competitor pricing intelligence — enabling price competitiveness analysis and stockout risk monitoring.

---

## Entity Relationship Overview

```text
Collections
  retail_inventory_intelligence  ←── all datasets, pipelines, codes, dashboards

Datasets
  retail_inventory         ← branch-level inventory with pricing and stock metrics
  competitor_prices        ← competitor price observations by channel and item
  update_competitor_price  ← inventory enriched with latest competitor prices

Pipelines
  update_competitor_prices   retail_inventory + competitor_prices → update_competitor_price
  ontology_materialization   update_competitor_price → RI_* ontology tables

Codes
  update_competitor_price (python)  ← joins inventory with competitor prices on item_code
  build_retail_ontology (python)    ← builds ontology entity/relation tables

Dashboards
  inventory_overview  ← KPI stats, charts, heatmap, price competitiveness, and detail tables

Ontology
  Entities: ri_product, ri_branch, ri_region, ri_supplier
  Relations:
    ri_located_in   (ri_branch → ri_region)
    ri_stocked_at   (ri_product → ri_branch)
    ri_supplied_by  (ri_product → ri_supplier)
```

---

## Datasets

| Name | Type | Description |
| ---- | ---- | ----------- |
| `retail_inventory` | delta | Branch-level product inventory: stock qty, pricing, turnover rate, stockout risk |
| `competitor_prices` | delta | Competitor price observations per channel, branch, and item |
| `update_competitor_price` | delta | Inventory with `competitor_price_krw_synth` updated from latest competitor observations |

---

## Pipelines

### `update_competitor_prices`

**Purpose:** Keeps the inventory dataset current with the latest competitor pricing intelligence so that buyers and category managers can make pricing decisions based on real-time market data rather than stale snapshots.

Joins `retail_inventory` with `competitor_prices` on `item_code`. For each inventory record, it looks up the most recent competitor price observation for the same item and overwrites the `competitor_price_krw_synth` column. The result is written in overwrite mode so the output always reflects the current state of the market.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `update_competitor_price` | `update_competitor_price` | `retail_inventory` + `competitor_prices` → `update_competitor_price` | Join on `item_code`; refresh competitor price column with latest observation |

### `ontology_materialization`

**Purpose:** Materializes the graph-ready entity and relation tables from the competitor-price-enriched inventory dataset.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `build_ontology_tables` | `build_retail_ontology` | `update_competitor_price` → `ri_product`, `ri_branch`, `ri_region`, `ri_supplier`, `ri_located_in`, `ri_stocked_at`, `ri_supplied_by` | Build first-class ontology tables for graph import |

---

## Ontology

| Type | Name | Relationship |
| ---- | ---- | ------------ |
| Entity | `ri_product` | Product master |
| Entity | `ri_branch` | Branch master |
| Entity | `ri_region` | Region master |
| Entity | `ri_supplier` | Supplier master |
| Relation | `ri_located_in` | ri_branch → ri_region |
| Relation | `ri_stocked_at` | ri_product → ri_branch |
| Relation | `ri_supplied_by` | ri_product → ri_supplier |

---

## Dashboard

### `inventory_overview`

**Purpose:** Provides a single-pane view of inventory health and price competitiveness across all branches — KPI summary, stock distribution, outbound trends, time-based patterns, competitor price gaps, and detailed drill-down.

Dashboard freshness follows the derived `update_competitor_price` dataset. Run `update_competitor_prices` before reviewing price competitiveness widgets so competitor-price gaps reflect the latest observations.

| Widget | Type | Description |
| ------ | ---- | ----------- |
| Total Inventory | statistic | `SUM(inventory_qty)` across all branches |
| Total Outbound | statistic | `SUM(outbound_qty)` across all branches |
| Avg Turnover Rate | statistic | `AVG(inventory_turnover_rate)` |
| Stockout Risk | statistic | Count of items with `stockout_risk_flag_synth = 1` |
| Avg Discount Rate | statistic | `AVG(discount_rate_synth)` |
| Inventory / Outbound by Branch | bar-chart (stacked) | Side-by-side inventory vs outbound per branch |
| Daily Outbound Trend | line-chart | Multi-series line chart by branch over `delivery_date` |
| Outbound Heatmap by Time | heatmap | `delivery_time` × `branch_name` intensity map |
| Inventory Share by Branch | pie-chart (donut) | Proportional inventory distribution |
| Inventory Detail | data-table | Sortable/searchable table with branch, warehouse, qty, turnover, risk |
| Higher Than Competitor | statistic | Count of branch-product records where selling price exceeds competitor price |
| Avg Price Gap by Branch | bar-chart | Average selling-price minus competitor-price gap by branch |
| Price Adjustment Candidates | data-table | Branch-product candidates sorted by competitor price gap |

---

## Sample Data

Tracked sample data is stored under `data/parquet/`. CSV exports are documented under `data/csv/README.md` and stored outside the repository.

- `retail_inventory.csv` — id, item_code, product_name, branch_code, work_order_no, PROD_GRP, branch_name, warehouse_location, warn_clearnace, delivery_date, delivery_time, manager_id, inventory_qty, outbound_qty, stock_variance, inventory_turnover_rate, safety_stock_level, list_price_krw_synth, selling_price_krw_synth, discount_rate_synth, unit_cost_krw_synth, promo_flag_synth, competitor_price_krw_synth, stockout_risk_flag_synth, latitude, longitude, region_name, supplier_name
- `competitor_prices.csv` — competitor_id, channel_type, branch_code, item_code, observed_at, price_krw, price_type, promo_flag, source, currency
