#!/bin/bash

# SOLUCIÓN DEFINITIVA V2: SendGrid → CSV → MySQL
# Con key correcta y password MySQL

# Leer API key desde config.php
API_KEY=$(grep "define('SENDGRID_API_KEY'" /home/linder8/marketing.linderlake.com/01-config.php | grep -oP "SG\.[^']+")

CSV_FILE="/tmp/sendgrid_contacts.csv"
COMPANY="LINDERLAKE"
DB_USER="linder8_user"
DB_PASS="linder8_sendgrid_marketing"  # Password es el nombre de la BD
DB_NAME="linder8_sendgrid_marketing"

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

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

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

rm -f "$CSV_FILE"
echo "email,first_name,last_name,phone" > "$CSV_FILE"

PAGE=1
TOTAL=0

while true; do
  echo "  Página $PAGE..."
  
  RESPONSE=$(curl -s -H "Authorization: Bearer $API_KEY" \
    "https://api.sendgrid.com/v3/marketing/contacts?page_size=500")
  
  # Verificar HTTP
  HTTP=$(echo "$RESPONSE" | head -1)
  if [[ "$HTTP" == *"401"* ]] || [[ "$HTTP" == *"unauthorized"* ]]; then
    echo "❌ Error 401: API key inválida"
    echo "API Key: $API_KEY"
    exit 1
  fi
  
  # Contar contactos
  COUNT=$(echo "$RESPONSE" | jq '.result | length' 2>/dev/null || echo 0)
  
  if [ "$COUNT" -eq 0 ]; then
    echo "✓ Fin"
    break
  fi
  
  # Extraer contactos
  echo "$RESPONSE" | jq -r '.result[] | 
    select(.request_status != "unsubscribed") | 
    [.email, .first_name // "", .last_name // "", .phone_number // ""] | 
    @csv' 2>/dev/null >> "$CSV_FILE"
  
  TOTAL=$((TOTAL + COUNT))
  echo "  ✓ $COUNT contactos (acumulado: $TOTAL)"
  
  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 "$DB_USER" -p"$DB_PASS" "$DB_NAME" << 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\n"
else
  echo "❌ Error en importación\n"
fi

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

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

COUNT=$(mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -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 ""