#!/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, 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 = 'BCS' if 'BCS' in name.upper() else '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 ==="

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

tail -n +2 "$LISTS_TSV" | while IFS=$'\t' read -r LIST_ID LIST_NAME CONTACT_COUNT COMPANY_NAME; do
  echo "Lista: $LIST_NAME [$LIST_ID]"

  L_EXPORT_JSON="$WORKDIR/export_list_${LIST_ID}.json"
  L_DOWNLOAD="$WORKDIR/list_${LIST_ID}_download"
  L_CSV="$WORKDIR/list_${LIST_ID}.csv"

  curl -sS -X POST "$EXPORT_API" \
    -H "Authorization: Bearer $SENDGRID_API_KEY" \
    -H "Content-Type: application/json" \
    --data "{\"file_type\":\"csv\",\"list_ids\":[\"$LIST_ID\"]}" > "$L_EXPORT_JSON"

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

  [[ -z "$L_EXPORT_ID" ]] && { echo "WARNING sin export id para lista $LIST_ID"; continue; }

  L_URL=""

  for i in {1..80}; do
    curl -sS "$EXPORT_API/$L_EXPORT_ID" \
      -H "Authorization: Bearer $SENDGRID_API_KEY" > "$L_EXPORT_JSON"

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

    [[ -n "$L_URL" ]] && break
    [[ "$L_STATUS" == "failed" ]] && break

    sleep 5
  done

  [[ -z "$L_URL" ]] && { echo "WARNING sin URL para lista $LIST_ID"; continue; }

  curl -fL -sS "$L_URL" -o "$L_DOWNLOAD"

  python3 - "$L_DOWNLOAD" "$L_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")]
        if not csvs:
            raise SystemExit(0)
        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 - "$L_CSV" "$MEMBERS_TSV" "$LIST_ID" "$LIST_NAME" "$COMPANY_NAME" "$UNSUBS_TXT" <<'PY'
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 = {line.strip().lower() for line in f if line.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, 'a', 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(f"  miembros activos: {count}")
PY

done

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
LEFT JOIN (
  SELECT contact_id, JSON_ARRAYAGG(sendgrid_list_id) AS list_ids
  FROM contact_list_memberships
  GROUP BY contact_id
) x ON x.contact_id = c.id
SET c.list_ids = x.list_ids;

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';

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"