lambdaiot-core/internal/handler/handlers.go

package handler

import (
	"database/sql"
	"encoding/json"
	"net/http"
	"os"
	"strconv"
	"strings"
	"time"

	"git.piskot.si/SeminarM2/lambdaiot-core/internal/mqtt"
	"github.com/gin-gonic/gin"
	"github.com/golang-jwt/jwt/v5"
	"github.com/google/uuid"
	"golang.org/x/crypto/bcrypt"
)

type Handler struct {
	DB        *sql.DB
	JWTSecret string
	MQTTTopic string
}

type Device struct {
	ID          uuid.UUID `json:"id" db:"id"`
	Name        string    `json:"name" db:"name"`
	Description string    `json:"description" db:"description"`
	Location    string    `json:"location" db:"location"`
	StatusID    int       `json:"status_id" db:"status_id"`
	CreatedAt   time.Time `json:"created_at" db:"created_at"`
	UpdatedAt   time.Time `json:"updated_at" db:"updated_at"`
}

type Sensor struct {
	ID         uuid.UUID `json:"id" db:"id"`
	DeviceID   uuid.UUID `json:"device_id" db:"device_id"`
	Name       string    `json:"name" db:"name"`
	Type       string    `json:"type" db:"type"`
	DataTypeID int       `json:"data_type_id" db:"data_type_id"`
	CreatedAt  time.Time `json:"created_at" db:"created_at"`
	UpdatedAt  time.Time `json:"updated_at" db:"updated_at"`
}

type Actor struct {
	ID         uuid.UUID `json:"id" db:"id"`
	DeviceID   uuid.UUID `json:"device_id" db:"device_id"`
	Name       string    `json:"name" db:"name"`
	Type       string    `json:"type" db:"type"`
	DataTypeID int       `json:"data_type_id" db:"data_type_id"`
	CreatedAt  time.Time `json:"created_at" db:"created_at"`
	UpdatedAt  time.Time `json:"updated_at" db:"updated_at"`
}

type SensorReading struct {
	ID       int64     `json:"id" db:"id"`
	SensorID uuid.UUID `json:"sensor_id" db:"sensor_id"`
	Value    float64   `json:"value" db:"value"`
	ValueAt  time.Time `json:"value_at" db:"value_at"`
}

func (h *Handler) mqttTopic() string {
	if h != nil && h.MQTTTopic != "" {
		return h.MQTTTopic
	}
	if v := os.Getenv("MQTT_TOPIC"); v != "" {
		return v
	}
	return "lambdaiot"
}

func (h *Handler) publishMQTT(c *gin.Context, payload any) bool {
	topic := h.mqttTopic()
	body, err := json.Marshal(payload)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to marshal payload"})
		return false
	}
	if err := mqtt.PublishDefault(topic, body); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return false
	}
	return true
}

// Health returns basic service health
func (h *Handler) Health(c *gin.Context) {
	c.JSON(http.StatusOK, gin.H{"status": "ok"})
}

// Hello returns a simple greeting
func (h *Handler) Hello(c *gin.Context) {
	c.JSON(http.StatusOK, gin.H{"message": "hello from lambdaiot"})
}

// Login issues a JWT token after validating credentials against the database
func (h *Handler) Login(c *gin.Context) {
	var req struct {
		Username string `json:"username"`
		Password string `json:"password"`
	}
	if err := c.BindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid json"})
		return
	}

	// Query user from database
	var userID string
	var passwordHash string
	err := h.DB.QueryRow("SELECT BIN_TO_UUID(id), password_hash FROM users WHERE username = ?", req.Username).Scan(&userID, &passwordHash)
	if err == sql.ErrNoRows {
		c.JSON(http.StatusUnauthorized, gin.H{"error": "invalid credentials"})
		return
	}
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "database error"})
		return
	}

	// Verify password
	if err := bcrypt.CompareHashAndPassword([]byte(passwordHash), []byte(req.Password)); err != nil {
		c.JSON(http.StatusUnauthorized, gin.H{"error": "invalid credentials"})
		return
	}

	// Create token
	token := jwt.NewWithClaims(jwt.SigningMethodHS256, jwt.MapClaims{
		"sub":     req.Username,
		"user_id": userID,
		"exp":     time.Now().Add(24 * time.Hour).Unix(),
	})
	signed, err := token.SignedString([]byte(h.JWTSecret))
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "could not sign token"})
		return
	}
	c.JSON(http.StatusOK, gin.H{"token": signed})
}

