-- Migration: pipeline step log for POST /pipelines tracing.
-- Target: logging / auth catalog database (e.g. athena_logging).
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS pipeline_step_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    pipeline_run_id uuid NOT NULL,
    request_id text,
    pipeline_name text,
    step text NOT NULL,
    athena_client text,
    table_name text,
    status_code integer,
    duration_ms bigint,
    error_message text,
    details jsonb,
    CONSTRAINT pipeline_step_log_step_check CHECK (step IN ('source', 'transform', 'sink'))
);

CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_pipeline_run_id
    ON pipeline_step_log (pipeline_run_id);

CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_created_at
    ON pipeline_step_log (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_pipeline_name_created_at
    ON pipeline_step_log (pipeline_name, created_at DESC)
    WHERE pipeline_name IS NOT NULL;
