nano /root/restore_marzban.sh

#!/bin/bash
set -euo pipefail

ENV_FILE="/opt/marzban/.env"
BACKUP_FILE="/root/backuper.sql"

if [[ ! -f "$ENV_FILE" ]]; then
  echo "ERROR: فایل $ENV_FILE پیدا نشد."
  exit 1
fi

if [[ ! -f "$BACKUP_FILE" ]]; then
  echo "ERROR: فایل بکاپ $BACKUP_FILE پیدا نشد."
  exit 1
fi

set -o allexport
source "$ENV_FILE"
set +o allexport

DB_NAME="${MYSQL_DATABASE:?MYSQL_DATABASE در .env پیدا نشد}"
ROOT_PASS="${MYSQL_ROOT_PASSWORD:?MYSQL_ROOT_PASSWORD در .env پیدا نشد}"
DB_HOST="${MYSQL_HOST:-127.0.0.1}"
DB_PORT="${MYSQL_PORT:-3306}"

echo "قراره دیتابیس '$DB_NAME' روی $DB_HOST:$DB_PORT کاملاً پاک و از بکاپ برگردانده بشه."
echo "این عملیات غیرقابل برگشته."
read -p "برای ادامه دقیقاً YES را تایپ کنید: " CONFIRM

if [[ "$CONFIRM" != "YES" ]]; then
  echo "عملیات لغو شد."
  exit 1
fi

echo "در حال Drop و ایجاد مجدد دیتابیس $DB_NAME ..."

mysql -u root -p"$ROOT_PASS" -h "$DB_HOST" -P "$DB_PORT" \
  -e "DROP DATABASE IF EXISTS \`$DB_NAME\`; CREATE DATABASE \`$DB_NAME\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

echo "در حال Import بکاپ از $BACKUP_FILE به دیتابیس $DB_NAME ..."

mysql -u root -p"$ROOT_PASS" -h "$DB_HOST" -P "$DB_PORT" "$DB_NAME" < "$BACKUP_FILE"

echo "✅ عملیات با موفقیت انجام شد."

chmod +x /root/restore_marzban.sh

/root/restore_marzban.sh

START TRANSACTION;

SET @GB200 := 214748364800;

DROP TEMPORARY TABLE IF EXISTS tmp_users_to_delete;
CREATE TEMPORARY TABLE tmp_users_to_delete (PRIMARY KEY (id))
AS
SELECT id
FROM users
WHERE data_limit IS NULL
   OR data_limit > @GB200;

DELETE e
FROM exclude_inbounds_association e
JOIN proxies p ON e.proxy_id = p.id
JOIN tmp_users_to_delete t ON p.user_id = t.id;

DELETE np
FROM next_plans np
JOIN tmp_users_to_delete t ON np.user_id = t.id;

DELETE nu
FROM node_user_usages nu
JOIN tmp_users_to_delete t ON nu.user_id = t.id;

DELETE ul
FROM user_usage_logs ul
JOIN tmp_users_to_delete t ON ul.user_id = t.id;

DELETE p
FROM proxies p
JOIN tmp_users_to_delete t ON p.user_id = t.id;

DELETE u
FROM users u
JOIN tmp_users_to_delete t ON u.id = t.id;

COMMIT;

DROP TEMPORARY TABLE IF EXISTS tmp_users_to_delete;

214748364800

UPDATE `users`
SET status = 'deleted'
WHERE admin_id = 1;

UPDATE `users`
SET status = 'disabled'
WHERE admin_id = 1;

SELECT *
FROM `users`
WHERE admin_id = 1;