func (h *Handler) CreateSensor(c *gin.Context) {
	var req struct {
		DeviceID   string `json:"device_id" binding:"required,uuid"`
		Name       string `json:"name" binding:"required"`
		Type       string `json:"type" binding:"required"`
		DataTypeID int    `json:"data_type_id" binding:"required,min=1"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	deviceUUID, err := uuid.Parse(req.DeviceID)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
		return
	}

	// Create sensor in database (schema uses data_type_id)
	sensorID := uuid.New()
	_, err = h.DB.Exec("INSERT INTO sensors (id, device_id, name, type, data_type_id) VALUES (UUID_TO_BIN(?), UUID_TO_BIN(?), ?, ?, ?)", sensorID.String(), deviceUUID.String(), req.Name, req.Type, req.DataTypeID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create sensor"})
		return
	}

	c.JSON(http.StatusCreated, gin.H{"id": sensorID.String()})
}

// GetSensors lists all sensors
func (h *Handler) GetSensors(c *gin.Context) {
	rows, err := h.DB.Query("SELECT BIN_TO_UUID(id), BIN_TO_UUID(device_id), name, type, data_type_id, created_at, updated_at FROM sensors")
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch sensors"})
		return
	}
	defer rows.Close()

	var sensors []Sensor
	for rows.Next() {
		var s Sensor
		if err := rows.Scan(&s.ID, &s.DeviceID, &s.Name, &s.Type, &s.DataTypeID, &s.CreatedAt, &s.UpdatedAt); err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to scan sensor"})
			return
		}
		sensors = append(sensors, s)
	}

	c.JSON(http.StatusOK, sensors)
}

// GetSensor returns a single sensor
func (h *Handler) GetSensor(c *gin.Context) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sensor id"})
		return
	}

	var s Sensor
	err = h.DB.QueryRow("SELECT BIN_TO_UUID(id), BIN_TO_UUID(device_id), name, type, data_type_id, created_at, updated_at FROM sensors WHERE id = UUID_TO_BIN(?)", id.String()).Scan(&s.ID, &s.DeviceID, &s.Name, &s.Type, &s.DataTypeID, &s.CreatedAt, &s.UpdatedAt)
	if err == sql.ErrNoRows {
		c.JSON(http.StatusNotFound, gin.H{"error": "sensor not found"})
		return
	} else if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch sensor"})
		return
	}

	c.JSON(http.StatusOK, s)
}

// UpdateSensor updates sensor fields
func (h *Handler) UpdateSensor(c *gin.Context) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sensor id"})
		return
	}

	var req struct {
		DeviceID   *string `json:"device_id"`
		Name       *string `json:"name"`
		Type       *string `json:"type"`
		DataTypeID *int    `json:"data_type_id"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	setParts := []string{}
	args := []interface{}{}
	if req.DeviceID != nil {
		devID, err := uuid.Parse(*req.DeviceID)
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
			return
		}
		setParts = append(setParts, "device_id = UUID_TO_BIN(?)")
		args = append(args, devID.String())
	}
	if req.Name != nil {
		setParts = append(setParts, "name = ?")
		args = append(args, *req.Name)
	}
	if req.Type != nil {
		setParts = append(setParts, "type = ?")
		args = append(args, *req.Type)
	}
	if req.DataTypeID != nil {
		setParts = append(setParts, "data_type_id = ?")
		args = append(args, *req.DataTypeID)
	}
	if len(setParts) == 0 {
		c.JSON(http.StatusBadRequest, gin.H{"error": "no fields to update"})
		return
	}

	query := "UPDATE sensors SET " + strings.Join(setParts, ", ") + " WHERE id = UUID_TO_BIN(?)"
	args = append(args, id.String())

	if _, err := h.DB.Exec(query, args...); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to update sensor"})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": "sensor updated"})
}

// DeleteSensor removes a sensor
func (h *Handler) DeleteSensor(c *gin.Context) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sensor id"})
		return
	}
	if _, err := h.DB.Exec("DELETE FROM sensors WHERE id = UUID_TO_BIN(?)", id.String()); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to delete sensor"})
		return
	}
	c.JSON(http.StatusOK, gin.H{"message": "sensor deleted"})
}

