package main

import (
	"context"
	"encoding/json"
	"fmt"
	"hash/fnv"
	"log"
	"net/http"
	"strconv"
	"strings"
	"time"
)

// Helper functions
func ptrStr(s string) *string {
	return &s
}

func ptrFloat(f float64) *float64 {
	return &f
}

func hashInt(s string) int {
	h := fnv.New32a()
	h.Write([]byte(s))
	return int(h.Sum32())
}

// ============================================================================
// CMMS handlers
// ============================================================================

// POST /api/admin/cmms/import
// Accepts multipart/form-data with field "file" = xlsx of the Asset ID Report.
// Query param ?replace=true truncates the table before inserting (full reload).
func importCMMSAssetsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		if err := r.ParseMultipartForm(64 << 20); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "multipart parse failed: " + err.Error()})
			return
		}
		file, hdr, err := r.FormFile("file")
		if err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "field 'file' required"})
			return
		}
		defer file.Close()
		_ = hdr

		replace := r.URL.Query().Get("replace") == "true"

		ctx, cancel := context.WithTimeout(r.Context(), 60*time.Second)
		defer cancel()

		rows, err := ParseAssetReportXLSX(file)
		if err != nil {
			w.WriteHeader(422)
			json.NewEncoder(w).Encode(map[string]string{"error": "parse xlsx: " + err.Error()})
			return
		}
		if len(rows) == 0 {
			w.WriteHeader(422)
			json.NewEncoder(w).Encode(map[string]string{"error": "no data rows found in file"})
			return
		}

		result, err := db.BulkImportAssets(ctx, rows, replace)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(result)
	}
}

// GET /api/cmms/assets?customer=&category=&status=active|inactive&page=1&page_size=100
func listCMMSAssetsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		q := r.URL.Query()
		page, _ := strconv.Atoi(q.Get("page"))
		pageSize, _ := strconv.Atoi(q.Get("page_size"))

		assets, total, err := db.ListAssets(ctx,
			q.Get("customer"), q.Get("category"), q.Get("status"),
			page, pageSize,
		)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		if assets == nil {
			assets = []AssetRecord{}
		}
		json.NewEncoder(w).Encode(map[string]any{
			"data":  assets,
			"total": total,
			"count": len(assets),
		})
	}
}

// GET /api/cmms/assets/{id}  (id = asset_id text)
func getCMMSAssetHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		assetID := r.PathValue("id")
		if assetID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "asset_id required"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		asset, err := db.GetAsset(ctx, assetID)
		if err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": "asset not found"})
			return
		}
		json.NewEncoder(w).Encode(asset)
	}
}

// GET /api/cmms/health
func cmmsDashboardHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()
		summary, err := db.GetAssetHealth(ctx)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(summary)
	}
}

// GET /api/cmms/pm-overdue
func cmmsPMOverdueHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		rows, err := db.GetPMOverdue(ctx)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]any{"data": rows, "count": len(rows)})
	}
}

// ============================================================================
// Location handlers
// ============================================================================

// GET /api/locations/tree
func locationTreeHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		tree, err := db.GetLocationTree(ctx)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		if tree == nil {
			tree = []*LocationNode{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": tree})
	}
}

// GET /api/locations
func listLocationsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		nodes, err := db.ListLocations(ctx)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		if nodes == nil {
			nodes = []*LocationNode{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": nodes, "count": len(nodes)})
	}
}

// POST /api/admin/locations
func createLocationHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var in LocationInput
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		node, err := db.CreateLocation(ctx, in)
		if err != nil {
			w.WriteHeader(409)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(node)
	}
}

// PUT /api/admin/locations/{id}
func updateLocationHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id := r.PathValue("id")
		var in LocationInput
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		node, err := db.UpdateLocation(ctx, id, in)
		if err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(node)
	}
}

// DELETE /api/admin/locations/{id}
func deleteLocationHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id := r.PathValue("id")
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		if err := db.DeleteLocation(ctx, id); err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]string{"status": "deleted"})
	}
}

// ============================================================================
// Customer handlers
// ============================================================================

// GET /api/customers
func listCustomersHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()
		q := r.URL.Query()
		page, _ := strconv.Atoi(q.Get("page"))
		pageSize, _ := strconv.Atoi(q.Get("page_size"))
		customers, total, err := db.ListCustomers(ctx,
			q.Get("dma_id"), q.Get("type"), q.Get("status"), page, pageSize)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		if customers == nil {
			customers = []Customer{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": customers, "total": total, "count": len(customers)})
	}
}

