# GSD: Fix BulkImportAssets — Wrong Column Names
## File: `cmms_import.go`

---

## PROBLEM

```
{"error":"batch close: ERROR: column \"asset_name\" of relation \"ic3_asset_master\"
does not exist (SQLSTATE 42703)"}
```

The existing `BulkImportAssets` function inserts into columns that don't exist
in `ic3_asset_master`:
- `asset_name` → doesn't exist (correct col is `serialnumber`)
- `asset_category` → doesn't exist (correct col is `asset_cat_id` FK)
- `asset_sub_category` → doesn't exist (correct col is `asset_group_id` FK)
- `customer_name` → doesn't exist (correct col is `customer_id` FK)
- `location_name` → doesn't exist (correct col is `location_id` FK)
- `serial_no` → doesn't exist (correct col is `serialnumber`)
- `schedule_count_configured` → doesn't exist (correct col is `schedule_configured`)
- `purchase_number` → doesn't exist (correct col is `po_number`)

---

## FIX — Replace the entire `BulkImportAssets` function

Find the function `BulkImportAssets` in `cmms_import.go` and replace it
completely with the following:

```go
// BulkImportAssets upserts all master tables then ic3_asset_master.
// Insertion order respects FK dependencies:
//   ic3_customer_master → ic3_location_master → ic3_manufacturer
//   → ic3_asset_category → ic3_asset_group → ic3_equipment_master
//   → ic3_asset_master
// replace=true truncates ic3_asset_master CASCADE before inserting.
func (db *DB) BulkImportAssets(ctx context.Context, rows []AssetRow, replace bool) (ImportResult, error) {
	start := time.Now()
	res := ImportResult{Total: len(rows)}

	tx, err := db.pool.Begin(ctx)
	if err != nil {
		return res, fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback(ctx)

	if replace {
		if _, err := tx.Exec(ctx, `TRUNCATE ic3_asset_master CASCADE`); err != nil {
			return res, fmt.Errorf("truncate: %w", err)
		}
	}

	// Step 1: upsert all lookup/master tables, collect ID maps
	customerIDs, err := upsertCustomers(ctx, tx, rows)
	if err != nil {
		return res, fmt.Errorf("upsert customers: %w", err)
	}

	locationIDs, err := upsertLocations(ctx, tx, rows, customerIDs)
	if err != nil {
		return res, fmt.Errorf("upsert locations: %w", err)
	}

	manufacturerIDs, err := upsertManufacturers(ctx, tx, rows)
	if err != nil {
		return res, fmt.Errorf("upsert manufacturers: %w", err)
	}

	categoryIDs, err := upsertCategories(ctx, tx, rows)
	if err != nil {
		return res, fmt.Errorf("upsert categories: %w", err)
	}

	groupIDs, err := upsertAssetGroups(ctx, tx, rows, categoryIDs)
	if err != nil {
		return res, fmt.Errorf("upsert asset groups: %w", err)
	}

	equipmentIDs, err := upsertEquipment(ctx, tx, rows, categoryIDs, groupIDs, manufacturerIDs)
	if err != nil {
		return res, fmt.Errorf("upsert equipment: %w", err)
	}

	// Step 2: upsert ic3_asset_master in chunks of 500
	const chunkSize = 500
	for start2 := 0; start2 < len(rows); start2 += chunkSize {
		end := start2 + chunkSize
		if end > len(rows) {
			end = len(rows)
		}
		chunk := rows[start2:end]

		batch := &pgx.Batch{}
		for _, r := range chunk {
			custID := customerIDs[r.CustomerName]
			locID := locationIDs[r.Location]
			catID := categoryIDs[r.Category]
			grpID := groupIDs[r.SubCategory]
			eqID := equipmentIDs[equipKey(r.AssetName, r.AssetModel)]

			batch.Queue(`
				INSERT INTO ic3_asset_master (
					serialnumber,
					cmms_asset_id,
					source_system_id,
					customer_id,
					location_id,
					asset_cat_id,
					asset_group_id,
					model_id,
					criticality,
					status,
					asset_status,
					asset_live_status,
					schedule_configured,
					schedule_initiate,
					schedule_assigned,
					schedulecount,
					ticketcount,
					po_number,
					purchase_date,
					purchase_value,
					service_provider_name,
					inactive_reason,
					report_seq_no,
					data_source,
					last_synced_at,
					created_at,
					updated_at
				) VALUES (
					$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,
					$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,
					$21,$22,$23,$24,NOW(),NOW(),NOW()
				)
				ON CONFLICT (cmms_asset_id) DO UPDATE SET
					serialnumber        = EXCLUDED.serialnumber,
					customer_id         = EXCLUDED.customer_id,
					location_id         = EXCLUDED.location_id,
					asset_cat_id        = EXCLUDED.asset_cat_id,
					asset_group_id      = EXCLUDED.asset_group_id,
					model_id            = EXCLUDED.model_id,
					criticality         = EXCLUDED.criticality,
					status              = EXCLUDED.status,
					asset_live_status   = EXCLUDED.asset_live_status,
					schedule_configured = EXCLUDED.schedule_configured,
					schedule_initiate   = EXCLUDED.schedule_initiate,
					schedule_assigned   = EXCLUDED.schedule_assigned,
					po_number           = EXCLUDED.po_number,
					purchase_date       = EXCLUDED.purchase_date,
					purchase_value      = EXCLUDED.purchase_value,
					service_provider_name = EXCLUDED.service_provider_name,
					inactive_reason     = EXCLUDED.inactive_reason,
					sync_version        = COALESCE(ic3_asset_master.sync_version, 0) + 1,
					last_synced_at      = NOW(),
					updated_at          = NOW()`,
				r.AssetName,                    // $1  serialnumber
				r.AssetID,                      // $2  cmms_asset_id (Innomaint Asset ID string)
				"INNOMAINT",                    // $3  source_system_id
				nullInt(custID),                // $4  customer_id
				nullInt(locID),                 // $5  location_id
				nullInt(catID),                 // $6  asset_cat_id
				nullInt(grpID),                 // $7  asset_group_id
				nullInt(eqID),                  // $8  model_id
				r.AssetCriticality,             // $9  criticality
				r.Status,                       // $10 status
				r.Status,                       // $11 asset_status
				1,                              // $12 asset_live_status
				r.ScheduleConfigured,           // $13 schedule_configured
				r.ScheduleInitiated,            // $14 schedule_initiate
				nullStr(r.ScheduleAssigned),    // $15 schedule_assigned
				r.ScheduleConfigured,           // $16 schedulecount
				0,                              // $17 ticketcount
				nullStr(r.PurchaseNumber),      // $18 po_number
				r.PurchaseDate,                 // $19 purchase_date
				r.PurchaseValue,                // $20 purchase_value
				nullStr(r.ServiceProviderName), // $21 service_provider_name
				nullStr(r.ReasonForInactive),   // $22 inactive_reason
				r.SeqNo,                        // $23 report_seq_no
				"IMPORT",                       // $24 data_source
			)
		}

		br := tx.SendBatch(ctx, batch)
		for range chunk {
			tag, err := br.Exec()
			if err != nil {
				res.Skipped++
				log.Printf("BulkImportAssets row skip: %v", err)
				continue
			}
			if tag.RowsAffected() == 1 {
				res.Inserted++
			} else {
				res.Updated++
			}
		}
		if err := br.Close(); err != nil {
			return res, fmt.Errorf("batch close: %w", err)
		}
	}

	// Step 3: refresh aggregation counts
	if err := refreshAggregations(ctx, tx); err != nil {
		return res, fmt.Errorf("refresh aggregations: %w", err)
	}

	// Step 4: log to cmms_sync_log
	tx.Exec(ctx, `
		INSERT INTO cmms_sync_log (sync_type, status, assets_synced, duration_ms)
		VALUES ('bulk_import','success',$1,$2)`,
		res.Inserted+res.Updated,
		int(time.Since(start).Milliseconds()),
	)

	if err := tx.Commit(ctx); err != nil {
		return res, fmt.Errorf("commit: %w", err)
	}
	res.Duration = strconv.FormatInt(time.Since(start).Milliseconds(), 10)
	return res, nil
}
```

