-- Create extension
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- Create hypertable from existing table
SELECT create_hypertable('sensor_data', 'timestamp', 
    chunk_time_interval => INTERVAL '1 hour',
    partitioning_column => 'sensor_id',
    number_partitions => 4);

-- Create retention policy
SELECT add_retention_policy('sensor_data', INTERVAL '30 days');

-- Create compression policy
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Time bucket aggregation
SELECT 
    time_bucket('1 hour', timestamp) AS hour,
    sensor_id,
    AVG(temperature) as avg_temp,
    MAX(temperature) as max_temp,
    MIN(temperature) as min_temp
FROM sensor_data 
WHERE timestamp >= NOW() - INTERVAL '1 day'
GROUP BY hour, sensor_id
ORDER BY hour DESC;

-- Gap filling with interpolation
SELECT 
    time_bucket_gapfill('15 minutes', timestamp) AS bucket,
    sensor_id,
    AVG(temperature) as avg_temp,
    interpolate(AVG(temperature)) as interpolated_temp
FROM sensor_data 
WHERE timestamp >= NOW() - INTERVAL '4 hours'
  AND sensor_id = 'temp_001'
GROUP BY bucket, sensor_id
ORDER BY bucket;

-- Continuous aggregate (materialized view)
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', timestamp) AS hour,
    sensor_id,
    AVG(temperature) as avg_temp,
    COUNT(*) as sample_count
FROM sensor_data
GROUP BY hour, sensor_id;

-- Refresh continuous aggregate
CALL refresh_continuous_aggregate('sensor_data_hourly', 
    start_time => NOW() - INTERVAL '1 day',
    end_time => NOW());

-- Create sensors table
CREATE TABLE sensor_readings (
    timestamp TIMESTAMPTZ NOT NULL,
    sensor_id TEXT NOT NULL,
    location TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    pressure DOUBLE PRECISION,
    battery_level INTEGER
);

-- Convert to hypertable with 1-hour chunks, partitioned by sensor_id
SELECT create_hypertable('sensor_readings', 'timestamp',
    chunk_time_interval => INTERVAL '1 hour',
    partitioning_column => 'sensor_id',
    number_partitions => 8);

-- Create index for efficient queries
CREATE INDEX ON sensor_readings (sensor_id, timestamp DESC);

-- Set up data retention (keep data for 90 days)
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');

-- Set up compression (compress data older than 1 day)
SELECT add_compression_policy('sensor_readings', INTERVAL '1 day');

-- Insert sample data
INSERT INTO sensor_readings VALUES 
('2024-01-01 10:00:00', 'temp_001', 'building_a', 23.5, 45.2, 1013.25, 87),
('2024-01-01 10:01:00', 'temp_001', 'building_a', 23.6, 45.1, 1013.30, 87),
('2024-01-01 10:02:00', 'temp_001', 'building_a', 23.4, 45.3, 1013.20, 86);

-- Query with time bucketing
SELECT 
    time_bucket('15 minutes', timestamp) AS fifteen_min_bucket,
    sensor_id,
    location,
    AVG(temperature) as avg_temp,
    MAX(temperature) - MIN(temperature) as temp_range,
    AVG(humidity) as avg_humidity
FROM sensor_readings
WHERE timestamp >= NOW() - INTERVAL '4 hours'
GROUP BY fifteen_min_bucket, sensor_id, location
ORDER BY fifteen_min_bucket DESC, sensor_id;

-- Create stock prices table
CREATE TABLE stock_prices (
    timestamp TIMESTAMPTZ NOT NULL,
    symbol TEXT NOT NULL,
    price DOUBLE PRECISION NOT NULL,
    volume BIGINT,
    market_cap DOUBLE PRECISION
);

-- Create hypertable for stock data
SELECT create_hypertable('stock_prices', 'timestamp',
    chunk_time_interval => INTERVAL '1 day',
    partitioning_column => 'symbol',
    number_partitions => 16);

-- Create continuous aggregate for OHLCV data
CREATE MATERIALIZED VIEW stock_ohlcv_1min
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 minute', timestamp) AS bucket,
    symbol,
    first(price, timestamp) AS open,
    max(price) AS high,
    min(price) AS low,
    last(price, timestamp) AS close,
    sum(volume) AS volume
FROM stock_prices
GROUP BY bucket, symbol;

-- Add automatic refresh policy
SELECT add_continuous_aggregate_policy('stock_ohlcv_1min',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');

-- Query OHLCV data with gap filling
SELECT 
    time_bucket_gapfill('1 minute', bucket, NOW() - INTERVAL '2 hours', NOW()) AS time,
    symbol,
    locf(open) AS open,
    locf(high) AS high,
    locf(low) AS low,
    locf(close) AS close,
    coalesce(volume, 0) AS volume
FROM stock_ohlcv_1min
WHERE bucket >= NOW() - INTERVAL '2 hours'
  AND symbol = 'AAPL'
GROUP BY time, symbol
ORDER BY time DESC;

-- Create metrics table for APM data
CREATE TABLE app_metrics (
    timestamp TIMESTAMPTZ NOT NULL,
    service_name TEXT NOT NULL,
    instance_id TEXT NOT NULL,
    metric_name TEXT NOT NULL,
    metric_value DOUBLE PRECISION NOT NULL,
    tags JSONB
);

-- Create hypertable
SELECT create_hypertable('app_metrics', 'timestamp',
    chunk_time_interval => INTERVAL '6 hours',
    partitioning_column => 'service_name',
    number_partitions => 4);

-- Create index for efficient tag queries
CREATE INDEX ON app_metrics USING GIN (tags);

-- Continuous aggregate for service health dashboard
CREATE MATERIALIZED VIEW service_health_5min
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('5 minutes', timestamp) AS bucket,
    service_name,
    metric_name,
    AVG(metric_value) as avg_value,
    percentile_agg(0.95) as p95_value,
    percentile_agg(0.99) as p99_value,
    COUNT(*) as sample_count
FROM app_metrics
WHERE metric_name IN ('response_time', 'cpu_usage', 'memory_usage', 'error_rate')
GROUP BY bucket, service_name, metric_name;

-- Query service performance trends
SELECT 
    bucket,
    service_name,
    metric_name,
    avg_value,
    percentile(p95_value, 0.95) OVER (
        PARTITION BY service_name, metric_name 
        ORDER BY bucket 
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) as rolling_p95
FROM service_health_5min
WHERE bucket >= NOW() - INTERVAL '1 hour'
  AND service_name = 'user-service'
ORDER BY bucket DESC, metric_name;