Multi-Tenant Architecture

// Extract tenant/empresaID from topic path
// Format: "GREENHOUSE/empresaID" → empresaID
// Legacy: "GREENHOUSE" → "DEFAULT"
val tenantId = when {
    topic.startsWith("GREENHOUSE/") -> topic.substringAfter("GREENHOUSE/").takeWhile { it != '/' }
    topic == "GREENHOUSE" -> "DEFAULT"
    else -> "UNKNOWN"
}

logger.info("Processing GREENHOUSE data - Topic: {}, TenantID: {}", topic, tenantId)

// Process the message (save to DB, cache, validate tenant, etc.)
messageProcessor.processGreenhouseData(payload, tenantId)

ALTER TABLE metadata.tenants
  ADD COLUMN IF NOT EXISTS company_name VARCHAR(200),
  ADD COLUMN IF NOT EXISTS tax_id VARCHAR(50),
  ADD COLUMN IF NOT EXISTS address TEXT,
  ADD COLUMN IF NOT EXISTS city VARCHAR(100),
  ADD COLUMN IF NOT EXISTS postal_code VARCHAR(20),
  ADD COLUMN IF NOT EXISTS province VARCHAR(100),
  ADD COLUMN IF NOT EXISTS country VARCHAR(50) DEFAULT 'España',
  ADD COLUMN IF NOT EXISTS phone VARCHAR(50),
  ADD COLUMN IF NOT EXISTS contact_person VARCHAR(150),
  ADD COLUMN IF NOT EXISTS contact_email VARCHAR(255),
  ADD COLUMN IF NOT EXISTS mqtt_topic_prefix VARCHAR(50),
  ADD COLUMN IF NOT EXISTS coordinates JSONB,
  ADD COLUMN IF NOT EXISTS notes TEXT;

-- Tax ID must be unique (CIF/NIF)
ALTER TABLE metadata.tenants 
  ADD CONSTRAINT uq_tenants_tax_id UNIQUE (tax_id);

-- MQTT topic prefix must be unique (used for routing)
ALTER TABLE metadata.tenants 
  ADD CONSTRAINT uq_tenants_mqtt_topic_prefix UNIQUE (mqtt_topic_prefix);

ALTER TABLE metadata.greenhouses
  ADD COLUMN IF NOT EXISTS greenhouse_code VARCHAR(50),
  ADD COLUMN IF NOT EXISTS mqtt_topic VARCHAR(100),
  ADD COLUMN IF NOT EXISTS mqtt_publish_interval_seconds INT DEFAULT 5,
  ADD COLUMN IF NOT EXISTS external_id VARCHAR(100);

-- Add explicit FK to tenants (CASCADE DELETE)
ALTER TABLE metadata.greenhouses
  ADD CONSTRAINT fk_greenhouse_tenant
  FOREIGN KEY (tenant_id)
  REFERENCES metadata.tenants(id)
  ON DELETE CASCADE;

-- greenhouse_code must be unique within a tenant
ALTER TABLE metadata.greenhouses 
  ADD CONSTRAINT uq_greenhouse_code_per_tenant 
  UNIQUE (tenant_id, greenhouse_code);

-- mqtt_topic must be globally unique
ALTER TABLE metadata.greenhouses 
  ADD CONSTRAINT uq_greenhouse_mqtt_topic 
  UNIQUE (mqtt_topic);

ALTER TABLE metadata.sensors
  ADD COLUMN IF NOT EXISTS sensor_code VARCHAR(50),
  ADD COLUMN IF NOT EXISTS tenant_id BIGINT,  -- Denormalized for performance
  ADD COLUMN IF NOT EXISTS mqtt_field_name VARCHAR(100),
  ADD COLUMN IF NOT EXISTS data_format VARCHAR(20) DEFAULT 'NUMERIC';

-- Populate tenant_id based on greenhouse_id (denormalization)
UPDATE metadata.sensors s
SET tenant_id = (
    SELECT g.tenant_id
    FROM metadata.greenhouses g
    WHERE g.id = s.greenhouse_id
)
WHERE s.tenant_id IS NULL;

-- Add FK to tenants (for direct queries by tenant)
ALTER TABLE metadata.sensors
  ADD CONSTRAINT fk_sensor_tenant
  FOREIGN KEY (tenant_id)
  REFERENCES metadata.tenants(id)
  ON DELETE CASCADE;

@Entity
@Table(name = "sensor_readings", schema = "iot")
@IdClass(SensorReadingId::class)
data class SensorReading(
    @Id
    @Column(nullable = false)
    val time: Instant,

    @Id
    @Column(name = "sensor_id", nullable = false, length = 50)
    val sensorId: String,

    @Column(name = "greenhouse_id", nullable = false)
    val greenhouseId: Long,  // BIGINT auto-generated ID

    @Column(name = "tenant_id")
    val tenantId: Long? = null,  // Denormalized tenant ID

    @Column(name = "sensor_type", nullable = false, length = 30)
    val sensorType: String,

    @Column(nullable = false, columnDefinition = "double precision")
    val value: Double,

    @Column(length = 20)
    val unit: String? = null
)

@Repository
interface GreenhouseRepository : JpaRepository<Greenhouse, Long> {

    /**
     * Find greenhouses by tenant ID.
     */
    fun findByTenantId(tenantId: Long): List<Greenhouse>

    /**
     * Find active greenhouses for a specific tenant.
     * CRITICAL for multi-tenant validation in MQTT processing.
     */
    fun findByTenantIdAndIsActive(tenantId: Long, isActive: Boolean): List<Greenhouse>

    /**
     * Find greenhouse by name within a tenant.
     */
    fun findByTenantIdAndName(tenantId: Long, name: String): Greenhouse?

    /**
     * Find a greenhouse by ID and tenant ID (double-check tenant isolation).
     */
    fun findByIdAndTenantId(id: Long, tenantId: Long): Greenhouse?
}

/**
 * Calculate the trend (trend) of a sensor in a time period.
 * Uses TimescaleDB FIRST() and LAST() functions.
 *
 * Optimized: Avoids loading all data in memory, calculates directly in DB.
 *
 * @param sensorId ID of the sensor
 * @param tenantId ID of the tenant (null to filter only by sensor)
 * @param startTime Start of period
 * @param endTime End of period
 * @return Map with: first_value, last_value, first_time, last_time, unit
 */
@Query(value = """
    SELECT
        FIRST(value, time) AS first_value,
        LAST(value, time) AS last_value,
        FIRST(time, time) AS first_time,
        LAST(time, time) AS last_time,
        MAX(unit) AS unit
    FROM iot.sensor_readings
    WHERE sensor_id = :sensorId
      AND (:tenantId IS NULL OR tenant_id = :tenantId)
      AND time BETWEEN :startTime AND :endTime
""", nativeQuery = true)
fun calculateTrend(
    @Param("sensorId") sensorId: String,
    @Param("tenantId") tenantId: Long?,  // NULL for cross-tenant queries
    @Param("startTime") startTime: Instant,
    @Param("endTime") endTime: Instant
): Map<String, Any>?