// GET /api/customers/{id}/connections
func listConnectionsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		customerID := r.PathValue("id")
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		conns, err := db.ListServiceConnections(ctx, customerID)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]any{"data": conns, "count": len(conns)})
	}
}

// POST /api/admin/customers
func createCustomerHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var in CustomerInput
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		cu, err := db.CreateCustomer(ctx, in)
		if err != nil {
			w.WriteHeader(409)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(cu)
	}
}

// PUT /api/admin/customers/{id}
func updateCustomerHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id := r.PathValue("id")
		var in CustomerInput
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		cu, err := db.UpdateCustomer(ctx, id, in)
		if err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(cu)
	}
}

// DELETE /api/admin/customers/{id}
func deleteCustomerHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id := r.PathValue("id")
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		if err := db.DeleteCustomer(ctx, id); err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]string{"status": "deleted"})
	}
}

// POST /api/admin/customers/{id}/connections
func createConnectionHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		customerID := r.PathValue("id")
		var conn CustomerServiceConnection
		if err := json.NewDecoder(r.Body).Decode(&conn); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		conn.CustomerID = customerID
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		result, err := db.CreateServiceConnection(ctx, conn)
		if err != nil {
			w.WriteHeader(409)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(result)
	}
}

// POST /api/admin/assets  — create one asset
func createAssetHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var in AssetUpsertInput
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil || in.AssetID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "asset_id and asset_name required"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		asset, err := db.CreateAsset(ctx, in)
		if err != nil {
			w.WriteHeader(409)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(asset)
	}
}

// PUT /api/admin/assets/{id}  — update one asset
func updateAssetHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		assetID := r.PathValue("id")
		if assetID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "id required"})
			return
		}
		var in AssetUpsertInput
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()
		asset, err := db.UpdateAsset(ctx, assetID, in)
		if err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(asset)
	}
}

// DELETE /api/admin/assets/{id}
func deleteAssetHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		assetID := r.PathValue("id")
		if assetID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "id required"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		if err := db.DeleteAsset(ctx, assetID); err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]string{"status": "deleted"})
	}
}

// GET /api/cmms/sync-log
func cmmsSyncLogHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		limit, _ := strconv.Atoi(r.URL.Query().Get("limit"))
		rows, err := db.GetSyncLog(ctx, limit)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]any{"data": rows, "count": len(rows)})
	}
}

// POST /api/admin/cmms/sync?batch=500&offset=0
// POST /api/admin/cmms/sync?batch=100&offset=0&retry_failed=false
func syncInnomaintAssetsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")

		q := r.URL.Query()
		batch, _    := strconv.Atoi(q.Get("batch"))
		offset, _   := strconv.Atoi(q.Get("offset"))
		retryFailed := q.Get("retry_failed") == "true"
		customerID, _ := strconv.Atoi(q.Get("customer_id"))
		if customerID == 0 {
			customerID = innomaintCustomerId
		}

		if batch <= 0 {
			batch = 100
		}

		ctx, cancel := context.WithTimeout(r.Context(), 30*time.Minute)
		defer cancel()

		result, err := db.SyncAllAssetsFromInnomaint(ctx, batch, offset, retryFailed, customerID)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}

		w.WriteHeader(200)
		json.NewEncoder(w).Encode(result)
	}
}

// POST /api/admin/cmms/sync/{id}
// Sync one asset by cmms_asset_id — useful for testing and on-demand refresh
func syncSingleAssetHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")

		cmmsID := r.PathValue("id")
		if cmmsID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "cmms_asset_id required"})
			return
		}

		ctx, cancel := context.WithTimeout(r.Context(), 15*time.Second)
		defer cancel()

		client := &http.Client{Timeout: 10 * time.Second}
		apiResp, err := FetchInnomaintAssetDetail(ctx, client, cmmsID)
		if err != nil {
			w.WriteHeader(502)
			json.NewEncoder(w).Encode(map[string]string{"error": "innomaint api: " + err.Error()})
			return
		}

		tx, err := db.pool.Begin(ctx)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": "tx begin: " + err.Error()})
			return
		}
		defer tx.Rollback(ctx)

		if err := db.EnrichAssetFromAPI(ctx, tx, apiResp); err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": "enrich: " + err.Error()})
			return
		}

		if err := tx.Commit(ctx); err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": "commit: " + err.Error()})
			return
		}

		json.NewEncoder(w).Encode(map[string]any{
			"status":       "synced",
			"cmms_asset_id": cmmsID,
			"traceability_id": apiResp.Response.Data.TraceID,
		})
	}
}

