package main

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

// PressureZone represents a pressure zone in the supply system
type PressureZone struct {
	ZoneID           int     `json:"zone_id"`
	ZoneName         string  `json:"zone_name"`
	LocationName     string  `json:"location_name"`
	MinPressure      float64 `json:"min_pressure_bar"`
	MaxPressure      float64 `json:"max_pressure_bar"`
	AvgPressure      float64 `json:"avg_pressure_bar"`
	SupplyType       string  `json:"supply_type"`
	ConnectionsCount int     `json:"connections_count"`
	PopulationServed int     `json:"population_served"`
	DemandLpd        float64 `json:"demand_lpd"`
}

// PumpStation represents a pump station
type PumpStation struct {
	StationID          int     `json:"station_id"`
	StationName        string  `json:"station_name"`
	LocationName       string  `json:"location_name"`
	CapacityLps        float64 `json:"capacity_lps"`
	InstalledPowerKw   float64 `json:"installed_power_kw"`
	NumberOfPumps      int     `json:"number_of_pumps"`
	PumpType           string  `json:"pump_type"`
	DischargePressure  float64 `json:"discharge_pressure_bar"`
	SuctionPressure    float64 `json:"suction_pressure_bar"`
	Status             string  `json:"status"`
	EnergyConsumption  float64 `json:"energy_consumption_kwh"`
	OperationalHours   int     `json:"operational_hours"`
}

// SupplyMetric represents supply metrics for a zone
type SupplyMetric struct {
	ZoneID                  int     `json:"zone_id"`
	LocationName            string  `json:"location_name"`
	SystemPressureBar       float64 `json:"system_pressure_bar"`
	FlowRateLps             float64 `json:"flow_rate_lps"`
	SupplyHoursPerDay       int     `json:"supply_hours_per_day"`
	ReliabilityPercent      float64 `json:"reliability_percent"`
	WaterQualityScore       int     `json:"water_quality_score"`
	ConsumerSatisfaction    int     `json:"consumer_satisfaction_score"`
	PeakDemandHour          string  `json:"peak_demand_hour"`
	PeakFlowLps             float64 `json:"peak_flow_lps"`
	WaterLossPercent        float64 `json:"water_loss_percent"`
}

// ServiceConnection represents a consumer service connection
type ServiceConnection struct {
	ConnectionID      int     `json:"connection_id"`
	ConnectionNumber  string  `json:"connection_number"`
	ConsumerType      string  `json:"consumer_type"`
	MeterType         string  `json:"meter_type"`
	CurrentReading    float64 `json:"current_meter_reading"`
	DemandLpd         float64 `json:"demand_lpd"`
	Status            string  `json:"connection_status"`
	LastReadingDate   string  `json:"last_reading_date"`
	TariffCategory    string  `json:"tariff_category"`
}

// SupplyMaintenanceRecord represents maintenance history
type SupplyMaintenanceRecord struct {
	MaintenanceID   int     `json:"maintenance_id"`
	StationType     string  `json:"station_name"`
	MaintenanceType string  `json:"maintenance_type"`
	Cost            float64 `json:"cost"`
	MaintenanceDate string  `json:"maintenance_date"`
	CompletionDate  string  `json:"completion_date"`
	Status          string  `json:"status"`
	TechnicianName  string  `json:"technician_name"`
}

// SupplySummary represents overall supply statistics
type SupplySummary struct {
	TotalZones          int     `json:"total_zones"`
	TotalConnections    int     `json:"total_connections"`
	TotalPumpStations   int     `json:"total_pump_stations"`
	AvgSystemPressure   float64 `json:"avg_system_pressure"`
	AvgReliability      float64 `json:"avg_reliability_percent"`
	TotalPopulationServed int   `json:"total_population_served"`
	AvgWaterLoss        float64 `json:"avg_water_loss_percent"`
}

// GetSupplySummary returns overall supply statistics
func GetSupplySummary(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 SupplySummary
		err := db.pool.QueryRow(ctx, `
			SELECT
				(SELECT COUNT(*) FROM ic3_pressure_zones WHERE asset_status = 1) as total_zones,
				(SELECT COUNT(*) FROM ic3_service_connections WHERE asset_status = 1) as total_connections,
				(SELECT COUNT(*) FROM ic3_pump_stations WHERE asset_status = 1) as total_stations,
				COALESCE((SELECT AVG(avg_pressure_bar) FROM ic3_pressure_zones WHERE asset_status = 1), 0) as avg_pressure,
				COALESCE((SELECT AVG(reliability_percent) FROM ic3_supply_metrics), 0) as avg_reliability,
				COALESCE((SELECT SUM(population_served) FROM ic3_pressure_zones WHERE asset_status = 1), 0) as total_population,
				COALESCE((SELECT AVG(water_loss_percent) FROM ic3_supply_metrics), 0) as avg_water_loss
		`).Scan(&summary.TotalZones, &summary.TotalConnections, &summary.TotalPumpStations,
			&summary.AvgSystemPressure, &summary.AvgReliability, &summary.TotalPopulationServed, &summary.AvgWaterLoss)

		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)
	}
}

