-- Migration: gateway webhook definitions and delivery log.
-- Target: logging / auth catalog database (e.g. athena_logging).
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS gateway_webhook (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    enabled boolean NOT NULL DEFAULT true,
    name text NOT NULL,
    description text,
    athena_base_url text,
    client_name text NOT NULL,
    table_name text,
    route_key text NOT NULL,
    http_method text NOT NULL,
    url_template text NOT NULL,
    headers_templates jsonb NOT NULL DEFAULT '{}'::jsonb,
    body_template text,
    timeout_ms integer NOT NULL DEFAULT 30000,
    include_request_body_in_context boolean NOT NULL DEFAULT true,
    lookup_keys jsonb NOT NULL DEFAULT '{}'::jsonb,
    slug text,
    state_key_template text,
    state_cap_max_fires integer,
    state_cap_window_seconds integer,
    idempotency_template text,
    state_resolution_mode text NOT NULL DEFAULT 'template',
    state_resolution_query_builder jsonb NOT NULL DEFAULT '{}'::jsonb,
    athena_base_url_norm text GENERATED ALWAYS AS (COALESCE(athena_base_url, '')) STORED,
    table_name_norm text GENERATED ALWAYS AS (COALESCE(table_name, '')) STORED,
    CONSTRAINT gateway_webhook_route_key_check CHECK (route_key IN (
        'gateway_data', 'gateway_fetch', 'gateway_update', 'gateway_query',
        'gateway_insert', 'gateway_delete',
        'rest_get', 'rest_post', 'rest_patch', 'rest_delete',
        'schema_table_created', 'schema_table_dropped',
        'schema_column_added', 'schema_column_dropped', 'schema_column_renamed', 'schema_column_altered',
        'schema_index_created', 'schema_index_dropped', 'schema_extension_installed'
    )),
    CONSTRAINT gateway_webhook_method_check CHECK (http_method IN (
        'GET', 'POST', 'PUT', 'PATCH', 'DELETE'
    )),
    CONSTRAINT gateway_webhook_timeout_ms_check CHECK (timeout_ms >= 1000 AND timeout_ms <= 300000),
    CONSTRAINT gateway_webhook_state_resolution_mode_check CHECK (
        state_resolution_mode IN ('template', 'gateway_query_builder')
    ),
    CONSTRAINT gateway_webhook_state_cap_nonnegative_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_max_fires > 0
    ),
    CONSTRAINT gateway_webhook_state_window_nonnegative_check CHECK (
        state_cap_window_seconds IS NULL OR state_cap_window_seconds > 0
    ),
    CONSTRAINT gateway_webhook_state_cap_requires_window_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_window_seconds IS NOT NULL
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_slug
    ON gateway_webhook (slug)
    WHERE slug IS NOT NULL AND slug <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_natural_identity
    ON gateway_webhook (
        client_name,
        route_key,
        http_method,
        url_template,
        athena_base_url_norm,
        table_name_norm
    );

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_client_route
    ON gateway_webhook (client_name, route_key, enabled);

CREATE TABLE IF NOT EXISTS gateway_webhook_delivery (
    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,
    trigger_route_key text NOT NULL,
    request_id text,
    idempotency_key text NOT NULL,
    status text NOT NULL,
    http_status integer,
    response_headers_json jsonb,
    response_body_snippet text,
    error_message text,
    duration_ms bigint,
    resolved_url text,
    context_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT gateway_webhook_delivery_status_check CHECK (status IN (
        'pending', 'success', 'failed', 'skipped'
    )),
    CONSTRAINT gateway_webhook_delivery_idempotency_key_unique UNIQUE (idempotency_key)
);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_delivery_webhook_created
    ON gateway_webhook_delivery (webhook_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_delivery_status_created
    ON gateway_webhook_delivery (status, created_at DESC);

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);