// POST /api/admin/cmms/sync/retry-failed
// Retries only assets that failed in the last sync run
func retryFailedSyncHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")

		customerID, _ := strconv.Atoi(r.URL.Query().Get("customer_id"))
		if customerID == 0 {
			customerID = innomaintCustomerId
		}

		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Minute)
		defer cancel()

		result, err := db.SyncAllAssetsFromInnomaint(ctx, 0, 0, true, customerID)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}

		json.NewEncoder(w).Encode(result)
	}
}

// GET /api/admin/cmms/sync/status
// Returns summary of last sync run
func syncStatusHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")

		var summary struct {
			TotalAssets   int        `json:"total_assets"`
			SyncedAssets  int        `json:"synced_assets"`
			FailedAssets  int        `json:"failed_assets"`
			NeverSynced   int        `json:"never_synced"`
			LastSyncAt    *time.Time `json:"last_sync_at"`
			LastBatchLog  []map[string]any `json:"last_batch"`
		}

		db.pool.QueryRow(r.Context(), `SELECT COUNT(*) FROM ic3_asset_master`).Scan(&summary.TotalAssets)

		db.pool.QueryRow(r.Context(), `
			SELECT COUNT(DISTINCT serialnumber) FROM ic3_sync_log
			WHERE sync_type='api_sync' AND status='success'`).Scan(&summary.SyncedAssets)

		db.pool.QueryRow(r.Context(), `
			SELECT COUNT(*) FROM ic3_asset_master am
			WHERE NOT EXISTS (
				SELECT 1 FROM ic3_sync_log sl
				WHERE sl.serialnumber = am.cmms_asset_id
				AND sl.sync_type = 'api_sync'
			)`).Scan(&summary.NeverSynced)

		// Failed = assets whose LAST sync was error
		db.pool.QueryRow(r.Context(), `
			SELECT COUNT(DISTINCT am.cmms_asset_id)
			FROM ic3_asset_master am
			INNER JOIN (
				SELECT serialnumber, MAX(synced_at) as last_sync
				FROM ic3_sync_log WHERE sync_type='api_sync'
				GROUP BY serialnumber
			) latest ON latest.serialnumber = am.cmms_asset_id
			INNER JOIN ic3_sync_log sl
				ON sl.serialnumber = am.cmms_asset_id
				AND sl.synced_at = latest.last_sync
				AND sl.status = 'error'`).Scan(&summary.FailedAssets)

		// Last sync timestamp
		var lastSync time.Time
		if err := db.pool.QueryRow(r.Context(), `
			SELECT synced_at FROM cmms_sync_log
			WHERE sync_type='api_sync'
			ORDER BY synced_at DESC LIMIT 1`).Scan(&lastSync); err == nil {
			summary.LastSyncAt = &lastSync
		}

		// Last 5 batch log entries
		logs, _ := db.GetSyncLog(r.Context(), 5)
		summary.LastBatchLog = logs

		json.NewEncoder(w).Encode(summary)
	}
}

// GET /api/admin/cmms/sync/asset-log?status=error&limit=100&customer_id=17099
// Returns per-asset sync results from ic3_sync_log.
// status: "success" | "error" | "" (all)
// limit: default 100, max 500
// customer_id: filter by Innomaint customer_id (0 = all)
func assetSyncLogHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")

		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		q := r.URL.Query()
		statusFilter := q.Get("status")
		customerID, _ := strconv.Atoi(q.Get("customer_id"))
		limit, _ := strconv.Atoi(q.Get("limit"))
		if limit <= 0 || limit > 500 {
			limit = 100
		}

		where := []string{"sync_type = 'api_sync'"}
		args := []any{}
		n := 1

		if statusFilter == "success" || statusFilter == "error" {
			where = append(where, fmt.Sprintf("status = $%d", n))
			args = append(args, statusFilter)
			n++
		}
		if customerID > 0 {
			where = append(where, fmt.Sprintf("customer_id = $%d", n))
			args = append(args, customerID)
			n++
		}

		args = append(args, limit)
		cond := strings.Join(where, " AND ")

		query := fmt.Sprintf(`
			SELECT sync_id, serialnumber, customer_id, status, error_message,
			       duration_ms, synced_at
			FROM ic3_sync_log
			WHERE %s
			ORDER BY synced_at DESC
			LIMIT $%d`, cond, n)

		rows, err := db.pool.Query(ctx, query, args...)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var out []map[string]any
		for rows.Next() {
			var (
				syncID     int64
				serial     string
				custID     *int
				st         string
				errMsg     *string
				durMs      *int
				syncedAt   time.Time
			)
			if err := rows.Scan(&syncID, &serial, &custID, &st, &errMsg, &durMs, &syncedAt); err != nil {
				continue
			}
			out = append(out, map[string]any{
				"sync_id":      syncID,
				"cmms_asset_id": serial,
				"customer_id":  custID,
				"status":       st,
				"error":        errMsg,
				"duration_ms":  durMs,
				"synced_at":    syncedAt,
			})
		}
		if out == nil {
			out = []map[string]any{}
		}

		var total, success, failed int
		db.pool.QueryRow(ctx, `SELECT COUNT(*), SUM(CASE WHEN status='success' THEN 1 ELSE 0 END), SUM(CASE WHEN status='error' THEN 1 ELSE 0 END) FROM ic3_sync_log WHERE sync_type='api_sync'`).Scan(&total, &success, &failed)

		json.NewEncoder(w).Encode(map[string]any{
			"data":    out,
			"count":   len(out),
			"summary": map[string]any{
				"total_logged": total,
				"success":      success,
				"failed":       failed,
			},
		})
	}
}

