#!/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_full_sync}"
EXPORT_API="https://api.sendgrid.com/v3/marketing/contacts/exports"

rm -rf "$WORKDIR"
mkdir -p "$WORKDIR/exported"

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 "=== 0) PREPARANDO TABLAS ==="

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<'SQL'
CREATE TABLE IF NOT EXISTS sendgrid_lists (
  id BIGINT NOT NULL AUTO_INCREMENT,
  sendgrid_list_id VARCHAR(100) NOT NULL,
  name VARCHAR(255) NOT NULL,
  contact_count INT DEFAULT 0,
  company_name VARCHAR(100) DEFAULT 'LINDERLAKE',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sendgrid_list_id (sendgrid_list_id),
  KEY idx_company_name (company_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_list_memberships (
  id BIGINT NOT NULL AUTO_INCREMENT,
  contact_id BIGINT NOT NULL,
  sendgrid_list_id VARCHAR(100) NOT NULL,
  list_name VARCHAR(255) NOT NULL,
  company_name VARCHAR(100) DEFAULT 'LINDERLAKE',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_contact_list (contact_id, sendgrid_list_id),
  KEY idx_contact_id (contact_id),
  KEY idx_sendgrid_list_id (sendgrid_list_id),
  KEY idx_company_name (company_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SQL

echo "=== 1) EXPORTANDO CONTACTOS COMPLETOS ==="

EXPORT_JSON="$WORKDIR/export_all.json"
RAW_DOWNLOAD="$WORKDIR/sendgrid_all_download"
RAW_CSV="$WORKDIR/contacts.csv"
CLEAN_TSV="$WORKDIR/contacts_clean.tsv"
UNSUBS_TXT="$WORKDIR/unsubscribed.txt"
ACTIVE_TSV="$WORKDIR/contacts_active.tsv"

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")"
echo "Export ID: $EXPORT_ID"

while true; do
  curl -sS "$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"

  [[ -n "$URL" ]] && break
  [[ "$STATUS" == "failed" ]] && { cat "$EXPORT_JSON"; exit 1; }

  sleep 10
done

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

python3 - "$RAW_DOWNLOAD" "$RAW_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

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

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

with open(src, newline='', encoding='utf-8-sig') as f, open(dst, 'w', encoding='utf-8', newline='') as out:
    r = csv.DictReader(f)
    out.write("email\tfirst_name\tlast_name\n")
    total = 0

    for row in r:
        email = (row.get('EMAIL') or '').strip().lower()
        if not email:
            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 totales limpios: {total}")
PY

echo "=== 2) DESCARGANDO UNSUBSCRIBED ==="

GROUPS_JSON="$WORKDIR/groups.json"
> "$UNSUBS_TXT"

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")"

for GROUP_ID in $GROUP_IDS; do
  echo "ASM Group: $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

sort -u "$UNSUBS_TXT" -o "$UNSUBS_TXT"
echo "Unsubscribed: $(wc -l < "$UNSUBS_TXT")"

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" > "$ACTIVE_TSV"

echo "Contactos activos: $(( $(wc -l < "$ACTIVE_TSV") - 1 ))"

echo "=== 3) IMPORTANDO CONTACTOS ACTIVOS ==="

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

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

UPDATE \`$MYSQL_TABLE\`
SET company_name='LINDERLAKE',
    unsubscribed=0,
    status='active',
    synced_from_sendgrid=1,
    updated_at=NOW();
SQL

echo "=== 4) DESCARGANDO LISTAS SENDGRID ==="

LISTS_JSON="$WORKDIR/lists.json"
LISTS_TSV="$WORKDIR/lists.tsv"
MEMBERS_TSV="$WORKDIR/memberships.tsv"

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

python3 - "$LISTS_JSON" "$LISTS_TSV" <<'PY'
import json
import sys

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

with open(src, encoding='utf-8') as f:
    data = json.load(f)

items = data.get('result') or data.get('lists') or data

with open(dst, 'w', encoding='utf-8') as out:
    out.write("sendgrid_list_id\tname\tcontact_count\tcompany_name\n")

    total = 0

    for item in items:
        if not isinstance(item, dict):
            continue

        list_id = str(item.get('id') or '').strip()
        name = str(item.get('name') or '').strip()
        count = item.get('contact_count') or item.get('contacts_count') or 0

        if not list_id or not name:
            continue

        # Company classification precedence:
        #   1. Explicit AtWork/Conroe lists
        #   2. BCS lists
        #   3. Everything else = Linderlake
        upper_name = name.upper()

        if upper_name in ('2026_ATWORK_CLIENTS', '2026_ATWORK_STAFF'):
            company = 'ATWORK/CONROE'
        elif 'BCS' in upper_name:
            company = 'BCS'
        else:
            company = 'LINDERLAKE'

        name = name.replace('\t', ' ').replace('\n', ' ').replace('\r', ' ')

        out.write(f"{list_id}\t{name}\t{count}\t{company}\n")
        total += 1

print(f"Listas detectadas: {total}")
PY

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

LOAD DATA LOCAL INFILE '$LISTS_TSV'
INTO TABLE sendgrid_lists
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sendgrid_list_id, name, contact_count, company_name);
SQL

echo "=== 5) EXPORTANDO CONTACTOS POR LISTA Y CREANDO MAPEO ==="

MAX_WORKERS="${MAX_WORKERS:-3}"
LIST_WORKDIR="$WORKDIR/list_parallel"

rm -rf "$LIST_WORKDIR"
mkdir -p "$LIST_WORKDIR"

echo -e "email\tsendgrid_list_id\tlist_name\tcompany_name" > "$MEMBERS_TSV"

process_list() {
  local LIST_ID="$1"
  local LIST_NAME="$2"
  local CONTACT_COUNT="$3"
  local COMPANY_NAME="$4"

  local SAFE_ID="$LIST_ID"
  local L_EXPORT_JSON="$LIST_WORKDIR/export_${SAFE_ID}.json"
  local L_DOWNLOAD="$LIST_WORKDIR/download_${SAFE_ID}"
  local L_CSV="$LIST_WORKDIR/${SAFE_ID}.csv"
  local L_MEMBERS="$LIST_WORKDIR/${SAFE_ID}.tsv"

  echo "START | $LIST_NAME | expected=$CONTACT_COUNT"

  # Una lista vacia no necesita crear un export remoto.
  if [[ "${CONTACT_COUNT:-0}" =~ ^[0-9]+$ ]] && (( CONTACT_COUNT == 0 )); then
    : > "$L_MEMBERS"
    echo "DONE  | $LIST_NAME | expected=0 active=0 | SKIP EMPTY"
    return 0
  fi

  local HTTP
  HTTP="$(
    curl --connect-timeout 10 --max-time 60 \
      --retry 3 --retry-delay 2 \
      -sS \
      -o "$L_EXPORT_JSON" \
      -w '%{http_code}' \
      -X POST "$EXPORT_API" \
      -H "Authorization: Bearer $SENDGRID_API_KEY" \
      -H "Content-Type: application/json" \
      --data "{\"file_type\":\"csv\",\"list_ids\":[\"$LIST_ID\"]}"
  )"

  if [[ "$HTTP" != "202" ]]; then
    echo "FAIL CREATE | $LIST_NAME | HTTP=$HTTP" >&2
    return 1
  fi

  local L_EXPORT_ID
  L_EXPORT_ID="$(jq -r '.id // empty' "$L_EXPORT_JSON")"

  if [[ -z "$L_EXPORT_ID" ]]; then
    echo "FAIL NO_EXPORT_ID | $LIST_NAME" >&2
    return 1
  fi

  local L_URL=""
  local L_STATUS=""
  local POLL_HTTP=""

  for i in $(seq 1 80); do
    POLL_HTTP="$(
      curl --connect-timeout 10 --max-time 60 \
        --retry 3 --retry-delay 2 \
        -sS \
        -o "$L_EXPORT_JSON" \
        -w '%{http_code}' \
        "$EXPORT_API/$L_EXPORT_ID" \
        -H "Authorization: Bearer $SENDGRID_API_KEY"
    )"

    if [[ "$POLL_HTTP" != "200" ]]; then
      echo "WARNING POLL | $LIST_NAME | HTTP=$POLL_HTTP | attempt=$i" >&2
      sleep 5
      continue
    fi

    L_STATUS="$(jq -r '.status // empty' "$L_EXPORT_JSON")"
    L_URL="$(jq -r '.urls[0] // empty' "$L_EXPORT_JSON")"

    [[ -n "$L_URL" ]] && break

    if [[ "$L_STATUS" == "failed" ]]; then
      echo "FAIL EXPORT | $LIST_NAME" >&2
      return 1
    fi

    sleep 5
  done

  if [[ -z "$L_URL" ]]; then
    echo "FAIL TIMEOUT | $LIST_NAME" >&2
    return 1
  fi

  if ! curl --connect-timeout 10 --max-time 180 \
      --retry 3 --retry-delay 2 \
      -fLsS "$L_URL" -o "$L_DOWNLOAD"; then
    echo "FAIL DOWNLOAD | $LIST_NAME" >&2
    return 1
  fi

  if ! python3 - "$L_DOWNLOAD" "$L_CSV" <<'PYCSV'
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")]
        if not csvs:
            raise SystemExit("ZIP_WITHOUT_CSV")
        with z.open(csvs[0]) as fi, open(dst, "wb") as fo:
            shutil.copyfileobj(fi, fo)

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

else:
    shutil.copyfile(src, dst)
PYCSV
  then
    echo "FAIL EXTRACT | $LIST_NAME" >&2
    return 1
  fi

  if ! python3 - "$L_CSV" "$L_MEMBERS" "$LIST_ID" "$LIST_NAME" "$COMPANY_NAME" "$UNSUBS_TXT" <<'PYMEM'
import csv, sys

src, dst, list_id, list_name, company, unsub_file = sys.argv[1:7]

unsubs = set()

try:
    with open(unsub_file, encoding="utf-8") as f:
        unsubs = {x.strip().lower() for x in f if x.strip()}
except FileNotFoundError:
    pass

list_name = (
    list_name
    .replace("\t", " ")
    .replace("\n", " ")
    .replace("\r", " ")
)

count = 0

with open(src, newline="", encoding="utf-8-sig") as f, \
     open(dst, "w", encoding="utf-8", newline="") as out:

    r = csv.DictReader(f)

    for row in r:
        email = (row.get("EMAIL") or row.get("email") or "").strip().lower()

        if not email or email in unsubs:
            continue

        out.write(f"{email}\t{list_id}\t{list_name}\t{company}\n")
        count += 1

print(count)
PYMEM
  then
    echo "FAIL PARSE | $LIST_NAME" >&2
    return 1
  fi

  local ACTIVE_COUNT
  ACTIVE_COUNT="$(wc -l < "$L_MEMBERS")"

  echo "DONE  | $LIST_NAME | expected=$CONTACT_COUNT active=$ACTIVE_COUNT"
}

PIDS=()
FAILURES=0

wait_batch() {
  local PID

  for PID in "${PIDS[@]}"; do
    if ! wait "$PID"; then
      FAILURES=$((FAILURES + 1))
    fi
  done

  PIDS=()
}

while IFS=$'\t' read -r LIST_ID LIST_NAME CONTACT_COUNT COMPANY_NAME; do

  [[ "$LIST_ID" == "sendgrid_list_id" ]] && continue
  [[ -z "$LIST_ID" ]] && continue

  process_list \
    "$LIST_ID" \
    "$LIST_NAME" \
    "$CONTACT_COUNT" \
    "$COMPANY_NAME" &

  PIDS+=("$!")

  if (( ${#PIDS[@]} >= MAX_WORKERS )); then
    wait_batch
  fi

done < "$LISTS_TSV"

if (( ${#PIDS[@]} > 0 )); then
  wait_batch
fi

if (( FAILURES > 0 )); then
  echo "ERROR: $FAILURES lista(s) fallaron."
  echo "STOP: no se ejecutara el PASO 6."
  exit 1
fi

# Consolidar en el mismo MEMBERS_TSV esperado por el script original.
while IFS=$'\t' read -r LIST_ID LIST_NAME CONTACT_COUNT COMPANY_NAME; do

  [[ "$LIST_ID" == "sendgrid_list_id" ]] && continue
  [[ -z "$LIST_ID" ]] && continue

  L_MEMBERS="$LIST_WORKDIR/${LIST_ID}.tsv"

  if [[ ! -f "$L_MEMBERS" ]]; then
    echo "ERROR: falta resultado para lista $LIST_ID ($LIST_NAME)"
    exit 1
  fi

  cat "$L_MEMBERS" >> "$MEMBERS_TSV"

done < "$LISTS_TSV"

echo "Paso 5 completado."
echo "Membresias activas: $(( $(wc -l < "$MEMBERS_TSV") - 1 ))"

echo "=== 6) IMPORTANDO MAPEO CONTACTO-LISTA ==="

mysql --local-infile=1 \
  -h "$MYSQL_HOST" \
  -u "$MYSQL_USER" \
  -p"$MYSQL_PASS" \
  "$MYSQL_DB" <<SQL
DROP TEMPORARY TABLE IF EXISTS tmp_memberships;

CREATE TEMPORARY TABLE tmp_memberships (
  email VARCHAR(255) NOT NULL,
  sendgrid_list_id VARCHAR(100) NOT NULL,
  list_name VARCHAR(255) NOT NULL,
  company_name VARCHAR(100) DEFAULT 'LINDERLAKE',
  INDEX idx_email (email),
  INDEX idx_list (sendgrid_list_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOAD DATA LOCAL INFILE '$MEMBERS_TSV'
INTO TABLE tmp_memberships
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(email, sendgrid_list_id, list_name, company_name);

TRUNCATE TABLE contact_list_memberships;

INSERT IGNORE INTO contact_list_memberships
(contact_id, sendgrid_list_id, list_name, company_name)
SELECT c.id, t.sendgrid_list_id, t.list_name, t.company_name
FROM tmp_memberships t
INNER JOIN contacts c ON LOWER(c.email)=LOWER(t.email);

UPDATE contacts c
INNER JOIN (
  SELECT contact_id
  FROM contact_list_memberships
  WHERE company_name='BCS'
  GROUP BY contact_id
) x ON x.contact_id=c.id
SET c.company_name='BCS';

-- AtWork/Conroe has precedence over BCS.
-- A contact belonging to either authorized AtWork list is classified
-- as ATWORK/CONROE even if it also has historical BCS memberships.
UPDATE contacts c
INNER JOIN (
  SELECT contact_id
  FROM contact_list_memberships
  WHERE list_name IN (
    '2026_Atwork_Clients',
    '2026_Atwork_Staff'
  )
  GROUP BY contact_id
) x ON x.contact_id=c.id
SET c.company_name='ATWORK/CONROE';

UPDATE contacts
SET company_name='LINDERLAKE'
WHERE company_name IS NULL OR company_name='';

SQL

echo "=== 7) RESUMEN FINAL ==="

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<SQL
SELECT COUNT(*) AS contactos_activos FROM contacts;
SELECT COUNT(*) AS listas FROM sendgrid_lists;
SELECT COUNT(*) AS membresias FROM contact_list_memberships;

SELECT company_name, COUNT(*) AS contactos
FROM contacts
GROUP BY company_name
ORDER BY contactos DESC;

SELECT l.company_name, l.name, COUNT(m.id) AS contactos
FROM sendgrid_lists l
LEFT JOIN contact_list_memberships m ON m.sendgrid_list_id=l.sendgrid_list_id
GROUP BY l.company_name, l.name
ORDER BY l.company_name, l.name;
SQL

echo "DONE JARVIS 🚀"
echo "Workdir: $WORKDIR"