package main

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

// PipeNetworkStats holds overall network statistics
type PipeNetworkStats struct {
	TotalSegments   int     `json:"total_segments"`
	TotalLength     float64 `json:"total_length_km"`
	AvgAge          float64 `json:"avg_age_years"`
	MaterialCount   int     `json:"material_count"`
	AvgHealthScore  float64 `json:"avg_health_score"`
	AvgNRW          float64 `json:"avg_nrw_percentage"`
}

// PipeByDiameter represents pipe count by diameter
type PipeByDiameter struct {
	Diameter string `json:"diameter"`
	Count    int    `json:"count"`
	Pct      float64 `json:"pct"`
}

// PipeByMaterial represents pipe count by material type
type PipeByMaterial struct {
	Material string `json:"material"`
	Count    int    `json:"count"`
	Pct      float64 `json:"pct"`
}

// PipeByAge represents pipe count by age group
type PipeByAge struct {
	AgeGroup string `json:"age_group"`
	Count    int    `json:"count"`
	Pct      float64 `json:"pct"`
}

// MaintenanceRecord represents maintenance history
type MaintenanceRecord struct {
	Type     string  `json:"type"`
	Count    int     `json:"count"`
	AvgCost  float64 `json:"avg_cost"`
	LastDate string  `json:"last_date"`
}

// NetworkHealthByLocation represents NRW by DMA
type NetworkHealthByLocation struct {
	Location     string  `json:"location"`
	NRW          float64 `json:"nrw_percentage"`
	WaterLoss    float64 `json:"water_loss_lpd"`
	EfficiencyScore int   `json:"efficiency_score"`
}

// GetPipeNetworkSummary returns overall network statistics
func GetPipeNetworkSummary(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		var stats PipeNetworkStats
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*) AS total_segments,
				COALESCE(SUM(length_m) / 1000.0, 0) AS total_length_km,
				COALESCE(AVG(EXTRACT(YEAR FROM CURRENT_DATE) - installation_year), 0) AS avg_age,
				COUNT(DISTINCT material) AS material_count,
				0 AS avg_health_score,
				0 AS avg_nrw
			FROM ic3_pipe_network
			WHERE asset_status = 1
		`).Scan(&stats.TotalSegments, &stats.TotalLength, &stats.AvgAge, &stats.MaterialCount, &stats.AvgHealthScore, &stats.AvgNRW)

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

// GetPipesByDiameter returns pipe segments grouped by diameter
func GetPipesByDiameter(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
				CASE
					WHEN diameter_mm <= 50 THEN '25-50 mm'
					WHEN diameter_mm <= 100 THEN '50-100 mm'
					WHEN diameter_mm <= 200 THEN '100-200 mm'
					WHEN diameter_mm <= 500 THEN '200-500 mm'
					ELSE '500+ mm'
				END AS diameter,
				COUNT(*) AS count,
				ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
			FROM ic3_pipe_network
			WHERE asset_status = 1 AND diameter_mm IS NOT NULL
			GROUP BY 1
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []PipeByDiameter
		for rows.Next() {
			var d PipeByDiameter
			if err := rows.Scan(&d.Diameter, &d.Count, &d.Pct); err != nil {
				continue
			}
			results = append(results, d)
		}

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

// GetPipesByMaterial returns pipe segments grouped by material
func GetPipesByMaterial(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
				COALESCE(material, 'Unknown') AS material,
				COUNT(*) AS count,
				ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
			FROM ic3_pipe_network
			WHERE asset_status = 1
			GROUP BY material
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []PipeByMaterial
		for rows.Next() {
			var m PipeByMaterial
			if err := rows.Scan(&m.Material, &m.Count, &m.Pct); err != nil {
				continue
			}
			results = append(results, m)
		}

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

// GetPipesByAge returns pipe segments grouped by age
func GetPipesByAge(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
				CASE
					WHEN installation_year IS NULL THEN 'Unknown'
					WHEN EXTRACT(YEAR FROM CURRENT_DATE) - installation_year < 5 THEN '0-5 years'
					WHEN EXTRACT(YEAR FROM CURRENT_DATE) - installation_year < 10 THEN '5-10 years'
					WHEN EXTRACT(YEAR FROM CURRENT_DATE) - installation_year < 20 THEN '10-20 years'
					WHEN EXTRACT(YEAR FROM CURRENT_DATE) - installation_year < 50 THEN '20-50 years'
					ELSE '50+ years'
				END AS age_group,
				COUNT(*) AS count,
				ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
			FROM ic3_pipe_network
			WHERE asset_status = 1
			GROUP BY 1
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []PipeByAge
		for rows.Next() {
			var a PipeByAge
			if err := rows.Scan(&a.AgeGroup, &a.Count, &a.Pct); err != nil {
				continue
			}
			results = append(results, a)
		}

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

// GetMaintenanceHistory returns maintenance records
func GetMaintenanceHistory(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
				maintenance_type,
				COUNT(*) AS count,
				ROUND(AVG(cost), 2) AS avg_cost,
				MAX(maintenance_date)::TEXT AS last_date
			FROM ic3_network_maintenance
			GROUP BY maintenance_type
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []MaintenanceRecord
		for rows.Next() {
			var m MaintenanceRecord
			if err := rows.Scan(&m.Type, &m.Count, &m.AvgCost, &m.LastDate); err != nil {
				continue
			}
			results = append(results, m)
		}

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

// GetNetworkHealthByLocation returns NRW and efficiency by DMA
func GetNetworkHealthByLocation(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
				COALESCE(lm.location_name, 'Unknown') AS location,
				ROUND(AVG(nh.nrw_percentage), 1) AS nrw,
				ROUND(AVG(nh.water_loss_lpd), 0) AS water_loss,
				ROUND(AVG(nh.efficiency_score), 0) AS efficiency
			FROM ic3_network_health nh
			JOIN ic3_location_master lm ON nh.location_id = lm.location_id
			GROUP BY lm.location_id, lm.location_name
			ORDER BY nrw DESC
		`)
		defer rows.Close()

		var results []NetworkHealthByLocation
		for rows.Next() {
			var h NetworkHealthByLocation
			if err := rows.Scan(&h.Location, &h.NRW, &h.WaterLoss, &h.EfficiencyScore); err != nil {
				continue
			}
			results = append(results, h)
		}

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