package main

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

// ============================================================================
// RESERVOIR HANDLERS (Query ic3_reservoirs table)
// ============================================================================

type ReservoirSummaryResp struct {
	TotalReservoirs   int     `json:"total_reservoirs"`
	OperationalCount  int     `json:"operational_count"`
	TotalCapacityM3   float64 `json:"total_capacity_m3"`
	CurrentStorageM3  float64 `json:"current_storage_m3"`
	AvgFillPercentage float64 `json:"avg_fill_percentage"`
}

type ReservoirByLocation struct {
	LocationID    int     `json:"location_id"`
	LocationName  string  `json:"location_name"`
	ReservoirName string  `json:"reservoir_name"`
	CurrentLevelM float64 `json:"current_level_m"`
	CapacityM3    float64 `json:"capacity_m3"`
	FillPercentage float64 `json:"fill_percentage"`
	Status        string  `json:"status"`
}

type ReservoirHealth struct {
	LocationID    int     `json:"location_id"`
	LocationName  string  `json:"location_name"`
	HealthScore   int     `json:"health_score"`
	LastUpdated   string  `json:"last_updated"`
	MaintenanceStatus string `json:"maintenance_status"`
}

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

		var summary ReservoirSummaryResp
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*)::INT as total,
				COUNT(CASE WHEN asset_status = 1 THEN 1 END)::INT as operational,
				COALESCE(SUM(capacity_m3), 0),
				COALESCE(SUM(current_level_m), 0),
				COALESCE(AVG(level_percentage), 0)
			FROM ic3_reservoirs
		`).Scan(&summary.TotalReservoirs, &summary.OperationalCount,
			&summary.TotalCapacityM3, &summary.CurrentStorageM3, &summary.AvgFillPercentage)

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

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(summary)
	}
}

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

		rows, _ := db.pool.Query(ctx, `
			SELECT
				ir.location_id,
				COALESCE(lm.location_name, 'Unknown'),
				ir.asset_name,
				COALESCE(ir.current_level_m, 0),
				COALESCE(ir.capacity_m3, 0),
				COALESCE(ir.level_percentage, 0),
				CASE WHEN ir.asset_status = 1 THEN 'Operational' ELSE 'Non-Operational' END
			FROM ic3_reservoirs ir
			LEFT JOIN ic3_location_master lm ON ir.location_id = lm.location_id
			ORDER BY lm.location_name
		`)
		defer rows.Close()

		var results []ReservoirByLocation
		for rows.Next() {
			var r ReservoirByLocation
			if err := rows.Scan(&r.LocationID, &r.LocationName, &r.ReservoirName, &r.CurrentLevelM, &r.CapacityM3, &r.FillPercentage, &r.Status); err != nil {
				continue
			}
			results = append(results, r)
		}

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(results)
	}
}

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

		rows, _ := db.pool.Query(ctx, `
			SELECT
				ir.location_id,
				COALESCE(lm.location_name, 'Unknown'),
				COALESCE(ir.health_score, 50),
				COALESCE(ir.last_inspected_date, CURRENT_DATE)::TEXT,
				CASE WHEN ir.maintenance_status = 1 THEN 'Good' WHEN ir.maintenance_status = 0 THEN 'Needs Attention' ELSE 'Unknown' END
			FROM ic3_reservoirs ir
			LEFT JOIN ic3_location_master lm ON ir.location_id = lm.location_id
			ORDER BY ir.health_score DESC
		`)
		defer rows.Close()

		var results []ReservoirHealth
		for rows.Next() {
			var h ReservoirHealth
			if err := rows.Scan(&h.LocationID, &h.LocationName, &h.HealthScore, &h.LastUpdated, &h.MaintenanceStatus); err != nil {
				continue
			}
			results = append(results, h)
		}

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(results)
	}
}

// ============================================================================
// PUMPING STATION HANDLERS (Query ic3_pumping_stations table)
// ============================================================================

type PumpingStationSummaryResp struct {
	TotalStations     int     `json:"total_stations"`
	OperationalCount  int     `json:"operational_count"`
	TotalCapacityLPS  float64 `json:"total_capacity_lps"`
	AvgDischargeBar   float64 `json:"avg_discharge_pressure_bar"`
}

type PumpingStationItemResp struct {
	StationID         int     `json:"station_id"`
	StationName       string  `json:"station_name"`
	LocationID        int     `json:"location_id"`
	PumpCount         int     `json:"pump_count"`
	TotalCapacityLPS  float64 `json:"total_capacity_lps"`
	DischargePressure float64 `json:"discharge_pressure_bar"`
	PowerConsumptionKW float64 `json:"power_consumption_kw"`
	OperationalStatus string  `json:"operational_status"`
}

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

		var summary PumpingStationSummaryResp
		err := db.pool.QueryRow(ctx, `
			SELECT
				COALESCE(SUM(pump_count), 0)::INT as total_pumps,
				COUNT(CASE WHEN asset_status = 1 THEN 1 END)::INT as operational,
				COALESCE(SUM(total_capacity_lps), 0),
				COALESCE(AVG(discharge_pressure_bar), 0)
			FROM ic3_pumping_stations
		`).Scan(&summary.TotalStations, &summary.OperationalCount,
			&summary.TotalCapacityLPS, &summary.AvgDischargeBar)

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

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(summary)
	}
}

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

		rows, _ := db.pool.Query(ctx, `
			SELECT station_id, station_name, location_id, pump_count,
			       total_capacity_lps, discharge_pressure_bar, power_consumption_kw,
			       operational_status
			FROM ic3_pumping_stations
			ORDER BY station_id
		`)
		defer rows.Close()

		var stations []PumpingStationItemResp
		for rows.Next() {
			var s PumpingStationItemResp
			rows.Scan(&s.StationID, &s.StationName, &s.LocationID, &s.PumpCount,
				&s.TotalCapacityLPS, &s.DischargePressure, &s.PowerConsumptionKW,
				&s.OperationalStatus)
			stations = append(stations, s)
		}

		if stations == nil {
			stations = []PumpingStationItemResp{}
		}

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(map[string]interface{}{
			"total": len(stations),
			"data":  stations,
		})
	}
}

// ============================================================================
// METER HANDLERS (Query ic3_meters table)
// ============================================================================

type MeterSummaryResp struct {
	TotalMeters        int     `json:"total_meters"`
	ActiveMeters       int     `json:"active_meters"`
	TotalDailyVolumeM3 float64 `json:"total_daily_volume_m3"`
	AvgCurrentFlowLPS  float64 `json:"avg_current_flow_lps"`
}

type MeterByTypeResp struct {
	MeterType  string  `json:"meter_type"`
	Count      int     `json:"count"`
	TotalFlow  float64 `json:"total_flow"`
	AvgFlow    float64 `json:"avg_flow"`
}

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

		var summary MeterSummaryResp
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*)::INT as total,
				COUNT(CASE WHEN asset_status = 1 THEN 1 END)::INT as active,
				COALESCE(SUM(daily_volume_m3), 0),
				COALESCE(AVG(current_flow_lps), 0)
			FROM ic3_meters
		`).Scan(&summary.TotalMeters, &summary.ActiveMeters,
			&summary.TotalDailyVolumeM3, &summary.AvgCurrentFlowLPS)

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

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(summary)
	}
}

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

		rows, _ := db.pool.Query(ctx, `
			SELECT meter_type, COUNT(*)::INT as count,
			       COALESCE(SUM(current_flow_lps), 0) as total_flow,
			       COALESCE(AVG(current_flow_lps), 0) as avg_flow
			FROM ic3_meters
			GROUP BY meter_type
			ORDER BY count DESC
		`)
		defer rows.Close()

		var meters []MeterByTypeResp
		for rows.Next() {
			var m MeterByTypeResp
			rows.Scan(&m.MeterType, &m.Count, &m.TotalFlow, &m.AvgFlow)
			meters = append(meters, m)
		}

		if meters == nil {
			meters = []MeterByTypeResp{}
		}

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(map[string]interface{}{
			"total": len(meters),
			"data":  meters,
		})
	}
}

