# .gitea/workflows/manual-db-reset-test.yml
#
# This workflow is DESTRUCTIVE and intended for manual execution only.
# It will completely WIPE and RESET the TEST database.
#
name: Manual - Reset Test Database

on:
  workflow_dispatch:
    inputs:
      user_email:
        description: 'Email of the user to preserve (e.g., tsx@gmail.com). Leave blank to skip backup/restore.'
        required: false
      confirmation:
        description: 'This will WIPE the test database. Type "reset-test-db" to confirm.'
        required: true
        default: 'do-not-run'

jobs:
  reset-database:
    runs-on: projectium.com # This job runs on your self-hosted Gitea runner.

    env:
      # Use test database credentials for this entire job.
      DB_HOST: ${{ secrets.DB_HOST }}
      DB_USER: ${{ secrets.DB_USER_TEST }}
      DB_PASSWORD: ${{ secrets.DB_PASSWORD_TEST }}
      DB_NAME: ${{ secrets.DB_DATABASE_TEST }}

    steps:
      - name: Checkout Code
        uses: actions/checkout@v3

      - name: Validate Secrets
        run: |
          # Fail-fast check to ensure secrets are configured in Gitea.
          if [ -z "$DB_HOST" ] || [ -z "$DB_USER" ] || [ -z "$DB_PASSWORD" ] || [ -z "$DB_NAME" ]; then
            echo "ERROR: One or more test database secrets (DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE_TEST) are not set in Gitea repository settings."
            exit 1
          fi
          echo "✅ All required database secrets are present."

      - name: Verify Confirmation Phrase
        run: |
          if [ "${{ gitea.event.inputs.confirmation }}" != "reset-test-db" ]; then
            echo "ERROR: Confirmation phrase did not match. Aborting database reset."
            exit 1
          fi
          echo "✅ Confirmation accepted. Proceeding with database reset."

      - name: 🚨 WARNING & PAUSE 🚨
        run: |
          echo "*********************************************************************"
          echo "WARNING: YOU ARE ABOUT TO WIPE AND RESET THE TEST DATABASE."
          echo "This action is IRREVERSIBLE. Press Ctrl+C in the runner terminal NOW to cancel."
          echo "Sleeping for 10 seconds..."
          echo "*********************************************************************"
          sleep 10

      - name: Step 1 - (Optional) Backup Specific User Data
        if: ${{ gitea.event.inputs.user_email != '' }}
        run: |
          USER_EMAIL="${{ gitea.event.inputs.user_email }}"
          BACKUP_FILE="user_backup_${USER_EMAIL}.sql"
          echo "Attempting to back up data for user: $USER_EMAIL from TEST DB"

          # Get the user_id for the specified email.
          USER_ID=$(PGPASSWORD="$DB_PASSWORD" psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" -c "SELECT user_id FROM public.users WHERE email = '$USER_EMAIL';" -t -A)

          if [ -z "$USER_ID" ]; then
            echo "WARNING: User with email '$USER_EMAIL' not found. Skipping backup."
            echo "NO_USER_BACKUP=true" >> $GITEA_ENV
          else
            echo "User ID found: $USER_ID. Proceeding with backup..."
            PGPASSWORD="$DB_PASSWORD" pg_dump -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" \
              --data-only --column-inserts \
              --table="public.users" --table="public.profiles" \
              > "$BACKUP_FILE"
            
            grep "public.users" "$BACKUP_FILE" | grep "'$USER_ID'" > filtered_backup.sql
            grep "public.profiles" "$BACKUP_FILE" | grep "'$USER_ID'" >> filtered_backup.sql

            echo "✅ User data backup created in filtered_backup.sql"
          fi

      - name: Step 2 - Drop All Tables from Test DB
        run: |
          echo "Executing drop_tables.sql against the TEST database..."
          PGPASSWORD="$DB_PASSWORD" psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" -f sql/drop_tables.sql
          echo "✅ All tables dropped successfully."

      - name: Step 3 - Rebuild Schema from Master Rollup
        run: |
          echo "Executing master_schema_rollup.sql against the TEST database..."
          PGPASSWORD="$DB_PASSWORD" psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" -f sql/master_schema_rollup.sql
          echo "✅ Schema rebuilt successfully."

      - name: Step 4 - (Optional) Restore Specific User Data
        if: ${{ gitea.event.inputs.user_email != '' && env.NO_USER_BACKUP != 'true' }}
        run: |
          echo "Restoring user data from filtered_backup.sql..."          
          PGPASSWORD="$DB_PASSWORD" psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" -f filtered_backup.sql
          echo "✅ User data restored successfully."

      - name: Step 5 - Update Schema Info Table
        run: |
          echo "Updating schema_info table with the new schema hash for 'test'..."
          # Calculate the hash of the current schema file.
          CURRENT_HASH=$(cat sql/master_schema_rollup.sql | dos2unix | sha256sum | awk '{ print $1 }')
          echo "New Schema Hash: $CURRENT_HASH"

          # Insert the new hash into the freshly created schema_info table.
          PGPASSWORD="$DB_PASSWORD" psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" -c \
          "INSERT INTO public.schema_info (environment, schema_hash, deployed_at) VALUES ('test', '$CURRENT_HASH', NOW())
           ON CONFLICT (environment) DO UPDATE SET schema_hash = EXCLUDED.schema_hash, deployed_at = NOW();"

          # Verify the hash was updated
          UPDATED_HASH=$(PGPASSWORD="$DB_PASSWORD" psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p 5432 -U "$DB_USER" -d "$DB_NAME" -c "SELECT schema_hash FROM public.schema_info WHERE environment = 'test';" -t -A)
          if [ "$CURRENT_HASH" = "$UPDATED_HASH" ]; then
            echo "✅ Schema hash successfully set in the database to: $UPDATED_HASH"
          else
            echo "ERROR: Failed to set schema hash in the database."
            exit 1
          fi

      - name: Step 6 - Clear Flyer Asset Directories
        run: |
          APP_PATH="/var/www/flyer-crawler-test.projectium.com"
          echo "Clearing contents of test flyer asset directories..."
          find "$APP_PATH/flyer-images" -mindepth 1 -type f -delete
          find "$APP_PATH/flyer-images/icons" -mindepth 1 -type f -delete
          find "$APP_PATH/flyer-images/archive" -mindepth 1 -type f -delete || echo "Archive directory not found, skipping."
          echo "✅ Test flyer asset directories cleared."
