tests-unified.yml

name: Tests

on:
  push:
    # main: post-merge validation; dependabot/**: full-permission runs (secrets +
    # security-events) since dependabot pull_request events get a read-only token;
    # v* tags: release bundle builds
    branches: [main, 'dependabot/**']
    tags: ['v*']
  # Human PRs build the merge preview with main, catching semantic conflicts
  # (e.g. #262+#265) that head-only builds miss
  pull_request:

env:
  DOTNET_NOLOGO: 1
  DOTNET_SKIP_FIRST_TIME_EXPERIENCE: true
  DOTNET_CLI_TELEMETRY_OPTOUT: 1

jobs:
  # Windows UI Tests - Both Database and File System modes on single VM
  tests_windows_ui:
    name: Windows UI Tests
    # Dependabot PRs are validated by the push run on their branch instead
    if: github.event_name != 'pull_request' || github.actor != 'dependabot[bot]'
    runs-on: windows-latest
    permissions:
      actions: read
      contents: read
      security-events: write
    env:
      RDMP_KEY_LOCATION: D:\a\RDMP\RDMP\key\private-key.xml
    steps:
      - name: Checkout code
        uses: actions/checkout@v7
        with:
          submodules: true

      - name: Setup .NET
        uses: actions/setup-dotnet@v6
        with:
          cache: true
          cache-dependency-path: Directory.Packages.props

      - name: Initialize CodeQL
        uses: github/codeql-action/init@v4
        with:
          languages: csharp
          config-file: ./.github/configs/codeql.yml
          queries: +security-and-quality

      - name: Populate Databases.yaml
        shell: bash
        run: |
          cat > ./Tools/rdmp/Databases.yaml << EOF
          CatalogueConnectionString: Server=(localdb)\MSSQLLocalDB;Database=TEST_Catalogue;Trusted_Connection=True;TrustServerCertificate=true;Connection Timeout=180;
          DataExportConnectionString: Server=(localdb)\MSSQLLocalDB;Database=TEST_DataExport;Trusted_Connection=True;TrustServerCertificate=true;Connection Timeout=180;
          EOF

      - name: Set RSA Key
        shell: powershell
        run: |
          $SavePath="D:\a\RDMP\RDMP\key"
          New-Item -ItemType Directory -Force -Path $SavePath  | Out-Null

          # Create XML using PowerShell XML object to avoid escaping issues
          $xml = New-Object xml
          $decl = $xml.CreateXmlDeclaration("1.0", "utf-16", $null)
          $xml.AppendChild($decl) | Out-Null

          $root = $xml.CreateElement("RSAParameters")
          $root.SetAttribute("xmlns:xsd", "http://www.w3.org/2001/XMLSchema")
          $root.SetAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance")
          $xml.AppendChild($root) | Out-Null

          $modulus = $xml.CreateElement("Modulus")
          $modulus.InnerText = "5CBYA/4GKu57eepMM5dHguZR6QxPakujvUPq81YQlAs9XdAS5OugT9xYATXV0ZUVeQrtCOj1jjS6cSnekJKzXMD48H2IbT+ImRVqyjE19dgeeZtK1cGa8wDKTdNtjo+ur2iaMzItE3VChcMidWncQpiieieSUwQ81uoab7foVdaQm078TzlHLaWiSyAPCOOIeeO2q7HEjVPkbiqCGl2Lkrzvzct84SDMFkyQXzXVJCfdlFn5bX3/8OwC9gWVICPBVbVFZZQ3skUKFqK/aYcgJL/svDyhFsj89TK3xzz8YE1r8VwxVtvqLfRXrWqUCV1n2vEm4XUjuTwQi2nwclREuQ=="
          $root.AppendChild($modulus) | Out-Null

          $exponent = $xml.CreateElement("Exponent")
          $exponent.InnerText = "AQAB"
          $root.AppendChild($exponent) | Out-Null

          $p = $xml.CreateElement("P")
          $p.InnerText = "+zfBbR8e3gLc5TDOSoyjclKdPyl62BZvp0kdlmihI5rJ/Bk+CnymYr22vBbWe/wJugWL4bLEAMWiWsa0ri0mJig24aJZ+DMEJDh+wz4J70OBbsE7jydw9whkt6r/8dYCwE2L4aKlL5pUOL/DzgQJ6vxkQ3UAjYMvEzBPapd7Z7c="
          $root.AppendChild($p) | Out-Null

          $q = $xml.CreateElement("Q")
          $q.InnerText = "6HgO3YVz2umROVrHkQ0xa+4a+EMZVAEBhS+ZuJ5KhXBomxYptfAud0WmGN1zOM7TpBYnk8IBx2kuBKoAavFtjbjsoINVxlLlUvUDEJmeElVz5TqRRCNEChY0sfDlR9gVIopB/p7BU5SFRz5i7+qpsWzWdBU/BR93K2vJNsj2Vw8="
          $root.AppendChild($q) | Out-Null

          $dp = $xml.CreateElement("DP")
          $dp.InnerText = "ecv4bY1vC7hbnIrjGWXCQMUpE9xqgKWwEGz0eV3U8kwzrZQXbkIs8SaFl/+Cka4KkTPrM8vWF4G6S0SXiPK+0jUhFpf+AsXJNj5lxwcnDeeusyHgXHGE5WAeZKX1XSyjPNTcAtM2PzQVrUXcCuAOZu1jNwlc8T8u7aC4gDddT1U="
          $root.AppendChild($dp) | Out-Null

          $dq = $xml.CreateElement("DQ")
          $dq.InnerText = "KNlP42Ub4o/AUQ++maJz2L9SReWkgbpbhgfDP0mxVplWCEpwseOuho7ajOv83zKYxfCOq8wfe+bjizZENIaP9aNVES+C1wKiAV3EWBpmSFpzrwgHlq2LuyoDwHDQGTvDGvqodhF3bzRd5xLzV60ofGDfni5NkJzi1+JszQ+rGck="
          $root.AppendChild($dq) | Out-Null

          $invQ = $xml.CreateElement("InverseQ")
          $invQ.InnerText = "BC4M2rtw/lHKW8gDVcQSB1a1yWlgtLqtoX+krelqO59/6Np2ApsPc43SUoy4PY1f+Oxf+Erik1NM1+TRucVBGB8AP1q0SFuTsmWiLE9zv/1zjeJJLOoPbpGia+bQ/r7fP+ZhBK8ldae7FAOctcoSfQ0jAn2IBpDyvlAlcnwRvwQ="
          $root.AppendChild($invQ) | Out-Null

          $d = $xml.CreateElement("D")
          $d.InnerText = "q1e/w//gEg7dn0xjv7w4chEcJLaiT2xQp6+DoRFbklZ+2R+XkWmJF3KghwgweSJI5olWUALprM3d23FfQaduIJSwZbFj7upxZsm3U/ZyWRzihuQk6ThpcWt+h8Xt283/nrAqYZmmUZ8ZP+64ywef8EVEhAuE0+Wy7JkZEiBH2W/MEXUvbMV8w282/X6H8zpIkHgjMvy/rouDMFA+ZLR9OOCofw7aVV9VivOVCVIhWe+inrQzG3UCLEEmKNOy0FmqQYvZ4vtwJ+kAByo6xW2YO9cHtEJFiKrZ9O2A0P0xtziOqStDq6JqoeE/bty8y3oM3HPyXMZXG2ecLuwbP4usoQ=="
          $root.AppendChild($d) | Out-Null

          # Save as UTF-16 Little Endian
          $settings = New-Object System.Xml.XmlWriterSettings
          $settings.Encoding = [System.Text.Encoding]::Unicode
          $writer = [System.Xml.XmlWriter]::Create("$SavePath\private-key.xml", $settings)
          $xml.Save($writer)
          $writer.Close()

      - name: Install MS SQL LocalDB
        uses: crazy-max/ghaction-chocolatey@v4
        with:
          args: install -r sqllocaldb --no-progress

      - name: Initialise LocalDB
        shell: bash
        run: |
          SqlLocalDB.exe create MSSQLLocalDB -s
          sqlcmd -l 180 -S '(localdb)\MSSQLLocalDB' -Q "SELECT @@VERSION;"
          # Configure TestDatabases.txt for LocalDB (Windows Auth, no Username/Password)
          sed -i'' -e 's/localhost/\(localdb\)\\MSSQLLocalDB/' Tests.Common/TestDatabases.txt
          sed -i'' -e 's/^Username:/#Username:/' Tests.Common/TestDatabases.txt
          sed -i'' -e 's/^Password:/#Password:/' Tests.Common/TestDatabases.txt

      - name: Setup MySQL
        uses: shogo82148/actions-setup-mysql@v1
        with:
          mysql-version: '8.0'
          root-password: 'YourStrong!Passw0rd'
          auto-start: true

      - name: Wait for databases
        shell: bash
        run: |
          # Wait for LocalDB
          for i in {1..10}; do
            if sqlcmd -l 60 -S '(localdb)\MSSQLLocalDB' -Q "SELECT 1" > /dev/null 2>&1; then
              echo "LocalDB is ready!"
              break
            fi
            echo "Attempt $i: LocalDB not ready..."
            sleep 3
          done

          # Wait for MySQL
          for i in {1..30}; do
            if mysqladmin ping -h127.0.0.1 -uroot -p'YourStrong!Passw0rd' --silent; then
              echo "MySQL is ready!"
              break
            fi
            echo "Attempt $i: MySQL not ready..."
            sleep 2
          done

      - name: Build
        run: dotnet build --configuration Release --verbosity minimal

      - name: Initialise RDMP (Database)
        run: |
          dotnet run -c Release --no-build --project Tools/rdmp/rdmp.csproj -- install --createdatabasetimeout 180 --collation "Latin1_General_CS_AI" "(localdb)\MSSQLLocalDB" TEST_ -e

      - name: Run integration test scripts
        timeout-minutes: 10
        shell: bash
        run: |
          {
            echo "Commands:"
            echo "  - createnewexternaldatabaseserver LiveLoggingServer_ID \"DatabaseType:MySQL:Server=127.0.0.1;Uid=root;Pwd=YourStrong!Passw0rd;Database=rdmp_logging2\""
            echo "  - createnewexternaldatabaseserver DQE \"DatabaseType:MySQL:Server=127.0.0.1;Uid=root;Pwd=YourStrong!Passw0rd;Database=rdmp_dqe\""
            echo "  - createnewexternaldatabaseserver CohortIdentificationQueryCachingServer_ID \"DatabaseType:MySQL:Server=127.0.0.1;Uid=root;Pwd=YourStrong!Passw0rd;Database=rdmp_cache\""
          } > /tmp/db.yaml
          dotnet run -c Release --no-build --project Tools/rdmp/rdmp.csproj -- -f /tmp/db.yaml --dir ~/rdmp/rdmp-yaml/

          # Create combined YAML with external database setup first
          {
            echo "Commands:"
            # Append integration test scripts (skip the Commands header)
            sed '1,/^Commands:/d' scripts/create_list_destroy_catalogue.yaml
            sed '1,/^Commands:/d' scripts/create_cohort.yaml
            sed '1,/^Commands:/d' scripts/create_dataload.yaml
            sed '1,/^Commands:/d' scripts/orphan_extractable_column.yaml
          } > /tmp/combined-tests.yaml

          # Run all commands in a single invocation
          dotnet run -c Release --no-build --project Tools/rdmp/rdmp.csproj -- -f /tmp/combined-tests.yaml

      - name: Copy TestDatabases.txt to UI test output
        shell: bash
        run: |
          cp Tests.Common/TestDatabases.txt Rdmp.UI.Tests/bin/Release/net10.0-windows/

      - name: Test UI (Database Mode)
        timeout-minutes: 45
        shell: bash
        run: |
          dotnet test Rdmp.UI.Tests/Rdmp.UI.Tests.csproj --no-build -c Release

      - name: Re-initialise RDMP (File System)
        shell: bash
        run: |
          echo "UseFileSystemRepo: true" >> Tests.Common/TestDatabases.txt
          cp Tests.Common/TestDatabases.txt Rdmp.UI.Tests/bin/Release/net10.0-windows/

      - name: Test UI (File System Mode)
        timeout-minutes: 30
        shell: bash
        run: |
          dotnet test Rdmp.UI.Tests/Rdmp.UI.Tests.csproj --no-build -c Release

      - name: Perform CodeQL Analysis
        uses: github/codeql-action/analyze@v4
        with:
          category: "/language:csharp"
          upload: false
          output: sarif-results

      - name: Filter SARIF for generated files
        uses: advanced-security/filter-sarif@v1
        with:
          patterns: |
            -**/*.Designer.cs
            -**/*.g.cs
            -**/*Generated.cs
            -**/*.g.i.cs
            -**/*.designer.cs
            -**/obj/**
            -**/bin/**
            -**/externals/**
          input: sarif-results/csharp.sarif
          output: sarif-results/csharp.sarif

      - name: Upload CodeQL Results
        uses: github/codeql-action/upload-sarif@v4
        with:
          sarif_file: sarif-results/csharp.sarif
          category: "/language:csharp"

      - name: Run markdown link check
        continue-on-error: true
        shell: bash
        run: |
          npx markdown-link-check --quiet README.md Documentation/**/*.md || true


  # Linux Database Tests
  tests_linux_db:
    name: Linux Database Tests
    # Dependabot PRs are validated by the push run on their branch instead
    if: github.event_name != 'pull_request' || github.actor != 'dependabot[bot]'
    runs-on: ubuntu-22.04
    env:
      # RDMP_KEY_LOCATION not set - will use built-in fallback key
      MSSQL_SA_PASSWORD: "YourStrong!Passw0rd"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "developer"
    steps:
      - name: Checkout code
        uses: actions/checkout@v7
        with:
          submodules: true

      - name: Setup .NET
        uses: actions/setup-dotnet@v6
        with:
          cache: true
          cache-dependency-path: Directory.Packages.props

      - name: Add Microsoft repository
        run: |
          curl -sL https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
          sudo add-apt-repository "deb [arch=amd64] https://packages.microsoft.com/ubuntu/22.04/mssql-server-2022 jammy main"

      - name: Install database packages
        uses: awalsh128/cache-apt-pkgs-action@v1.6.3
        with:
          packages: libeatmydata1 apt-transport-https curl mysql-server libmysqlclient-dev mssql-tools mssql-server alien libaio1
          version: 1.0
          execute_install_scripts: true

      - name: Configure and start databases
        continue-on-error: true
        run: |
          # Configure SQL Server for performance
          sudo -E /opt/mssql/bin/mssql-conf -n setup accept-eula

          # Performance optimizations (only supported settings)
          sudo -E /opt/mssql/bin/mssql-conf set sqlagent.enabled false  # Disable SQL Agent
          # Note: telemetry and memory settings not supported on this version

          # Create performance tuning configuration
          sudo mkdir -p /var/opt/mssql/data
          sudo chown mssql:mssql /var/opt/mssql/data

          # Start databases (PostgreSQL disabled - tests are commented out)
          sudo systemctl start mysql.service
          sudo systemctl start mssql-server

          echo "Waiting for MySQL..."
          for i in {1..30}; do
            mysqladmin ping -h127.0.0.1 --silent && echo "MySQL is ready!" && break
            sleep 2
          done

          echo "Waiting for SQL Server..."
          for i in {1..30}; do
            if /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -C -Q "SELECT 1" &>/dev/null; then
              echo "SQL Server is ready!"
              break
            fi
            sleep 2
          done

          # Oracle disabled - no wait needed

          # Apply SQL Server performance settings after startup
          /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -C -Q "
            -- Set traceflags for performance (disable ForceFlush)
            DBCC TRACEON(3422, -1) WITH NO_INFOMSGS;

            -- Set simple recovery mode for model database
            ALTER MODEL SET RECOVERY SIMPLE;
          " || echo "SQL Server performance tuning completed with some errors"

          # Restart SQL Server to apply traceflag settings (required for traceflags)
          echo "Restarting SQL Server to apply traceflags..."
          sudo systemctl restart mssql-server

          # Wait for SQL Server to restart
          echo "Waiting for SQL Server restart..."
          for i in {1..60}; do
            if /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -C -Q "SELECT 1" &>/dev/null; then
              echo "SQL Server restarted successfully!"
              break
            fi
            sleep 2
          done

      - name: Populate Databases.yaml and TestDatabases.txt
        run: |
          cat > ./Tools/rdmp/Databases.yaml << EOF
          CatalogueConnectionString: Server=localhost;Database=TEST_Catalogue;User Id=SA;Password=YourStrong!Passw0rd;TrustServerCertificate=true;Connection Timeout=180;
          DataExportConnectionString: Server=localhost;Database=TEST_DataExport;User Id=SA;Password=YourStrong!Passw0rd;TrustServerCertificate=true;Connection Timeout=180;
          EOF

          cat > ./Tests.Common/TestDatabases.txt << EOF
          Prefix: TEST_
          ServerName: localhost
          Username: SA
          Password: YourStrong!Passw0rd
          MySql: server=127.0.0.1;Uid=root;Pwd=YourStrong!Passw0rd;AllowPublicKeyRetrieval=True
          #PostgreSql: User ID=postgres;Password=pgpass4291;Host=127.0.0.1;Port=5432
          #Oracle: Data Source=localhost:1521/XEPDB1;User Id=system;Password=oracle
          EOF

      - name: Build
        run: dotnet build --configuration Release --verbosity minimal

      - name: Initialise RDMP
        run: |
          dotnet run -c Release --no-build --project Tools/rdmp/rdmp.csproj -- install --createdatabasetimeout 180 --collation "Latin1_General_CS_AI" -u SA -p $MSSQL_SA_PASSWORD localhost TEST_ -e

      - name: Setup MinIO
        run: |
          curl -sL "https://dl.min.io/server/minio/release/linux-amd64/minio" > minio
          chmod +x minio
          mkdir -p ./minio-data
          ./minio server ./minio-data --console-address :9001 &
          MINIO_PID=$!

          # Wait for MinIO
          for i in {1..30}; do
            if curl -s http://127.0.0.1:9000/minio/health/live > /dev/null; then
              echo "MinIO is ready!"
              break
            fi
            sleep 2
          done

          # Configure AWS credentials
          mkdir -p ~/.aws
          cat > ~/.aws/credentials << EOF
          [minio]
          aws_access_key_id=minioadmin
          aws_secret_access_key=minioadmin
          aws_endpoint_url=http://127.0.0.1:9001
          EOF

          echo $MINIO_PID > /tmp/minio.pid

      - name: Test non-UI
        timeout-minutes: 45
        env:
          AWS_ENDPOINT_URL: http://127.0.0.1:9000
          AWS_ACCESS_KEY_ID: minioadmin
          AWS_SECRET_ACCESS_KEY: minioadmin
        run: |
          dotnet test linuxtests.slnf \
            --nologo \
            --collect:"XPlat Code Coverage" \
            --verbosity minimal \
            --results-directory coverage \
            -- DataCollectionRunSettings.DataCollectors.DataCollector.Configuration.Format=lcov

      - name: Switch to File System Mode
        run: |
          echo "=== Switching to File System Mode ==="
          # Add filesystem flag to TestDatabases.txt - RDMP will auto-create YAML repo
          echo "UseFileSystemRepo: true" >> Tests.Common/TestDatabases.txt

      - name: Test Core (File System Mode)
        timeout-minutes: 45
        env:
          AWS_ENDPOINT_URL: http://127.0.0.1:9000
          AWS_ACCESS_KEY_ID: minioadmin
          AWS_SECRET_ACCESS_KEY: minioadmin
        run: |
          dotnet test linuxtests.slnf \
            --nologo \
            --collect:"XPlat Code Coverage" \
            --verbosity minimal \
            --results-directory coverage \
            -- DataCollectionRunSettings.DataCollectors.DataCollector.Configuration.Format=lcov

      - name: Upload coverage
        uses: codecov/codecov-action@v7
        with:
          token: ${{ secrets.CODECOV_TOKEN }}
          directory: ./coverage
          fail_ci_if_error: false
        continue-on-error: true

  # Bundle job - only runs on tagged releases
  bundle:
    name: Bundle Build
    runs-on: ubuntu-latest
    needs: [tests_windows_ui, tests_linux_db]
    if: startsWith(github.ref, 'refs/tags/v')
    permissions:
      actions: read
      contents: write
    steps:
      - name: Checkout code
        uses: actions/checkout@v7
        with:
          submodules: true

      - name: Setup .NET
        uses: actions/setup-dotnet@v6
        with:
          cache: true
          cache-dependency-path: Directory.Packages.props

      - name: Cache apt packages
        uses: awalsh128/cache-apt-pkgs-action@v1.6.3
        with:
          packages: pixz p7zip-full
          version: 1.0

      - name: Build and bundle
        id: version
        shell: bash
        run: ./.github/scripts/build-bundle.sh

      - name: Archive production artifacts
        uses: actions/upload-artifact@v7
        with:
          name: dist
          path: dist

      - name: Upload NuGet packages
        run: dotnet nuget push dist/HIC.RDMP.Plugin*.nupkg -s https://api.nuget.org/v3/index.json --skip-duplicate -k ${{ secrets.NUGET_KEY }}

      - name: Upload binaries to release
        uses: svenstaro/upload-release-action@2.11.5
        with:
          repo_token: ${{ secrets.GITHUB_TOKEN }}
          file: dist/*
          tag: ${{ github.ref }}
          overwrite: true
          file_glob: true