CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS athena.webhook_sink_events (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    webhook_sink_event_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    sink_name text NOT NULL,
    received_at timestamptz NOT NULL,
    request_path text NOT NULL,
    http_method text NOT NULL,
    source_ip text,
    content_type text,
    verified boolean NOT NULL DEFAULT false,
    event_type text,
    external_id text,
    query_params_json jsonb NOT NULL DEFAULT '{}'::jsonb,
    headers_json jsonb NOT NULL DEFAULT '{}'::jsonb,
    payload_json jsonb,
    payload_text text,
    payload_sha256 text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT webhook_sink_events_sink_name_nonempty CHECK (btrim(sink_name) <> ''),
    CONSTRAINT webhook_sink_events_request_path_nonempty CHECK (btrim(request_path) <> ''),
    CONSTRAINT webhook_sink_events_http_method_nonempty CHECK (btrim(http_method) <> ''),
    CONSTRAINT webhook_sink_events_payload_sha256_nonempty CHECK (btrim(payload_sha256) <> ''),
    CONSTRAINT webhook_sink_events_query_params_json_object CHECK (jsonb_typeof(query_params_json) = 'object'),
    CONSTRAINT webhook_sink_events_headers_json_object CHECK (jsonb_typeof(headers_json) = 'object'),
    CONSTRAINT webhook_sink_events_metadata_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS webhook_sink_events_sink_received_idx
    ON athena.webhook_sink_events (sink_name, received_at DESC);

CREATE INDEX IF NOT EXISTS webhook_sink_events_event_type_idx
    ON athena.webhook_sink_events (event_type, created_at DESC);

CREATE INDEX IF NOT EXISTS webhook_sink_events_external_id_idx
    ON athena.webhook_sink_events (external_id, created_at DESC)
    WHERE external_id IS NOT NULL;
