-- Sense owns device desired state and reconciliation progress. It deliberately
-- has no writable Site or quota truth table.

CREATE SCHEMA IF NOT EXISTS sense AUTHORIZATION sense_app;
ALTER SCHEMA sense OWNER TO sense_app;

CREATE TABLE IF NOT EXISTS sense.schema_migrations (
    version bigint PRIMARY KEY,
    applied_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
ALTER TABLE sense.schema_migrations OWNER TO sense_app;

CREATE TABLE IF NOT EXISTS sense.devices (
    id text PRIMARY KEY,
    tenant_id text NOT NULL,
    site_id text NOT NULL,
    serial_number text NOT NULL,
    name text NOT NULL,
    modality text NOT NULL,
    desired_state text NOT NULL,
    actual_state text NOT NULL,
    endpoint_ref text NOT NULL DEFAULT '',
    credential_ref text NOT NULL DEFAULT '',
    path_name text NOT NULL DEFAULT '',
    generation bigint NOT NULL DEFAULT 1,
    quota_source_version bigint,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL,
    CONSTRAINT sense_devices_identity_not_blank CHECK (
        btrim(id) <> '' AND btrim(tenant_id) <> '' AND btrim(site_id) <> ''
        AND btrim(serial_number) <> '' AND btrim(name) <> ''
    ),
    CONSTRAINT sense_devices_modality CHECK (
        modality IN ('video', 'radar', 'contact', 'button', 'wearable', 'other')
    ),
    CONSTRAINT sense_devices_desired_state CHECK (desired_state IN ('disabled', 'enabled')),
    CONSTRAINT sense_devices_actual_state CHECK (actual_state IN ('pending', 'online', 'offline', 'failed')),
    CONSTRAINT sense_devices_generation_positive CHECK (generation >= 1),
    CONSTRAINT sense_devices_quota_version_positive CHECK (
        quota_source_version IS NULL OR quota_source_version >= 1
    ),
    UNIQUE (tenant_id, site_id, serial_number)
);
ALTER TABLE sense.devices OWNER TO sense_app;

CREATE TABLE IF NOT EXISTS sense.device_capabilities (
    device_id text NOT NULL REFERENCES sense.devices(id) ON DELETE CASCADE,
    capability text NOT NULL,
    PRIMARY KEY (device_id, capability),
    CONSTRAINT sense_device_capability_known CHECK (
        capability IN ('video_capture', 'audio_capture', 'spatial_rule', 'telemetry')
    )
);
ALTER TABLE sense.device_capabilities OWNER TO sense_app;

CREATE TABLE IF NOT EXISTS sense.reconcile_state (
    device_id text PRIMARY KEY REFERENCES sense.devices(id) ON DELETE CASCADE,
    failure_count integer NOT NULL DEFAULT 0,
    next_attempt_at timestamptz,
    last_error_code text,
    observed_generation bigint NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL,
    CONSTRAINT sense_reconcile_failure_nonnegative CHECK (failure_count >= 0),
    CONSTRAINT sense_reconcile_generation_nonnegative CHECK (observed_generation >= 0)
);
ALTER TABLE sense.reconcile_state OWNER TO sense_app;

CREATE TABLE IF NOT EXISTS sense.site_quota_projection_state (
    tenant_id text NOT NULL,
    site_id text NOT NULL,
    source_version bigint NOT NULL,
    synced_at timestamptz NOT NULL,
    PRIMARY KEY (tenant_id, site_id),
    CONSTRAINT sense_quota_projection_version_positive CHECK (source_version >= 1)
);
ALTER TABLE sense.site_quota_projection_state OWNER TO sense_app;

CREATE INDEX IF NOT EXISTS sense_devices_site_state_idx
    ON sense.devices(tenant_id, site_id, desired_state);
CREATE UNIQUE INDEX IF NOT EXISTS sense_devices_path_name_idx
    ON sense.devices(path_name) WHERE path_name <> '';
CREATE INDEX IF NOT EXISTS sense_reconcile_due_idx
    ON sense.reconcile_state(next_attempt_at);

INSERT INTO sense.schema_migrations(version) VALUES (1)
ON CONFLICT (version) DO NOTHING;