package main

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

// Valve represents a valve in the distribution network
type Valve struct {
	ValveID              int     `json:"valve_id"`
	ValveName            string  `json:"valve_name"`
	ValveType            string  `json:"valve_type"`
	LocationName         string  `json:"location_name"`
	SizeMm               int     `json:"size_mm"`
	Material             string  `json:"material"`
	InstallationYear     int     `json:"installation_year"`
	Manufacturer         string  `json:"manufacturer"`
	OperationalStatus    string  `json:"operational_status"`
	LastMaintenanceDate  string  `json:"last_maintenance_date"`
	Criticality          string  `json:"criticality"`
}

// Hydrant represents a hydrant in the distribution network
type Hydrant struct {
	HydrantID           int     `json:"hydrant_id"`
	HydrantName         string  `json:"hydrant_name"`
	HydrantType         string  `json:"hydrant_type"`
	LocationName        string  `json:"location_name"`
	SizeMm              int     `json:"size_mm"`
	Material            string  `json:"material"`
	InstallationYear    int     `json:"installation_year"`
	Manufacturer        string  `json:"manufacturer"`
	FlowRateLps         float64 `json:"flow_rate_lps"`
	OperationalStatus   string  `json:"operational_status"`
	LastTestDate        string  `json:"last_test_date"`
	Criticality         string  `json:"criticality"`
}

// ValveHealth represents valve operational health
type ValveHealth struct {
	ValveID          int     `json:"valve_id"`
	ValveName        string  `json:"valve_name"`
	OperationalStatus string  `json:"operational_status"`
	PressureRatingBar float64 `json:"pressure_rating_bar"`
	FlowCapacityLps  float64 `json:"flow_capacity_lps"`
	LeakageStatus    string  `json:"leakage_status"`
	PerformanceScore int     `json:"performance_score"`
}

// HydrantHealth represents hydrant operational health
type HydrantHealth struct {
	HydrantID           int     `json:"hydrant_id"`
	HydrantName         string  `json:"hydrant_name"`
	OperationalStatus   string  `json:"operational_status"`
	FlowTestResult      float64 `json:"flow_test_result"`
	PressureAvailableBar float64 `json:"pressure_available_bar"`
	ConditionRating     string  `json:"condition_rating"`
	PerformanceScore    int     `json:"performance_score"`
}

// ValveStats holds overall valve statistics
type ValveStats struct {
	TotalValves         int     `json:"total_valves"`
	OperationalCount    int     `json:"operational_count"`
	MaintenanceNeeded   int     `json:"maintenance_needed"`
	AvgAge              float64 `json:"avg_age_years"`
	AvgPerformanceScore float64 `json:"avg_performance_score"`
}

// HydrantStats holds overall hydrant statistics
type HydrantStats struct {
	TotalHydrants       int     `json:"total_hydrants"`
	OperationalCount    int     `json:"operational_count"`
	DefectiveCount      int     `json:"defective_count"`
	AvgAge              float64 `json:"avg_age_years"`
	AvgFlowRateLps      float64 `json:"avg_flow_rate_lps"`
	AvgPerformanceScore float64 `json:"avg_performance_score"`
}

