#!/bin/sh
# Vuelca el contenido de la tienda a ficheros de texto, para poder leerlo.
#
# Las credenciales salen de wp-config.php y se quedan en variables de este
# script: no se imprimen, no se pasan por la linea de ordenes de mysql —donde
# cualquiera con `ps` las veria— sino por un fichero temporal de opciones con
# permisos 600 que se borra al terminar.
set -e

WP=~/tienda/wp-config.php
saca() { grep -oE "define\( *'$1', *'[^']*'" "$WP" | sed "s/.*, *'//;s/'$//"; }

NOMBRE=$(saca DB_NAME)
USUARIO=$(saca DB_USER)
CLAVE=$(saca DB_PASSWORD)
MAQUINA=$(saca DB_HOST)

CNF=$(mktemp)
chmod 600 "$CNF"
trap 'rm -f "$CNF"' EXIT INT TERM
cat > "$CNF" <<EOF
[client]
user=$USUARIO
password=$CLAVE
host=$MAQUINA
EOF

SALIDA=~/volcado-tienda
mkdir -p "$SALIDA"
Q() { mysql --defaults-extra-file="$CNF" --default-character-set=utf8mb4 -B -N "$NOMBRE" -e "$1"; }

echo "== INVENTARIO =="
Q "SELECT post_type, post_status, COUNT(*)
   FROM wp_posts
   WHERE post_type IN ('product','page','post','product_variation')
   GROUP BY post_type, post_status ORDER BY post_type;"

echo
echo "== PEDIDOS (para saber si la tienda vende de verdad) =="
Q "SELECT post_status, COUNT(*), MIN(post_date), MAX(post_date)
   FROM wp_posts WHERE post_type='shop_order' GROUP BY post_status;" 2>/dev/null || echo "  sin tabla de pedidos clasica"
Q "SELECT status, COUNT(*), MIN(date_created_gmt), MAX(date_created_gmt)
   FROM wp_wc_orders GROUP BY status;" 2>/dev/null || echo "  sin wp_wc_orders"

echo
echo "== CLIENTES Y SOCIOS =="
Q "SELECT COUNT(*) FROM wp_users;"
Q "SELECT COUNT(*) FROM wp_pmpro_memberships_users WHERE status='active';" 2>/dev/null || echo "  sin socios"

echo
echo "== PRODUCTOS: precio, sku y estado =="
Q "SELECT p.ID, p.post_name,
          COALESCE(MAX(CASE WHEN m.meta_key='_sku' THEN m.meta_value END),'-'),
          COALESCE(MAX(CASE WHEN m.meta_key='_price' THEN m.meta_value END),'-'),
          COALESCE(MAX(CASE WHEN m.meta_key='_stock_status' THEN m.meta_value END),'-'),
          CHAR_LENGTH(p.post_content)
   FROM wp_posts p LEFT JOIN wp_postmeta m ON m.post_id=p.ID
   WHERE p.post_type='product' AND p.post_status='publish'
   GROUP BY p.ID, p.post_name, p.post_content ORDER BY p.ID;"

echo
echo "== PAGINAS Y ENTRADAS =="
Q "SELECT ID, post_type, post_name, CHAR_LENGTH(post_content),
          CASE WHEN EXISTS (SELECT 1 FROM wp_postmeta m WHERE m.post_id=p.ID AND m.meta_key='_elementor_data')
               THEN 'elementor' ELSE 'html' END
   FROM wp_posts p
   WHERE post_type IN ('page','post') AND post_status='publish' ORDER BY post_type, ID;"

# El contenido entero, a ficheros
Q "SELECT ID FROM wp_posts WHERE post_type IN ('product','page','post') AND post_status='publish';" \
  | while read -r ID; do
      Q "SELECT CONCAT('### ', post_type, ' | ', post_name, ' | ', post_title) FROM wp_posts WHERE ID=$ID;" > "$SALIDA/$ID.txt"
      Q "SELECT post_content FROM wp_posts WHERE ID=$ID;" >> "$SALIDA/$ID.txt"
      Q "SELECT meta_value FROM wp_postmeta WHERE post_id=$ID AND meta_key='_elementor_data';" > "$SALIDA/$ID.elementor.json" 2>/dev/null
      [ -s "$SALIDA/$ID.elementor.json" ] || rm -f "$SALIDA/$ID.elementor.json"
    done

echo
echo "== VOLCADO =="
ls "$SALIDA" | wc -l
du -sh "$SALIDA"