package main

import (
	"context"
	"encoding/json"
	"fmt"
	"log"
	"math"
	"net/http"
	"strings"
	"time"

	"github.com/redis/go-redis/v9"
)

// DMAWaterBalance represents water balance metrics from nrw_dma_water_balance
type DMAWaterBalance struct {
	BalanceID        string    `json:"balanceId"`
	DMAID            string    `json:"dmaId"`
	BalancePeriod    string    `json:"balancePeriod"`
	PeriodStart      time.Time `json:"periodStart"`
	PeriodEnd        time.Time `json:"periodEnd"`
	CalculationDate  time.Time `json:"calculationDate"`
	InletVolumeM3    float64   `json:"inletVolumeM3"`
	NRWVolumeM3      float64   `json:"nrwVolumeM3"`
	NRWPercentage    float64   `json:"nrwPercentage"`
	CalculatedAt     time.Time `json:"calculatedAt"`
}

// ============================================================================
// NRW Engine Worker
// ============================================================================

// RunNRWEngine periodically calculates NRW (Non-Revenue Water) metrics
func RunNRWEngine(ctx context.Context, db *DB, rdb *redis.Client) {
	ticker := time.NewTicker(15 * time.Minute)
	defer ticker.Stop()

	log.Println("NRW engine started (15 minute interval)")

	// Run once immediately, then on ticker
	processNRW(ctx, db, rdb)

	for {
		select {
		case <-ctx.Done():
			log.Println("NRW engine stopped")
			return
		case <-ticker.C:
			processNRW(ctx, db, rdb)
		}
	}
}

