# Customs Data Migration Plan

This document outlines the plan for moving hardcoded customs data from `FabricatorSchemas.ts` into a database-configurable system.

## Current State

### Hardcoded Data Location
The `BigProductTypeCustomsInfo` constant in `FabricatorSchemas.ts` maps `BigProductType` enum values to customs information:

```typescript
export interface BigProductCustomsInfo {
    tariffNumber: string;
    description: string;
    weight: string;
    massUnit: string;
    defaultValue: number;
    customsValueOverrides?: any;
}

export const BigProductTypeCustomsInfo: Partial<Record<BigProductType, BigProductCustomsInfo>> = {
    [BigProductType.BigscreenBeyondV1]: {
        tariffNumber: "8528.5210.00",
        description: "VR Headset",
        weight: "2.0",
        massUnit: "lb",
        defaultValue: 999.0
    },
    [BigProductType.UniversalLightFitSeal]: {
        tariffNumber: "8529.90",
        description: "VR Headset Light Fit Seal",
        weight: "0.25",
        massUnit: "lb",
        defaultValue: 29.0,
        customsValueOverrides: {
            "JPY": 3100.0,
            "USD": 29.0,
            "EUR": 27.0,
            "GBP": 23.0,
            "AUD": 43.0
        }
    },
    // ... ~50 product types
};
```

### Current Usage

| File | Location | Usage |
|------|----------|-------|
| `ShippingAdminApi.ts` | `getAdjustedCustomsPrices2()` | Builds customs items for international shipping labels; applies `customsValueOverrides` for currency-specific pricing |
| `FabricatorAdminApi.ts` | `/api/admin/fabricator/schemas2` endpoint | Serves data to the frontend as part of the schemas2 bundle |
| `BigProductCustomsInfo.jsx` | Frontend | Displays customs info in read-only table |

### Product Categories with Customs Info (~50 entries)

- **VR Headsets**: BigscreenBeyondV1, Beyond2, Beyond2Eye, Beyond2Upgrade, Beyond2EyeUpgrade, Beyond2EyeVRChat
- **Cyberboxes**: B2ClearCyberbox, B2BlackCyberbox, B2OrangeCyberbox, B2PurpleCyberbox (+ Mini and V2 variants)
- **Eye Tracking Cyberboxes**: B2EyeClearCyberbox, B2EyeBlackCyberbox, etc. (+ Mini and V2 variants)
- **Audio Straps**: AudioStrapV1, AudioStrap
- **Lenses**: PrescriptionLenses, PrescriptionLensInserts
- **Cushions**: BeyondCushionV1, CustomCushion, UniversalLightFitSeal, ReplacementBeyondCushionV1, GenericCushion
- **Cables**: BeyondFibreOpticCableV1, BeyondFibreOpticCableV2, ReplacementBeyondFibreOpticCableV1
- **Link Boxes**: BeyondLinkBoxV1, BeyondLinkBoxV2, ReplacementBeyondLinkBoxV1
- **Soft Straps**: BeyondSoftStrapV1, BeyondSoftStrapV2, ReplacementBeyondSoftStrapV1
- **Accessories**: HaloMount, UniversalHalo, IPDDiscoveryUnitV1, Beyond2IPDTool, StorageCan, B2AccessoriesBundle
- **Shells**: CoverShell, B2ShellClear, B2ShellBlack, B2ShellOrange, B2ShellYellow, B2ShellVRChatPurple
- **Other**: ReplacementBigscreenBeyondV1, Merchandise, BigscreenRacingGloves

---

## Phase 1: Database Schema & Migration

### 1.1 Create New Table

Add to `PostgresInfo.Tables` in `FabricatorDatabase.ts`:
```typescript
public static BigProductCustomsInfo: string = "big_product_customs_info";
```

### 1.2 Table Schema

All column names use `"camelCase"` with double quotes, matching the convention used throughout `FabricatorDatabase.ts`.

