-- ===================================================================== -- VPN SHOP BOT - DATABASE SCHEMA -- Everything an admin needs to change later (plans, buttons, admins, -- channels, texts, prices) lives in these tables, NOT in PHP code. -- ===================================================================== SET NAMES utf8mb4; -- --------------------------------------------------------------------- -- users -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tg_id BIGINT UNSIGNED NOT NULL UNIQUE, username VARCHAR(64) NULL, first_name VARCHAR(128) NULL, phone VARCHAR(32) NULL, balance BIGINT NOT NULL DEFAULT 0, -- Toman ref_by INT UNSIGNED NULL, -- users.id of inviter is_banned TINYINT(1) NOT NULL DEFAULT 0, used_free_test TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (ref_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- admins (role: owner | manager | support) -- owner -> full access, can add/remove other admins -- manager -> everything except managing admins -- support -> only approve/reject orders & wallet charges + broadcast -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS admins ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tg_id BIGINT UNSIGNED NOT NULL UNIQUE, role ENUM('owner','manager','support') NOT NULL DEFAULT 'support', added_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- settings (generic key/value store, edited entirely from bot panel) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS settings ( setting_key VARCHAR(64) PRIMARY KEY, setting_value TEXT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- menu_buttons (main-menu feature toggles - add/remove/enable/disable -- without touching code) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS menu_buttons ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, button_key VARCHAR(32) NOT NULL UNIQUE, -- internal code, e.g. 'buy' label VARCHAR(64) NOT NULL, -- what user sees, e.g. 'خرید اشتراک 🛒' enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- categories ("مولتی لوکیشن" etc.) - the top level of the buy flow -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(64) NOT NULL, pg_group_ids VARCHAR(255) NULL, -- PasarGuard group/inbound ids for this category enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- durations (1 ماهه / 2 ماهه / 3 ماهه ...) attached to a category -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS durations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NOT NULL, title VARCHAR(32) NOT NULL, -- '1 ماهه' days INT NOT NULL, -- 30 enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- plans (the actual gb/price rows shown after duration is picked) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS plans ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, duration_id INT UNSIGNED NOT NULL, gb INT NOT NULL, -- 0 = unlimited price BIGINT NOT NULL, -- Toman enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, FOREIGN KEY (duration_id) REFERENCES durations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- force_join_channels -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS force_join_channels ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, chat_id VARCHAR(64) NOT NULL, -- @channelusername OR -100xxxxxxxxxx title VARCHAR(128) NULL, invite_link VARCHAR(255) NULL, enabled TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- service_names (names a user has previously used, offered again) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS service_names ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, name VARCHAR(40) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_user_name (user_id, name), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- orders (purchase requests waiting/approved/rejected) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, plan_id INT UNSIGNED NULL, -- NULL for wallet-paid instantly service_name VARCHAR(40) NOT NULL, amount BIGINT NOT NULL, pay_method ENUM('wallet','card') NOT NULL DEFAULT 'wallet', status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', receipt_file_id VARCHAR(255) NULL, sub_link TEXT NULL, admin_msg_id BIGINT NULL, -- message id sent to admin for approve/reject reviewed_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, reviewed_at DATETIME NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- wallet_charges (کارت‌به‌کارت افزایش موجودی, pending admin approval) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS wallet_charges ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, amount BIGINT NOT NULL, receipt_file_id VARCHAR(255) NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', admin_msg_id BIGINT NULL, reviewed_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, reviewed_at DATETIME NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- transactions (full ledger: charge / purchase / referral_commission) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS transactions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, type ENUM('charge','purchase','referral_commission','admin_adjust') NOT NULL, amount BIGINT NOT NULL, -- positive = credit, negative = debit balance_after BIGINT NOT NULL, meta VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- user_state (multi-step conversation state machine) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS user_state ( tg_id BIGINT UNSIGNED PRIMARY KEY, step VARCHAR(64) NOT NULL, data TEXT NULL, -- JSON blob of collected step data updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ===================================================================== -- SEED DATA - default settings & menu buttons -- (all editable later from inside the bot, this is just the starting -- point so the bot works immediately after install) -- ===================================================================== INSERT INTO settings (setting_key, setting_value) VALUES ('welcome_text', 'به ربات IranJrVPN خوش آمدید 🤍\n\nاز منوی زیر بخش مورد نظرتان را انتخاب کنید.'), ('card_number', '6037-XXXX-XXXX-XXXX'), ('card_holder', 'نام صاحب کارت'), ('min_charge_amount', '50000'), ('referral_percent', '5'), ('force_join_enabled', '0'), ('reseller_enabled', '0'), ('free_test_gb', '1'), ('free_test_days', '1'), ('bot_username', 'your_bot_username') ON DUPLICATE KEY UPDATE setting_key = setting_key; INSERT INTO menu_buttons (button_key, label, enabled, sort_order) VALUES ('buy', 'خرید اشتراک 🛒', 1, 1), ('plans_info', 'انواع اشتراک 📚', 1, 2), ('free_test', 'تست رایگان 🧪', 1, 3), ('account', 'حساب کاربری 👤', 1, 4), ('charge', 'افزایش موجودی 💰', 1, 5), ('referral', 'دعوت دوستان 🎁', 1, 6), ('reseller', 'درخواست نمایندگی 🤝', 0, 7), ('support', 'پشتیبانی 💬', 1, 8) ON DUPLICATE KEY UPDATE button_key = button_key; INSERT INTO categories (title, pg_group_ids, enabled, sort_order) VALUES ('مولتی لوکیشن', '', 1, 1) ON DUPLICATE KEY UPDATE title = title; INSERT INTO durations (category_id, title, days, enabled, sort_order) VALUES (1, '1 ماهه', 30, 1, 1), (1, '2 ماهه', 60, 1, 2), (1, '3 ماهه', 90, 1, 3) ON DUPLICATE KEY UPDATE title = title; -- Example plans under "1 ماهه" - EDIT/ADD FROM THE ADMIN PANEL LATER INSERT INTO plans (duration_id, gb, price, enabled, sort_order) VALUES (1, 10, 30000, 1, 1), (1, 20, 60000, 1, 2), (1, 30, 90000, 1, 3) ON DUPLICATE KEY UPDATE gb = gb;