#!/bin/bash
# process-airtable.sh — Provisiona proyectos desde Airtable con distribución automática entre 2 VPS
# Uso: ./process-airtable.sh [--dry-run] [--vps=donweb-2] [--parallel=N]
#
# FLAGS:
#   --vps=hostinger-1   Forzar VPS1 (cPanel local)
#   --vps=donweb-2      Forzar VPS2 (HestiaCP) ← para pruebas masivas
#   --parallel=N        Provisionar N proyectos simultáneamente (default: 1)
#   --dry-run           Sin cambios reales
#
# Requiere: /root/scripts/.airtable.env con:
#   AIRTABLE_TOKEN=pat...
#   AIRTABLE_BASE_ID=app...
#   AIRTABLE_TABLE_ID=tbl...

set -uo pipefail

########################################
# LOCK — evitar ejecuciones concurrentes
########################################

LOCK_FILE="/tmp/process-airtable.lock"
MAX_RUNTIME=1200  # 1 hora máximo

if [[ -f "$LOCK_FILE" ]]; then
  PID=$(cat "$LOCK_FILE")
  if kill -0 "$PID" 2>/dev/null; then
    LOCK_AGE=$(( $(date +%s) - $(stat -c %Y "$LOCK_FILE") ))
    if [[ "$LOCK_AGE" -gt "$MAX_RUNTIME" ]]; then
      echo "WARN: Proceso ${PID} lleva ${LOCK_AGE}s — matando por timeout..." >&2
      kill -9 "$PID" 2>/dev/null || true
      rm -f "$LOCK_FILE"
    else
      echo "Ya hay una instancia corriendo (PID: ${PID}, hace ${LOCK_AGE}s). Saliendo."
      exit 0
    fi
  else
    echo "Lock obsoleto (PID ${PID} ya no existe), limpiando..."
    rm -f "$LOCK_FILE"
  fi
fi

echo $$ > "$LOCK_FILE"
trap "rm -f ${LOCK_FILE}" EXIT INT TERM

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SETUP_SCRIPT="${SCRIPT_DIR}/setup_cd_project4.sh"
CONFIG_FILE="/root/scripts/.airtable.env"

########################################
# CONFIGURACIÓN BI VPS
########################################

VPS1_IP="72.61.45.136"
VPS2_IP="179.43.120.113"
VPS2_PORT="5651"
VPS1_NAME="VPS Hostinger 1"
VPS2_NAME="VPS Donweb 2"

########################################
# FLAGS — parseo de argumentos
########################################

DRY_RUN=false
FORCE_VPS=""        # vacío = selección automática
MAX_PARALLEL=1      # default: secuencial

for arg in "$@"; do
  case "$arg" in
    --dry-run)
      DRY_RUN=true
      ;;
    --vps=hostinger-1)
      FORCE_VPS="${VPS1_NAME}|${VPS1_IP}"
      echo "⚡ VPS forzado: ${VPS1_NAME} (${VPS1_IP})" >&2
      ;;
    --vps=donweb-2)
      FORCE_VPS="${VPS2_NAME}|${VPS2_IP}"
      echo "⚡ VPS forzado: ${VPS2_NAME} (${VPS2_IP}) — HestiaCP" >&2
      ;;
    --parallel=*)
      MAX_PARALLEL="${arg#--parallel=}"
      if ! [[ "$MAX_PARALLEL" =~ ^[0-9]+$ ]] || [[ "$MAX_PARALLEL" -lt 1 ]]; then
        echo "ERROR: --parallel debe ser un número >= 1" >&2
        exit 1
      fi
      echo "⚡ Paralelismo: ${MAX_PARALLEL} provisiones simultáneas" >&2
      ;;
    *)
      echo "WARN: Flag desconocido ignorado: ${arg}" >&2
      ;;
  esac
done

########################################
# CHEQUEO DE ROOT
########################################

if [[ "$(id -u)" -ne 0 ]]; then
  echo "Este script debe ejecutarse como root." >&2
  exit 1
fi

########################################
# DEPENDENCIAS
########################################

for cmd in curl python3 jq openssl ssh; do
  if ! command -v "$cmd" &>/dev/null; then
    echo "ERROR: '$cmd' no está instalado." >&2
    exit 1
  fi
done

if [[ ! -x "$SETUP_SCRIPT" ]]; then
  echo "ERROR: setup_cd_project4.sh no encontrado o no ejecutable: ${SETUP_SCRIPT}" >&2
  exit 1
fi

########################################
# CARGAR CONFIG DE AIRTABLE
########################################

if [[ -f "$CONFIG_FILE" ]]; then
  source "$CONFIG_FILE"
fi

AIRTABLE_TOKEN="${AIRTABLE_TOKEN:-}"
AIRTABLE_BASE_ID="${AIRTABLE_BASE_ID:-}"
AIRTABLE_TABLE_ID="${AIRTABLE_TABLE_ID:-}"

