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

MAX_WORKERS="${MAX_WORKERS:-3}"
RESUME_SYNC="${RESUME_SYNC:-0}"

CONNECT_TIMEOUT="${CONNECT_TIMEOUT:-10}"
API_MAX_TIME="${API_MAX_TIME:-45}"
DOWNLOAD_MAX_TIME="${DOWNLOAD_MAX_TIME:-180}"
API_RETRIES="${API_RETRIES:-3}"
POLL_INTERVAL="${POLL_INTERVAL:-5}"
MAX_POLLS="${MAX_POLLS:-120}"

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

need_cmd curl
need_cmd jq
need_cmd python3
need_cmd mysql
need_cmd file
need_cmd flock
need_cmd sha256sum

if ! [[ "$MAX_WORKERS" =~ ^[1-9][0-9]*$ ]]; then
    echo "ERROR: MAX_WORKERS debe ser entero >= 1"
    exit 1
fi

# -------------------------------------------------------------------
# LOCK: evita dos sincronizaciones simultaneas
# -------------------------------------------------------------------

LOCKFILE="/tmp/sendgrid_full_sync_v2.lock"
exec 9>"$LOCKFILE"

if ! flock -n 9; then
    echo "ERROR: ya existe otra instancia de 1-sync_sendgrid_mysql.sh ejecutandose."
    exit 1
fi

# -------------------------------------------------------------------
# CURL AUTH
# La API key queda en archivo chmod 600 y NO aparece en ps.
# -------------------------------------------------------------------

AUTH_CONFIG="$(mktemp /tmp/sendgrid_curl_auth.XXXXXX)"
chmod 600 "$AUTH_CONFIG"

cleanup_auth() {
    rm -f "$AUTH_CONFIG"
}
trap cleanup_auth EXIT

printf 'header = "Authorization: Bearer %s"\n' "$SENDGRID_API_KEY" > "$AUTH_CONFIG"

curl_api() {
    curl \
        --config "$AUTH_CONFIG" \
        --connect-timeout "$CONNECT_TIMEOUT" \
        --max-time "$API_MAX_TIME" \
        --retry "$API_RETRIES" \
        --retry-delay 2 \
        -sS \
        "$@"
}

curl_download() {
    curl \
        --connect-timeout "$CONNECT_TIMEOUT" \
        --max-time "$DOWNLOAD_MAX_TIME" \
        --retry "$API_RETRIES" \
        --retry-delay 2 \
        -fLsS \
        "$@"
}

api_json_request() {
    local outfile="$1"
    local method="$2"
    local url="$3"
    local data="${4:-}"
    local attempt code

    for attempt in 1 2 3 4; do
        if [[ -n "$data" ]]; then
            code="$(
                curl \
                    --config "$AUTH_CONFIG" \
                    --connect-timeout "$CONNECT_TIMEOUT" \
                    --max-time "$API_MAX_TIME" \
                    -sS \
                    -o "$outfile" \
                    -w '%{http_code}' \
                    -X "$method" \
                    -H "Content-Type: application/json" \
                    --data "$data" \
                    "$url" || true
            )"
        else
            code="$(
                curl \
                    --config "$AUTH_CONFIG" \
                    --connect-timeout "$CONNECT_TIMEOUT" \
                    --max-time "$API_MAX_TIME" \
                    -sS \
                    -o "$outfile" \
                    -w '%{http_code}' \
                    -X "$method" \
                    "$url" || true
            )"
        fi

        if [[ "$code" =~ ^2[0-9][0-9]$ ]] && jq -e . "$outfile" >/dev/null 2>&1; then
            return 0
        fi

        echo "WARNING API HTTP=${code:-NONE} intento=$attempt url=$url" >&2

        if [[ -s "$outfile" ]]; then
            jq -c . "$outfile" 2>/dev/null >&2 || head -c 500 "$outfile" >&2 || true
            echo >&2
        fi

        sleep $((attempt * 2))
    done

    return 1
}

