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

SENDGRID_API_KEY="${SENDGRID_API_KEY:?Falta SENDGRID_API_KEY}"
MYSQL_PASS="${MYSQL_PASS:?Falta MYSQL_PASS}"

MYSQL_HOST="${MYSQL_HOST:-localhost}"
MYSQL_USER="${MYSQL_USER:-linder8_sendgrid_marketing}"
MYSQL_DB="${MYSQL_DB:-linder8_sendgrid_marketing}"
BACKUP_ROOT="${BACKUP_ROOT:-/home/linder8/sendgrid_backups}"
SENDGRID_ACCOUNT_NAME="${SENDGRID_ACCOUNT_NAME:-Linderlake}"

TS="$(date '+%Y%m%d_%H%M%S')"
BACKUP_DIR="$BACKUP_ROOT/$TS"

MYSQL_DIR="$BACKUP_DIR/mysql"
SENDGRID_DIR="$BACKUP_DIR/sendgrid"
SYSTEM_DIR="$BACKUP_DIR/system"

mkdir -p "$MYSQL_DIR" "$SENDGRID_DIR" "$SYSTEM_DIR"

need_cmd() {
  command -v "$1" >/dev/null || { echo "ERROR: falta $1"; exit 1; }
}

need_cmd curl
need_cmd jq
need_cmd mysql
need_cmd mysqldump
need_cmd python3
need_cmd gzip
need_cmd tar

echo "=== 93 FULL BACKUP SENDGRID ==="
echo "Backup dir: $BACKUP_DIR"

RUN_ID=""

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<SQL
INSERT INTO backup_runs
(backup_path, status, started_at, notes)
VALUES
('$BACKUP_DIR', 'running', NOW(), '93-fullbackup_sendgrid.sh');
SQL

RUN_ID="$(mysql --batch --raw --skip-column-names \
  -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" \
  -e "SELECT MAX(id) FROM backup_runs;")"

finish_failed() {
  mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<SQL
UPDATE backup_runs
SET status='failed', finished_at=NOW(), notes='Backup failed'
WHERE id=$RUN_ID;
SQL
}
trap finish_failed ERR

echo "=== 1) MYSQL SCHEMA + DATA ==="

mysqldump \
  -h "$MYSQL_HOST" \
  -u "$MYSQL_USER" \
  -p"$MYSQL_PASS" \
  --single-transaction \
  --routines \
  --triggers \
  "$MYSQL_DB" \
  > "$MYSQL_DIR/full_database.sql"

gzip -f "$MYSQL_DIR/full_database.sql"

TABLES=(
  contacts
  sendgrid_lists
  contact_list_memberships
  sendgrid_suppressions
  sendgrid_suppression_actions
  sendgrid_suppression_rules
  suppression_import_runs
  backup_runs
  dynamic_sendgrid_lists
)

for T in "${TABLES[@]}"; do
  echo "Export table: $T"

  mysql --batch --raw \
    -h "$MYSQL_HOST" \
    -u "$MYSQL_USER" \
    -p"$MYSQL_PASS" \
    "$MYSQL_DB" \
    -e "SELECT * FROM \`$T\`;" \
    > "$MYSQL_DIR/$T.tsv" 2>/dev/null || true
done

echo "=== 2) SENDGRID LISTS ==="

curl -sS \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  "https://api.sendgrid.com/v3/marketing/lists?page_size=1000" \
  > "$SENDGRID_DIR/lists.json"

echo "=== 3) SENDGRID ASM GROUPS ==="

curl -sS \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  "https://api.sendgrid.com/v3/asm/groups" \
  > "$SENDGRID_DIR/asm_groups.json"

echo "=== 4) SENDGRID GLOBAL SUPPRESSIONS ==="

curl -sS \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  "https://api.sendgrid.com/v3/asm/suppressions/global" \
  > "$SENDGRID_DIR/global_suppressions.json" || true

echo "=== 5) SENDGRID SPAM REPORTS ==="

curl -sS \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  "https://api.sendgrid.com/v3/suppression/spam_reports" \
  > "$SENDGRID_DIR/spam_reports.json" || true

echo "=== 6) SENDGRID INVALID EMAILS ==="

curl -sS \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  "https://api.sendgrid.com/v3/suppression/invalid_emails" \
  > "$SENDGRID_DIR/invalid_emails.json" || true

