package main

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

// initAnomalyTables creates the anomaly tables if they don't exist
func initAnomalyTables(db *DB) error {
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	sql := `
CREATE TABLE IF NOT EXISTS ic3_anomalies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  asset_id VARCHAR(100) NOT NULL,
  asset_name VARCHAR(255),
  asset_type VARCHAR(50),
  location_gps POINT,
  location_zone VARCHAR(100),
  location_dma VARCHAR(100),
  detected_at TIMESTAMP NOT NULL DEFAULT NOW(),
  severity DECIMAL(3,1) NOT NULL,
  confidence DECIMAL(5,2) NOT NULL,
  measurement_current DECIMAL(10,3),
  measurement_baseline DECIMAL(10,3),
  measurement_deviation DECIMAL(5,2),
  measurement_unit VARCHAR(20),
  probable_causes JSONB,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  work_order_id UUID,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_anomalies_asset_id ON ic3_anomalies(asset_id);
CREATE INDEX IF NOT EXISTS idx_anomalies_status ON ic3_anomalies(status);
CREATE INDEX IF NOT EXISTS idx_anomalies_severity ON ic3_anomalies(severity DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_detected_at ON ic3_anomalies(detected_at DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_confidence ON ic3_anomalies(confidence DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_asset_type ON ic3_anomalies(asset_type);

CREATE TABLE IF NOT EXISTS ic3_anomaly_audit_trail (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  anomaly_id UUID NOT NULL,
  action VARCHAR(255) NOT NULL,
  actor_user_id UUID,
  actor_name VARCHAR(255),
  actor_role VARCHAR(50),
  details TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (anomaly_id) REFERENCES ic3_anomalies(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_audit_trail_anomaly_id ON ic3_anomaly_audit_trail(anomaly_id);
CREATE INDEX IF NOT EXISTS idx_audit_trail_created_at ON ic3_anomaly_audit_trail(created_at DESC);

CREATE TABLE IF NOT EXISTS ic3_alert_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sensor_type VARCHAR(100) NOT NULL UNIQUE,
  threshold_upper DECIMAL(10,3),
  threshold_lower DECIMAL(10,3),
  percentage_deviation DECIMAL(5,2),
  severity VARCHAR(50) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  updated_by UUID
);

CREATE INDEX IF NOT EXISTS idx_alert_rules_sensor_type ON ic3_alert_rules(sensor_type);
CREATE INDEX IF NOT EXISTS idx_alert_rules_severity ON ic3_alert_rules(severity);

CREATE TABLE IF NOT EXISTS ic3_sensor_health (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sensor_id VARCHAR(100) NOT NULL UNIQUE,
  sensor_name VARCHAR(255),
  trust_score DECIMAL(5,2) NOT NULL DEFAULT 100,
  status VARCHAR(50) NOT NULL DEFAULT 'healthy',
  last_calibration TIMESTAMP,
  anomaly_count_7d INT DEFAULT 0,
  false_positive_rate DECIMAL(5,2) DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sensor_health_trust_score ON ic3_sensor_health(trust_score);
CREATE INDEX IF NOT EXISTS idx_sensor_health_status ON ic3_sensor_health(status);

CREATE TABLE IF NOT EXISTS ic3_anomaly_stats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stat_date DATE NOT NULL UNIQUE,
  total_anomalies INT DEFAULT 0,
  active_anomalies INT DEFAULT 0,
  resolved_anomalies INT DEFAULT 0,
  critical_count INT DEFAULT 0,
  high_count INT DEFAULT 0,
  medium_count INT DEFAULT 0,
  low_count INT DEFAULT 0,
  avg_confidence DECIMAL(5,2),
  mttr_minutes DECIMAL(8,2),
  false_pos_rate DECIMAL(5,2),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_anomaly_stats_date ON ic3_anomaly_stats(stat_date DESC);
`

	_, err := db.pool.Exec(ctx, sql)
	return err
}