```sql
CREATE TABLE big_product_customs_info (
    "id" uuid DEFAULT uuid_generate_v4() PRIMARY KEY,

    -- Core fields
    "productType" VARCHAR(64) NOT NULL UNIQUE,
    "tariffNumber" VARCHAR(32) NOT NULL,
    "description" VARCHAR(256) NOT NULL,
    "weight" VARCHAR(16) NOT NULL,
    "massUnit" VARCHAR(8) NOT NULL DEFAULT 'lb',
    "defaultValue" DECIMAL(10, 2) NOT NULL,
    "customsValueOverrides" jsonb,

    -- Audit fields
    "createdAt" BIGINT DEFAULT (EXTRACT(EPOCH FROM NOW()) * 1000),
    "updatedAt" BIGINT DEFAULT (EXTRACT(EPOCH FROM NOW()) * 1000),
    "createdBy" VARCHAR(32),
    "updatedBy" VARCHAR(32),

    -- Constraints
    CONSTRAINT valid_mass_unit CHECK ("massUnit" IN ('lb', 'kg', 'oz', 'g'))
);
```

Note: The `UNIQUE` constraint on `"productType"` implicitly creates an index, so no separate `CREATE INDEX` is needed.

Note: `"weight"` is `VARCHAR(16)` rather than `DECIMAL` because the existing interface defines it as `string` and all current data stores weight as strings like `"2.0"`, `"0.25"`, etc. Keeping it as VARCHAR avoids silent coercion issues.

### 1.3 Migration Script

Add to `beyond_db_migrate.ts`:

```typescript
// Create big_product_customs_info table
try {
    const sql = `
        CREATE TABLE IF NOT EXISTS ${PostgresInfo.Tables.BigProductCustomsInfo} (
            "id" uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
            "productType" VARCHAR(64) NOT NULL UNIQUE,
            "tariffNumber" VARCHAR(32) NOT NULL,
            "description" VARCHAR(256) NOT NULL,
            "weight" VARCHAR(16) NOT NULL,
            "massUnit" VARCHAR(8) NOT NULL DEFAULT 'lb',
            "defaultValue" DECIMAL(10, 2) NOT NULL,
            "customsValueOverrides" jsonb,
            "createdAt" BIGINT DEFAULT (EXTRACT(EPOCH FROM NOW()) * 1000),
            "updatedAt" BIGINT DEFAULT (EXTRACT(EPOCH FROM NOW()) * 1000),
            "createdBy" VARCHAR(32),
            "updatedBy" VARCHAR(32),
            CONSTRAINT valid_mass_unit CHECK ("massUnit" IN ('lb', 'kg', 'oz', 'g'))
        );
    `;
    await client.query(sql);
} catch (e) {
    Logger.error(`Error creating ${PostgresInfo.Tables.BigProductCustomsInfo}: ` + e.message);
}
```

### 1.4 Seed Script

Create a separate seeding function that reads from `BigProductTypeCustomsInfo` and inserts into the database:

```typescript
async function seedCustomsInfo(client: PoolClient) {
    for (const [productType, info] of Object.entries(FabricatorSchemas.BigProductTypeCustomsInfo)) {
        const sql = `
            INSERT INTO ${PostgresInfo.Tables.BigProductCustomsInfo}
                ("productType", "tariffNumber", "description", "weight", "massUnit", "defaultValue", "customsValueOverrides")
            VALUES ($1, $2, $3, $4, $5, $6, $7)
            ON CONFLICT ("productType") DO NOTHING;
        `;
        await client.query(sql, [
            productType,
            info.tariffNumber,
            info.description,
            info.weight,
            info.massUnit,
            info.defaultValue,
            info.customsValueOverrides ? JSON.stringify(info.customsValueOverrides) : null
        ]);
    }
}
```

---

## Phase 2: Backend Database Layer

### 2.1 Column Schema

Follow the existing `Columns` / `UpdateColumns` pattern used by `BigOrder` and `ScanRequest` in `FabricatorSchemas.ts`. This keeps INSERT/UPDATE generation consistent across the codebase.

