name: Continuous Integration

on:
  push:
    branches: [ main, develop ]
  pull_request:
    branches: [ main, develop ]

env:
  PYTHON_VERSION: '3.11'

jobs:
  test-database-migrations:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        db_type: [postgresql, sqlite]
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_PASSWORD: test_password
          POSTGRES_USER: test_user
          POSTGRES_DB: test_db
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}
          cache: 'pip'

      - name: Install dependencies
        run: pip install -r requirements.txt

      - name: Test PostgreSQL migrations
        if: matrix.db_type == 'postgresql'
        env:
          DATABASE_URL: postgresql://test_user:test_password@localhost:5432/test_db
          FLASK_APP: app.py
          FLASK_ENV: testing
        run: |
          echo "Testing PostgreSQL migrations..."
          flask db upgrade
          python -c "from app import create_app, db; app = create_app(); app.app_context().push(); print('PostgreSQL migration successful')"
          flask db downgrade base
          flask db upgrade
          echo "PostgreSQL migration rollback/upgrade test passed"

      - name: Test SQLite migrations
        if: matrix.db_type == 'sqlite'
        env:
          DATABASE_URL: sqlite:///test.db
          FLASK_APP: app.py
          FLASK_ENV: testing
        run: |
          echo "Testing SQLite migrations..."
          flask db upgrade
          python -c "from app import create_app, db; app = create_app(); app.app_context().push(); print('SQLite migration successful')"
          flask db downgrade base
          flask db upgrade
          echo "SQLite migration rollback/upgrade test passed"

  test-docker-build:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@v3

      - name: Test Docker build
        run: |
          docker build -t timetracker-test:latest .
          echo "Docker build successful"

      - name: Test Docker container startup
        run: |
          # Start container in background
          docker run -d --name test-container -p 8080:8080 \
            -e DATABASE_URL="sqlite:///test.db" \
            timetracker-test:latest

          # Wait for container to be ready
          for i in {1..30}; do
            if curl -f http://localhost:8080/_health >/dev/null 2>&1; then
              echo "Container health check passed"
              break
            fi
            echo "Waiting for container to be ready... ($i/30)"
            sleep 2
          done

          # Show container logs for debugging
          docker logs test-container

          # Stop container
          docker stop test-container
          docker rm test-container

  security-scan:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}
          cache: 'pip'

      - name: Install security tools
        run: |
          pip install safety bandit

      - name: Run safety (dependency vulnerability scan)
        run: safety check --file requirements.txt

      - name: Run bandit (security linting)
        run: bandit -r app/ -f json -o bandit-report.json || true

      - name: Upload security report
        uses: actions/upload-artifact@v4
        if: always()
        with:
          name: security-report
          path: bandit-report.json

  create-pr-preview:
    runs-on: ubuntu-latest
    if: github.event_name == 'pull_request'
    needs: [test-database-migrations, test-docker-build]
    permissions:
      contents: read
      pull-requests: write
      issues: write
    steps:
      - name: Comment on PR
        uses: actions/github-script@v7
        with:
          script: |
            const { data: comments } = await github.rest.issues.listComments({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
            });
            
            const botComment = comments.find(comment => 
              comment.user.type === 'Bot' && 
              comment.body.includes('CI Pipeline Status')
            );
            
            const commentBody = [
              '## CI Pipeline Status',
              '',
              '**All checks passed!** :white_check_mark:',
              '',
              '**Completed Checks:**',
              '- :white_check_mark: Database migration tests (PostgreSQL & SQLite)',
              '- :white_check_mark: Docker build and startup test', 
              '- :white_check_mark: Security vulnerability scan',
              '',
              '**Ready for review and merge** :rocket:',
              '',
              '---',
              '*This comment was automatically generated by the CI pipeline.*'
            ].join('\n');
            
            if (botComment) {
              await github.rest.issues.updateComment({
                comment_id: botComment.id,
                owner: context.repo.owner,
                repo: context.repo.repo,
                body: commentBody,
              });
            } else {
              await github.rest.issues.createComment({
                issue_number: context.issue.number,
                owner: context.repo.owner,
                repo: context.repo.repo,
                body: commentBody,
              });
            }
