# AI Database Online Synchronization Plan

## Overview
This document outlines the step-by-step plan to synchronize data from the local Viamed database to the online database. The goal is to upload only the necessary data for web-visible cross-references while maintaining data integrity.

## Current Status
- ✅ Online database connection tested and working
- ✅ Online database structure documented
- ✅ Data synchronization strategy planned

## Synchronization Strategy

### Step 1: Identify Web-Visible Categories
**Query**: `SELECT xrefcode FROM viamed_xref_cat WHERE showonweb = 1`
- **Purpose**: Get all cross-reference categories that should be visible on the web
- **Output**: List of xrefcode values (e.g., ['COMPATIBLE', 'EQUIVALENT', etc.])
- **Note**: Only categories with showonweb=1 are approved for online display

### Step 2: Extract Approved Stock References
**Query**: `SELECT DISTINCT stock_ref FROM viamed_xref WHERE xrefcode IN (web_visible_categories)`
- **Purpose**: Get all stock references that have web-visible cross-references
- **Output**: List of approved stock_ref values
- **Note**: A stock_ref may have multiple xref entries (one per OEM compatibility)

### Step 3: Filter Stock List Data
**Query**: `SELECT * FROM stocklist_tarriffs WHERE hostcompany_id = '1' AND stock_ref IN (approved_stock_refs)`
**Fields to sync** (mapping to online structure):
- stock_ref → stock_ref
- OperaDescription → pricedesc (or appropriate description field)
- hostcompany_id → hostcompany_id
- SUPERSEDED → SUPERSEDED
- shopifyhandle → shopifyhandle

**Additional considerations**:
- Only Viamed data (hostcompany_id='1')
- Only approved stock references
- Map local field names to online field names where different

### Step 4: Clean Up Related Tables
For each related table, remove records not associated with approved stock references:

**viamed_xref**:
- Keep only records where xrefcode IN (web_visible_categories)
- This automatically filters to approved stock references

**viamed_xref_fields_answers**:
- Keep only records where stock_ref IN (approved_stock_refs)

**viamed_xref_fields**:
- Keep only records where xrefcode IN (web_visible_categories)

**viamed_xref_answer**:
- Keep only records where xrefcode IN (web_visible_categories)

**viamed_xref_cleanlookup**:
- Keep only records where xrefcode IN (web_visible_categories)

**viamed_xref_cat**:
- Keep only records where showonweb = 1

## Data Flow Summary

```
Local Database
├── viamed_xref_cat (showonweb=1)
│   └── xrefcode list
├── viamed_xref (filter by xrefcode)
│   └── approved stock_ref list
└── stocklist_tarriffs (filter by stock_ref + hostcompany_id=1)
    └── filtered stock data

Online Database (to be updated)
├── stocklist_tarriffs (replace with filtered data)
├── viamed_xref (replace with filtered data)
├── viamed_xref_cat (replace with web-visible only)
├── viamed_xref_fields_answers (clean up non-approved stock_refs)
├── viamed_xref_fields (clean up non-web-visible xrefcodes)
├── viamed_xref_answer (clean up non-web-visible xrefcodes)
└── viamed_xref_cleanlookup (clean up non-web-visible xrefcodes)
```

## Implementation Steps

### Phase 1: Data Extraction (Local)
1. Query web-visible categories
2. Query approved stock references
3. Query filtered stock data
4. Query related table data with filters applied

### Phase 2: Data Transformation
1. Map local field names to online field names
2. Handle missing fields (set defaults or skip)
3. Validate data integrity
4. Prepare INSERT/UPDATE statements

### Phase 3: Online Database Update
1. Backup online database
2. Clear existing data (or use REPLACE INTO)
3. Insert new filtered data
4. Verify data integrity
5. Test cross-reference functionality

## Technical Considerations

### Field Mapping Issues
- **stocklist_tarriffs.pricedesc**: Local has multiple description fields (OperaDescription, paperworkdescription, etc.) - need to choose appropriate source
- **Missing fields**: Online tables lack many audit fields - synchronization must handle this gracefully

### Data Volume
- Expect significantly reduced dataset (only web-visible items)
- Stock references may number in hundreds/thousands vs full catalog

### Error Handling
- Connection failures between local and online DB
- Data validation errors
- Partial synchronization recovery

### Performance
- Batch operations for large datasets
- Transaction management for data consistency
- Progress tracking for long operations

## Next Steps
1. Create PHP script to implement the extraction and synchronization logic
2. Test with sample data first
3. Implement full synchronization with error handling
4. Set up automated sync process (if needed)

## Notes
- viamed_xref contains multiple entries per stock_ref (one per OEM compatibility)
- All filtering is based on web visibility to keep online database lean
- Online database appears optimized for read-only cross-reference operations