name: Stage 5 day 1 investigation — round 3 (schema + full rows)

# Round 3: реальные имена колонок hash в audit-таблицах,
# реальные имена FK в supplier_projects/supplier_leads,
# полное содержимое битых строк (599/462) и застрявших лидов (1110/1157).

on:
  workflow_dispatch:

jobs:
  investigate:
    runs-on: ubuntu-latest
    timeout-minutes: 10

    env:
      LIDERRA_HOST: 111.88.246.137
      LIDERRA_USER: ubuntu

    steps:
      - name: Setup SSH key
        run: |
          mkdir -p ~/.ssh
          echo "${{ secrets.LIDERRA_SSH_KEY }}" > ~/.ssh/liderra_deploy
          chmod 600 ~/.ssh/liderra_deploy
          ssh-keyscan -H ${{ env.LIDERRA_HOST }} >> ~/.ssh/known_hosts 2>/dev/null

      - name: Round 3 schema + rows
        run: |
          ssh -i ~/.ssh/liderra_deploy ${{ env.LIDERRA_USER }}@${{ env.LIDERRA_HOST }} 'bash -s' <<'REMOTE' | tee /tmp/investigate3.log
            set +e
            cd /var/www/liderra/app

            echo "=========================================="
            echo "SCHEMAS"
            echo "=========================================="

            echo
            echo "--- activity_log columns ---"
            sudo -u postgres psql -d liderra -c "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='activity_log' ORDER BY ordinal_position;"

            echo
            echo "--- balance_transactions columns ---"
            sudo -u postgres psql -d liderra -c "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='balance_transactions' ORDER BY ordinal_position;"

            echo
            echo "--- supplier_projects columns ---"
            sudo -u postgres psql -d liderra -c "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='supplier_projects' ORDER BY ordinal_position;"

            echo
            echo "--- supplier_leads columns ---"
            sudo -u postgres psql -d liderra -c "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='supplier_leads' ORDER BY ordinal_position;"


            echo
            echo "=========================================="
            echo "BROKEN ROWS — full SELECT *"
            echo "=========================================="

            echo
            echo "--- activity_log_y2026_m05 ids 597-601 ---"
            sudo -u postgres psql -d liderra -x -c "SELECT * FROM activity_log_y2026_m05 WHERE id BETWEEN 597 AND 601 ORDER BY id;"

            echo
            echo "--- balance_transactions_y2026_m05 ids 460-464 ---"
            sudo -u postgres psql -d liderra -x -c "SELECT * FROM balance_transactions_y2026_m05 WHERE id BETWEEN 460 AND 464 ORDER BY id;"


            echo
            echo "=========================================="
            echo "STUCK LEADS 1110 + 1157"
            echo "=========================================="

            echo
            echo "--- supplier_leads.id IN (1110, 1157) ---"
            sudo -u postgres psql -d liderra -x -c "SELECT * FROM supplier_leads WHERE id IN (1110, 1157);"

            echo
            echo "--- failed_webhook_jobs sample raw_payload for sl_id=1110 (1 row) ---"
            sudo -u postgres psql -d liderra -x -c "SELECT * FROM failed_webhook_jobs WHERE raw_payload->>'supplier_lead_id' = '1110' ORDER BY failed_at DESC LIMIT 1;"

            echo
            echo "--- All supplier_projects with platform B1 ---"
            sudo -u postgres psql -d liderra -c "SELECT * FROM supplier_projects WHERE platform='B1' LIMIT 5;"

            echo
            echo "=========================================="
            echo "DONE"
            echo "=========================================="
          REMOTE

      - name: Print summary
        if: always()
        run: |
          {
            echo "## Stage 5 day 1 investigation — round 3 schemas"
            echo
            echo '```'
            cat /tmp/investigate3.log 2>/dev/null || echo "(no output)"
            echo '```'
          } >> "$GITHUB_STEP_SUMMARY"

      - name: Upload artifact
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: investigate-day1-round3
          path: /tmp/investigate3.log

      - name: Cleanup SSH key
        if: always()
        run: rm -f ~/.ssh/liderra_deploy
