init.sql

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE hubs (
    hub_id          TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    current_lat     DOUBLE PRECISION,
    current_lon     DOUBLE PRECISION,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE deployments (
    deployment_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hub_id          TEXT REFERENCES hubs(hub_id),
    
    -- Origin is fixed at deploy time
    origin_lat      DOUBLE PRECISION NOT NULL,
    origin_lon      DOUBLE PRECISION NOT NULL,
    
    launch_time     TIMESTAMPTZ DEFAULT NOW(),
    recall_time     TIMESTAMPTZ,
    status          TEXT DEFAULT 'active' CHECK (status IN ('active', 'recalled', 'lost')),
    
    notes           TEXT
);

CREATE INDEX idx_deployments_hub ON deployments (hub_id, launch_time DESC);
CREATE INDEX idx_deployments_active ON deployments (hub_id) WHERE status = 'active';

CREATE TABLE detections (
    time            TIMESTAMPTZ NOT NULL,
    deployment_id   UUID REFERENCES deployments(deployment_id),
    sensor_index    INTEGER,
    mac_address     MACADDR,
    ssid            TEXT,
    rssi            SMALLINT
);

SELECT create_hypertable('detections', 'time');

CREATE INDEX idx_detections_deployment ON detections (deployment_id, time DESC);
CREATE INDEX idx_detections_ssid ON detections (ssid, time DESC);

CREATE TABLE calculated_positions (
    time            TIMESTAMPTZ NOT NULL,
    deployment_id   UUID REFERENCES deployments(deployment_id),
    ssid            TEXT,
    local_x         REAL,
    local_y         REAL,
    confidence      REAL
);

SELECT create_hypertable('calculated_positions', 'time');

CREATE INDEX idx_positions_deployment ON calculated_positions (deployment_id, time DESC);
CREATE INDEX idx_positions_ssid ON calculated_positions (ssid, time DESC);

SELECT add_retention_policy('detections', INTERVAL '7 days');
SELECT add_retention_policy('calculated_positions', INTERVAL '30 days');

CREATE MATERIALIZED VIEW hourly_detection_summary
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', time) AS bucket,
    deployment_id,
    COUNT(*) AS detection_count,
    COUNT(DISTINCT ssid) AS unique_ssids,
    AVG(rssi) AS avg_rssi
FROM detections
GROUP BY bucket, deployment_id
WITH NO DATA;

SELECT add_continuous_aggregate_policy('hourly_detection_summary',
    start_offset => INTERVAL '2 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');