-- Bell v5 Brain event ingress identity bindings and durable receipts.

CREATE TABLE IF NOT EXISTS bell.event_ingress_bindings (
    producer_id text NOT NULL,
    tenant_id bigint NOT NULL,
    site_id bigint NOT NULL,
    device_id bigint NOT NULL,
    logical_tenant_id text NOT NULL,
    logical_site_id text NOT NULL,
    logical_device_id text NOT NULL,
    logical_area_id text NOT NULL,
    modality text NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (producer_id, tenant_id, site_id, device_id),
    CONSTRAINT bell_ingress_binding_site_fk
        FOREIGN KEY (logical_tenant_id, logical_site_id)
        REFERENCES bell.sites(tenant_id, id),
    CONSTRAINT bell_ingress_binding_area_fk
        FOREIGN KEY (logical_tenant_id, logical_area_id)
        REFERENCES bell.areas(tenant_id, id),
    CONSTRAINT bell_ingress_binding_device_fk
        FOREIGN KEY (logical_tenant_id, logical_site_id, logical_device_id)
        REFERENCES sense.devices(tenant_id, site_id, id),
    CONSTRAINT bell_ingress_binding_numeric_ids CHECK (
        tenant_id >= 1 AND site_id >= 1 AND device_id >= 1
    ),
    CONSTRAINT bell_ingress_binding_producer CHECK (
        char_length(producer_id) BETWEEN 1 AND 64
        AND producer_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
    ),
    CONSTRAINT bell_ingress_binding_logical_ids CHECK (
        btrim(logical_tenant_id) <> '' AND btrim(logical_site_id) <> ''
        AND btrim(logical_device_id) <> '' AND btrim(logical_area_id) <> ''
    ),
    CONSTRAINT bell_ingress_binding_modality CHECK (
        modality IN ('video', 'radar', 'contact', 'button', 'wearable', 'other')
    )
);
ALTER TABLE bell.event_ingress_bindings OWNER TO bell_app;

CREATE INDEX IF NOT EXISTS bell_event_ingress_binding_numeric_idx
    ON bell.event_ingress_bindings(tenant_id, site_id, device_id)
    WHERE enabled;

CREATE TABLE IF NOT EXISTS bell.event_ingress_receipts (
    producer_id text NOT NULL,
    source_event_id text NOT NULL,
    candidate_hash bytea NOT NULL,
    event_id text NOT NULL REFERENCES bell.events(id),
    received_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (producer_id, source_event_id),
    CONSTRAINT bell_ingress_receipt_producer CHECK (
        char_length(producer_id) BETWEEN 1 AND 64
        AND producer_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
    ),
    CONSTRAINT bell_ingress_receipt_source CHECK (
        source_event_id ~ '^[A-Za-z0-9_-]{1,128}$'
    ),
    CONSTRAINT bell_ingress_receipt_hash CHECK (octet_length(candidate_hash) = 32),
    CONSTRAINT bell_ingress_receipt_event_id CHECK (
        event_id ~ '^evt_[0-9A-HJKMNP-TV-Z]{26}$'
    )
);
ALTER TABLE bell.event_ingress_receipts OWNER TO bell_app;

DROP TRIGGER IF EXISTS bell_event_ingress_receipts_immutable
    ON bell.event_ingress_receipts;
CREATE TRIGGER bell_event_ingress_receipts_immutable
BEFORE UPDATE OR DELETE ON bell.event_ingress_receipts
FOR EACH ROW EXECUTE FUNCTION bell.reject_immutable_change();

CREATE INDEX IF NOT EXISTS bell_event_ingress_receipt_event_idx
    ON bell.event_ingress_receipts(event_id);

CREATE TABLE IF NOT EXISTS bell.event_ingress_nonces (
    key_id text NOT NULL,
    nonce text NOT NULL,
    request_hash bytea NOT NULL,
    response_status integer NOT NULL,
    response_body jsonb NOT NULL,
    received_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    expires_at timestamptz NOT NULL,
    PRIMARY KEY (key_id, nonce),
    CONSTRAINT bell_ingress_nonce_key CHECK (
        char_length(key_id) BETWEEN 1 AND 64
        AND key_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
    ),
    CONSTRAINT bell_ingress_nonce_value CHECK (
        char_length(nonce) BETWEEN 22 AND 64
        AND nonce ~ '^[A-Za-z0-9_-]+$'
    ),
    CONSTRAINT bell_ingress_nonce_hash CHECK (octet_length(request_hash) = 32),
    CONSTRAINT bell_ingress_nonce_status CHECK (response_status IN (200, 201)),
    CONSTRAINT bell_ingress_nonce_body CHECK (jsonb_typeof(response_body) = 'object'),
    CONSTRAINT bell_ingress_nonce_ttl CHECK (
        expires_at >= received_at + interval '10 minutes'
        AND expires_at <= received_at + interval '11 minutes'
    )
);
ALTER TABLE bell.event_ingress_nonces OWNER TO bell_app;

CREATE INDEX IF NOT EXISTS bell_event_ingress_nonce_expiry_idx
    ON bell.event_ingress_nonces(expires_at, key_id, nonce);

INSERT INTO bell.schema_migrations(version) VALUES (5)
ON CONFLICT (version) DO NOTHING;