name: Test Migrations

on:
  push:
    branches:
      - main
      - master
      - dev
    paths:
      - 'src/langbot/pkg/persistence/**'
      - 'src/langbot/pkg/entity/persistence/**'
  pull_request:
    types: [opened, synchronize, reopened, ready_for_review]
    paths:
      - 'src/langbot/pkg/persistence/**'
      - 'src/langbot/pkg/entity/persistence/**'

jobs:
  test-migrations-sqlite:
    name: Migrations (SQLite)
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.12'

      - name: Install uv
        uses: astral-sh/setup-uv@v4

      - name: Install dependencies
        run: uv sync --dev

      - name: Test Alembic upgrade (SQLite)
        run: |
          uv run python -c "
          import asyncio
          from sqlalchemy.ext.asyncio import create_async_engine
          from langbot.pkg.entity.persistence.base import Base
          from langbot.pkg.persistence.alembic_runner import run_alembic_upgrade, run_alembic_stamp, get_alembic_current

          async def main():
              engine = create_async_engine('sqlite+aiosqlite:///test_migrations.db')

              # Create all tables (simulates existing DB)
              async with engine.begin() as conn:
                  await conn.run_sync(Base.metadata.create_all)

              # Stamp baseline
              await run_alembic_stamp(engine, '0001_baseline')
              rev = await get_alembic_current(engine)
              assert rev == '0001_baseline', f'Expected 0001_baseline, got {rev}'
              print(f'Stamped: {rev}')

              # Upgrade to head
              await run_alembic_upgrade(engine, 'head')
              rev = await get_alembic_current(engine)
              print(f'After upgrade: {rev}')
              assert rev is not None, 'Expected a revision after upgrade'

              # Verify idempotent
              await run_alembic_upgrade(engine, 'head')
              rev2 = await get_alembic_current(engine)
              assert rev2 == rev, f'Expected {rev}, got {rev2}'
              print(f'Idempotent check passed: {rev2}')

              # Fresh DB: upgrade from scratch
              engine2 = create_async_engine('sqlite+aiosqlite:///test_migrations_fresh.db')
              async with engine2.begin() as conn:
                  await conn.run_sync(Base.metadata.create_all)
              await run_alembic_upgrade(engine2, 'head')
              rev3 = await get_alembic_current(engine2)
              print(f'Fresh DB upgrade: {rev3}')
              assert rev3 is not None

              print('All SQLite migration tests passed!')

          asyncio.run(main())
          "

  test-migrations-postgres:
    name: Migrations (PostgreSQL)
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: langbot
          POSTGRES_PASSWORD: langbot
          POSTGRES_DB: langbot_test
        ports:
          - 5432:5432
        options: >-
          --health-cmd="pg_isready -U langbot"
          --health-interval=5s
          --health-timeout=5s
          --health-retries=5

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

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.12'

      - name: Install uv
        uses: astral-sh/setup-uv@v4

      - name: Install dependencies
        run: uv sync --dev

      - name: Test Alembic upgrade (PostgreSQL)
        run: |
          uv run python -c "
          import asyncio
          from sqlalchemy.ext.asyncio import create_async_engine
          from langbot.pkg.entity.persistence.base import Base
          from langbot.pkg.persistence.alembic_runner import run_alembic_upgrade, run_alembic_stamp, get_alembic_current

          DB_URL = 'postgresql+asyncpg://langbot:langbot@localhost:5432/langbot_test'

          async def main():
              engine = create_async_engine(DB_URL)

              # Create all tables
              async with engine.begin() as conn:
                  await conn.run_sync(Base.metadata.create_all)

              # Stamp baseline
              await run_alembic_stamp(engine, '0001_baseline')
              rev = await get_alembic_current(engine)
              assert rev == '0001_baseline', f'Expected 0001_baseline, got {rev}'
              print(f'Stamped: {rev}')

              # Upgrade to head
              await run_alembic_upgrade(engine, 'head')
              rev = await get_alembic_current(engine)
              print(f'After upgrade: {rev}')
              assert rev is not None

              # Verify idempotent
              await run_alembic_upgrade(engine, 'head')
              rev2 = await get_alembic_current(engine)
              assert rev2 == rev, f'Expected {rev}, got {rev2}'
              print(f'Idempotent check passed: {rev2}')

              # Fresh DB: drop all and upgrade from scratch
              engine2 = create_async_engine(DB_URL.replace('langbot_test', 'langbot_fresh'))

              # Create fresh database
              from sqlalchemy import text
              async with engine.connect() as conn:
                  await conn.execute(text('COMMIT'))
                  await conn.execute(text('CREATE DATABASE langbot_fresh'))

              async with engine2.begin() as conn:
                  await conn.run_sync(Base.metadata.create_all)
              await run_alembic_upgrade(engine2, 'head')
              rev3 = await get_alembic_current(engine2)
              print(f'Fresh DB upgrade: {rev3}')
              assert rev3 is not None

              print('All PostgreSQL migration tests passed!')

          asyncio.run(main())
          "