name: 'pgmold CI'
description: 'PostgreSQL schema CI — migration plans, drift detection, PR comments, auto-labeling, and tunnel support for firewalled databases'
author: 'fmguerreiro'

inputs:
  schema:
    description: 'Schema source(s), space-separated (e.g. sql:schema.sql or drizzle:drizzle.config.ts)'
    required: true
  database:
    description: 'PostgreSQL connection string (e.g. db:postgres://user:pass@host:5432/dbname)'
    required: false
  baseline:
    description: 'sql:path/to/baseline.sql for SQL-to-SQL diff mode (no live database required)'
    required: false
  target-schemas:
    description: 'Comma-separated list of PostgreSQL schemas to introspect'
    required: false
    default: 'public'
  version:
    description: 'pgmold version to install'
    required: false
    default: 'latest'
  fail-on-drift:
    description: 'Fail the action if drift is detected'
    required: false
    default: 'true'
  plan-comment:
    description: 'Post migration plan as a PR comment'
    required: false
    default: 'true'
  drift-check:
    description: 'Run drift detection against the live database'
    required: false
    default: 'true'
  auto-label:
    description: 'Add database-schema label to the PR when schema changes are detected'
    required: false
    default: 'true'
  github-token:
    description: 'GitHub token for API calls (PR comments, labels)'
    required: false
    default: ${{ github.token }}
  tunnel-command:
    description: 'Command to run in background to establish a tunnel to the database (e.g. ssh -N -L 15432:db:5432 bastion)'
    required: false
  tunnel-port:
    description: 'Local port the tunnel exposes (used for TCP readiness check)'
    required: false
    default: '15432'
  tunnel-ready-timeout:
    description: 'Seconds to wait for the tunnel to accept TCP connections'
    required: false
    default: '30'
  database-secret-command:
    description: 'Command that prints a db:postgres://... connection string to stdout'
    required: false
  plan-args:
    description: 'Extra arguments to pass to pgmold plan (e.g. --exclude "public.legacy_*" --exclude-grants-for-role admin_role --include-types tables,functions)'
    required: false
    default: ''
  pr-number:
    description: 'PR number for comments/labels (auto-detected for pull_request events, required for workflow_call)'
    required: false

outputs:
  has-drift:
    description: 'Whether drift was detected (true/false)'
    value: ${{ steps.drift.outputs.has-drift }}
  expected-fingerprint:
    description: 'Expected schema fingerprint from SQL files'
    value: ${{ steps.drift.outputs.expected-fingerprint }}
  actual-fingerprint:
    description: 'Actual schema fingerprint from database'
    value: ${{ steps.drift.outputs.actual-fingerprint }}
  report:
    description: 'Full JSON drift report'
    value: ${{ steps.drift.outputs.report }}
  plan-json:
    description: 'Full plan JSON output'
    value: ${{ steps.plan.outputs.plan-json }}
  statement-count:
    description: 'Number of SQL statements in the migration plan'
    value: ${{ steps.plan.outputs.statement-count }}
  has-destructive:
    description: 'Whether the plan contains destructive operations'
    value: ${{ steps.plan.outputs.has-destructive }}
  comment-id:
    description: 'ID of the PR comment that was posted or updated'
    value: ${{ steps.comment.outputs.comment-id }}

