#!/usr/bin/env bash
set -euo pipefail

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

MYSQL_HOST="${1:-132.226.40.48}"
MYSQL_PORT="${2:-3310}"
MYSQL_USER="${3:-christian}"
SOURCE_DB="${SOURCE_DB:-mbinvcxp}"
MB_USER_NAME="${MB_USER_NAME:-mb}"

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
REPO_ROOT="$(dirname "$(dirname "$SCRIPT_DIR")")"
OUTPUT_DIR="$REPO_ROOT/sql"
STRUCTURE_FILE="$OUTPUT_DIR/cxpEstructura.sql"
INSTALL_FILE="$OUTPUT_DIR/cxpInstalacion.sql"

GLOBAL_DATA_TABLES=(
  monedas
  roles
  tipos_cambio
  paises
  regimenes_fiscales
  clasificaciones_proveedor
  tipos_iva
  idp_tasas
  doctrine_migration_versions
)
CLIENT_DATA_TABLES=(
  impuestos
  retenciones
  centros_costo
  proyectos
  tipos_documento
  condiciones_pago
  bancos
  cuentas_bancarias
  cuentas_contables
)

mysql_exec() {
  mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -N -B -e "$1" "$SOURCE_DB"
}

dump_table_data() {
  local table="$1"
  shift || true
  local extra=("$@")
  mysqldump -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
    --no-create-info --skip-triggers --skip-comments --set-gtid-purged=OFF \
    --complete-insert "${extra[@]}" "$SOURCE_DB" "$table" >> "$INSTALL_FILE"
}

echo -e "${CYAN}Password MySQL ($MYSQL_USER):${NC}"
read -s MYSQL_PASS; echo ""
mkdir -p "$OUTPUT_DIR"

mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SELECT 1" "$SOURCE_DB" >/dev/null 2>&1 || {
  echo -e "${RED}No se pudo conectar a $SOURCE_DB${NC}"; exit 1; }

MB_USER_ID=$(mysql_exec "SELECT id FROM usuarios WHERE nombre = '${MB_USER_NAME}' ORDER BY id LIMIT 1;")
[ -n "$MB_USER_ID" ] || { echo -e "${RED}No existe usuario '${MB_USER_NAME}' en usuarios${NC}"; exit 1; }
read -r TEMPLATE_CLIENT_ID TEMPLATE_SITE_ID TEMPLATE_ROLE_ID <<< "$(mysql_exec "SELECT cliente_id, sitio_id, rol_id FROM usuarios_sitios_roles WHERE usuario_id = ${MB_USER_ID} ORDER BY id LIMIT 1;")"
[ -n "${TEMPLATE_CLIENT_ID:-}" ] || { echo -e "${RED}El usuario ${MB_USER_NAME} no tiene relacion en usuarios_sitios_roles${NC}"; exit 1; }
TEMPLATE_COMPANY_ID=$(mysql_exec "SELECT id FROM empresas WHERE cliente_id = ${TEMPLATE_CLIENT_ID} AND sitio_id = ${TEMPLATE_SITE_ID} ORDER BY id LIMIT 1;")
TEMPLATE_BRANCH_ID=$(mysql_exec "SELECT id FROM sucursales WHERE cliente_id = ${TEMPLATE_CLIENT_ID} AND sitio_id = ${TEMPLATE_SITE_ID} AND empresa_id = ${TEMPLATE_COMPANY_ID} ORDER BY id LIMIT 1;")
MB_PASSWORD_HASH=$(mysql_exec "SELECT password_hash FROM usuarios WHERE id = ${MB_USER_ID} LIMIT 1;")
TEMPLATE_CLIENT_CURRENCY=$(mysql_exec "SELECT COALESCE(moneda_base_id, 1) FROM clientes WHERE id = ${TEMPLATE_CLIENT_ID} LIMIT 1;")
TEMPLATE_COUNTRY=$(mysql_exec "SELECT COALESCE(pais_iso2, 'GT') FROM clientes WHERE id = ${TEMPLATE_CLIENT_ID} LIMIT 1;")

[ -n "$TEMPLATE_COMPANY_ID" ] || { echo -e "${RED}No se encontro empresa base para cliente ${TEMPLATE_CLIENT_ID}${NC}"; exit 1; }
[ -n "$TEMPLATE_BRANCH_ID" ] || { echo -e "${RED}No se encontro sucursal base para cliente ${TEMPLATE_CLIENT_ID}${NC}"; exit 1; }