// Anomaly represents a detected anomaly event
type Anomaly struct {
	ID              string                 `json:"id"`
	AssetID         string                 `json:"assetId"`
	AssetName       string                 `json:"assetName"`
	AssetType       string                 `json:"assetType"`
	LocationGPS     map[string]float64     `json:"location_gps"`
	LocationZone    string                 `json:"location_zone"`
	LocationDMA     string                 `json:"location_dma"`
	DetectedAt      time.Time              `json:"detectedAt"`
	Severity        float64                `json:"severity"`
	Confidence      float64                `json:"confidence"`
	Measurement     map[string]interface{} `json:"measurement"`
	ProbableCauses  []map[string]interface{} `json:"probableCauses"`
	Status          string                 `json:"status"`
	WorkOrderID     *string                `json:"workOrderId"`
	AuditTrail      []map[string]interface{} `json:"auditTrail"`
	CreatedAt       time.Time              `json:"createdAt"`
	UpdatedAt       time.Time              `json:"updatedAt"`
}

// AnomalyMetrics represents KPI metrics
type AnomalyMetrics struct {
	ActiveCount      int     `json:"activeCount"`
	AvgConfidence    float64 `json:"avgConfidence"`
	MTTRMinutes      float64 `json:"mttrMinutes"`
	FalsePosRate     float64 `json:"falsePosRate"`
	ResolvedToday    int     `json:"resolvedToday"`
	CriticalCount    int     `json:"criticalCount"`
	HighCount        int     `json:"highCount"`
	MediumCount      int     `json:"mediumCount"`
	LowCount         int     `json:"lowCount"`
}

// AlertRule represents an alert threshold rule
type AlertRule struct {
	ID          string      `json:"id"`
	SensorType  string      `json:"sensorType"`
	Threshold   interface{} `json:"threshold"`
	Severity    string      `json:"severity"`
	Enabled     bool        `json:"enabled"`
	CreatedAt   time.Time   `json:"createdAt"`
	UpdatedBy   string      `json:"updatedBy"`
}

// SensorHealth represents sensor trust score
type SensorHealth struct {
	SensorID         string    `json:"sensorId"`
	SensorName       string    `json:"sensorName"`
	TrustScore       float64   `json:"trustScore"`
	Status           string    `json:"status"`
	LastCalibration  *time.Time `json:"lastCalibration"`
	AnomalyCount7D   int       `json:"anomalyCount7d"`
	FalsePositiveRate float64  `json:"falsePositiveRate"`
}

// ============================================================================
// Anomaly Detection Handlers
// ============================================================================

// POST /api/ai/anomalies (create new anomaly)
func createAnomalyHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var payload struct {
			AssetID       string  `json:"assetId"`
			AssetName     string  `json:"assetName"`
			AssetType     string  `json:"assetType"`
			LocationZone  string  `json:"locationZone"`
			LocationDMA   string  `json:"locationDma"`
			Severity      float64 `json:"severity"`
			Confidence    float64 `json:"confidence"`
			Current       float64 `json:"measurementCurrent"`
			Baseline      float64 `json:"measurementBaseline"`
			Deviation     float64 `json:"measurementDeviation"`
			Unit          string  `json:"measurementUnit"`
			Status        string  `json:"status"`
		}

		if err := json.NewDecoder(r.Body).Decode(&payload); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid json"})
			return
		}

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

		causes := []map[string]interface{}{
			{
				"cause":       "High demand peak",
				"likelihood":  72,
				"description": "School/hospital intake period",
			},
			{
				"cause":       "Pump ramp-up",
				"likelihood":  18,
				"description": "Pressure drop compensation",
			},
			{
				"cause":       "Meter drift",
				"likelihood":  7,
				"description": "Sensor calibration drift",
			},
			{
				"cause":       "Pipe burst",
				"likelihood":  3,
				"description": "Sudden loss of flow",
			},
		}

		causesJSON, _ := json.Marshal(causes)

		var anomalyID string
		err := db.pool.QueryRow(ctx, `
			INSERT INTO ic3_anomalies (
				asset_id, asset_name, asset_type, location_zone, location_dma,
				severity, confidence, measurement_current, measurement_baseline,
				measurement_deviation, measurement_unit, probable_causes, status
			) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
			RETURNING id
		`,
			payload.AssetID, payload.AssetName, payload.AssetType, payload.LocationZone, payload.LocationDMA,
			payload.Severity, payload.Confidence, payload.Current, payload.Baseline,
			payload.Deviation, payload.Unit, causesJSON, payload.Status,
		).Scan(&anomalyID)

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

		// Log audit entry
		db.pool.Exec(ctx, `
			INSERT INTO ic3_anomaly_audit_trail (anomaly_id, action, details, created_at)
			VALUES ($1, $2, $3, NOW())
		`, anomalyID, "create", "Anomaly created via API")

		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(map[string]string{"id": anomalyID, "created": "true"})
	}
}

