excel_inbox.yaml

# .github/workflows/excel_inbox.yaml
#
# Excel Inbox — apply vocabulary changes and validate
# ====================================================
# Triggered when a PR to main touches the inbox/ folder.
#
# Contributor flow:
#   1. Download docs/assets/coremeta4cat_vocabulary.xlsx.
#   2. Edit it (add/modify/delete rows), re-save as coremeta4cat_vocabulary.xlsx.
#   3. Open a PR that places the file at inbox/coremeta4cat_vocabulary.xlsx.
#   4. This workflow:
#        a. Applies the changes to the schema YAML source files.
#        b. Runs `just test` to validate the modified schema.
#        c. Regenerates docs/assets/coremeta4cat_vocabulary.xlsx from the schema.
#        d. Runs a round-trip check (inbox Excel vs updated schema).
#        e. Posts a detailed PR comment with all results.
#        f. On success: commits schema + docs Excel back to the branch and
#           removes the inbox file (with [skip ci] to avoid infinite loops).
#        g. On error: posts the full error list as a PR comment and fails.
#   5. A maintainer reviews the diff, verifies the bot commit, and merges.
#
# Security note: uses pull_request_target with two-checkout pattern.
# Contributor-controlled files (the xlsx) are only opened as data, never
# executed. All scripts run from main (checked out in _main_branch/).
# See: https://securitylab.github.com/resources/github-actions-preventing-pwn-requests/
#
# The Excel is derived output — the schema YAML is the ground truth.
---
name: Excel inbox — apply and validate

on:  # yamllint disable-line rule:truthy
  pull_request_target:
    branches:
      - main
    types: [opened, reopened, synchronize]
    paths:
      - "inbox/**"
  workflow_dispatch:

env:
  FORCE_COLOR: "1"
  INBOX_FILE: "inbox/coremeta4cat_vocabulary.xlsx"

concurrency:
  group: ${{ github.workflow }}-${{ github.event.pull_request.number || github.ref }}
  cancel-in-progress: true

permissions: {}