runs:
  using: 'composite'
  steps:
    - name: Determine platform and architecture
      shell: bash
      id: platform
      run: |
        OS=$(uname -s | tr '[:upper:]' '[:lower:]')
        ARCH=$(uname -m)

        if [[ "$OS" == "linux" ]]; then
          if [[ "$ARCH" == "x86_64" ]]; then
            TARGET="x86_64-unknown-linux-gnu"
          elif [[ "$ARCH" == "aarch64" ]] || [[ "$ARCH" == "arm64" ]]; then
            TARGET="aarch64-unknown-linux-gnu"
          else
            echo "Unsupported Linux architecture: $ARCH"
            exit 1
          fi
        elif [[ "$OS" == "darwin" ]]; then
          if [[ "$ARCH" == "x86_64" ]]; then
            TARGET="x86_64-apple-darwin"
          elif [[ "$ARCH" == "arm64" ]]; then
            TARGET="aarch64-apple-darwin"
          else
            echo "Unsupported macOS architecture: $ARCH"
            exit 1
          fi
        else
          echo "Unsupported OS: $OS"
          exit 1
        fi

        echo "target=$TARGET" >> "$GITHUB_OUTPUT"

    - name: Get latest release version
      shell: bash
      id: version
      env:
        INPUT_VERSION: ${{ inputs.version }}
        GH_TOKEN: ${{ inputs.github-token }}
      run: |
        if [[ "$INPUT_VERSION" == "latest" ]]; then
          VERSION=$(gh api repos/fmguerreiro/pgmold/releases/latest --jq '.tag_name')
        else
          VERSION="$INPUT_VERSION"
        fi

        if [[ -z "$VERSION" ]]; then
          echo "::error::Could not resolve pgmold version (got empty string)"
          exit 1
        fi
        echo "version=$VERSION" >> "$GITHUB_OUTPUT"

    - name: Download and install pgmold
      shell: bash
      env:
        VERSION: ${{ steps.version.outputs.version }}
        TARGET: ${{ steps.platform.outputs.target }}
      run: |
        echo "Downloading pgmold ${VERSION} for ${TARGET}..."
        curl --fail -L -o pgmold.tar.gz \
          "https://github.com/fmguerreiro/pgmold/releases/download/${VERSION}/pgmold-${TARGET}.tar.gz"

        tar xzf pgmold.tar.gz
        chmod +x pgmold

        mkdir -p "$HOME/.local/bin"
        mv pgmold "$HOME/.local/bin/"
        echo "$HOME/.local/bin" >> "$GITHUB_PATH"

    - name: Validate inputs
      shell: bash
      env:
        INPUT_DATABASE: ${{ inputs.database }}
        INPUT_BASELINE: ${{ inputs.baseline }}
        INPUT_DRIFT_CHECK: ${{ inputs.drift-check }}
        INPUT_SECRET_CMD: ${{ inputs.database-secret-command }}
      run: |
        if [[ -z "$INPUT_DATABASE" && -z "$INPUT_BASELINE" && -z "$INPUT_SECRET_CMD" ]]; then
          echo "::error::One of 'database', 'baseline', or 'database-secret-command' must be provided."
          exit 1
        fi

        if [[ "$INPUT_DRIFT_CHECK" == "true" && -z "$INPUT_DATABASE" && -z "$INPUT_SECRET_CMD" ]]; then
          echo "::warning::drift-check is enabled but no database connection was provided — skipping drift detection."
        fi

    - name: Start tunnel
      shell: bash
      id: tunnel
      if: ${{ inputs.tunnel-command != '' }}
      env:
        TUNNEL_COMMAND: ${{ inputs.tunnel-command }}
      run: |
        echo "Starting tunnel..."
        eval "$TUNNEL_COMMAND" &
        TUNNEL_PID=$!
        echo "pid=$TUNNEL_PID" >> "$GITHUB_OUTPUT"
        echo "Tunnel started (PID: $TUNNEL_PID)"

    - name: Wait for tunnel
      shell: bash
      if: ${{ steps.tunnel.outputs.pid != '' }}
      env:
        TUNNEL_PORT: ${{ inputs.tunnel-port }}
        TUNNEL_TIMEOUT: ${{ inputs.tunnel-ready-timeout }}
        TUNNEL_PID: ${{ steps.tunnel.outputs.pid }}
      run: |
        if ! [[ "$TUNNEL_TIMEOUT" =~ ^[0-9]+$ ]]; then
          echo "::error::tunnel-ready-timeout must be a positive integer (got: '$TUNNEL_TIMEOUT')"
          exit 1
        fi

        ELAPSED=0
        echo "Waiting for tunnel on localhost:${TUNNEL_PORT}..."
        while ! (echo >/dev/tcp/localhost/"$TUNNEL_PORT") 2>/dev/null; do
          if ! kill -0 "$TUNNEL_PID" 2>/dev/null; then
            echo "::error::Tunnel process (PID $TUNNEL_PID) exited before becoming ready"
            exit 1
          fi
          if [[ "$ELAPSED" -ge "$TUNNEL_TIMEOUT" ]]; then
            echo "::error::Tunnel not ready after ${TUNNEL_TIMEOUT}s on localhost:${TUNNEL_PORT}"
            kill "$TUNNEL_PID" 2>/dev/null || true
            exit 1
          fi
          sleep 1
          ELAPSED=$((ELAPSED + 1))
        done
        echo "Tunnel ready on localhost:${TUNNEL_PORT} (${ELAPSED}s)"

    - name: Resolve database connection
      shell: bash
      id: resolve-database
      if: ${{ inputs.database-secret-command != '' }}
      env:
        SECRET_COMMAND: ${{ inputs.database-secret-command }}
      run: |
        echo "Resolving database connection string..."
        DATABASE=$(eval "$SECRET_COMMAND" | tr -d '\r')

        if [[ -z "$DATABASE" ]]; then
          echo "::error::database-secret-command produced empty output"
          exit 1
        fi

        echo "::add-mask::$DATABASE"

        if [[ "$DATABASE" != db:* ]]; then
          echo "::error::database-secret-command output must start with 'db:'"
          exit 1
        fi

        echo "RESOLVED_DATABASE=$DATABASE" >> "$GITHUB_ENV"
        echo "resolved=true" >> "$GITHUB_OUTPUT"

    - name: Run migration plan
      shell: bash
      id: plan
      env:
        INPUT_SCHEMA: ${{ inputs.schema }}
        INPUT_DATABASE: ${{ env.RESOLVED_DATABASE || inputs.database }}
        INPUT_BASELINE: ${{ inputs.baseline }}
        INPUT_TARGET_SCHEMAS: ${{ inputs.target-schemas }}
        INPUT_PLAN_ARGS: ${{ inputs.plan-args }}
      run: |
        set -f
        SCHEMA_ARGS=()
        for source in $INPUT_SCHEMA; do
          SCHEMA_ARGS+=(--schema "$source")
        done
        set +f

        if [[ -n "$INPUT_DATABASE" ]]; then
          echo "Running migration plan against database..."
          eval "OUTPUT=\$(pgmold plan \"\${SCHEMA_ARGS[@]}\" \
            --database \"\$INPUT_DATABASE\" \
            --target-schemas \"\$INPUT_TARGET_SCHEMAS\" \
            $INPUT_PLAN_ARGS \
            --json)"
        else
          echo "Running SQL-to-SQL diff (baseline mode)..."
          SCHEMA_FIRST="${INPUT_SCHEMA%% *}"
          if [[ "$INPUT_SCHEMA" != "$SCHEMA_FIRST" ]]; then
            echo "::warning::baseline mode only supports a single schema source; ignoring all but '$SCHEMA_FIRST'"
          fi
          OUTPUT=$(pgmold diff \
            --from "$INPUT_BASELINE" \
            --to "$SCHEMA_FIRST" \
            --json)
        fi

        STATEMENT_COUNT=$(echo "$OUTPUT" | jq -r '.statement_count // 0')
        HAS_DESTRUCTIVE=$(echo "$OUTPUT" | jq -r '[.operations[] | select(startswith("Drop"))] | length > 0')

        echo "statement-count=$STATEMENT_COUNT" >> "$GITHUB_OUTPUT"
        echo "has-destructive=$HAS_DESTRUCTIVE" >> "$GITHUB_OUTPUT"

        PLAN_FILE="$RUNNER_TEMP/pgmold-plan.json"
        echo "$OUTPUT" > "$PLAN_FILE"
        echo "plan-json-file=$PLAN_FILE" >> "$GITHUB_OUTPUT"

        MAX_OUTPUT_CHARS=48000
        if [[ ${#OUTPUT} -gt $MAX_OUTPUT_CHARS ]]; then
          echo "::warning::plan-json output truncated (${#OUTPUT} chars exceeds $MAX_OUTPUT_CHARS limit); use plan-json-file output instead"
          PLAN_FOR_OUTPUT="${OUTPUT:0:$MAX_OUTPUT_CHARS}"
        else
          PLAN_FOR_OUTPUT="$OUTPUT"
        fi

        DELIMITER="pgmold_$(openssl rand -hex 8)"
        {
          echo "plan-json<<$DELIMITER"
          echo "$PLAN_FOR_OUTPUT"
          echo "$DELIMITER"
        } >> "$GITHUB_OUTPUT"

    - name: Run drift detection
      shell: bash
      id: drift
      if: ${{ inputs.drift-check == 'true' && (inputs.database != '' || steps.resolve-database.outputs.resolved == 'true') }}
      env:
        INPUT_SCHEMA: ${{ inputs.schema }}
        INPUT_DATABASE: ${{ env.RESOLVED_DATABASE || inputs.database }}
        INPUT_TARGET_SCHEMAS: ${{ inputs.target-schemas }}
        INPUT_FAIL_ON_DRIFT: ${{ inputs.fail-on-drift }}
      run: |
        set -f
        SCHEMA_ARGS=()
        for source in $INPUT_SCHEMA; do
          SCHEMA_ARGS+=(--schema "$source")
        done
        set +f

        echo "Running drift detection..."
        set +e
        OUTPUT=$(pgmold drift "${SCHEMA_ARGS[@]}" \
          --database "$INPUT_DATABASE" \
          --target-schemas "$INPUT_TARGET_SCHEMAS" \
          --json 2>&1)
        DRIFT_EXIT=$?
        set -e

        if ! echo "$OUTPUT" | jq empty 2>/dev/null; then
          echo "::error::pgmold drift produced non-JSON output (exit $DRIFT_EXIT):"
          echo "$OUTPUT"
          exit 1
        fi

        HAS_DRIFT=$(echo "$OUTPUT" | jq -r '.has_drift')
        EXPECTED_FP=$(echo "$OUTPUT" | jq -r '.expected_fingerprint')
        ACTUAL_FP=$(echo "$OUTPUT" | jq -r '.actual_fingerprint')

        echo "has-drift=$HAS_DRIFT" >> "$GITHUB_OUTPUT"
        echo "expected-fingerprint=$EXPECTED_FP" >> "$GITHUB_OUTPUT"
        echo "actual-fingerprint=$ACTUAL_FP" >> "$GITHUB_OUTPUT"

        REPORT_FILE="$RUNNER_TEMP/pgmold-drift-report.json"
        echo "$OUTPUT" > "$REPORT_FILE"
        echo "report-file=$REPORT_FILE" >> "$GITHUB_OUTPUT"

        MAX_OUTPUT_CHARS=48000
        if [[ ${#OUTPUT} -gt $MAX_OUTPUT_CHARS ]]; then
          echo "::warning::drift report output truncated (${#OUTPUT} chars exceeds $MAX_OUTPUT_CHARS limit); use report-file output instead"
          REPORT_FOR_OUTPUT="${OUTPUT:0:$MAX_OUTPUT_CHARS}"
        else
          REPORT_FOR_OUTPUT="$OUTPUT"
        fi

        DELIMITER="pgmold_$(openssl rand -hex 8)"
        {
          echo "report<<$DELIMITER"
          echo "$REPORT_FOR_OUTPUT"
          echo "$DELIMITER"
        } >> "$GITHUB_OUTPUT"

        if [[ "$DRIFT_EXIT" -ne 0 && "$HAS_DRIFT" != "true" ]]; then
          echo "::error::pgmold drift failed with exit code $DRIFT_EXIT"
          exit "$DRIFT_EXIT"
        fi

        if [[ "$HAS_DRIFT" == "true" && "$INPUT_FAIL_ON_DRIFT" == "true" ]]; then
          DIFF_COUNT=$(echo "$OUTPUT" | jq '.differences | length')
          echo "::error::Schema drift detected! Expected: $EXPECTED_FP, Actual: $ACTUAL_FP ($DIFF_COUNT differences)"
          exit 1
        fi

    - name: Emit destructive operation warnings
      shell: bash
      if: ${{ steps.plan.outputs.has-destructive == 'true' }}
      env:
        PLAN_FILE: ${{ steps.plan.outputs.plan-json-file }}
      run: |
        jq -r '.operations[] | select(startswith("Drop"))' "$PLAN_FILE" | while IFS= read -r statement; do
          echo "::warning::Destructive operation: $statement"
        done

    - name: Post PR comment
      shell: bash
      id: comment
      if: ${{ inputs.plan-comment == 'true' && (github.event_name == 'pull_request' || inputs.pr-number != '') }}
      env:
        GH_TOKEN: ${{ inputs.github-token }}
        PLAN_FILE: ${{ steps.plan.outputs.plan-json-file }}
        STATEMENT_COUNT: ${{ steps.plan.outputs.statement-count }}
        HAS_DESTRUCTIVE: ${{ steps.plan.outputs.has-destructive }}
        HAS_DRIFT: ${{ steps.drift.outputs.has-drift }}
        DRIFT_FILE: ${{ steps.drift.outputs.report-file }}
        REPO: ${{ github.repository }}
        PR_NUMBER: ${{ inputs.pr-number || github.event.pull_request.number }}
      run: |
        emit_drift_status() {
          if [[ -z "$HAS_DRIFT" ]]; then return; fi
          echo ""
          echo "### Drift Status"
          if [[ "$HAS_DRIFT" == "true" ]]; then
            if [[ ! -f "$DRIFT_FILE" ]]; then
              echo "Drift detected (report file unavailable)"
              return
            fi
            local diff_count
            diff_count=$(jq '.differences | length' "$DRIFT_FILE")
            echo "Drift detected ($diff_count differences)"
          else
            echo "No drift detected"
          fi
        }

        emit_footer() {
          echo ""
          echo "---"
          echo "*Generated by [pgmold](https://github.com/fmguerreiro/pgmold)*"
        }

        build_comment() {
          local stmt_count="$STATEMENT_COUNT"

          echo "<!-- pgmold-ci-comment -->"
          echo "## pgmold Migration Plan"
          echo ""

          if [[ "$stmt_count" == "0" || -z "$stmt_count" ]]; then
            echo "No schema changes detected."
            emit_drift_status
            emit_footer
            return
          fi

          if [[ ! -f "$PLAN_FILE" ]]; then
            echo "Plan output unavailable (may have been truncated)."
            emit_footer
            return
          fi

          local dest_count
          dest_count=$(jq -r '[.operations[] | select(startswith("Drop"))] | length' "$PLAN_FILE")

          local dest_display="$dest_count"
          if [[ "$dest_count" -gt 0 ]]; then
            dest_display="**$dest_count**"
          fi

          echo "### Summary"
          echo "| Metric | Value |"
          echo "|--------|-------|"
          echo "| Statements | $stmt_count |"
          echo "| Destructive operations | $dest_display |"

          if [[ "$HAS_DESTRUCTIVE" == "true" ]]; then
            echo ""
            echo "### Destructive Operations"
            echo "> **Warning:** This migration contains destructive operations that require \`--allow-destructive\`."
            jq -r '.operations[] | select(startswith("Drop"))' "$PLAN_FILE" | while IFS= read -r op; do
              echo "- \`$op\`"
            done
          fi

          local lock_warnings
          lock_warnings=$(jq -r '.lock_warnings // [] | .[]' "$PLAN_FILE" 2>/dev/null)
          if [[ -n "$lock_warnings" ]]; then
            echo ""
            echo "### Lock Warnings"
            echo "$lock_warnings" | while IFS= read -r warning; do
              echo "- $warning"
            done
          fi

          echo ""
          echo "### Migration SQL"
          echo "<details><summary>Click to expand ($stmt_count statements)</summary>"
          echo ""
          echo '```sql'
          jq -r '.statements // [] | .[]' "$PLAN_FILE"
          echo '```'
          echo ""
          echo "</details>"

          emit_drift_status
          emit_footer
        }

        COMMENT_BODY=$(build_comment)

        EXISTING_COMMENT_ID=$(gh api "repos/$REPO/issues/$PR_NUMBER/comments" \
          --jq '.[] | select(.body | startswith("<!-- pgmold-ci-comment -->")) | .id' \
          | head -n1)

        PAYLOAD=$(jq -n --arg body "$COMMENT_BODY" '{body: $body}')

        if [[ -n "$EXISTING_COMMENT_ID" ]]; then
          echo "$PAYLOAD" | gh api "repos/$REPO/issues/comments/$EXISTING_COMMENT_ID" \
            -X PATCH --input -
          echo "comment-id=$EXISTING_COMMENT_ID" >> "$GITHUB_OUTPUT"
        else
          CREATED=$(echo "$PAYLOAD" | gh api "repos/$REPO/issues/$PR_NUMBER/comments" \
            -X POST --input -)
          COMMENT_ID=$(echo "$CREATED" | jq -r '.id')
          echo "comment-id=$COMMENT_ID" >> "$GITHUB_OUTPUT"
        fi

    - name: Auto-label PR
      shell: bash
      if: ${{ inputs.auto-label == 'true' && (github.event_name == 'pull_request' || inputs.pr-number != '') }}
      env:
        GH_TOKEN: ${{ inputs.github-token }}
        STATEMENT_COUNT: ${{ steps.plan.outputs.statement-count }}
        REPO: ${{ github.repository }}
        PR_NUMBER: ${{ inputs.pr-number || github.event.pull_request.number }}
      run: |
        LABEL="database-schema"
        LABEL_COLOR="5319e7"

        if [[ "$STATEMENT_COUNT" -gt 0 ]]; then
          gh api "repos/$REPO/labels" \
            -X POST \
            -f name="$LABEL" \
            -f color="$LABEL_COLOR" \
            -f description="Pull request modifies database schema" 2>/dev/null || true
          gh api "repos/$REPO/issues/$PR_NUMBER/labels" \
            -X POST \
            -f "labels[]=$LABEL"
        else
          gh api "repos/$REPO/issues/$PR_NUMBER/labels/$LABEL" -X DELETE &>/dev/null || true
        fi

    - name: Cleanup tunnel
      shell: bash
      if: ${{ always() }}
      env:
        TUNNEL_PID: ${{ steps.tunnel.outputs.pid }}
      run: |
        if [[ -n "$TUNNEL_PID" ]] && kill -0 "$TUNNEL_PID" 2>/dev/null; then
          kill "$TUNNEL_PID" 2>/dev/null || true
          echo "Tunnel process (PID $TUNNEL_PID) terminated"
        fi

branding:
  icon: 'database'
  color: 'blue'