extract_download_to_csv() {
    local src="$1"
    local dst="$2"

    python3 - "$src" "$dst" <<'PY'
import sys
import zipfile
import gzip
import shutil

src, dst = sys.argv[1:3]

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("ERROR: ZIP sin archivo 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)
PY
}

# -------------------------------------------------------------------
# PREPARACION / RESUME
# -------------------------------------------------------------------

if [[ "$RESUME_SYNC" == "1" ]]; then
    if [[ ! -f "$WORKDIR/.resume_ready" ]]; then
        echo "ERROR: RESUME_SYNC=1 pero no existe estado reanudable en:"
        echo "       $WORKDIR"
        exit 1
    fi

    echo "============================================================"
    echo "REANUDANDO SYNC EXISTENTE"
    echo "WORKDIR=$WORKDIR"
    echo "MAX_WORKERS=$MAX_WORKERS"
    echo "============================================================"

else
    rm -rf "$WORKDIR"
    mkdir -p \
        "$WORKDIR/exported" \
        "$WORKDIR/list_exports" \
        "$WORKDIR/list_csv" \
        "$WORKDIR/list_members" \
        "$WORKDIR/done" \
        "$WORKDIR/failed"
fi

# ===================================================================
# PASOS 0-4 SOLO EN EJECUCION NUEVA
# ===================================================================

if [[ "$RESUME_SYNC" != "1" ]]; then

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"

if ! api_json_request \
    "$EXPORT_JSON" \
    POST \
    "$EXPORT_API" \
    '{"file_type":"csv"}'
then
    echo "ERROR: no se pudo crear export general."
    exit 1
fi

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

if [[ -z "$EXPORT_ID" ]]; then
    echo "ERROR: export general no devolvio ID:"
    jq . "$EXPORT_JSON" || cat "$EXPORT_JSON"
    exit 1
fi

echo "Export ID: $EXPORT_ID"

URL=""
STATUS=""

for ((poll=1; poll<=MAX_POLLS; poll++)); do
    if ! api_json_request \
        "$EXPORT_JSON" \
        GET \
        "$EXPORT_API/$EXPORT_ID"
    then
        echo "ERROR: no se pudo consultar export general."
        exit 1
    fi

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

    echo "Status: ${STATUS:-UNKNOWN} poll=$poll/$MAX_POLLS"

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

    if [[ "$STATUS" == "failed" ]]; then
        echo "ERROR: export general fallo."
        jq . "$EXPORT_JSON"
        exit 1
    fi

    sleep "$POLL_INTERVAL"
done

if [[ -z "$URL" ]]; then
    echo "ERROR: export general excedio limite de polling."
    exit 1
fi

if ! curl_download "$URL" -o "$RAW_DOWNLOAD"; then
    echo "ERROR: no se pudo descargar export general."
    exit 1
fi

extract_download_to_csv "$RAW_DOWNLOAD" "$RAW_CSV"

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

src, dst = sys.argv[1:3]

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 row.get('email') or '').strip().lower()

        if not email:
            continue

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

        last = (
            row.get('LAST_NAME')
            or 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"

if ! api_json_request \
    "$GROUPS_JSON" \
    GET \
    "https://api.sendgrid.com/v3/asm/groups"
then
    echo "ERROR: no se pudieron obtener ASM groups."
    exit 1
fi

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"

    GROUP_FILE="$WORKDIR/asm_group_${GROUP_ID}.json"

    if ! api_json_request \
        "$GROUP_FILE" \
        GET \
        "https://api.sendgrid.com/v3/asm/groups/$GROUP_ID/suppressions"
    then
        echo "ERROR: fallo consultando ASM Group $GROUP_ID"
        exit 1
    fi

    jq -r '
        .[]
        | if type=="string" then .
          elif type=="object" then (.email // empty)
          else empty
          end
    ' "$GROUP_FILE" \
    | 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"

ACTIVE_COUNT=$(( $(wc -l < "$ACTIVE_TSV") - 1 ))

if (( ACTIVE_COUNT <= 0 )); then
    echo "ERROR: ACTIVE_TSV quedo vacio."
    exit 1
fi

echo "Contactos activos: $ACTIVE_COUNT"

# -------------------------------------------------------------------

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

# Recupera inmediatamente BCS desde snapshot anterior de membresias.
# Esto evita dejar todos los contactos como LINDERLAKE durante el
# procesamiento largo del paso 5.

mysql \
    -h "$MYSQL_HOST" \
    -u "$MYSQL_USER" \
    -p"$MYSQL_PASS" \
    "$MYSQL_DB" <<'SQL'

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

SQL

# -------------------------------------------------------------------

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

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

if ! api_json_request \
    "$LISTS_JSON" \
    GET \
    "https://api.sendgrid.com/v3/marketing/lists?page_size=1000"
then
    echo "ERROR: no se pudieron descargar listas SendGrid."
    exit 1
fi

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

src, dst = sys.argv[1:3]

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

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

if not isinstance(items, list):
    raise SystemExit("ERROR: respuesta de listas no contiene array valido")

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

LIST_COUNT=$(( $(wc -l < "$LISTS_TSV") - 1 ))

if (( LIST_COUNT <= 0 )); then
    echo "ERROR: SendGrid devolvio cero listas."
    exit 1
fi

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

sha256sum "$LISTS_TSV" > "$WORKDIR/lists.sha256"
touch "$WORKDIR/.resume_ready"

fi

# ===================================================================
# PASO 5
# ===================================================================

LISTS_TSV="$WORKDIR/lists.tsv"
UNSUBS_TXT="$WORKDIR/unsubscribed.txt"
MEMBERS_TSV="$WORKDIR/memberships.tsv"

if [[ ! -s "$LISTS_TSV" || ! -f "$UNSUBS_TXT" ]]; then
    echo "ERROR: faltan archivos necesarios para paso 5."
    exit 1
fi

echo "=== 5) EXPORTANDO CONTACTOS POR LISTA ==="
echo "Workers simultaneos: $MAX_WORKERS"

TOTAL_LISTS=$(( $(wc -l < "$LISTS_TSV") - 1 ))
ALREADY_DONE="$(find "$WORKDIR/done" -type f 2>/dev/null | wc -l)"

echo "Listas totales : $TOTAL_LISTS"
echo "Ya completas   : $ALREADY_DONE"
echo "Pendientes     : $((TOTAL_LISTS - ALREADY_DONE))"

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

    local L_EXPORT_JSON="$WORKDIR/list_exports/export_${LIST_ID}.json"
    local L_DOWNLOAD="$WORKDIR/list_exports/download_${LIST_ID}"
    local L_CSV="$WORKDIR/list_csv/${LIST_ID}.csv"
    local L_MEMBERS="$WORKDIR/list_members/${LIST_ID}.tsv"
    local DONE_FILE="$WORKDIR/done/${LIST_ID}"
    local FAILED_FILE="$WORKDIR/failed/${LIST_ID}"

    local L_EXPORT_ID=""
    local L_STATUS=""
    local L_URL=""

    if [[ -f "$DONE_FILE" && -s "$L_MEMBERS" ]]; then
        echo "SKIP completa: $LIST_NAME [$LIST_ID]"
        return 0
    fi

    rm -f "$FAILED_FILE" "$DONE_FILE"

    echo "START: $LIST_NAME [$LIST_ID] esperados=$CONTACT_COUNT"

    if ! api_json_request \
        "$L_EXPORT_JSON" \
        POST \
        "$EXPORT_API" \
        "{\"file_type\":\"csv\",\"list_ids\":[\"$LIST_ID\"]}"
    then
        echo "CREATE_EXPORT_FAILED" > "$FAILED_FILE"
        echo "FAIL create: $LIST_NAME [$LIST_ID]" >&2
        return 1
    fi

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

    if [[ -z "$L_EXPORT_ID" ]]; then
        echo "NO_EXPORT_ID" > "$FAILED_FILE"
        echo "FAIL no export id: $LIST_NAME [$LIST_ID]" >&2
        return 1
    fi

    for ((poll=1; poll<=MAX_POLLS; poll++)); do
        if ! api_json_request \
            "$L_EXPORT_JSON" \
            GET \
            "$EXPORT_API/$L_EXPORT_ID"
        then
            echo "STATUS_REQUEST_FAILED" > "$FAILED_FILE"
            echo "FAIL status: $LIST_NAME [$LIST_ID]" >&2
            return 1
        fi

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

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

        if [[ "$L_STATUS" == "failed" ]]; then
            echo "EXPORT_FAILED" > "$FAILED_FILE"
            echo "FAIL export: $LIST_NAME [$LIST_ID]" >&2
            return 1
        fi

        sleep "$POLL_INTERVAL"
    done

    if [[ -z "$L_URL" ]]; then
        echo "POLL_TIMEOUT" > "$FAILED_FILE"
        echo "FAIL timeout: $LIST_NAME [$LIST_ID]" >&2
        return 1
    fi

    if ! curl_download "$L_URL" -o "$L_DOWNLOAD"; then
        echo "DOWNLOAD_FAILED" > "$FAILED_FILE"
        echo "FAIL download: $LIST_NAME [$LIST_ID]" >&2
        return 1
    fi

    if ! extract_download_to_csv "$L_DOWNLOAD" "$L_CSV"; then
        echo "EXTRACT_FAILED" > "$FAILED_FILE"
        echo "FAIL extract: $LIST_NAME [$LIST_ID]" >&2
        return 1
    fi

    if [[ ! -f "$L_CSV" ]]; then
        echo "CSV_MISSING" > "$FAILED_FILE"
        return 1
    fi

    if ! python3 \
        - "$L_CSV" "$L_MEMBERS" \
        "$LIST_ID" "$LIST_NAME" "$COMPANY_NAME" "$UNSUBS_TXT" <<'PY'
import csv
import 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, 'w', encoding='utf-8', newline='') as out:

    out.write("email\tsendgrid_list_id\tlist_name\tcompany_name\n")

    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)