func processNRW(ctx context.Context, db *DB, rdb *redis.Client) {
	ctx, cancel := context.WithTimeout(ctx, 14*time.Minute)
	defer cancel()

	// Query active DMAs
	rows, err := db.pool.Query(ctx, `
		SELECT dma_id, dma_name, nrw_target_pct
		FROM dma_master
		WHERE boundary_status = 'ACTIVE'
	`)
	if err != nil {
		log.Printf("nrw engine query error: %v", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var dmaID, dmaName string
		var nrwTarget float64

		if err := rows.Scan(&dmaID, &dmaName, &nrwTarget); err != nil {
			log.Printf("nrw scan error: %v", err)
			continue
		}

		// Get inlet volume from NRW input tags (hourly, convert to 15-min)
		var inletM3h float64
		err := db.pool.QueryRow(ctx, `
			SELECT COALESCE(SUM(tc.value_num), 0)
			FROM telemetry_current tc
			JOIN ic3_tag_master t ON tc.tag_id = t.tag_id
			WHERE tc.dma_id = $1
			AND t.is_nrw_input = true
		`, dmaID).Scan(&inletM3h)

		if err != nil {
			log.Printf("inlet query error for dma %s: %v", dmaID, err)
			continue
		}

		// Convert from hourly to 15-minute volume
		inletM3 := inletM3h * 0.25

		// Get billed volume from meter readings
		var billedM3 float64
		err = db.pool.QueryRow(ctx, `
			SELECT COALESCE(SUM(interval_volume_m3), 0)
			FROM mdm_meter_reading
			WHERE dma_id = $1
			AND reading_time >= NOW() - INTERVAL '15 minutes'
		`, dmaID).Scan(&billedM3)

		if err != nil {
			log.Printf("billed query error for dma %s: %v", dmaID, err)
			continue
		}

		// Calculate NRW
		nrwM3 := inletM3 - billedM3
		var nrwPct float64
		if inletM3 == 0 {
			nrwPct = 0
		} else {
			nrwPct = (nrwM3 / inletM3) * 100
		}
		nrwPct = math.Max(0, nrwPct) // NRW can't be negative

		// Insert into nrw_dma_water_balance
		now := time.Now()
		periodStart := now.Add(-15 * time.Minute)
		var balanceID string
		err = db.pool.QueryRow(ctx, `
			INSERT INTO nrw_dma_water_balance (
				dma_id, balance_period, period_start, period_end,
				calculation_date, inlet_volume_m3, nrw_volume_m3,
				nrw_percentage, calculated_at
			) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
			RETURNING balance_id
		`,
			dmaID, "15MIN", periodStart, now,
			now.Truncate(24*time.Hour), inletM3, nrwM3,
			nrwPct, now,
		).Scan(&balanceID)

		if err != nil {
			log.Printf("insert nrw balance error for dma %s: %v", dmaID, err)
			continue
		}

		// Cache to Redis for 15 minutes
		nrwData := map[string]interface{}{
			"balance_id":      balanceID,
			"dma_id":          dmaID,
			"dma_name":        dmaName,
			"inlet_volume_m3": inletM3,
			"nrw_volume_m3":   nrwM3,
			"nrw_percentage":  nrwPct,
			"nrw_target_pct":  nrwTarget,
			"calculated_at":   now,
		}
		nrwJSON, _ := json.Marshal(nrwData)
		rdb.Set(ctx, fmt.Sprintf("nrw:live:%s", dmaID), string(nrwJSON), 15*time.Minute)

		// If NRW exceeds target, create alarm event
		if nrwPct > nrwTarget {
			severity := "MEDIUM"
			if nrwPct > nrwTarget*1.5 {
				severity = "HIGH"
			}
			if nrwPct > nrwTarget*2.0 {
				severity = "CRITICAL"
			}

			eventMessage := fmt.Sprintf("NRW alert for %s: current=%.2f%%, target=%.2f%%",
				dmaName, nrwPct, nrwTarget)

			_, err := db.pool.Exec(ctx, `
				INSERT INTO ic3_event_alarm (
					event_code, event_type, severity, dma_id,
					event_message, event_time, status
				) VALUES ($1, $2, $3, $4, $5, $6, $7)
			`,
				"NRW_ALERT_"+strings.ToUpper(dmaID), "NRW", severity,
				dmaID, eventMessage, now, "OPEN",
			)
			if err != nil {
				log.Printf("insert nrw alarm error for dma %s: %v", dmaID, err)
			}
		}
	}
}

// ============================================================================
// HTTP Handlers
// ============================================================================

// GET /api/v1/nrw/live
func getNRWLiveHandler(db *DB, rdb *redis.Client) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		// Try to read all Redis NRW keys
		keys, err := rdb.Keys(ctx, "nrw:live:*").Result()
		if err == nil && len(keys) > 0 {
			var nrwData []map[string]interface{}
			for _, key := range keys {
				val, err := rdb.Get(ctx, key).Result()
				if err == nil {
					var data map[string]interface{}
					if json.Unmarshal([]byte(val), &data) == nil {
						nrwData = append(nrwData, data)
					}
				}
			}
			if len(nrwData) > 0 {
				w.Header().Set("Content-Type", "application/json")
				json.NewEncoder(w).Encode(nrwData)
				return
			}
		}

		// Fallback to database: get latest balance per DMA
		rows, err := db.pool.Query(ctx, `
			SELECT DISTINCT ON (dma_id)
				balance_id, dma_id, balance_period, period_start, period_end,
				calculation_date, inlet_volume_m3, nrw_volume_m3,
				nrw_percentage, calculated_at
			FROM nrw_dma_water_balance
			ORDER BY dma_id, calculated_at DESC
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var balances []DMAWaterBalance
		for rows.Next() {
			var b DMAWaterBalance
			err := rows.Scan(
				&b.BalanceID, &b.DMAID, &b.BalancePeriod, &b.PeriodStart, &b.PeriodEnd,
				&b.CalculationDate, &b.InletVolumeM3, &b.NRWVolumeM3,
				&b.NRWPercentage, &b.CalculatedAt,
			)
			if err != nil {
				continue
			}
			balances = append(balances, b)
		}

		if balances == nil {
			balances = []DMAWaterBalance{}
		}

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

// GET /api/v1/nrw/{dma_id}/history
func getNRWHistoryHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		dmaID := r.PathValue("dma_id")
		if dmaID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "dma_id required"})
			return
		}

		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		// Get last 24 hours of NRW data
		rows, err := db.pool.Query(ctx, `
			SELECT balance_id, dma_id, balance_period, period_start, period_end,
				calculation_date, inlet_volume_m3, nrw_volume_m3,
				nrw_percentage, calculated_at
			FROM nrw_dma_water_balance
			WHERE dma_id = $1
			AND calculated_at >= NOW() - INTERVAL '24 hours'
			ORDER BY calculated_at DESC
		`, dmaID)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var balances []DMAWaterBalance
		for rows.Next() {
			var b DMAWaterBalance
			err := rows.Scan(
				&b.BalanceID, &b.DMAID, &b.BalancePeriod, &b.PeriodStart, &b.PeriodEnd,
				&b.CalculationDate, &b.InletVolumeM3, &b.NRWVolumeM3,
				&b.NRWPercentage, &b.CalculatedAt,
			)
			if err != nil {
				continue
			}
			balances = append(balances, b)
		}

		if balances == nil {
			balances = []DMAWaterBalance{}
		}

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