jobs:
  apply-inbox:
    name: Apply inbox Excel to schema
    if: ${{ !github.event.pull_request.merged }}
    runs-on: ubuntu-latest
    timeout-minutes: 20

    permissions:
      contents: write       # to commit schema changes back to the PR branch
      pull-requests: write  # to post the summary comment

    steps:

      # ── Checkout ─────────────────────────────────────────────────────────

      - name: Check out PR branch (fork-safe)
        uses: actions/checkout@v6.0.3
        with:
          # Check out the contributor's branch so we can push back to it.
          # pull_request_target + explicit head repo is the standard pattern
          # for safe write-back to fork PRs.
          repository: ${{ github.event.pull_request.head.repo.full_name }}
          ref: ${{ github.event.pull_request.head.ref }}
          fetch-depth: 0
          persist-credentials: true

      - name: Check out main branch into _main_branch/
        # Always run OUR scripts from main — never from the PR.
        # This is the key security boundary for pull_request_target.
        # Full checkout needed: just auto-loads config.public.mk and test suite
        # needs examples/, tests/data/, config.yaml, etc.
        uses: actions/checkout@v6.0.3
        with:
          ref: main
          path: _main_branch
          fetch-depth: 1
          persist-credentials: false

      # ── Tool setup ────────────────────────────────────────────────────────

      - name: Install uv
        uses: astral-sh/setup-uv@v8.2.0
        with:
          python-version: "3.12"
          enable-cache: true
          cache-dependency-glob: "uv.lock"

      - name: Install project from main branch
        run: uv sync --dev
        working-directory: _main_branch

      - name: Install just
        run: uv tool install rust-just

      # ── Pre-flight ────────────────────────────────────────────────────────

      - name: Check inbox file exists
        id: inbox_check
        run: |
          if [ -f "${{ env.INBOX_FILE }}" ]; then
            echo "present=true" >> "$GITHUB_OUTPUT"
            echo "Inbox file found: ${{ env.INBOX_FILE }}"
          else
            echo "present=false" >> "$GITHUB_OUTPUT"
            echo "No inbox file at ${{ env.INBOX_FILE }} — nothing to process."
          fi

      # ── Step 1: Apply inbox Excel changes to schema YAMLs ─────────────────

      - name: Apply inbox to schema (inbox_to_schema.py)
        if: steps.inbox_check.outputs.present == 'true'
        id: apply
        run: |
          # Run inbox_to_schema.py from _main_branch/ against the inbox Excel
          # (which lives in the PR branch working directory, one level up).
          #
          # set +e: GitHub Actions shells run with -eo pipefail, which means
          # a failed command substitution (OUTPUT=$(cmd)) would abort the shell
          # before we can write the output to GITHUB_ENV for the PR comment.
          set +e
          OUTPUT=$(uv run python scripts/inbox_to_schema.py \
            "../${{ env.INBOX_FILE }}" 2>&1)
          EXIT_CODE=$?
          set -e

          echo "$OUTPUT"

          # Capture output for PR comment
          {
            echo "apply_output<<APPLY_EOF"
            echo "$OUTPUT"
            echo "APPLY_EOF"
          } >> "$GITHUB_ENV"

          # Classify exit code
          case "$EXIT_CODE" in
            0) echo "status=ok"       >> "$GITHUB_OUTPUT" ;;
            2) echo "status=warnings" >> "$GITHUB_OUTPUT" ;;
            *) echo "status=errors"   >> "$GITHUB_OUTPUT" ;;
          esac

          # Exit code 3 = errors, nothing was written to the schema — fail here.
          # (Comment will be posted in the Post PR comment step below.)
          if [ "$EXIT_CODE" -eq 3 ] || [ "$EXIT_CODE" -eq 1 ]; then
            exit 1
          fi
        working-directory: _main_branch
        # Continue so we can post the PR comment even when this step fails
        continue-on-error: true

      - name: Record apply failure
        if: steps.apply.outcome == 'failure'
        run: echo "apply_failed=true" >> "$GITHUB_ENV"

      # ── Step 2: Validate the modified schema ───────────────────────────────

      - name: Validate schema (just test)
        if: >
          steps.inbox_check.outputs.present == 'true' &&
          steps.apply.outputs.status != 'errors' &&
          steps.apply.outcome != 'failure'
        id: test
        run: |
          # Capture output + exit code; disable set -e so a failing test suite
          # does not abort the shell before we can write to GITHUB_ENV.
          set +e
          TEST_OUTPUT=$(just test 2>&1)
          TEST_EXIT=$?
          set -e

          echo "$TEST_OUTPUT"

          {
            echo "test_output<<TEST_EOF"
            echo "$TEST_OUTPUT"
            echo "TEST_EOF"
          } >> "$GITHUB_ENV"

          if [ "$TEST_EXIT" -eq 0 ]; then
            echo "status=ok"   >> "$GITHUB_OUTPUT"
          else
            echo "status=fail" >> "$GITHUB_OUTPUT"
          fi
          # Step always exits 0 — failure recorded in status output above
        working-directory: _main_branch

      # ── Step 3: Regenerate docs Excel from updated schema ─────────────────

      - name: Regenerate docs/assets Excel (just schema-to-excel)
        if: >
          steps.inbox_check.outputs.present == 'true' &&
          steps.apply.outputs.status != 'errors' &&
          steps.apply.outcome != 'failure' &&
          steps.test.outputs.status == 'ok'
        id: regen_excel
        run: just schema-to-excel
        working-directory: _main_branch
        continue-on-error: true

      # ── Step 4: Round-trip check ───────────────────────────────────────────

      - name: Round-trip check (inbox Excel vs updated schema)
        if: >
          steps.inbox_check.outputs.present == 'true' &&
          steps.apply.outputs.status != 'errors' &&
          steps.apply.outcome != 'failure' &&
          steps.test.outputs.status == 'ok' &&
          steps.regen_excel.outcome == 'success'
        id: roundtrip
        run: |
          set +e
          RT_OUTPUT=$(uv run python scripts/excel_to_schema.py \
            "../${{ env.INBOX_FILE }}" 2>&1)
          set -e

          echo "$RT_OUTPUT"

          {
            echo "roundtrip_output<<RT_EOF"
            echo "$RT_OUTPUT"
            echo "RT_EOF"
          } >> "$GITHUB_ENV"

          if echo "$RT_OUTPUT" | grep -q "Schema and workbook are fully aligned"; then
            echo "status=ok"   >> "$GITHUB_OUTPUT"
          else
            echo "status=diff" >> "$GITHUB_OUTPUT"
          fi
        working-directory: _main_branch
        continue-on-error: true

      # ── Step 5: Post combined PR comment ──────────────────────────────────

      - name: Post PR comment
        if: steps.inbox_check.outputs.present == 'true'
        uses: actions/github-script@v7
        with:
          github-token: ${{ secrets.GITHUB_TOKEN }}
          script: |
            const applyStatus = "${{ steps.apply.outputs.status   }}";
            const testStatus  = "${{ steps.test.outputs.status    }}";
            const rtStatus    = "${{ steps.roundtrip.outputs.status }}";

            const applyOutput = (process.env.apply_output    || "").trim();
            const testOutput  = (process.env.test_output     || "").trim();
            const rtOutput    = (process.env.roundtrip_output || "").trim();

            const applyFailed  = applyStatus === "errors";
            const testFailed   = testStatus  === "fail";
            const rtDiff       = rtStatus    === "diff";

            const allOk = !applyFailed && !testFailed && !rtDiff &&
                          applyStatus !== "" && testStatus !== "";

            const sections = [];

            // ── inbox_to_schema.py report (always shown) ─────────────────
            if (applyOutput) {
              sections.push(applyOutput, "");
            } else {
              sections.push(
                "## 📋 Inbox vocabulary — processing report", "",
                "*(No output from inbox_to_schema.py)*", ""
              );
            }

            // ── schema validation ────────────────────────────────────────
            if (testStatus) {
              const icon = testStatus === "ok" ? "✅" : "❌";
              const msg  = testStatus === "ok"
                ? "All LinkML validation checks passed."
                : "**LinkML validation failed.** Fix the errors and push again.";
              sections.push(
                `### ${icon} Schema validation (\`just test\`)`, "",
                msg, "",
                "<details><summary>Test output</summary>", "",
                "```", testOutput.slice(0, 8000), "```",
                "</details>", ""
              );
            }

            // ── round-trip check ─────────────────────────────────────────
            if (rtStatus) {
              const icon = rtStatus === "ok" ? "✅" : "⚠️";
              const msg  = rtStatus === "ok"
                ? "The inbox workbook and the updated schema are fully aligned."
                : "Some fields in the inbox workbook differ from the regenerated schema. "
                  + "This may indicate fields that could not be automatically converted.";
              sections.push(
                `### ${icon} Round-trip check`, "",
                msg, "",
                "<details><summary>Diff output</summary>", "",
                "```", rtOutput.slice(0, 4000), "```",
                "</details>", ""
              );
            }

            // ── summary ───────────────────────────────────────────────────
            sections.push("---", "");
            if (allOk) {
              sections.push(
                "✅ **All checks passed.** The schema changes have been applied to this "
                + "branch and the docs Excel has been regenerated. "
                + "A maintainer will review the diff and merge.", ""
              );
            } else {
              sections.push(
                "❌ **Some checks failed.** Fix the issues listed above, "
                + "update the workbook, and push again.", ""
              );
            }

            const body = sections.join("\n");

            // Update existing bot comment or create a new one
            const { data: comments } = await github.rest.issues.listComments({
              owner: context.repo.owner,
              repo:  context.repo.repo,
              issue_number: context.issue.number,
            });
            const existing = comments.find(c =>
              c.user.type === "Bot" &&
              (c.body.includes("Inbox vocabulary") || c.body.includes("Excel inbox"))
            );

            if (existing) {
              await github.rest.issues.updateComment({
                owner: context.repo.owner, repo: context.repo.repo,
                comment_id: existing.id, body,
              });
            } else {
              await github.rest.issues.createComment({
                owner: context.repo.owner, repo: context.repo.repo,
                issue_number: context.issue.number, body,
              });
            }

      # ── Step 6: Fail explicitly after comment is posted ───────────────────

      - name: Fail on errors (after comment posted)
        if: >
          steps.inbox_check.outputs.present == 'true' && (
            steps.apply.outcome  == 'failure' ||
            steps.test.outputs.status == 'fail'
          )
        run: |
          echo "::error::Inbox processing failed. See the PR comment for details."
          exit 1

      # ── Step 7: Commit schema changes back to the PR branch ───────────────

      - name: Commit schema changes and remove inbox file
        if: >
          steps.inbox_check.outputs.present == 'true' &&
          steps.apply.outcome  != 'failure' &&
          steps.apply.outputs.status != 'errors' &&
          steps.test.outputs.status  == 'ok'
        run: |
          git config user.name  "github-actions[bot]"
          git config user.email "github-actions[bot]@users.noreply.github.com"

          # Copy modified schema YAMLs from _main_branch/ back to PR branch
          mkdir -p src/coremeta4cat/schema/
          cp -f _main_branch/src/coremeta4cat/schema/*.yaml \
            src/coremeta4cat/schema/

          # Copy regenerated docs Excel
          mkdir -p docs/assets/
          cp -f _main_branch/docs/assets/coremeta4cat_vocabulary.xlsx \
            docs/assets/coremeta4cat_vocabulary.xlsx

          # Stage schema YAMLs, docs Excel, and remove inbox file
          git add src/coremeta4cat/schema/*.yaml
          git add docs/assets/coremeta4cat_vocabulary.xlsx
          git rm --force "${{ env.INBOX_FILE }}"

          # [skip ci] prevents this bot commit from re-triggering CI loops
          git commit -m \
            "ci: apply inbox vocabulary changes and regenerate Excel [skip ci]"
          git push