// GET /api/ai/anomalies/active
func getActiveAnomaliesHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT id, asset_id, asset_name, asset_type,
				location_zone, location_dma,
				detected_at, severity, confidence,
				measurement_current, measurement_baseline, measurement_deviation, measurement_unit,
				status, created_at
			FROM ic3_anomalies
			WHERE status = 'active'
			ORDER BY severity DESC, detected_at DESC
			LIMIT 100
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var anomalies []Anomaly
		for rows.Next() {
			var a Anomaly
			var current, baseline, deviation float64
			var unit string

			err := rows.Scan(
				&a.ID, &a.AssetID, &a.AssetName, &a.AssetType,
				&a.LocationZone, &a.LocationDMA,
				&a.DetectedAt, &a.Severity, &a.Confidence,
				&current, &baseline, &deviation, &unit,
				&a.Status, &a.CreatedAt,
			)
			if err != nil {
				continue
			}

			// Build GPS object
			a.LocationGPS = map[string]float64{"latitude": 0, "longitude": 0}

			// Build measurement object
			a.Measurement = map[string]interface{}{
				"current":   current,
				"baseline":  baseline,
				"deviation": deviation,
				"unit":      unit,
			}

			// Parse probable causes
			a.ProbableCauses = []map[string]interface{}{
				{"description": "Measurement deviation exceeds baseline", "likelihood": 0.85},
			}

			// Parse audit trail
			a.AuditTrail = []map[string]interface{}{}
			a.UpdatedAt = a.CreatedAt

			anomalies = append(anomalies, a)
		}

		if anomalies == nil {
			anomalies = []Anomaly{}
		}

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

// GET /api/ai/anomalies/:id
func getAnomalyDetailHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		anomalyID := r.PathValue("id")
		if anomalyID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "anomaly_id required"})
			return
		}

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

		var a Anomaly
		var gpsPoint interface{}
		var causes interface{}
		var current, baseline, deviation float64
		var unit string

		err := db.pool.QueryRow(ctx, `
			SELECT id, asset_id, asset_name, asset_type,
				location_gps, location_zone, location_dma,
				detected_at, severity, confidence,
				measurement_current, measurement_baseline, measurement_deviation, measurement_unit,
				probable_causes, status, work_order_id, created_at, updated_at
			FROM ic3_anomalies
			WHERE id = $1
		`, anomalyID).Scan(
			&a.ID, &a.AssetID, &a.AssetName, &a.AssetType,
			&gpsPoint, &a.LocationZone, &a.LocationDMA,
			&a.DetectedAt, &a.Severity, &a.Confidence,
			&current, &baseline, &deviation, &unit,
			&causes, &a.Status, &a.WorkOrderID, &a.CreatedAt, &a.UpdatedAt,
		)

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

		// Parse data
		a.LocationGPS = map[string]float64{"latitude": 0, "longitude": 0}
		if causes != nil {
			json.Unmarshal(causes.([]byte), &a.ProbableCauses)
		} else {
			a.ProbableCauses = []map[string]interface{}{}
		}
		a.Measurement = map[string]interface{}{
			"current":   current,
			"baseline":  baseline,
			"deviation": deviation,
			"unit":      unit,
		}
		a.AuditTrail = []map[string]interface{}{}

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

