name: Database Schema Migrator

on:
  push:
    branches:
      - main
      - staging
    paths:
      - 'migrations/**'
      - 'alembic/**'
      - 'db/**'
  workflow_dispatch:
    inputs:
      environment:
        description: 'Target environment'
        required: true
        default: 'staging'
        type: choice
        options:
          - staging
          - production

env:
  PYTHON_VERSION: '3.12'

jobs:
  validate:
    name: Validate Migrations
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test_db
        ports:
          - 5432:5432
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10

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

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

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

      - name: Run migrations on test DB
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test_db
        run: |
          # --- Alembic ---
          # alembic upgrade head
          #
          # --- Flask-Migrate ---
          # flask db upgrade
          #
          # --- Flyway (Java) ---
          # flyway -url=jdbc:postgresql://localhost:5432/test_db migrate
          #
          # --- Liquibase ---
          # liquibase update
          echo "Replace this step with your migration command"

      - name: Verify schema
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test_db
        run: |
          echo "Add schema validation / smoke queries here"
          # psql $DATABASE_URL -c "\dt"

  migrate:
    name: Apply Migrations — ${{ github.event.inputs.environment || (github.ref_name == 'main' && 'production' || 'staging') }}
    runs-on: ubuntu-latest
    needs: validate
    environment: ${{ github.event.inputs.environment || (github.ref_name == 'main' && 'production' || 'staging') }}
    concurrency:
      group: db-migrate-${{ github.event.inputs.environment || github.ref_name }}
      cancel-in-progress: false   # never cancel an in-flight migration

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

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

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

      - name: Apply migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          # alembic upgrade head
          echo "Replace with your migration command"

      - name: Notify on failure
        if: failure()
        uses: actions/github-script@v7
        with:
          script: |
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: '❌ Database migration failed. Check the [workflow run](${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}) for details.'
            })