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

source ./0-config_var_setup.sh

WORKDIR="${SUPPRESSION_WORKDIR:-/tmp/sendgrid_suppressions}"
mkdir -p "$WORKDIR"

sql_escape() {
  printf "%s" "${1:-}" | sed "s/'/''/g"
}

insert_suppression() {
  local EMAIL="${1:-}"
  local TYPE="${2:-}"
  local SOURCE="${3:-}"
  local SMTP_STATUS="${4:-}"
  local REASON="${5:-}"
  local CREATED="${6:-0}"

  [[ -z "$EMAIL" ]] && return 0
  [[ "$CREATED" == "null" || -z "$CREATED" ]] && CREATED=0

  EMAIL="$(sql_escape "$EMAIL")"
  TYPE="$(sql_escape "$TYPE")"
  SOURCE="$(sql_escape "$SOURCE")"
  SMTP_STATUS="$(sql_escape "$SMTP_STATUS")"
  REASON="$(sql_escape "$REASON")"

  mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
INSERT IGNORE INTO sendgrid_suppressions
(email, suppression_type, source, smtp_status, reason, sendgrid_created)
VALUES
('$EMAIL', '$TYPE', '$SOURCE', '$SMTP_STATUS', '$REASON', $CREATED);
"
}

echo "======================================"
echo "90 IMPORT SENDGRID SUPPRESSIONS"
echo "======================================"

RUN_ID="$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -Nse "
INSERT INTO suppression_import_runs(notes)
VALUES('90-import_suppressions.sh');
SELECT LAST_INSERT_ID();
")"

echo "Run ID: $RUN_ID"
echo "Limpiando tablas actuales..."

mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
DELETE FROM sendgrid_suppression_actions;
DELETE FROM sendgrid_suppressions;
ALTER TABLE sendgrid_suppressions AUTO_INCREMENT=1;
"

echo ""
echo "SPAM REPORTS"

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

jq -c '.[]?' "$WORKDIR/spam_reports.json" | while read -r ROW; do
  EMAIL="$(echo "$ROW" | jq -r '.email // empty')"
  CREATED="$(echo "$ROW" | jq -r '.created // 0')"
  insert_suppression "$EMAIL" "spam_report" "spam_reports" "" "" "$CREATED"
done

echo ""
echo "INVALID EMAILS"

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

jq -c '.[]?' "$WORKDIR/invalid.json" | while read -r ROW; do
  EMAIL="$(echo "$ROW" | jq -r '.email // empty')"
  REASON="$(echo "$ROW" | jq -r '.reason // empty')"
  CREATED="$(echo "$ROW" | jq -r '.created // 0')"
  insert_suppression "$EMAIL" "invalid_email" "invalid_emails" "" "$REASON" "$CREATED"
done

echo ""
echo "BLOCKS"

curl -sS -H "Authorization: Bearer $SENDGRID_API_KEY" \
"https://api.sendgrid.com/v3/suppression/blocks" \
> "$WORKDIR/blocks.json"

jq -c '.[]?' "$WORKDIR/blocks.json" | while read -r ROW; do
  EMAIL="$(echo "$ROW" | jq -r '.email // empty')"
  STATUS="$(echo "$ROW" | jq -r '.status // empty')"
  REASON="$(echo "$ROW" | jq -r '.reason // empty')"
  CREATED="$(echo "$ROW" | jq -r '.created // 0')"
  insert_suppression "$EMAIL" "block" "blocks" "$STATUS" "$REASON" "$CREATED"
done

echo ""
########################################
# BOUNCES
########################################

echo ""
echo "BOUNCES"

OFFSET=0
TOTAL_IMPORTED=0

