# Innomaint API → IC3 Database Field Mapping

## Primary Asset Master (`ic3_asset_master`)

| API Field | Location | DB Column | Type | Notes |
|-----------|----------|-----------|------|-------|
| `serialnumber` | `data.serialnumber` | `serialnumber` | text | "Delhi Cantt Naraina_Trans_10" |
| `id` | `data.id` | `traceability_id` | int | 577842 |
| `traceid` | `data.traceid` | `traceability_id` | int | Same as `id` |
| `serialnumber` (input) | Request body | `cmms_asset_id` | text | Used as lookup key |
| `barcode` | `data.barcode` | `barcode` | text | "MOD136555/SER577842" |
| `criticality` | `data.criticality` | `criticality` | text | "High" |
| `capacity_rating` | `data.capacity_rating` | `capacity_rating` | text | "400KVA" |
| `ticketcount` | `data.ticketcount` | `ticketcount` | int | 2 |
| `schedulecount` | `data.schedulecount` | `schedulecount` | int | 3 |
| `schedule_configured` | `data.schedule_configured` | `schedule_configured` | int | 1 |
| `schedule_initiate` | `data.schedule_initiate` | `schedule_initiate` | int | 1 |
| `in_charge_person` | `data.in_charge_person` | `in_charge_person` | text | "JWIL" |
| `status` | `data.status` | `status` | int | 1 |
| `asset_live_status` | `data.asset_live_status` | `asset_live_status` | int | 1 |
| `iot_device_mapped` | `data.iot_device_mapped` | `iot_device_mapped` | int | 0 |
| `mapping_status` | `data.mapping_status` | `mapping_status` | int | 1 |
| `is_loaner` | `data.is_loaner` | `is_loaner` | int | 0 |
| `is_ble_tracking` | `data.is_ble_tracking` | `is_ble_tracking` | int | 0 |
| `is_rfid_tracking` | `data.is_rfid_tracking` | `is_rfid_tracking` | int | 0 |
| `is_qr_tracking` | `data.is_qr_tracking` | `is_qr_tracking` | int | 0 |
| `is_tracking_enabled` | `data.is_tracking_enabled` | `is_tracking_enabled` | int | 0 |

## Location Hierarchy (Foreign Keys)

| API Field | Location | DB Table | DB Column | Lookup Method |
|-----------|----------|----------|-----------|----------------|
| `building_id` | `data.building_id` | `ic3_building` | `building_id` | Direct (API value) |
| `building_name` | `data.building_name` | `ic3_building` | `building_name` | Insert with building_id |
| `floor_id` | `data.floor_id` | `ic3_floor_master` | `floor_id` | Direct (API value) |
| `floor_name` | `data.floor_name` | `ic3_floor_master` | `floor_name` | Insert with floor_id |
| `department_id` | `data.department_id` | `ic3_department_master` | `department_id` | Direct (API value) |
| `department_name` | `data.department_name` | `ic3_department_master` | `department_name` | Insert with department_id |
| `location_name` | `data.location_name` | `ic3_asset_master` | `location_id` | **Resolve by name lookup** |
| `customer_name` | `data.customer_name` | `ic3_asset_master` | `customer_id` | **Resolve by name lookup** |

## Performance Metrics (`ic3_asset_performance`)

| API Field | Location | DB Column | Type | Notes |
|-----------|----------|-----------|------|-------|
| `equipments_name` | `performance.equipments_name` | `equipments_name` | text | "Transformer" |
| `equipment_model_name` | `performance.equipment_model_name` | `equipment_model_name` | text | "76HC-022/21" |
| `criticality_type_label` | `performance.criticality_type_label` | `criticality_type_label` | text | "Non-Critical" |
| `total_ticket` | `performance.total_ticket` | `total_ticket` | int | 2 |
| `mttr` | `performance.mttr` | `mttr` | text | "00:04:00" |
| `mtbf` | `performance.mtbf` | `mtbf` | text | "12989:16:38" |
| `availability` | `performance.availability` | `availability` | text | "100" |
| `total_ticket_breakdown_hrs` | `performance.total_ticket_breakdown_hrs` | `total_ticket_breakdown_hrs` | text | "00:08:00" |
| `total_schedule_breakdown_hrs` | `performance.total_schedule_breakdown_hrs` | `total_schedule_breakdown_hrs` | text | "0" |
| `asset_created_date` | `performance.asset_created_date` | `asset_created_date` | text | "03/07/2023 18:48" |
| `total_actual_hrs` | `performance.total_actual_hrs` | `total_actual_hrs` | text | "25978:41:17" |

## Work Order Summary (`ic3_workorder_summary`)

### UPTO Period
| API Field | Location | DB Column | Type | Notes |
|-----------|----------|-----------|------|-------|
| `work_estimate_count` | `workorder_transactional.upto.work_estimate_count` | `work_estimate_count` | int | "3" |
| `work_estimate_cost` | `workorder_transactional.upto.work_estimate_cost` | `work_estimate_cost` | float | "0.00" |
| `schedule_count` | `workorder_transactional.upto.schedule_count` | `schedule_count` | int | "1" |
| `schedule_cost` | `workorder_transactional.upto.schedule_cost` | `schedule_cost` | float | "0.00" |
| `ticket_count` | `workorder_transactional.upto.ticket_count` | `ticket_count` | int | "2" |
| `ticket_cost` | `workorder_transactional.upto.ticket_cost` | `ticket_cost` | float | "0.00" |
| period_type | - | `period_type` | text | "upto" (hardcoded) |

