# Innomaint API Sync Implementation Guide

## Overview

This implementation adds real-time asset enrichment from the Innomaint CMMS API to IC³. The system fetches detailed asset information for each of the 2957 imported assets and enriches the database with:

- **Asset Master enrichment**: traceability_id, barcode, criticality (API overrides Excel)
- **Location hierarchy**: building_id, floor_id, department_id (not in Excel)
- **Counts & refs**: ticketcount, schedulecount, capacity_rating, in_charge_person
- **Performance metrics**: mttr, mtbf, availability (→ ic3_asset_performance table)
- **Work order summary**: upto + last_year counts (→ ic3_workorder_summary table)
- **Location masters**: Automatic upsert of ic3_building, ic3_floor_master, ic3_department_master

## Files Modified

| File | Changes |
|---|---|
| `cmms_import.go` | Added Innomaint API structs, FetchInnomaintAssetDetail(), EnrichAssetFromAPI(), SyncAllAssetsFromInnomaint() |
| `handlers.go` | Added syncInnomaintAssetsHandler(), syncSingleAssetHandler() |
| `main.go` | Registered two new routes |
| `migrations_innomaint_sync.sql` | Added unique constraints for ON CONFLICT upserts |

## API Endpoints

### 1. Batch Sync (All Assets)
**POST** `/api/admin/cmms/sync?batch=500&offset=0`

Sync assets in batches with optional pagination.

**Query Parameters:**
- `batch` (default 100): How many assets to sync per call
- `offset` (default 0): Skip first N assets (for resume/pagination)

**Example:**
```bash
# First 100 assets
curl -X POST "http://localhost:9090/api/admin/cmms/sync?batch=100&offset=0"

# Next 100 assets
curl -X POST "http://localhost:9090/api/admin/cmms/sync?batch=100&offset=100"
```

**Response:**
```json
{
  "total": 100,
  "success": 98,
  "failed": 2,
  "skipped": 0,
  "errors": [
    "asset_id_1: API status 404",
    "asset_id_2: http: connection refused"
  ],
  "duration_ms": "18523"
}
```

### 2. Single Asset Sync
**POST** `/api/admin/cmms/sync/{id}`

Sync one asset by cmms_asset_id — useful for testing and on-demand refresh.

**Example:**
```bash
curl -X POST "http://localhost:9090/api/admin/cmms/sync/Delhi%20Cantt%20Naraina_BP_01"
```

**Response:**
```json
{
  "status": "synced",
  "cmms_asset_id": "Delhi Cantt Naraina_BP_01",
  "traceability_id": 577833
}
```

## Testing Sequence

### Step 1: Run SQL Migrations

Connect to the PostgreSQL database and run:

```sql
-- From migrations_innomaint_sync.sql
ALTER TABLE ic3_asset_performance
    ADD CONSTRAINT uq_performance_asset_id UNIQUE (asset_id);

ALTER TABLE ic3_workorder_summary
    ADD CONSTRAINT uq_workorder_asset_period UNIQUE (asset_id, period_type);
```

**Note:** If constraints already exist, you'll see a "duplicate key value violates" error — this is safe to ignore.

### Step 2: Test Single Asset First

```bash
curl -X POST "http://localhost:9090/api/admin/cmms/sync/Delhi%20Cantt%20Naraina_BP_01"
```

Expected response:
```json
{
  "status": "synced",
  "cmms_asset_id": "Delhi Cantt Naraina_BP_01",
  "traceability_id": 577833
}
```

### Step 3: Verify DB Updates

```sql
SELECT asset_id, cmms_asset_id, traceability_id, criticality, ticketcount,
       building_id, floor_id, department_id, last_synced_at, sync_version
FROM ic3_asset_master
WHERE cmms_asset_id = 'Delhi Cantt Naraina_BP_01';
```

Expected: Row should show updated traceability_id, criticality, building_id, etc.

```sql
SELECT * FROM ic3_asset_performance
WHERE asset_id = (
    SELECT asset_id FROM ic3_asset_master WHERE cmms_asset_id = 'Delhi Cantt Naraina_BP_01'
);
```

Expected: One row with mttr, mtbf, availability, etc.

### Step 4: Batch Sync (100 assets at a time)

```bash
# First batch
curl -X POST "http://localhost:9090/api/admin/cmms/sync?batch=100&offset=0"

# If successful, continue with next batches
curl -X POST "http://localhost:9090/api/admin/cmms/sync?batch=100&offset=100"
curl -X POST "http://localhost:9090/api/admin/cmms/sync?batch=100&offset=200"
# ... repeat until Total = Success + Failed
```

Track progress with:
```sql
SELECT COUNT(*) as synced FROM ic3_asset_master WHERE last_synced_at IS NOT NULL AND sync_version > 0;
SELECT COUNT(*) as total FROM ic3_asset_master WHERE cmms_asset_id IS NOT NULL;
```

