package main

import (
	"context"
	"encoding/json"
		"math"
	"net/http"
	"time"
)

// GetValves returns valve assets with KPI and breakdown
func GetValves(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		// KPI query
		var total, active, inactive, highCrit int
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*),
				COUNT(*) FILTER (WHERE am.asset_status = 1),
				COUNT(*) FILTER (WHERE am.asset_status = 0),
				COUNT(*) FILTER (WHERE am.criticality = 'HIGH')
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%valve%' OR ag.asset_group_name ILIKE '%NRV%'
		`).Scan(&total, &active, &inactive, &highCrit)

		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]interface{}{"error": err.Error()})
			return
		}

		// Group breakdown
		rows, err := db.pool.Query(ctx, `
			SELECT
				ag.asset_group_name,
				COUNT(*) AS total,
				COUNT(*) FILTER (WHERE am.asset_status = 1) AS active,
				ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%valve%' OR ag.asset_group_name ILIKE '%NRV%'
			GROUP BY ag.asset_group_name
			ORDER BY total DESC
		`)
		defer rows.Close()

		var groups []map[string]interface{}
		for rows.Next() {
			var name string
			var tot, act int
			var pct float64
			rows.Scan(&name, &tot, &act, &pct)
			groups = append(groups, map[string]interface{}{
				"name": name, "total": tot, "active": act, "pct": pct,
			})
		}

		// Location breakdown
		lrows, _ := db.pool.Query(ctx, `
			SELECT lm.location_name, lm.dma_id, COUNT(*) AS cnt,
				   COUNT(*) FILTER (WHERE am.asset_status=1) AS active
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			JOIN ic3_location_master lm ON am.location_id = lm.location_id
			WHERE ag.asset_group_name ILIKE '%valve%' OR ag.asset_group_name ILIKE '%NRV%'
			GROUP BY lm.location_name, lm.dma_id
			ORDER BY cnt DESC
		`)
		defer lrows.Close()

		var locations []map[string]interface{}
		for lrows.Next() {
			var loc, dma string
			var cnt, act int
			lrows.Scan(&loc, &dma, &cnt, &act)
			locations = append(locations, map[string]interface{}{
				"location": loc, "dma_id": dma, "total": cnt, "active": act,
			})
		}

		opPct := 0.0
		if total > 0 {
			opPct = math.Round(float64(active)*100/float64(total)*10) / 10
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"kpi": map[string]interface{}{
				"total": total, "active": active,
				"inactive": inactive, "high_criticality": highCrit,
				"operational_pct": opPct,
			},
			"groups":    groups,
			"locations": locations,
		})
	}
}

// GetPumps returns pump/motor assets
func GetPumps(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var total, active, inactive, stations int
		err := db.pool.QueryRow(ctx, `
			SELECT COUNT(*), COUNT(*) FILTER (WHERE asset_status=1),
				   COUNT(*) FILTER (WHERE asset_status=0),
				   COUNT(DISTINCT location_id)
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%pump%' OR ag.asset_group_name ILIKE '%motor%'
		`).Scan(&total, &active, &inactive, &stations)

		if err != nil {
			w.WriteHeader(500)
		json.NewEncoder(w).Encode(map[string]interface{}{"error": err.Error()})
			return
		}

		rows, _ := db.pool.Query(ctx, `
			SELECT ag.asset_group_name, COUNT(*) AS total,
				   COUNT(*) FILTER (WHERE am.asset_status=1) AS active
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%pump%' OR ag.asset_group_name ILIKE '%motor%'
			GROUP BY ag.asset_group_name ORDER BY total DESC
		`)
		defer rows.Close()

		var groups []map[string]interface{}
		for rows.Next() {
			var name string
			var tot, act int
			rows.Scan(&name, &tot, &act)
			groups = append(groups, map[string]interface{}{
				"name": name, "total": tot, "active": act,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"kpi": map[string]interface{}{
				"total": total, "active": active, "inactive": inactive, "stations": stations,
			},
			"groups": groups,
		})
	}
}

// GetFlowMeters returns flow meter assets
func GetFlowMeters(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var total, active int
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*), COUNT(*) FILTER (WHERE asset_status=1)
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%flow%' OR ag.asset_group_name ILIKE '%meter%'
		`).Scan(&total, &active)

		rows, _ := db.pool.Query(ctx, `
			SELECT ag.asset_group_name, COUNT(*) AS total,
				   COUNT(*) FILTER (WHERE am.asset_status=1) AS active
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%flow%' OR ag.asset_group_name ILIKE '%meter%'
			GROUP BY ag.asset_group_name ORDER BY total DESC
		`)
		defer rows.Close()

		var groups []map[string]interface{}
		for rows.Next() {
			var name string
			var tot, act int
			rows.Scan(&name, &tot, &act)
			groups = append(groups, map[string]interface{}{
				"name": name, "total": tot, "active": act,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"kpi": map[string]interface{}{"total": total, "active": active},
			"groups": groups,
		})
	}
}