echo "=== 7) SENDGRID CONTACTS EXPORT ==="

EXPORT_JSON="$SENDGRID_DIR/contacts_export_status.json"
RAW_DOWNLOAD="$SENDGRID_DIR/contacts_export_download"
CONTACTS_CSV="$SENDGRID_DIR/contacts_export.csv"

curl -sS -X POST "https://api.sendgrid.com/v3/marketing/contacts/exports" \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  -H "Content-Type: application/json" \
  --data '{}' > "$EXPORT_JSON"

EXPORT_ID="$(jq -r '.id // empty' "$EXPORT_JSON")"

if [[ -n "$EXPORT_ID" ]]; then
  echo "Export ID: $EXPORT_ID"

  while true; do
    curl -sS \
      -H "Authorization: Bearer $SENDGRID_API_KEY" \
      "https://api.sendgrid.com/v3/marketing/contacts/exports/$EXPORT_ID" \
      > "$EXPORT_JSON"

    STATUS="$(jq -r '.status // empty' "$EXPORT_JSON")"
    URL="$(jq -r '.urls[0] // empty' "$EXPORT_JSON")"

    echo "Status: $STATUS"

    [[ -n "$URL" ]] && break
    [[ "$STATUS" == "failed" ]] && break

    sleep 10
  done

  if [[ -n "${URL:-}" ]]; then
    curl -fL -sS "$URL" -o "$RAW_DOWNLOAD"

    python3 - "$RAW_DOWNLOAD" "$CONTACTS_CSV" <<'PY'
import sys, zipfile, gzip, shutil

src, dst = sys.argv[1], sys.argv[2]

with open(src, "rb") as f:
    head = f.read(4)

if zipfile.is_zipfile(src):
    with zipfile.ZipFile(src) as z:
        csvs = [n for n in z.namelist() if n.lower().endswith(".csv")]
        with z.open(csvs[0]) as f_in, open(dst, "wb") as f_out:
            shutil.copyfileobj(f_in, f_out)
elif head[:2] == b"\x1f\x8b":
    with gzip.open(src, "rb") as f_in, open(dst, "wb") as f_out:
        shutil.copyfileobj(f_in, f_out)
else:
    shutil.copyfile(src, dst)
PY
  fi
fi

echo "=== 8) MANIFEST ==="

CONTACTS_COUNT="$(mysql --batch --raw --skip-column-names -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "SELECT COUNT(*) FROM contacts;" 2>/dev/null || echo 0)"
LISTS_COUNT="$(mysql --batch --raw --skip-column-names -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "SELECT COUNT(*) FROM sendgrid_lists;" 2>/dev/null || echo 0)"
MEMBERSHIPS_COUNT="$(mysql --batch --raw --skip-column-names -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "SELECT COUNT(*) FROM contact_list_memberships;" 2>/dev/null || echo 0)"
SUPPRESSIONS_COUNT="$(mysql --batch --raw --skip-column-names -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "SELECT COUNT(*) FROM sendgrid_suppressions;" 2>/dev/null || echo 0)"

cat > "$BACKUP_DIR/manifest.json" <<JSON
{
  "status": "success",
  "backup_date": "$TS",
  "account": "$SENDGRID_ACCOUNT_NAME",
  "backup_path": "$BACKUP_DIR",
  "mysql_database": "$MYSQL_DB",
  "contacts_count": $CONTACTS_COUNT,
  "lists_count": $LISTS_COUNT,
  "memberships_count": $MEMBERSHIPS_COUNT,
  "suppressions_count": $SUPPRESSIONS_COUNT
}
JSON

echo "=== 9) COMPRESS BACKUP ==="

tar -czf "$BACKUP_ROOT/${TS}.tar.gz" -C "$BACKUP_ROOT" "$TS"

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<SQL
UPDATE backup_runs
SET
  contacts_count=$CONTACTS_COUNT,
  lists_count=$LISTS_COUNT,
  memberships_count=$MEMBERSHIPS_COUNT,
  suppressions_count=$SUPPRESSIONS_COUNT,
  status='success',
  finished_at=NOW(),
  notes='Backup completed successfully'
WHERE id=$RUN_ID;
SQL

trap - ERR

echo "DONE BACKUP 🚀"
echo "Folder: $BACKUP_DIR"
echo "Archive: $BACKUP_ROOT/${TS}.tar.gz"