# GSD: Fix Floor FK Error in EnrichAssetFromAPI
## File: `cmms_import.go`

---

## PROBLEM

```
{"error":"enrich: upsert floor: ERROR: insert or update on table \"ic3_floor_master\"
violates foreign key constraint \"ic3_floor_master_location_id_fkey\" (SQLSTATE 23503)"}
```

Same root cause as building FK fix:
- Innomaint API returns `location_id: 34801` (their internal ID)
- Our `ic3_location_master.location_id` was generated as Postgres serial during Excel import
- `34801` does not exist in our table → FK violation on `ic3_floor_master.location_id`

---

## FIX — Replace Step 2 in `EnrichAssetFromAPI`

Find this block:

```go
// 2. Upsert ic3_floor_master
if d.FloorID > 0 {
    _, err := tx.Exec(ctx, `
        INSERT INTO ic3_floor_master (floor_id, floor_name, location_id, asset_floor_id, created_at, updated_at)
        VALUES ($1, $2, $3, $4, NOW(), NOW())
        ON CONFLICT (floor_id) DO UPDATE SET
            floor_name = EXCLUDED.floor_name,
            updated_at = NOW()`,
        d.FloorID, d.FloorName, nullInt(d.LocationID), nullInt(d.AssetFloorID),
    )
    if err != nil {
        return fmt.Errorf("upsert floor: %w", err)
    }
}
```

Replace with:

```go
// 2. Upsert ic3_floor_master — resolve local location_id by name
if d.FloorID > 0 && d.FloorName != "" {
    var localLocID *int
    tx.QueryRow(ctx,
        `SELECT location_id FROM ic3_location_master WHERE location_name = $1 LIMIT 1`,
        d.LocationName,
    ).Scan(&localLocID)

    _, err := tx.Exec(ctx, `
        INSERT INTO ic3_floor_master (floor_id, floor_name, location_id, asset_floor_id, created_at, updated_at)
        VALUES ($1, $2, $3, $4, NOW(), NOW())
        ON CONFLICT (floor_id) DO UPDATE SET
            floor_name = EXCLUDED.floor_name,
            updated_at = NOW()`,
        d.FloorID, d.FloorName, localLocID, nullInt(d.AssetFloorID),
    )
    if err != nil {
        return fmt.Errorf("upsert floor: %w", err)
    }
}
```

---

## ALSO FIX Step 3 — ic3_department_master

`ic3_department_master` has FK on both `floor_id` and `location_id`.
`floor_id` from API (Innomaint) is their internal ID — need to pass it directly
since we just inserted it above (so it now exists). But `location_id` still needs
local resolution.

Find:

```go
// 3. Upsert ic3_department_master
if d.DepartmentID > 0 {
    _, err := tx.Exec(ctx, `
        INSERT INTO ic3_department_master (department_id, department_name, floor_id, location_id, asset_department_id, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
        ON CONFLICT (department_id) DO UPDATE SET
            department_name   = EXCLUDED.department_name,
            updated_at        = NOW()`,
        d.DepartmentID, d.DepartmentName,
        nullInt(d.FloorID), nullInt(d.LocationID), nullInt(d.AssetDepartmentID),
    )
    if err != nil {
        return fmt.Errorf("upsert department: %w", err)
    }
}
```

Replace with:

```go
// 3. Upsert ic3_department_master — floor_id already upserted above so safe to use
//    location_id resolved by name
if d.DepartmentID > 0 && d.DepartmentName != "" {
    var localLocID2 *int
    tx.QueryRow(ctx,
        `SELECT location_id FROM ic3_location_master WHERE location_name = $1 LIMIT 1`,
        d.LocationName,
    ).Scan(&localLocID2)

    _, err := tx.Exec(ctx, `
        INSERT INTO ic3_department_master (department_id, department_name, floor_id, location_id, asset_department_id, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
        ON CONFLICT (department_id) DO UPDATE SET
            department_name = EXCLUDED.department_name,
            updated_at      = NOW()`,
        d.DepartmentID, d.DepartmentName,
        nullInt(d.FloorID), localLocID2, nullInt(d.AssetDepartmentID),
    )
    if err != nil {
        return fmt.Errorf("upsert department: %w", err)
    }
}
```

---

## SUMMARY OF ALL FK RESOLUTIONS IN EnrichAssetFromAPI

| Table | Innomaint FK field | Resolution |
|---|---|---|
| `ic3_building.customer_id` | `d.CustomerID` | lookup by `d.CustomerName` → local ID |
| `ic3_floor_master.location_id` | `d.LocationID` | lookup by `d.LocationName` → local ID |
| `ic3_department_master.floor_id` | `d.FloorID` | safe — just inserted above in Step 2 |
| `ic3_department_master.location_id` | `d.LocationID` | lookup by `d.LocationName` → local ID |
| `ic3_asset_master.location_id` | resolved via `localLocID` | lookup by `d.LocationName` → local ID |

**Rule:** Never use Innomaint integer IDs directly as FK values. Always resolve to local IDs by name lookup.

---

## VERIFY

```bash
curl -X POST "http://localhost:9090/api/admin/cmms/sync/Delhi%20Cantt%20Naraina_BP_01" \
  -H "Authorization: Bearer <your_token>"
```

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

DB check:
```sql
SELECT cmms_asset_id, traceability_id, criticality, capacity_rating,
       building_id, floor_id, department_id, location_id,
       ticketcount, schedulecount, last_synced_at
FROM ic3_asset_master
WHERE cmms_asset_id = 'Delhi Cantt Naraina_BP_01';
```