#!/usr/bin/env bash
set -u
# ============================================================
#  MySQL 索引与 SQL 优化实战 — 一键安装脚本（macOS / Linux / WSL）
#  公开源码，欢迎审查 —— 不放心可先复制给 AI 判断
# ============================================================

GREEN='\033[0;32m'; YELLOW='\033[1;33m'; RED='\033[0;31m'; CYAN='\033[0;36m'; NC='\033[0m'
info()  { echo -e "${GREEN}[INFO]${NC} $1"; }
warn()  { echo -e "${YELLOW}[WARN]${NC} $1"; }
error() { echo -e "${RED}[ERROR]${NC} $1"; }
step()  { echo -e "${CYAN}[STEP]${NC} $1"; }

detect_network() {
  step "检测网络环境（国内 / 国外）..."
  if curl -fsI --max-time 5 "https://claude.ai" >/dev/null 2>&1; then
    info "可直连 claude.ai，判定为海外网络"
    echo "overseas"
  else
    warn "无法直连 claude.ai，判定为国内网络环境（将使用国内镜像源）"
    echo "domestic"
  fi
}

get_os() {
  if [ -f /etc/os-release ]; then
    . /etc/os-release
    echo "$ID"
  elif command -v sw_vers >/dev/null 2>&1; then
    echo "darwin"
  else
    echo "unknown"
  fi
}

detect_os() {
  step "检测操作系统..."
  local os_id
  os_id=$(get_os)
  case "$os_id" in
    darwin)   info "检测到 macOS";        echo "macos" ;;
    debian|ubuntu) info "检测到 Debian/Ubuntu"; echo "debian" ;;
    centos|rhel|fedora) info "检测到 CentOS/RHEL/Fedora"; echo "redhat" ;;
    alpine)   info "检测到 Alpine Linux";  echo "alpine" ;;
    *)        warn "未知发行版，尝试使用 apt"; echo "debian" ;;
  esac
}

check_mysql_running() {
  if command -v mysql >/dev/null 2>&1; then
    if mysql -u root -e "SELECT 1;" >/dev/null 2>&1; then
      info "MySQL 已安装并正在运行"
      return 0
    fi
  fi
  return 1
}

install_on_debian() {
  local net="$1"
  step "安装 MySQL Server（Debian/Ubuntu 系）..."
  if [ "$net" = "domestic" ]; then
    warn "国内网络，apt 源可能较慢；如遇超时请手动更换清华源（见下方提示）"
  fi
  # apt-get update 带超时 + 回退逻辑
  if timeout 60 sudo apt-get update >/dev/null 2>&1; then
    :
  else
    warn "apt-get update 超时或失败"
  fi

  if timeout 120 sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server >/dev/null 2>&1; then
    info "MySQL 安装成功"
    return 0
  fi
  warn "MySQL 安装失败，回退尝试 MariaDB（功能一致、兼容 MySQL 协议）..."
  if timeout 120 sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mariadb-server >/dev/null 2>&1; then
    info "MariaDB 安装成功（与 MySQL 高度兼容）"
    return 0
  fi
  error "MySQL/MariaDB 安装全部失败。"
  echo "  处理方式："
  echo "  1. 换清华源：sudo sed -i 's/archive.ubuntu.com/mirrors.tuna.tsinghua.edu.cn/g' /etc/apt/sources.list"
  echo "     然后重试：sudo apt-get update && sudo apt-get install -y mysql-server"
  echo "  2. 下载离线包：https://dev.mysql.com/downloads/mysql/ （海外）或 ghfast.top 代理（国内）"
  return 1
}

install_on_redhat() {
  step "安装 MySQL Server（CentOS/RHEL 系）..."
  if timeout 120 sudo dnf install -y mysql-server >/dev/null 2>&1; then
    info "MySQL 安装成功"
    return 0
  fi
  warn "dnf 安装失败，回退 yum..."
  if timeout 120 sudo yum install -y mysql-server >/dev/null 2>&1; then
    info "MySQL 安装成功（yum）"
    return 0
  fi
  warn "MySQL 安装失败，尝试 MariaDB..."
  if timeout 120 sudo dnf install -y mariadb-server >/dev/null 2>&1 2>/dev/null; then
    info "MariaDB 安装成功"
    return 0
  fi
  error "MySQL/MariaDB 安装失败。"
  echo "  请手动：sudo dnf install -y mysql-server  或  sudo yum install -y mysql-server"
  return 1
}

install_on_macos() {
  step "安装 MySQL（macOS）..."
  if command -v brew >/dev/null 2>&1; then
    if timeout 300 brew install mysql >/dev/null 2>&1; then
      info "MySQL 安装成功"
      brew services start mysql >/dev/null 2>&1
      return 0
    fi
  fi
  error "Homebrew 不可用或安装失败。"
  echo "  请先安装 Homebrew："
  echo "    /bin/bash -c \"\$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)\""
  echo "  国内加速：把 raw.githubusercontent.com 换成 ghfast.top 前缀代理"
  return 1
}

