-- ARR source XML single-read grants for the SuperAgent runtime fetch provider.
-- PostgreSQL 15+. Additive only; migration 008 remains immutable.

BEGIN;

DO $$
BEGIN
    IF current_database() <> 'booking_test' THEN
        RAISE EXCEPTION
            'ARR source read grants are allowed only in booking_test';
    END IF;
    IF to_regclass('ingestion.artifacts') IS NULL
       OR to_regclass('ingestion.processing_runs') IS NULL THEN
        RAISE EXCEPTION
            'ARR MVP v1 migration 008 must be applied first';
    END IF;
END;
$$;

CREATE TABLE ingestion.source_read_grants (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    grant_key text NOT NULL UNIQUE CHECK (
        grant_key ~ '^[A-Za-z0-9][A-Za-z0-9._:-]{0,127}$'
    ),
    job_key text NOT NULL CHECK (
        job_key ~ '^[A-Za-z0-9][A-Za-z0-9._:-]{0,127}$'
    ),
    attempt_no integer NOT NULL CHECK (attempt_no BETWEEN 1 AND 9999),
    object_key text NOT NULL CHECK (
        btrim(object_key) <> ''
        AND object_key !~ '^/'
        AND object_key !~ '(^|/)\.\.(/|$)'
        AND object_key !~ '[?#]'
    ),
    original_filename text NOT NULL DEFAULT 'source.xml'
        CHECK (original_filename = 'source.xml'),
    created_at timestamptz NOT NULL,
    expires_at timestamptz NOT NULL,
    consumed_at timestamptz,
    CONSTRAINT source_read_grants_lifetime CHECK (
        expires_at > created_at
        AND expires_at <= created_at + interval '5 minutes'
    ),
    CONSTRAINT source_read_grants_consumption_shape CHECK (
        consumed_at IS NULL OR consumed_at >= created_at
    )
);

CREATE INDEX source_read_grants_expiry_idx
    ON ingestion.source_read_grants (expires_at);

CREATE INDEX source_read_grants_job_attempt_idx
    ON ingestion.source_read_grants (job_key, attempt_no);

COMMENT ON TABLE ingestion.source_read_grants IS
  'Opaque job-bound source XML grants. One atomic redemption, maximum five-minute lifetime; never stores bytes, URLs, credentials, guest fields, or Agent-local paths.';

COMMENT ON COLUMN ingestion.source_read_grants.object_key IS
  'Server-side committed ARR object identity resolved only by the trusted runtime fetch provider.';

COMMIT;