while true
do

    echo "Bounce page offset=$OFFSET"

    curl -sS \
    -H "Authorization: Bearer $SENDGRID_API_KEY" \
    "https://api.sendgrid.com/v3/suppression/bounces?limit=500&offset=$OFFSET" \
    > "$WORKDIR/bounces_${OFFSET}.json"

    COUNT=$(jq 'length' "$WORKDIR/bounces_${OFFSET}.json")

    echo "Records: $COUNT"

    [[ "$COUNT" -eq 0 ]] && break

    jq -c '.[]?' "$WORKDIR/bounces_${OFFSET}.json" |
    while read -r ROW
    do

        EMAIL=$(echo "$ROW" | jq -r '.email // empty')
        STATUS=$(echo "$ROW" | jq -r '.status // empty')
        REASON=$(echo "$ROW" | jq -r '.reason // empty')
        CREATED=$(echo "$ROW" | jq -r '.created // 0')

        EMAIL="$(sql_escape "$EMAIL")"
        STATUS="$(sql_escape "$STATUS")"
        REASON="$(sql_escape "$REASON")"


        [[ -z "$EMAIL" ]] && continue

        mysql \
        -u"$MYSQL_USER" \
        -p"$MYSQL_PASS" \
        "$MYSQL_DB" \
        -e "
        INSERT IGNORE INTO sendgrid_suppressions
        (
            email,
            suppression_type,
            source,
            smtp_status,
            reason,
            sendgrid_created
        )
        VALUES
        (
            '$EMAIL',
            'bounce',
            'bounces',
            '$STATUS',
            '$REASON',
            $CREATED
        );
        "

    done

    TOTAL_IMPORTED=$((TOTAL_IMPORTED + COUNT))

    [[ "$COUNT" -lt 500 ]] && break

    OFFSET=$((OFFSET + 500))

done

echo "Total bounces imported: $TOTAL_IMPORTED"

echo ""
echo "ASM GROUPS"

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

GROUP_IDS="$(jq -r '
if type=="array" then
  .[]?.id // empty
elif type=="object" and (.suppressions? | type=="array") then
  .suppressions[]?.id // empty
else
  empty
end
' "$WORKDIR/groups.json")"

for GROUP_ID in $GROUP_IDS; do
  echo "Procesando ASM Group: $GROUP_ID"

  curl -sS -H "Authorization: Bearer $SENDGRID_API_KEY" \
  "https://api.sendgrid.com/v3/asm/groups/$GROUP_ID/suppressions" \
  > "$WORKDIR/group_${GROUP_ID}.json"

  jq -r '
  if type=="array" then
    .[] |
    if type=="string" then .
    elif type=="object" then .email // empty
    else empty
    end
  elif type=="object" and (.suppressions? | type=="array") then
    .suppressions[] |
    if type=="string" then .
    elif type=="object" then .email // empty
    else empty
    end
  else
    empty
  end
  ' "$WORKDIR/group_${GROUP_ID}.json" | while read -r EMAIL; do
    insert_suppression "$EMAIL" "asm_unsubscribe" "group_$GROUP_ID" "" "" 0
  done
done

echo ""
echo "HISTORY"

mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
INSERT INTO sendgrid_suppressions_history
(import_run_id, email, suppression_type, source, smtp_status, reason, sendgrid_created)
SELECT
$RUN_ID,
email,
suppression_type,
source,
smtp_status,
reason,
sendgrid_created
FROM sendgrid_suppressions;
"

TOTAL_IMPORTED="$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -Nse "
SELECT COUNT(*) FROM sendgrid_suppressions;
")"

mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
UPDATE suppression_import_runs
SET imported_records=$TOTAL_IMPORTED,
    notes=CONCAT(COALESCE(notes,''), ' | completed')
WHERE id=$RUN_ID;
"

echo ""
echo "======================================"
echo "SUPPRESSIONS IMPORTADAS"
echo "======================================"

mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "
SELECT suppression_type, COUNT(*) total
FROM sendgrid_suppressions
GROUP BY suppression_type
ORDER BY total DESC;
"

echo ""
echo "Total importado: $TOTAL_IMPORTED"
echo "Run ID: $RUN_ID completado."