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

COMPANY="${COMPANY:-LINDERLAKE}"
CONTACTS_PER_LIST="${CONTACTS_PER_LIST:-1000}"
BATCH_SIZE="${BATCH_SIZE:-1000}"
PREFIX="${PREFIX:-Campaign_${COMPANY}_Random}"

WORKDIR="${WORKDIR:-/tmp/sendgrid_dynamic_lists}"
RUN_ID="$(date '+%Y%m%d_%H%M%S')"

CONTACTS_TSV="$WORKDIR/contacts_to_use.tsv"
CHUNKS_DIR="$WORKDIR/chunks"
CREATED_TSV="$WORKDIR/created_lists.tsv"

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

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

need_cmd mysql
need_cmd curl
need_cmd jq
need_cmd python3
need_cmd split

cat > "$WORKDIR/exclude_lists.txt" <<'EOF'
2025_BCS_P_BCS_2026_02_20
2025_BCS_P_Myrex_Competitors
2025_C_BCSENGINEERS_Clients
2025_Clients_Dlcossich
2025_Clients_Ecook
EOF

echo "=== 1) PREPARANDO TABLA DE LISTAS DINÁMICAS ==="

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

echo "=== 2) GENERANDO CONTACTOS ELEGIBLES DESDE MYSQL ==="
echo "Empresa: $COMPANY"
echo "Contactos por lista: $CONTACTS_PER_LIST"

mysql --batch --raw --skip-column-names \
  -h "$MYSQL_HOST" \
  -u "$MYSQL_USER" \
  -p"$MYSQL_PASS" \
  "$MYSQL_DB" <<SQL > "$CONTACTS_TSV"
SELECT DISTINCT LOWER(c.email), c.first_name, c.last_name
FROM contacts c
WHERE c.company_name = '$COMPANY'
  AND c.status = 'active'
  AND c.unsubscribed = 0
  AND NOT EXISTS (
    SELECT 1
    FROM contact_list_memberships m
    WHERE m.contact_id = c.id
      AND m.list_name IN (
        '2025_BCS_P_BCS_2026_02_20',
        '2025_BCS_P_Myrex_Competitors',
        '2025_C_BCSENGINEERS_Clients',
        '2025_Clients_Dlcossich',
        '2025_Clients_Ecook'
      )
  )
ORDER BY RAND();
SQL

TOTAL_CONTACTS="$(wc -l < "$CONTACTS_TSV" | tr -d ' ')"
echo "Contactos elegibles: $TOTAL_CONTACTS"

if [[ "$TOTAL_CONTACTS" -eq 0 ]]; then
  echo "ERROR: no hay contactos elegibles."
  exit 1
fi

echo "=== 3) DIVIDIENDO EN CHUNKS ==="

split -l "$CONTACTS_PER_LIST" -d -a 4 "$CONTACTS_TSV" "$CHUNKS_DIR/chunk_"

TOTAL_LISTS="$(find "$CHUNKS_DIR" -type f -name 'chunk_*' | wc -l | tr -d ' ')"
echo "Listas a crear: $TOTAL_LISTS"

echo -e "sendgrid_list_id\tlist_name\tcontact_count" > "$CREATED_TSV"

echo "=== 4) CREANDO LISTAS EN SENDGRID Y AGREGANDO CONTACTOS ==="

LIST_NUM=0