---

## ALSO ADD these helper functions if not already present in cmms_import.go

Check if these functions exist. If not, add them:

```go
func upsertCustomers(ctx context.Context, tx pgx.Tx, rows []AssetRow) (map[string]int, error) {
	seen := map[string]struct{}{}
	for _, r := range rows {
		if r.CustomerName != "" {
			seen[r.CustomerName] = struct{}{}
		}
	}
	ids := map[string]int{}
	for name := range seen {
		var id int
		err := tx.QueryRow(ctx, `
			INSERT INTO ic3_customer_master (customer_name, customer_status, source_system_id, created_at, updated_at)
			VALUES ($1, 1, 'INNOMAINT', NOW(), NOW())
			ON CONFLICT (customer_name) DO UPDATE SET updated_at = NOW()
			RETURNING customer_id`, name).Scan(&id)
		if err != nil {
			return nil, fmt.Errorf("customer %q: %w", name, err)
		}
		ids[name] = id
	}
	return ids, nil
}

func upsertLocations(ctx context.Context, tx pgx.Tx, rows []AssetRow, customerIDs map[string]int) (map[string]int, error) {
	type locKey struct{ loc, cust string }
	seen := map[locKey]struct{}{}
	for _, r := range rows {
		if r.Location != "" {
			seen[locKey{r.Location, r.CustomerName}] = struct{}{}
		}
	}
	ids := map[string]int{}
	for k := range seen {
		custID := nullInt(customerIDs[k.cust])
		var id int
		err := tx.QueryRow(ctx, `
			INSERT INTO ic3_location_master (location_name, customer_id, location_status, source_system_id, created_at, updated_at)
			VALUES ($1, $2, 1, 'INNOMAINT', NOW(), NOW())
			ON CONFLICT (location_name) DO UPDATE SET customer_id = EXCLUDED.customer_id, updated_at = NOW()
			RETURNING location_id`, k.loc, custID).Scan(&id)
		if err != nil {
			return nil, fmt.Errorf("location %q: %w", k.loc, err)
		}
		ids[k.loc] = id
	}
	return ids, nil
}

func upsertManufacturers(ctx context.Context, tx pgx.Tx, rows []AssetRow) (map[string]int, error) {
	seen := map[string]struct{}{}
	for _, r := range rows {
		if r.Manufacturer != "" {
			seen[r.Manufacturer] = struct{}{}
		}
	}
	ids := map[string]int{}
	for name := range seen {
		var id int
		err := tx.QueryRow(ctx, `
			INSERT INTO ic3_manufacturer (manufacturer_name, manufacturer_status, created_at)
			VALUES ($1, 1, NOW())
			ON CONFLICT (manufacturer_name) DO UPDATE SET manufacturer_status = 1
			RETURNING manufacturer_id`, name).Scan(&id)
		if err != nil {
			return nil, fmt.Errorf("manufacturer %q: %w", name, err)
		}
		ids[name] = id
	}
	return ids, nil
}

func upsertCategories(ctx context.Context, tx pgx.Tx, rows []AssetRow) (map[string]int, error) {
	seen := map[string]struct{}{}
	for _, r := range rows {
		cat := r.Category
		if cat == "" {
			cat = "Uncategorised"
		}
		seen[cat] = struct{}{}
	}
	ids := map[string]int{}
	for name := range seen {
		var id int
		err := tx.QueryRow(ctx, `
			INSERT INTO ic3_asset_category (category_name, category_status, created_at)
			VALUES ($1, 1, NOW())
			ON CONFLICT (category_name) DO UPDATE SET category_status = 1
			RETURNING cat_id`, name).Scan(&id)
		if err != nil {
			return nil, fmt.Errorf("category %q: %w", name, err)
		}
		ids[name] = id
	}
	return ids, nil
}

func upsertAssetGroups(ctx context.Context, tx pgx.Tx, rows []AssetRow, categoryIDs map[string]int) (map[string]int, error) {
	type grpKey struct{ sub, cat string }
	seen := map[grpKey]struct{}{}
	for _, r := range rows {
		if r.SubCategory != "" {
			seen[grpKey{r.SubCategory, r.Category}] = struct{}{}
		}
	}
	ids := map[string]int{}
	for k := range seen {
		catID := nullInt(categoryIDs[k.cat])
		var id int
		err := tx.QueryRow(ctx, `
			INSERT INTO ic3_asset_group (asset_group_name, cat_id, asset_group_status, created_at)
			VALUES ($1, $2, 1, NOW())
			ON CONFLICT (asset_group_name) DO UPDATE SET cat_id = EXCLUDED.cat_id
			RETURNING subcategory_id`, k.sub, catID).Scan(&id)
		if err != nil {
			return nil, fmt.Errorf("asset group %q: %w", k.sub, err)
		}
		ids[k.sub] = id
	}
	return ids, nil
}

func equipKey(name, model string) string {
	return name + "||" + model
}

func upsertEquipment(ctx context.Context, tx pgx.Tx, rows []AssetRow,
	categoryIDs, groupIDs, manufacturerIDs map[string]int,
) (map[string]int, error) {
	type eqEntry struct{ name, model, cat, sub, mfr string }
	seen := map[string]eqEntry{}
	for _, r := range rows {
		k := equipKey(r.AssetName, r.AssetModel)
		if _, ok := seen[k]; !ok {
			seen[k] = eqEntry{r.AssetName, r.AssetModel, r.Category, r.SubCategory, r.Manufacturer}
		}
	}
	ids := map[string]int{}
	for k, e := range seen {
		catID := nullInt(categoryIDs[e.cat])
		grpID := nullInt(groupIDs[e.sub])
		mfrID := nullInt(manufacturerIDs[e.mfr])
		modelName := e.model
		if modelName == "" {
			modelName = "NA"
		}
		var id int
		err := tx.QueryRow(ctx, `
			INSERT INTO ic3_equipment_master (
				equipments_name, equipment_model_name, equipment_description,
				cat_id, asset_group_id, manufacturer_id, manufacturer_name,
				model_status, created_at
			) VALUES ($1,$2,$3,$4,$5,$6,$7,1,NOW())
			ON CONFLICT (equipments_name, equipment_model_name) DO UPDATE SET
				cat_id          = EXCLUDED.cat_id,
				asset_group_id  = EXCLUDED.asset_group_id,
				manufacturer_id = EXCLUDED.manufacturer_id
			RETURNING model_id`,
			e.name, modelName, e.name, catID, grpID, mfrID, nullStr(e.mfr),
		).Scan(&id)
		if err != nil {
			return nil, fmt.Errorf("equipment %q: %w", k, err)
		}
		ids[k] = id
	}
	return ids, nil
}

func refreshAggregations(ctx context.Context, tx pgx.Tx) error {
	_, err := tx.Exec(ctx, `
		INSERT INTO ic3_asset_group_aggregation (
			group_id, group_name, cat_id, category_name,
			equipment_count, active_equipment, inactive_equipment,
			updated_at
		)
		SELECT
			ag.subcategory_id,
			ag.asset_group_name,
			ag.cat_id,
			ac.category_name,
			COUNT(am.asset_id),
			SUM(CASE WHEN am.status = 1 THEN 1 ELSE 0 END),
			SUM(CASE WHEN am.status = 0 THEN 1 ELSE 0 END),
			NOW()
		FROM ic3_asset_group ag
		LEFT JOIN ic3_asset_category ac ON ac.cat_id = ag.cat_id
		LEFT JOIN ic3_asset_master   am ON am.asset_group_id = ag.subcategory_id
		GROUP BY ag.subcategory_id, ag.asset_group_name, ag.cat_id, ac.category_name
		ON CONFLICT (group_id) DO UPDATE SET
			equipment_count    = EXCLUDED.equipment_count,
			active_equipment   = EXCLUDED.active_equipment,
			inactive_equipment = EXCLUDED.inactive_equipment,
			updated_at         = NOW()`)
	return err
}
```

---

## ALSO ADD to imports at top of cmms_import.go if missing

```go
"log"
```

---

## AFTER APPLYING — test import

```bash
curl -X POST "http://localhost:9090/api/admin/cmms/import" \
  -H "Authorization: Bearer <token>" \
  -F "file=@H:/ic3/docs/Asset_list.xlsx" \
  -F "replace=false"
```

Expected response:
```json
{"total":2957,"inserted":2957,"updated":0,"skipped":0,"duration_ms":"..."}
```

Then verify:
```sql
SELECT COUNT(*) FROM ic3_asset_master;
SELECT asset_id, cmms_asset_id, serialnumber FROM ic3_asset_master LIMIT 5;
```