// GET /api/ai/anomalies/history?days=30
func getAnomalyHistoryHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		days := 30
		if d := r.URL.Query().Get("days"); d != "" {
			if parsed, err := strconv.Atoi(d); err == nil {
				days = parsed
			}
		}

		cutoff := time.Now().AddDate(0, 0, -days)

		rows, err := db.pool.Query(ctx, `
			SELECT id, asset_id, asset_name, asset_type,
				location_gps, location_zone, location_dma,
				detected_at, severity, confidence,
				measurement_current, measurement_baseline, measurement_deviation, measurement_unit,
				probable_causes, status, work_order_id, created_at, updated_at
			FROM ic3_anomalies
			WHERE detected_at >= $1
			ORDER BY detected_at DESC
			LIMIT 500
		`, cutoff)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var anomalies []Anomaly
		for rows.Next() {
			var a Anomaly
			var gpsPoint interface{}
			var causes interface{}
			var current, baseline, deviation float64
			var unit string

			err := rows.Scan(
				&a.ID, &a.AssetID, &a.AssetName, &a.AssetType,
				&gpsPoint, &a.LocationZone, &a.LocationDMA,
				&a.DetectedAt, &a.Severity, &a.Confidence,
				&current, &baseline, &deviation, &unit,
				&causes, &a.Status, &a.WorkOrderID, &a.CreatedAt, &a.UpdatedAt,
			)
			if err != nil {
				continue
			}

			a.LocationGPS = map[string]float64{"latitude": 0, "longitude": 0}
			if causes != nil {
				json.Unmarshal(causes.([]byte), &a.ProbableCauses)
			} else {
				a.ProbableCauses = []map[string]interface{}{}
			}
			a.Measurement = map[string]interface{}{
				"current":   current,
				"baseline":  baseline,
				"deviation": deviation,
				"unit":      unit,
			}
			a.AuditTrail = []map[string]interface{}{}

			anomalies = append(anomalies, a)
		}

		if anomalies == nil {
			anomalies = []Anomaly{}
		}

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

