build-test.yml

name: MyEnergy Build and Test

on:
  push:
    branches: ["main"]
  pull_request:
    branches: ["main"]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      timescaledb:
        image: timescale/timescaledb-ha:pg17
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: tsdb
        ports:
          - 25432:5432
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 10

    steps:
      - uses: actions/checkout@v6

      - name: Sqitch install
        run: docker pull sqitch/sqitch && curl -L https://git.io/JJKCn -o /usr/local/bin/sqitch && chmod +x /usr/local/bin/sqitch && sqitch --help

      - name: Setup DB
        run: |
          docker run --rm --network host postgres:17-alpine psql \
            "postgresql://postgres:postgres@localhost:25432/postgres" \
            -c "DO \$\$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname='tsdbadmin') THEN CREATE ROLE tsdbadmin LOGIN SUPERUSER PASSWORD 'tsdbadmin'; ELSE ALTER USER tsdbadmin WITH PASSWORD 'tsdbadmin'; END IF; END \$\$;"
          docker run --rm --network host \
            -v "$(pwd)/sqitch:/sqitch" postgres:17-alpine \
            psql "postgresql://tsdbadmin:tsdbadmin@localhost:25432/tsdb" \
            -f /sqitch/ci-bootstrap.sql

      - name: Checkout flows-db
        uses: actions/checkout@v6
        with:
          repository: cepro/simt-flows-db
          path: flows-db

      - name: Flows migrations
        run: |
          cd flows-db/sqitch && sqitch deploy "db:pg://tsdbadmin:tsdbadmin@localhost:25432/tsdb" \
            --set flows_password=flows \
            --set grafanareader_password=grafanareader \
            --set tableau_password=tableau \
            --set env=local \
            --verbose && cd -

      - name: Flows seed
        run: |
          docker run --rm --network host \
            -v "$(pwd)/flows-db/sqitch/seed:/seed" postgres:17-alpine \
            psql "postgresql://tsdbadmin:tsdbadmin@localhost:25432/tsdb" \
            -f /seed/seed.sql

      - name: Migrations
        run: |
          cd sqitch && sqitch deploy --target timescale-ci \
            --set postgraphile_password=postgraphile \
            --verbose && cd -

      # uncomment the following to be able to ssh in and investigate an issue:
      #
      # - name: Setup tmate session
      #   if: success() || failure()
      #   uses: mxschmitt/action-tmate@v3

      - name: create .pgpass for seed script
        run: mkdir -p /tmp/psql-script/ && echo "*:*:*:tsdbadmin:tsdbadmin" > /tmp/psql-script/.pgpass && chmod 0600 /tmp/psql-script/.pgpass

      - name: Seed
        run: bin/seed

      - name: Test
        env:
          PGPASSWORD: tsdbadmin
        run: bin/test

      # TODO: Was using supabase lint - replace with something else
      # - name: Lint
      #   run: bin/supa-lint