name: Prepare Or Publish Packages

on:
  workflow_dispatch:
    inputs:
      mode:
        description: Prepare builds before release, or publish prepared artifacts after release
        required: true
        type: choice
        options:
          - prepare
          - publish
      tag:
        description: Intended or existing release tag in vX.Y.Z form
        required: true
        type: string
      source_ref:
        description: Full 40-character commit SHA for prepare mode
        required: false
        default: ""
        type: string
      prepare_run_id:
        description: Successful prepare workflow run ID for publish mode
        required: false
        type: string

permissions:
  contents: read
  actions: read

env:
  IMAGE_NAME: ghcr.io/evokoa/pggraph

jobs:
  validate:
    name: Validate release
    runs-on: ubuntu-latest
    outputs:
      mode: ${{ steps.release.outputs.mode }}
      tag: ${{ steps.release.outputs.tag }}
      version: ${{ steps.release.outputs.version }}
      short_sha: ${{ steps.commit.outputs.short_sha }}
      full_sha: ${{ steps.commit.outputs.full_sha }}
      source_ref: ${{ steps.release.outputs.source_ref }}
    steps:
      - name: Resolve release input
        id: release
        run: |
          mode="${{ inputs.mode }}"
          tag="${{ inputs.tag }}"
          source_ref="${{ inputs.source_ref }}"

          if [[ "$mode" != "prepare" && "$mode" != "publish" ]]; then
            echo "Mode must be prepare or publish: $mode" >&2
            exit 1
          fi

          if [[ ! "$tag" =~ ^v[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
            echo "Release tag must use vX.Y.Z form: $tag" >&2
            exit 1
          fi

          if [[ "$mode" == "prepare" && -z "$source_ref" ]]; then
            echo "Prepare mode requires source_ref" >&2
            exit 1
          fi
          if [[ "$mode" == "prepare" && ! "$source_ref" =~ ^[0-9a-f]{40}$ ]]; then
            echo "Prepare source_ref must be a full 40-character commit SHA" >&2
            exit 1
          fi
          if [[ "$mode" == "publish" && -z "${{ inputs.prepare_run_id }}" ]]; then
            echo "Publish mode requires prepare_run_id" >&2
            exit 1
          fi
          if [[ "$mode" == "publish" && ! "${{ inputs.prepare_run_id }}" =~ ^[0-9]+$ ]]; then
            echo "Publish prepare_run_id must be a numeric workflow run ID" >&2
            exit 1
          fi

          echo "mode=$mode" >> "$GITHUB_OUTPUT"
          echo "tag=$tag" >> "$GITHUB_OUTPUT"
          echo "version=${tag#v}" >> "$GITHUB_OUTPUT"
          echo "source_ref=$source_ref" >> "$GITHUB_OUTPUT"

      - name: Verify GitHub Release exists
        if: steps.release.outputs.mode == 'publish'
        env:
          GH_TOKEN: ${{ github.token }}
          GH_REPO: ${{ github.repository }}
        run: gh release view "${{ steps.release.outputs.tag }}" >/dev/null

      - name: Checkout release source
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4
        with:
          ref: ${{ steps.release.outputs.mode == 'prepare' && steps.release.outputs.source_ref || steps.release.outputs.tag }}
          fetch-depth: 0

      - name: Validate preparation source
        if: steps.release.outputs.mode == 'prepare'
        run: |
          commit="$(git rev-parse HEAD)"
          test "${{ github.sha }}" = "$commit"
          scripts/validate_release.py \
            --version "${{ steps.release.outputs.version }}" \
            --ref "$commit" --check-main --require-clean

      - name: Validate signed publication tag
        if: steps.release.outputs.mode == 'publish'
        run: |
          test "${{ github.sha }}" = "$(git rev-parse HEAD)"
          scripts/validate_release.py \
            --tag "${{ steps.release.outputs.tag }}" \
            --check-main --require-clean --require-signed-tag

      - name: Authenticate preparation workflow run
        if: steps.release.outputs.mode == 'publish'
        env:
          GH_TOKEN: ${{ github.token }}
          GH_REPO: ${{ github.repository }}
          EXPECTED_SHA: ${{ github.sha }}
          RUN_ID: ${{ inputs.prepare_run_id }}
        run: |
          gh api "repos/$GH_REPO/actions/runs/$RUN_ID" > /tmp/prepare-run.json
          jq -e \
            --arg repo "$GH_REPO" \
            --arg sha "$EXPECTED_SHA" \
            --arg workflow ".github/workflows/release.yml" \
            '.repository.full_name == $repo and
             .head_repository.full_name == $repo and
             .event == "workflow_dispatch" and
             .status == "completed" and
             .conclusion == "success" and
             .head_sha == $sha and
             (.path | split("@")[0]) == $workflow' \
            /tmp/prepare-run.json >/dev/null

      - name: Capture release commit
        id: commit
        run: |
          echo "short_sha=$(git rev-parse --short=12 HEAD)" >> "$GITHUB_OUTPUT"
          echo "full_sha=$(git rev-parse HEAD)" >> "$GITHUB_OUTPUT"

  release-bundle:
    name: Build source release bundle
    if: needs.validate.outputs.mode == 'prepare'
    needs: validate
    runs-on: ubuntu-latest
    permissions:
      contents: read
      id-token: write
      attestations: write
    steps:
      - name: Checkout release source
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4
        with:
          ref: ${{ needs.validate.outputs.full_sha }}
          fetch-depth: 0

      - name: Build deterministic release bundle
        run: |
          scripts/prepare_release_bundle.py \
            --version "${{ needs.validate.outputs.version }}" \
            --ref "${{ needs.validate.outputs.full_sha }}" \
            --out-dir dist

      - name: Verify release bundle
        run: |
          scripts/verify_release_bundle.py dist \
            --version "${{ needs.validate.outputs.version }}" \
            --commit "${{ needs.validate.outputs.full_sha }}"

      - name: Attest release bundle provenance
        uses: actions/attest-build-provenance@96b4a1ef7235a096b17240c259729fdd70c83d45 # v2
        with:
          subject-path: dist/*

      - name: Attest source archive SBOM
        uses: actions/attest-sbom@10926c72720ffc3f7b666661c8e55b1344e2a365 # v2
        with:
          subject-path: dist/pgGraph-${{ needs.validate.outputs.version }}.zip
          sbom-path: dist/pgGraph-${{ needs.validate.outputs.version }}.spdx.json

      - name: Upload workflow artifact
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4
        with:
          name: release-bundle-${{ needs.validate.outputs.version }}-${{ needs.validate.outputs.short_sha }}
          path: dist/
          if-no-files-found: error
          retention-days: 90

  approve-publishing:
    name: Approve package publishing
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
    runs-on: ubuntu-latest
    environment: release
    steps:
      - name: Approved
        run: echo "Approved PGXN and Docker package publishing."

  verify-prepared-artifacts:
    name: Verify prepared source attestations
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - approve-publishing
    runs-on: ubuntu-latest
    permissions:
      contents: read
      actions: read
      attestations: read
    steps:
      - name: Checkout release tag
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4
        with:
          ref: ${{ needs.validate.outputs.tag }}
          fetch-depth: 0

      - name: Download prepared release bundle
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          run-id: ${{ inputs.prepare_run_id }}
          pattern: release-bundle-${{ needs.validate.outputs.version }}-*
          path: dist
          merge-multiple: true
          github-token: ${{ github.token }}

      - name: Verify bundle and GitHub attestations
        env:
          GH_TOKEN: ${{ github.token }}
        run: |
          scripts/verify_release_bundle.py dist \
            --version "${{ needs.validate.outputs.version }}" \
            --commit "${{ needs.validate.outputs.full_sha }}"
          for artifact in dist/*; do
            gh attestation verify "$artifact" \
              --repo "${{ github.repository }}" \
              --signer-workflow "${{ github.repository }}/.github/workflows/release.yml" \
              --source-digest "${{ needs.validate.outputs.full_sha }}" \
              --predicate-type https://slsa.dev/provenance/v1
          done
          gh attestation verify \
            "dist/pgGraph-${{ needs.validate.outputs.version }}.zip" \
            --repo "${{ github.repository }}" \
            --signer-workflow "${{ github.repository }}/.github/workflows/release.yml" \
            --source-digest "${{ needs.validate.outputs.full_sha }}" \
            --predicate-type https://spdx.dev/Document/v2.3

  publish-pgxn:
    name: Publish PGXN package
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - approve-publishing
      - verify-prepared-artifacts
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools:v1@sha256:53e79ef1c8b9a0ee88b51e89b36fd30c46c9f2326aec0cde1d7f66e47ad719ff
    permissions:
      contents: read
      actions: read
    steps:
      - name: Checkout release source
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4
        with:
          ref: ${{ needs.validate.outputs.full_sha }}

      - name: Download prepared release bundle
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          run-id: ${{ inputs.prepare_run_id }}
          pattern: release-bundle-${{ needs.validate.outputs.version }}-*
          path: dist
          merge-multiple: true
          github-token: ${{ github.token }}

      - name: Verify prepared release bundle
        run: |
          scripts/verify_release_bundle.py dist \
            --version "${{ needs.validate.outputs.version }}" \
            --commit "${{ needs.validate.outputs.full_sha }}"

      - name: Publish to PGXN
        env:
          PGXN_USERNAME: ${{ secrets.PGXN_USERNAME }}
          PGXN_PASSWORD: ${{ secrets.PGXN_PASSWORD }}
        run: pgxn-release "dist/pgGraph-${{ needs.validate.outputs.version }}.zip"

  pgxn-verify:
    name: Verify PGXN package
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - publish-pgxn
    runs-on: ubuntu-latest
    permissions:
      contents: read
      actions: read
    steps:
      - name: Download prepared release bundle
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          run-id: ${{ inputs.prepare_run_id }}
          pattern: release-bundle-${{ needs.validate.outputs.version }}-*
          path: dist
          merge-multiple: true
          github-token: ${{ github.token }}

      - name: Verify PGXN metadata is visible
        run: |
          url="https://api.pgxn.org/dist/pggraph/${{ needs.validate.outputs.version }}/META.json"

          for _ in {1..30}; do
            if curl --fail --silent --show-error --location "$url" >/tmp/pgxn-meta.json; then
              break
            fi
            sleep 10
          done

          test -s /tmp/pgxn-meta.json

      - name: Verify PGXN version metadata
        run: |
          python3 - <<'PY'
          import json
          from pathlib import Path

          expected = "${{ needs.validate.outputs.version }}"
          meta = json.loads(Path("/tmp/pgxn-meta.json").read_text(encoding="utf-8"))
          actual = meta.get("version")

          if actual != expected:
              raise SystemExit(f"Expected PGXN version {expected}, got {actual!r}")
          PY

      - name: Verify published PGXN archive bytes
        run: |
          url="https://api.pgxn.org/dist/pggraph/${{ needs.validate.outputs.version }}/pgGraph-${{ needs.validate.outputs.version }}.zip"
          curl --fail --silent --show-error --location "$url" --output /tmp/pggraph-published.zip
          expected="$(sha256sum "dist/pgGraph-${{ needs.validate.outputs.version }}.zip" | awk '{print $1}')"
          actual="$(sha256sum /tmp/pggraph-published.zip | awk '{print $1}')"
          test "$actual" = "$expected"

  attach-pgxn-artifact:
    name: Attach PGXN artifact to GitHub Release
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - approve-publishing
      - verify-prepared-artifacts
      - pgxn-verify
    runs-on: ubuntu-latest
    permissions:
      contents: write
      actions: read
    steps:
      - name: Checkout release source
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4
        with:
          ref: ${{ needs.validate.outputs.full_sha }}
          fetch-depth: 0

      - name: Download prepared release bundle
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          run-id: ${{ inputs.prepare_run_id }}
          pattern: release-bundle-${{ needs.validate.outputs.version }}-*
          path: dist
          merge-multiple: true
          github-token: ${{ github.token }}

      - name: Verify prepared release bundle
        run: |
          scripts/verify_release_bundle.py dist \
            --version "${{ needs.validate.outputs.version }}" \
            --commit "${{ needs.validate.outputs.full_sha }}"

      - name: Attach exact bundle to GitHub Release
        env:
          GH_TOKEN: ${{ github.token }}
          GH_REPO: ${{ github.repository }}
        run: |
          gh release upload "${{ needs.validate.outputs.tag }}" \
            dist/* --clobber

  docker:
    name: Prepare Docker image PG${{ matrix.pg }} ${{ matrix.platform.name }}
    if: needs.validate.outputs.mode == 'prepare'
    needs:
      - validate
    runs-on: ${{ matrix.platform.runner }}
    permissions:
      contents: read
      packages: write
    strategy:
      fail-fast: false
      matrix:
        pg: [14, 15, 16, 17, 18]
        platform:
          - name: linux/amd64
            runner: ubuntu-24.04
            pair: linux-amd64
          - name: linux/arm64
            runner: ubuntu-24.04-arm
            pair: linux-arm64
    steps:
      - name: Checkout release source
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4
        with:
          ref: ${{ needs.validate.outputs.full_sha }}
          fetch-depth: 0

      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@8d2750c68a42422c14e847fe6c8ac0403b4cbd6f # v3

      - name: Login to GHCR
        uses: docker/login-action@c94ce9fb468520275223c153574b00df6fe4bcc9 # v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ github.token }}

      - name: Extract Docker metadata
        id: meta
        uses: docker/metadata-action@c299e40c65443455700f0fdfc63efafe5b349051 # v5
        with:
          images: ${{ env.IMAGE_NAME }}
          tags: |
            type=raw,value=pg${{ matrix.pg }}-sha-${{ needs.validate.outputs.short_sha }}
            type=raw,value=pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}-prepared
            type=raw,value=pg${{ matrix.pg }}-${{ needs.validate.outputs.version }}-prepared
          labels: |
            org.opencontainers.image.version=${{ needs.validate.outputs.version }}
            org.opencontainers.image.postgresql.major=${{ matrix.pg }}
            org.opencontainers.image.revision=${{ needs.validate.outputs.short_sha }}

      - name: Select immutable PostgreSQL base image
        id: postgres-base
        run: |
          case "${{ matrix.pg }}" in
            14) image="postgres:14-bookworm@sha256:9062043625415b4628f0ca44c2c65de93778afb7b1db33aff67877cace3b966f" ;;
            15) image="postgres:15-bookworm@sha256:b0c5bab0fbba8e0c221f73b1dc6359ec35f8650074377e727299df248fc8ad51" ;;
            16) image="postgres:16-bookworm@sha256:92620daddcd947f8d5ab5ba66e848702fe443d87fed30c4cea8e389fd78dfc55" ;;
            17) image="postgres:17-bookworm@sha256:4f736ae292687621d4dbe0d499ffd024a36bd2ee7d8ca6f2ccd4c800f047b394" ;;
            18) image="postgres:18-bookworm@sha256:1961f96e6029a02c3812d7cb329a3b03a3ac2bb067058dec17b0f5596aca9296" ;;
            *) echo "Unsupported PostgreSQL major: ${{ matrix.pg }}" >&2; exit 2 ;;
          esac
          echo "image=$image" >> "$GITHUB_OUTPUT"

      - name: Build and push
        id: build
        uses: docker/build-push-action@10e90e3645eae34f1e60eeb005ba3a3d33f178e8 # v6
        with:
          context: .
          platforms: ${{ matrix.platform.name }}
          build-args: |
            PG_MAJOR=${{ matrix.pg }}
            POSTGRES_IMAGE=${{ steps.postgres-base.outputs.image }}
          outputs: type=image,push-by-digest=true,name-canonical=true,push=true
          tags: ${{ env.IMAGE_NAME }}
          labels: ${{ steps.meta.outputs.labels }}
          sbom: true
          provenance: mode=max
          cache-from: type=gha,scope=pggraph-pg${{ matrix.pg }}-${{ matrix.platform.pair }}
          cache-to: type=gha,mode=max,scope=pggraph-pg${{ matrix.pg }}-${{ matrix.platform.pair }}

      - name: Export digest
        run: |
          mkdir -p "${{ runner.temp }}/digests"
          digest="${{ steps.build.outputs.digest }}"
          touch "${{ runner.temp }}/digests/${digest#sha256:}"

      - name: Upload digest
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4
        with:
          name: digests-pg${{ matrix.pg }}-${{ matrix.platform.pair }}
          path: ${{ runner.temp }}/digests/*
          if-no-files-found: error
          retention-days: 90

  docker-merge:
    name: Merge prepared Docker image PG${{ matrix.pg }}
    if: needs.validate.outputs.mode == 'prepare'
    needs:
      - validate
      - docker
    runs-on: ubuntu-24.04
    permissions:
      contents: read
      packages: write
      id-token: write
      attestations: write
    strategy:
      fail-fast: false
      matrix:
        pg: [14, 15, 16, 17, 18]
    steps:
      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@8d2750c68a42422c14e847fe6c8ac0403b4cbd6f # v3

      - name: Login to GHCR
        uses: docker/login-action@c94ce9fb468520275223c153574b00df6fe4bcc9 # v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ github.token }}

      - name: Extract Docker metadata
        id: meta
        uses: docker/metadata-action@c299e40c65443455700f0fdfc63efafe5b349051 # v5
        with:
          images: ${{ env.IMAGE_NAME }}
          tags: |
            type=raw,value=pg${{ matrix.pg }}-sha-${{ needs.validate.outputs.short_sha }}
            type=raw,value=pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}-prepared
            type=raw,value=pg${{ matrix.pg }}-${{ needs.validate.outputs.version }}-prepared

      - name: Download digests
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          path: ${{ runner.temp }}/digests
          pattern: digests-pg${{ matrix.pg }}-*
          merge-multiple: true

      - name: Create manifest list and push
        working-directory: ${{ runner.temp }}/digests
        env:
          DOCKER_METADATA_OUTPUT_JSON: ${{ steps.meta.outputs.json }}
        run: |
          digest_count="$(find . -maxdepth 1 -type f | wc -l | tr -d ' ')"
          if [[ "$digest_count" != "2" ]]; then
            echo "Expected 2 platform digests for PG${{ matrix.pg }}, found $digest_count" >&2
            find . -maxdepth 1 -type f -print >&2
            exit 1
          fi

          docker buildx imagetools create \
            $(jq -cr '.tags | map("-t " + .) | join(" ")' <<< "$DOCKER_METADATA_OUTPUT_JSON") \
            $(printf '${{ env.IMAGE_NAME }}@sha256:%s ' *)

      - name: Resolve merged manifest digest
        id: manifest
        run: |
          image="${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}-prepared"
          digest="$(docker buildx imagetools inspect "$image" | awk '/Digest:/ { print $2; exit }')"
          echo "digest=$digest" >> "$GITHUB_OUTPUT"

      - name: Record prepared manifest digest
        run: |
          mkdir -p "${{ runner.temp }}/manifest"
          jq -n \
            --arg image "${{ env.IMAGE_NAME }}" \
            --arg pg "${{ matrix.pg }}" \
            --arg tag "${{ needs.validate.outputs.tag }}" \
            --arg commit "${{ needs.validate.outputs.full_sha }}" \
            --arg digest "${{ steps.manifest.outputs.digest }}" \
            '{image: $image, postgresql_major: ($pg | tonumber), intended_tag: $tag, source_commit: $commit, digest: $digest}' \
            > "${{ runner.temp }}/manifest/pg${{ matrix.pg }}.json"

      - name: Retain prepared manifest digest
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4
        with:
          name: docker-manifest-pg${{ matrix.pg }}-${{ needs.validate.outputs.short_sha }}
          path: ${{ runner.temp }}/manifest/pg${{ matrix.pg }}.json
          if-no-files-found: error
          retention-days: 90

      - name: Attest image provenance
        uses: actions/attest-build-provenance@96b4a1ef7235a096b17240c259729fdd70c83d45 # v2
        with:
          subject-name: ${{ env.IMAGE_NAME }}
          subject-digest: ${{ steps.manifest.outputs.digest }}
          push-to-registry: true

  docker-verify:
    name: Verify prepared image PG${{ matrix.pg }} ${{ matrix.platform.name }}
    if: needs.validate.outputs.mode == 'prepare'
    needs:
      - validate
      - docker-merge
    runs-on: ${{ matrix.platform.runner }}
    permissions:
      packages: read
    strategy:
      fail-fast: false
      matrix:
        pg: [14, 15, 16, 17, 18]
        platform:
          - name: linux/amd64
            runner: ubuntu-24.04
            pair: linux-amd64
          - name: linux/arm64
            runner: ubuntu-24.04-arm
            pair: linux-arm64
    steps:
      - name: Login to GHCR
        uses: docker/login-action@c94ce9fb468520275223c153574b00df6fe4bcc9 # v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ github.token }}

      - name: Pull and start release image
        run: |
          image="${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}-prepared"
          docker pull --platform "${{ matrix.platform.name }}" "$image"
          docker run -d --rm \
            --platform "${{ matrix.platform.name }}" \
            --name pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
            -e POSTGRES_PASSWORD=postgres \
            -p 55432:5432 \
            "$image"

      - name: Wait for PostgreSQL
        run: |
          consecutive=0
          for _ in {1..60}; do
            if docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 -tAc "SELECT 1" >/dev/null 2>&1; then
              consecutive=$((consecutive + 1))
              if [[ "$consecutive" -ge 2 ]]; then
                exit 0
              fi
            else
              consecutive=0
            fi
            sleep 1
          done
          docker logs pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }}
          exit 1

      - name: Verify extensions
        run: |
          count=""
          for _ in {1..60}; do
            if count="$(docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 \
              -tAc "SELECT count(*) FROM pg_extension WHERE extname IN ('graph', 'pg_cron');")"; then
              if [[ "$count" == "2" ]]; then
                break
              fi
            else
              count=""
            fi

            sleep 2
          done

          if [[ "$count" != "2" ]]; then
            echo "Expected graph and pg_cron extensions, found $count" >&2
            docker logs pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }}
            docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 \
              -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
            exit 1
          fi

          server_version_num="$(docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
            psql -U postgres -d graph -v ON_ERROR_STOP=1 \
            -tAc "SHOW server_version_num;")"

          if [[ "${server_version_num:0:2}" != "${{ matrix.pg }}" ]]; then
            echo "Expected PostgreSQL ${{ matrix.pg }}, got server_version_num=$server_version_num" >&2
            exit 1
          fi

      - name: Stop container
        if: always()
        run: docker rm -f pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} >/dev/null 2>&1 || true

  docker-manifests:
    name: Retain prepared Docker manifest digests
    if: needs.validate.outputs.mode == 'prepare'
    needs:
      - validate
      - docker-merge
      - docker-verify
    runs-on: ubuntu-latest
    steps:
      - name: Download prepared manifest records
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          pattern: docker-manifest-pg*-${{ needs.validate.outputs.short_sha }}
          path: manifests
          merge-multiple: true

      - name: Build deterministic manifest inventory
        run: jq -s 'sort_by(.postgresql_major)' manifests/*.json > docker-manifests.json

      - name: Upload manifest inventory
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4
        with:
          name: docker-manifests-${{ needs.validate.outputs.version }}-${{ needs.validate.outputs.short_sha }}
          path: docker-manifests.json
          if-no-files-found: error
          retention-days: 90

  publish-docker:
    name: Publish Docker image PG${{ matrix.pg }}
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - approve-publishing
      - verify-prepared-artifacts
    runs-on: ubuntu-latest
    permissions:
      contents: read
      packages: write
      actions: read
      attestations: read
    strategy:
      fail-fast: false
      matrix:
        pg: [14, 15, 16, 17, 18]
    steps:
      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@8d2750c68a42422c14e847fe6c8ac0403b4cbd6f # v3

      - name: Login to GHCR
        uses: docker/login-action@c94ce9fb468520275223c153574b00df6fe4bcc9 # v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ github.token }}

      - name: Download prepared manifest inventory
        uses: actions/download-artifact@d3f86a106a0bac45b974a628896c90dbdf5c8093 # v4
        with:
          run-id: ${{ inputs.prepare_run_id }}
          pattern: docker-manifests-${{ needs.validate.outputs.version }}-*
          path: manifests
          merge-multiple: true
          github-token: ${{ github.token }}

      - name: Verify and promote prepared image digest
        env:
          GH_TOKEN: ${{ github.token }}
        run: |
          source="${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}-prepared"
          sha_source="${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-sha-${{ needs.validate.outputs.short_sha }}"
          docker buildx imagetools inspect "$source" >/dev/null
          docker buildx imagetools inspect "$sha_source" >/dev/null

          source_digest="$(docker buildx imagetools inspect "$source" | awk '/Digest:/ { print $2; exit }')"
          sha_digest="$(docker buildx imagetools inspect "$sha_source" | awk '/Digest:/ { print $2; exit }')"
          jq -e \
            --arg image "${{ env.IMAGE_NAME }}" \
            --arg tag "${{ needs.validate.outputs.tag }}" \
            --arg commit "${{ needs.validate.outputs.full_sha }}" \
            --argjson pg "${{ matrix.pg }}" \
            '[.[] | select(
              .image == $image and
              .intended_tag == $tag and
              .source_commit == $commit and
              .postgresql_major == $pg and
              (.digest | startswith("sha256:"))
            )] | length == 1' manifests/docker-manifests.json >/dev/null
          expected_digest="$(jq -r '.[] | select(.postgresql_major == ${{ matrix.pg }}) | .digest' manifests/docker-manifests.json)"

          if [[ "$source_digest" != "$sha_digest" || "$source_digest" != "$expected_digest" ]]; then
            echo "Prepared Docker digest does not match the retained candidate manifest" >&2
            exit 1
          fi
          source_by_digest="${{ env.IMAGE_NAME }}@$expected_digest"
          gh attestation verify "oci://$source_by_digest" \
            --repo "${{ github.repository }}" \
            --signer-workflow "${{ github.repository }}/.github/workflows/release.yml" \
            --source-digest "${{ needs.validate.outputs.full_sha }}" \
            --predicate-type https://slsa.dev/provenance/v1
          docker buildx imagetools inspect "$source_by_digest" \
            --format '{{ json .SBOM }}' | jq -e 'length > 0' >/dev/null

          tags=(
            -t "${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}"
            -t "${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-${{ needs.validate.outputs.version }}"
            -t "${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}"
            -t "${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-sha-${{ needs.validate.outputs.short_sha }}"
          )

          if [[ "${{ matrix.pg }}" == "17" ]]; then
            tags+=(
              -t "${{ env.IMAGE_NAME }}:${{ needs.validate.outputs.tag }}"
              -t "${{ env.IMAGE_NAME }}:${{ needs.validate.outputs.version }}"
              -t "${{ env.IMAGE_NAME }}:latest"
              -t "${{ env.IMAGE_NAME }}:sha-${{ needs.validate.outputs.short_sha }}"
            )
          fi

          docker buildx imagetools create "${tags[@]}" "$source_by_digest"

  publish-docker-verify:
    name: Verify published image PG${{ matrix.pg }} ${{ matrix.platform.name }}
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - publish-docker
    runs-on: ${{ matrix.platform.runner }}
    permissions:
      packages: read
    strategy:
      fail-fast: false
      matrix:
        pg: [14, 15, 16, 17, 18]
        platform:
          - name: linux/amd64
            runner: ubuntu-24.04
            pair: linux-amd64
          - name: linux/arm64
            runner: ubuntu-24.04-arm
            pair: linux-arm64
    steps:
      - name: Login to GHCR
        uses: docker/login-action@c94ce9fb468520275223c153574b00df6fe4bcc9 # v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ github.token }}

      - name: Pull and start published image
        run: |
          image="${{ env.IMAGE_NAME }}:pg${{ matrix.pg }}-${{ needs.validate.outputs.tag }}"
          docker pull --platform "${{ matrix.platform.name }}" "$image"
          docker run -d --rm \
            --platform "${{ matrix.platform.name }}" \
            --name pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
            -e POSTGRES_PASSWORD=postgres \
            -p 55432:5432 \
            "$image"

      - name: Wait for PostgreSQL
        run: |
          consecutive=0
          for _ in {1..60}; do
            if docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 -tAc "SELECT 1" >/dev/null 2>&1; then
              consecutive=$((consecutive + 1))
              if [[ "$consecutive" -ge 2 ]]; then
                exit 0
              fi
            else
              consecutive=0
            fi
            sleep 1
          done
          docker logs pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }}
          exit 1

      - name: Verify extensions
        run: |
          count=""
          for _ in {1..60}; do
            if count="$(docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 \
              -tAc "SELECT count(*) FROM pg_extension WHERE extname IN ('graph', 'pg_cron');")"; then
              if [[ "$count" == "2" ]]; then
                break
              fi
            else
              count=""
            fi

            sleep 2
          done

          if [[ "$count" != "2" ]]; then
            echo "Expected graph and pg_cron extensions, found $count" >&2
            docker logs pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }}
            docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 \
              -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
            exit 1
          fi

          server_version_num="$(docker exec pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} \
            psql -U postgres -d graph -v ON_ERROR_STOP=1 \
            -tAc "SHOW server_version_num;")"

          if [[ "${server_version_num:0:2}" != "${{ matrix.pg }}" ]]; then
            echo "Expected PostgreSQL ${{ matrix.pg }}, got server_version_num=$server_version_num" >&2
            exit 1
          fi

      - name: Stop container
        if: always()
        run: docker rm -f pggraph-release-verify-pg${{ matrix.pg }}-${{ matrix.platform.pair }} >/dev/null 2>&1 || true

  docker-verify-default:
    name: Verify default published image ${{ matrix.platform.name }}
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - publish-docker
    runs-on: ${{ matrix.platform.runner }}
    permissions:
      packages: read
    strategy:
      fail-fast: false
      matrix:
        platform:
          - name: linux/amd64
            runner: ubuntu-24.04
            pair: linux-amd64
          - name: linux/arm64
            runner: ubuntu-24.04-arm
            pair: linux-arm64
    steps:
      - name: Login to GHCR
        uses: docker/login-action@c94ce9fb468520275223c153574b00df6fe4bcc9 # v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ github.token }}

      - name: Pull and start default release image
        run: |
          image="${{ env.IMAGE_NAME }}:${{ needs.validate.outputs.tag }}"
          docker pull --platform "${{ matrix.platform.name }}" "$image"
          docker run -d --rm \
            --platform "${{ matrix.platform.name }}" \
            --name pggraph-release-verify-default-${{ matrix.platform.pair }} \
            -e POSTGRES_PASSWORD=postgres \
            -p 55432:5432 \
            "$image"

      - name: Wait for PostgreSQL
        run: |
          consecutive=0
          for _ in {1..60}; do
            if docker exec pggraph-release-verify-default-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 -tAc "SELECT 1" >/dev/null 2>&1; then
              consecutive=$((consecutive + 1))
              if [[ "$consecutive" -ge 2 ]]; then
                exit 0
              fi
            else
              consecutive=0
            fi
            sleep 1
          done
          docker logs pggraph-release-verify-default-${{ matrix.platform.pair }}
          exit 1

      - name: Verify extensions and default PostgreSQL major
        run: |
          count=""
          for _ in {1..60}; do
            if count="$(docker exec pggraph-release-verify-default-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 \
              -tAc "SELECT count(*) FROM pg_extension WHERE extname IN ('graph', 'pg_cron');")"; then
              if [[ "$count" == "2" ]]; then
                break
              fi
            else
              count=""
            fi

            sleep 2
          done

          if [[ "$count" != "2" ]]; then
            echo "Expected graph and pg_cron extensions, found $count" >&2
            docker logs pggraph-release-verify-default-${{ matrix.platform.pair }}
            docker exec pggraph-release-verify-default-${{ matrix.platform.pair }} \
              psql -U postgres -d graph -v ON_ERROR_STOP=1 \
              -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
            exit 1
          fi

          server_version_num="$(docker exec pggraph-release-verify-default-${{ matrix.platform.pair }} \
            psql -U postgres -d graph -v ON_ERROR_STOP=1 \
            -tAc "SHOW server_version_num;")"

          if [[ "${server_version_num:0:2}" != "17" ]]; then
            echo "Expected default PostgreSQL 17 image, got server_version_num=$server_version_num" >&2
            exit 1
          fi

      - name: Stop container
        if: always()
        run: docker rm -f pggraph-release-verify-default-${{ matrix.platform.pair }} >/dev/null 2>&1 || true

  publish-summary:
    name: Publish summary
    if: needs.validate.outputs.mode == 'publish'
    needs:
      - validate
      - pgxn-verify
      - attach-pgxn-artifact
      - publish-docker-verify
      - docker-verify-default
    runs-on: ubuntu-latest
    steps:
      - name: Summarize published packages
        run: |
          {
            echo "## Published packages"
            echo
            echo "- PGXN: pggraph ${{ needs.validate.outputs.version }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:${{ needs.validate.outputs.tag }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg14-${{ needs.validate.outputs.tag }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg15-${{ needs.validate.outputs.tag }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg16-${{ needs.validate.outputs.tag }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg17-${{ needs.validate.outputs.tag }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg18-${{ needs.validate.outputs.tag }}"
          } >> "$GITHUB_STEP_SUMMARY"

  prepare-summary:
    name: Prepare summary
    if: needs.validate.outputs.mode == 'prepare'
    needs:
      - validate
      - release-bundle
      - docker-verify
      - docker-manifests
    runs-on: ubuntu-latest
    steps:
      - name: Summarize prepared packages
        run: |
          {
            echo "## Prepared packages"
            echo
            echo "- Commit: ${{ needs.validate.outputs.short_sha }}"
            echo "- Source bundle: release-bundle-${{ needs.validate.outputs.version }}-${{ needs.validate.outputs.short_sha }}"
            echo "- Docker manifests: docker-manifests-${{ needs.validate.outputs.version }}-${{ needs.validate.outputs.short_sha }}"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg14-${{ needs.validate.outputs.tag }}-prepared"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg15-${{ needs.validate.outputs.tag }}-prepared"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg16-${{ needs.validate.outputs.tag }}-prepared"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg17-${{ needs.validate.outputs.tag }}-prepared"
            echo "- Docker: ${{ env.IMAGE_NAME }}:pg18-${{ needs.validate.outputs.tag }}-prepared"
          } >> "$GITHUB_STEP_SUMMARY"