// TriggerSensor sends an MQTT message requesting a new reading from the sensor.
// The message payload contains the sensor ID and the requested action (default "read").
func (h *Handler) TriggerSensor(c *gin.Context) {
	sensorID, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sensor id"})
		return
	}

	var exists int
	if err := h.DB.QueryRow("SELECT COUNT(1) FROM sensors WHERE id = UUID_TO_BIN(?)", sensorID.String()).Scan(&exists); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to check sensor"})
		return
	}
	if exists == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": "sensor not found"})
		return
	}

	var req struct {
		Action string `json:"action"`
	}
	if c.Request.ContentLength > 0 {
		if err := c.ShouldBindJSON(&req); err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
			return
		}
	}
	action := req.Action
	if action == "" {
		action = "read"
	}

	payload := struct {
		Type        string `json:"type"`
		SensorID    string `json:"sensor_id"`
		Action      string `json:"action"`
		RequestedAt string `json:"requested_at"`
	}{
		Type:        "sensor_trigger",
		SensorID:    sensorID.String(),
		Action:      action,
		RequestedAt: time.Now().UTC().Format(time.RFC3339),
	}

	if !h.publishMQTT(c, payload) {
		return
	}

	c.JSON(http.StatusAccepted, gin.H{"status": "published", "topic": h.mqttTopic()})
}

// CreateDevice creates a new device
func (h *Handler) CreateDevice(c *gin.Context) {
	var req struct {
		Name        string `json:"name" binding:"required"`
		Description string `json:"description" binding:"required"`
		Location    string `json:"location" binding:"required"`
		StatusID    int    `json:"status_id" binding:"required,min=1,max=4"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	id := uuid.New()
	_, err := h.DB.Exec("INSERT INTO devices (id, name, description, location, status_id) VALUES (UUID_TO_BIN(?), ?, ?, ?, ?)", id.String(), req.Name, req.Description, req.Location, req.StatusID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create device"})
		return
	}

	c.JSON(http.StatusCreated, gin.H{"id": id.String()})
}

// GetDevices retrieves all devices
func (h *Handler) GetDevices(c *gin.Context) {
	rows, err := h.DB.Query("SELECT BIN_TO_UUID(id), name, description, location, status_id, created_at, updated_at FROM devices")
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch devices"})
		return
	}
	defer rows.Close()

	var devices []Device
	for rows.Next() {
		var d Device
		err := rows.Scan(&d.ID, &d.Name, &d.Description, &d.Location, &d.StatusID, &d.CreatedAt, &d.UpdatedAt)
		if err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to scan device"})
			return
		}
		devices = append(devices, d)
	}

	c.JSON(http.StatusOK, devices)
}

// GetDevice retrieves a single device by ID
func (h *Handler) GetDevice(c *gin.Context) {
	idStr := c.Param("id")
	id, err := uuid.Parse(idStr)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
		return
	}

	var d Device
	err = h.DB.QueryRow("SELECT BIN_TO_UUID(id), name, description, location, status_id, created_at, updated_at FROM devices WHERE id = UUID_TO_BIN(?)", id.String()).Scan(&d.ID, &d.Name, &d.Description, &d.Location, &d.StatusID, &d.CreatedAt, &d.UpdatedAt)
	if err == sql.ErrNoRows {
		c.JSON(http.StatusNotFound, gin.H{"error": "device not found"})
		return
	} else if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch device"})
		return
	}

	c.JSON(http.StatusOK, d)
}

// UpdateDevice updates a device
func (h *Handler) UpdateDevice(c *gin.Context) {
	idStr := c.Param("id")
	id, err := uuid.Parse(idStr)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
		return
	}

	var req struct {
		Name        *string `json:"name"`
		Description *string `json:"description"`
		Location    *string `json:"location"`
		StatusID    *int    `json:"status_id"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// Build update query dynamically
	setParts := []string{}
	args := []interface{}{}
	if req.Name != nil {
		setParts = append(setParts, "name = ?")
		args = append(args, *req.Name)
	}
	if req.Description != nil {
		setParts = append(setParts, "description = ?")
		args = append(args, *req.Description)
	}
	if req.Location != nil {
		setParts = append(setParts, "location = ?")
		args = append(args, *req.Location)
	}
	if req.StatusID != nil {
		setParts = append(setParts, "status_id = ?")
		args = append(args, *req.StatusID)
	}
	if len(setParts) == 0 {
		c.JSON(http.StatusBadRequest, gin.H{"error": "no fields to update"})
		return
	}

	query := "UPDATE devices SET " + strings.Join(setParts, ", ") + " WHERE id = UUID_TO_BIN(?)"
	args = append(args, id.String())

	_, err = h.DB.Exec(query, args...)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to update device"})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": "device updated"})
}

