akmon/locations.sql

-- Location tracking schema for 2.4G gateway based wearable positioning
-- Run this script in Supabase/PostgreSQL environment after ensuring uuid-ossp extension is available.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- =============================================================
-- 1. 2.4G Gateways registry
-- =============================================================
CREATE TABLE IF NOT EXISTS location_gateways (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID,                       -- optional, reference to owning org / school (wire manually)
    campus_code TEXT,                           -- logical campus/region grouping code
    area_name TEXT,                             -- eg. East Campus, Zone B
    building_name TEXT,
    floor_label TEXT,
    room_label TEXT,
    name TEXT NOT NULL,                         -- human friendly name
    system_code TEXT NOT NULL UNIQUE,           -- system/asset identifier
    serial_number TEXT,
    description TEXT,

    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    altitude_m NUMERIC(8, 2),
    coordinate_system TEXT DEFAULT 'WGS84',     -- WGS84 / GCJ02 / BD09 etc

    install_height_m NUMERIC(6, 2),
    orientation_deg NUMERIC(5, 2),
    coverage_radius_m NUMERIC(6, 2),
    coverage_notes TEXT,

    ip_address INET,
    lan_mac MACADDR,
    upstream_mac MACADDR,
    firmware_version TEXT,
    hardware_version TEXT,
    heartbeat_interval_s INTEGER,

    status TEXT DEFAULT 'active' CHECK (status IN ('active', 'maintenance', 'inactive', 'retired')),
    last_online_at TIMESTAMPTZ,
    last_maintenance_at TIMESTAMPTZ,

    tags TEXT[],
    extra JSONB,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

COMMENT ON TABLE location_gateways IS 'Registry of 2.4G gateways installed across campuses.';
COMMENT ON COLUMN location_gateways.extra IS 'Extensible JSON payload for vendor-specific metadata.';

CREATE INDEX IF NOT EXISTS idx_location_gateways_org ON location_gateways(organization_id);
CREATE INDEX IF NOT EXISTS idx_location_gateways_geo ON location_gateways(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_location_gateways_status ON location_gateways(status);

-- Auto update trigger for updated_at
CREATE OR REPLACE FUNCTION trg_location_gateways_touch() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_location_gateways_touch ON location_gateways;
CREATE TRIGGER trg_location_gateways_touch
    BEFORE UPDATE ON location_gateways
    FOR EACH ROW EXECUTE FUNCTION trg_location_gateways_touch();

-- =============================================================
-- 2. Wearable location snapshots (per time slice, per device)
-- =============================================================
CREATE TABLE IF NOT EXISTS wearable_location_snapshots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    device_id UUID NOT NULL REFERENCES public.ak_devices(id) ON DELETE CASCADE,
    user_id UUID REFERENCES public.ak_users(id) ON DELETE SET NULL,

    estimated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    window_started_at TIMESTAMPTZ,
    window_ended_at TIMESTAMPTZ,

    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    altitude_m NUMERIC(8, 2),
    coordinate_system TEXT DEFAULT 'WGS84',

    uncertainty_radius_m NUMERIC(6, 2),        -- estimated accuracy / radius (meters)
    confidence NUMERIC(4, 2),                  -- 0.00 - 1.00
    method TEXT,                               -- e.g. WCL, Trilateration, Kalman
    algorithm_version TEXT,
    observation_count INTEGER DEFAULT 0,

    campus_code TEXT,
    area_name TEXT,
    building_name TEXT,
    floor_label TEXT,
    room_label TEXT,

    observations JSONB,                        -- optional denormalized structure of gateway RSSI readings
    diagnostics JSONB,                         -- troubleshooting info (residuals, covariance etc)

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE wearable_location_snapshots IS 'Estimated wearable locations derived from multi-gateway RSSI aggregation.';
COMMENT ON COLUMN wearable_location_snapshots.observations IS 'Optional JSON containing gateway RSSI array for quick lookup.';

CREATE INDEX IF NOT EXISTS idx_wearable_snapshots_device_time ON wearable_location_snapshots(device_id, estimated_at DESC);
CREATE INDEX IF NOT EXISTS idx_wearable_snapshots_user_time ON wearable_location_snapshots(user_id, estimated_at DESC);
CREATE INDEX IF NOT EXISTS idx_wearable_snapshots_geo ON wearable_location_snapshots(latitude, longitude);

CREATE OR REPLACE FUNCTION trg_wearable_snapshots_touch() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_wearable_snapshots_touch ON wearable_location_snapshots;
CREATE TRIGGER trg_wearable_snapshots_touch
    BEFORE UPDATE ON wearable_location_snapshots
    FOR EACH ROW EXECUTE FUNCTION trg_wearable_snapshots_touch();

-- =============================================================
-- 3. Raw gateway RSSI observations per snapshot
-- =============================================================
CREATE TABLE IF NOT EXISTS wearable_location_observations (
    id BIGSERIAL PRIMARY KEY,
    snapshot_id UUID NOT NULL REFERENCES wearable_location_snapshots(id) ON DELETE CASCADE,
    gateway_id UUID NOT NULL REFERENCES location_gateways(id) ON DELETE CASCADE,

    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    gateway_time TIMESTAMPTZ,

    rssi_dbm NUMERIC(5, 2) NOT NULL,
    snr_db NUMERIC(5, 2),
    distance_m NUMERIC(7, 2),                 -- computed from RSSI if available
    channel INTEGER,
    frequency_mhz NUMERIC(6, 2),
    packet_sequence INTEGER,
    latency_ms INTEGER,

    firmware_version TEXT,
    payload JSONB,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE wearable_location_observations IS 'Raw 2.4G gateway readings contributing to a wearable location snapshot.';

CREATE UNIQUE INDEX IF NOT EXISTS idx_observations_unique_sample
    ON wearable_location_observations(snapshot_id, gateway_id);

CREATE INDEX IF NOT EXISTS idx_observations_gateway_time
    ON wearable_location_observations(gateway_id, received_at DESC);

CREATE INDEX IF NOT EXISTS idx_observations_snapshot ON wearable_location_observations(snapshot_id);

-- Optional view for latest snapshot per device
CREATE OR REPLACE VIEW wearable_latest_location AS
SELECT DISTINCT ON (device_id)
    device_id,
    id          AS snapshot_id,
    estimated_at,
    latitude,
    longitude,
    altitude_m,
    uncertainty_radius_m,
    confidence,
    method,
    algorithm_version,
    created_at
FROM wearable_location_snapshots
ORDER BY device_id, estimated_at DESC;

COMMENT ON VIEW wearable_latest_location IS 'Convenience view returning the latest computed position per wearable device.';