-- ============================================================ -- NexaNetwork MLM Platform — MySQL Schema -- Run: mysql -u root -p < db/schema.sql -- ============================================================ CREATE DATABASE IF NOT EXISTS nexanetwork CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE nexanetwork; -- ── USERS ────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(180) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, phone VARCHAR(20), ref_code VARCHAR(30) NOT NULL UNIQUE, referred_by INT UNSIGNED NULL, -- FK to users.id (direct upline) upi_id VARCHAR(120), -- for payouts bank_account VARCHAR(30), bank_ifsc VARCHAR(15), balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_earned DECIMAL(12,2) NOT NULL DEFAULT 0.00, rank ENUM('bronze','silver','gold','platinum') NOT NULL DEFAULT 'bronze', is_active TINYINT(1) NOT NULL DEFAULT 1, email_verified TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- ── COMMISSIONS ──────────────────────────────────────────── CREATE TABLE IF NOT EXISTS commissions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, to_user_id INT UNSIGNED NOT NULL, -- earner from_user_id INT UNSIGNED NOT NULL, -- who triggered it level TINYINT UNSIGNED NOT NULL, -- 1-4 amount DECIMAL(10,2) NOT NULL, sale_amount DECIMAL(10,2) NOT NULL, -- base amount commission was on rate DECIMAL(5,4) NOT NULL, -- e.g. 0.1000 = 10% status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending', note VARCHAR(255), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME, FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ── WITHDRAWALS ──────────────────────────────────────────── CREATE TABLE IF NOT EXISTS withdrawals ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL, method ENUM('upi','bank','razorpay') NOT NULL DEFAULT 'upi', payout_account VARCHAR(120) NOT NULL, -- UPI ID or account number status ENUM('pending','processing','paid','failed') NOT NULL DEFAULT 'pending', razorpay_payout_id VARCHAR(80), razorpay_fund_id VARCHAR(80), failure_reason TEXT, requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, processed_at DATETIME, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ── EMAIL INVITES ────────────────────────────────────────── CREATE TABLE IF NOT EXISTS invites ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sender_id INT UNSIGNED NOT NULL, email VARCHAR(180) NOT NULL, token VARCHAR(64) NOT NULL UNIQUE, used TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL, FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ── SESSIONS (optional: use JWT instead) ────────────────── CREATE TABLE IF NOT EXISTS sessions ( id VARCHAR(64) PRIMARY KEY, user_id INT UNSIGNED NOT NULL, ip VARCHAR(45), user_agent VARCHAR(255), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ── INDEXES ─────────────────────────────────────────────── CREATE INDEX idx_users_referred_by ON users(referred_by); CREATE INDEX idx_users_ref_code ON users(ref_code); CREATE INDEX idx_commissions_to ON commissions(to_user_id); CREATE INDEX idx_commissions_from ON commissions(from_user_id); CREATE INDEX idx_withdrawals_user ON withdrawals(user_id); CREATE INDEX idx_withdrawals_status ON withdrawals(status); -- ── SEED DEMO DATA ───────────────────────────────────────── -- Passwords are bcrypt of "demo1234" INSERT IGNORE INTO users (id, name, email, password_hash, phone, ref_code, referred_by, balance, total_earned, rank) VALUES (1, 'Priya Sharma', 'priya@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919876543210', 'REF-PRI001', NULL, 4520.00, 12400.00, 'gold'), (2, 'Amit Verma', 'amit@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919988765432', 'REF-AMI002', 1, 1800.00, 3600.00, 'silver'), (3, 'Sunita Patel', 'sunita@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919765432109', 'REF-SUN003', 1, 950.00, 2100.00, 'silver'), (4, 'Rahul Singh', 'rahul@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919654321098', 'REF-RAH004', 2, 600.00, 1200.00, 'bronze'), (5, 'Neha Gupta', 'neha@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919543210987', 'REF-NEH005', 2, 430.00, 870.00, 'bronze'), (6, 'Vikram Joshi', 'vikram@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919432109876', 'REF-VIK006', 3, 210.00, 420.00, 'bronze'); INSERT IGNORE INTO commissions (to_user_id, from_user_id, level, amount, sale_amount, rate, status, paid_at) VALUES (1, 2, 1, 500.00, 5000.00, 0.1000, 'paid', '2024-02-10'), (1, 3, 1, 400.00, 4000.00, 0.1000, 'paid', '2024-02-20'), (1, 4, 2, 200.00, 4000.00, 0.0500, 'paid', '2024-03-05'), (2, 4, 1, 400.00, 4000.00, 0.1000, 'paid', '2024-03-05'), (1, 5, 2, 180.00, 3600.00, 0.0500, 'paid', '2024-03-18'), (2, 5, 1, 360.00, 3600.00, 0.1000, 'paid', '2024-03-18'), (1, 6, 3, 90.00, 3000.00, 0.0300, 'pending', NULL), (3, 6, 1, 300.00, 3000.00, 0.1000, 'pending', NULL);