// ── User management (admin only) ─────────────────────────────────────────────

func listUsersHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		users, err := db.listUsers(ctx)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		if users == nil {
			users = []User{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": users, "count": len(users)})
	}
}

func createUserHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var req struct {
			Username string `json:"username"`
			Password string `json:"password"`
			Role     string `json:"role"`
			FullName string `json:"full_name"`
			Email    string `json:"email"`
		}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil || req.Username == "" || req.Password == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "username and password required"})
			return
		}
		if req.Role == "" {
			req.Role = "viewer"
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		u, err := db.createUser(ctx, req.Username, req.Password, req.Role, req.FullName, req.Email)
		if err != nil {
			w.WriteHeader(409)
			json.NewEncoder(w).Encode(map[string]string{"error": "username already exists"})
			return
		}
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(u)
	}
}

func updateUserHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
		if err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid id"})
			return
		}
		// Prevent self-demotion
		c := claimsFrom(r)
		var req struct {
			Role     string `json:"role"`
			Status   string `json:"status"`
			FullName string `json:"full_name"`
			Email    string `json:"email"`
		}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid body"})
			return
		}
		if req.Role == "" {
			req.Role = "viewer"
		}
		if req.Status == "" {
			req.Status = "active"
		}
		// Prevent admin from disabling their own account
		if c != nil && c.UserID == id && (req.Status == "disabled" || req.Role != "admin") {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "cannot demote or disable your own account"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		u, err := db.updateUser(ctx, id, req.Role, req.Status, req.FullName, req.Email)
		if err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": "user not found"})
			return
		}
		json.NewEncoder(w).Encode(u)
	}
}

func deleteUserHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
		if err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid id"})
			return
		}
		// Prevent self-deletion
		c := claimsFrom(r)
		if c != nil && c.UserID == id {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "cannot delete your own account"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		if err := db.deleteUser(ctx, id); err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]string{"status": "deleted"})
	}
}

func resetPasswordHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
		if err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid id"})
			return
		}
		var req struct {
			Password string `json:"password"`
		}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil || len(req.Password) < 4 {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "password must be at least 4 characters"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		if err := db.resetPassword(ctx, id, req.Password); err != nil {
			w.WriteHeader(404)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		json.NewEncoder(w).Encode(map[string]string{"status": "password updated"})
	}
}

func latestHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		assets, err := db.latestAssets(ctx, r.URL.Query().Get("domain"))
		if err != nil {
			json.NewEncoder(w).Encode(map[string]any{"error": err.Error(), "data": []any{}})
			return
		}
		if assets == nil {
			assets = []TelemetryEnvelope{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": assets, "count": len(assets)})
	}
}

func historyHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			json.NewEncoder(w).Encode(map[string]any{"data": []any{}, "error": "database not available"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		assetID := r.URL.Query().Get("asset_id")
		domain := r.URL.Query().Get("domain")
		limit := 500
		if l := r.URL.Query().Get("limit"); l != "" {
			if n, err := strconv.Atoi(l); err == nil && n > 0 && n <= 5000 {
				limit = n
			}
		}

		rows, err := db.history(ctx, assetID, domain, limit)
		if err != nil {
			json.NewEncoder(w).Encode(map[string]any{"error": err.Error(), "data": []any{}})
			return
		}
		if rows == nil {
			rows = []TelemetryEnvelope{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": rows, "count": len(rows)})
	}
}

func alarmsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		alarms, err := db.latestAlarms(ctx)
		if err != nil {
			json.NewEncoder(w).Encode(map[string]any{"error": err.Error(), "data": []any{}})
			return
		}
		json.NewEncoder(w).Encode(map[string]any{"data": alarms, "count": len(alarms)})
	}
}

func statsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		dbStats := map[string]any{
			"total": 0, "alarms": 0, "domains": 0, "live_assets": 0,
			"ai_anomalies": 0, "pump_critical": 0, "nrw_breach_count": 0, "predictive_wo": 0,
		}
		if db != nil {
			dbStats, _ = db.statsV2(ctx)
		}

		json.NewEncoder(w).Encode(map[string]any{
			"live_assets":      dbStats["live_assets"],
			"live_alarms":      dbStats["alarms"],
			"live_domains":     dbStats["domains"],
			"ai_anomalies":     dbStats["ai_anomalies"],
			"pump_critical":    dbStats["pump_critical"],
			"nrw_breach_count": dbStats["nrw_breach_count"],
			"predictive_wo":    dbStats["predictive_wo"],
			"db":               dbStats,
			"ts":               time.Now().UTC().Format(time.RFC3339),
		})
	}
}

// GISAsset is a map-ready asset row returned by v_gis_assets.
type GISAsset struct {
	AssetID       string  `json:"asset_id"`
	Traceid       *int    `json:"traceid,omitempty"`
	Serialnumber  string  `json:"serialnumber"`
	ReportAssetID *string `json:"report_asset_id,omitempty"`
	AssetName     *string `json:"asset_name,omitempty"`
	AssetCategory *string `json:"asset_category,omitempty"`
	AssetSubCat   *string `json:"asset_sub_category,omitempty"`
	Manufacturer  *string `json:"manufacturer_name,omitempty"`
	Criticality   string  `json:"criticality"`
	LiveStatus    int     `json:"asset_live_status"`
	IotMapped     int     `json:"iot_device_mapped"`
	LocationName  *string `json:"location_name,omitempty"`
	BuildingName  *string `json:"building_name,omitempty"`
	CustomerName  *string `json:"customer_name,omitempty"`
	AssetGroupID  *int    `json:"asset_group_id,omitempty"`
	AssetGroupName *string `json:"asset_group_name,omitempty"`
	AssetIcon     *string `json:"asset_icon,omitempty"`
	// GIS coordinates — fallback chain: asset GPS → zone GPS → site GPS
	GisLat        *float64 `json:"gis_lat,omitempty"`
	GisLng        *float64 `json:"gis_lng,omitempty"`
	AssetLat      *float64 `json:"asset_lat,omitempty"`
	AssetLng      *float64 `json:"asset_lng,omitempty"`
	ZoneName      *string  `json:"zone_name,omitempty"`
	ZoneLat       *float64 `json:"zone_lat,omitempty"`
	ZoneLng       *float64 `json:"zone_lon,omitempty"`
	SiteName      *string  `json:"site_name,omitempty"`
	SiteLat       *float64 `json:"site_latitude,omitempty"`
	SiteLng       *float64 `json:"site_longitude,omitempty"`
	CustomerPkg   *string  `json:"customer_package,omitempty"`
	OmPackageCode *string  `json:"om_package_code,omitempty"`
}

// GISSiteCluster groups assets by site/zone for the map cluster view.
type GISSiteCluster struct {
	SiteCode    string    `json:"site_code"`
	SiteName    string    `json:"site_name"`
	SiteType    string    `json:"site_type"`
	Lat         float64   `json:"lat"`
	Lng         float64   `json:"lng"`
	AssetCount  int       `json:"asset_count"`
	CustomerPkg string    `json:"customer_package"`
}