### Step 5: Check Sync Log

```sql
SELECT status, COUNT(*) FROM ic3_sync_log
WHERE sync_type = 'api_sync'
GROUP BY status;
```

Expected: Most should show 'success', a few may show 'error' for assets that no longer exist in Innomaint.

### Step 6: Check for Data Quality Issues

```sql
-- Assets still missing key enrichment
SELECT asset_id, cmms_asset_id, traceability_id, building_id, floor_id, department_id
FROM ic3_asset_master
WHERE last_synced_at IS NOT NULL
  AND (traceability_id IS NULL OR building_id IS NULL OR department_id IS NULL)
LIMIT 20;
```

## Implementation Details

### Rate Limiting
- **200ms** between API requests to be polite to Innomaint's servers
- Configurable via `innomaintRateMS` constant in cmms_import.go

### Transaction Strategy
- **One transaction per asset** — if one asset fails, others still get synced
- Rollback only that asset's enrichment, not the entire batch
- Failures logged to `ic3_sync_log` table

### Idempotency
- **sync_version** counter increments on each sync — tracks how many times an asset has been updated
- **last_synced_at** timestamp — tracks when the asset was last enriched
- **ON CONFLICT DO UPDATE** — safe to run multiple times without duplicates

### Error Handling
- **404 errors**: Asset no longer exists in Innomaint — logged but doesn't stop batch
- **Network errors**: Retried via timeout logic; eventually logged as failed
- **Parse errors**: Unexpected API response shape — logged with full error message

## Performance Characteristics

### Single Asset Sync
- **Time**: ~10-15 seconds (includes 10s HTTP timeout)
- **DB overhead**: ~1 transaction, 4-6 SQL statements

### Batch Sync (100 assets)
- **Time**: ~20-30 seconds (100 × 200ms rate limit + network overhead)
- **Throughput**: 3-5 assets/second
- **Full sync (2957 assets)**: ~10-15 minutes

### Full Sync Strategy (Recommended)
```bash
# Run in background or scheduled job
for offset in 0 100 200 300 ... 2900; do
    curl -X POST "http://localhost:9090/api/admin/cmms/sync?batch=100&offset=$offset"
    sleep 2  # Small delay between batches
done
```

## API Response Schemas

### Innomaint API Request
```json
{
  "serialnumber": "Delhi Cantt Naraina_BP_01"
}
```

### Innomaint API Response (Abbreviated)
```json
{
  "status": true,
  "response_code": 200,
  "response": {
    "data": {
      "id": 577833,
      "traceid": 577833,
      "serialnumber": "Delhi Cantt Naraina_BP_01",
      "barcode": "MOD135692/SER577833",
      "criticality": "High",
      "capacity_rating": "90KW",
      "building_id": 16420,
      "building_name": "Naraina D.Cantt UGR_Zone",
      "floor_id": 12030,
      "floor_name": "UGR",
      "department_id": 12250,
      "department_name": "Working",
      "ticketcount": 5,
      "schedulecount": 44,
      "in_charge_person": "JWIL"
    },
    "performance": {
      "equipments_name": "LT-Panel",
      "total_ticket": 2,
      "mttr": "00:08:30",
      "mtbf": "8526:06:43",
      "availability": "100"
    },
    "workorder_transactional": {
      "upto": {
        "ticket_count": "2",
        "schedule_count": "0"
      },
      "last_year": {
        "ticket_count": "0",
        "schedule_count": "0"
      }
    }
  }
}
```

## Troubleshooting

### "API returned status=false"
- Asset no longer exists in Innomaint
- Check Innomaint console for asset status
- Safe to skip — marked as failed in sync log

### "API status 502"
- Innomaint server temporarily unavailable
- Retry the asset later

### "http: connection refused"
- Network connectivity issue
- Check if Innomaint domain is reachable
- Verify firewall rules if behind corporate proxy

### "decode: invalid character"
- Unexpected API response format
- Check if Innomaint API structure changed
- Contact Innomaint support

### Synced assets not showing in UI
- Check `last_synced_at` and `sync_version` fields were updated
- UI may need to refresh or clear cache
- Verify `traceability_id` was populated

## Security Considerations

### API Token
- Token is hardcoded in `innomaintRateMS` (secure storage recommended for production)
- Only admin users can trigger sync endpoints (protected by `adminOnly()` middleware)
- Sync data is internal — no sensitive customer data exposed

### Rate Limiting
- 200ms between requests prevents DOS-ing Innomaint API
- Batch size defaults to 100 to avoid memory issues on large syncs
- 15-30 second timeouts prevent hung requests

## Next Steps

1. **Schedule regular syncs** — Set up a cron job or background task to sync assets daily/weekly
2. **Monitor sync log** — Set up alerts for high failure rates
3. **Feed back to Excel** — Export sync results back to original XLSX for auditing
4. **Mobile integration** — Expose traceability_id and building_id in mobile app