// GetPressureSensors returns pressure sensor assets
func GetPressureSensors(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var total, active int
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*), COUNT(*) FILTER (WHERE asset_status=1)
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%pressure%'
		`).Scan(&total, &active)

		rows, _ := db.pool.Query(ctx, `
			SELECT ag.asset_group_name, COUNT(*) AS total,
				   COUNT(*) FILTER (WHERE am.asset_status=1) AS active
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%pressure%'
			GROUP BY ag.asset_group_name ORDER BY total DESC
		`)
		defer rows.Close()

		var groups []map[string]interface{}
		for rows.Next() {
			var name string
			var tot, act int
			rows.Scan(&name, &tot, &act)
			groups = append(groups, map[string]interface{}{
				"name": name, "total": tot, "active": act,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"kpi": map[string]interface{}{"total": total, "active": active},
			"groups": groups,
		})
	}
}

// GetLevelSensors returns level sensor assets
func GetLevelSensors(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var total, active int
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*), COUNT(*) FILTER (WHERE asset_status=1)
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%level%' OR ag.asset_group_name ILIKE '%ULT%'
		`).Scan(&total, &active)

		rows, _ := db.pool.Query(ctx, `
			SELECT ag.asset_group_name, COUNT(*) AS total,
				   COUNT(*) FILTER (WHERE am.asset_status=1) AS active
			FROM ic3_asset_master am
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			WHERE ag.asset_group_name ILIKE '%level%' OR ag.asset_group_name ILIKE '%ULT%'
			GROUP BY ag.asset_group_name ORDER BY total DESC
		`)
		defer rows.Close()

		var groups []map[string]interface{}
		for rows.Next() {
			var name string
			var tot, act int
			rows.Scan(&name, &tot, &act)
			groups = append(groups, map[string]interface{}{
				"name": name, "total": tot, "active": act,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"kpi": map[string]interface{}{"total": total, "active": active},
			"groups": groups,
		})
	}
}

// GetDMARegistry returns DMA location data
func GetDMARegistry(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT lm.dma_id, lm.location_name, lm.zone_id, lm.latitude, lm.longitude,
				   COUNT(am.asset_id) AS total_assets,
				   COUNT(am.asset_id) FILTER (WHERE am.asset_status=1) AS active,
				   COUNT(am.asset_id) FILTER (WHERE am.criticality='HIGH') AS high_criticality
			FROM ic3_location_master lm
			LEFT JOIN ic3_asset_master am ON am.location_id = lm.location_id
			GROUP BY lm.dma_id, lm.location_name, lm.zone_id, lm.latitude, lm.longitude
			ORDER BY total_assets DESC
		`)
		defer rows.Close()

		if err != nil {
			w.WriteHeader(500)
		json.NewEncoder(w).Encode(map[string]interface{}{"error": err.Error()})
			return
		}

		var dmas []map[string]interface{}
		for rows.Next() {
			var dmaID, locName, zoneID string
			var lat, lon float64
			var total, active, highCrit int
			rows.Scan(&dmaID, &locName, &zoneID, &lat, &lon, &total, &active, &highCrit)
			dmas = append(dmas, map[string]interface{}{
				"dma_id": dmaID, "location_name": locName, "zone_id": zoneID,
				"latitude": lat, "longitude": lon, "total_assets": total,
				"active": active, "high_criticality": highCrit,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"dmas": dmas, "total_dmas": len(dmas),
		})
	}
}

// GetHealthScores returns asset health scores
func GetHealthScores(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var avgScore float64
		var good, fair, poor int
		err := db.pool.QueryRow(ctx, `
			SELECT
				COALESCE(ROUND(AVG(latest.health_score), 1), 0),
				COUNT(*) FILTER (WHERE latest.health_score >= 80),
				COUNT(*) FILTER (WHERE latest.health_score >= 60 AND latest.health_score < 80),
				COUNT(*) FILTER (WHERE latest.health_score < 60)
			FROM (
				SELECT DISTINCT ON (asset_id) value_num AS health_score
				FROM ic3_asset_monitoring_data
				WHERE tag_code ILIKE '%.HEALTH_SCORE'
				ORDER BY asset_id, recorded_at DESC
			) latest
		`).Scan(&avgScore, &good, &fair, &poor)

		if err != nil {
			w.WriteHeader(500)
		json.NewEncoder(w).Encode(map[string]interface{}{"error": err.Error()})
			return
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"avg_score": avgScore, "good": good, "fair": fair, "poor": poor,
		})
	}
}

// GetLiveReadings returns latest VOLTAGE/CURRENT readings
func GetLiveReadings(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT DISTINCT ON (md.asset_id, md.tag_code)
				am.serialnumber, ag.asset_group_name, lm.location_name,
				md.tag_code, md.value_num, md.unit, md.recorded_at
			FROM ic3_asset_monitoring_data md
			JOIN ic3_asset_master am ON md.asset_id = am.asset_id
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			JOIN ic3_location_master lm ON am.location_id = lm.location_id
			WHERE md.tag_code ILIKE '%.VOLTAGE' OR md.tag_code ILIKE '%.CURRENT'
			ORDER BY md.asset_id, md.tag_code, md.recorded_at DESC
			LIMIT 50
		`)
		defer rows.Close()

		if err != nil {
			w.WriteHeader(500)
		json.NewEncoder(w).Encode(map[string]interface{}{"error": err.Error()})
			return
		}

		var readings []map[string]interface{}
		for rows.Next() {
			var serial, group, loc, tag, unit string
			var val float64
			var recordedAt time.Time
			rows.Scan(&serial, &group, &loc, &tag, &val, &unit, &recordedAt)
			readings = append(readings, map[string]interface{}{
				"serial": serial, "group": group, "location": loc,
				"tag": tag, "value": val, "unit": unit, "recorded_at": recordedAt,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{"readings": readings})
	}
}