CLIENT_ID_FOR_FILTER="$TEMPLATE_CLIENT_ID"
SITE_ID_FOR_FILTER="$TEMPLATE_SITE_ID"
COMPANY_ID_FOR_FILTER="$TEMPLATE_COMPANY_ID"
BRANCH_ID_FOR_FILTER="$TEMPLATE_BRANCH_ID"
ROLE_ID_FOR_FILTER="$TEMPLATE_ROLE_ID"

echo -e "\n${CYAN}[1/2]${NC} Generando $STRUCTURE_FILE"
mysqldump -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
  --no-data --routines --triggers --events --skip-comments --set-gtid-purged=OFF --no-create-db \
  "$SOURCE_DB" > "$STRUCTURE_FILE"
echo -e "${GREEN}OK${NC} cxpEstructura.sql"

echo -e "\n${CYAN}[2/2]${NC} Generando $INSTALL_FILE"
cp "$STRUCTURE_FILE" "$INSTALL_FILE"
{
  echo ""
  echo "SET FOREIGN_KEY_CHECKS = 0;"
  echo ""
} >> "$INSTALL_FILE"

for table in "${GLOBAL_DATA_TABLES[@]}"; do
  exists=$(mysql_exec "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '${SOURCE_DB}' AND table_name = '${table}';")
  if [ "$exists" = "1" ]; then
    echo "  -> $table"
    dump_table_data "$table"
  fi
done

for table in "${CLIENT_DATA_TABLES[@]}"; do
  exists=$(mysql_exec "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '${SOURCE_DB}' AND table_name = '${table}';")
  if [ "$exists" = "1" ]; then
    echo "  -> $table (cliente ${CLIENT_ID_FOR_FILTER})"
    dump_table_data "$table" "--where=cliente_id=${CLIENT_ID_FOR_FILTER}"
  fi
done

cat >> "$INSTALL_FILE" <<EOF
DELETE FROM usuarios_sitios_roles;
DELETE FROM usuarios;
DELETE FROM sucursales;
DELETE FROM empresas;
DELETE FROM sitios;
DELETE FROM clientes;

INSERT INTO clientes (id, nombre, nombre_comercial, nit, pais_iso2, moneda_base_id, timezone, dominio, activo, creado_en, actualizado_en)
VALUES (${CLIENT_ID_FOR_FILTER}, 'Base Instalacion', 'Base Instalacion', NULL, '${TEMPLATE_COUNTRY}', ${TEMPLATE_CLIENT_CURRENCY}, 'America/Guatemala', NULL, 1, NOW(), NOW());

INSERT INTO sitios (id, cliente_id, nombre, moneda_base_id, timezone, activo, creado_en, actualizado_en)
VALUES (${SITE_ID_FOR_FILTER}, ${CLIENT_ID_FOR_FILTER}, 'Principal', ${TEMPLATE_CLIENT_CURRENCY}, 'America/Guatemala', 1, NOW(), NOW());

INSERT INTO empresas (id, cliente_id, sitio_id, razon_social, nit, direccion, telefono, email, activo)
VALUES (${COMPANY_ID_FOR_FILTER}, ${CLIENT_ID_FOR_FILTER}, ${SITE_ID_FOR_FILTER}, 'Empresa Demo', NULL, NULL, NULL, NULL, 1);

INSERT INTO sucursales (id, cliente_id, sitio_id, empresa_id, nombre, codigo, direccion, activo, creado_en, actualizado_en)
VALUES (${BRANCH_ID_FOR_FILTER}, ${CLIENT_ID_FOR_FILTER}, ${SITE_ID_FOR_FILTER}, ${COMPANY_ID_FOR_FILTER}, 'Principal', 'PRINCIPAL', NULL, 1, NOW(), NOW());

INSERT INTO usuarios (id, email, password_hash, nombre, activo, superadmin, ultimo_login_en, creado_en, actualizado_en)
VALUES (${MB_USER_ID}, 'mb@local', '$(printf "%s" "$MB_PASSWORD_HASH" | sed "s/'/''/g")', 'mb', 1, 1, NULL, NOW(), NOW());

INSERT INTO usuarios_sitios_roles (usuario_id, cliente_id, sitio_id, rol_id, activo, creado_en)
VALUES (${MB_USER_ID}, ${CLIENT_ID_FOR_FILTER}, ${SITE_ID_FOR_FILTER}, ${ROLE_ID_FOR_FILTER}, 1, NOW());

SET FOREIGN_KEY_CHECKS = 1;
EOF

echo -e "\n${GREEN}OK${NC} Plantillas generadas en $OUTPUT_DIR"
echo -e "${YELLOW}Revision recomendada:${NC} validar que impuestos y catalogos del cliente base son los correctos antes de usar en produccion."