GitHub-Code-Validation-Checks.yml

name: GitHub - Pipeline Validation Workflow

on:
  pull_request:
    branches:
      - release
  schedule:
    - cron: "0 3 * * *"  # Runs at 3 AM UTC every night

  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:

jobs:
  setup-test-environment:
    name: Spin Up Test SQL Server
    runs-on: ubuntu-latest
    env:
      DB_IMAGE: "mcr.microsoft.com/mssql/server:2019-latest"
      DB_PORT: "1433"
      SA_PASSWORD: "Redg@te1"
    steps:
      - name: Checkout Code
        uses: actions/checkout@v4

      - name: Start SQL Server with Access to Backups & Scripts
        run: |
          docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=${{ env.SA_PASSWORD }}" \
            -p ${{ env.DB_PORT }}:1433 -d \
            -v ${{ github.workspace }}/backups:/var/opt/mssql/backups \
            -v ${{ github.workspace }}/Scripts:/var/opt/mssql/scripts \
            ${{ env.DB_IMAGE }} 

      - name: Enabled TCP/IP
        run: |
          CONTAINER_ID=$(docker ps -q --filter ancestor=${{ env.DB_IMAGE }})
          docker exec -i $CONTAINER_ID /bin/bash -c "
            /opt/mssql-tools18/bin/sqlcmd -S 'localhost,${{ env.DB_PORT }}' -U sa -P '${{ env.SA_PASSWORD }}' -I -C -v TrustServerCertificate=yes -Q \
              \"EXEC sp_configure 'remote access', 1; RECONFIGURE;\"
            "

      - name: Wait for SQL Server to be ready
        run: |
          echo "Waiting for SQL Server to be ready..."
          # Wait for the container to be fully up and running by checking the logs
          until docker logs $(docker ps -q --filter ancestor=mcr.microsoft.com/mssql/server:2019-latest) 2>&1 | grep -q "SQL Server is now ready for client connections"; do
            echo "Waiting for SQL Server to be ready..."
            sleep 5
          done
          echo "SQL Server is ready."

      - name: Execute SQL Script to Create Test Databases
        run: |
          CONTAINER_ID=$(docker ps -q --filter ancestor=${{ env.DB_IMAGE }})
          docker exec -i $CONTAINER_ID /bin/bash -c "
            /opt/mssql-tools18/bin/sqlcmd -S 'localhost,${{ env.DB_PORT }}' -U sa -P '${{ env.SA_PASSWORD }}' -i '/var/opt/mssql/scripts/CreateAutoPilotDatabases.sql' -I -C -v TrustServerCertificate=yes
          "

      - name: Verify Databases Were Created
        run: |
          docker exec -i $(docker ps -q --filter ancestor=${{ env.DB_IMAGE }}) /opt/mssql-tools18/bin/sqlcmd \
            -S localhost -U sa -P '${{ env.SA_PASSWORD }}' \
            -Q "SELECT name FROM sys.databases" -I -C -v TrustServerCertificate=yes

  Validate-Flyway-Pipeline:
    name: Run Flyway Pipeline
    needs: setup-test-environment
    uses: ./.github/workflows/GitHub-Flyway-CICD-Pipeline_Linux.yml
    # with:
    #   db_user: ${{ needs.setup-test-database.outputs.db_user }}
    #   db_password: ${{ needs.setup-test-database.outputs.db_password }}
    #   db_jdbc: ${{ needs.setup-test-database.outputs.db_jdbc }}
    secrets: inherit  # Inherit existing secrets but allow environment overrides