# AI Database Crossreference Structure

## Overview
This document outlines the database structure for cross-reference tables used in the Viamed system. These tables manage OEM (Original Equipment Manufacturer) part number mappings to internal stock references, enabling efficient product cross-referencing and compatibility management.

## viamed_xref

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| stock_ref | varchar(124) | NO |  |  |  |
| oem | varchar(64) | NO |  |  |  |
| oem_pn | varchar(128) | NO |  |  |  |
| type | varchar(64) | NO |  |  |  |
| verified | varchar(64) | NO |  |  |  |
| updatedon | bigint | NO |  | 0 |  |
| updatedby | varchar(64) | NO |  |  |  |
| xrefcode | varchar(128) | NO |  |  |  |
| combined | longtext | NO |  |  |  |
| primary_id | int | NO |  |  |  |
| specsheet_id | int | NO |  |  |  |
| certificatecompatible | varchar(1) | NO |  |  |  |
| rowcolor | varchar(7) | NO |  |  |  |
| hideweb | varchar(7) | NO |  |  |  |
| priorityorder | int | NO |  |  |  |
| importtag | varchar(5) | NO |  |  |  |
| hostcompanyid | int | NO |  |  |  |
| preferedsell | varchar(48) | NO |  |  |  |
| preferedsellby | int | NO |  |  |  |
| preferedsellon | bigint | NO |  |  |  |
| deletestatus | int | NO |  |  |  |
| deletestatuson | bigint | NO |  |  |  |
| deletestatusby | int | NO |  |  |  |
| shopifyhandle | varchar(256) | NO |  |  |  |
| xrefcode_temp | varchar(64) | NO |  |  |  |
| oemclean | varchar(128) | NO |  |  |  |
| cleansearch | varchar(128) | NO |  |  |  |
| imgurl | varchar(200) | NO |  |  |  |
| brand | varchar(255) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **stock_ref**: Internal stock reference number (up to 124 characters)
- **oem**: Original Equipment Manufacturer name (up to 64 characters)
- **oem_pn**: OEM part number (up to 128 characters)
- **type**: Product type classification (up to 64 characters)
- **verified**: Verification status or notes (up to 64 characters)
- **updatedon**: Unix timestamp of last update
- **updatedby**: User who last updated the record (up to 64 characters)
- **xrefcode**: Cross-reference category code (up to 128 characters)
- **combined**: Combined search field for indexing
- **primary_id**: Reference to primary product record
- **specsheet_id**: Reference to specification sheet document
- **certificatecompatible**: Certificate compatibility flag (Y/N)
- **rowcolor**: Hex color code for UI display (7 characters)
- **hideweb**: Web visibility flag (Y/N, 7 characters)
- **priorityorder**: Display priority order
- **importtag**: Import batch identifier (up to 5 characters)
- **hostcompanyid**: Host company identifier
- **preferedsell**: Preferred selling information (up to 48 characters)
- **preferedsellby**: User who set preferred selling info
- **preferedsellon**: Timestamp when preferred selling was set
- **deletestatus**: Soft delete status (0=active, 1=deleted)
- **deletestatuson**: Timestamp of deletion
- **deletestatusby**: User who deleted the record
- **shopifyhandle**: Shopify product handle (up to 256 characters)
- **xrefcode_temp**: Temporary cross-reference code (up to 64 characters)
- **oemclean**: Cleaned OEM name for searching (up to 128 characters)
- **cleansearch**: Cleaned search field (up to 128 characters)
- **imgurl**: Image URL (up to 200 characters)
- **brand**: Product brand name (up to 255 characters)

## viamed_xref_cat

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| updatedon | bigint | NO |  |  |  |
| updatedby | int | NO |  |  |  |
| xrefcode | varchar(128) | NO |  |  |  |
| description | longtext | NO |  |  |  |
| showonweb | int | NO |  |  |  |
| hostcompanyid | int | NO |  |  |  |
| showfeaturesonweb | tinyint | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **updatedon**: Unix timestamp of last update
- **updatedby**: User who last updated the record
- **xrefcode**: Cross-reference category code (up to 128 characters)
- **description**: Category description
- **showonweb**: Web visibility flag (0=hidden, 1=visible)
- **hostcompanyid**: Host company identifier
- **showfeaturesonweb**: Feature visibility on web flag (0=hidden, 1=visible)

