# Finale Inventory to WHIMS Migration Plan

## Data Analysis Summary

### Finale Export Files:
1. **PurchaseOrderWDetail.json** - 114 purchase orders with line items
2. **StockQuantityBySublocationInUnitsWDetail.json** - 4,192 products across 200+ sublocations
3. **StockHistoryTransactionDetails (1).json** - 117,456 transaction records

### Current WHIMS Database Structure:
- Products management with SKU tracking
- Purchase orders with line items
- Stock levels by warehouse/location
- Stock history/transaction tracking
- Multiple warehouses (Go-Parts GA, RapidFulfillment, SimplePrice, Today Delivery INC, Main Warehouse)

## Migration Checklist

### Phase 1: Pre-Migration Setup
- [ ] **Backup current database**
- [ ] **Create staging tables** for data validation
- [ ] **Verify warehouse mappings** (Go-Parts GA already exists)
- [ ] **Create missing warehouses** if needed
- [ ] **Set up location structure** for sublocations

### Phase 2: Product Migration
- [ ] **Extract unique products** from all Finale files
- [ ] **Validate product IDs** against existing products
- [ ] **Import new products** with basic info
- [ ] **Log products requiring manual review** (missing data)

### Phase 3: Purchase Order Migration
- [ ] **Parse purchase order headers** (114 orders)
- [ ] **Map order statuses** (all are "Completed")
- [ ] **Import purchase orders** with correct dates
- [ ] **Import purchase order line items**
- [ ] **Calculate and verify totals**

### Phase 4: Stock/Inventory Migration
- [ ] **Map sublocations to locations table**
- [ ] **Import current stock quantities** by location
- [ ] **Set initial average prices** where available
- [ ] **Mark all stock as "new" condition**
- [ ] **Set is_selling flag** appropriately

### Phase 5: Stock History Migration
- [ ] **Parse 117k transaction records**
- [ ] **Map transaction types** to action_types
- [ ] **Import historical transactions**
- [ ] **Verify quantity calculations**

### Phase 6: Post-Migration Validation
- [ ] **Verify product counts** match source
- [ ] **Verify purchase order totals**
- [ ] **Verify stock quantities** by location
- [ ] **Run data integrity checks**
- [ ] **Generate migration report**

## Data Mappings

### Finale → WHIMS Field Mappings

#### Products Table
- Product ID → product_id
- (Not in Finale) → supplier_part_number
- (Calculate from PO) → average_price
- (Default) → status = 'active'

#### Purchase Orders Table
- Order ID → po_number
- Order date → order_date
- Status → status (map "Completed" → "received")
- (Lookup) → warehouse_id (based on order ID prefix)

#### Purchase Order Items Table
- Product ID → product_id (via lookup)
- Quantity → quantity
- Unit price → unit_price
- Subtotal → total_price

#### Stocks Table
- Product ID → product_id (via lookup)
- Sublocation → location_id (via lookup/creation)
- Units QoH → quantity
- (Default) → reserved_quantity = 0
- (Default) → condition = 'new'
- (Default) → is_selling = 1

#### Stock History Table
- Product ID → product_id (via lookup)
- Record date → created_at
- Transaction description → action_type
- Qty → quantity_change
- Transaction details → notes

## Critical Considerations

1. **Product ID Format**: Finale uses direct SKUs, WHIMS uses internal IDs with product_id field
2. **Warehouse Identification**: Order IDs contain warehouse codes (GA, SP, TDI)
3. **Location Hierarchy**: Sublocations need mapping to warehouse→location structure
4. **Missing Data**: No supplier info, dimensions, or weights in Finale export
5. **Stock Types**: Only "In stock" type in history - need to handle other types
6. **Date Formats**: M/D/YYYY format needs conversion to MySQL format

## Risk Mitigation

- Run migration on test database first
- Keep detailed logs of all transformations
- Maintain mapping tables for rollback capability
- Validate data at each phase before proceeding
- Have rollback scripts ready