### LAST_YEAR Period
| API Field | Location | DB Column | Type | Notes |
|-----------|----------|-----------|------|-------|
| `work_estimate_count` | `workorder_transactional.last_year.work_estimate_count` | `work_estimate_count` | int | "2" |
| `work_estimate_cost` | `workorder_transactional.last_year.work_estimate_cost` | `work_estimate_cost` | float | "0.00" |
| `schedule_count` | `workorder_transactional.last_year.schedule_count` | `schedule_count` | int | "1" |
| `schedule_cost` | `workorder_transactional.last_year.schedule_cost` | `schedule_cost` | float | "0.00" |
| `ticket_count` | `workorder_transactional.last_year.ticket_count` | `ticket_count` | int | "1" |
| `ticket_cost` | `workorder_transactional.last_year.ticket_cost` | `ticket_cost` | float | "0.00" |
| period_type | - | `period_type` | text | "last_year" (hardcoded) |

## Equipment Master (`ic3_equipment_master`) — From `assetIDDeatails`

| API Field | Location | DB Column | Type | Notes |
|-----------|----------|-----------|------|-------|
| `equipments_name` | `assetIDDeatails.equipments_name` | `equipments_name` | text | "Transformer" |
| `equipment_model_name` | `assetIDDeatails.equipment_model_name` | `equipment_model_name` | text | "76HC-022/21" |
| `equipment_description` | `assetIDDeatails.equipment_description` | `equipment_description` | text | "Transformer oil type" |
| `manufacturer_id` | `assetIDDeatails.manufacturer_id` | `manufacturer_id` | int | 19773 |
| `manufacturer_name` | `assetIDDeatails.manufacturer_name` | `manufacturer_name` | text | "Kirloskar" |
| `cat_id` | `assetIDDeatails.cat_id` | `cat_id` | int | 11369 |
| `asset_group_id` | `assetIDDeatails.subcategory_id` | `asset_group_id` | int | 12484 |
| `model_status` | `assetIDDeatails.model_status` | `model_status` | int | 1 |

## Summary of FK Resolution Pattern

```
Innomaint API sends integer IDs that are their internal system IDs.
Our database uses different integer IDs generated during Excel import.

RESOLUTION METHOD:
- Use API string identifiers (names) to look up local database IDs
- Then use local IDs for all FK relationships

Examples:
✓ API: customer_id=24463, customer_name="DJB Khyala"
  → Query: SELECT customer_id FROM ic3_customer_master WHERE customer_name='DJB Khyala'
  → Use returned local customer_id (not 24463)

✓ API: location_id=34801, location_name="Naraina D.Cantt UGR_Zone"
  → Query: SELECT location_id FROM ic3_location_master WHERE location_name='Naraina...'
  → Use returned local location_id (not 34801)

✓ API: building_id=16420, building_name="Naraina D.Cantt UGR_Zone"
  → Insert/Upsert with building_id=16420 (safe, use direct)
  → But customer_id FK → resolve via customer_name lookup
```

## Data Transformation Notes

| Field | Transformation | Reason |
|-------|-----------------|--------|
| `work_estimate_count` (string) | `strconv.Atoi()` | API sends strings, DB expects int |
| `work_estimate_cost` (string) | `strconv.ParseFloat(, 64)` | API sends strings, DB expects float64 |
| `mttr` (duration string) | Store as-is | "HH:MM:SS" format, store in text column |
| `mtbf` (duration string) | Store as-is | "HH:MM:SS" format, store in text column |
| `availability` (string) | Store as-is | Percentage as text "100" |
| `status` (int: 1,0) | Map directly | 1=Active, 0=Inactive |
| `asset_live_status` (int) | Map directly | 1=Live, 0=Offline |
| NULL handling | Use `nullInt()`, `nullStr()` | Innomaint may send null, convert to SQL NULL |

## Example: Complete Asset Flow

```
API REQUEST: { "serialnumber": "Delhi Cantt Naraina_Trans_10" }

API RESPONSE provides:
  data.id = 577842                          → traceability_id
  data.serialnumber = "Delhi Cantt..."      → serialnumber (stays same)
  data.criticality = "High"                 → criticality
  data.customer_name = "DJB Khyala"         → lookup customer_id
  data.location_name = "Naraina D.Cantt..." → lookup location_id
  performance.mttr = "00:04:00"             → store in ic3_asset_performance
  workorder_transactional.upto.ticket_count = "2" → parse & store in ic3_workorder_summary

DB INSERTS:
  1. ic3_asset_master.serialnumber = "Delhi Cantt Naraina_Trans_10"
  2. ic3_asset_master.traceability_id = 577842
  3. ic3_asset_master.customer_id = (SELECT customer_id FROM ic3_customer_master WHERE customer_name='DJB Khyala')
  4. ic3_asset_master.location_id = (SELECT location_id FROM ic3_location_master WHERE location_name='Naraina D.Cantt...')
  5. ic3_asset_performance row with mttr, mtbf, availability
  6. ic3_workorder_summary rows (upto + last_year)
```