// GetManualEntries returns manual valve inspection entries
func GetManualEntries(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT DISTINCT ON (md.asset_id)
				am.serialnumber, ag.asset_group_name, lm.location_name, lm.dma_id,
				md.value_num, md.unit, md.recorded_at
			FROM ic3_asset_monitoring_data md
			JOIN ic3_asset_master am ON md.asset_id = am.asset_id
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			JOIN ic3_location_master lm ON am.location_id = lm.location_id
			WHERE md.tag_code ILIKE '%.VLV_MAN_STS'
			ORDER BY md.asset_id, md.recorded_at DESC
			LIMIT 50
		`)
		defer rows.Close()

		if err != nil {
			w.WriteHeader(500)
		json.NewEncoder(w).Encode(map[string]interface{}{"error": err.Error()})
			return
		}

		var entries []map[string]interface{}
		for rows.Next() {
			var serial, group, loc, dma, unit string
			var val float64
			var recordedAt time.Time
			rows.Scan(&serial, &group, &loc, &dma, &val, &unit, &recordedAt)
			entries = append(entries, map[string]interface{}{
				"serial": serial, "group": group, "location": loc, "dma_id": dma,
				"value": val, "unit": unit, "recorded_at": recordedAt,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{"entries": entries})
	}
}

// GetRealtimeSummary returns Realtime telemetry summary
func GetRealtimeSummary(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var assetsTagged, totalTags, totalReadings int
		var lastReading, firstReading *time.Time

		db.pool.QueryRow(ctx, `
			SELECT
				COUNT(DISTINCT tm.asset_id),
				COUNT(DISTINCT tm.tag_code),
				COUNT(md.id),
				MAX(md.recorded_at),
				MIN(md.recorded_at)
			FROM ic3_asset_tag_map tm
			LEFT JOIN ic3_asset_monitoring_data md
				ON md.asset_id = tm.asset_id AND md.tag_code = tm.tag_code
		`).Scan(&assetsTagged, &totalTags, &totalReadings, &lastReading, &firstReading)

		// Recent readings
		rows, _ := db.pool.Query(ctx, `
			SELECT am.serialnumber, ag.asset_group_name, lm.location_name,
				   md.tag_code, md.value_num, md.unit, md.recorded_at
			FROM ic3_asset_monitoring_data md
			JOIN ic3_asset_master am ON md.asset_id = am.asset_id
			JOIN ic3_asset_group ag ON am.asset_group_id = ag.subcategory_id
			JOIN ic3_location_master lm ON am.location_id = lm.location_id
			ORDER BY md.recorded_at DESC LIMIT 20
		`)
		defer rows.Close()

		var readings []map[string]interface{}
		for rows.Next() {
			var serial, group, loc, tag, unit string
			var val float64
			var recordedAt time.Time
			rows.Scan(&serial, &group, &loc, &tag, &val, &unit, &recordedAt)
			readings = append(readings, map[string]interface{}{
				"serial": serial, "group": group, "location": loc,
				"tag": tag, "value": val, "unit": unit, "recorded_at": recordedAt,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"assets_tagged": assetsTagged, "total_tags": totalTags,
			"total_readings": totalReadings, "last_reading": lastReading,
			"recent_readings": readings,
		})
	}
}

// GetMeterReads returns daily meter read counts
func GetMeterReads(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var totalReadings int
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_asset_monitoring_data
		`).Scan(&totalReadings)

		rows, _ := db.pool.Query(ctx, `
			SELECT DATE(recorded_at) AS read_date,
				   COUNT(*) AS readings,
				   COUNT(DISTINCT asset_id) AS assets_read
			FROM ic3_asset_monitoring_data
			GROUP BY DATE(recorded_at)
			ORDER BY read_date DESC LIMIT 14
		`)
		defer rows.Close()

		var dailyReads []map[string]interface{}
		for rows.Next() {
			var date time.Time
			var readings, assetsRead int
			rows.Scan(&date, &readings, &assetsRead)
			dailyReads = append(dailyReads, map[string]interface{}{
				"date": date.Format("2006-01-02"),
				"readings": readings, "assets_read": assetsRead,
			})
		}

		json.NewEncoder(w).Encode(map[string]interface{}{
			"total_readings": totalReadings, "daily_reads": dailyReads,
		})
	}
}