#!/bin/bash
# ================================================================
# Cambiar CPE de un suscriptor
# Actualiza username (PPPoE) y MAC en Master + Réplica + Contabilidad
#
# Uso: ./cambiar_cpe.sh <mac_vieja> <mac_nueva>
# Ej:  ./cambiar_cpe.sh 1c:ef:03:8d:8f:80 b4:64:15:e5:6c:38
# ================================================================

set -euo pipefail

# Colores
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
CYAN='\033[0;36m'
NC='\033[0m'

# Credenciales
LOCAL_USER="ctnet"
LOCAL_PASS="ctnet2009"
MASTER_HOST="192.168.254.3"
MASTER_USER="radius"
MASTER_PASS="radius123"
MASTER_DB="radius"

# Validar argumentos
if [ $# -ne 2 ]; then
    echo -e "${RED}Uso: $0 <mac_vieja> <mac_nueva>${NC}"
    echo -e "Ejemplo: $0 1c:ef:03:8d:8f:80 b4:64:15:e5:6c:38"
    exit 1
fi

OLD_MAC=$(echo "$1" | tr '[:upper:]' '[:lower:]')
NEW_MAC=$(echo "$2" | tr '[:upper:]' '[:lower:]')

# Validar formato MAC (xx:xx:xx:xx:xx:xx)
MAC_REGEX='^([0-9a-f]{2}:){5}[0-9a-f]{2}$'
if ! [[ "$OLD_MAC" =~ $MAC_REGEX ]]; then
    echo -e "${RED}Error: MAC vieja inválida: $OLD_MAC${NC}"
    echo "Formato esperado: xx:xx:xx:xx:xx:xx"
    exit 1
fi
if ! [[ "$NEW_MAC" =~ $MAC_REGEX ]]; then
    echo -e "${RED}Error: MAC nueva inválida: $NEW_MAC${NC}"
    echo "Formato esperado: xx:xx:xx:xx:xx:xx"
    exit 1
fi

# Calcular MAC+1 para el campo mac (binding)
# La MAC del campo mac suele ser username+1
IFS=':' read -ra OCTETS <<< "$NEW_MAC"
LAST_OCTET=$((16#${OCTETS[5]} + 1))
OCTETS[5]=$(printf '%02x' $LAST_OCTET)
NEW_MAC_BIND="${OCTETS[0]}:${OCTETS[1]}:${OCTETS[2]}:${OCTETS[3]}:${OCTETS[4]}:${OCTETS[5]}"

echo -e "${CYAN}═══════════════════════════════════════════════${NC}"
echo -e "${CYAN}         CAMBIO DE CPE - CTNetOSP${NC}"
echo -e "${CYAN}═══════════════════════════════════════════════${NC}"
echo ""

# =============================================
# 1. Verificar usuario actual
# =============================================
echo -e "${YELLOW}[1/5] Verificando usuario actual...${NC}"

USER_DATA=$(mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -N -e "
    SELECT CONCAT(firstname,' ',lastname), expiration, mac,
           (SELECT srvname FROM rm_services WHERE srvid = u.srvid)
    FROM rm_users u WHERE username = '$OLD_MAC';" 2>/dev/null)

if [ -z "$USER_DATA" ]; then
    echo -e "${RED}Error: No se encontró usuario con MAC: $OLD_MAC${NC}"
    exit 1
fi

NOMBRE=$(echo "$USER_DATA" | cut -f1)
EXPIRACION=$(echo "$USER_DATA" | cut -f2)
MAC_ACTUAL=$(echo "$USER_DATA" | cut -f3)
PLAN=$(echo "$USER_DATA" | cut -f4)

echo -e "  Cliente:    ${GREEN}$NOMBRE${NC}"
echo -e "  Plan:       $PLAN"
echo -e "  Expiración: $EXPIRACION"
echo -e "  MAC actual: $MAC_ACTUAL"
echo ""

# Verificar que la nueva MAC no exista ya
EXISTE=$(mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -N -e "
    SELECT COUNT(*) FROM rm_users WHERE username = '$NEW_MAC';" 2>/dev/null)

if [ "$EXISTE" != "0" ]; then
    echo -e "${RED}Error: Ya existe un usuario con MAC: $NEW_MAC${NC}"
    exit 1
fi

# =============================================
# 2. Mostrar resumen del cambio
# =============================================
echo -e "${YELLOW}[2/5] Resumen del cambio:${NC}"
echo -e "  ┌──────────────────┬─────────────────────┬─────────────────────┐"
echo -e "  │ Campo            │ Antes               │ Después             │"
echo -e "  ├──────────────────┼─────────────────────┼─────────────────────┤"
echo -e "  │ Username (PPPoE) │ $OLD_MAC │ $NEW_MAC │"
echo -e "  │ MAC (binding)    │ $MAC_ACTUAL │ $NEW_MAC_BIND │"
echo -e "  └──────────────────┴─────────────────────┴─────────────────────┘"
echo ""

# Contar registros afectados
echo -e "${YELLOW}  Registros a actualizar (sin radacct):${NC}"
for TABLE in rm_users radcheck rm_invoices rm_actsrv rm_dailyacct; do
    CNT=$(mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -N -e "
        SELECT COUNT(*) FROM $TABLE WHERE username = '$OLD_MAC';" 2>/dev/null)
    echo -e "    radius.$TABLE: $CNT"
done
CNT_ING=$(mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" contabilidad -N -e "
    SELECT COUNT(*) FROM ingresos WHERE username = '$OLD_MAC';" 2>/dev/null)
echo -e "    contabilidad.ingresos: $CNT_ING"
echo -e "    ${CYAN}(radacct omitido - solo historial, sin impacto operativo)${NC}"
echo ""

# =============================================
# 3. Confirmar
# =============================================
read -p "¿Confirmar cambio? (s/n): " -n 1 -r
echo ""
if [[ ! $REPLY =~ ^[Ss]$ ]]; then
    echo -e "${RED}Cancelado.${NC}"
    exit 0
fi

echo ""

# =============================================
# 4. Ejecutar en MASTER
# =============================================
echo -e "${YELLOW}[3/6] Actualizando RM Master ($MASTER_HOST)...${NC}"

ssh root@$MASTER_HOST "mysql -u $MASTER_USER -p$MASTER_PASS $MASTER_DB -e \"
    UPDATE rm_users SET username = '$NEW_MAC', mac = '$NEW_MAC_BIND' WHERE username = '$OLD_MAC';
    UPDATE radcheck SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';
    UPDATE rm_invoices SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';
    UPDATE rm_actsrv SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';
    UPDATE rm_dailyacct SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';
\"" 2>&1

if [ $? -eq 0 ]; then
    echo -e "  ${GREEN}✓ Master actualizado${NC}"
else
    echo -e "  ${RED}✗ Error en Master${NC}"
    exit 1
fi

# =============================================
# 5. Ejecutar en RÉPLICA LOCAL
# =============================================
echo -e "${YELLOW}[4/6] Actualizando réplica local...${NC}"

# Eliminar viejo si la sync lo recreó
mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -e "
    DELETE FROM rm_users WHERE username = '$OLD_MAC';
    DELETE FROM radcheck WHERE username = '$OLD_MAC';" 2>/dev/null

# Actualizar tablas operativas (sin radacct)
mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -e "
    UPDATE rm_invoices SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';
    UPDATE rm_actsrv SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';
    UPDATE rm_dailyacct SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';" 2>/dev/null

echo -e "  ${GREEN}✓ Réplica local actualizada${NC}"

# Contabilidad
mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" contabilidad -e "
    UPDATE ingresos SET username = '$NEW_MAC' WHERE username = '$OLD_MAC';" 2>/dev/null

echo -e "  ${GREEN}✓ Contabilidad actualizada${NC}"

# =============================================
# 5.1 Send PoD del username viejo (liberar sesión)
# =============================================
echo -e "${YELLOW}[5/6] Enviando PoD (desconexión)...${NC}"
curl -s "http://192.168.254.3/radiusmanager/api/sysapi.php?apiuser=apictnet&apipass=apictnet2009&q=send_pod&username=$OLD_MAC" > /dev/null 2>&1
echo -e "  ${GREEN}✓ PoD enviado para $OLD_MAC${NC}"

# =============================================
# 5.2 Registrar en historial
# =============================================
mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -e "
    INSERT INTO cpe_cambios (old_username, new_username, old_mac, new_mac, nombre_cliente, plan_servicio, operador, resultado_master, resultado_replica, resultado_contabilidad)
    VALUES ('$OLD_MAC', '$NEW_MAC', '$MAC_ACTUAL', '$NEW_MAC_BIND', '$NOMBRE', '$PLAN', '$(whoami)_cli', 1, 1, 1);" 2>/dev/null
echo -e "  ${GREEN}✓ Historial registrado${NC}"

# =============================================
# 6. Verificación
# =============================================
echo ""
echo -e "${YELLOW}[6/6] Verificación final...${NC}"

# Verificar en Master
MASTER_OK=$(ssh root@$MASTER_HOST "mysql -u $MASTER_USER -p$MASTER_PASS $MASTER_DB -N -e \"
    SELECT COUNT(*) FROM rm_users WHERE username = '$NEW_MAC';\"" 2>/dev/null)

# Verificar que viejo no exista en Master
MASTER_OLD=$(ssh root@$MASTER_HOST "mysql -u $MASTER_USER -p$MASTER_PASS $MASTER_DB -N -e \"
    SELECT COUNT(*) FROM rm_users WHERE username = '$OLD_MAC';\"" 2>/dev/null)

# Verificar en local
LOCAL_OK=$(mysql -u "$LOCAL_USER" -p"$LOCAL_PASS" radius -N -e "
    SELECT COUNT(*) FROM rm_users WHERE username = '$NEW_MAC';" 2>/dev/null)

echo -e "  Master  - nuevo existe: $([ "$MASTER_OK" = "1" ] && echo "${GREEN}✓${NC}" || echo "${RED}✗${NC}")"
echo -e "  Master  - viejo borrado: $([ "$MASTER_OLD" = "0" ] && echo "${GREEN}✓${NC}" || echo "${RED}✗${NC}")"
echo -e "  Réplica - nuevo existe: $([ "$LOCAL_OK" = "1" ] && echo "${GREEN}✓${NC}" || echo "${RED}✗${NC}")"

echo ""
echo -e "${GREEN}═══════════════════════════════════════════════${NC}"
echo -e "${GREEN}  CPE cambiado exitosamente${NC}"
echo -e "${GREEN}═══════════════════════════════════════════════${NC}"
echo -e "  PPPoE user: ${CYAN}$NEW_MAC${NC}"
echo -e "  PPPoE pass: ${CYAN}1234${NC}"
echo ""
echo -e "  ${YELLOW}Nota: El técnico debe configurar la ONU con estos datos.${NC}"

# Log
LOG_FILE="/home/www/admin/tools/cambiar_cpe.log"
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $NOMBRE: $OLD_MAC → $NEW_MAC (por: $(whoami))" >> "$LOG_FILE"