// GET /api/ai/anomalies/metrics
func getAnomalyMetricsHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		var metrics AnomalyMetrics

		// Active count
		err := db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_anomalies WHERE status = 'active'
		`).Scan(&metrics.ActiveCount)
		if err != nil {
			metrics.ActiveCount = 0
		}

		// Average confidence
		err = db.pool.QueryRow(ctx, `
			SELECT COALESCE(AVG(confidence), 0) FROM ic3_anomalies WHERE status = 'active'
		`).Scan(&metrics.AvgConfidence)
		if err != nil {
			metrics.AvgConfidence = 0
		}

		// Severity distribution
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_anomalies WHERE status = 'active' AND severity >= 9.0
		`).Scan(&metrics.CriticalCount)
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_anomalies WHERE status = 'active' AND severity >= 7.0 AND severity < 9.0
		`).Scan(&metrics.HighCount)
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_anomalies WHERE status = 'active' AND severity >= 5.0 AND severity < 7.0
		`).Scan(&metrics.MediumCount)
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_anomalies WHERE status = 'active' AND severity < 5.0
		`).Scan(&metrics.LowCount)

		// Resolved today
		today := time.Now().Truncate(24 * time.Hour)
		db.pool.QueryRow(ctx, `
			SELECT COUNT(*) FROM ic3_anomalies WHERE status = 'resolved' AND updated_at >= $1
		`, today).Scan(&metrics.ResolvedToday)

		// Mock MTTR and false positive rate (would be calculated from resolved anomalies)
		metrics.MTTRMinutes = 14.2
		metrics.FalsePosRate = 2.8

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

// POST /api/ai/anomalies/:id/dismiss
func dismissAnomalyHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		anomalyID := r.PathValue("id")
		if anomalyID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "anomaly_id required"})
			return
		}

		var payload struct {
			Reason string `json:"reason"`
		}
		if err := json.NewDecoder(r.Body).Decode(&payload); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid json"})
			return
		}

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

		// Update anomaly status
		_, err := db.pool.Exec(ctx, `
			UPDATE ic3_anomalies
			SET status = 'resolved', updated_at = NOW()
			WHERE id = $1
		`, anomalyID)

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

		// Log audit entry
		db.pool.Exec(ctx, `
			INSERT INTO ic3_anomaly_audit_trail (anomaly_id, action, details, created_at)
			VALUES ($1, $2, $3, NOW())
		`, anomalyID, "dismiss", fmt.Sprintf("Dismissed: %s", payload.Reason))

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(map[string]string{"success": "true"})
	}
}

// GET /api/ai/alert-rules
func getAlertRulesHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT id, sensor_type, threshold_upper, threshold_lower,
				percentage_deviation, severity, enabled, created_at, updated_by
			FROM ic3_alert_rules
			ORDER BY sensor_type
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var rules []AlertRule
		for rows.Next() {
			var rule AlertRule
			var upper, lower, pct *float64

			err := rows.Scan(
				&rule.ID, &rule.SensorType, &upper, &lower,
				&pct, &rule.Severity, &rule.Enabled, &rule.CreatedAt, &rule.UpdatedBy,
			)
			if err != nil {
				continue
			}

			threshold := map[string]interface{}{}
			if upper != nil {
				threshold["upper"] = *upper
			}
			if lower != nil {
				threshold["lower"] = *lower
			}
			if pct != nil {
				threshold["percentageDeviation"] = *pct
			}
			rule.Threshold = threshold

			rules = append(rules, rule)
		}

		if rules == nil {
			rules = []AlertRule{}
		}

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

// GET /api/ai/sensors/health
func getSensorHealthHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 10*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT sensor_id, sensor_name, trust_score, status,
				last_calibration, anomaly_count_7d, false_positive_rate
			FROM ic3_sensor_health
			ORDER BY trust_score DESC
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var sensors []SensorHealth
		for rows.Next() {
			var s SensorHealth
			var calib *time.Time

			err := rows.Scan(
				&s.SensorID, &s.SensorName, &s.TrustScore, &s.Status,
				&calib, &s.AnomalyCount7D, &s.FalsePositiveRate,
			)
			if err != nil {
				continue
			}

			s.LastCalibration = calib
			sensors = append(sensors, s)
		}

		if sensors == nil {
			sensors = []SensorHealth{}
		}

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

// POST /api/ai/alert-rules
func createAlertRuleHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var rule AlertRule
		if err := json.NewDecoder(r.Body).Decode(&rule); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid json"})
			return
		}

		rule.CreatedAt = time.Now()

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

		var returnedID string
		err := db.pool.QueryRow(ctx, `
			INSERT INTO ic3_alert_rules (sensor_type, severity, enabled, created_at, updated_by)
			VALUES ($1, $2, $3, $4, $5)
			RETURNING id
		`, rule.SensorType, rule.Severity, rule.Enabled, rule.CreatedAt, "system").Scan(&returnedID)

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

		rule.ID = returnedID

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

// POST /api/work-orders (create from anomaly)
func createWorkOrderHandler(db *DB) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		var payload struct {
			AnomalyID string `json:"anomalyId"`
			Priority  string `json:"priority"`
			Type      string `json:"type"`
		}
		if err := json.NewDecoder(r.Body).Decode(&payload); err != nil {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "invalid json"})
			return
		}

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

		// Update anomaly with work order reference (generate UUID in DB)
		var woID string
		err := db.pool.QueryRow(ctx, `
			UPDATE ic3_anomalies
			SET work_order_id = gen_random_uuid(), updated_at = NOW()
			WHERE id = $1
			RETURNING work_order_id::text
		`, payload.AnomalyID).Scan(&woID)

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

		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(201)
		json.NewEncoder(w).Encode(map[string]string{"id": woID, "created": "true"})
	}
}