// GetPressureZones returns all pressure zones
func GetPressureZones(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
				pz.zone_id,
				pz.zone_name,
				COALESCE(lm.location_name, 'Unknown') as location_name,
				pz.min_pressure_bar,
				pz.max_pressure_bar,
				pz.avg_pressure_bar,
				pz.supply_type,
				COALESCE(pz.connections_count, 0),
				COALESCE(pz.population_served, 0),
				COALESCE(pz.demand_lpd, 0)
			FROM ic3_pressure_zones pz
			LEFT JOIN ic3_location_master lm ON pz.location_id = lm.location_id
			WHERE pz.asset_status = 1
			ORDER BY pz.zone_id
		`)
		defer rows.Close()

		var results []PressureZone
		for rows.Next() {
			var z PressureZone
			if err := rows.Scan(&z.ZoneID, &z.ZoneName, &z.LocationName, &z.MinPressure,
				&z.MaxPressure, &z.AvgPressure, &z.SupplyType, &z.ConnectionsCount,
				&z.PopulationServed, &z.DemandLpd); err != nil {
				continue
			}
			results = append(results, z)
		}

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

// GetPumpStations returns all pump stations
func GetPumpStations(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
				ps.station_id,
				ps.station_name,
				COALESCE(lm.location_name, 'Unknown') as location_name,
				ps.capacity_lps,
				ps.installed_power_kw,
				ps.number_of_pumps,
				ps.pump_type,
				ps.discharge_pressure_bar,
				ps.suction_pressure_bar,
				ps.status,
				ps.energy_consumption_kwh,
				ps.operational_hours
			FROM ic3_pump_stations ps
			LEFT JOIN ic3_location_master lm ON ps.location_id = lm.location_id
			WHERE ps.asset_status = 1
			ORDER BY ps.station_id
		`)
		defer rows.Close()

		var results []PumpStation
		for rows.Next() {
			var p PumpStation
			if err := rows.Scan(&p.StationID, &p.StationName, &p.LocationName, &p.CapacityLps,
				&p.InstalledPowerKw, &p.NumberOfPumps, &p.PumpType, &p.DischargePressure,
				&p.SuctionPressure, &p.Status, &p.EnergyConsumption, &p.OperationalHours); err != nil {
				continue
			}
			results = append(results, p)
		}

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

// GetSupplyMetrics returns supply metrics by zone
func GetSupplyMetrics(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
				sm.zone_id,
				COALESCE(lm.location_name, 'Unknown'),
				COALESCE(AVG(sm.system_pressure_bar), 0),
				COALESCE(AVG(sm.flow_rate_lps), 0),
				COALESCE(AVG(sm.supply_hours_per_day), 0),
				COALESCE(AVG(sm.reliability_percent), 0),
				COALESCE(AVG(sm.water_quality_score), 0),
				COALESCE(AVG(sm.consumer_satisfaction_score), 0),
				MAX(sm.peak_demand_hour),
				COALESCE(MAX(sm.peak_flow_lps), 0),
				COALESCE(AVG(sm.water_loss_percent), 0)
			FROM ic3_supply_metrics sm
			LEFT JOIN ic3_location_master lm ON sm.location_id = lm.location_id
			GROUP BY sm.zone_id, lm.location_name
			ORDER BY sm.zone_id
		`)
		defer rows.Close()

		var results []SupplyMetric
		for rows.Next() {
			var m SupplyMetric
			if err := rows.Scan(&m.ZoneID, &m.LocationName, &m.SystemPressureBar,
				&m.FlowRateLps, &m.SupplyHoursPerDay, &m.ReliabilityPercent,
				&m.WaterQualityScore, &m.ConsumerSatisfaction, &m.PeakDemandHour,
				&m.PeakFlowLps, &m.WaterLossPercent); err != nil {
				continue
			}
			results = append(results, m)
		}

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

// GetServiceConnections returns service connections
func GetServiceConnections(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
				connection_id,
				connection_number,
				consumer_type,
				meter_type,
				current_meter_reading,
				demand_lpd,
				connection_status,
				last_reading_date::TEXT,
				tariff_category
			FROM ic3_service_connections
			WHERE asset_status = 1
			ORDER BY connection_id
			LIMIT 100
		`)
		defer rows.Close()

		var results []ServiceConnection
		for rows.Next() {
			var sc ServiceConnection
			if err := rows.Scan(&sc.ConnectionID, &sc.ConnectionNumber, &sc.ConsumerType,
				&sc.MeterType, &sc.CurrentReading, &sc.DemandLpd, &sc.Status,
				&sc.LastReadingDate, &sc.TariffCategory); err != nil {
				continue
			}
			results = append(results, sc)
		}

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

// GetSupplyMaintenance returns maintenance records
func GetSupplyMaintenance(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
				sm.maintenance_id,
				COALESCE(ps.station_name, 'Station'),
				sm.maintenance_type,
				sm.cost,
				sm.maintenance_date::TEXT,
				sm.completion_date::TEXT,
				sm.status,
				COALESCE(sm.technician_name, 'N/A')
			FROM ic3_supply_maintenance sm
			LEFT JOIN ic3_pump_stations ps ON sm.station_id = ps.station_id
			ORDER BY sm.maintenance_date DESC
			LIMIT 100
		`)
		defer rows.Close()

		var results []SupplyMaintenanceRecord
		for rows.Next() {
			var m SupplyMaintenanceRecord
			if err := rows.Scan(&m.MaintenanceID, &m.StationType, &m.MaintenanceType,
				&m.Cost, &m.MaintenanceDate, &m.CompletionDate, &m.Status,
				&m.TechnicianName); err != nil {
				continue
			}
			results = append(results, m)
		}

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