#!/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_DB="${MYSQL_DB:-linder8_sendgrid_marketing}"
MYSQL_TABLE="${MYSQL_TABLE:-contacts}"
MYSQL_HOST="${MYSQL_HOST:-localhost}"
MYSQL_USER="${MYSQL_USER:-linder8_sendgrid_marketing}"

WORKDIR="${WORKDIR:-/tmp/sendgrid_sync}"
EXPORT_API="https://api.sendgrid.com/v3/marketing/contacts/exports"

EXPORT_JSON="$WORKDIR/export.json"
RAW_DOWNLOAD="$WORKDIR/sendgrid_export_download"
EXTRACT_DIR="$WORKDIR/exported"
RAW_CSV="$WORKDIR/contacts.csv"
CLEAN_TSV="$WORKDIR/contacts_clean.tsv"
UNSUBS_TXT="$WORKDIR/unsubscribed.txt"
FILTERED_TSV="$WORKDIR/contacts_active_filtered.tsv"

rm -rf "$WORKDIR"
mkdir -p "$EXTRACT_DIR"

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

need_cmd curl
need_cmd jq
need_cmd python3
need_cmd mysql
need_cmd file

echo "=== 1) SOLICITANDO EXPORT COMPLETO SENDGRID ==="

curl -sS -X POST "$EXPORT_API" \
  -H "Authorization: Bearer $SENDGRID_API_KEY" \
  -H "Content-Type: application/json" \
  --data '{}' > "$EXPORT_JSON"

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

if [[ -z "$EXPORT_ID" ]]; then
  echo "ERROR creando export:"
  cat "$EXPORT_JSON"
  exit 1
fi

echo "Export ID: $EXPORT_ID"

while true; do
  curl -sS -X GET "$EXPORT_API/$EXPORT_ID" \
    -H "Authorization: Bearer $SENDGRID_API_KEY" > "$EXPORT_JSON"

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

  echo "Status: $STATUS"

  if [[ -n "$URL" ]]; then
    break
  fi

  if [[ "$STATUS" == "failed" ]]; then
    echo "ERROR: export falló:"
    cat "$EXPORT_JSON"
    exit 1
  fi

  sleep 10
done

echo "=== 2) DESCARGANDO EXPORT ==="

curl -fL -sS "$URL" -o "$RAW_DOWNLOAD"

echo "Tipo descargado:"
file "$RAW_DOWNLOAD"

echo "=== 3) EXTRAYENDO CSV ==="

python3 - "$RAW_DOWNLOAD" "$EXTRACT_DIR" "$RAW_CSV" <<'PY'
import sys, os, zipfile, gzip, shutil

src = sys.argv[1]
extract_dir = sys.argv[2]
raw_csv = sys.argv[3]

os.makedirs(extract_dir, exist_ok=True)

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

if zipfile.is_zipfile(src):
    with zipfile.ZipFile(src) as z:
        z.extractall(extract_dir)

    csvs = []
    for root, dirs, files in os.walk(extract_dir):
        for name in files:
            if name.lower().endswith(".csv"):
                csvs.append(os.path.join(root, name))

    if not csvs:
        raise SystemExit("ERROR: no se encontró CSV dentro del ZIP")

    shutil.copyfile(csvs[0], raw_csv)

elif head[:2] == b"\x1f\x8b":
    with gzip.open(src, "rb") as f_in, open(raw_csv, "wb") as f_out:
        shutil.copyfileobj(f_in, f_out)

else:
    shutil.copyfile(src, raw_csv)

print(f"CSV original: {raw_csv}")
PY

echo "=== 4) GENERANDO TSV LIMPIO ==="

python3 - "$RAW_CSV" "$CLEAN_TSV" <<'PY'
import csv
import sys

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

with open(src, newline='', encoding='utf-8-sig') as f, open(dst, 'w', encoding='utf-8', newline='') as out:
    reader = csv.DictReader(f)

    out.write("email\tfirst_name\tlast_name\n")

    total = 0
    sin_email = 0

    for row in reader:
        email = (row.get('EMAIL') or '').strip().lower()

        if not email:
            sin_email += 1
            continue

        first = (row.get('FIRST_NAME') or '').replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').strip()
        last = (row.get('LAST_NAME') or '').replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').strip()

        out.write(f"{email}\t{first}\t{last}\n")
        total += 1

print(f"Contactos limpios TSV: {total}")
print(f"Sin email omitidos: {sin_email}")
PY

echo "=== 5) DESCARGANDO UNSUBSCRIBED DE ASM GROUPS ==="

> "$UNSUBS_TXT"

GROUPS_JSON="$WORKDIR/groups.json"

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

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

if [[ -z "$GROUP_IDS" ]]; then
  echo "WARNING: no encontré grupos ASM. No se filtrarán unsubscribed."
else
  for GROUP_ID in $GROUP_IDS; do
    echo "Grupo ASM: $GROUP_ID"

    curl -sS "https://api.sendgrid.com/v3/asm/groups/$GROUP_ID/suppressions" \
      -H "Authorization: Bearer $SENDGRID_API_KEY" \
      | jq -r '
          .[]
          | if type == "string" then .
            elif type == "object" then (.email // empty)
            else empty
            end
        ' \
      | tr '[:upper:]' '[:lower:]' \
      | grep -E '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$' \
      >> "$UNSUBS_TXT" || true
  done
fi

sort -u "$UNSUBS_TXT" -o "$UNSUBS_TXT"

UNSUB_TOTAL="$(wc -l < "$UNSUBS_TXT" | tr -d ' ')"
echo "Unsubscribed detectados: $UNSUB_TOTAL"

echo "=== 6) FILTRANDO ACTIVOS ==="

awk '
BEGIN { FS="\t"; OFS="\t" }

NR==FNR {
  u[tolower($1)] = 1
  next
}

FNR==1 {
  print
  next
}

!(tolower($1) in u) {
  print
}
' "$UNSUBS_TXT" "$CLEAN_TSV" > "$FILTERED_TSV"

TOTAL_FINAL="$(( $(wc -l < "$FILTERED_TSV") - 1 ))"
echo "Contactos activos finales: $TOTAL_FINAL"

echo "=== 7) IMPORTANDO A MYSQL ==="

mysql --local-infile=1 \
  -h "$MYSQL_HOST" \
  -u "$MYSQL_USER" \
  -p"$MYSQL_PASS" \
  "$MYSQL_DB" <<SQL
TRUNCATE TABLE \`$MYSQL_TABLE\`;

LOAD DATA LOCAL INFILE '$FILTERED_TSV'
INTO TABLE \`$MYSQL_TABLE\`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(email, first_name, last_name);
SQL

echo "=== 8) VERIFICANDO ==="

mysql \
  -h "$MYSQL_HOST" \
  -u "$MYSQL_USER" \
  -p"$MYSQL_PASS" \
  "$MYSQL_DB" \
  -e "SELECT COUNT(*) AS total_importado FROM \`$MYSQL_TABLE\`;"

echo "Archivo limpio completo: $CLEAN_TSV"
echo "Archivo filtrado activo: $FILTERED_TSV"
echo "DONE 🚀"