// listGISAssetsHandler — GET /api/gis/assets
// Returns all assets with real GPS from v_gis_assets view.
// Query params: zone=<zone_name>, customer=<pkg_name>, category=<cat>
func listGISAssetsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")
		if db == nil {
			json.NewEncoder(w).Encode(map[string]any{"data": []any{}, "count": 0})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 15*time.Second)
		defer cancel()

		q := r.URL.Query()
		zone      := q.Get("zone")       // matches zone_name, site_name, or location_name
		siteCode  := q.Get("site_code")  // exact site code match (preferred)
		customer  := q.Get("customer")
		category  := q.Get("category")

		sql := `
SELECT
    am.asset_id,
    NULL::INT as traceid,
    am.serialnumber,
    am.asset_id as report_asset_id,
    COALESCE(am.asset_reference_number, CAST(am.asset_id AS VARCHAR)) as asset_name,
    COALESCE(cm.category_name, 'Unknown') as asset_category,
    COALESCE(ag.asset_group_name, 'Unknown') as asset_sub_category,
    am.in_charge_person as manufacturer,
    am.criticality,
    CASE WHEN am.asset_status = 1 THEN 1 ELSE 0 END as asset_live_status,
    CASE WHEN am.iot_device_mapped = 1 THEN 1 ELSE 0 END as iot_device_mapped,
    lm.location_id as location_name,
    COALESCE(am.building_id::TEXT, lm.location_id::TEXT) as building_name,
    lm.city_id as customer_name,
    ag.subcategory_id as asset_group_id,
    ag.asset_group_name,
    ag.icon as asset_icon,
    lm.latitude::FLOAT8 as gis_lat,
    lm.longitude::FLOAT8 as gis_lng,
    lm.latitude::FLOAT8 as asset_lat,
    lm.longitude::FLOAT8 as asset_lng,
    lm.zone_id as zone_name,
    lm.latitude::FLOAT8 as zone_lat,
    lm.longitude::FLOAT8 as zone_lon,
    lm.location_id as site_name,
    lm.latitude::FLOAT8 as site_latitude,
    lm.longitude::FLOAT8 as site_longitude,
    lm.city_id as customer_package,
    lm.dma_id as om_package_code
FROM ic3_asset_master am
INNER JOIN ic3_location_master lm ON am.location_id = lm.location_id
LEFT JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
LEFT JOIN ic3_category_master cm ON am.asset_type = cm.cat_id
WHERE lm.latitude IS NOT NULL
  AND lm.longitude IS NOT NULL`

		args := []any{}
		idx := 1
		if siteCode != "" {
			// Exact match on location_id (site code = location_id in ic3_location_master)
			sql += fmt.Sprintf(` AND lm.location_id = $%d`, idx)
			args = append(args, siteCode)
			idx++
		} else if zone != "" {
			// Fuzzy match across zone_name, site_name, location_name
			sql += fmt.Sprintf(` AND (
				zone_name    ILIKE $%d OR
				site_name    ILIKE $%d OR
				location_name ILIKE $%d)`, idx, idx+1, idx+2)
			args = append(args, "%"+zone+"%", "%"+zone+"%", "%"+zone+"%")
			idx += 3
		}
		if customer != "" {
			sql += fmt.Sprintf(" AND (customer_name ILIKE $%d OR customer_package ILIKE $%d)", idx, idx+1)
			args = append(args, "%"+customer+"%", "%"+customer+"%")
			idx += 2
		}
		if category != "" {
			sql += fmt.Sprintf(" AND asset_category ILIKE $%d", idx)
			args = append(args, "%"+category+"%")
			idx++
		}
		sql += " ORDER BY zone_name, asset_id LIMIT 5000"

		rows, err := db.pool.Query(ctx, sql, args...)
		if err != nil {
			http.Error(w, `{"error":"query failed"}`, 500)
			return
		}
		defer rows.Close()

		var assets []GISAsset
		for rows.Next() {
			var a GISAsset
			if err := rows.Scan(
				&a.AssetID, &a.Traceid, &a.Serialnumber, &a.ReportAssetID,
				&a.AssetName, &a.AssetCategory, &a.AssetSubCat, &a.Manufacturer,
				&a.Criticality, &a.LiveStatus, &a.IotMapped,
				&a.LocationName, &a.BuildingName, &a.CustomerName,
				&a.AssetGroupID, &a.AssetGroupName, &a.AssetIcon,
				&a.GisLat, &a.GisLng, &a.AssetLat, &a.AssetLng,
				&a.ZoneName, &a.ZoneLat, &a.ZoneLng,
				&a.SiteName, &a.SiteLat, &a.SiteLng,
				&a.CustomerPkg, &a.OmPackageCode,
			); err != nil {
				continue
			}
			assets = append(assets, a)
		}
		if assets == nil {
			assets = []GISAsset{}
		}
		json.NewEncoder(w).Encode(map[string]any{"data": assets, "count": len(assets)})
	}
}

