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

---

## PROBLEM

```
{"error":"enrich: upsert building: ERROR: insert or update on table \"ic3_building\"
violates foreign key constraint \"ic3_building_customer_id_fkey\" (SQLSTATE 23503)"}
```

`ic3_building.customer_id` is a FK → `ic3_customer_master.customer_id`.

The Innomaint API returns `customer_id: 24463` (their internal ID).
Our `ic3_customer_master` was populated from Excel import with Postgres-generated serial IDs — not Innomaint's IDs.

So `customer_id = 24463` does not exist in our `ic3_customer_master` → FK violation.

---

## FIX

In `cmms_import.go`, find the function `EnrichAssetFromAPI`.

Find **Step 1 — Upsert ic3_building** which currently looks like:

```go
// 1. Upsert ic3_building
if d.BuildingID > 0 {
    _, err := tx.Exec(ctx, `
        INSERT INTO ic3_building (building_id, customer_id, building_name, created_at, updated_at)
        VALUES ($1, $2, $3, NOW(), NOW())
        ON CONFLICT (building_id) DO UPDATE SET
            building_name = EXCLUDED.building_name,
            updated_at    = NOW()`,
        d.BuildingID, nullInt(d.CustomerID), d.BuildingName,
    )
    if err != nil {
        return fmt.Errorf("upsert building: %w", err)
    }
}
```

**Replace the entire Step 1 block** with this:

```go
// 1. Upsert ic3_building
// Resolve local customer_id by customer_name (not Innomaint's integer customer_id)
// because our ic3_customer_master was seeded from Excel with Postgres serial IDs
if d.BuildingID > 0 && d.BuildingName != "" {
    var localCustID *int
    tx.QueryRow(ctx,
        `SELECT customer_id FROM ic3_customer_master WHERE customer_name = $1 LIMIT 1`,
        d.CustomerName,
    ).Scan(&localCustID)

    _, err := tx.Exec(ctx, `
        INSERT INTO ic3_building (building_id, customer_id, building_name, created_at, updated_at)
        VALUES ($1, $2, $3, NOW(), NOW())
        ON CONFLICT (building_id) DO UPDATE SET
            building_name = EXCLUDED.building_name,
            updated_at    = NOW()`,
        d.BuildingID, localCustID, d.BuildingName,
    )
    if err != nil {
        return fmt.Errorf("upsert building: %w", err)
    }
}
```

---

## ALSO UPDATE — asset_master customer_id resolution

In the same `EnrichAssetFromAPI` function, find **Step 4 — UPDATE ic3_asset_master**.

The current UPDATE does not touch `customer_id` — that is correct, leave it as is.
The `customer_id` in `ic3_asset_master` was already set correctly during Excel import.

**No change needed to Step 4.**

---

## ALSO UPDATE — location_id resolution in asset_master UPDATE

