package main

import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"strconv"
	"time"
)

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

// ── 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}
		if db != nil {
			dbStats, _ = db.stats(ctx)
		}

		json.NewEncoder(w).Encode(map[string]any{
			"live_assets":  dbStats["live_assets"],
			"live_alarms":  dbStats["alarms"],
			"live_domains": dbStats["domains"],
			"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       int     `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"`
	// 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 asset_id, traceid, serialnumber, report_asset_id,
       asset_name, asset_category, asset_sub_category, manufacturer_name,
       criticality, asset_live_status, iot_device_mapped,
       location_name, building_name, customer_name,
       gis_lat, gis_lng, asset_lat, asset_lng,
       zone_name, zone_lat, zone_lon,
       site_name, site_latitude, site_longitude,
       customer_package, om_package_code
FROM v_gis_assets
WHERE gis_lat IS NOT NULL`

		args := []any{}
		idx := 1
		if siteCode != "" {
			// Exact match via join to tbl_site — most reliable
			sql += fmt.Sprintf(` AND asset_id IN (
				SELECT a.id FROM tbl_asset a
				JOIN tbl_asset_location al ON al.asset_id = a.id
				JOIN tbl_site s ON s.site_id = al.site_id
				WHERE s.site_code = $%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.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()

		rows, err := db.pool.Query(ctx, `
SELECT s.site_code, s.site_name, s.site_type,
       s.site_latitude::FLOAT8, s.site_longitude::FLOAT8,
       COUNT(a.id)::INT,
       COALESCE(cu.customer_name, '')
FROM tbl_site s
LEFT JOIN tbl_zone z ON z.site_id = s.site_id
LEFT JOIN tbl_asset_location al ON al.zone_id = z.zone_id
LEFT JOIN tbl_asset a ON a.id = al.asset_id AND a.status = 1
LEFT JOIN tbl_customer cu ON cu.customer_id = s.customer_id
WHERE s.site_latitude IS NOT NULL AND s.status = 1
GROUP BY s.site_code, s.site_name, s.site_type,
         s.site_latitude, s.site_longitude, cu.customer_name
ORDER BY COUNT(a.id) DESC`)
		if err != nil {
			http.Error(w, `{"error":"query failed"}`, 500)
			return
		}
		defer rows.Close()

		var clusters []GISSiteCluster
		for rows.Next() {
			var c GISSiteCluster
			if err := rows.Scan(&c.SiteCode, &c.SiteName, &c.SiteType, &c.Lat, &c.Lng,
				&c.AssetCount, &c.CustomerPkg); err != nil {
				continue
			}
			clusters = append(clusters, c)
		}
		if clusters == nil {
			clusters = []GISSiteCluster{}
		}
		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         int      `json:"asset_id"`
	ReportAssetID   *string  `json:"report_asset_id,omitempty"`
	Traceid         *int     `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 tbl_asset + v_gis_assets
		err := db.pool.QueryRow(ctx, `
SELECT
    a.id, a.report_asset_id, a.traceid, a.serialnumber,
    a.asset_name, a.asset_category, a.asset_sub_category, a.manufacturer_name,
    a.criticality, a.asset_live_status, a.iot_device_mapped, a.status,
    a.location_name, a.building_name, a.customer_name,
    COALESCE(al.latitude,  z.zone_lat,  s.site_latitude)  AS gis_lat,
    COALESCE(al.longitude, z.zone_lon,  s.site_longitude) AS gis_lng,
    z.zone_name, s.site_name,
    a.in_charge_person,
    TO_CHAR(a.installation_date,'YYYY-MM-DD'),
    a.purchase_value, a.po_number,
    COALESCE(a.schedule_configured,0)::INT,
    COALESCE((SELECT ticketcount FROM ic3_asset_master WHERE report_asset_id=a.report_asset_id LIMIT 1),0)
FROM tbl_asset a
LEFT JOIN tbl_asset_location al ON al.asset_id = a.id
LEFT JOIN tbl_zone z ON z.zone_id = al.zone_id
LEFT JOIN tbl_site s ON s.site_id = al.site_id
WHERE a.report_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. CMMS detail from ic3_asset_master (flat table)
		db.pool.QueryRow(ctx, `
SELECT capacity_rating, contract_name, TO_CHAR(contract_end_date,'YYYY-MM-DD'),
       service_provider_name, barcode
FROM ic3_asset_master WHERE report_asset_id = $1 LIMIT 1`, reportID).Scan(
			&d.CapacityRating, &d.ContractName, &d.ContractEnd,
			&d.ServiceProvider, &d.Barcode,
		)

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

		json.NewEncoder(w).Encode(map[string]any{"data": d})
	}
}