// DeleteDevice deletes a device
func (h *Handler) DeleteDevice(c *gin.Context) {
	idStr := c.Param("id")
	id, err := uuid.Parse(idStr)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
		return
	}

	_, err = h.DB.Exec("DELETE FROM devices WHERE id = UUID_TO_BIN(?)", id.String())
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to delete device"})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": "device deleted"})
}

// Protected requires a valid JWT and returns the token claims
func (h *Handler) Protected(c *gin.Context) {
	if v, ok := c.Get("claims"); ok {
		c.JSON(http.StatusOK, gin.H{"claims": v})
		return
	}
	c.JSON(http.StatusUnauthorized, gin.H{"error": "no claims"})
}

// Package-level handlers used by tests
func Health(c *gin.Context) {
	c.JSON(http.StatusOK, gin.H{"status": "ok"})
}

func Hello(c *gin.Context) {
	c.JSON(http.StatusOK, gin.H{"message": "hello from lambdaiot"})
}

func Protected(c *gin.Context) {
	if v, ok := c.Get("claims"); ok {
		c.JSON(http.StatusOK, gin.H{"claims": v})
		return
	}
	c.JSON(http.StatusUnauthorized, gin.H{"error": "no claims"})
}

// Actor CRUD
func (h *Handler) CreateActor(c *gin.Context) {
	var req struct {
		DeviceID   string `json:"device_id" binding:"required,uuid"`
		Name       string `json:"name" binding:"required"`
		Type       string `json:"type" binding:"required"`
		DataTypeID int    `json:"data_type_id" binding:"required,min=1"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}
	devID, err := uuid.Parse(req.DeviceID)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
		return
	}
	actorID := uuid.New()
	if _, err := h.DB.Exec("INSERT INTO actors (id, device_id, name, type, data_type_id) VALUES (UUID_TO_BIN(?), UUID_TO_BIN(?), ?, ?, ?)", actorID.String(), devID.String(), req.Name, req.Type, req.DataTypeID); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create actor"})
		return
	}
	c.JSON(http.StatusCreated, gin.H{"id": actorID.String()})
}

func (h *Handler) GetActors(c *gin.Context) {
	rows, err := h.DB.Query("SELECT BIN_TO_UUID(id), BIN_TO_UUID(device_id), name, type, data_type_id, created_at, updated_at FROM actors")
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch actors"})
		return
	}
	defer rows.Close()
	var actors []Actor
	for rows.Next() {
		var a Actor
		if err := rows.Scan(&a.ID, &a.DeviceID, &a.Name, &a.Type, &a.DataTypeID, &a.CreatedAt, &a.UpdatedAt); err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to scan actor"})
			return
		}
		actors = append(actors, a)
	}
	c.JSON(http.StatusOK, actors)
}

func (h *Handler) GetActor(c *gin.Context) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid actor id"})
		return
	}
	var a Actor
	err = h.DB.QueryRow("SELECT BIN_TO_UUID(id), BIN_TO_UUID(device_id), name, type, data_type_id, created_at, updated_at FROM actors WHERE id = UUID_TO_BIN(?)", id.String()).Scan(&a.ID, &a.DeviceID, &a.Name, &a.Type, &a.DataTypeID, &a.CreatedAt, &a.UpdatedAt)
	if err == sql.ErrNoRows {
		c.JSON(http.StatusNotFound, gin.H{"error": "actor not found"})
		return
	} else if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch actor"})
		return
	}
	c.JSON(http.StatusOK, a)
}

func (h *Handler) UpdateActor(c *gin.Context) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid actor id"})
		return
	}
	var req struct {
		DeviceID   *string `json:"device_id"`
		Name       *string `json:"name"`
		Type       *string `json:"type"`
		DataTypeID *int    `json:"data_type_id"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}
	setParts := []string{}
	args := []interface{}{}
	if req.DeviceID != nil {
		devID, err := uuid.Parse(*req.DeviceID)
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": "invalid device id"})
			return
		}
		setParts = append(setParts, "device_id = UUID_TO_BIN(?)")
		args = append(args, devID.String())
	}
	if req.Name != nil {
		setParts = append(setParts, "name = ?")
		args = append(args, *req.Name)
	}
	if req.Type != nil {
		setParts = append(setParts, "type = ?")
		args = append(args, *req.Type)
	}
	if req.DataTypeID != nil {
		setParts = append(setParts, "data_type_id = ?")
		args = append(args, *req.DataTypeID)
	}
	if len(setParts) == 0 {
		c.JSON(http.StatusBadRequest, gin.H{"error": "no fields to update"})
		return
	}
	query := "UPDATE actors SET " + strings.Join(setParts, ", ") + " WHERE id = UUID_TO_BIN(?)"
	args = append(args, id.String())
	if _, err := h.DB.Exec(query, args...); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to update actor"})
		return
	}
	c.JSON(http.StatusOK, gin.H{"message": "actor updated"})
}

