CREATE TABLE paliot.LAB002_ingestion_data (
    timestamp TIMESTAMPTZ NOT NULL,
    machine_sensors_id INT REFERENCES paliot.machine_sensors(machine_sensors_id),
    acc_x FLOAT, acc_y FLOAT, acc_z FLOAT, -- Questi dati andrebbero messi nel campo "external_sensor_data", così da gestire tutto come json 
    gyr_x FLOAT, gyr_y FLOAT, gyr_z FLOAT, -- idem come sopra
    external_sensor_data JSONB,
    PRIMARY KEY (timestamp, machine_sensors_id)
);

SELECT create_hypertable('paliot.LAB002_ingestion_data', 'timestamp');
SELECT set_chunk_time_interval('paliot.LAB002_ingestion_data', INTERVAL '24 hours');

-- Indexes
CREATE INDEX ON paliot.LAB002_ingestion_data (machine_sensors_id);
CREATE INDEX ON paliot.LAB002_ingestion_data (timestamp DESC);

-- Add compression policy (compress data older than 7 days)
ALTER TABLE paliot.LAB002_ingestion_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'machine_sensors_id',
    timescaledb.compress_orderby = 'timestamp'
);

SELECT add_compression_policy('paliot.LAB002_ingestion_data', INTERVAL '7 days');

-- Retention policy
SELECT add_retention_policy('paliot.LAB002_ingestion_data', INTERVAL '1 month');

COMMENT ON TABLE paliot.LAB002_ingestion_data IS 'Ingested raw sensor data from LAB002';