// GetValveStats returns overall valve statistics
func GetValveStats(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 ValveStats
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*) as total_valves,
				SUM(CASE WHEN operational_status = 'Operational' THEN 1 ELSE 0 END) as operational,
				SUM(CASE WHEN operational_status = 'Maintenance' THEN 1 ELSE 0 END) as maintenance_needed,
				COALESCE(AVG(EXTRACT(YEAR FROM CURRENT_DATE) - installation_year), 0) as avg_age,
				0 as avg_performance
			FROM ic3_valves
			WHERE asset_status = 1
		`).Scan(&stats.TotalValves, &stats.OperationalCount, &stats.MaintenanceNeeded, &stats.AvgAge, &stats.AvgPerformanceScore)

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

// GetValvesByType returns valves grouped by type
func GetValvesByType(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
				valve_type,
				COUNT(*) as count,
				ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as pct
			FROM ic3_valves
			WHERE asset_status = 1
			GROUP BY valve_type
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []map[string]interface{}
		for rows.Next() {
			var valveType string
			var count int
			var pct float64
			if err := rows.Scan(&valveType, &count, &pct); err != nil {
				continue
			}
			results = append(results, map[string]interface{}{
				"valve_type": valveType,
				"count":      count,
				"pct":        pct,
			})
		}

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

// GetValvesBySize returns valves grouped by size
func GetValvesBySize(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
				CONCAT(size_mm, ' mm') as size,
				COUNT(*) as count,
				ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as pct
			FROM ic3_valves
			WHERE asset_status = 1
			GROUP BY size_mm
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []map[string]interface{}
		for rows.Next() {
			var size string
			var count int
			var pct float64
			if err := rows.Scan(&size, &count, &pct); err != nil {
				continue
			}
			results = append(results, map[string]interface{}{
				"size":  size,
				"count": count,
				"pct":   pct,
			})
		}

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

// GetValveHealth returns valve health status
func GetValveHealth(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
				vh.valve_id,
				v.valve_name,
				vh.operational_status,
				vh.pressure_rating_bar,
				vh.flow_capacity_lps,
				vh.leakage_status,
				vh.performance_score
			FROM ic3_valve_health vh
			JOIN ic3_valves v ON vh.valve_id = v.valve_id
			WHERE v.asset_status = 1
			ORDER BY vh.performance_score DESC
			LIMIT 100
		`)
		defer rows.Close()

		var results []ValveHealth
		for rows.Next() {
			var vh ValveHealth
			if err := rows.Scan(&vh.ValveID, &vh.ValveName, &vh.OperationalStatus,
				&vh.PressureRatingBar, &vh.FlowCapacityLps, &vh.LeakageStatus, &vh.PerformanceScore); err != nil {
				continue
			}
			results = append(results, vh)
		}

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

// GetHydrantStats returns overall hydrant statistics
func GetHydrantStats(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 HydrantStats
		err := db.pool.QueryRow(ctx, `
			SELECT
				COUNT(*) as total_hydrants,
				SUM(CASE WHEN operational_status = 'Operational' THEN 1 ELSE 0 END) as operational,
				SUM(CASE WHEN operational_status = 'Defective' THEN 1 ELSE 0 END) as defective,
				COALESCE(AVG(EXTRACT(YEAR FROM CURRENT_DATE) - installation_year), 0) as avg_age,
				COALESCE(AVG(flow_rate_lps), 0) as avg_flow,
				0 as avg_performance
			FROM ic3_hydrants
			WHERE asset_status = 1
		`).Scan(&stats.TotalHydrants, &stats.OperationalCount, &stats.DefectiveCount,
			&stats.AvgAge, &stats.AvgFlowRateLps, &stats.AvgPerformanceScore)

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

// GetHydrantsByType returns hydrants grouped by type
func GetHydrantsByType(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
				hydrant_type,
				COUNT(*) as count,
				ROUND(AVG(flow_rate_lps), 1) as avg_flow
			FROM ic3_hydrants
			WHERE asset_status = 1
			GROUP BY hydrant_type
			ORDER BY count DESC
		`)
		defer rows.Close()

		var results []map[string]interface{}
		for rows.Next() {
			var hydrantType string
			var count int
			var avgFlow float64
			if err := rows.Scan(&hydrantType, &count, &avgFlow); err != nil {
				continue
			}
			results = append(results, map[string]interface{}{
				"hydrant_type": hydrantType,
				"count":        count,
				"avg_flow_lps": avgFlow,
			})
		}

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

// GetHydrantHealth returns hydrant health status
func GetHydrantHealth(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
				hh.hydrant_id,
				h.hydrant_name,
				hh.operational_status,
				hh.flow_test_result,
				hh.pressure_available_bar,
				hh.condition_rating,
				hh.performance_score
			FROM ic3_hydrant_health hh
			JOIN ic3_hydrants h ON hh.hydrant_id = h.hydrant_id
			WHERE h.asset_status = 1
			ORDER BY hh.performance_score DESC
			LIMIT 100
		`)
		defer rows.Close()

		var results []HydrantHealth
		for rows.Next() {
			var hh HydrantHealth
			if err := rows.Scan(&hh.HydrantID, &hh.HydrantName, &hh.OperationalStatus,
				&hh.FlowTestResult, &hh.PressureAvailableBar, &hh.ConditionRating, &hh.PerformanceScore); err != nil {
				continue
			}
			results = append(results, hh)
		}

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