```typescript
export class BigProductCustomsInfoRecord {
    // Extend the existing interface fields, plus audit fields
    id: string;
    productType: FabricatorSchemas.BigProductType;
    tariffNumber: string;
    description: string;
    weight: string;
    massUnit: string;
    defaultValue: number;
    customsValueOverrides?: any;
    createdAt: number;
    updatedAt: number;
    createdBy?: string;
    updatedBy?: string;

    public static readonly Columns = [
        "id", "productType", "tariffNumber", "description",
        "weight", "massUnit", "defaultValue", "customsValueOverrides",
        "createdAt", "updatedAt", "createdBy", "updatedBy"
    ];

    public static get UpdateColumns(): string[] {
        return BigProductCustomsInfoRecord.Columns.filter(
            (c) => c !== "id" && c !== "createdAt" && c !== "createdBy"
        );
    }
}
```

### 2.2 Add CRUD Methods to FabricatorDatabase.ts

Use `PostgresUtils.SearchQuery()` for reads (matching all other queries in the file), and the `Columns.map()` pattern for inserts/updates:

```typescript
export class FabricatorDatabase {
    /**
     * Get all product customs info from database
     */
    public static async getAllProductCustomsInfo(): Promise<BigProductCustomsInfoRecord[]> {
        return await PostgresUtils.SearchQuery(PostgresInfo.Tables.BigProductCustomsInfo, {});
    }

    /**
     * Get customs info for a specific product type
     */
    public static async getProductCustomsInfo(
        productType: FabricatorSchemas.BigProductType
    ): Promise<BigProductCustomsInfoRecord | null> {
        const rows = await PostgresUtils.SearchQuery(
            PostgresInfo.Tables.BigProductCustomsInfo,
            { productType }
        );
        return rows.length > 0 ? rows[0] : null;
    }

    /**
     * Create or update customs info for a product type.
     * Uses the Columns/UpdateColumns pattern for consistent INSERT generation.
     */
    public static async upsertProductCustomsInfo(
        info: Partial<BigProductCustomsInfoRecord>,
        updatedBy: string
    ): Promise<BigProductCustomsInfoRecord> {
        const client = await PostgresDatabase.Postgres.getFabricatorClient();
        try {
            const cols = BigProductCustomsInfoRecord.Columns.map((c) => `"${c}"`).join(", ");
            const colIds = BigProductCustomsInfoRecord.Columns.map((c, i) => `$${i + 1}`).join(", ");
            const updateCols = BigProductCustomsInfoRecord.UpdateColumns
                .map((c, i) => `"${c}" = EXCLUDED."${c}"`).join(", ");

            const sql = `
                INSERT INTO ${PostgresInfo.Tables.BigProductCustomsInfo}
                (${cols}) VALUES (${colIds})
                ON CONFLICT ("productType") DO UPDATE SET ${updateCols}
                RETURNING *;
            `;
            // ... build values array from info + updatedBy, execute query
        } finally {
            client.release();
        }
    }

    /**
     * Delete customs info for a product type
     */
    public static async deleteProductCustomsInfo(
        productType: FabricatorSchemas.BigProductType
    ): Promise<boolean> {
        const client = await PostgresDatabase.Postgres.getFabricatorClient();
        try {
            const result = await client.query(
                `DELETE FROM ${PostgresInfo.Tables.BigProductCustomsInfo} WHERE "productType" = $1`,
                [productType]
            );
            return result.rowCount > 0;
        } finally {
            client.release();
        }
    }
}
```

---

## Phase 3: Caching Layer

### 3.1 In-Memory Cache

The customs data is ~50 entries that rarely change. A full Redis caching layer with TTL, invalidation, and a dedicated class is overkill here. Instead, use an in-memory cache with TTL, following the pattern established by `InventoryService.ts`:

Add this to `FabricatorDatabase.ts` (or a nearby utility), co-located with the CRUD methods:

```typescript
interface CustomsInfoCacheEntry {
    data: Record<string, FabricatorSchemas.BigProductCustomsInfo>;
    timestamp: number;
}

const CUSTOMS_CACHE_TTL_MS = 60 * 1000; // 1 minute
let customsInfoCache: CustomsInfoCacheEntry | null = null;

export class CustomsInfoCache {
    /**
     * Get all customs info as a map, with in-memory caching.
     */
    public static async getAll(): Promise<Record<string, FabricatorSchemas.BigProductCustomsInfo>> {
        if (customsInfoCache && (Date.now() - customsInfoCache.timestamp) < CUSTOMS_CACHE_TTL_MS) {
            return customsInfoCache.data;
        }

        const records = await FabricatorDatabase.getAllProductCustomsInfo();

        const customsMap: Record<string, FabricatorSchemas.BigProductCustomsInfo> = {};
        for (const record of records) {
            customsMap[record.productType] = {
                tariffNumber: record.tariffNumber,
                description: record.description,
                weight: record.weight,
                massUnit: record.massUnit,
                defaultValue: record.defaultValue,
                customsValueOverrides: record.customsValueOverrides
            };
        }

        customsInfoCache = { data: customsMap, timestamp: Date.now() };
        return customsMap;
    }

    /**
     * Invalidate cache (call after any create/update/delete)
     */
    public static invalidate(): void {
        customsInfoCache = null;
    }
}
```

### 3.2 Fallback Strategy

Add this to `FabricatorDatabase.ts` alongside the `CustomsInfoCache` class, so it has a clear home:

```typescript
export class CustomsInfoCache {
    // ... getAll() and invalidate() from above ...

    /**
     * Get customs info for a single product type, with fallback to
     * hardcoded values if the database is unavailable.
     */
    public static async getCustomsInfoWithFallback(
        productType: FabricatorSchemas.BigProductType
    ): Promise<FabricatorSchemas.BigProductCustomsInfo | null> {
        try {
            const all = await this.getAll();
            if (all[productType]) return all[productType];
        } catch (error) {
            Logger.warn(`[CustomsInfoCache.getCustomsInfoWithFallback] DB error, falling back to hardcoded: ${error.message}`);
        }

        // Fallback to hardcoded values
        return FabricatorSchemas.BigProductTypeCustomsInfo[productType] || null;
    }
}
```

---

## Phase 4: Admin API Endpoints

### 4.1 New Endpoints

Add to `FabricatorAdminApi.ts` or create new `CustomsAdminApi.ts`:

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/admin/fabricator/customs-info` | List all customs info |
| GET | `/api/admin/fabricator/customs-info/:productType` | Get single product customs info |
| PUT | `/api/admin/fabricator/customs-info/:productType` | Create or update customs info |
| DELETE | `/api/admin/fabricator/customs-info/:productType` | Delete customs info |

### 4.2 Request/Response Schemas

```typescript
// GET /api/admin/fabricator/customs-info
interface GetAllCustomsInfoResponse {
    items: BigProductCustomsInfoRecord[];
    count: number;
}

// PUT /api/admin/fabricator/customs-info/:productType
interface UpsertCustomsInfoRequest {
    tariffNumber: string;
    description: string;
    weight: string;
    massUnit: string;
    defaultValue: number;
    customsValueOverrides?: Record<string, number>; // e.g. { "JPY": 3100.0, "USD": 29.0 }
}

interface UpsertCustomsInfoResponse {
    success: boolean;
    item: BigProductCustomsInfoRecord;
}
```

### 4.3 Validation

```typescript
const customsInfoValidation = [
    validator.body('tariffNumber')
        .isString()
        .isLength({ min: 1, max: 32 })
        .withMessage('Tariff number is required'),
    validator.body('description')
        .isString()
        .isLength({ min: 1, max: 256 })
        .withMessage('Description is required'),
    validator.body('weight')
        .isString()
        .matches(/^\d+(\.\d+)?$/)
        .withMessage('Weight must be a numeric string (e.g. "2.0")'),
    validator.body('massUnit')
        .isIn(['lb', 'kg', 'oz', 'g'])
        .withMessage('Mass unit must be lb, kg, oz, or g'),
    validator.body('defaultValue')
        .isNumeric()
        .withMessage('Default value must be a number'),
    validator.body('customsValueOverrides')
        .optional()
        .isObject()
        .withMessage('customsValueOverrides must be an object mapping currency codes to numeric values')
];
```

---

## Phase 5: Update Existing Code

### 5.1 Update ShippingAdminApi.ts

In `getAdjustedCustomsPrices2()`, replace direct hardcoded access:

```typescript
// BEFORE
const customsInfo = FabricatorSchemas.BigProductTypeCustomsInfo[lineItem.type];