PY
    then
        echo "PARSE_FAILED" > "$FAILED_FILE"
        echo "FAIL parse: $LIST_NAME [$LIST_ID]" >&2
        return 1
    fi

    MEMBER_COUNT=$(( $(wc -l < "$L_MEMBERS") - 1 ))

    touch "$DONE_FILE"
    rm -f "$FAILED_FILE"

    echo "DONE : $LIST_NAME [$LIST_ID] miembros_activos=$MEMBER_COUNT"

    return 0
}

export -f api_json_request
export -f curl_download
export -f extract_download_to_csv
export -f process_list

export SENDGRID_API_KEY MYSQL_PASS
export MYSQL_DB MYSQL_TABLE MYSQL_HOST MYSQL_USER
export WORKDIR EXPORT_API
export MAX_WORKERS CONNECT_TIMEOUT API_MAX_TIME
export DOWNLOAD_MAX_TIME API_RETRIES
export POLL_INTERVAL MAX_POLLS
export AUTH_CONFIG

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

    if [[ -f "$WORKDIR/done/$LIST_ID" && -s "$WORKDIR/list_members/$LIST_ID.tsv" ]]; then
        echo "SKIP completa: $LIST_NAME [$LIST_ID]"
        continue
    fi

    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

DONE_COUNT="$(find "$WORKDIR/done" -type f 2>/dev/null | wc -l)"
FAILED_COUNT="$(find "$WORKDIR/failed" -type f 2>/dev/null | wc -l)"