// ============================================================================
// DMA REGISTRY HANDLERS (Query ic3_dma_registry table)
// ============================================================================

type DMASummaryResp struct {
	TotalDMAs            int     `json:"total_dmas"`
	TotalAreaCoverageKm2 float64 `json:"total_area_coverage_km2"`
	TotalMetersInstalled int     `json:"total_meters_installed"`
	TotalPopulationServed int     `json:"total_population_served"`
}

type DMAItemResp struct {
	DMAID            int     `json:"dma_id"`
	DMAName          string  `json:"dma_name"`
	LocationID       int     `json:"location_id"`
	AreaKm2          float64 `json:"area_km2"`
	MetersInstalled  int     `json:"meters_installed"`
	CurrentFlowLPS   float64 `json:"current_flow_lps"`
	AvgDailyDemandM3 float64 `json:"avg_daily_demand_m3"`
	CoveragePercent  float64 `json:"coverage_percent"`
	AssetCount       int     `json:"asset_count"`
	PopulationServed int     `json:"population_served"`
	OperationalStatus string  `json:"operational_status"`
}

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

		var summary DMASummaryResp
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*)::INT as total,
				COALESCE(SUM(area_km2), 0),
				COALESCE(SUM(meters_installed), 0),
				COALESCE(SUM(population_served), 0)
			FROM ic3_dma_registry
		`).Scan(&summary.TotalDMAs, &summary.TotalAreaCoverageKm2,
			&summary.TotalMetersInstalled, &summary.TotalPopulationServed)

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

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(summary)
	}
}

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

		rows, _ := db.pool.Query(ctx, `
			SELECT dma_id, dma_name, location_id, area_km2, meters_installed,
			       current_flow_lps, avg_daily_demand_m3, coverage_percent,
			       asset_count, population_served, operational_status
			FROM ic3_dma_registry
			ORDER BY dma_id
		`)
		defer rows.Close()

		var dmas []DMAItemResp
		for rows.Next() {
			var d DMAItemResp
			rows.Scan(&d.DMAID, &d.DMAName, &d.LocationID, &d.AreaKm2,
				&d.MetersInstalled, &d.CurrentFlowLPS, &d.AvgDailyDemandM3,
				&d.CoveragePercent, &d.AssetCount, &d.PopulationServed,
				&d.OperationalStatus)
			dmas = append(dmas, d)
		}

		if dmas == nil {
			dmas = []DMAItemResp{}
		}

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(map[string]interface{}{
			"total": len(dmas),
			"data":  dmas,
		})
	}
}