func (h *Handler) DeleteActor(c *gin.Context) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid actor id"})
		return
	}
	if _, err := h.DB.Exec("DELETE FROM actors WHERE id = UUID_TO_BIN(?)", id.String()); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to delete actor"})
		return
	}
	c.JSON(http.StatusOK, gin.H{"message": "actor deleted"})
}

// WriteActor publishes a desired action/value for the actor to the MQTT topic.
func (h *Handler) WriteActor(c *gin.Context) {
	actorID, err := uuid.Parse(c.Param("id"))
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid actor id"})
		return
	}

	var exists int
	if err := h.DB.QueryRow("SELECT COUNT(1) FROM actors WHERE id = UUID_TO_BIN(?)", actorID.String()).Scan(&exists); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to check actor"})
		return
	}
	if exists == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": "actor not found"})
		return
	}

	var req struct {
		Action string          `json:"action" binding:"required"`
		Value  json.RawMessage `json:"value"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	payload := struct {
		Type        string          `json:"type"`
		ActorID     string          `json:"actor_id"`
		Action      string          `json:"action"`
		Value       json.RawMessage `json:"value,omitempty"`
		RequestedAt string          `json:"requested_at"`
	}{
		Type:        "actor_command",
		ActorID:     actorID.String(),
		Action:      req.Action,
		Value:       req.Value,
		RequestedAt: time.Now().UTC().Format(time.RFC3339),
	}

	if !h.publishMQTT(c, payload) {
		return
	}

	c.JSON(http.StatusAccepted, gin.H{"status": "published", "topic": h.mqttTopic()})
}

// Sensor readings CRUD
func (h *Handler) CreateSensorReading(c *gin.Context) {
	var req struct {
		SensorID string  `json:"sensor_id" binding:"required,uuid"`
		Value    float64 `json:"value" binding:"required"`
		ValueAt  *string `json:"value_at"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}
	sensorID, err := uuid.Parse(req.SensorID)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sensor id"})
		return
	}
	valueAt := time.Now()
	if req.ValueAt != nil && *req.ValueAt != "" {
		if parsed, err := time.Parse(time.RFC3339, *req.ValueAt); err == nil {
			valueAt = parsed
		} else {
			c.JSON(http.StatusBadRequest, gin.H{"error": "invalid value_at; use RFC3339"})
			return
		}
	}
	res, err := h.DB.Exec("INSERT INTO sensor_readings (sensor_id, value, value_at) VALUES (UUID_TO_BIN(?), ?, ?)", sensorID.String(), req.Value, valueAt)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create sensor reading"})
		return
	}
	id, _ := res.LastInsertId()
	c.JSON(http.StatusCreated, gin.H{"id": id})
}

