-- Active: 1782426846920@@thomas.proxy.rlwy.net@19878@railway
-- Migration: gateway webhook sink definitions, delivery log, and state ledger.
-- Target: logging / auth catalog database (e.g. athena_logging).
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_sink (
    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,
    target_client_name text NOT NULL,
    target_table text NOT NULL,
    target_write_mode text NOT NULL DEFAULT 'insert',
    target_conflict_columns jsonb NOT NULL DEFAULT '[]'::jsonb,
    projection_template jsonb NOT NULL DEFAULT '{}'::jsonb,
    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_sink_route_key_check CHECK (route_key IN (
        'billing_document_upserted',
        '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_sink_target_table_format_check CHECK (
        target_table ~ '^[A-Za-z0-9_]+\.[A-Za-z0-9_]+$'
    ),
    CONSTRAINT gateway_webhook_sink_target_write_mode_check CHECK (
        target_write_mode IN ('insert', 'upsert')
    ),
    CONSTRAINT gateway_webhook_sink_target_conflict_columns_type_check CHECK (
        jsonb_typeof(target_conflict_columns) = 'array'
    ),
    CONSTRAINT gateway_webhook_sink_state_resolution_mode_check CHECK (
        state_resolution_mode IN ('template', 'gateway_query_builder')
    ),
    CONSTRAINT gateway_webhook_sink_state_cap_nonnegative_check CHECK (
        state_cap_max_fires IS NULL OR state_cap_max_fires > 0
    ),
    CONSTRAINT gateway_webhook_sink_state_window_nonnegative_check CHECK (
        state_cap_window_seconds IS NULL OR state_cap_window_seconds > 0
    ),
    CONSTRAINT gateway_webhook_sink_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_sink_slug
    ON athena.gateway_webhook_sink (slug)
    WHERE slug IS NOT NULL AND slug <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_gateway_webhook_sink_natural_identity
    ON athena.gateway_webhook_sink (
        client_name,
        route_key,
        target_client_name,
        target_table,
        athena_base_url_norm,
        table_name_norm
    );

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_client_route
    ON athena.gateway_webhook_sink (client_name, route_key, enabled);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_target_client
    ON athena.gateway_webhook_sink (target_client_name, enabled);

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_sink_delivery (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    sink_id bigint NOT NULL REFERENCES athena.gateway_webhook_sink(id) ON DELETE CASCADE,
    trigger_route_key text NOT NULL,
    request_id text,
    idempotency_key text NOT NULL,
    status text NOT NULL,
    inserted_row_count integer,
    error_message text,
    duration_ms bigint,
    target_client_name text NOT NULL,
    target_table text NOT NULL,
    projected_payload jsonb,
    context_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT gateway_webhook_sink_delivery_status_check CHECK (status IN (
        'pending', 'success', 'failed', 'skipped'
    )),
    CONSTRAINT gateway_webhook_sink_delivery_idempotency_key_unique UNIQUE (idempotency_key)
);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_delivery_sink_created
    ON athena.gateway_webhook_sink_delivery (sink_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_delivery_status_created
    ON athena.gateway_webhook_sink_delivery (status, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.gateway_webhook_sink_state_ledger (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    sink_id bigint NOT NULL REFERENCES athena.gateway_webhook_sink(id) ON DELETE CASCADE,
    delivery_id bigint REFERENCES athena.gateway_webhook_sink_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_sink_state_ledger_gate_lookup
    ON athena.gateway_webhook_sink_state_ledger (sink_id, state_key_hash, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_webhook_sink_state_ledger_window_started
    ON athena.gateway_webhook_sink_state_ledger (window_started_at);
