# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Overview

B2B Reports dashboard for **physical product orders** across labels. Displays order quantities, shipments, returns, and net metrics broken down by label, subaccount, and product — with a format pivot view showing quantities by physical format (CD, LP, 7", Cassette).

- **Platform**: React 19 SPA with Snowflake browser-based OAuth
- **Data Warehouse**: Snowflake (SME-ORCHARD instance)
- **Design System**: Solfege (The Orchard's internal design system)
- **Reference Dashboard**: [Sigma B2B Reports](https://app.sigmacomputing.com/sony-music-entertainment/workbook/B2B-Reports-30wb89sP7gxNAbpgwcYGz7) (workbook ID: `30wb89sP7gxNAbpgwcYGz7`)
- **Sigma API**: Credentials stored externally (never commit client IDs or secrets to repo). Use Sigma REST API to extract SQL queries from workbook elements (`POST /v2/auth/token`, then `GET /v2/workbooks/{id}/elements/{elementId}/query`)

## Running the Dashboard

```bash
cd ui && npm install && npm run dev
# Opens at http://127.0.0.1:5175/
# Click "Connect Snowflake" → OAuth redirect → authenticate → select a label
```

## Running Tests

```bash
cd ui && npx vitest run
# 29 tests across 3 files: queries, label grouping, data mapping
```

## Tech Stack

- **React 19** + **TypeScript 5.9** + **Vite 7** (port `5175`)
- **MUI 7** (`@mui/material`) for UI components
- **Snowflake browser-based OAuth** via PKCE flow
- **Vite dev server proxy** (local) + **Vercel serverless proxy** (`ui/api/snowflake-proxy.js`)
- **IndexedDB** cache with SHA-256 query hashing (prefix: `sf_b2b_`)
- **Vitest** for unit tests

## File Structure

```
ui/
├── .env.local              # VITE_SNOWFLAKE_ACCOUNT=SME-ORCHARD, warehouse, role
├── vercel.json             # Build config + rewrites (snowflake-api proxy, SPA fallback)
├── vite.config.ts          # Snowflake proxy config (local dev), vitest config
├── api/
│   ├── snowflake-proxy.js  # Vercel serverless proxy for Snowflake token + SQL API
│   └── auth/callback.js    # OAuth callback — bypasses Vercel deployment protection
└── src/
    ├── main.tsx
    ├── App.tsx             # Sidebar nav + OAuth handler + section routing
    ├── theme.ts            # Solfege MUI theme (gray scale, midnight scale, chart colors)
    ├── api/
    │   ├── snowflake.ts    # OAuth PKCE + SQL API client (storage prefix: sf_b2b_)
    │   └── cache.ts        # IndexedDB cache (DB name: sf_b2b_cache)
    ├── queries/
    │   └── b2b.ts          # 7 SQL query functions, parameterized by vendor IDs
    ├── components/
    │   ├── SnowflakeConnect.tsx   # Connect/disconnect with token refresh polling
    │   ├── LabelFilter.tsx        # Multi-select grouped dropdown (Suggested + All Labels)
    │   ├── Leaderboard.tsx        # Ranked list card (reused 3x)
    │   ├── SummaryTable.tsx       # Label/Subaccount summary with expandable detail rows + totals
    │   ├── DetailTable.tsx        # Product-level detail (inline expand)
    │   └── FormatPivotTable.tsx   # Format pivot: CD/LP/7"/Cassette columns per product
    ├── pages/
    │   └── B2BDashboard.tsx       # Main page orchestrating all sections
    └── __tests__/
        ├── queries.test.ts        # SQL injection safety, query structure validation
        ├── labelFilter.test.ts    # Group assignment, ordering, edge cases
        └── snowflake.test.ts      # resultToObjects column mapping, null handling
```

## Snowflake Connection

- **Instance**: `SME-ORCHARD` (not SME-DELPHI)
- **Warehouse**: `DEV_PERFORMANCE_WAREHOUSE`
- **Role**: `DEV_ENGINEERING`
- **Database context**: `SONY_INTERNAL` / `PROD`
- **OAuth storage prefix**: `sf_b2b_` (distinct from other apps like `sf_email_`)
- **SQL API**: `POST /api/v2/statements` with async polling (2s interval, max 30 attempts)
- **Column names**: Snowflake SQL API returns **UPPERCASE** column names

## Data Sources

All queries join two views in `SONY_INTERNAL.PROD`, filtered by `supply_chain_id='738'` (US supply chain):

| View | Purpose |
|---|---|
| `PHYSICAL_PRODUCT_METADATA_VIEW` | Product metadata: label_nm, sublabel_nm, artist_nm, product_nm, product_cd, upc_cd, display_configuration, product_type, product_format, status_nm, release_dt, exclusive_for, price_cd, series_cd, vendor_id, subacct_id, local_product_cd, supply_chain_id |
| `PHYSICAL_PRODUCT_SALES_RTD_VIEW` | Sales metrics: order_qt, back_order_qt, day1_ship_qt, mtd_ship_qt, cytd_ship_qt, rtd_ship_qt, day1_return_qt, mtd_return_qt, cytd_return_qt, rtd_return_qt, + same pattern for _am (amount) columns |

**Join**: `ON s.local_product_cd = p.local_product_cd AND s.supply_chain_id = p.supply_chain_id`

**Net quantities**: Computed as `ship + return` (returns are stored as negative values).

### Vendor/Account Structure

- **Account = Label = Vendor** — the VENDOR table has `IS_DISTRIBUTOR` flag
- `IS_DISTRIBUTOR = 'Y'` means it has subaccounts; `'N'` means top-level label
- Vendor hierarchy in `ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR`
- Subaccount names come from `SUBLABEL_NM` in the metadata view

### Format Pivot

The `PRODUCT_TYPE` column provides clean format categories for the pivot:
- `CD`, `12" Vinyl` (LP), `7" Vinyl`, `Cassette`, `10" Vinyl`, `Blu-ray`
- Pivot uses `SUM(CASE WHEN product_type = '...' THEN metric ELSE 0 END)`

### Sigma RLS-Scoped Labels

The Sigma dashboard uses Row Level Security via `ContactEmail_To_Vendor_Subaccount_Mapping`. The "Suggested" group in the label dropdown includes these vendor IDs:

| Vendor ID | Label |
|---|---|
| 22221 | Fat Possum |
| 25420 | Grand Jury |
| 24583 | House Arrest |
| 26064 | UK Fat Possum Records [PHYSICAL ONLY] |
| 34514 | Columbia Records Group |
| 76325 | NQ Records |

## SQL Queries

All in `ui/src/queries/b2b.ts`, parameterized by `vendorIds: number[]`:

| Query | Purpose |
|---|---|
| `labelListQuery` | Distinct labels for filter dropdown |
| `summaryQuery` | Label/Subaccount summary (30 metric columns) |
| `detailQuery` | Product-level detail (43 columns, filtered by vendor) |
| `topOpenOrdersQuery` | Top 10 products by open order quantity |
| `topYesterdayShipmentsQuery` | Top 10 products by yesterday shipment quantity |
| `topMtdShipmentsQuery` | Top 10 products by MTD shipment quantity |
| `formatPivotQuery` | Format-pivoted view: CD/LP/7"/Cassette columns per product |

**SQL injection guard**: All vendor IDs validated via `Number.isInteger()` before interpolation. String parameters are never interpolated.

## Dashboard Sections

**Overview** (sidebar: "Overview"):
1. **Leaderboard strip** — 3 side-by-side cards: Top Open Orders, Top Yesterday Shipments, Top MTD Shipments
2. **Summary table** — Label/Subaccount grouped with 28 numeric columns (quantities + amounts), expandable rows showing product detail, sticky totals row at bottom

**Format View** (sidebar: "Format View"):
3. **Format pivot table** — One row per product with CD/LP/7"/Cassette/Other/Total columns. Toggle between Orders, MTD Ship, RTD Ship metrics.

## Solfege Design System Compliance

Tables follow [Solfege GridTable standards](https://solfege.theorchard.com/components/grid-table):

- **Zebra striping**: Alternating row backgrounds (even rows gray-50)
- **Summary bar**: Row count + "Updating table" spinner above column headers
- **Loading states**: Skeleton rows on first load; spinner + 50% opacity dim on refresh
- **Sticky headers**: Column headers stick on scroll
- **Totals row**: Sticky at bottom with aggregated values
- **Expandable rows**: Chevron toggle with `aria-expanded` and `aria-controls`

Multi-select dropdown follows [Solfege MultiSelect](https://solfege.theorchard.com/components/multi-select):
- Grouped options: "Suggested" (starred, blue bg) + "All Labels"
- Search/filter with `disableCloseOnSelect`
- Selected items shown as chips

General Solfege tokens defined in `theme.ts`:
- Gray scale (`#ffffff` to `#0a0c0d`), midnight scale (sidebar)
- Rubik font family, 14px base
- Chart colors: teal, royalBlue, darkPurple, orange, red, sapGreen

## Key Patterns

- **Debounced label selection**: 400ms debounce on multi-select changes to prevent concurrent query races
- **Last write wins**: `loadIdRef` counter ensures stale query results are discarded
- **Pre-computed detail map**: `useMemo` Map in SummaryTable for O(n+m) detail lookup instead of O(n*m) filter
- **Memoized totals**: Single `useMemo` pass over rows for totals row, not per-column reduce
- **Date formatting**: Snowflake SQL API returns DATE columns as epoch days (integer). Conversion: `new Date(n * 86400000).toISOString().slice(0, 10)`