echo
echo "=== RESULTADO EXPORTS POR LISTA ==="
echo "Total listas : $TOTAL_LISTS"
echo "Completadas  : $DONE_COUNT"
echo "Fallidas     : $FAILED_COUNT"

if (( DONE_COUNT != TOTAL_LISTS || FAILED_COUNT != 0 )); then
    echo
    echo "ERROR: no todas las listas terminaron correctamente."
    echo "NO se modificara contact_list_memberships."
    echo

    if (( FAILED_COUNT > 0 )); then
        echo "Listas fallidas:"
        while read -r FAILED_PATH; do
            FAILED_ID="$(basename "$FAILED_PATH")"
            REASON="$(cat "$FAILED_PATH" 2>/dev/null || echo UNKNOWN)"
            LIST_DESC="$(
                awk -F'\t' -v id="$FAILED_ID" \
                    '$1==id {print $2}' "$LISTS_TSV"
            )"
            echo "  $FAILED_ID | $LIST_DESC | $REASON"
        done < <(find "$WORKDIR/failed" -type f | sort)
    fi

    echo
    echo "Para reanudar:"
    echo "RESUME_SYNC=1 MAX_WORKERS=$MAX_WORKERS ./1-sync_sendgrid_mysql.sh"
    exit 2
fi

# Consolidar SOLO despues de tener todas las listas.

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

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

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

    FILE="$WORKDIR/list_members/$LIST_ID.tsv"

    if [[ ! -s "$FILE" ]]; then
        echo "ERROR: falta archivo consolidable para lista $LIST_ID"
        exit 1
    fi

    tail -n +2 "$FILE" >> "$MEMBERS_TSV"

done < "$LISTS_TSV"

MEMBERS_ROWS=$(( $(wc -l < "$MEMBERS_TSV") - 1 ))

echo "Membresias activas consolidadas: $MEMBERS_ROWS"

if (( MEMBERS_ROWS <= 0 )); then
    echo "ERROR: memberships.tsv quedo vacio."
    exit 1
fi

# ===================================================================
# PASO 6
# ===================================================================

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

START TRANSACTION;

DELETE FROM 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
SET list_ids=NULL;

UPDATE contacts c
INNER 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
SET company_name='LINDERLAKE';

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

COMMIT;

SQL

# ===================================================================
# PASO 7
# ===================================================================

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

rm -f "$WORKDIR/.resume_ready"

echo
echo "============================================================"
echo "SYNC COMPLETADO"
echo "============================================================"
echo "Contactos activos : $ACTIVE_COUNT"
echo "Listas             : $TOTAL_LISTS"
echo "Membresias         : $MEMBERS_ROWS"
echo "Workers            : $MAX_WORKERS"
echo "Workdir            : $WORKDIR"
echo
echo "DONE JARVIS 🚀"