// listGISSiteClustersHandler — GET /api/gis/sites
// Returns one cluster pin per site with asset count and GPS.
func listGISSiteClustersHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")
		if db == nil {
			json.NewEncoder(w).Encode(map[string]any{"data": []any{}, "count": 0})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		// Query from ic3_location_master + ic3_asset_master
		rows, err := db.pool.Query(ctx, `
SELECT
    lm.location_id,
    COALESCE(lm.location_name, lm.location_id::TEXT) AS site_name,
    CASE
        WHEN lm.location_name ILIKE '%WTP%' THEN 'WTP'
        WHEN lm.location_name ILIKE '%BPS%' THEN 'BPS'
        WHEN lm.location_name ILIKE '%UGR%' THEN 'UGR'
        ELSE 'SITE'
    END AS site_type,
    COALESCE(lm.latitude, 0)::FLOAT8,
    COALESCE(lm.longitude, 0)::FLOAT8,
    COUNT(DISTINCT am.asset_id)::INT as asset_count,
    COALESCE(lm.city_id, '') AS city
FROM ic3_location_master lm
LEFT JOIN ic3_asset_master am ON am.location_id = lm.location_id AND am.asset_status = 1
WHERE lm.latitude IS NOT NULL
  AND lm.longitude IS NOT NULL
  AND (lm.location_name IS NOT NULL OR lm.zone_id IS NOT NULL)
GROUP BY lm.location_id, lm.location_name, lm.latitude, lm.longitude, lm.city_id
ORDER BY asset_count DESC
LIMIT 100`)
		if err != nil {
			log.Printf("GIS query error: %v", err)
			http.Error(w, `{"error":"query failed"}`, 500)
			return
		}
		defer rows.Close()

		var clusters []GISSiteCluster
		rowCount := 0
		for rows.Next() {
			rowCount++
			var c GISSiteCluster
			if err := rows.Scan(&c.SiteCode, &c.SiteName, &c.SiteType, &c.Lat, &c.Lng,
				&c.AssetCount, &c.CustomerPkg); err != nil {
				log.Printf("Scan error: %v", err)
				continue
			}
			log.Printf("DEBUG: Scanned site: %s (%s) at %.4f, %.4f", c.SiteName, c.SiteType, c.Lat, c.Lng)
			clusters = append(clusters, c)
		}
		if err := rows.Err(); err != nil {
			log.Printf("Rows error: %v", err)
		}
		if clusters == nil {
			clusters = []GISSiteCluster{}
		}
		log.Printf("GIS: loaded %d site clusters (total rows iterated: %d)", len(clusters), rowCount)
		json.NewEncoder(w).Encode(map[string]any{"data": clusters, "count": len(clusters)})
	}
}

// AssetFullDetail — everything about one asset for the map popup.
type AssetFullDetail struct {
	// Core identity (from tbl_asset / ic3_asset_master)
	AssetID         string   `json:"asset_id"`
	ReportAssetID   *string  `json:"report_asset_id,omitempty"`
	Traceid         *string  `json:"traceid,omitempty"`
	Serialnumber    string   `json:"serialnumber"`
	AssetName       *string  `json:"asset_name,omitempty"`
	AssetCategory   *string  `json:"asset_category,omitempty"`
	AssetSubCat     *string  `json:"asset_sub_category,omitempty"`
	ManufacturerName *string `json:"manufacturer_name,omitempty"`
	CapacityRating  *string  `json:"capacity_rating,omitempty"`
	Criticality     string   `json:"criticality"`
	LiveStatus      int      `json:"asset_live_status"`
	IotMapped       int      `json:"iot_device_mapped"`
	Status          int      `json:"status"`
	// Location
	LocationName  *string  `json:"location_name,omitempty"`
	BuildingName  *string  `json:"building_name,omitempty"`
	CustomerName  *string  `json:"customer_name,omitempty"`
	SiteName      *string  `json:"site_name,omitempty"`
	ZoneName      *string  `json:"zone_name,omitempty"`
	GisLat        *float64 `json:"gis_lat,omitempty"`
	GisLng        *float64 `json:"gis_lng,omitempty"`
	// Operational
	InChargePerson  *string  `json:"in_charge_person,omitempty"`
	InstallDate     *string  `json:"installation_date,omitempty"`
	PurchaseValue   *float64 `json:"purchase_value,omitempty"`
	PoNumber        *string  `json:"po_number,omitempty"`
	// PM / WO counters
	ScheduleCount   int `json:"schedule_count"`
	TicketCount     int `json:"ticket_count"`
	// CMMS detail from ic3_asset_master
	ContractName    *string `json:"contract_name,omitempty"`
	ContractEnd     *string `json:"contract_end_date,omitempty"`
	ServiceProvider *string `json:"service_provider_name,omitempty"`
	Barcode         *string `json:"barcode,omitempty"`
	// Latest alarm from alarm_events
	LatestAlarm     *string `json:"latest_alarm,omitempty"`
	AlarmState      *string `json:"alarm_state,omitempty"`
	AlarmTs         *string `json:"alarm_ts,omitempty"`
	// PM schedule
	LastPMDate   *string `json:"last_pm_date,omitempty"`
	NextPMDate   *string `json:"next_pm_date,omitempty"`
	PMOverdue    bool    `json:"pm_overdue"`
	// Live telemetry quality
	Quality      *string `json:"quality,omitempty"`
}