func (h *Handler) GetSensorReadings(c *gin.Context) {
	limit := 100
	if l := c.Query("limit"); l != "" {
		if n, err := strconv.Atoi(l); err == nil && n > 0 && n <= 1000 {
			limit = n
		}
	}
	page := 0
	if p := c.Query("page"); p != "" {
		if n, err := strconv.Atoi(p); err == nil && n >= 0 {
			page = n
		}
	}
	offset := page * limit

	var (
		rows *sql.Rows
		err  error
	)

	if sensorIDStr := c.Query("sensor_id"); sensorIDStr != "" {
		sensorID, err := uuid.Parse(sensorIDStr)
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": "invalid sensor id"})
			return
		}
		rows, err = h.DB.Query("SELECT id, BIN_TO_UUID(sensor_id), value, value_at FROM sensor_readings WHERE sensor_id = UUID_TO_BIN(?) ORDER BY id DESC LIMIT ? OFFSET ?", sensorID.String(), limit, offset)
	} else {
		rows, err = h.DB.Query("SELECT id, BIN_TO_UUID(sensor_id), value, value_at FROM sensor_readings ORDER BY id DESC LIMIT ? OFFSET ?", limit, offset)
	}
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch sensor readings"})
		return
	}
	defer rows.Close()

	var readings []SensorReading
	for rows.Next() {
		var r SensorReading
		if err := rows.Scan(&r.ID, &r.SensorID, &r.Value, &r.ValueAt); err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to scan sensor reading"})
			return
		}
		readings = append(readings, r)
	}

	c.JSON(http.StatusOK, readings)
}

func (h *Handler) GetSensorReading(c *gin.Context) {
	idStr := c.Param("id")
	id, err := strconv.ParseInt(idStr, 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid reading id"})
		return
	}
	var r SensorReading
	err = h.DB.QueryRow("SELECT id, BIN_TO_UUID(sensor_id), value, value_at FROM sensor_readings WHERE id = ?", id).Scan(&r.ID, &r.SensorID, &r.Value, &r.ValueAt)
	if err == sql.ErrNoRows {
		c.JSON(http.StatusNotFound, gin.H{"error": "sensor reading not found"})
		return
	} else if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch sensor reading"})
		return
	}
	c.JSON(http.StatusOK, r)
}

func (h *Handler) UpdateSensorReading(c *gin.Context) {
	idStr := c.Param("id")
	id, err := strconv.ParseInt(idStr, 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid reading id"})
		return
	}
	var req struct {
		Value   *float64 `json:"value"`
		ValueAt *string  `json:"value_at"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}
	setParts := []string{}
	args := []interface{}{}
	if req.Value != nil {
		setParts = append(setParts, "value = ?")
		args = append(args, *req.Value)
	}
	if req.ValueAt != nil {
		parsed, err := time.Parse(time.RFC3339, *req.ValueAt)
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": "invalid value_at; use RFC3339"})
			return
		}
		setParts = append(setParts, "value_at = ?")
		args = append(args, parsed)
	}
	if len(setParts) == 0 {
		c.JSON(http.StatusBadRequest, gin.H{"error": "no fields to update"})
		return
	}
	query := "UPDATE sensor_readings SET " + strings.Join(setParts, ", ") + " WHERE id = ?"
	args = append(args, id)
	if _, err := h.DB.Exec(query, args...); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to update sensor reading"})
		return
	}
	c.JSON(http.StatusOK, gin.H{"message": "sensor reading updated"})
}

func (h *Handler) DeleteSensorReading(c *gin.Context) {
	idStr := c.Param("id")
	id, err := strconv.ParseInt(idStr, 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid reading id"})
		return
	}
	if _, err := h.DB.Exec("DELETE FROM sensor_readings WHERE id = ?", id); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to delete sensor reading"})
		return
	}
	c.JSON(http.StatusOK, gin.H{"message": "sensor reading deleted"})
}

// Register creates a new user account
func (h *Handler) Register(c *gin.Context) {
	var req struct {
		Username string `json:"username" binding:"required,min=3,max=50"`
		Password string `json:"password" binding:"required,min=6"`
		Email    string `json:"email"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// Check if username already exists
	var exists int
	err := h.DB.QueryRow("SELECT COUNT(*) FROM users WHERE username = ?", req.Username).Scan(&exists)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "database error"})
		return
	}
	if exists > 0 {
		c.JSON(http.StatusConflict, gin.H{"error": "username already exists"})
		return
	}

	// Hash password
	hashedPassword, err := bcrypt.GenerateFromPassword([]byte(req.Password), bcrypt.DefaultCost)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to hash password"})
		return
	}

	// Insert user
	userID := uuid.New()
	_, err = h.DB.Exec("INSERT INTO users (id, username, password_hash, email) VALUES (UUID_TO_BIN(?), ?, ?, ?)",
		userID.String(), req.Username, string(hashedPassword), req.Email)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create user"})
		return
	}

	c.JSON(http.StatusCreated, gin.H{"id": userID.String(), "username": req.Username})
}