-- ╔══════════════════════════════════════════════════════╗ -- ║ NH FREE FIRE SHOP — DATABASE SCHEMA v∞ ║ -- ║ nhfreefire.pro | MySQL 8.x | utf8mb4 ║ -- ╚══════════════════════════════════════════════════════╝ CREATE DATABASE IF NOT EXISTS nhfreefire_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE nhfreefire_bot; -- ── USERS ────────────────────────────────────────────── CREATE TABLE users ( id BIGINT PRIMARY KEY, username VARCHAR(64), full_name VARCHAR(128), phone VARCHAR(20), balance DECIMAL(15,0) DEFAULT 0, points INT DEFAULT 0, rank ENUM('member','silver','gold','vip','diamond') DEFAULT 'member', total_spent DECIMAL(15,0) DEFAULT 0, total_orders INT DEFAULT 0, is_banned TINYINT DEFAULT 0, ban_reason VARCHAR(255), state VARCHAR(64) DEFAULT 'idle', state_data JSON, lang VARCHAR(8) DEFAULT 'vi', ref_by BIGINT, ref_count INT DEFAULT 0, last_seen DATETIME, joined_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_rank (rank), INDEX idx_balance (balance) ); -- ── CATEGORIES ───────────────────────────────────────── CREATE TABLE categories ( id INT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(64) UNIQUE, name VARCHAR(128), emoji VARCHAR(16), description TEXT, sort_order INT DEFAULT 0, is_active TINYINT DEFAULT 1 ); INSERT INTO categories (slug, name, emoji, sort_order) VALUES ('ai_account', 'Tài khoản AI', '🤖', 1), ('via_clone', 'Via / Clone', '👤', 2), ('tut_trick', 'Tut / Trick', '📚', 3), ('topup', 'Nạp tiền / SOS', '💳', 4); -- ── PRODUCTS ─────────────────────────────────────────── CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, category_id INT, name VARCHAR(255), emoji VARCHAR(16) DEFAULT '📦', description TEXT, price DECIMAL(15,0) NOT NULL, sale_price DECIMAL(15,0), stock_count INT DEFAULT 0, sold_count INT DEFAULT 0, delivery_type ENUM('auto','manual') DEFAULT 'auto', is_active TINYINT DEFAULT 1, sort_order INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id), INDEX idx_category (category_id), INDEX idx_active (is_active) ); -- ── STOCK (kho hàng số) ──────────────────────────────── CREATE TABLE stock ( id BIGINT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, content TEXT NOT NULL, is_sold TINYINT DEFAULT 0, sold_to BIGINT, order_id BIGINT, added_at DATETIME DEFAULT CURRENT_TIMESTAMP, sold_at DATETIME, FOREIGN KEY (product_id) REFERENCES products(id), INDEX idx_product_unsold (product_id, is_sold) ); -- ── ORDERS ───────────────────────────────────────────── CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_code VARCHAR(32) UNIQUE, user_id BIGINT, product_id INT, product_name VARCHAR(255), quantity INT DEFAULT 1, unit_price DECIMAL(15,0), total_price DECIMAL(15,0), discount DECIMAL(15,0) DEFAULT 0, paid_amount DECIMAL(15,0) DEFAULT 0, pay_method ENUM('bank','wallet','point') DEFAULT 'bank', status ENUM('pending','paid','delivered','cancelled','refunded') DEFAULT 'pending', delivery_data TEXT, note VARCHAR(500), expires_at DATETIME, paid_at DATETIME, delivered_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id), INDEX idx_user (user_id), INDEX idx_status (status), INDEX idx_code (order_code), INDEX idx_expires (expires_at) ); -- ── TRANSACTIONS ──────────────────────────────────────── CREATE TABLE transactions ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT, type ENUM('deposit','purchase','refund','bonus','point_use') DEFAULT 'deposit', amount DECIMAL(15,0), balance_before DECIMAL(15,0), balance_after DECIMAL(15,0), order_id BIGINT, bank_ref VARCHAR(128), description VARCHAR(500), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_user (user_id), INDEX idx_type (type), INDEX idx_bank_ref (bank_ref) ); -- ── BANK TRANSACTIONS (raw từ API) ───────────────────── CREATE TABLE bank_transactions ( id BIGINT AUTO_INCREMENT PRIMARY KEY, bank_ref VARCHAR(128) UNIQUE, amount DECIMAL(15,0), content VARCHAR(500), transaction_at DATETIME, is_processed TINYINT DEFAULT 0, order_code VARCHAR(32), processed_at DATETIME, raw_data JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_processed (is_processed), INDEX idx_ref (bank_ref) ); -- ── TICKETS (khiếu nại / hỗ trợ) ────────────────────── CREATE TABLE tickets ( id BIGINT AUTO_INCREMENT PRIMARY KEY, ticket_code VARCHAR(32) UNIQUE, user_id BIGINT, order_id BIGINT, type ENUM('complaint','support','feedback','refund') DEFAULT 'support', subject VARCHAR(255), status ENUM('open','inprogress','resolved','closed') DEFAULT 'open', priority ENUM('low','normal','high','urgent') DEFAULT 'normal', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_user (user_id), INDEX idx_status (status) ); CREATE TABLE ticket_messages ( id BIGINT AUTO_INCREMENT PRIMARY KEY, ticket_id BIGINT, sender_id BIGINT, is_admin TINYINT DEFAULT 0, message TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (ticket_id) REFERENCES tickets(id) ); -- ── BROADCASTS ───────────────────────────────────────── CREATE TABLE broadcasts ( id BIGINT AUTO_INCREMENT PRIMARY KEY, admin_id BIGINT, message TEXT, target ENUM('all','category','rank') DEFAULT 'all', target_val VARCHAR(64), sent_count INT DEFAULT 0, fail_count INT DEFAULT 0, status ENUM('pending','sending','done') DEFAULT 'pending', scheduled_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── REVIEWS ──────────────────────────────────────────── CREATE TABLE reviews ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT, order_id BIGINT UNIQUE, product_id INT, rating TINYINT, comment TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── SETTINGS ──────────────────────────────────────────── CREATE TABLE settings ( `key` VARCHAR(64) PRIMARY KEY, `value` TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO settings (`key`, `value`) VALUES ('maintenance', '0'), ('welcome_msg', 'Chào mừng đến NH Free Fire Shop! 🔥'), ('min_deposit', '10000'), ('point_rate', '100'), ('point_value', '500');