# 13 - Customs and Shipping Data Refactoring

**Status: NOT STARTED**

## Summary

Move hardcoded customs and shipping configuration data from TypeScript source files into a database-driven configuration system editable through the Arda UI, eliminating the need for code deployments to update product mappings, customs info, package configs, and grouping rules.

See `shipping-configurations.md` in this folder for detailed diagrams of the current shipment grouping logic.

## Current State

There is an explicit comment in the codebase acknowledging this need:

```typescript
// FabricatorSchemas.ts line 651:
/**
 * There are five Five FIVE FIVE!!! enums dealing with Shopify products.
 *
 * THIS MUST BE TURNED INTO A DATABASE SOMEWHERE.
 */
```

### Data Structures to Migrate

1. **Shopify Product Variant Mapping** (`ShopifyData.ts` lines 26-650)
    - 140+ product variant mappings from Shopify `product_id:variant_id` to internal product types

2. **Product Type Enum** (`FabricatorSchemas.ts` lines 657-771)
    - 75+ product type codes (headsets, accessories, cushions, cyberbox variants, etc.)

3. **Product Customs Information** (`FabricatorSchemas.ts` lines 3100-3605)
    - 60+ product customs configs with tariff numbers, weights, values, currency overrides

4. **Shipping Package Configurations** (`FabricatorSchemas.ts` lines 3640-4011)
    - 20+ package type configs with dimensions, declared values, customs items

5. **Shipment Grouping Logic** (`ShippingAdminApi.ts` lines 930-1247)
    - How line items are grouped into shipments
    - Which products can ship together
    - Package type selection based on product combinations

## Database Changes

```sql
CREATE TABLE big_product_types (
    id TEXT PRIMARY KEY,
    code TEXT UNIQUE NOT NULL,
    displayName TEXT NOT NULL,
    category TEXT,
    isActive BOOLEAN DEFAULT true,
    createdAt BIGINT,
    updatedAt BIGINT
);

CREATE TABLE big_shopify_product_mappings (
    id TEXT PRIMARY KEY,
    shopifyProductId TEXT NOT NULL,
    shopifyVariantId TEXT NOT NULL,
    productTypeId TEXT REFERENCES big_product_types(id),
    sku TEXT,
    title TEXT,
    productTitle TEXT,
    isActive BOOLEAN DEFAULT true,
    createdAt BIGINT,
    updatedAt BIGINT,
    UNIQUE(shopifyProductId, shopifyVariantId)
);

CREATE TABLE big_product_customs_info (
    id TEXT PRIMARY KEY,
    productTypeId TEXT REFERENCES big_product_types(id),
    tariffNumber TEXT NOT NULL,
    description TEXT NOT NULL,
    weight NUMERIC NOT NULL,
    massUnit TEXT DEFAULT 'lb',
    defaultValue NUMERIC NOT NULL,
    valueCurrency TEXT DEFAULT 'USD',
    createdAt BIGINT,
    updatedAt BIGINT
);

CREATE TABLE big_customs_value_overrides (
    id TEXT PRIMARY KEY,
    productCustomsInfoId TEXT REFERENCES big_product_customs_info(id),
    currency TEXT NOT NULL,
    value NUMERIC NOT NULL,
    UNIQUE(productCustomsInfoId, currency)
);

CREATE TABLE big_package_types (
    id TEXT PRIMARY KEY,
    code TEXT UNIQUE NOT NULL,
    displayName TEXT NOT NULL,
    lengthInches NUMERIC,
    widthInches NUMERIC,
    heightInches NUMERIC,
    weightLbs NUMERIC,
    tariffNumber TEXT,
    customsDescription TEXT,
    itemDescription TEXT,
    includeExporterIdentification BOOLEAN DEFAULT true,
    declaredValueUsd NUMERIC,
    isActive BOOLEAN DEFAULT true,
    createdAt BIGINT,
    updatedAt BIGINT
);

CREATE TABLE big_shipment_grouping_rules (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    priority INT DEFAULT 0,
    productTypeIds TEXT[],
    resultingPackageTypeId TEXT REFERENCES big_package_types(id),
    resultingInventoryTypeId TEXT REFERENCES big_product_types(id),
    conditions JSONB,
    isActive BOOLEAN DEFAULT true,
    createdAt BIGINT,
    updatedAt BIGINT
);
```

## Files to Create

| File | Purpose |
|------|---------|
| `api/src/fabricator/ConfigurationApi.ts` | API for managing configuration data |
| `api/src/fabricator/ConfigurationSchemas.ts` | Schema definitions for config tables |
| `api/src/fabricator/ConfigurationDatabase.ts` | Database queries for config data |
| `webapps/src/components/Configuration/ProductTypesPage.jsx` | Manage product types |
| `webapps/src/components/Configuration/ShopifyMappingsPage.jsx` | Manage Shopify mappings |
| `webapps/src/components/Configuration/CustomsInfoPage.jsx` | Manage customs info |
| `webapps/src/components/Configuration/PackageTypesPage.jsx` | Manage package configs |
| `webapps/src/components/Configuration/GroupingRulesPage.jsx` | Manage grouping rules |
| `webapps/src/components/Configuration/ConfigurationWrapper.jsx` | Navigation wrapper |

## Files to Modify

| File | Change |
|------|--------|
| `api/src/fabricator/ShopifyData.ts` | Replace hardcoded map with database lookup |
| `api/src/fabricator/FabricatorSchemas.ts` | Keep enums for backward compat but load values from DB |
| `api/src/fabricator/ShippingAdminApi.ts` | Update `getBigOrderShipmentGroups` to use DB rules |
| `api/src/fabricator/ShippingAdminApi.ts` | Update customs declaration to use DB config |
| `apps/admin_api/admin_api.ts` | Register configuration API routes |
| `apps/admin_api/db_setup.ts` | Add new configuration tables |
| `webapps/app/App.jsx` | Add routes for configuration pages |
| `webapps/app/ArdaWrapper.jsx` | Add Configuration section to navigation |

## API Endpoints

| Method | Endpoint | Purpose |
|--------|----------|---------|
| GET/POST/PUT | `/api/admin/config/product-types` | CRUD product types |
| GET/POST/PUT/DELETE | `/api/admin/config/shopify-mappings` | CRUD Shopify mappings |
| GET/POST/PUT | `/api/admin/config/customs-info` | CRUD customs info |
| GET/POST/PUT | `/api/admin/config/package-types` | CRUD package types |
| GET/POST/PUT | `/api/admin/config/grouping-rules` | CRUD grouping rules |
| POST | `/api/admin/config/migrate` | Run migration from code to DB |
| GET | `/api/admin/config/validate` | Validate current configuration |

## Migration Strategy

1. **Phase 1**: Create tables, API, and admin UI
2. **Phase 2**: Write migration script, run in staging, validate
3. **Phase 3**: Dual-read mode (DB first, hardcoded fallback)
4. **Phase 4**: Switch to DB as primary source
5. **Phase 5**: Remove hardcoded data, update docs, train team

## Risk Mitigation

- Keep hardcoded data as fallback during transition
- Comprehensive validation before saving config changes
- "Preview" mode to see how changes would affect shipments
- Alerts if database config is missing expected entries
- Consider caching (Redis) since config changes rarely
- Audit log for who changed what and when
- Import/export as JSON for backup