name: CI DB Seed

on:
  schedule:
    - cron: '0 3 * * *'
  workflow_dispatch:
    inputs:
      factory_restore_timestamp:
        description: ISO-8601 timestamp for restoring the CI factory branch from its own history
        required: false
        type: string

concurrency:
  group: ci-db-seed
  cancel-in-progress: false

jobs:
  seed:
    if: inputs.factory_restore_timestamp == ''
    runs-on: ubuntu-latest
    env:
      NEON_API_KEY: ${{ secrets.NEON_API_KEY }}
      NEON_PROJECT_ID: ${{ secrets.NEON_PROJECT_ID }}
      NEON_MAIN_BRANCH_ID: ${{ secrets.NEON_MAIN_BRANCH_ID }}
      NEON_CI_API_KEY: ${{ secrets.NEON_CI_API_KEY }}
      NEON_CI_PROJECT_ID: ${{ secrets.NEON_CI_PROJECT_ID }}
      NEON_CI_MAIN_BRANCH_ID: ${{ secrets.NEON_CI_MAIN_BRANCH_ID }}

    steps:
      - name: Install postgresql-client-17
        run: |
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail \
            https://www.postgresql.org/media/keys/ACCC4CF8.asc
          sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          sudo apt-get update -q
          sudo apt-get install -y postgresql-client-17

      - name: Resolve production and CI main connection URIs
        run: |
          PROD_RESPONSE=$(curl --silent --fail-with-body \
            --retry 3 --retry-delay 30 --retry-all-errors \
            -H "Authorization: Bearer ${NEON_API_KEY}" \
            "https://console.neon.tech/api/v2/projects/${NEON_PROJECT_ID}/connection_uri?branch_id=${NEON_MAIN_BRANCH_ID}&role_name=neondb_owner&database_name=neondb")
          PROD_DATABASE_URL=$(echo "${PROD_RESPONSE}" | jq -r '.uri')
          echo "::add-mask::${PROD_DATABASE_URL}"

          if [ -z "${PROD_DATABASE_URL}" ] || [ "${PROD_DATABASE_URL}" = "null" ]; then
            echo "ERROR: Could not retrieve production main connection URI."
            echo "${PROD_RESPONSE}" | jq -r '.message // .code // "no message"'
            exit 1
          fi

          CI_RESPONSE=$(curl --silent --fail-with-body \
            --retry 3 --retry-delay 30 --retry-all-errors \
            -H "Authorization: Bearer ${NEON_CI_API_KEY}" \
            "https://console.neon.tech/api/v2/projects/${NEON_CI_PROJECT_ID}/connection_uri?branch_id=${NEON_CI_MAIN_BRANCH_ID}&role_name=neondb_owner&database_name=neondb")
          CI_DATABASE_URL=$(echo "${CI_RESPONSE}" | jq -r '.uri')
          echo "::add-mask::${CI_DATABASE_URL}"

          if [ -z "${CI_DATABASE_URL}" ] || [ "${CI_DATABASE_URL}" = "null" ]; then
            echo "ERROR: Could not retrieve CI main connection URI."
            echo "${CI_RESPONSE}" | jq -r '.message // .code // "no message"'
            exit 1
          fi

          if [ "${CI_DATABASE_URL}" = "${PROD_DATABASE_URL}" ]; then
            echo "ERROR: CI target connection URI matches production source URI. Aborting."
            exit 1
          fi

          echo "PROD_DATABASE_URL=${PROD_DATABASE_URL}" >> "$GITHUB_ENV"
          echo "CI_DATABASE_URL=${CI_DATABASE_URL}" >> "$GITHUB_ENV"

      - name: Dump production main
        run: |
          /usr/lib/postgresql/17/bin/pg_dump \
            --format=custom \
            --compress=9 \
            --no-owner \
            --no-privileges \
            "${PROD_DATABASE_URL}" \
            > /tmp/multideal-ci-main.dump

      - name: Restore into CI main
        run: |
          # pg_restore reports errors and still exits 0 unless --exit-on-error;
          # --single-transaction implies it and leaves no half-restored main.
          /usr/lib/postgresql/17/bin/pg_restore \
            --clean \
            --if-exists \
            --no-owner \
            --no-privileges \
            --single-transaction \
            --dbname="${CI_DATABASE_URL}" \
            /tmp/multideal-ci-main.dump

      - name: Remove local dump
        if: always()
        run: rm -f /tmp/multideal-ci-main.dump

  restore-factory:
    if: inputs.factory_restore_timestamp != ''
    runs-on: ubuntu-latest
    env:
      NEON_CI_API_KEY: ${{ secrets.NEON_CI_API_KEY }}
      NEON_CI_PROJECT_ID: ${{ secrets.NEON_CI_PROJECT_ID }}
      NEON_CI_FACTORY_BRANCH_ID: ${{ secrets.NEON_CI_FACTORY_BRANCH_ID }}
      FACTORY_RESTORE_TIMESTAMP: ${{ inputs.factory_restore_timestamp }}
    steps:
      - name: Restore factory branch from its own history
        run: |
          payload=$(jq -n \
            --arg branch_id "$NEON_CI_FACTORY_BRANCH_ID" \
            --arg timestamp "$FACTORY_RESTORE_TIMESTAMP" \
            --arg preserve_name "factory-overwrite-${GITHUB_RUN_ID}" \
            '{source_branch_id: $branch_id, source_timestamp: $timestamp, preserve_under_name: $preserve_name}')
          curl --silent --show-error --fail-with-body \
            --request POST \
            -H "Authorization: Bearer ${NEON_CI_API_KEY}" \
            -H 'Content-Type: application/json' \
            --data "$payload" \
            "https://console.neon.tech/api/v2/projects/${NEON_CI_PROJECT_ID}/branches/${NEON_CI_FACTORY_BRANCH_ID}/restore"
