#!/bin/bash

# SCRIPT DEFINITIVO: SendGrid → MySQL
# - Solo contactos ACTIVOS (sin unsubscribed)
# - Sin repetidos
# - Importa directo sin CSV

API_KEY=$(grep "define('SENDGRID_API_KEY'" /home/linder8/marketing.linderlake.com/01-config.php | grep -oP "SG\.[^']+")
DB_USER="linder8_sendgrid_marketing"
DB_PASS="Jesdam1305@"
DB_NAME="linder8_sendgrid_marketing"
COMPANY="LINDERLAKE"

echo ""
echo "╔════════════════════════════════════════════════════════════╗"
echo "║  IMPORTAR CONTACTOS ACTIVOS DE SENDGRID → MYSQL            ║"
echo "╚════════════════════════════════════════════════════════════╝"
echo ""

# ════════════════════════════════════════════════════════════════════════════
# PASO 1: LIMPIAR TABLA
# ════════════════════════════════════════════════════════════════════════════

echo "[PASO 1] Limpiando tabla contacts"
echo "─────────────────────────────────────────────────────────────"

mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" << EOF > /dev/null
DELETE FROM contacts WHERE company_name='$COMPANY';
EOF

echo "✓ Tabla limpiada\n"

# ════════════════════════════════════════════════════════════════════════════
# PASO 2: OBTENER CONTACTOS E IMPORTAR
# ════════════════════════════════════════════════════════════════════════════

echo "[PASO 2] Importando contactos de SendGrid"
echo "─────────────────────────────────────────────────────────────"

TOTAL=0
PAGE=1
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=$PAGE_TOKEN"
  fi
  
  RESPONSE=$(curl -s -H "Authorization: Bearer $API_KEY" "$URL")
  
  # Contar contactos
  COUNT=$(echo "$RESPONSE" | jq '.result | length' 2>/dev/null || echo 0)
  
  if [ "$COUNT" -eq 0 ]; then
    echo "✓ Fin"
    break
  fi
  
  echo "  $COUNT contactos en página..."
  
  # Extraer SOLO activos (sin unsubscribed) e insertar directamente
  echo "$RESPONSE" | jq -r '.result[] | 
    select(.request_status != "unsubscribed") | 
    [.email, .first_name // "", .last_name // ""] | 
    @tsv' 2>/dev/null | while IFS=$'\t' read email first_name last_name; do
    
    # Escapar para SQL
    email=$(echo "$email" | sed "s/'/''/g")
    first_name=$(echo "$first_name" | sed "s/'/''/g")
    last_name=$(echo "$last_name" | sed "s/'/''/g")
    
    # Insertar directo en MySQL
    mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" << EOFMYSQL > /dev/null 2>&1
INSERT INTO contacts (email, first_name, last_name, company_name, status, unsubscribed, created_at)
VALUES ('$email', '$first_name', '$last_name', '$COMPANY', 'active', 0, NOW())
ON DUPLICATE KEY UPDATE 
first_name = '$first_name',
last_name = '$last_name';
EOFMYSQL
    
    TOTAL=$((TOTAL + 1))
  done
  
  echo "  ✓ Acumulado: $TOTAL"
  
  # Siguiente página
  PAGE_TOKEN=$(echo "$RESPONSE" | jq -r '._metadata.next // empty' 2>/dev/null)
  
  if [ -z "$PAGE_TOKEN" ]; then
    break
  fi
  
  PAGE=$((PAGE + 1))
  sleep 1
done

echo ""

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

echo "[PASO 3] Verificación"
echo "─────────────────────────────────────────────────────────────"

COUNT=$(mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -se "SELECT COUNT(*) FROM contacts WHERE company_name='$COMPANY' AND status='active';")

echo "✓ Contactos importados: $COUNT"
echo ""

echo "╔════════════════════════════════════════════════════════════╗"
echo "║              ✅ IMPORTACIÓN COMPLETADA                    ║"
echo "║                                                            ║"
echo "║  Contactos activos: $COUNT                                      ║"
echo "║  Company: $COMPANY                                               ║"
echo "║  Status: active                                           ║"
echo "║  Unsubscribed: 0                                          ║"
echo "╚════════════════════════════════════════════════════════════╝"
echo ""