if [[ -z "$AIRTABLE_TOKEN" || -z "$AIRTABLE_BASE_ID" || -z "$AIRTABLE_TABLE_ID" ]]; then
  echo "ERROR: Faltan variables de Airtable en ${CONFIG_FILE}" >&2
  exit 1
fi

AIRTABLE_API_URL="https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/${AIRTABLE_TABLE_ID}"
SLACK_WEBHOOK_URL="${SLACK_WEBHOOK_URL:-}"

########################################
# FUNCIONES SLACK
########################################

slack_notify() {
  local msg="$1"
  if [[ -n "$SLACK_WEBHOOK_URL" ]]; then
    curl -s -X POST "$SLACK_WEBHOOK_URL" \
      -H 'Content-type: application/json' \
      -d '{"text": "'"$msg"'"}' > /dev/null 2>&1 &
  fi
}

########################################
# FUNCIONES BI VPS
########################################

get_vps1_count() {
  /usr/local/cpanel/bin/whmapi1 listaccts 2>/dev/null | grep "^      user:" | wc -l
}

get_vps2_count() {
  ssh -p "${VPS2_PORT}" -o StrictHostKeyChecking=no -o ConnectTimeout=5 \
    root@${VPS2_IP} \
    "/usr/local/hestia/bin/v-list-users 2>/dev/null | grep -v '^admin' | wc -l" \
    2>/dev/null || echo 999
}

select_target_server() {
  # Si hay VPS forzado, usarlo directamente sin consultar cuentas
  if [[ -n "${FORCE_VPS}" ]]; then
    echo "${FORCE_VPS}"
    return
  fi

  local vps2_ok=false

  if ssh -p "${VPS2_PORT}" -o StrictHostKeyChecking=no -o ConnectTimeout=5 \
    root@${VPS2_IP} "echo ok" >/dev/null 2>&1; then
    vps2_ok=true
  else
    echo "WARN: VPS2 no disponible — usando VPS1" >&2
    echo "${VPS1_NAME}|${VPS1_IP}"
    return
  fi

  local vps1_count vps2_count
  vps1_count=$(get_vps1_count)
  vps2_count=$( $vps2_ok && get_vps2_count || echo 999 )

  vps1_count="${vps1_count//[^0-9]/}"
  vps2_count="${vps2_count//[^0-9]/}"
  vps1_count="${vps1_count:-0}"
  vps2_count="${vps2_count:-999}"

  echo "  Cuentas VPS1: ${vps1_count} | VPS2: ${vps2_count}" >&2

  if [[ "${vps2_count}" -lt "${vps1_count}" ]]; then
    echo "${VPS2_NAME}|${VPS2_IP}"
  else
    echo "${VPS1_NAME}|${VPS1_IP}"
  fi
}

run_setup_on_server() {
  local target_ip="$1"
  local base_name="$2"
  local email="$3"
  local title="$4"
  local slug="$5"
  local password="$6"
  local domain="$7"

  if [[ "$target_ip" == "$VPS1_IP" ]]; then
    TARGET_SERVER_IP="${VPS1_IP}" \
    BEWPRO_VENDOR_CACHE="${BEWPRO_VENDOR_CACHE:-}" \
    bash "$SETUP_SCRIPT" "$base_name" "$email" "$title" "$slug" "$password" "$domain"

  else
    # VPS2 — HestiaCP
    ssh -p "${VPS2_PORT}" \
      -o StrictHostKeyChecking=no \
      -o ConnectTimeout=30 \
      -o ServerAliveInterval=30 \
      -o ServerAliveCountMax=3 \
      root@${target_ip} \
      "export PATH=/usr/local/hestia/bin:/usr/local/bin:\$PATH; \
       TARGET_SERVER_IP='${target_ip}' CLEAN_PROVISION='${CLEAN_PROVISION:-false}' BRAND_COLORS='${BRAND_COLORS:-}' \
       BEWPRO_VENDOR_CACHE='${BEWPRO_VENDOR_CACHE:-}' \
       bash /root/scripts/setup_cd_project_hestia4.sh \
       '$base_name' '$email' '$title' '$slug' '$password' '$domain'"
  fi
}

########################################
# FUNCIONES AIRTABLE
########################################

airtable_get_required() {
  local filter
  filter=$(python3 -c "import urllib.parse; print(urllib.parse.quote('{Pipeline_Status}=\"Required\"'))")
  curl -s \
    -H "Authorization: Bearer ${AIRTABLE_TOKEN}" \
    -H "Content-Type: application/json" \
    "${AIRTABLE_API_URL}?filterByFormula=${filter}&sort%5B0%5D%5Bfield%5D=Name&sort%5B0%5D%5Bdirection%5D=asc"
}

airtable_patch() {
  local record_id="$1"
  local payload="$2"
  curl -s -X PATCH \
    -H "Authorization: Bearer ${AIRTABLE_TOKEN}" \
    -H "Content-Type: application/json" \
    -d "$payload" \
    "${AIRTABLE_API_URL}/${record_id}"
}