// getGISAssetDetailHandler — GET /api/gis/asset-detail?id=<report_asset_id>
func getGISAssetDetailHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")
		if db == nil {
			http.Error(w, `{"error":"db unavailable"}`, 503)
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		reportID := r.URL.Query().Get("id")
		if reportID == "" {
			http.Error(w, `{"error":"id required"}`, 400)
			return
		}

		var d AssetFullDetail

		// ── 1. Core asset from ic3_asset_master + ic3_location_master
		err := db.pool.QueryRow(ctx, `
SELECT
    am.asset_id, am.asset_id, am.cmms_asset_id, am.serial_no,
    am.asset_name, am.asset_category, am.asset_subtype, am.manufacturer,
    am.criticality, CASE WHEN am.asset_status='ACTIVE' THEN 1 ELSE 0 END, 0,
    CASE WHEN am.asset_status='ACTIVE' THEN 1 ELSE 0 END,
    lm.site_name, COALESCE(lm.site_name,''), lm.city_code,
    COALESCE(am.latitude, lm.latitude)  AS gis_lat,
    COALESCE(am.longitude, lm.longitude) AS gis_lng,
    lm.zone_id, lm.site_name,
    '', '', 0, '', 0, 0
FROM ic3_asset_master am
LEFT JOIN ic3_location_master lm ON am.location_id = lm.location_id
WHERE am.asset_id = $1
LIMIT 1`, reportID).Scan(
			&d.AssetID, &d.ReportAssetID, &d.Traceid, &d.Serialnumber,
			&d.AssetName, &d.AssetCategory, &d.AssetSubCat, &d.ManufacturerName,
			&d.Criticality, &d.LiveStatus, &d.IotMapped,
			&d.Status,
			&d.LocationName, &d.BuildingName, &d.CustomerName,
			&d.GisLat, &d.GisLng,
			&d.ZoneName, &d.SiteName,
			&d.InChargePerson, &d.InstallDate,
			&d.PurchaseValue, &d.PoNumber,
			&d.ScheduleCount, &d.TicketCount,
		)
		if err != nil {
			http.Error(w, fmt.Sprintf(`{"error":"asset not found: %v"}`, err), 404)
			return
		}

		// ── 2. Dummy CMMS & Health data for demo
		d.CapacityRating = ptrStr("250 m³/hr")
		d.ContractName = ptrStr("Annual Maintenance Contract")
		d.ContractEnd = ptrStr("2026-12-31")
		d.ServiceProvider = ptrStr("DJB Engineering Services")
		d.Barcode = ptrStr("DJB-CHW-2024-001")
		d.Quality = ptrStr("GOOD")
		d.ScheduleCount = 2
		d.TicketCount = 1

		// ── 3. Latest alarm from alarm_events (if any)
		db.pool.QueryRow(ctx, `
SELECT to_state, timestamp_utc
FROM alarm_events
WHERE asset_id = $1
ORDER BY timestamp_utc DESC LIMIT 1`, reportID).Scan(&d.LatestAlarm, &d.AlarmTs)

		// ── 4. Live quality from asset_latest
		db.pool.QueryRow(ctx, `
SELECT quality_code, alarm_state FROM asset_latest WHERE asset_id = $1`, reportID).Scan(
			&d.Quality, &d.AlarmState,
		)

		// ── 5. PM schedule from cmms_pm_schedule via cmms_asset_master
		if d.Traceid != nil {
			db.pool.QueryRow(ctx, `
SELECT TO_CHAR(s.schedule_date,'YYYY-MM-DD'),
       TO_CHAR(s.next_schedule_date,'YYYY-MM-DD'),
       s.is_overdue
FROM cmms_pm_schedule s
JOIN cmms_asset_master m ON m.id = s.asset_id
WHERE m.traceid = $1
ORDER BY s.synced_at DESC LIMIT 1`, *d.Traceid).Scan(
				&d.LastPMDate, &d.NextPMDate, &d.PMOverdue,
			)
		}

		// Generate health score data for demo
		healthScore := 78 + (hashInt(reportID) % 15)
		riskLevel := "LOW"
		if healthScore < 50 {
			riskLevel = "CRITICAL"
		} else if healthScore < 70 {
			riskLevel = "HIGH"
		}

		// Add health data to response
		resp := map[string]any{
			"data": d,
			"health_score": healthScore,
			"risk_level": riskLevel,
			"open_wo_count": 1,
			"failure_probability_30d": 0.15,
			"dma_name": "DMA-CHAND-PATEL-RD",
		}
		json.NewEncoder(w).Encode(resp)
	}
}