for CHUNK_FILE in "$CHUNKS_DIR"/chunk_*; do
  LIST_NUM=$((LIST_NUM + 1))
  LIST_NAME="${PREFIX}_${RUN_ID}_${LIST_NUM}"
  CONTACT_COUNT="$(wc -l < "$CHUNK_FILE" | tr -d ' ')"

  echo "Lista $LIST_NUM/$TOTAL_LISTS: $LIST_NAME ($CONTACT_COUNT contactos)"

  CREATE_RESP="$(
    curl -sS -X POST "https://api.sendgrid.com/v3/marketing/lists" \
      -H "Authorization: Bearer $SENDGRID_API_KEY" \
      -H "Content-Type: application/json" \
      --data "$(jq -n --arg name "$LIST_NAME" '{name:$name}')"
  )"

  LIST_ID="$(echo "$CREATE_RESP" | jq -r '.id // empty')"

  if [[ -z "$LIST_ID" ]]; then
    echo "ERROR creando lista:"
    echo "$CREATE_RESP" | jq .
    exit 1
  fi

  echo "  SendGrid List ID: $LIST_ID"

  SPLIT_DIR="$WORKDIR/batches_${LIST_NUM}"
  mkdir -p "$SPLIT_DIR"
  split -l "$BATCH_SIZE" -d -a 4 "$CHUNK_FILE" "$SPLIT_DIR/batch_"

  BATCH_NUM=0

  for BATCH_FILE in "$SPLIT_DIR"/batch_*; do
    BATCH_NUM=$((BATCH_NUM + 1))
    BATCH_COUNT="$(wc -l < "$BATCH_FILE" | tr -d ' ')"

    echo "  Batch $BATCH_NUM ($BATCH_COUNT contactos)"

    PAYLOAD="$WORKDIR/payload_${LIST_NUM}_${BATCH_NUM}.json"

    python3 - "$BATCH_FILE" "$LIST_ID" "$PAYLOAD" <<'PY'
import json
import sys

batch_file, list_id, payload_file = sys.argv[1], sys.argv[2], sys.argv[3]

contacts = []

with open(batch_file, encoding="utf-8") as f:
    for line in f:
        parts = line.rstrip("\n").split("\t")
        email = parts[0].strip().lower() if len(parts) > 0 else ""
        first = parts[1].strip() if len(parts) > 1 else ""
        last = parts[2].strip() if len(parts) > 2 else ""

        if not email:
            continue

        contacts.append({
            "email": email,
            "first_name": first,
            "last_name": last
        })

payload = {
    "list_ids": [list_id],
    "contacts": contacts
}

with open(payload_file, "w", encoding="utf-8") as out:
    json.dump(payload, out, ensure_ascii=False)
PY

    ADD_RESP="$(
      curl -sS -X PUT "https://api.sendgrid.com/v3/marketing/contacts" \
        -H "Authorization: Bearer $SENDGRID_API_KEY" \
        -H "Content-Type: application/json" \
        --data-binary "@$PAYLOAD"
    )"

    JOB_ID="$(echo "$ADD_RESP" | jq -r '.job_id // .id // empty')"

    if [[ -z "$JOB_ID" ]]; then
      echo "  ERROR agregando contactos:"
      echo "$ADD_RESP" | jq .
      exit 1
    fi

    echo "  Job ID: $JOB_ID"

    sleep 2
  done

  echo -e "${LIST_ID}\t${LIST_NAME}\t${CONTACT_COUNT}" >> "$CREATED_TSV"

  mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<SQL
INSERT INTO dynamic_sendgrid_lists
(run_id, company_name, sendgrid_list_id, list_name, contact_count, contacts_per_list)
VALUES
('$RUN_ID', '$COMPANY', '$LIST_ID', '$LIST_NAME', $CONTACT_COUNT, $CONTACTS_PER_LIST);
SQL

done

echo "=== 5) RESUMEN ==="

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" <<SQL
SELECT run_id, company_name, COUNT(*) AS listas, SUM(contact_count) AS contactos
FROM dynamic_sendgrid_lists
WHERE run_id = '$RUN_ID'
GROUP BY run_id, company_name;

SELECT list_name, sendgrid_list_id, contact_count
FROM dynamic_sendgrid_lists
WHERE run_id = '$RUN_ID'
ORDER BY id;
SQL

echo "DONE JARVIS 🚀"
echo "Run ID: $RUN_ID"
echo "Archivo creado: $CREATED_TSV"