airtable_mark_processing() {
  local record_id="$1"
  airtable_patch "$record_id" '{"fields":{"Pipeline_Status":"Processing"}}'
}

airtable_mark_failed() {
  local record_id="$1"
  airtable_patch "$record_id" '{"fields":{"Pipeline_Status":"Failed"}}'
}

airtable_mark_retry() {
  local record_id="$1"
  local current_notes="$2"
  local current_count
  if echo "$current_notes" | grep -qE 'RETRY=[0-9]+'; then
    current_count=$(echo "$current_notes" | grep -oE 'RETRY=[0-9]+' | tail -1 | cut -d= -f2)
  else
    current_count=0
  fi
  local new_count=$((current_count + 1))
  local new_notes
  if [[ -n "$current_notes" ]]; then
    new_notes=$(echo "$current_notes" | sed 's/RETRY=[0-9]*//g' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')
    new_notes="${new_notes}\nRETRY=${new_count}"
  else
    new_notes="RETRY=${new_count}"
  fi
  local payload
  payload=$(python3 -c "
import json, sys
data = {'fields': {'Pipeline_Status': 'Required', 'Notes': sys.argv[1]}}
print(json.dumps(data))
" "$new_notes")
  airtable_patch "$record_id" "$payload"
  echo "$new_count"
}

MAX_RETRIES=2

airtable_mark_on_development() {
  local record_id="$1"
  local db_name="$2"
  local password="$3"
  local cpanel_user="$4"
  local app_url="$5"
  local server_name="$6"

  local grace_end
  grace_end=$(date -d "+15 days" '+%Y-%m-%d' 2>/dev/null || date -v+15d '+%Y-%m-%d' 2>/dev/null || "")

  local payload
  payload=$(python3 -c "
import json, sys
data = {
    'fields': {
        'Pipeline_Status': 'On Development',
        'Cpanel_User': sys.argv[1],
        'Provisioned_DB': sys.argv[2],
        'Provisioned_Password': sys.argv[3],
        'Domain': sys.argv[4],
        'SERVER': sys.argv[6]
    }
}
if sys.argv[5]:
    data['fields']['Grace_Period_End'] = sys.argv[5]
print(json.dumps(data))
" "$cpanel_user" "$db_name" "$password" "$app_url" "$grace_end" "$server_name")

  airtable_patch "$record_id" "$payload"
}

airtable_create_subscription() {
  local project_record_id="$1"
  local project_name="$2"
  local cpanel_user="$3"
  local app_url="$4"
  local stripe_customer_id="${5:-}"
  local amount_usd="${6:-}"
  local grace_end="${7:-}"
  local stripe_subscription_id="${8:-}"

  local existing_record_id=""
  local search_url="https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/tblnpr52JhFBBi2Mg"

  if [[ -n "$stripe_subscription_id" ]]; then
    local search_response
    search_response=$(curl -s -G \
      -H "Authorization: Bearer ${AIRTABLE_TOKEN}" \
      --data-urlencode "filterByFormula={Stripe_Subscription_ID}=\"${stripe_subscription_id}\"" \
      --data-urlencode "maxRecords=1" \
      "${search_url}")
    existing_record_id=$(echo "$search_response" | python3 -c "
import sys, json
try:
    d = json.load(sys.stdin)
    recs = d.get('records', [])
    print(recs[0]['id'] if recs else '')
except: print('')
" 2>/dev/null)
  fi

  if [[ -z "$existing_record_id" && -n "$stripe_customer_id" ]]; then
    local search_response
    search_response=$(curl -s -G \
      -H "Authorization: Bearer ${AIRTABLE_TOKEN}" \
      --data-urlencode "filterByFormula={Stripe_Customer_ID}=\"${stripe_customer_id}\"" \
      --data-urlencode "maxRecords=1" \
      "${search_url}")
    existing_record_id=$(echo "$search_response" | python3 -c "
import sys, json
try:
    d = json.load(sys.stdin)
    recs = d.get('records', [])
    if recs and not recs[0].get('fields', {}).get('Stripe_Subscription_ID'):
        print(recs[0]['id'])
    else:
        print('')
except: print('')
" 2>/dev/null)
  fi

  if [[ -n "$existing_record_id" ]]; then
    local payload
    payload=$(python3 -c "
import json, sys
data = {
    'fields': {
        'Status': 'Active',
        'Payment_Status': 'Paid',
        'Payment_Method': 'Stripe',
        'Plan': 'Monthly',
        'Provisioned_At': __import__('datetime').date.today().isoformat(),
        'Cpanel_User': sys.argv[1],
        'App_URL': sys.argv[2],
        'Project': sys.argv[3],
        'Copia de Project': [sys.argv[4]],
    }
}
if sys.argv[5]:
    try:
        data['fields']['Amount_USD'] = float(sys.argv[5])
    except: pass
if sys.argv[6]:
    data['fields']['Grace_Period_End'] = sys.argv[6]
print(json.dumps(data))
" "$cpanel_user" "$app_url" "$project_name" "$project_record_id" "$amount_usd" "$grace_end")

    curl -s -X PATCH \
      -H "Authorization: Bearer ${AIRTABLE_TOKEN}" \
      -H "Content-Type: application/json" \
      -d "$payload" \
      "https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/tblnpr52JhFBBi2Mg/${existing_record_id}"
  else
    local payload
    payload=$(python3 -c "
import json, sys
data = {
    'fields': {
        'Project': sys.argv[1],
        'Copia de Project': [sys.argv[2]],
        'Billing_Model': 'Stripe',
        'Status': 'Active',
        'Payment_Status': 'Paid',
        'Payment_Method': 'Stripe',
        'Plan': 'Monthly',
        'Provisioned_At': __import__('datetime').date.today().isoformat(),
        'Cpanel_User': sys.argv[3],
        'App_URL': sys.argv[4],
    }
}
if sys.argv[5]:
    data['fields']['Stripe_Customer_ID'] = sys.argv[5]
if sys.argv[6]:
    try:
        data['fields']['Amount_USD'] = float(sys.argv[6])
    except: pass
if sys.argv[7]:
    data['fields']['Grace_Period_End'] = sys.argv[7]
print(json.dumps(data))
" "$project_name" "$project_record_id" "$cpanel_user" "$app_url" \
  "$stripe_customer_id" "$amount_usd" "$grace_end")

    curl -s -X POST \
      -H "Authorization: Bearer ${AIRTABLE_TOKEN}" \
      -H "Content-Type: application/json" \
      -d "$payload" \
      "https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/tblnpr52JhFBBi2Mg"
  fi
}

########################################
# PARSEAR REGISTROS DE AIRTABLE
########################################

parse_records() {
  local json="$1"

  python3 - "$json" <<'PYEOF'
import sys, json, re

raw = sys.argv[1]
try:
    data = json.loads(raw)
except Exception as e:
    print(f"ERROR parsing JSON: {e}", file=sys.stderr)
    sys.exit(1)

records = data.get('records', [])
if not records:
    print("NO_RECORDS")
    sys.exit(0)

def make_base_name(title, record_id):
    base = re.sub(r'[^a-z0-9]', '', title.lower())[:5]
    suffix = re.sub(r'[^a-z0-9]', '', record_id.lower())[-3:]
    result = (base + suffix)[:8]
    if result and result[0].isdigit():
        result = 'x' + result[:7]
    return result

def make_subdomain(title):
    slug = re.sub(r'[^a-z0-9]+', '-', title.lower().strip()).strip('-')
    return slug[:63]

def normalize_domain(domain, slug):
    d = domain.split('\n')[0].strip()
    if not d or d in ('---', '-', 'N/A', 'n/a'):
        return f"https://{slug}.bewpro.com"
    if not re.match(r'^https?://[a-zA-Z0-9]', d) and not re.match(r'^[a-zA-Z0-9][\w.-]+\.[a-zA-Z]{2,}$', d):
        return f"https://{slug}.bewpro.com"
    if not d.startswith('http'):
        return f"https://{d}"
    return d

for r in records:
    fields = r.get('fields', {})
    record_id   = r.get('id', '')
    name        = fields.get('Name', '').strip()
    cpanel_user = fields.get('Cpanel_User', '').strip()
    email       = fields.get('Email', '').strip()

    slug_raw = fields.get('Slug (from Product)', [])
    if isinstance(slug_raw, list):
        slug = slug_raw[0].strip() if slug_raw else ''
    else:
        slug = str(slug_raw).strip()
    if not slug:
        slug = fields.get('Slug', '').strip()

    domain_raw = fields.get('Domain', '').strip()
    notes      = fields.get('Notes', '').strip()

    clean  = 'false'
    colors = ''
    for line in notes.split('\n'):
        line = line.strip()
        if line.startswith('CLEAN='):
            clean = line.split('=', 1)[1].strip().lower()
        elif line.startswith('COLORS='):
            colors = line.split('=', 1)[1].strip()

    if not name or not slug:
        print(f"SKIP|{record_id}|{name}|missing name or slug", file=sys.stderr)
        continue

    base_name = cpanel_user if cpanel_user else make_base_name(name, record_id)
    if not email:
        email = f"admin@{base_name}.bewpro.com"

    subdomain = make_subdomain(name) if not domain_raw.strip() or domain_raw.strip() in ('---', '-', 'N/A', 'n/a') else None
    domain = normalize_domain(domain_raw, subdomain or make_subdomain(name))

    print(f"{record_id}|{name}|{base_name}|{email}|{slug}|{domain}|{clean}|{colors}")
PYEOF
}

########################################
# MAIN
########################################

echo "============================================="
echo "  PROCESS AIRTABLE BI VPS — Provisioning"
if $DRY_RUN; then
  echo "  MODE: DRY RUN (sin cambios reales)"
fi
[[ -n "${FORCE_VPS}" ]] && echo "  VPS FORZADO: ${FORCE_VPS%%|*}"
[[ "$MAX_PARALLEL" -gt 1 ]] && echo "  PARALELO: ${MAX_PARALLEL} simultáneos"
echo "============================================="
echo ""

echo "Consultando Airtable (Pipeline_Status = Required)..."
RESPONSE=$(airtable_get_required)

if echo "$RESPONSE" | python3 -c \
  "import sys,json; d=json.load(sys.stdin); sys.exit(0 if 'records' in d else 1)" 2>/dev/null; then
  :
else
  echo "ERROR: Respuesta inesperada de Airtable:" >&2
  echo "$RESPONSE" >&2
  exit 1
fi

RECORDS=$(parse_records "$RESPONSE")

if [[ "$RECORDS" == "NO_RECORDS" || -z "$RECORDS" ]]; then
  echo "No hay proyectos con Pipeline_Status = 'Required'. Nada que hacer."
  exit 0
fi

COUNT=$(echo "$RECORDS" | grep -c '.' || true)
COUNT="${COUNT//[^0-9]/}"
COUNT="${COUNT:-0}"
echo "Proyectos a provisionar: ${COUNT}"
echo ""

RESULTS_FILE=$(mktemp /tmp/provision-results-XXXXXX)
trap "rm -f ${RESULTS_FILE} ${LOCK_FILE}" EXIT INT TERM

# ── Función que encapsula el provision de UN record ─────────────────────────
provision_record() {
  local RECORD_ID="$1" NAME="$2" BASE_NAME="$3" EMAIL="$4"
  local SLUG="$5" DOMAIN="$6" CLEAN_FLAG="$7" COLORS_VAL="$8"
  local PREFIX="[${NAME}]"

  local SERVER_SELECTION TARGET_SERVER TARGET_IP
  SERVER_SELECTION=$(select_target_server)
  TARGET_SERVER=$(echo "$SERVER_SELECTION" | cut -d'|' -f1)
  TARGET_IP=$(echo "$SERVER_SELECTION" | cut -d'|' -f2)

  # HestiaCP (VPS2): truncar username y garantizar unicidad
  if [[ "$TARGET_IP" == "$VPS2_IP" ]]; then
    if [[ "${#BASE_NAME}" -gt 8 ]]; then
      echo "${PREFIX} WARN: base_name '${BASE_NAME}' tiene ${#BASE_NAME} chars, truncando a 8..." >&2
      BASE_NAME="${BASE_NAME:0:8}"
    fi
    CANDIDATE="${BASE_NAME}"
    COUNTER=1
    while ssh -p "${VPS2_PORT}" -o StrictHostKeyChecking=no -o ConnectTimeout=5 \
        root@${VPS2_IP} "id '${CANDIDATE}'" >/dev/null 2>&1; do
      SUFFIX="${COUNTER}"
      MAX_BASE=$((8 - ${#SUFFIX}))
      CANDIDATE="${BASE_NAME:0:${MAX_BASE}}${SUFFIX}"
      COUNTER=$((COUNTER + 1))
      if [[ $COUNTER -gt 99 ]]; then
        echo "${PREFIX} ERROR: no se pudo generar username único para HestiaCP" >&2
        echo "FAILED:${NAME}" >> "$RESULTS_FILE"
        return 1
      fi
    done
    if [[ "$CANDIDATE" != "$BASE_NAME" ]]; then
      echo "${PREFIX} Username '${BASE_NAME}' ya existe en VPS2, usando '${CANDIDATE}'" >&2
      BASE_NAME="${CANDIDATE}"
    fi
  fi

  echo "---------------------------------------------"
  echo "${PREFIX} Proyecto:  ${NAME}"
  echo "${PREFIX} base_name: ${BASE_NAME}"
  echo "${PREFIX} Email:     ${EMAIL}"
  echo "${PREFIX} Slug:      ${SLUG}"
  echo "${PREFIX} Domain:    ${DOMAIN}"
  echo "${PREFIX} Servidor:  ${TARGET_SERVER} (${TARGET_IP})"
  echo "${PREFIX} Clean:     ${CLEAN_FLAG:-false}"
  echo "${PREFIX} Colors:    ${COLORS_VAL:-default}"
  echo ""

  local PASSWORD NAME_SAFE
  PASSWORD=$(openssl rand -base64 12 | tr -d '/+=')
  NAME_SAFE=$(echo "$NAME" | sed "s/'/'\\\\''/g")

  if $DRY_RUN; then
    echo "${PREFIX} [DRY RUN] setup en ${TARGET_SERVER}: ${BASE_NAME} ${EMAIL} \"${NAME}\" ${SLUG} ${DOMAIN}"
    echo "DRY:${NAME}" >> "$RESULTS_FILE"
    return 0
  fi

  echo "${PREFIX} Marcando como Processing en Airtable..."
  airtable_mark_processing "$RECORD_ID" > /dev/null

  export CLEAN_PROVISION="${CLEAN_FLAG:-false}"
  export BRAND_COLORS="${COLORS_VAL:-}"

  local APP_URL=""
  if APP_URL=$(run_setup_on_server \
    "$TARGET_IP" "$BASE_NAME" "$EMAIL" "$NAME_SAFE" "$SLUG" "$PASSWORD" "$DOMAIN"); then

    echo "${PREFIX} OK — ${APP_URL}"

    echo "${PREFIX} Actualizando Airtable..."
    PATCH_RESULT=$(airtable_mark_on_development \
      "$RECORD_ID" "${BASE_NAME}_bp" "$PASSWORD" "$BASE_NAME" "$APP_URL" "$TARGET_SERVER")

    if echo "$PATCH_RESULT" | python3 -c \
      "import sys,json; d=json.load(sys.stdin); sys.exit(0 if 'id' in d else 1)" 2>/dev/null; then
      echo "${PREFIX} Project: Pipeline_Status=On Development, Server=${TARGET_SERVER}"
    else
      echo "${PREFIX} WARN: Project PATCH falló:" >&2
      echo "$PATCH_RESULT" >&2
    fi

    local STRIPE_FIELDS STRIPE_CID AMOUNT GRACE_END STRIPE_SUB_ID SUB_RESULT
    STRIPE_FIELDS=$(echo "$RESPONSE" | python3 -c "
import sys,json
data = json.load(sys.stdin)
for r in data.get('records',[]):
    if r['id'] == sys.argv[1]:
        f = r.get('fields',{})
        print('|'.join([
            f.get('Stripe_Customer_ID',''),
            str(f.get('Amount_USD','')),
            f.get('Grace_Period_End',''),
            f.get('Stripe_Subscription_ID',''),
        ]))
        break
" "$RECORD_ID" 2>/dev/null || echo "|||")

    STRIPE_CID=$(echo "$STRIPE_FIELDS" | cut -d'|' -f1)
    AMOUNT=$(echo "$STRIPE_FIELDS" | cut -d'|' -f2)
    GRACE_END=$(echo "$STRIPE_FIELDS" | cut -d'|' -f3)
    STRIPE_SUB_ID=$(echo "$STRIPE_FIELDS" | cut -d'|' -f4)

    if [[ -z "$GRACE_END" ]]; then
      GRACE_END=$(date -d '+15 days' '+%Y-%m-%d' 2>/dev/null || date -v+15d '+%Y-%m-%d' 2>/dev/null || '')
    fi

    SUB_RESULT=$(airtable_create_subscription \
      "$RECORD_ID" "$NAME" "$BASE_NAME" "$APP_URL" "$STRIPE_CID" "$AMOUNT" "$GRACE_END" "$STRIPE_SUB_ID")

    if echo "$SUB_RESULT" | python3 -c \
      "import sys,json; d=json.load(sys.stdin); sys.exit(0 if 'id' in d else 1)" 2>/dev/null; then
      echo "${PREFIX} Subscription: creada/actualizada (Stripe/Active)"
    else
      echo "${PREFIX} WARN: Subscription no creada:" >&2
      echo "$SUB_RESULT" >&2
    fi

    # ── IMPORT JSON post-provisión ───────────────────────────────────────
    local STASH_DIR="/home/bewpro22/public_html/bewpro/storage/app/reseller-imports"
    local STASH_FILE="${STASH_DIR}/${RECORD_ID}.json"
    local IMPORT_SCRIPT="${SCRIPT_DIR}/apply-reseller-import.sh"

    if [[ -f "$STASH_FILE" ]]; then
      echo "${PREFIX} Stash JSON encontrado — aplicando import..."

      if [[ "$TARGET_IP" == "$VPS1_IP" ]]; then
        # VPS1 (cPanel) — ejecución local directa
        if [[ -x "$IMPORT_SCRIPT" ]]; then
          bash "$IMPORT_SCRIPT" \
            "$RECORD_ID" "$BASE_NAME" "$AIRTABLE_TOKEN" "$AIRTABLE_BASE_ID" 2>&1 \
            | sed "s/^/${PREFIX} [import] /" \
            && echo "${PREFIX} Import JSON: OK" \
            || echo "${PREFIX} WARN: Import JSON falló — ver Import_Status en Airtable" >&2
        else
          echo "${PREFIX} WARN: apply-reseller-import.sh no encontrado en ${IMPORT_SCRIPT}" >&2
        fi

      else
        # VPS2 (HestiaCP) — SCP del stash + ejecución remota
        local HESTIA_DOMAIN_IMPORT
        HESTIA_DOMAIN_IMPORT=$(echo "${DOMAIN}" | sed 's|https://||' | sed 's|/.*||')
        local REMOTE_TENANT_DIR="/home/${BASE_NAME}/web/${HESTIA_DOMAIN_IMPORT}/public_html/git-files/${BASE_NAME}"

        echo "${PREFIX} [import] Transfiriendo stash a VPS2 via SSH..."
        if ssh vps3 "cat > /tmp/${RECORD_ID}.json" < "$STASH_FILE"; then

          ssh vps3 "
              set -e
              TENANT_JSON='${REMOTE_TENANT_DIR}/storage/app/incoming-import.json'
              cp /tmp/${RECORD_ID}.json \"\${TENANT_JSON}\"
              chown ${BASE_NAME}:${BASE_NAME} \"\${TENANT_JSON}\"
              chmod 640 \"\${TENANT_JSON}\"
              SECTIONS=\$(python3 -c \"import json; d=json.load(open('/tmp/${RECORD_ID}.json')); print(' '.join([k for k in d.keys() if not k.startswith('_')]))\" 2>/dev/null || echo '')
              declare -A S2T=([blog]='posts post_categories' [faqs]='faqs faqs_categories' [gallery]='gallery gallery_categories gallery_tags gallery_gallery_tag' [menu]='menu_products menu_categories' [testimonials]='testimonials testimonial_categories' [services]='services service_categories' [team]='team_members team_categories' [references]='references reference_categories' [projects]='projects project_categories')
              CLEAR_SQL='SET FOREIGN_KEY_CHECKS=0;'
              for s in \$SECTIONS; do [[ -n \"\${S2T[\$s]:-}\" ]] && for t in \${S2T[\$s]}; do CLEAR_SQL=\"\${CLEAR_SQL} DELETE FROM \\\`\${t}\\\`;\"; done; done
              CLEAR_SQL=\"\${CLEAR_SQL} SET FOREIGN_KEY_CHECKS=1;\"
              mysql ${BASE_NAME}_bp -e \"\$CLEAR_SQL\" 2>&1 || true
              su -s /bin/bash '${BASE_NAME}' -c \"cd /home/${BASE_NAME}/web/${HESTIA_DOMAIN_IMPORT}/public_html/git-files/${BASE_NAME} && php artisan bewpro:seed storage/app/incoming-import.json\"
              rm -f /tmp/${RECORD_ID}.json
            " 2>&1 | sed "s/^/${PREFIX} [import-vps2] /" \
            && echo "${PREFIX} Import JSON VPS2: OK" \
            || echo "${PREFIX} WARN: Import JSON VPS2 falló" >&2
        else
          echo "${PREFIX} WARN: SCP del stash a VPS2 falló" >&2
        fi
      fi
    else
      echo "${PREFIX} Sin stash JSON — provisión sin import de contenido"
    fi
    # ────────────────────────────────────────────────────────────────────

    GRACE_END=$(date -d "+15 days" '+%Y-%m-%d' 2>/dev/null || date -v+15d '+%Y-%m-%d' 2>/dev/null || "")

    echo "${PREFIX} Enviando emails (cliente: ${EMAIL})..."

    local ARTISAN_CMD="php artisan bewpro:send-welcome '${EMAIL}' '${NAME_SAFE}' '${APP_URL}' '${PASSWORD}' --product='${SLUG}'"
    if [[ -n "${GRACE_END}" ]]; then
      ARTISAN_CMD="${ARTISAN_CMD} --grace-end='${GRACE_END}'"
    fi

    if [[ "$TARGET_IP" == "$VPS1_IP" ]]; then
      su - "${BASE_NAME}" -c "
        cd public_html/git-files/${BASE_NAME} && ${ARTISAN_CMD}
      " >&2 && echo "${PREFIX} Emails: enviados via artisan" || {
        echo "${PREFIX} WARN: artisan falló, intentando fallback..." >&2
        local PHP_BIN_GLOBAL EMAIL_SCRIPT
        PHP_BIN_GLOBAL=$(which php 2>/dev/null || echo "php")
        EMAIL_SCRIPT="${SCRIPT_DIR}/send-welcome-email.php"
        if [[ -f "$EMAIL_SCRIPT" ]]; then
          $PHP_BIN_GLOBAL "$EMAIL_SCRIPT" \
            "$EMAIL" "$NAME" "$APP_URL" "$PASSWORD" "$EMAIL" 2>/dev/null \
            && echo "${PREFIX} Email: enviado via fallback" \
            || echo "${PREFIX} WARN: email no enviado" >&2
        fi
      }

    else
      # VPS2 — HestiaCP
      local HESTIA_DOMAIN_NAME
      HESTIA_DOMAIN_NAME=$(echo "${DOMAIN}" | sed 's|https://||' | sed 's|/.*||')
      ssh -p "${VPS2_PORT}" \
        -o StrictHostKeyChecking=no \
        -o ConnectTimeout=30 \
        -o ServerAliveInterval=30 \
        -o ServerAliveCountMax=3 \
        root@${TARGET_IP} \
        "su -s /bin/bash '${BASE_NAME}' -c \"cd /home/${BASE_NAME}/web/${HESTIA_DOMAIN_NAME}/public_html/git-files/${BASE_NAME} && ${ARTISAN_CMD}\"" >&2 \
        && echo "${PREFIX} Emails: enviados via artisan en VPS2" \
        || echo "${PREFIX} WARN: email no enviado en VPS2" >&2
    fi

    slack_notify "✅ *Provisioning completado*\nProyecto: ${NAME}\nURL: ${APP_URL}\nServidor: ${TARGET_SERVER}\nProducto: ${SLUG}"
    echo "SUCCESS:${NAME}" >> "$RESULTS_FILE"
    return 0

  else
    local SETUP_EXIT=$?
    echo "${PREFIX} ERROR: Provisioning falló (exit ${SETUP_EXIT}) en ${TARGET_SERVER}." >&2

    local RETRY_COUNT
    RETRY_COUNT=$(airtable_mark_retry "$RECORD_ID" "${NOTES_VAL:-}" 2>/dev/null | tail -1)
    RETRY_COUNT=$(echo "${RETRY_COUNT}" | tr -d '\n' | tr -d ' ')
    RETRY_COUNT="${RETRY_COUNT:-99}"

    if [[ "$RETRY_COUNT" =~ ^[0-9]+$ ]] && [[ "$RETRY_COUNT" -le "$MAX_RETRIES" ]]; then
      echo "${PREFIX} Retry ${RETRY_COUNT}/${MAX_RETRIES} programado — Pipeline_Status=Required" >&2
      slack_notify "🔁 *Provisioning retry ${RETRY_COUNT}/${MAX_RETRIES}*\nProyecto: ${NAME}\nServidor: ${TARGET_SERVER}\nExit: ${SETUP_EXIT}"
    else
      echo "${PREFIX} Max retries alcanzado — Pipeline_Status=Failed" >&2
      airtable_mark_failed "$RECORD_ID" > /dev/null
      slack_notify "❌ *Provisioning FALLÓ definitivo*\nProyecto: ${NAME}\nServidor: ${TARGET_SERVER}\nProducto: ${SLUG}\nExit: ${SETUP_EXIT}"
    fi

    echo "FAILED:${NAME}" >> "$RESULTS_FILE"
    return 1
  fi
}

# ── Loop principal ───────────────────────────────────────────────────────────
RUNNING_JOBS=0
while IFS='|' read -r RECORD_ID NAME BASE_NAME EMAIL SLUG DOMAIN CLEAN_FLAG COLORS_VAL; do
  if [[ "$MAX_PARALLEL" -le 1 ]]; then
    provision_record "$RECORD_ID" "$NAME" "$BASE_NAME" "$EMAIL" "$SLUG" "$DOMAIN" "$CLEAN_FLAG" "$COLORS_VAL" || true
  else
    provision_record "$RECORD_ID" "$NAME" "$BASE_NAME" "$EMAIL" "$SLUG" "$DOMAIN" "$CLEAN_FLAG" "$COLORS_VAL" &
    RUNNING_JOBS=$((RUNNING_JOBS + 1))
    if [[ "$RUNNING_JOBS" -ge "$MAX_PARALLEL" ]]; then
      wait -n 2>/dev/null || wait
      RUNNING_JOBS=$((RUNNING_JOBS - 1))
    fi
  fi
  echo ""
done <<< "$RECORDS"

if [[ "$MAX_PARALLEL" -gt 1 ]]; then
  wait
fi

SUCCEEDED=$(grep -c "^SUCCESS:" "$RESULTS_FILE" 2>/dev/null || true)
FAILED=$(grep -c "^FAILED:" "$RESULTS_FILE" 2>/dev/null || true)
SUCCEEDED=$(echo "${SUCCEEDED}" | tr -d '\n' | tr -dc '0-9')
FAILED=$(echo "${FAILED}" | tr -d '\n' | tr -dc '0-9')
SUCCEEDED="${SUCCEEDED:-0}"
FAILED="${FAILED:-0}"

FAILED_NAMES=()
while IFS=: read -r status name; do
  [[ "$status" == "FAILED" ]] && FAILED_NAMES+=("$name")
done < "$RESULTS_FILE"

echo "============================================="
if $DRY_RUN; then
  echo "  DRY RUN completado — ${COUNT} proyecto(s) en cola"
else
  echo "  Resultado: ${SUCCEEDED} OK, ${FAILED} fallidos"
  if [[ ${#FAILED_NAMES[@]} -gt 0 ]]; then
    echo "  Fallidos:"
    for n in "${FAILED_NAMES[@]}"; do
      echo "    - ${n}"
    done
  fi
fi
echo "============================================="

if [[ "${FAILED}" -gt 0 ]] 2>/dev/null; then
  exit 1
fi
exit 0