TimescaleDB Time-Series Storage

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = ["com.apptolast.invernaderos.features.telemetry.timeseries"],
    entityManagerFactoryRef = "timescaleEntityManagerFactory",
    transactionManagerRef = "timescaleTransactionManager"
)
class TimescaleDataSourceConfig {

    @Primary
    @Bean(name = ["timescaleDataSource"])
    @ConfigurationProperties("spring.datasource.configuration")
    fun timescaleDataSource(
        @Qualifier("timescaleDataSourceProperties") properties: DataSourceProperties
    ): HikariDataSource {
        return properties
            .initializeDataSourceBuilder()
            .type(HikariDataSource::class.java)
            .build()
    }

    @Primary
    @Bean(name = ["timescaleEntityManagerFactory"])
    fun timescaleEntityManagerFactory(
        @Qualifier("timescaleDataSource") dataSource: DataSource
    ): LocalContainerEntityManagerFactoryBean {
        val entityManager = LocalContainerEntityManagerFactoryBean()
        entityManager.dataSource = dataSource
        entityManager.setPackagesToScan(
            "com.apptolast.invernaderos.features.telemetry.timescaledb.entities"
        )
        entityManager.persistenceUnitName = "timescalePersistenceUnit"

        val vendorAdapter = HibernateJpaVendorAdapter()
        vendorAdapter.setGenerateDdl(false)
        entityManager.jpaVendorAdapter = vendorAdapter

        val properties = hashMapOf<String, Any>(
            "hibernate.dialect" to "org.hibernate.dialect.PostgreSQLDialect",
            "hibernate.hbm2ddl.auto" to "validate",
            "hibernate.show_sql" to "false",
            "hibernate.format_sql" to "true"
        )
        entityManager.setJpaPropertyMap(properties)

        return entityManager
    }
}

/**
 * Entity for storing sensor readings in TimescaleDB
 *
 * Uses a composite primary key (time, sensorId) to allow
 * multiple readings from different sensors with the same timestamp
 */
@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 for multi-tenant queries

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

/**
 * Composite ID class for SensorReading
 * Required for composite primary key (time, sensorId)
 */
data class SensorReadingId(
    val time: Instant = Instant.now(),
    val sensorId: String = ""
) : Serializable

-- Create iot schema
CREATE SCHEMA IF NOT EXISTS iot;

-- Create sensor_readings table
CREATE TABLE iot.sensor_readings (
    time TIMESTAMPTZ NOT NULL,
    sensor_id VARCHAR(50) NOT NULL,
    greenhouse_id BIGINT NOT NULL,
    tenant_id BIGINT,
    sensor_type VARCHAR(30) NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    unit VARCHAR(20),
    metadata JSONB,
    PRIMARY KEY (time, sensor_id)
);

-- Convert to hypertable with 7-day chunks
SELECT create_hypertable(
    'iot.sensor_readings', 
    'time', 
    chunk_time_interval => INTERVAL '7 days'
);

-- Create indexes for common queries
CREATE INDEX idx_sensor_readings_greenhouse_id 
    ON iot.sensor_readings(greenhouse_id, time DESC);

CREATE INDEX idx_sensor_readings_tenant_time 
    ON iot.sensor_readings(tenant_id, time DESC) 
    WHERE tenant_id IS NOT NULL;

CREATE INDEX idx_sensor_readings_greenhouse_sensor_time 
    ON iot.sensor_readings(greenhouse_id, sensor_id, time DESC);

/**
 * Calculate the trend of a sensor using TimescaleDB FIRST() and LAST() functions.
 * Optimized: Calculates directly in DB, avoids loading all data in memory.
 */
@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?,
    @Param("startTime") startTime: Instant,
    @Param("endTime") endTime: Instant
): Map<String, Any>?

-- SARA_01 - Temperature (TEMP_01) - Realistic diurnal pattern
INSERT INTO iot.sensor_readings (time, sensor_id, greenhouse_id, tenant_id, sensor_type, value, unit)
VALUES
-- Last 60 minutes (12 readings at 5-minute intervals)
(NOW() - INTERVAL '5 minutes', 'SARA_TEMP01_valor', 1, 1, 'TEMPERATURE', 24.5, '°C'),
(NOW() - INTERVAL '10 minutes', 'SARA_TEMP01_valor', 1, 1, 'TEMPERATURE', 24.3, '°C'),
(NOW() - INTERVAL '15 minutes', 'SARA_TEMP01_valor', 1, 1, 'TEMPERATURE', 24.7, '°C'),
(NOW() - INTERVAL '20 minutes', 'SARA_TEMP01_valor', 1, 1, 'TEMPERATURE', 24.4, '°C'),
(NOW() - INTERVAL '25 minutes', 'SARA_TEMP01_valor', 1, 1, 'TEMPERATURE', 24.6, '°C'),
(NOW() - INTERVAL '30 minutes', 'SARA_TEMP01_valor', 1, 1, 'TEMPERATURE', 24.2, '°C');

-- SARA_01 - Humidity (HUM_01) - Inverse pattern to temperature
INSERT INTO iot.sensor_readings (time, sensor_id, greenhouse_id, tenant_id, sensor_type, value, unit)
VALUES
(NOW() - INTERVAL '5 minutes', 'SARA_HUM01_valor', 1, 1, 'HUMIDITY', 62.3, '%'),
(NOW() - INTERVAL '10 minutes', 'SARA_HUM01_valor', 1, 1, 'HUMIDITY', 62.8, '%'),
(NOW() - INTERVAL '15 minutes', 'SARA_HUM01_valor', 1, 1, 'HUMIDITY', 61.9, '%');