#!/usr/bin/env bash
set -euo pipefail

usage() {
  cat <<'USAGE'
Usage: scripts/remove_zero_readings.sh [--dry-run] [--yes]

Removes readings where temperature, humidity, and battery are all zero.

Options:
  --dry-run   Count matching readings without deleting them.
  --yes       Delete without prompting.
  --help      Show this help.
USAGE
}

dry_run=0
assume_yes=0

while [[ $# -gt 0 ]]; do
  case "$1" in
    --dry-run)
      dry_run=1
      ;;
    --yes|-y)
      assume_yes=1
      ;;
    --help|-h)
      usage
      exit 0
      ;;
    *)
      echo "Unknown option: $1" >&2
      usage >&2
      exit 2
      ;;
  esac
  shift
done

where_clause="temperature = 0 AND humidity = 0 AND battery = 0"

run_mysql() {
  docker compose exec -T db sh -c 'MYSQL_PWD="$MYSQL_PASSWORD" exec mysql -u"$MYSQL_USER" "$MYSQL_DATABASE" "$@"' sh "$@"
}

matching_count=$(
  printf 'SELECT COUNT(*) FROM readings WHERE %s;\n' "$where_clause" | run_mysql --batch --skip-column-names
)

echo "Found ${matching_count} all-zero reading(s)."

if [[ "$dry_run" -eq 1 || "$matching_count" -eq 0 ]]; then
  exit 0
fi

if [[ "$assume_yes" -ne 1 ]]; then
  read -r -p "Delete these readings? Type 'yes' to continue: " answer
  if [[ "$answer" != "yes" ]]; then
    echo "No records deleted."
    exit 0
  fi
fi

printf 'DELETE FROM readings WHERE %s;\nSELECT ROW_COUNT();\n' "$where_clause" |
  run_mysql --batch --skip-column-names |
  tail -n 1 |
  xargs printf 'Deleted %s all-zero reading(s).\n'