#!/bin/bash

# SOLUCIÓN DEFINITIVA: SendGrid → CSV → MySQL
# Sin PHP, sin curl individual, sin problemas

API_KEY="SG.0-T4ZhIHTN6Kuyhobx0uBQ"
CSV_FILE="/tmp/sendgrid_contacts.csv"
COMPANY="LINDERLAKE"

echo ""
echo "╔════════════════════════════════════════════════════════════╗"
echo "║  SOLUCIÓN DEFINITIVA: SendGrid → CSV → MySQL              ║"
echo "╚════════════════════════════════════════════════════════════╝"
echo ""

# ════════════════════════════════════════════════════════════════════════════
# PASO 1: EXPORTAR CONTACTOS DE SENDGRID A CSV
# ════════════════════════════════════════════════════════════════════════════

echo "[PASO 1] Exportando contactos de SendGrid a CSV"
echo "─────────────────────────────────────────────────────────────"

# Limpiar CSV anterior
rm -f "$CSV_FILE"
echo "email,first_name,last_name,phone" > "$CSV_FILE"

PAGE=1
TOTAL=0
PAGE_TOKEN=""

while true; do
  echo "  Página $PAGE..."
  
  URL="https://api.sendgrid.com/v3/marketing/contacts?page_size=500"
  if [ ! -z "$PAGE_TOKEN" ]; then
    URL="$URL&page_token=$(echo -n "$PAGE_TOKEN" | jq -sRr @uri)"
  fi
  
  RESPONSE=$(curl -s -H "Authorization: Bearer $API_KEY" "$URL")
  
  # Contar contactos
  COUNT=$(echo "$RESPONSE" | jq '.result | length')
  
  if [ "$COUNT" -eq 0 ]; then
    echo "✓ Fin de paginación"
    break
  fi
  
  # Extraer contactos (solo activos)
  echo "$RESPONSE" | jq -r '.result[] | 
    select(.request_status != "unsubscribed") | 
    [.email, .first_name // "", .last_name // "", .phone_number // ""] | 
    @csv' >> "$CSV_FILE"
  
  TOTAL=$((TOTAL + COUNT))
  echo "  ✓ $COUNT contactos (acumulado: $TOTAL)"
  
  # Siguiente página
  PAGE_TOKEN=$(echo "$RESPONSE" | jq -r '._metadata.next // empty')
  
  if [ -z "$PAGE_TOKEN" ]; then
    break
  fi
  
  PAGE=$((PAGE + 1))
  sleep 1
done

FINAL_COUNT=$(tail -n +2 "$CSV_FILE" | wc -l)
echo "✓ Total en CSV: $FINAL_COUNT\n"

# ════════════════════════════════════════════════════════════════════════════
# PASO 2: IMPORTAR CSV A MYSQL
# ════════════════════════════════════════════════════════════════════════════

echo "[PASO 2] Importando CSV a MySQL"
echo "─────────────────────────────────────────────────────────────"

mysql -u linder8_user linder8_sendgrid_marketing << EOFMYSQL
LOAD DATA LOCAL INFILE '$CSV_FILE'
INTO TABLE contacts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(email, first_name, last_name, phone)
SET company_name = '$COMPANY',
    status = 'active',
    unsubscribed = 0,
    created_at = NOW();
EOFMYSQL

if [ $? -eq 0 ]; then
  echo "✓ Importación exitosa"
else
  echo "❌ Error en importación"
  exit 1
fi

echo ""

# ════════════════════════════════════════════════════════════════════════════
# PASO 3: VERIFICAR
# ════════════════════════════════════════════════════════════════════════════

echo "[PASO 3] Verificando"
echo "─────────────────────────────────────────────────────────────"

COUNT=$(mysql -u linder8_user linder8_sendgrid_marketing -se "SELECT COUNT(*) FROM contacts WHERE company_name='$COMPANY' AND status='active' AND unsubscribed=0;")

echo "✓ Contactos en MySQL: $COUNT"
echo ""

echo "╔════════════════════════════════════════════════════════════╗"
echo "║              ✅ IMPORTACIÓN COMPLETADA                    ║"
echo "╚════════════════════════════════════════════════════════════╝"
echo ""