-- Add resilience policy fields and per-state gating ledger for gateway webhooks.
-- Safe to re-run.

ALTER TABLE gateway_webhook
    ADD COLUMN IF NOT EXISTS state_key_template text,
    ADD COLUMN IF NOT EXISTS state_cap_max_fires integer,
    ADD COLUMN IF NOT EXISTS state_cap_window_seconds integer,
    ADD COLUMN IF NOT EXISTS idempotency_template text,
    ADD COLUMN IF NOT EXISTS state_resolution_mode text NOT NULL DEFAULT 'template',
    ADD COLUMN IF NOT EXISTS state_resolution_query_builder jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE gateway_webhook
    DROP CONSTRAINT IF EXISTS gateway_webhook_state_resolution_mode_check;

ALTER TABLE gateway_webhook
    ADD CONSTRAINT gateway_webhook_state_resolution_mode_check CHECK (
        state_resolution_mode IN ('template', 'gateway_query_builder')
    );

ALTER TABLE gateway_webhook
    DROP CONSTRAINT IF EXISTS gateway_webhook_state_cap_nonnegative_check;

ALTER TABLE gateway_webhook
    ADD CONSTRAINT gateway_webhook_state_cap_nonnegative_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_max_fires > 0
    );

ALTER TABLE gateway_webhook
    DROP CONSTRAINT IF EXISTS gateway_webhook_state_window_nonnegative_check;

ALTER TABLE gateway_webhook
    ADD CONSTRAINT gateway_webhook_state_window_nonnegative_check CHECK (
        state_cap_window_seconds IS NULL OR state_cap_window_seconds > 0
    );

ALTER TABLE gateway_webhook
    DROP CONSTRAINT IF EXISTS gateway_webhook_state_cap_requires_window_check;

ALTER TABLE gateway_webhook
    ADD CONSTRAINT gateway_webhook_state_cap_requires_window_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_window_seconds IS NOT NULL
    );

CREATE TABLE IF NOT EXISTS gateway_webhook_state_ledger (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    webhook_id bigint NOT NULL REFERENCES gateway_webhook(id) ON DELETE CASCADE,
    delivery_id bigint REFERENCES gateway_webhook_delivery(id) ON DELETE SET NULL,
    state_key_hash text NOT NULL,
    state_key_raw text,
    window_started_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_state_ledger_gate_lookup
    ON gateway_webhook_state_ledger (webhook_id, state_key_hash, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_state_ledger_window_started
    ON gateway_webhook_state_ledger (window_started_at);