// AFTER
const customsInfo = await CustomsInfoCache.getCustomsInfoWithFallback(lineItem.type);
```

Note: `getAdjustedCustomsPrices2()` is currently synchronous. It will need to become `async` since the cache/fallback involves an async DB call on cache miss. Callers (`getShippingConfig2()` and its chain up to `getCustomsPreview()`) already use `await` patterns, so this change propagates cleanly.

### 5.2 Update schemas2 Endpoint

In `FabricatorAdminApi.ts`, update the `/api/admin/fabricator/schemas2` handler:

```typescript
// BEFORE
BigProductTypeCustomsInfo: FabricatorSchemas.BigProductTypeCustomsInfo,

// AFTER
BigProductTypeCustomsInfo: await CustomsInfoCache.getAll(),
```

---

## Phase 6: Frontend Changes

### 6.1 Update BigProductCustomsInfo.jsx

Transform from read-only display to editable admin interface. Follow the existing editable table patterns used elsewhere in the arda webapp:

**Component structure:**
- `BigProductCustomsInfo.jsx` — main container, fetches data on mount, manages edit state
- Reuse the existing table component patterns from arda for row layout
- Each row has an "Edit" button that toggles inline editing for that row
- A "Create New" row/button at the top for adding new product types

**State management:**
- `items`: array of `BigProductCustomsInfoRecord` from the API
- `editingProductType`: which row is currently being edited (null if none)
- `editForm`: current form values for the row being edited
- `loading` / `saving`: loading states for fetch and save operations

**Editing flow:**
1. User clicks "Edit" on a row → row fields become input fields pre-filled with current values
2. `customsValueOverrides` is edited as a JSON text area (since it's a freeform currency→value map)
3. User clicks "Save" → PUT request to `/api/admin/fabricator/customs-info/:productType`
4. On success, refresh the row data and show a success toast
5. User clicks "Cancel" → revert to display mode

**Delete flow:**
1. User clicks "Delete" → confirmation modal ("Are you sure you want to delete customs info for {productType}?")
2. On confirm → DELETE request to `/api/admin/fabricator/customs-info/:productType`
3. On success, remove the row and show a success toast

### 6.2 New API Methods in ApiUtils.js

```javascript
export async function getProductCustomsInfo() {
    const res = await superagent.get("/api/admin/fabricator/customs-info");
    return res.body;
}

export async function updateProductCustomsInfo(productType, data) {
    const res = await superagent
        .put(`/api/admin/fabricator/customs-info/${productType}`)
        .send(data);
    return res.body;
}

export async function deleteProductCustomsInfo(productType) {
    const res = await superagent
        .delete(`/api/admin/fabricator/customs-info/${productType}`);
    return res.body;
}
```

---

## Phase 7: Testing

### 7.1 Integration Tests

Add to `tests/fabricator/` directory:

```typescript
describe('CustomsInfo API', () => {
    it('should return all customs info');
    it('should return customs info for specific product type');
    it('should create new customs info');
    it('should update existing customs info');
    it('should update customs info with customsValueOverrides');
    it('should delete customs info');
    it('should fall back to hardcoded values on database error');
});
```

### 7.2 Test Scenarios

- [ ] Verify all existing product types are seeded correctly (including `customsValueOverrides` for UniversalLightFitSeal)
- [ ] Verify shipping label generation works with database values
- [ ] Verify `customsValueOverrides` currency-specific pricing still works for the universal cushion case
- [ ] Verify cache invalidation works correctly (update a record, then read it back)
- [ ] Verify fallback to hardcoded values when database unavailable
- [ ] Verify frontend displays and edits correctly

---

## Phase 8: Deployment & Rollout

### 8.1 Deployment Steps

1. **Deploy database migration** (creates table, seeds data)
2. **Deploy backend changes** (new endpoints, caching layer)
3. **Verify data integrity** (compare DB values to hardcoded — all ~50 entries should match, including the `customsValueOverrides` on UniversalLightFitSeal)
4. **Deploy frontend changes** (editable UI)
5. **Monitor for issues** (check logs for fallback warnings, which indicate DB read failures)

### 8.2 Rollback Plan

If issues occur:
1. The hardcoded `BigProductTypeCustomsInfo` constant remains in code throughout the migration period
2. The `getCustomsInfoWithFallback()` method automatically falls back to hardcoded values if the DB read fails
3. To fully revert: redeploy the previous backend version. The hardcoded constant is still the source of truth until Phase 9 cleanup, so no data is lost.

---

## Phase 9: Deprecation & Cleanup

### 9.1 Mark Hardcoded Data as Deprecated

```typescript
/**
 * @deprecated Use CustomsInfoCache.getAll() or CustomsInfoCache.getCustomsInfoWithFallback() instead.
 * This constant is kept as a fallback and will be removed in a future release.
 */
