update-db.yml

name: Update DB

on:
  workflow_dispatch:
    inputs:
      sql_path:
        description: "Path to SQL file committed in repo"
        required: true

permissions:
  contents: write

jobs:
  update:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repo
        uses: actions/checkout@v3
        with:
          fetch-depth: 0

      - name: Install sqlite3
        run: |
          sudo apt-get update
          sudo apt-get install -y sqlite3 gzip

      - name: Validate sql_path
        run: |
          set -euo pipefail
          SQL_PATH="${{ github.event.inputs.sql_path }}"

          # Permite SOLO dentro de esta carpeta (ajusta si hace falta)
          case "$SQL_PATH" in
            pending-sql/*.sql) ;;
            *) echo "Invalid sql_path: $SQL_PATH"; exit 1 ;;
          esac

          test -f "$SQL_PATH"
          echo "SQL_PATH=$SQL_PATH" >> $GITHUB_ENV

      - name: Unzip DB
        run: |
          set -euo pipefail
          test -f ./public/CollecTF.db.gz
          gunzip -c ./public/CollecTF.db.gz > ./public/CollecTF.db

      - name: Run SQL from file (atomic-ish)
        run: |
          set -euo pipefail
          echo "Using SQL file: $SQL_PATH"

          # Ejecuta SQL en una transacción (por si el sql no la incluye)
          sqlite3 ./public/CollecTF.db <<'SQL'
          PRAGMA foreign_keys=ON;
          .read '"'"$SQL_PATH"'"'
          SQL

      - name: Re-zip DB
        run: |
          set -euo pipefail
          gzip -f ./public/CollecTF.db  # deja ./public/CollecTF.db.gz
          # opcional: elimina el .db si gzip no lo borra (gzip normal lo borra)
          rm -f ./public/CollecTF.db || true

      - name: Delete SQL file after use
        run: |
          set -euo pipefail
          rm -f "$SQL_PATH"

      - name: Commit updated database + cleanup sql file
        env:
          TOKEN: ${{ secrets.BOT_TOKEN }}
        run: |
          set -euo pipefail
          git config user.name "github-actions"
          git config user.email "github-actions@github.com"

          git add ./public/CollecTF.db.gz
          git add -u

          if git diff --cached --quiet; then
            echo "No changes to commit."
            exit 0
          fi

          git commit -m "Update database via workflow_dispatch"
          git remote set-url origin https://x-access-token:${TOKEN}@github.com/${{ github.repository }}
          git push origin main