## viamed_xref_answer

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| answer | varchar(200) | NO |  |  |  |
| xrefcode | varchar(32) | NO |  |  |  |
| fieldid | int | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **answer**: Answer text (up to 200 characters)
- **xrefcode**: Associated cross-reference code (up to 32 characters)
- **fieldid**: Reference to field definition

## viamed_xref_fields

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| field_name | varchar(32) | NO |  |  |  |
| xrefcode | varchar(128) | NO |  |  |  |
| displayorder | int | NO |  |  |  |
| addedon | int | NO |  |  |  |
| addedby | bigint | NO |  | 0 |  |
| showweb | int | NO |  |  |  |
| showlocal | int | NO |  |  |  |
| hiddenfield | int | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **field_name**: Field name (up to 32 characters)
- **xrefcode**: Associated cross-reference code (up to 128 characters)
- **displayorder**: Display order priority
- **addedon**: Unix timestamp when field was added
- **addedby**: User who added the field
- **showweb**: Web display flag (0=hidden, 1=visible)
- **showlocal**: Local display flag (0=hidden, 1=visible)
- **hiddenfield**: Hidden field flag (0=visible, 1=hidden)

## viamed_xref_fields_answers

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| xrefcode | varchar(126) | NO |  |  |  |
| fieldid | int | NO |  |  |  |
| answer | varchar(255) | NO |  |  |  |
| specsheet_id | int | NO |  |  |  |
| setby | int | NO |  |  |  |
| seton | bigint | NO |  | 0 |  |
| stock_ref | varchar(32) | NO |  |  |  |
| addedby | int | NO |  |  |  |
| addedon | int | NO |  |  |  |
| answerid | int | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **xrefcode**: Associated cross-reference code (up to 126 characters)
- **fieldid**: Reference to field definition
- **answer**: Answer text (up to 255 characters)
- **specsheet_id**: Reference to specification sheet
- **setby**: User who set the answer
- **seton**: Timestamp when answer was set
- **stock_ref**: Associated stock reference (up to 32 characters)
- **addedby**: User who added the answer
- **addedon**: Timestamp when answer was added
- **answerid**: Reference to predefined answer

## viamed_xref_cleanlookup

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| clean | varchar(128) | NO |  |  |  |
| dirty | varchar(128) | NO |  |  |  |
| xrefcode | varchar(64) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **clean**: Cleaned/standardized value (up to 128 characters)
- **dirty**: Original/raw value (up to 128 characters)
- **xrefcode**: Associated cross-reference code (up to 64 characters)

## viamed_xref_memos

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| memo | longtext | NO |  |  |  |
| addedby | int | NO |  |  |  |
| addedon | bigint | NO |  | 0 |  |
| rowid | int | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **memo**: Memo/note text
- **addedby**: User who added the memo
- **addedon**: Timestamp when memo was added
- **rowid**: Reference to associated record

## viamed_xref_removed

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| stock_ref | varchar(124) | NO |  |  |  |
| oem | varchar(64) | NO |  |  |  |
| oem_pn | varchar(128) | NO |  |  |  |
| type | varchar(64) | NO |  |  |  |
| verified | varchar(64) | NO |  |  |  |
| updatedon | varchar(64) | NO |  |  |  |
| updatedby | varchar(64) | NO |  |  |  |
| xrefcode | varchar(64) | NO |  |  |  |
| combined | longtext | NO |  |  |  |
| primaryid | int | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **stock_ref**: Internal stock reference (up to 124 characters)
- **oem**: OEM name (up to 64 characters)
- **oem_pn**: OEM part number (up to 128 characters)
- **type**: Product type (up to 64 characters)
- **verified**: Verification status (up to 64 characters)
- **updatedon**: Update timestamp (stored as string, up to 64 characters)
- **updatedby**: User who updated (up to 64 characters)
- **xrefcode**: Cross-reference code (up to 64 characters)
- **combined**: Combined search field
- **primaryid**: Primary record identifier

## viamed_xref_specsheet

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| specsheet | varchar(100) | NO |  |  |  |
| xrefcode | varchar(64) | NO |  |  |  |
| stock_ref | varchar(32) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **specsheet**: Specification sheet name (up to 100 characters)
- **xrefcode**: Associated cross-reference code (up to 64 characters)
- **stock_ref**: Associated stock reference (up to 32 characters)

## viamed_xref_specsheet_documents

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int | NO | PRI |  | auto_increment |
| docid | int | NO |  |  |  |
| specsheetid | int | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **docid**: Document identifier
- **specsheetid**: Specification sheet identifier