export const BigProductTypeCustomsInfo: Partial<Record<BigProductType, BigProductCustomsInfo>> = {
    // ...
};
```

### 9.2 Future Removal

After sufficient testing period (recommended: 2-3 release cycles):
1. Remove hardcoded `BigProductTypeCustomsInfo` constant
2. Remove fallback logic from `getCustomsInfoWithFallback()` (simplify to DB-only)
3. Update all references to use database-only approach

---

## File Changes Summary

| File | Changes |
|------|---------|
| `FabricatorDatabase.ts` | Add table name to `PostgresInfo.Tables`, add `BigProductCustomsInfoRecord` class, add CRUD methods, add `CustomsInfoCache` class with in-memory caching and fallback |
| `FabricatorSchemas.ts` | Add deprecation notice to hardcoded constant |
| `beyond_db_migrate.ts` | Add table creation and seeding (including `customsValueOverrides`) |
| `ShippingAdminApi.ts` | Use `CustomsInfoCache.getCustomsInfoWithFallback()` in `getAdjustedCustomsPrices2()`, make it async |
| `FabricatorAdminApi.ts` | Update schemas2 endpoint, add new CRUD API endpoints |
| `ApiUtils.js` | Add new API methods for customs info CRUD |
| `BigProductCustomsInfo.jsx` | Add inline editing, create, delete capabilities |
| `tests/fabricator/*.ts` | Add integration tests |

---

## Open Questions (with Recommendations)

1. **Permissions**: Should editing customs info require a specific admin role?
   - **Recommendation: No, use the existing admin API auth.** The admin API server already runs behind a security group that restricts access to valid IP addresses. All admin endpoints already require a valid access token. Adding a separate role system for this one feature is unnecessary complexity. If role-based access is ever needed across the admin API, it should be a separate initiative.

2. **Audit Log**: Should we maintain a separate history table for customs info changes?
   - **Recommendation: Not initially.** The `updatedAt` / `updatedBy` audit fields on the record itself are sufficient for now. Customs data changes are infrequent (~50 entries, rarely modified). If change tracking becomes important later, a generic audit log system across the admin API would be more valuable than a customs-specific one.

3. **Validation**: Should tariff numbers be validated against a known list?
   - **Recommendation: No.** Tariff numbers (HS codes) vary by destination country and change periodically. Hardcoding a validation list would create a maintenance burden and could block legitimate entries. The string format validation (`VARCHAR(32)`, non-empty) is sufficient. Operators entering customs data are expected to know the correct tariff numbers.

4. **Bulk Operations**: Should we support CSV import/export for customs info?
   - **Recommendation: Not in this migration.** The seed script handles the initial bulk load from the hardcoded data. Future additions will be one-off (new product types are added infrequently). If bulk editing becomes needed, it can be added later as a separate feature.

5. **Versioning**: Should we track which customs info version was used for each shipment?
   - **Recommendation: Yes, but as a separate follow-up.** This is valuable for compliance (knowing exactly what was declared at time of shipment), but it's orthogonal to this migration. The current system doesn't track this either, so the migration doesn't regress. A follow-up task could snapshot the customs info onto the shipment record at label creation time.