install_on_alpine() {
  step "安装 MariaDB（Alpine Linux）..."
  if apk add --no-cache mariadb mariadb-server >/dev/null 2>&1; then
    info "MariaDB 安装成功"
    rc-update add mariadbd default 2>/dev/null || true
    rc-service mariadbd start 2>/dev/null || mysqld_safe &>/dev/null &
    return 0
  fi
  error "Alpine 安装失败。请手动：apk add --no-cache mariadb mariadb-server"
  return 1
}

start_mysql() {
  step "启动 MySQL 服务..."
  if command -v systemctl >/dev/null 2>&1; then
    sudo systemctl enable mysql 2>/dev/null || sudo systemctl enable mysqld 2>/dev/null || true
    sleep 1
    sudo systemctl start mysql 2>/dev/null || sudo systemctl start mysqld 2>/dev/null || true
  elif command -v brew >/dev/null 2>&1; then
    brew services start mysql 2>/dev/null || true
  fi
  # 等 MySQL 完全就绪（最多等待 20 秒）
  local retries=10
  while ! mysql -u root -e "SELECT 1;" >/dev/null 2>&1; do
    retries=$((retries - 1))
    if [ "$retries" -le 0 ]; then
      warn "MySQL 未在自动等待期内就绪"
      break
    fi
    info "等待 MySQL 就绪...（剩余 ${retries}s）"
    sleep 2
  done
  if command -v mysql >/dev/null 2>&1 && mysql -u root -e "SELECT 1;" >/dev/null 2>&1; then
    info "MySQL 服务已就绪"
    return 0
  fi
  warn "未能确认 MySQL 运行状态，可能需手动启动。"
  echo "  systemd: sudo systemctl start mysql (Debian) 或 sudo systemctl start mysqld (RHEL)"
  echo "  Homebrew: brew services start mysql"
  echo "  其他: sudo service mysql start"
  return 0
}

setup_security() {
  warn "安全提醒："
  echo "  以下命令用于加固数据库安全设置："
  echo "    sudo mysql_secure_installation"
  echo "  它会引导你：设置 root 密码、删除匿名账号、禁止 root 远程登录、移除 test 库。"
  echo "  这些是生产环境的**必备操作**——不做等于门户大开。开发测试阶段可以跳过。"
}

create_demo_database() {
  step "创建示例数据库 shop 并导入测试数据..."
  local retries=10
  while ! mysql -u root -e "SELECT 1;" >/dev/null 2>&1; do
    retries=$((retries - 1))
    if [ "$retries" -le 0 ]; then
      error "MySQL 仍未响应，无法创建数据库"
      return 1
    fi
    info "等待 MySQL 就绪...（剩余 ${retries} 次尝试）"
    sleep 2
  done

  mysql -u root <<'SQLEOF'
CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');

INSERT INTO products (name, price, stock, category) VALUES
('机械键盘', 399.00, 50, '数码'),
('无线鼠标', 89.90, 200, '数码'),
('Python入门教程', 49.00, 1000, '图书'),
('咖啡杯', 35.00, 300, '生活');

INSERT INTO orders (user_id, product_id, quantity) VALUES
(1, 1, 1),
(1, 3, 2),
(2, 2, 3);

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_category ON products(category);

SELECT 'Demo data inserted successfully!' AS status;
SQLEOF

  if [ $? -eq 0 ]; then
    info "示例数据库创建完成！"
    return 0
  else
    error "示例数据导入失败"
    return 1
  fi
}

verify_query() {
  step "验证安装——运行联合查询测试..."
  if mysql -u root -D shop -e "
    SELECT u.username, p.name, o.quantity, o.order_time
    FROM users u JOIN orders o ON u.id = o.user_id
    JOIN products p ON o.product_id = p.id WHERE u.username = 'alice';
  " 2>/dev/null; then
    info "查询验证通过！"
    return 0
  else
    error "查询验证失败"
    return 1
  fi
}

main() {
  echo "============================================"
  echo "  MySQL 索引与 SQL 优化实战 — 一键安装脚本"
  echo "  适用于 macOS / Linux / WSL"
  echo "============================================"
  echo ""

  local NET
  NET=$(detect_network)

  local OS_TYPE
  OS_TYPE=$(detect_os)

  if check_mysql_running; then
    info "MySQL 已在运行，跳过安装步骤"
  else
    case "$OS_TYPE" in
      debian)  install_on_debian "$NET" || exit 1 ;;
      redhat)  install_on_redhat ;;
      macos)   install_on_macos ;;
      alpine)  install_on_alpine ;;
      *)       warn "未知系统，尝试通用安装流程"; install_on_debian "$NET" || exit 1 ;;
    esac
  fi

  start_mysql
  setup_security
  create_demo_database
  verify_query

  echo ""
  echo "============================================"
  info "安装完成！"
  echo "  进入数据库：mysql -u root -p"
  echo "  选择示例库：USE shop;"
  echo "  查看用户表：SELECT * FROM users;"
  echo "  查看索引：  SHOW INDEX FROM users;"
  echo "  执行计划：  EXPLAIN SELECT * FROM users WHERE username='alice';"
  echo "  安全加固：  sudo mysql_secure_installation"
  echo "============================================"
}

main "$@"