Currently Step 4 sets many fields but NOT `location_id`. The API gives us
`location_id: 34801` (Innomaint's ID) which also won't match our local
`ic3_location_master.location_id`.

Add this local location_id resolution **before the big UPDATE in Step 4**:

```go
// Resolve local location_id by location_name
var localLocID *int
tx.QueryRow(ctx,
    `SELECT location_id FROM ic3_location_master WHERE location_name = $1 LIMIT 1`,
    d.LocationName,
).Scan(&localLocID)
```

Then in the big UPDATE SQL, add one more SET line:

```go
// In the UPDATE ic3_asset_master SET block, add after in_charge_person:
location_id = $33,
```

And add `localLocID` as `$33` at the end of the args list.

---

## COMPLETE EnrichAssetFromAPI after fix

The function should look like this in full:

```go
func (db *DB) EnrichAssetFromAPI(ctx context.Context, tx pgx.Tx, apiResp *InnomaintAPIResponse) error {
    d := apiResp.Response.Data
    p := apiResp.Response.Performance
    wo := apiResp.Response.WorkorderTransactional

    // 1. Upsert ic3_building — use local customer_id resolved by name
    if d.BuildingID > 0 && d.BuildingName != "" {
        var localCustID *int
        tx.QueryRow(ctx,
            `SELECT customer_id FROM ic3_customer_master WHERE customer_name = $1 LIMIT 1`,
            d.CustomerName,
        ).Scan(&localCustID)

        _, err := tx.Exec(ctx, `
            INSERT INTO ic3_building (building_id, customer_id, building_name, created_at, updated_at)
            VALUES ($1, $2, $3, NOW(), NOW())
            ON CONFLICT (building_id) DO UPDATE SET
                building_name = EXCLUDED.building_name,
                updated_at    = NOW()`,
            d.BuildingID, localCustID, d.BuildingName,
        )
        if err != nil {
            return fmt.Errorf("upsert building: %w", err)
        }
    }

    // 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)
        }
    }

    // 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)
        }
    }

    // Resolve local location_id by name (Innomaint location_id ≠ our location_id)
    var localLocID *int
    tx.QueryRow(ctx,
        `SELECT location_id FROM ic3_location_master WHERE location_name = $1 LIMIT 1`,
        d.LocationName,
    ).Scan(&localLocID)

    // 4. Update ic3_asset_master — enrich with API data
    _, err := tx.Exec(ctx, `
        UPDATE ic3_asset_master SET
            traceability_id                             = $2,
            barcode                                     = $3,
            criticality                                 = $4,
            capacity_rating                             = $5,
            building_id                                 = $6,
            floor_id                                    = $7,
            department_id                               = $8,
            asset_building_id                           = $9,
            asset_floor_id                              = $10,
            asset_department_id                         = $11,
            fk_customers_equipment_mapping_id           = $12,
            customers_locations_serialnumber_mapping_id = $13,
            customer_traceability_id                    = $14,
            customer_model_id                           = $15,
            location_traceability_id                    = $16,
            location_model_id                           = $17,
            in_charge_person                            = $18,
            ticketcount                                 = $19,
            schedulecount                               = $20,
            schedule_configured                         = $21,
            schedule_initiate                           = $22,
            iot_device_mapped                           = $23,
            status                                      = $24,
            asset_live_status                           = $25,
            mapping_status                              = $26,
            traceability_status                         = $27,
            is_loaner                                   = $28,
            is_ble_tracking                             = $29,
            is_rfid_tracking                            = $30,
            is_qr_tracking                              = $31,
            is_tracking_enabled                         = $32,
            location_id                                 = COALESCE($33, location_id),
            last_synced_at                              = NOW(),
            sync_version                                = COALESCE(sync_version, 0) + 1,
            updated_at                                  = NOW()
        WHERE cmms_asset_id = $1`,
        d.Serialnumber,
        d.TraceID,
        nullStr(d.Barcode),
        nullStr(d.Criticality),
        nullStr(d.CapacityRating),
        nullInt(d.BuildingID),
        nullInt(d.FloorID),
        nullInt(d.DepartmentID),
        nullInt(d.AssetBuildingID),
        nullInt(d.AssetFloorID),
        nullInt(d.AssetDepartmentID),
        nullInt(d.FkCustomersEquipmentMappingID),
        nullInt(d.CustomersLocationsSerialMappingID),
        nullInt(d.CustomerTraceabilityID),
        nullInt(d.CustomerModelID),
        nullInt(d.LocationTraceabilityID),
        nullInt(d.LocationModelID),
        nullStr(d.InChargePerson),
        d.Ticketcount,
        d.Schedulecount,
        d.ScheduleConfigured,
        d.ScheduleInitiate,
        d.IotDeviceMapped,
        d.Status,
        d.AssetLiveStatus,
        d.MappingStatus,
        d.TraceabilityStatus,
        d.IsLoaner,
        d.IsBleTracking,
        d.IsRfidTracking,
        d.IsQrTracking,
        d.IsTrackingEnabled,
        localLocID,
    )
    if err != nil {
        return fmt.Errorf("update asset_master: %w", err)
    }

    // Get local asset_id for child table inserts
    var assetID int
    err = tx.QueryRow(ctx,
        `SELECT asset_id FROM ic3_asset_master WHERE cmms_asset_id = $1`,
        d.Serialnumber,
    ).Scan(&assetID)
    if err != nil {
        return fmt.Errorf("get asset_id: %w", err)
    }

    // 5. Upsert ic3_asset_performance
    _, err = tx.Exec(ctx, `
        INSERT INTO ic3_asset_performance (
            asset_id, serialnumber, equipments_name, equipment_model_name,
            criticality_type_label, total_ticket, mttr, mtbf, availability,
            total_ticket_breakdown_hrs, total_schedule_breakdown_hrs,
            asset_created_date, total_actual_hrs, recorded_at
        ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,NOW())
        ON CONFLICT (asset_id) DO UPDATE SET
            total_ticket                 = EXCLUDED.total_ticket,
            mttr                         = EXCLUDED.mttr,
            mtbf                         = EXCLUDED.mtbf,
            availability                 = EXCLUDED.availability,
            total_ticket_breakdown_hrs   = EXCLUDED.total_ticket_breakdown_hrs,
            total_schedule_breakdown_hrs = EXCLUDED.total_schedule_breakdown_hrs,
            total_actual_hrs             = EXCLUDED.total_actual_hrs,
            recorded_at                  = NOW()`,
        assetID, d.Serialnumber,
        p.EquipmentsName, p.EquipmentModelName, p.CriticalityTypeLabel,
        p.TotalTicket, p.Mttr, p.Mtbf, p.Availability,
        p.TotalTicketBreakdownHrs, p.TotalScheduleBreakdownHrs,
        p.AssetCreatedDate, p.TotalActualHrs,
    )
    if err != nil {
        return fmt.Errorf("upsert performance: %w", err)
    }

    // 6. Upsert ic3_workorder_summary — two rows (upto + last_year)
    for _, period := range []struct {
        pType string
        wop   InnomaintWOPeriod
    }{
        {"upto", wo.Upto},
        {"last_year", wo.LastYear},
    } {
        weCount, _ := strconv.Atoi(period.wop.WorkEstimateCount)
        weCost, _  := strconv.ParseFloat(period.wop.WorkEstimateCost, 64)
        scCount, _ := strconv.Atoi(period.wop.ScheduleCount)
        scCost, _  := strconv.ParseFloat(period.wop.ScheduleCost, 64)
        tkCount, _ := strconv.Atoi(period.wop.TicketCount)
        tkCost, _  := strconv.ParseFloat(period.wop.TicketCost, 64)

        _, err = tx.Exec(ctx, `
            INSERT INTO ic3_workorder_summary (
                asset_id, period_type,
                work_estimate_count, work_estimate_cost,
                schedule_count, schedule_cost,
                ticket_count, ticket_cost, updated_at
            ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,NOW())
            ON CONFLICT (asset_id, period_type) DO UPDATE SET
                work_estimate_count = EXCLUDED.work_estimate_count,
                work_estimate_cost  = EXCLUDED.work_estimate_cost,
                schedule_count      = EXCLUDED.schedule_count,
                schedule_cost       = EXCLUDED.schedule_cost,
                ticket_count        = EXCLUDED.ticket_count,
                ticket_cost         = EXCLUDED.ticket_cost,
                updated_at          = NOW()`,
            assetID, period.pType,
            weCount, weCost, scCount, scCost, tkCount, tkCost,
        )
        if err != nil {
            return fmt.Errorf("upsert workorder %s: %w", period.pType, err)
        }
    }

    return nil
}
```

---

## VERIFY AFTER FIX

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

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

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